NPOI导出excel实例

1.Excel实体类，根据实际情况修改，我的需求中要求导出多个sheet页，含多级表头；

/// <summary>
    /// Excel表头实体类
    /// </summary>
    public class ExcelClass
    {
        /// <summary>
        /// 内容
        /// </summary>
        public DataTable Table { get; set; }
        /// <summary>
        /// 标题
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 标题自定义合并（上）
        /// </summary>
        public int TitleTop { get; set; }
        /// <summary>
        /// 标题自定义合并（下）
        /// </summary>
        public int TitleDown { get; set; }
        /// <summary>
        /// 标题自定义合并（左）
        /// </summary>
        public int TitleLeft { get; set; }
        /// <summary>
        /// 标题自定义合并（右）
        /// </summary>
        public int TitleRight { get; set; }
        /// <summary>
        /// 一级表头
        /// </summary>
        public string[] HeadOne { get; set; }
        /// <summary>
        /// 二级表头
        /// </summary>
        public string[] HeadTwo { get; set; }
        /// <summary>
        /// Sheet页名称
        /// </summary>
        public string SheetName { get; set; }
    }

 

2.每个ExcelClass代表一个sheet页

//集合代表整个excel，每个list代表一个sheet页
List<ExcelClass> list = new List<ExcelClass>();
            string headone = "";
            string headtwo= "";
            string sql = "";
// 从上起第几个，到上起第几个，从左起第几个，到左起第几个；
headone = "种类一:1, 2, 0, 0|种类二:1,1,1,3|种类三:1,1,4,9";
headtwo= " |一|二|三|四|五|六|七|八|";
sql = "你的sql"    
DataTable dt = new DataTable();
ExcelClass excel = new ExcelClass();
                excel.Table = dt;
                excel.Title = "Title";
//这四个数字代表Title合并情况，规则与上面提到的相同，不需要合并则都写0
                excel.TitleTop = 0;
                excel.TitleDown = 0;
                excel.TitleLeft = 0;
                excel.TitleRight = 9;
                excel.HeadOne = maincol.Split('|');
                excel.HeadTwo = detailcol.Split('|');
                excel.SheetName = "SheetName ";
                list.Add(excel);

3.

string fileName = "MyExcel.xls";
MemoryStream ms = DeriveExcel.ExportExcel(list, "xls");
            return File(ms, "application/vnd.ms-excel", fileName);

 

4.方法看情况自行封装

