NOPI导出标准格式Excel

来源：互联网发布：历代职官沿革史淘宝编辑：程序博客网时间：2024/06/05 15:41

添加引用NOPI，官网下载地址NOPI下载地址选择合适的版本，根据你自己的项目的.net版本选择引用的dll有2.0和4.0的，vs2010以上可以选4.0的剩下的都选2.0的吧，引用一下NPOI.dll就行其他的，不用引用，如果用到nopi的其他功能自行百度引用

using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;

        private void Excel(DataTable dt, string FileName)        {            try            {                HSSFWorkbook workbook = new HSSFWorkbook();                ISheet sheet = workbook.CreateSheet("Sheet1");                ICellStyle HeadercellStyle = workbook.CreateCellStyle();                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.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                //字体                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();                headerfont.Boldweight = (short)FontBoldWeight.Bold;                HeadercellStyle.SetFont(headerfont);                //用column name 作为列名                int icolIndex = 0;                IRow headerRow = sheet.CreateRow(0);                foreach (DataColumn item in dt.Columns)                {                    ICell cell = headerRow.CreateCell(icolIndex);                    cell.SetCellValue(item.ColumnName);                    cell.CellStyle = HeadercellStyle;                    icolIndex++;                }                ICellStyle cellStyle = workbook.CreateCellStyle();                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");                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;                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();                cellfont.Boldweight = (short)FontBoldWeight.Normal;                cellStyle.SetFont(cellfont);                //建立内容行                int iRowIndex = 1;                int iCellIndex = 0;                foreach (DataRow Rowitem in dt.Rows)                {                    IRow DataRow = sheet.CreateRow(iRowIndex);                    foreach (DataColumn Colitem in dt.Columns)                    {                        ICell cell = DataRow.CreateCell(iCellIndex);                        cell.SetCellValue(Rowitem[Colitem].ToString());                        cell.CellStyle = cellStyle;                        iCellIndex++;                    }                    iCellIndex = 0;                    iRowIndex++;                }                //自适应列宽度                for (int i = 0; i < icolIndex; i++)                {                    sheet.AutoSizeColumn(i);                }                //写Excel                //FileStream file = new FileStream(FileName, FileMode.OpenOrCreate);                System.IO.MemoryStream file = new System.IO.MemoryStream();                workbook.Write(file);                Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(FileName) + ".xls");                Response.BinaryWrite(file.ToArray());                workbook = null;                file.Close();                file.Dispose();            }            catch (Exception ex)            {                Response.Write("<script>alert('" + ex.Message + "')</scrit>");            }        }

0 0