Estsh-Yiri/Estsh.Core/Util/NPOIExcelTools.cs

using Estsh.Core.Common.Models;
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace Estsh.Core.Util
{
    public class NPOIExcelTools
    {
        /// <summary>
        /// 根据模板创建Workbook
        /// </summary>
        /// <param name="TemplateFilePath">模板地址</param>
        /// <returns></returns>
        public static HSSFWorkbook InitHSSFWorkbook(string TemplateFilePath)
        {
            HSSFWorkbook workbook = null;
            if (string.IsNullOrEmpty(TemplateFilePath) == false)
            {
                // 模板文件的路径
                string TemplateFile =
                    Path.Combine(AppDomain.CurrentDomain.BaseDirectory, TemplateFilePath);
                FileStream SourceStream = new FileStream(TemplateFile, FileMode.Open);
                workbook = new HSSFWorkbook(SourceStream);
            }
            else
            {
                workbook = new HSSFWorkbook();
            }
            return workbook;
        }

        #region 无模板
        /// <summary>
        /// 根据datatable创建Workbook
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static HSSFWorkbook DataTableToWorkbook(DataTable dt, string sheetName)
        {
            return DataTableToWorkbook(new GridColumn[0], dt, sheetName);
        }
        /// <summary>
        /// 根据datatable创建Workbook
        /// </summary>
        /// <param name="columnArr"></param>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static HSSFWorkbook DataTableToWorkbook(GridColumn[] columnArr, DataTable dt, string sheetName)
        {
            return DataTableToWorkbook(string.Empty, columnArr, dt, sheetName);
        }

        #endregion

        #region 有模板
        /// <summary>
        /// DataTable装换为Workbook
        /// </summary>
        /// <param name="TemplateFilePath"></param>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static HSSFWorkbook DataTableToWorkbook(string TemplateFilePath, DataTable dt, string sheetName)
        {
            return DataTableToWorkbook(TemplateFilePath, new GridColumn[0], dt, sheetName);
        }
        /// <summary>
        /// DataTable装换为Workbook
        /// </summary>
        /// <param name="TemplateFilePath"></param>
        /// <param name="columnArr"></param>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static HSSFWorkbook DataTableToWorkbook(string TemplateFilePath, GridColumn[] columnArr, DataTable dt, string sheetName)
        {
            HSSFWorkbook workBook = InitHSSFWorkbook(TemplateFilePath);

            return AddSheet(workBook, columnArr, dt, sheetName);
        }
        #endregion
        /// <summary>
        /// 给现有Workbook添加Sheet
        /// </summary>
        /// <param name="workbook">需要添加Sheet的Workbook</param>
        /// <param name="columnArr">列</param>
        /// <param name="dt">原数据</param>
        /// <param name="sheetName">SheetName</param>
        /// <returns></returns>
        public static HSSFWorkbook AddSheet(HSSFWorkbook workbook, GridColumn[] columnArr, DataTable dt, string sheetName)
        {
            HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet;
            FillSheet(sheet, columnArr, dt);
            return workbook;
        }
        /// <summary>
        /// 填充Sheet
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="columnArr"></param>
        /// <param name="dt"></param>
        public static void FillSheet(HSSFSheet sheet, GridColumn[] columnArr, DataTable dt)
        {
            HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
            if (columnArr != null)
            {
                for (int i = 0, excelCol = 0; i < columnArr.Length; i++)
                {
                    if (Convert.ToBoolean(columnArr[i].Export))
                    {
                        headRow.CreateCell(excelCol).SetCellValue(columnArr[i].Display);
                        excelCol++;
                    }
                }
            }
            else
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    headRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
            }
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    int row = i + 1;
                    if (row < 65535)
                    {
                        HSSFRow dataRow = (HSSFRow)sheet.CreateRow(row);
                        for (int idx = 0, excelCol = 0; idx < columnArr.Length; idx++)
                        {
                            if (Convert.ToBoolean(columnArr[idx].Export))
                            {
                                string key = columnArr[idx].Name;
                                if (dt.Columns.Contains(key))
                                {
                                    object obj = dr[key];
                                    if (obj != null && obj != DBNull.Value)
                                    {
                                        dataRow.CreateCell(excelCol).SetCellValue(obj.ToString());
                                    }
                                }
                                excelCol++;
                            }
                        }
                    }
                }

            }
            if (columnArr != null)
            {
                for (int i = 0, excelCol = 0; i < columnArr.Length; i++)
                {
                    if (Convert.ToBoolean(columnArr[i].Export))
                    {
                        sheet.AutoSizeColumn(excelCol);
                        excelCol++;
                    }
                }
            }
            else
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }
        }
    }
}