//导出excel
public static MemoryStream ExportExcel(List<ExcelClass> list, string excelType)
        {
            HSSFWorkbook workbook = null;
            if (excelType == "xls")
            {
                workbook = new HSSFWorkbook();//2003
            }
            else
            {
                //workbook = new XSSFWorkbook();//2007
            }
            HSSFPalette palette = workbook.GetCustomPalette();
            Color colorBord = Color.FromArgb(0, 0, 0);
            Color color = Color.FromArgb(197, 217, 241);
            short FIRST_COLOR_INDEX = (short)0x8;
            palette.SetColorAtIndex((short)(FIRST_COLOR_INDEX + 1), colorBord.R, colorBord.G, colorBord.B);//边框颜色
            palette.SetColorAtIndex((short)(FIRST_COLOR_INDEX), color.R, color.G, color.B);//标题背景色

            foreach (var excelClass in list)
            {
                #region 创建一个sheet
                ISheet sheet = workbook.CreateSheet(excelClass.SheetName);

                sheet.AddMergedRegion(new CellRangeAddress(excelClass.TitleTop, excelClass.TitleDown, excelClass.TitleLeft, excelClass.TitleRight));  // (从上起第几个，到上起第几个，从左起第几个，到左起第几个)

                IRow rowHead = sheet.CreateRow(0);
                rowHead.Height = 600;
                ICell cellHead = rowHead.CreateCell(0);
                cellHead.SetCellValue(excelClass.Title); //标题
                ICellStyle cellstyleHead = workbook.CreateCellStyle();//设置垂直居中格式
                cellstyleHead.VerticalAlignment = VerticalAlignment.Center;//垂直对齐(默认应该为Center，如果Center无效则用justify)
                cellstyleHead.Alignment = HorizontalAlignment.Center;//水平对齐
                cellHead.CellStyle = cellstyleHead;

                IFont fontHead = workbook.CreateFont();
                fontHead.FontHeightInPoints = 16;
                fontHead.Boldweight = short.MaxValue;
                cellstyleHead.SetFont(fontHead);
                //设置全局列宽和行高   
                sheet.DefaultColumnWidth = 14; //全局列宽   
                sheet.DefaultRowHeightInPoints = 15; //全局行高   


                //设置大标题行   
                int mainRowCount = 1;
                int detailRowCount = 2;
                if (excelClass.HeadTwo.Length <= 0) { detailRowCount = 1; };
                //设置标题行数据   
                IRow mainRow = sheet.CreateRow(mainRowCount); //创建报表表头标题列   
                IRow detailRow = sheet.CreateRow(detailRowCount);
                ICellStyle style = workbook.CreateCellStyle();
                IFont font = workbook.CreateFont();
                //font.Boldweight = short.MaxValue;
                style.SetFont(font);
                style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐(默认应该为Center，如果Center无效则用justify)
                style.Alignment = HorizontalAlignment.Center;//水平对齐
                var vBord = palette.FindColor(colorBord.R, colorBord.G, colorBord.B);
                style.BottomBorderColor = vBord.Indexed;
                style.TopBorderColor = vBord.Indexed;
                style.LeftBorderColor = vBord.Indexed;
                style.RightBorderColor = vBord.Indexed;
                for (int k = 0; k < excelClass.HeadTwo.Length; k++)
                {  //将传递过来的字符串表头进行拆分到Excel
                    string columnName = excelClass.HeadTwo[k];
                    ICell cell = detailRow.CreateCell(k);
                    ICell maincell = mainRow.CreateCell(k);
                    cell.SetCellValue(columnName);
                    cell.CellStyle = style;
                    maincell.CellStyle = style;
                }
                int nowColIndex = 0; //当前写到第几列
                for (int k = 0; k < excelClass.HeadOne.Length; k++)
                {  //将传递过来的字符串表头进行拆分到Excel
                    string[] Htitle = excelClass.HeadOne[k].Split(':');
                    if (Htitle.Length > 1) //需要合并单元格
                    {
                        string[] arr = Htitle[1].Split(',');
                        sheet.AddMergedRegion(new CellRangeAddress(Convert.ToInt32(arr[0]), Convert.ToInt32(arr[1]), Convert.ToInt32(arr[2]), Convert.ToInt32(arr[3])));
                        nowColIndex = Convert.ToInt32(arr[2]);
                    }
                    string columnName = Htitle[0];
                    ICell cell = mainRow.CreateCell(nowColIndex); //写到第几列
                    nowColIndex++;
                    cell.SetCellValue(columnName);//写入的内容
                    var v1 = palette.FindColor(color.R, color.G, color.B);
                    if (v1 == null)
                    {
                        throw new Exception("Color is not in Palette");
                    }
                    cell.CellStyle = style;
                }
                //IDataFormat dataformat = workbook.CreateDataFormat();
                style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");

                //填写ds数据进excel   
                for (int i = 0; i < excelClass.Table.Rows.Count; i++) //写行数据   
                {
                    IRow contentRow = sheet.CreateRow(i + detailRowCount + 1);
                    for (int j = 0; j < excelClass.Table.Columns.Count - 1; j++)
                    {
                        string dgvValue = string.Empty;
                        dgvValue = excelClass.Table.Rows[i][j].ToString();
                        ICell cell = contentRow.CreateCell(j);
                        double number;
                        if (double.TryParse(dgvValue,out number))
                        {
                            cell.SetCellValue(number);
                        }
                        else
                        {
                            cell.SetCellValue(dgvValue);
                        }
                        cell.CellStyle = style;
                    }
                }

                //列宽自适应，只对英文和数字有效
                for (int i = 0; i <= excelClass.Table.Rows.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
                //获取当前列的宽度，然后对比本列的长度，取最大值
                for (int columnNum = 0; columnNum <= excelClass.Table.Columns.Count; columnNum++)
                {
                    int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                    for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                    {
                        IRow currentRow;
                        //当前行未被使用过
                        if (sheet.GetRow(rowNum) == null)
                        {
                            currentRow = sheet.CreateRow(rowNum);
                        }
                        else
                        {
                            currentRow = sheet.GetRow(rowNum);
                        }

                        if (currentRow.GetCell(columnNum) != null)
                        {
                            ICell currentCell = currentRow.GetCell(columnNum);
                            int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                            if (columnWidth < length)
                            {
                                columnWidth = length;
                            }
                        }
                    }
                    sheet.SetColumnWidth(columnNum, (columnWidth+1) * 256);
                }

                #endregion
            }

            //创建excel   
            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            memoryStream.Seek(0, SeekOrigin.Begin);
            return memoryStream;
        }

 

前台：

使用window.open(url) 

url指向后台方法，将2 3步的代码放到这个方法中