记录NPOI使用方法

DLL 下载地址：https://files.cnblogs.com/files/xujunbao/NPOI.rar

using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

 

protected void btn_export_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt = null;   //这里为导出的数据源
    string path = Server.MapPath("~/FileUpload/Excel/导出" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
    TableToExcel2(dt, path);

    //进行后台文件下载
    FileInfo fileInfo = new FileInfo(path);
    Response.Clear();
    Response.ClearContent();
    Response.ClearHeaders();
    Response.AddHeader("Content-Disposition", "attachment;filename=报价表" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
    Response.AddHeader("Content-Length", fileInfo.Length.ToString());
    Response.AddHeader("Content-Transfer-Encoding", "binary");
    Response.ContentType = "application/octet-stream";
    Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
    Response.WriteFile(fileInfo.FullName);
    Response.Flush();
    Response.End();
}

public static void TableToExcel2(DataTable dt, string file)
{
    IWorkbook workbook;
    string fileExt = Path.GetExtension(file).ToLower();
    if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
    if (workbook == null) { return; }
    ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
    int Excel_row = 0;
    int Excel_col = 0;
    BLL.jk_activatedTestingItem_base bll = new BLL.jk_activatedTestingItem_base();
    DataTable dt_item = new DataTable();
    ICellStyle style = workbook.CreateCellStyle();
    //设置单元格的样式：水平对齐居中
    style.Alignment = HorizontalAlignment.Center;
    //垂直居中
    style.VerticalAlignment = VerticalAlignment.Center;
    //新建一个字体样式对象
    IFont font = workbook.CreateFont();
    //设置字体加粗样式
    font.Boldweight = short.MaxValue;
    //使用SetFont方法将字体样式添加到单元格样式中 
    style.SetFont(font);
    //数据  
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        long activateId = TypeConverter.ObjectToLong(dt.Rows[i]["activateId"]);
        dt_item = bll.GetList("activateId=" + activateId + " and testingItemLevel=1").Tables[0];
        if (dt_item == null || dt_item.Rows.Count == 0)
        {
            continue;
        }
        IRow row1 = sheet.CreateRow(Excel_row);
        //表头  
        if (i == 0)
        {
            row1 = sheet.CreateRow(Excel_row);
            ICell cell = row1.CreateCell(0);
            cell.SetCellValue("产品大类");
            sheet.SetColumnWidth(0, 25 * 256);
            cell.CellStyle = style;   //绑定样式到单元格上
            cell = row1.CreateCell(1);
            cell.SetCellValue("检测对象");
            sheet.SetColumnWidth(1, 30 * 256);
            cell.CellStyle = style;
            cell = row1.CreateCell(2);
            cell.SetCellValue("检测标准");
            sheet.SetColumnWidth(2, 60 * 256);
            cell.CellStyle = style;
            cell = row1.CreateCell(3);
            cell.SetCellValue("收样说明");
            sheet.SetColumnWidth(0, 20 * 256);
            cell.CellStyle = style;
        }
        string value = "";
        for (int j = 0; j < dt_item.Rows.Count; j++)
        {
            if (j == 0 && i == 0)
            {
                ICell cell = row1.CreateCell(4);
                cell.SetCellValue("检测项目");
                sheet.SetColumnWidth(4, 50 * 256);
                cell.CellStyle = style;
                cell = row1.CreateCell(5);
                cell.SetCellValue("检测周期");
                cell.CellStyle = style;
                cell = row1.CreateCell(6);
                cell.SetCellValue("检测费用");
                cell.CellStyle = style;
                cell = row1.CreateCell(7);
                cell.SetCellValue("检测部门");
                sheet.SetColumnWidth(7, 25 * 256);
                cell.CellStyle = style;
                Excel_row++;
            }
            row1 = sheet.CreateRow(Excel_row);
            string deptName = TypeConverter.ObjectToString(dt.Rows[i]["parameterDeptName"]);
            for (int ij = 0; ij < dt.Columns.Count; ij++)
            {
                string ColumnName = dt.Columns[ij].ColumnName;
                switch (ColumnName)
                {
                    case "productTypeName":
                        value = TypeConverter.ObjectToString(dt.Rows[i]["productTypeName"]);
                        ICell cell0 = row1.CreateCell(0);
                        cell0.SetCellValue(value);
                        cell0.CellStyle = style;
                        break;
                    case "testingObject":
                        value = TypeConverter.ObjectToString(dt.Rows[i]["testingObject"]);
                        ICell cell1 = row1.CreateCell(1);
                        cell1.SetCellValue(value);
                        cell1.CellStyle = style;
                        break;
                    case "testingbasisId":
                        value = TypeConverter.ObjectToString(dt.Rows[i]["testingbasisCode"]) + TypeConverter.ObjectToString(dt.Rows[i]["testingbasisChiName"]) + TypeConverter.ObjectToString(dt.Rows[i]["parameterComment"]);
                        ICell cell2 = row1.CreateCell(2);
                        cell2.SetCellValue(value);
                        cell2.CellStyle = style;
                        break;
                    case "remark":
                        value = TypeConverter.ObjectToString(dt.Rows[i]["remark"]);
                        ICell cell3 = row1.CreateCell(3);
                        cell3.SetCellValue(value);
                        cell3.CellStyle = style;
                        break;
                    default:
                        value = "";
                        break;
                }
            }
            for (int jj = 0; jj < dt_item.Columns.Count; jj++)
            {
                string ColumnName = dt_item.Columns[jj].ColumnName;
                switch (ColumnName)
                {
                    case "testingItemName":
                        value = TypeConverter.ObjectToString(dt_item.Rows[j]["testingItemName"]);
                        ICell cell4 = row1.CreateCell(4);
                        cell4.SetCellValue(value);
                        break;
                    case "testingTotalDay":
                        value = TypeConverter.ObjectToString(dt_item.Rows[j]["testingTotalDay"]);
                        ICell cell5 = row1.CreateCell(5);
                        cell5.SetCellValue(value);
                        break;
                    case "testingFee":
                        value = TypeConverter.ObjectToString(dt_item.Rows[j]["testingFee"]);
                        ICell cell6 = row1.CreateCell(6);
                        cell6.SetCellValue(value);
                        break;
                    default:
                        value = "";
                        break;
                }
            }
            ICell cell7 = row1.CreateCell(7);
            cell7.SetCellValue(deptName);
            Excel_row++;
        }
        sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - 1, 0, 0));  //跨行 ：开始行，结束行，开始列，结束列
        sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - 1, 1, 1));
        sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - 1, 2, 2));
        sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - 1, 3, 3));
    }

    //转为字节数组  
    MemoryStream stream = new MemoryStream();
    workbook.Write(stream);
    var buf = stream.ToArray();

    //保存为Excel文件  
    using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
    {
        fs.Write(buf, 0, buf.Length);
        fs.Flush();
    }
}