NPOI导出Excel

安装npoi nuget包，在设置列宽时，不使用自动设置AutoSizeColumn，这个设了也未必准且有性能问题。

设置单元格的自定义格式，可以参考excel。

合并列代码片段：合并列的单元格格式每个都要处理。

 // 建立合并列   
            iRowIndex = iRowIndex + 2;
            IRow mergefundRow = sheet.CreateRow(iRowIndex);
            ICell mergefundCell = mergefundRow.CreateCell(0);
            mergefundCell.CellStyle = cellStyle;
            mergefundCell.SetCellValue("基金信息");
            var region2 = new CellRangeAddress(iRowIndex, iRowIndex, 0, 11);
            sheet.AddMergedRegion(region2);
            for (int i = region2.FirstRow; i <= region2.LastRow; i++)
            {
                IRow row = HSSFCellUtil.GetRow(i, (HSSFSheet)sheet);
                for (int j = region2.FirstColumn; j <= region2.LastColumn; j++)
                {
                    ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
                    singleCell.CellStyle = cellStyle;
                }
            }

View Code

//第一列的合并(纵向合并行)
            for (int i = 0; i < table.Rows.Count;)
            {
                var initRowIndex = i;
                var fund = table.Rows[initRowIndex][0].ToStringEx();
                int lastRowIndex = table.AsEnumerable().Where(r => r[0].ToStringEx() == fund).Max(x => Convert.ToInt32(x["index"]));
                i = lastRowIndex + 1;

                if (initRowIndex < lastRowIndex)  //保证多个连续单元格合并
                {
                    IRow mergeRow = sheet.GetRow(initRowIndex + 1); //行已经在前面创建了，so......
                    ICell mergeCell = mergeRow.CreateCell(0);
                    mergeCell.CellStyle = cellStyle;                    
                    mergeCell.SetCellValue(fund);
                    var region2 = new CellRangeAddress(initRowIndex + 1, lastRowIndex + 1, 0, 0);  //索引0列合并
                    sheet.AddMergedRegion(region2);
                }
                else
                {
                    //不合并的情况也填充值
                    var row = sheet.GetRow(initRowIndex + 1);
                    var cell = row.CreateCell(0);//索引0列填值
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue(fund);
                }

                //这段代码不一定要加，为解决最后合并的单元格的底线不加粗
                if (lastRowIndex == (table.Rows.Count - 1))
                {
                    var tempRow = sheet.CreateRow(lastRowIndex + 2);
                    var tempCell = tempRow.CreateCell(0);
                    tempCell.CellStyle = workbook.CreateCellStyle();
                    tempCell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                }
            }

View Code

下面是完整的导出excel代码，不包含合并列：

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

namespace ConsoleApp1
{
    internal class Program
    {
        public static void Main()
        {
            DataTable table = new DataTable();
            table.Columns.Add("客户");
            table.Columns.Add("XX份额");
            table.Columns.Add("XX占比");
            table.Rows.Add("科比","8000000000000", "0.9");
            table.Rows.Add("科比2","8000000000000.94", "0.7");
            table.Rows.Add("科比3","8000000000000.886", "0.5");

            IWorkbook workbook = new HSSFWorkbook();
            string fileName = @"C:\Users\s-huangsb\Desktop\xxx.xls";
            ExportExcel(table, fileName, workbook);
            try
            {
                using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                    file.Flush();
                    file.Close();
                }
            }
            catch (Exception ex)
            {
                //handle exception
            }
        }

