Datatable导出为Excel NPOI

本内容包含：Datatable转Excel主要代码框架；excel模板的使用；一些常用字体格式；单元格复制等内容（代码内均有注释）

 

Datatable转Excel主要代码框架：

public static void DataTableToExcel(string filePath, DataTable dataTable)
        {

            //读取excel模板
            IWorkbook workbook = null;
            //string excelTempPath = @"C:\Users\chenweilong\Desktop\Template.xlsx";
            string excelTempPath = HttpContext.Current.Request.PhysicalApplicationPath + "Template.xlsx";
            using (FileStream fs = new FileStream(excelTempPath, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(fs);
                fs.Close();
            }

            //读第一张表
            ISheet sheet = workbook.GetSheetAt(0);
            IRow row = null;   //数据行
            ICell cell = null;  //数据行的中某列

            //总页数
            int totalPage = (dataTable.Rows.Count / 14) + 1;
            for(int p =0; p < totalPage; p++)
            {
                if(p!=totalPage-1)
                { 
                    //填充数据
                    //sheet.createRow(i)-- - 创建Excel中一行
                    //sheet.getRow(i)--读取Excel中一行
                    for (int i =  3; i <  12; i++)
                    {
                        row = sheet.GetRow(14 *p+i);
                        if (row != null)
                        {
                            for (int j = 3; j < row.LastCellNum - 2; j++)
                            {

                                cell = row.GetCell(j);
                                cell.SetCellValue(dataTable.Rows[14 * p + j - 3][i - 3].ToString()); //14为一页填充数据列数
                            }

                        }
                    }

                    //定义显示页码
                    int showpage = (p+1) * 2;
                    //标签页码
                    cell = sheet.CreateRow(14 * p + 12).CreateCell(0);
                    cell.SetCellValue(showpage.ToString());
                    ICellStyle cellstyle = workbook.CreateCellStyle();
                    //居中
                    cellstyle.VerticalAlignment = VerticalAlignment.Center;
                    cellstyle.Alignment = HorizontalAlignment.Center;
                    //文本自动换行
                    cellstyle.WrapText = true;
                    IFont font = workbook.CreateFont();
                    //字体尺寸
                    font.FontHeightInPoints = 11;
                    //字体粗细
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "宋体";
                    cellstyle.SetFont(font);
                    cell.CellStyle = cellstyle;
                    //合并单元格
                    sheet.AddMergedRegion(new CellRangeAddress(14*p+12, 14*p+13, 0, 18));
                    //复制单元格
                    CopyRows(workbook, sheet, 0, 12, 14*(p+1));
                }
                else
                {
                    for (int i = 3; i < 12; i++)
                    {
                        row = sheet.GetRow(14 * p + i);
                        if (row != null)
                        {
                            int end = dataTable.Rows.Count - p * 14;
                            for (int j = 3; j < row.LastCellNum - 2; j++)
                            {
                                if(j < 3+end)
                                {
                                    cell = row.GetCell(j);
                                    cell.SetCellValue(dataTable.Rows[14 * p + j - 3][i - 3].ToString()); //14为一页填充数据列数
                                }
                                else
                                {
                                    cell = row.GetCell(j);
                                    cell.SetCellValue(""); //14为一页填充数据列数
                                }

                            }

                        }
                    }

                    //定义显示页码
                    int showpage = (p + 1) * 2;
                    //标签页码
                    cell = sheet.CreateRow(14*p +12 ).CreateCell(0);
                    cell.SetCellValue(showpage.ToString());
                    ICellStyle cellstyle = workbook.CreateCellStyle();
                    //居中
                    cellstyle.VerticalAlignment = VerticalAlignment.Center;
                    cellstyle.Alignment = HorizontalAlignment.Center;
                    //文本自动换行
                    cellstyle.WrapText = true;
                    IFont font = workbook.CreateFont();
                    //字体尺寸
                    font.FontHeightInPoints = 11;
                    //字体粗细
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "宋体";
                    cellstyle.SetFont(font);
                    cell.CellStyle = cellstyle;
                    //合并单元格
                    sheet.AddMergedRegion(new CellRangeAddress(14 * p + 12, 14 * p + 13, 0, 18));
                }
            }




            //创建文件流
            using (FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(file);//将工作簿写入文件流
            }
        }

//同一sheet行复制方法

public static void CopyRow(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int destinationRowNum)
        {
            // Get the source / new row
            IRow newRow = worksheet.GetRow(destinationRowNum);
            IRow sourceRow = worksheet.GetRow(sourceRowNum);
            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1, true, false);
            }
            else
            {
                newRow = worksheet.CreateRow(destinationRowNum);
            }
            //设置行高
            newRow.Height = sourceRow.Height;
            //设置分隔行
            if (worksheet.IsRowBroken(sourceRowNum))
            {
                worksheet.SetRowBreak(destinationRowNum);
            }

            // 遍历一次旧行并添加到新行
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);
                ICell newCell = newRow.CreateCell(i);

                // 如果旧行为空则跳过
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }
                newCell.CellStyle = oldCell.CellStyle;

                // 复制批注
                if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

                // 复制超链
                if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

                // 复制单元格类型
                newCell.SetCellType(oldCell.CellType);

                // 设置单元格数据值
                switch (oldCell.CellType)
                {
                    case CellType.Blank://空值
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.Boolean:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.Error:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.Formula://公式
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case CellType.Numeric://数值
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.String://字符串
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown://未知
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }
        }

//同一sheet多行复制方法

        public static void CopyRows(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int copyRowsCount, int destinationRowNum)
        {
            int[,] sm = getAllMergedRegions(worksheet);
            List<int[]> copyAreaMergedRegions = new List<int[]>();
            for (int i = 0; i < sm.GetLength(0); i++)
            {
                if (sm[i, 0] >= sourceRowNum && sm[i, 0] < (sourceRowNum + copyRowsCount))
                {
                    copyAreaMergedRegions.Add(new int[4] { sm[i, 0] - sourceRowNum, sm[i, 1], sm[i, 2] - sourceRowNum, sm[i, 3] });
                }
            }
            int addpoint = destinationRowNum;
            for (int row = 0; row < copyRowsCount; row++)
            {
                //复制行
                CopyRow(workbook, worksheet, sourceRowNum + row, addpoint);
                addpoint++;
            }
            foreach (int[] cell in copyAreaMergedRegions)
            {
                NPOI.SS.Util.CellRangeAddress newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(
                    cell[0] + destinationRowNum, //顶
                    cell[2] + destinationRowNum, //底
                    cell[1], //左
                    cell[3]  //右
                    );
                worksheet.AddMergedRegion(newCellRangeAddress);
            }

//合并单元格

        public static int[,] getAllMergedRegions(ISheet worksheet)
        {
            int NumMergedRegions = worksheet.NumMergedRegions;
            int[,] output = new int[NumMergedRegions, 4];
            for (int i = 0; i < worksheet.NumMergedRegions; i++)//NumMergedRegions：整个sheet的合拼单元格数量
            {
                NPOI.SS.Util.CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);//获取合拼单元格的地址字符串
                output[i, 0] = cellRangeAddress.FirstRow;
                output[i, 1] = cellRangeAddress.FirstColumn;
                output[i, 2] = cellRangeAddress.LastRow;
                output[i, 3] = cellRangeAddress.LastColumn;
            }
            return output;
        }