打工笔记--------------------------弄了一个还不错的NPOI的helper类

标签：style string helper int NPOI 表头 -------------------------- new model

`using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace Utils.Public
{
public partial class NPOIHelper
{
///

/// Excel导入
///

/// Excel路径
/// 表头行号
/// 表头名称对应（例如：跳远--1分钟跳远）
///
public static DataSet GetDataSetFromExcel(string excelPath, int headRowCount, int startRowIndex = 1, Dictionary<string, string> dicCols = null)
{
try
{
DataSet ds = new DataSet();
IWorkbook wb = null;
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
wb = WorkbookFactory.Create(fs);
//wb = new HSSFWorkbook(fs);
}
int sheetcount = wb.NumberOfSheets;
if (sheetcount <= 0)
{
return null;
}
for (int x = 0; x < sheetcount; x++)
{
DataTable dt = new DataTable();
ISheet sheet = wb.GetSheetAt(x);

                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                dt.TableName = sheet.SheetName;
                int cellCount = 0;
                bool NoData = false;
                List<IRow> headerRows = new List<IRow>();
                for (int i = 0; i < headRowCount; i++)
                {
                    IRow headerRow = sheet.GetRow(i);
                    if (headerRow == null)
                    {
                        NoData = true;
                        continue;
                    }
                    headerRows.Add(headerRow);
                    cellCount = headerRow.LastCellNum;
                }
                //设置表头
                for (int j = 0; j < cellCount; j++)
                {
                    for (int i = 0; i < headerRows.Count; i++)
                    {
                        ICell cell = headerRows[i].GetCell(j);
                        string tabelTitle = cell.ToString();
                        if (!String.IsNullOrWhiteSpace(tabelTitle))
                        {
                            if (dicCols != null)
                            {
                                string colTitle = dicCols.Where(p => tabelTitle.Contains(p.Key)).Select(p => p.Value).FirstOrDefault();
                                if (!string.IsNullOrEmpty(colTitle))
                                {
                                    tabelTitle = colTitle;//默认列名为子列第一列的名称
                                }
                            }
                            if (!dt.Columns.Contains(tabelTitle))//防止重复录入子列名
                            {
                                dt.Columns.Add(tabelTitle);
                            }
                        }
                    }
                }
                cellCount = dt.Columns.Count;
                if (NoData)
                {
                    continue;
                }

                for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();
                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                dataRow[j] = null;
                            }
                            else
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        dataRow[j] = null;
                                        break;

                                    case CellType.Boolean:
                                        dataRow[j] = cell.BooleanCellValue;
                                        break;

                                    case CellType.Numeric:
                                        //dataRow[j] = Convert.ToDouble(cell.NumericCellValue);
                                        //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                        if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                        {
                                            dataRow[j] = cell.DateCellValue;
                                        }
                                        else//其他数字类型
                                        {
                                            dataRow[j] = cell.NumericCellValue;
                                        }
                                        //dataRow[j] = cell.CellStyle.DataFormat;
                                        //if (dataRow[j] != "0") 
                                        //{ dataRow[j] = cell.DateCellValue; } 
                                        //else { dataRow[j] = Convert.ToDouble(cell.NumericCellValue); }
                                        break;

                                    case CellType.String:
                                        dataRow[j] = cell.StringCellValue;
                                        break;

                                    case CellType.Error:
                                        dataRow[j] = cell.ErrorCellValue;
                                        break;

                                    case CellType.Formula:
                                        dataRow[j] = Math.Round(cell.NumericCellValue, 2);
                                        break;

                                    default:
                                        dataRow[j] = "=" + cell.CellFormula;
                                        break;
                                }
                            }
                        }

                    }
                    dt.Rows.Add(dataRow);
                }

                ds.Tables.Add(dt);

            }
            return ds;
        }
        catch (Exception)
        {
            return null;
        }
    }
}

public partial class NPOIHelper
{

    public static bool DelRowFromExcel(string excelPath, int RowIndex)
    {
        try
        {
            FileStream reade = new FileStream(Path.GetFullPath(excelPath),
                      FileMode.Open, FileAccess.Read,
                      FileShare.ReadWrite);
            IWorkbook workBook = WorkbookFactory.Create(reade);
            ISheet workSheet = workBook.GetSheetAt(0);
            int lastRowNum = workSheet.LastRowNum;
            workSheet.ShiftRows(RowIndex, RowIndex, 1);
            FileStream stream = new FileStream(Path.GetFullPath(excelPath),
                       FileMode.Open, FileAccess.Read,
                       FileShare.ReadWrite);
            workBook.Write(stream);


            stream.Close();
            reade.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }
}


public partial class NPOIHelper
{
    /// <summary>
    /// Excel导入
    /// </summary>
    /// <param name="excelPath">Excel路径</param>
    /// <param name="headRowCount">表头行号</param>
    /// <param name="dicCols">表头名称对应（例如：跳远--1分钟跳远）</param>
    /// <returns></returns>
    public static DataSet GetDataSetFromExcel(int headRowStar, string excelPath, int headRowCount, int startRowIndex = 1, Dictionary<string, string> dicCols = null)
    {
        try
        {
            DataSet ds = new DataSet();
            IWorkbook wb = null;
            using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(fs);
                //wb = new HSSFWorkbook(fs);
            }
            int sheetcount = wb.NumberOfSheets;
            if (sheetcount <= 0)
            {
                return null;
            }
            for (int x = 0; x < sheetcount; x++)
            {
                DataTable dt = new DataTable();
                ISheet sheet = wb.GetSheetAt(x);

                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                dt.TableName = sheet.SheetName;
                int cellCount = 0;
                bool NoData = false;
                for (int i = headRowStar; i < headRowCount; i++)
                {
                    IRow headerRow = sheet.GetRow(i);
                    if (headerRow == null)
                    {
                        NoData = true;
                        continue;
                    }
                    cellCount = headerRow.LastCellNum;

                    for (int j = 0; j < cellCount; j++)
                    {
                        ICell cell = headerRow.GetCell(j);
                        string tabelTitle = cell.ToString();
                        if (!String.IsNullOrWhiteSpace(tabelTitle))
                        {
                            if (dicCols != null)
                            {
                                string colTitle = dicCols.Where(p => tabelTitle.Contains(p.Key)).Select(p => p.Value).FirstOrDefault();
                                if (!string.IsNullOrEmpty(colTitle))
                                {
                                    tabelTitle = colTitle;//默认列名为子列第一列的名称
                                }
                            }
                            if (!dt.Columns.Contains(tabelTitle))//防止重复录入子列名
                            {
                                dt.Columns.Add(tabelTitle);
                            }

                        }
                    }
                    cellCount = dt.Columns.Count;
                }
                if (NoData)
                {
                    continue;
                }

                for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();
                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                dataRow[j] = null;
                            }
                            else
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        dataRow[j] = null;
                                        break;

                                    case CellType.Boolean:
                                        dataRow[j] = cell.BooleanCellValue;
                                        break;

                                    case CellType.Numeric:
                                        //dataRow[j] = Convert.ToDouble(cell.NumericCellValue);
                                        //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                        if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                        {
                                            dataRow[j] = cell.DateCellValue;
                                        }
                                        else//其他数字类型
                                        {
                                            dataRow[j] = cell.NumericCellValue;
                                        }
                                        //dataRow[j] = cell.CellStyle.DataFormat;
                                        //if (dataRow[j] != "0") 
                                        //{ dataRow[j] = cell.DateCellValue; } 
                                        //else { dataRow[j] = Convert.ToDouble(cell.NumericCellValue); }
                                        break;

                                    case CellType.String:
                                        dataRow[j] = cell.StringCellValue;
                                        break;

                                    case CellType.Error:
                                        dataRow[j] = cell.ErrorCellValue;
                                        break;

                                    case CellType.Formula:
                                        dataRow[j] = Math.Round(cell.NumericCellValue, 2);
                                        break;

                                    default:
                                        dataRow[j] = "=" + cell.CellFormula;
                                        break;
                                }
                            }
                        }

                    }
                    dt.Rows.Add(dataRow);
                }

                ds.Tables.Add(dt);

            }
            return ds;
        }
        catch (Exception)
        {
            return null;
        }
    }
}

/// <summary>
/// Excel导出
/// </summary>
/// <remarks>
/// author:zhujt
/// create date:2015-9-11 16:44:41
/// </remarks>
public partial class NPOIHelper
{
    #region 初始化
    /// <summary>
    /// 声明 HSSFWorkbook 对象
    /// </summary>
    private static HSSFWorkbook _workbook;
    /// <summary>
    /// 声明 HSSFSheet 对象
    /// </summary>
    private static HSSFSheet _sheet;
    #endregion

    /// <summary>
    /// Excel导出
    /// </summary>
    /// <param name="fileName">文件名称 如果为空或NULL，则默认“新建Excel.xls”</param>
    /// <param name="list"></param>
    /// <param name="method">导出方式 1：WEB导出（默认）2：按文件路径导出</param>
    /// <param name="filePath">文件路径 如果WEB导出，则可以为空；如果按文件路径导出，则默认桌面路径</param>
    public static void Export(string fileName, IList<NPOIModel> list, int method = 1, string filePath = null)
    {
        // 文件名称
        if (!string.IsNullOrEmpty(fileName))
        {
            if (fileName.IndexOf('.') == -1)
                fileName += ".xls";
            else
                fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls";
        }
        else
            fileName = "新建Excel.xls";

        // 文件路径
        if (2 == method && string.IsNullOrEmpty(filePath))
            filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

        // 调用导出处理程序
        Export(list);

        // WEB导出
        if (1 == method)
        {
            MvcContext.GetContext().Response.ContentType = "application/vnd.ms-excel";
            //设置下载的Excel文件名
            MvcContext.GetContext().Response.Headers.Add("Content-Disposition", string.Format("attachment;filename={0}", fileName));

            using (MemoryStream ms = new MemoryStream())
            {
                //将工作簿的内容放到内存流中
                _workbook.Write(ms);
                //将内存流转换成字节数组发送到客户端
                MvcContext.GetContext().Response.Body.Write(ms.GetBuffer());
                MvcContext.GetContext().Response.Body.Flush();
                _sheet = null;
                _workbook = null;
            }
        }
        else if (2 == method)
        {
            using (FileStream fs = File.Open(filePath, FileMode.Append))
            {
                _workbook.Write(fs);
                _sheet = null;
                _workbook = null;
            }
        }
    }

    /// <summary>
    /// 导出方法实现
    /// </summary>
    /// <param name="list"></param>
    private static void Export(IList<NPOIModel> list)
    {
        #region 变量声明
        // 初始化
        _workbook = new HSSFWorkbook();
        // 声明 Row 对象
        IRow _row;
        // 声明 Cell 对象
        ICell _cell;
        // 总列数
        int cols = 0;
        // 总行数
        int rows = 0;
        // 行数计数器
        int rowIndex = 0;
        // 单元格值
        string drValue = null;
        #endregion

        foreach (NPOIModel model in list)
        {
            // 工作薄命名
            if (model.sheetName != null)
                _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
            else
                _sheet = (HSSFSheet)_workbook.CreateSheet();

            // 获取数据源
            DataTable dt = model.dataSource;
            // 初始化
            rowIndex = 0;
            // 获取总行数
            rows = GetRowCount(model.headerName);
            // 获取总列数
            cols = GetColCount(model.headerName);
            ICellStyle style = _workbook.CreateCellStyle();
            // 循环行数
            foreach (DataRow row in dt.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                        _sheet = (HSSFSheet)_workbook.CreateSheet();

                    // 构建行
                    for (int i = 0; i < rows + model.isTitle; i++)
                    {
                        _row = _sheet.GetRow(i);
                        // 创建行
                        if (_row == null)
                            _row = _sheet.CreateRow(i);

                        for (int j = 0; j < cols; j++)
                            _row.CreateCell(j).CellStyle = bodyStyle(style);
                    }

                    // 如果存在表标题
                    if (model.isTitle > 0)
                    {
                        // 获取行
                        _row = _sheet.GetRow(0);
                        // 合并单元格
                        CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
                        _sheet.AddMergedRegion(region);
                        // 填充值
                        _row.CreateCell(0).SetCellValue(model.tableTitle);
                        // 设置样式
                        _row.GetCell(0).CellStyle = titleStyle;
                        // 设置行高
                        _row.HeightInPoints = 20;
                    }

                    // 取得上一个实体
                    NPOIHeader lastRow = null;
                    IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
                    // 创建表头
                    foreach (NPOIHeader m in hList)
                    {
                        var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
                        if (data.Count() > 0)
                        {
                            lastRow = data.First();
                            if (m.headerName == lastRow.headerName)
                                m.firstCol = lastRow.firstCol;
                        }

                        // 获取行
                        _row = _sheet.GetRow(m.firstRow);
                        if (m.firstRow != m.lastRow || m.firstCol != m.lastCol)
                        {
                            // 合并单元格
                            CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);
                            _sheet.AddMergedRegion(region);
                        }
                        // 填充值
                        _row.CreateCell(m.firstCol).SetCellValue(m.headerName);
                    }

                    // 填充表头样式
                    for (int i = 0; i < rows + model.isTitle; i++)
                    {
                        _row = _sheet.GetRow(i);
                        for (int j = 0; j < cols; j++)
                        {
                            _row.GetCell(j).CellStyle = bodyStyle(style);
                            //设置列宽
                            //_sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 256);
                            if (model.colWidths[j] > 255)
                            {
                                model.colWidths[j] = 254;
                            }
                            else
                            {
                                _sheet.SetColumnWidth(j, ((model.colWidths[j] + 1) > 40 ? 40 : model.colWidths[j] + 1) * 256);
                            }
                        }
                    }

                    rowIndex = (rows + model.isTitle);
                }
                #endregion

                #region 填充内容
                // 构建列
                _row = _sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    // 添加序号列
                    if (1 == model.isOrderby && column.Ordinal == 0)
                    {
                        _cell = _row.CreateCell(0);
                        _cell.SetCellValue(rowIndex - rows);
                        _cell.CellStyle = bodyStyle(style);
                    }

                    // 创建列
                    _cell = _row.CreateCell(column.Ordinal + model.isOrderby);

                    // 获取值
                    drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            _cell.SetCellValue(drValue);
                            _cell.CellStyle = bodyStyle(style);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            _cell.SetCellValue(dateV);

                            _cell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            _cell.SetCellValue(boolV);
                            _cell.CellStyle = bodyStyle(style);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            _cell.SetCellValue(intV);
                            _cell.CellStyle = bodyRightStyle;
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            _cell.SetCellValue(doubV);
                            _cell.CellStyle = bodyRightStyle;
                            break;
                        case "System.DBNull"://空值处理
                            _cell.SetCellValue("");
                            break;
                        default:
                            _cell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }
        }
    }

    #region 辅助方法
    /// <summary>
    /// 表头解析
    /// </summary>
    /// <remarks>
    /// author:zhujt
    /// create date:2015-9-10 19:24:51
    /// </remarks>
    /// <param name="header">表头</param>
    /// <param name="rows">总行数</param>
    /// <param name="addRows">外加行</param>
    /// <param name="addCols">外加列</param>
    /// <returns></returns>
    private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows)
    {
        // 临时表头数组
        string[] tempHeader;
        string[] tempHeader2;
        // 所跨列数
        int colSpan = 0;
        // 所跨行数
        int rowSpan = 0;
        // 单元格对象
        NPOIHeader model = null;
        // 行数计数器
        int rowIndex = 0;
        // 列数计数器
        int colIndex = 0;
        // 
        IList<NPOIHeader> list = new List<NPOIHeader>();
        // 初步解析
        string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);
        // 表头遍历
        for (int i = 0; i < headers.Length; i++)
        {
            // 行数计数器清零
            rowIndex = 0;
            // 列数计数器清零
            colIndex = 0;
            // 获取所跨行数
            rowSpan = GetRowSpan(headers[i], rows);
            // 获取所跨列数
            colSpan = GetColSpan(headers[i]);

            // 如果所跨行数与总行数相等，则不考虑是否合并单元格问题
            if (rows == rowSpan)
            {
                colIndex = GetMaxCol(list);
                model = new NPOIHeader(headers[i],
                    addRows,
                    (rowSpan - 1 + addRows),
                    colIndex,
                    (colSpan - 1 + colIndex),
                    addRows);
                list.Add(model);
                rowIndex += (rowSpan - 1) + addRows;
            }
            else
            {
                // 列索引
                colIndex = GetMaxCol(list);
                // 如果所跨行数不相等，则考虑是否包含多行
                tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                for (int j = 0; j < tempHeader.Length; j++)
                {

                    // 如果总行数=数组长度
                    if (1 == GetColSpan(tempHeader[j]))
                    {
                        if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
                        {
                            model = new NPOIHeader(tempHeader[j],
                                (j + addRows),
                                (j + addRows) + (rows - tempHeader.Length),
                                colIndex,
                                (colIndex + colSpan - 1),
                                addRows);
                            list.Add(model);
                        }
                        else
                        {
                            model = new NPOIHeader(tempHeader[j],
                                    (j + addRows),
                                    (j + addRows),
                                    colIndex,
                                    (colIndex + colSpan - 1),
                                    addRows);
                            list.Add(model);
                        }
                    }
                    else
                    {
                        // 如果所跨列数不相等，则考虑是否包含多列
                        tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                        for (int m = 0; m < tempHeader2.Length; m++)
                        {
                            // 列索引
                            colIndex = GetMaxCol(list) - colSpan + m;
                            if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
                            {
                                model = new NPOIHeader(tempHeader2[m],
                                    (j + addRows),
                                    (j + addRows) + (rows - tempHeader.Length),
                                    colIndex,
                                    colIndex,
                                    addRows);
                                list.Add(model);
                            }
                            else
                            {
                                model = new NPOIHeader(tempHeader2[m],
                                        (j + addRows),
                                        (j + addRows),
                                        colIndex,
                                        colIndex,
                                        addRows);
                                list.Add(model);
                            }
                        }
                    }
                    rowIndex += j + addRows;
                }
            }
        }
        return list;
    }

    /// <summary>
    /// 获取最大列
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    private static int GetMaxCol(IList<NPOIHeader> list)
    {
        int maxCol = 0;
        if (list.Count > 0)
        {
            foreach (NPOIHeader model in list)
            {
                if (maxCol < model.lastCol)
                    maxCol = model.lastCol;
            }
            maxCol += 1;
        }

        return maxCol;
    }

    /// <summary>
    /// 获取表头行数
    /// </summary>
    /// <param name="newHeaders">表头文字</param>
    /// <returns></returns>
    private static int GetRowCount(string newHeaders)
    {
        string[] ColumnNames = newHeaders.Split(new char[] { '@' });
        int Count = 0;
        if (ColumnNames.Length <= 1)
            ColumnNames = newHeaders.Split(new char[] { '#' });
        foreach (string name in ColumnNames)
        {
            int TempCount = name.Split(new char[] { ' ' }).Length;
            if (TempCount > Count)
                Count = TempCount;
        }
        return Count;
    }

    /// <summary>
    /// 获取表头列数
    /// </summary>
    /// <param name="newHeaders">表头文字</param>
    /// <returns></returns>
    private static int GetColCount(string newHeaders)
    {
        string[] ColumnNames = newHeaders.Split(new char[] { '@' });
        int Count = 0;
        if (ColumnNames.Length <= 1)
            ColumnNames = newHeaders.Split(new char[] { '#' });
        Count = ColumnNames.Length;
        foreach (string name in ColumnNames)
        {
            int TempCount = name.Split(new char[] { ',' }).Length;
            if (TempCount > 1)
                Count += TempCount - 1;
        }
        return Count;
    }

    /// <summary>
    /// 列头跨列数
    /// </summary>
    /// <remarks>
    /// author:zhujt
    /// create date:2015-9-9 09:17:34
    /// </remarks>
    /// <param name="newHeaders">表头文字</param>
    /// <returns></returns>
    private static int GetColSpan(string newHeaders)
    {
        return newHeaders.Split(',').Count();
    }

    /// <summary>
    /// 列头跨行数
    /// </summary> 
    /// <remarks>
    /// author:zhujt
    /// create date:2015-9-9 09:17:14
    /// </remarks>
    /// <param name="newHeaders">列头文本</param>
    /// <param name="rows">表头总行数</param>
    /// <returns></returns>
    private static int GetRowSpan(string newHeaders, int rows)
    {
        int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length;
        // 如果总行数与当前表头所拥有行数相等
        if (rows == Count)
            Count = 1;
        else if (Count < rows)
            Count = 1 + (rows - Count);
        else
            throw new Exception("表头格式不正确！");
        return Count;
    }
    #endregion

    #region 单元格样式
    /// <summary>
    /// 数据单元格样式
    /// </summary>
    private static ICellStyle bodyStyle(ICellStyle style)
    {
        //get
        //{
        //ICellStyle style = _workbook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.Center; //居中
        style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
        style.WrapText = true;//自动换行
                              // 边框
        style.BorderBottom = BorderStyle.Thin;
        style.BorderLeft = BorderStyle.Thin;
        style.BorderRight = BorderStyle.Thin;
        style.BorderTop = BorderStyle.Thin;
        // 字体
        IFont font = _workbook.CreateFont();
        font.FontHeightInPoints = 10;
        font.FontName = "宋体";
        style.SetFont(font);

        return style;
        //}
    }

    /// <summary>
    /// 数据单元格样式
    /// </summary>
    private static ICellStyle bodyRightStyle
    {
        get
        {
            ICellStyle style = _workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            style.WrapText = true;//自动换行
            // 边框
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            // 字体
            IFont font = _workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "宋体";
            style.SetFont(font);

            return style;
        }
    }

    /// <summary>
    /// 标题单元格样式
    /// </summary>
    private static ICellStyle titleStyle
    {
        get
        {
            ICellStyle style = _workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            style.WrapText = true;//自动换行 

            IFont font = _workbook.CreateFont();
            font.FontHeightInPoints = 14;
            font.FontName = "宋体";
            font.Boldweight = (short)FontBoldWeight.Bold;
            style.SetFont(font);

            return style;
        }
    }

    /// <summary>
    /// 日期单元格样式
    /// </summary>
    private static ICellStyle dateStyle
    {
        get
        {
            ICellStyle style = _workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            style.WrapText = true;//自动换行
            // 边框
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            // 字体
            IFont font = _workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "宋体";
            style.SetFont(font);

            IDataFormat format = _workbook.CreateDataFormat();
            style.DataFormat = format.GetFormat("yyyy-MM-dd");
            return style;
        }
    }
    #endregion
}

/// <summary>
/// 实体类
/// </summary>
public class NPOIModel
{
    /// <summary>
    /// 数据源
    /// </summary>
    public DataTable dataSource { get; private set; }
    /// <summary>
    /// 要导出的数据列数组
    /// </summary>
    public string[] fileds { get; private set; }
    /// <summary>
    /// 工作薄名称数组
    /// </summary>
    public string sheetName { get; private set; }
    /// <summary>
    /// 表标题
    /// </summary>
    public string tableTitle { get; private set; }
    /// <summary>
    /// 表标题是否存在 1：存在 0：不存在
    /// </summary>
    public int isTitle { get; private set; }
    /// <summary>
    /// 是否添加序号
    /// </summary>
    public int isOrderby { get; private set; }
    /// <summary>
    /// 表头
    /// </summary>
    public string headerName { get; private set; }
    /// <summary>
    /// 取得列宽
    /// </summary>
    public int[] colWidths { get; private set; }
    /// <summary>
    /// 构造函数
    /// </summary>
    /// <remarks>
    /// author:zhujt
    /// create date:2015-9-10 11:17:54
    /// </remarks>
    /// <param name="dataSource">数据来源 DataTable</param>
    /// <param name="filed">要导出的字段，如果为空或NULL，则默认全部</param> 
    /// <param name="sheetName">工作薄名称</param>
    /// <param name="headerName">表头名称 如果为空或NULL，则默认数据列字段
    /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')
    /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名 
    /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值 
    /// 三行时请注意：列头要重复
    /// </param>
    /// <param name="tableTitle">表标题</param> 
    /// <param name="isOrderby">是否添加序号 0：不添加 1：添加</param>
    public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0)
    {
        if (!string.IsNullOrEmpty(filed))
        {
            this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);

            // 移除多余数据列
            for (int i = dataSource.Columns.Count - 1; i >= 0; i--)
            {
                DataColumn dc = dataSource.Columns[i];
                if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper()))
                {
                    dataSource.Columns.Remove(dataSource.Columns[i]);
                }
            }

            // 列索引
            int colIndex = 0;
            // 循环排序
            for (int i = 0; i < this.fileds.Length; i++)
            {
                if (dataSource.Columns.Contains(this.fileds[i]))
                    dataSource.Columns[fileds[i]].SetOrdinal(colIndex++);
            }
            //导出未按指定排列顺序问题优化，注释以下代码   --add by wzy 2020/11/27
            //for (int i = 0; i < dataSource.Columns.Count; i++)
            //{
            //    // 获取索引
            //    colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper());
            //    // 设置下标
            //    dataSource.Columns[i].SetOrdinal(colIndex);
            //}
        }
        else
        {
            this.fileds = new string[dataSource.Columns.Count];
            for (int i = 0; i < dataSource.Columns.Count; i++)
            {
                this.fileds[i] = dataSource.Columns[i].ColumnName;
            }
        }
        this.dataSource = dataSource;

        if (!string.IsNullOrEmpty(sheetName))
            this.sheetName = sheetName;

        if (!string.IsNullOrEmpty(headerName))
            this.headerName = headerName;
        else
            this.headerName = string.Join("#", this.fileds);

        if (!string.IsNullOrEmpty(tableTitle))
        {
            this.tableTitle = tableTitle;
            this.isTitle = 1;
        }

        // 取得数据列宽 数据列宽可以和表头列宽比较，采取最长宽度  
        colWidths = new int[this.dataSource.Columns.Count];
        foreach (DataColumn item in this.dataSource.Columns)
            colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        // 循环比较最大宽度
        for (int i = 0; i < this.dataSource.Rows.Count; i++)
        {
            for (int j = 0; j < this.dataSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length;
                if (intTemp > colWidths[j])
                    colWidths[j] = intTemp;
            }
        }

        if (isOrderby > 0)
        {
            this.isOrderby = isOrderby;
            this.headerName = "序号#" + this.headerName;
        }

    }

    /// <summary>
    /// 获取列名下标
    /// </summary>
    /// <param name="colName">列名称</param>
    /// <returns></returns>
    private int GetColIndex(string colName)
    {
        for (int i = 0; i < this.fileds.Length; i++)
        {
            if (colName == this.fileds[i])
                return i;
        }
        return 0;
    }
}

/// <summary>
/// 表头构建类
/// </summary>
public class NPOIHeader
{
    /// <summary>
    /// 表头
    /// </summary>
    public string headerName { get; set; }
    /// <summary>
    /// 起始行
    /// </summary>
    public int firstRow { get; set; }
    /// <summary>
    /// 结束行
    /// </summary>
    public int lastRow { get; set; }
    /// <summary>
    /// 起始列
    /// </summary>
    public int firstCol { get; set; }
    /// <summary>
    /// 结束列
    /// </summary>
    public int lastCol { get; set; }
    /// <summary>
    /// 是否跨行
    /// </summary>
    public int isRowSpan { get; private set; }
    /// <summary>
    /// 是否跨列
    /// </summary>
    public int isColSpan { get; private set; }
    /// <summary>
    /// 外加行
    /// </summary>
    public int rows { get; set; }

    public NPOIHeader() { }
    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="headerName">表头</param>
    /// <param name="firstRow">起始行</param>
    /// <param name="lastRow">结束行</param>
    /// <param name="firstCol">起始列</param>
    /// <param name="lastCol">结束列</param>
    /// <param name="rows">外加行</param>
    /// <param name="cols">外加列</param>
    public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0)
    {
        this.headerName = headerName;
        this.firstRow = firstRow;
        this.lastRow = lastRow;
        this.firstCol = firstCol;
        this.lastCol = lastCol;
        // 是否跨行判断
        if (firstRow != lastRow)
            isRowSpan = 1;
        if (firstCol != lastCol)
            isColSpan = 1;

        this.rows = rows;
    }
}

}
`