        private static void ExportExcel(DataTable table, string fileName, IWorkbook workbook)
        {

            ISheet sheet = workbook.CreateSheet("客户信息");
            ICellStyle headercellStyle = GetHeaderStyle(workbook);

            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.IsBold = false;
            cellfont.FontName = "宋体";
            cellfont.FontHeightInPoints = 11;

            ICellStyle cellStyle = GetCellStyle(workbook);
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            cellStyle.SetFont(cellfont);

            ICellStyle numCellStyle = GetCellStyle(workbook);
            numCellStyle.SetFont(cellfont);
            numCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            numCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");

            ICellStyle ratioCellStyle = GetCellStyle(workbook);
            ratioCellStyle.SetFont(cellfont);
            ratioCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            ratioCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            int iRowIndex = 0;
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(iRowIndex);
            foreach (DataColumn item in table.Columns)
            {
                ICell cell = headerRow.CreateCell(icolIndex);
                cell.SetCellValue(item.ColumnName);
                cell.CellStyle = headercellStyle;
                icolIndex++;
            }
            iRowIndex++;

            int iCellIndex = 0;
            foreach (DataRow row in table.Rows)
            {
                IRow DataRow = sheet.CreateRow(iRowIndex);
                foreach (DataColumn colItem in table.Columns)
                {
                    ICell cell = DataRow.CreateCell(iCellIndex);
                    if (colItem.ColumnName.Contains("份额"))
                    {
                        cell.SetCellValue(ToDoubleEx(row[colItem]));
                        cell.CellStyle = numCellStyle;
                    }
                    else if (colItem.ColumnName.Contains("占比"))
                    {
                        cell.SetCellValue(Convert.ToDouble(row[colItem]));
                        cell.CellStyle = ratioCellStyle;
                    }
                    else
                    {
                        cell.SetCellValue(row[colItem].ToString());
                        cell.CellStyle = cellStyle;
                    }
                    iCellIndex++;
                }
                iCellIndex = 0;
                iRowIndex++;
            }

　　　　　　List<int> colsLength = new List<int>();
　　　　　　foreach (DataColumn column in table.Columns)
　　　　　　{
　　　　　　　var length = table.AsEnumerable().Max(row => row[column].ToString().Length);
　　　　　　　var columnLength = column.ColumnName.Length;
　　　　　　　length = length > columnLength ? length : columnLength;
　　　　　　　colsLength.Add(length);
　　　　　　}

　　　　　　AutoColumnWidth(sheet, table.Columns.Count, colsLength.ToArray(), 9);
}

private static void AutoColumnWidth(ISheet sheet, int cols, int[] colLength, int addlength)
{
for (int col = 0; col < cols; col++)
{
var columnWidth = colLength[col] * 256 + addlength * 256;
sheet.SetColumnWidth(col, columnWidth);
}
}

private static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            return cellStyle;
        }

        private static ICellStyle GetHeaderStyle(IWorkbook workbook)
        {
            ICellStyle headercellStyle = workbook.CreateCellStyle();
            headercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            headercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            
            headercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            headercellStyle.FillPattern = FillPattern.SolidForeground;
            NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
            headerfont.IsBold = true;
            headerfont.FontName = "宋体";
            headerfont.FontHeightInPoints = 11;
            headercellStyle.SetFont(headerfont);

            return headercellStyle;
        }

        private static double ToDoubleEx(object obj)
        {
            if (obj == DBNull.Value)
            {
                return 0;
            }
            string str = obj.ToString();
            if (str == null || str.Trim() == string.Empty)
            {
                return 0;
            }
            else
            {
                return Convert.ToDouble(str);
            }
        }
    }
}

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

namespace ConsoleApp1
{
    internal class Program
    {
        public static void Main()
        {
            DataTable table = new DataTable();
            table.Columns.Add("客户");
            table.Columns.Add("XX份额");
            table.Columns.Add("XX占比");
            table.Rows.Add("科比","8000000000000", "0.9");
            table.Rows.Add("科比2","8000000000000.94", "0.7");
            table.Rows.Add("科比3","8000000000000.886", "0.5");

            IWorkbook workbook = new HSSFWorkbook();
            string fileName = @"C:\Users\s-huangsb\Desktop\xxx.xls";
            ExportExcel(table, fileName, workbook);
            try
            {
                using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                    file.Flush();
                    file.Close();
                }
            }
            catch (Exception ex)
            {
                //handle exception
            }
        }

