OfficeTool 1.2.0

This is an Office tool, currently available functions include reading Excel data, generating Excel files, etc...

Examples

new OfficeTool.Samples.CreateExcelSimpleExample().Create();
new OfficeTool.Samples.CreateExcelExample().Create();
new OfficeTool.Samples.CreateExcelCustomExample().Create();
string filePath = "D:\\c#\\ConsoleApp\\bin\\Debug\\net6.0\\officeTemp\\20240604.xlsx";
var data = new OfficeTool.Samples.ReadExcelExample().Read(filePath);
var allData = new OfficeTool.Samples.ReadExcelAllExample().Read(filePath);

Demo

using NPOI.SS.UserModel;
using OfficeTool.ExcelHelpers;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using static OfficeTool.Models.Excel;


class Program
{
    static void Main()
    {
            //依赖注入
            IExcelHelper excelHelper;

            excelHelper = new OfficeTool.ExcelHelpers.ExcelHelper();

            var basePath = AppDomain.CurrentDomain.BaseDirectory;
            var filePath = Path.Combine(basePath, "file");
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }

            //读取单个Sheet并返回IEnumerable<T>
            var readData = excelHelper.ReadExcel<ExcelDemoModel>(readFilePath, ExcelDemoModel.ExcelHeader);
            //读取全部Sheet并返回List<IEnumerable<T>>
            var allData = excelHelper.ReadSheets<ExcelDemoModel>(readFilePath, ExcelDemoModel.ExcelHeader);

            //Excel数据源
            var insertData = new List<ExcelDemoModel>
            {
                new ExcelDemoModel() { Age = 20, City = "aa", Name = "张三" },
                new ExcelDemoModel() { Age = 22, City = "bb", Name = "李四" },
                new ExcelDemoModel() { Age = 25, City = "cc", Name = "王五" }
            };

            //简单导出
            excelHelper.CreateExcel(Path.Combine(filePath + "", $"{DateTime.Now:yyyyMMddHHmmssfff}.xlsx"), ExportHeaders, insertData);


            //自定义导出
            var wb = excelHelper.CreateWorkbook();
            var sheet1 = wb.CreateSheet("Test");
            //设置标题
            excelHelper.SetTitle(wb, sheet1, "班级人员年龄统计表", ExportHeaders.Count() - 1);
            //渲染表格
            excelHelper.RenderTable(sheet1, ExportHeaders, insertData);
            //增加统计行
            excelHelper.CreateRow(sheet1, (row) =>
            {
                for (int i = 0; i < ExportHeaders.Count(); i++)
                {
                    var cell = row.CreateCell(i);
                    excelHelper.SetCellValue(cell, DataType.String, i == 0 ? "平均年龄" : $"{Math.Ceiling(insertData.Sum(item => item.Age) / (insertData.Count() * 1.0))}岁");
                    excelHelper.SetCellStyle(cell, excelHelper.CreateStyle(wb: wb, border: true));
                }
            });
            //合并单元格
            excelHelper.MergedCells(sheet1, sheet1.LastRowNum, sheet1.LastRowNum, 0, 1);
            excelHelper.MergedCells(sheet1, sheet1.LastRowNum, sheet1.LastRowNum, 2, 3);
            //导出文件
            excelHelper.Save(wb, Path.Combine(filePath + "", $"{DateTime.Now:yyyyMMddHHmmssfff}.xlsx"));


        }

        //渲染单元格样式
        public static Func<IWorkbook, ICell, ExcelDemoModel, int, int, int, object> ExportHeadersFilter = (wookbook, cell, data, dataIndex, rowIndex, cellIndex) =>
        {
            var cellStyle = wookbook.OTCreateStyle(fontColor: NPOI.HSSF.Util.HSSFColor.Red.Index, bold: true);
            if (data.Age < 25) cell.CellStyle = cellStyle;
            return "";
        };
        public static IEnumerable<Header<ExcelDemoModel>> ExportHeaders => new List<Header<ExcelDemoModel>>
        {
            new Header<ExcelDemoModel> { Title = "序号", DataSource = "Index", ShowIndex = true, RenderCellStyle = ExportHeadersFilter},
            new Header<ExcelDemoModel> { Title = "姓名", DataSource = "Name", Width = 3600, RenderCellStyle = ExportHeadersFilter },
            new Header<ExcelDemoModel> { Title = "年龄", DataSource = "Age", DataType = DataType.Number, RenderCellStyle = ExportHeadersFilter},
            new Header<ExcelDemoModel> { Title = "城市", DataSource = "City", Render = (data) => data.City, RenderCellStyle = ExportHeadersFilter }
        };

        public class ExcelDemoModel
        {

            public string Name { get; set; }
            public int Age { get; set; }
            public string City { get; set; }

            public static Dictionary<string, string> ExcelHeader =
                new Dictionary<string, string>()
                {
                {"姓名","Name" },
                {"年龄","Age" },
                {"城市","City" },
                };

        }

}

Effect