使用方法示例

` //查询数据
JD_EquipmentWaringBll server = new JD_EquipmentWaringBll(HttpContext);
Pagetion page = null;
var datalist = server.GetEquimentWaringList(page, search, typename, devicename);
if (datalist == null || datalist.Count <= 0)
{
datalist.Add(new BLL.Equipment.ViewModel.EquimentWaringVM()
{
EquimentID = "报警ID",
EquimentName = "设备名称",
WaringContent = "报警内容",
WaringID = "设备ID",
WaringTime = DateTime.Now,
WaringType = "报警类型"
});
}
//转换为表格
DataTable dt = new CommonUtil().ListToDataTable(datalist);
//使用IList配置表格列和表头，供NPOI调用
IList nlist = new List();
var fileld = "EquimentName;WaringContent;WaringTime;WaringType";
var heardname = "设备名称#报警内容#报警时间#报警类型";
//把NPOIModel实体添加到Ilist列表中，多个工作簿矿压加多个实体
nlist.Add(new NPOIModel(dt, fileld, "设备报警导出文件", heardname, "设备报警导出文件"));

            //设置保存路径
            string relativePath = "Upload/Temp/Equipment/";
            string savePath = AppContext.BaseDirectory + relativePath;
            //判断路径是否存在，不存在就创建一个
            if (!Directory.Exists(savePath))
            {
                Directory.CreateDirectory(savePath);
            }
            //参数准备好后，调用NPOIHelper的Export方法，该方法最后会将文件存在临时储存位置
            NPOIHelper.Export("设备报警导出文件", nlist, 2, savePath + "设备报警导出文件");
            //文件夹中寻找
            if(System.IO.File.Exists(savePath + "设备报警导出文件"))
            {
                //转为文件流，先new一个FileStream对象
                FileStream fs = new FileStream(savePath + "设备报警导出文件", FileMode.Open, FileAccess.Read);
                //new一个长度足够的byte数组
                byte[] buffur = new byte[fs.Length];
                //使用FileStream对象的Read方法读取文件流
                fs.Read(buffur, 0, (int)fs.Length);
                fs.Close();
                //删除临时文件
                System.IO.File.Delete(savePath + "设备报警导出文件");
                lr.Data= File(buffur, "application/vnd.ms-excel;charset=utf-8", "设备报警导出文件");
                return lr;
            }
            else
            {
                var result = new LRReturn();
                result.Code = (int)LRReturnStatusCode.BadRequest;
                result.Message = "导出失败";
                return result;
            }`

标签：style,string,helper,int,NPOI,表头,--------------------------,new,model
From： https://www.cnblogs.com/misakayoucn/p/17486078.html