        private static void ExportExcel(DataTable table, string fileName, IWorkbook workbook)
        {

            ISheet sheet = workbook.CreateSheet("客户信息");
            ICellStyle headercellStyle = GetHeaderStyle(workbook);

            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.IsBold = false;
            cellfont.FontName = "宋体";
            cellfont.FontHeightInPoints = 11;

            ICellStyle cellStyle = GetCellStyle(workbook);
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            cellStyle.SetFont(cellfont);

            ICellStyle numCellStyle = GetCellStyle(workbook);
            numCellStyle.SetFont(cellfont);
            numCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            numCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");

            ICellStyle ratioCellStyle = GetCellStyle(workbook);
            ratioCellStyle.SetFont(cellfont);
            ratioCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            ratioCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            int iRowIndex = 0;
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(iRowIndex);
            foreach (DataColumn item in table.Columns)
            {
                ICell cell = headerRow.CreateCell(icolIndex);
                cell.SetCellValue(item.ColumnName);
                cell.CellStyle = headercellStyle;
                icolIndex++;
            }
            iRowIndex++;

            int iCellIndex = 0;
            foreach (DataRow row in table.Rows)
            {
                IRow DataRow = sheet.CreateRow(iRowIndex);
                foreach (DataColumn colItem in table.Columns)
                {
                    ICell cell = DataRow.CreateCell(iCellIndex);
                    if (colItem.ColumnName.Contains("份额"))
                    {
                        cell.SetCellValue(ToDoubleEx(row[colItem]));
                        cell.CellStyle = numCellStyle;
                    }
                    else if (colItem.ColumnName.Contains("占比"))
                    {
                        cell.SetCellValue(Convert.ToDouble(row[colItem]));
                        cell.CellStyle = ratioCellStyle;
                    }
                    else
                    {
                        cell.SetCellValue(row[colItem].ToString());
                        cell.CellStyle = cellStyle;
                    }
                    iCellIndex++;
                }
                iCellIndex = 0;
                iRowIndex++;
            }
　　　　　　　　List<int> colsLength = new List<int>();
　　　　　　　　foreach (DataColumn column in table.Columns)
　　　　　　　　{
　　　　　　　　　　var length = table.AsEnumerable().Max(row => row[column].ToString().Length);
　　　　　　　　　　var columnLength = column.ColumnName.Length;
　　　　　　　　　　length = length > columnLength ? length : columnLength;
　　　　　　　　　　colsLength.Add(length);
　　　　　　　　}

　　　　AutoColumnWidth(sheet, table.Columns.Count, colsLength.ToArray(), 9);
　　}

　　　　　　private static void AutoColumnWidth(ISheet sheet, int cols, int[] colLength, int addlength)
　　　　　　{
　　　　　　　　for (int col = 0; col < cols; col++)
　　　　　　　　{
　　　　　　　　　　var columnWidth = colLength[col] * 256 + addlength * 256;
　　　　　　　　　　sheet.SetColumnWidth(col, columnWidth);
　　　　　　　　}
　　　　　　}

　　　　private static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            return cellStyle;
        }

        private static ICellStyle GetHeaderStyle(IWorkbook workbook)
        {
            ICellStyle headercellStyle = workbook.CreateCellStyle();
            headercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            headercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            
            headercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            headercellStyle.FillPattern = FillPattern.SolidForeground;
            NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
            headerfont.IsBold = true;
            headerfont.FontName = "宋体";
            headerfont.FontHeightInPoints = 11;
            headercellStyle.SetFont(headerfont);

            return headercellStyle;
        }

        private static double ToDoubleEx(object obj)
        {
            if (obj == DBNull.Value)
            {
                return 0;
            }
            string str = obj.ToString();
            if (str == null || str.Trim() == string.Empty)
            {
                return 0;
            }
            else
            {
                return Convert.ToDouble(str);
            }
        }
    }
}

posted @ 2020-06-19 14:38 舒碧阅读(862) 评论(0) 编辑收藏举报

刷新页面返回顶部

NPOI导出Excel

公告