NPOI _导出exl（简单应用）

1. 导出exl表格，创建表格导出到客户端

  public static MemoryStream Export_Table<T>(List<T> datalist)
        {
            MemoryStream ms = new MemoryStream();
            var members = typeof(T).GetProperties();
            var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
            int order = 1;
            foreach (var meber in members)//初始化标题
            {
                string titlevalue = "";
                var name = meber.GetCustomAttributes(typeof(TableAttribulate), false);
                if (name.Length == 0)
                    continue;
                var pro = name[0] as TableAttribulate;
                if (pro == null)
                    continue;
                titlevalue = pro.CName;
                var cell = headerRow.CreateCell(order);
                cell.SetCellValue(titlevalue);
                if(pro.Weight==0)
                    sheet.AutoSizeColumn(order,true);
                else
                {
                    sheet.SetColumnWidth(order,pro.Weight);
                }
                cell.CellStyle = GetStyle(workbook);
                cell.CellStyle.Alignment = pro.HorizontalAlignment;
                var cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                cell.CellStyle.SetFont(cellfont);
                order++;
            }

            int rowIndex = 1;
            foreach (var row in datalist)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                int colIndex = 0;
                var cellindex = dataRow.CreateCell(colIndex);
                cellindex.SetCellValue(rowIndex);
                cellindex.CellStyle = GetStyle(workbook);
                var cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
                cellfont.Color = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
                cellindex.CellStyle.SetFont(cellfont);
                colIndex++;
                foreach (var meber in members)
                {
                    var name = meber.GetCustomAttributes(typeof(TableAttribulate), false);
                    if (name.Length == 0)
                        continue;
                    var pro = name[0] as TableAttribulate;
                    if (pro == null)
                        continue;
                    //if (pro.Weight == 0)   
                    //    sheet.AutoSizeColumn(colIndex, true); //设置成动态的，自动大小时就很慢，这里注掉，如果要用，想别的办法
                    var cell = dataRow.CreateCell(colIndex);
                    var mebervalue = meber.GetValue(row);
                    cell.SetCellValue(mebervalue == null ? "" : mebervalue.ToString());
                    cell.CellStyle = GetStyle(workbook);
                    cell.CellStyle.Alignment = pro.HorizontalAlignment;
                    colIndex++;
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;//
        }

2.代码总的特性

public class TableAttribulate:Attribute
        {
            public TableAttribulate(string name, int weight = 0, NPOI.SS.UserModel.HorizontalAlignment hoalign = NPOI.SS.UserModel.HorizontalAlignment.Center)
            {
                CName = name;
                Weight = weight;
                HorizontalAlignment = hoalign;
            }
            public string CName { get; set; }

            public int Weight { get; set; }

            public NPOI.SS.UserModel.HorizontalAlignment HorizontalAlignment { get; set; }//对齐方式

        }

 3.函数样式

        public static NPOI.SS.UserModel.ICellStyle GetStyle(NPOI.HSSF.UserModel.HSSFWorkbook workbook)
        {
            var cs = workbook.CreateCellStyle();
            cs.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("@");
            cs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cs.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            
            var cellfont = workbook.CreateFont();
            cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;

            cs.SetFont(cellfont);
            return cs;
        }

老版的：

       public static NPOI.SS.UserModel.ICellStyle GetDefaultStyle(XSSFWorkbook workbook)
        {
            var cs = workbook.CreateCellStyle();
            cs.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("@");
            cs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cs.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cs.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            var cellfont = workbook.CreateFont();
            cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
            cs.SetFont(cellfont);
            return cs;
        }

4.js调用实例

        function func_queryExportExl() {
            var url = '@Url.Action("ExportExcel", "ResidentsSigned");
            window.location.href = url;
        }

 5.后端调用

List<TableExel> query = dbContext.Database.SqlQuery<TableExel>(sql).ToList();
var ms = CPSYS.Web.Common.FileOption.Export_Table<TableExel>(query);
return File(ms, "application/vnd.ms-excel", "text.xls");

 6.合并单元格后的边框格式问题

  public void SetTyleThin(NPOI.HSSF.UserModel.HSSFWorkbook workbook,NPOI.SS.UserModel.ISheet sheet,int lastrow)
        {
            NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(4, lastrow, 0, 23); 
            ((NPOI.HSSF.UserModel.HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
        }

 7.读入模板

            HSSFWorkbook workbook = null;
            var path = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTemplate/重点孕妇登记随访本.xls";
            FileStream filest = new FileStream(path, FileMode.Open, FileAccess.Read);

            workbook = new HSSFWorkbook(filest);
            filest.Close();

            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet("重点孕妇登记随访本");