C# 导入导出Excel 使用NPOI

最新推荐文章于 2024-04-10 17:47:48 发布

qq_39076039

最新推荐文章于 2024-04-10 17:47:48 发布

阅读量439

点赞数

文章标签： excel c# 开发语言 wpf .net

本文链接：https://blog.csdn.net/qq_39076039/article/details/127883840

版权

首先添加依赖 Nuget 下载Npoi

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

导出类

 /// <summary>
    /// Excel操作类
    /// </summary>
    public class ExcelHelper
    {
   
        //自定义导出事件 
        public delegate void ExportPercent(int value);
        public event ExportPercent Export;
        public BackgroundWorker worker;

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="data">数据</param>
        /// <param name="excelColumns">列头</param>
        /// <param name="titleName">标题</param>
        /// <returns></returns>
        public MemoryStream ExportExcel<T>(List<T> data, List<ExcelHeaderColumn> excelColumns, string titleName)
        {
   
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            //标题
            IRow rowTitle = sheet.CreateRow(0);
            ICell celltitle = rowTitle.CreateCell(0, CellType.STRING);
            celltitle.SetCellValue(titleName);
            rowTitle.Cells.Add(celltitle);

            //列名
            IRow rowColum = sheet.CreateRow(1);
            for (int j = 0; j < excelColumns.Count; j++)
            {
   
                ICell cell = rowColum.CreateCell(j);
                cell.SetCellValue(excelColumns[j].DisplayName);
                rowColum.Cells.Add(cell);
            }

            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            string value = null;
            for (int i = 0; i < data.Count; i++)
            {
   
                IRow row = sheet.CreateRow(i + 2);
                for (int j = 0; j < excelColumns.Count; j++)
                {
   
                    value = null;
                    ExcelHeaderColumn col = excelColumns[j];
                    PropertyInfo property = properties.FirstOrDefault(p => p.Name == col.Name);
                    if (property != null)
                    {
   
                        Object tempValue = property.GetValue(data[i], null);
                        if (tempValue != null) {
    value = tempValue.ToString(); }
                    }
                    SetCellValue(row, j, value);
                }
                if (worker != null)
                {
   
                    worker.ReportProgress(i);
                    Thread.Sleep(10);
                }
            }
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="data">数据</param>
        /// <param name="excelColumns">列头</param>
        /// <returns></returns>
        public  MemoryStream ExportExcel<T>(List<T> data, List<ExcelHeaderColumn> excelColumns)
        {
   
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            CreateHeader(sheet, excelColumns);
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            string value = null