C#使用NPOI框架 Excel导出

数据导出效果图

    /// <summary>
    /// 数据导出
    /// </summary>
    public void NiuniuTabulationExcelDownload()
    {
        var workbookTitle = "牛牛币数据列表";//excel表名称
        string serviceAddress = $"{HttpUrl}/NiuniuCoins/GetNiuniuCoinsRecordList";//获取数据接口
        var Ilist = new List<int>() { 2500, 3500, 5000, 6000, 8500, 3500, 3500, 4500, 7000, 4500, 7000 };//列宽
        var fieldName = new List<string> { "序号", "姓名", "账号", "部门","内容","牛牛币","余额","创建人","创建时间","更新人","更新时间" };//列表字段名
        var Cell = new CellRangeAddress(0, 0, 0, 10);//单元格合并
        WriteExcel<NiuniuCoinsRecordListDTO>(workbookTitle, serviceAddress, Ilist, fieldName, Cell);
    }
    /// <summary>
    /// 标题样式
    /// </summary>
    /// <returns></returns>
    public ICellStyle TitleStyle(IWorkbook workbook)
    {
        //创建单元格 第0列 
        var titleStyle = workbook.CreateCellStyle();

        titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 居中
        titleStyle.VerticalAlignment = VerticalAlignment.Center;// 垂直居中
        titleStyle.FillBackgroundColor = IndexedColors.Orange.Index;// 设置颜色

        //字体设置
        var font = workbook.CreateFont();
        font.FontHeightInPoints = 18;
        font.IsBold = true;//加粗
        font.FontName = "微软雅黑";
        font.Color = IndexedColors.Blue.Index;
        titleStyle.SetFont(font);

        return titleStyle;
    }

    /// <summary>
    /// 字段名样式
    /// </summary>
    /// <returns></returns>
    public ICellStyle twoTitleStyle(IWorkbook workbook)
    {
        //创建单元格 
        var titlewroStyle = workbook.CreateCellStyle();

        titlewroStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 居中
        titlewroStyle.VerticalAlignment = VerticalAlignment.Center;// 垂直居中
        titlewroStyle.FillBackgroundColor = IndexedColors.Red.Index;// 设置颜色

        //字体设置
        var fontwto = workbook.CreateFont();
        fontwto.FontHeightInPoints = 14;
        fontwto.IsBold = true;//加粗
        fontwto.FontName = "微软雅黑";
        fontwto.Color = IndexedColors.Green.Index;
        titlewroStyle.SetFont(fontwto);

        return titlewroStyle;
    }
    
      /// <summary>
    /// NPOI导出
    /// </summary>
    /// <typeparam name="T">实体</typeparam>
    /// <param name="workbookTitle">excel表名称</param>
    /// <param name="serviceAddress">获取数据接口</param>
    /// <param name="Ilist">列宽</param>
    /// <param name="fieldName">列表字段名</param>
    /// <param name="Cell">单元格合并</param>
    public void WriteExcel<T>(string workbookTitle,string serviceAddress,List<int> Ilist,List<string> fieldName, CellRangeAddress Cell) where T :class
    {
        
        //创建工作簿
        IWorkbook workbook = new XSSFWorkbook();
        //创建工作表
        ISheet sheet1 = workbook.CreateSheet(workbookTitle);
        var rowIndex = 0;
        //合并单元格
        sheet1.AddMergedRegion(Cell);
        //创建行
        IRow row = sheet1.CreateRow(rowIndex);
        //设置列宽
        for (int i = 0; i < Ilist.Count; i++)
        {
            sheet1.SetColumnWidth(i,Ilist[i]);
        }
        //标题
        row.CreateCell(0).SetCellValue(workbookTitle);
        row.GetCell(0).CellStyle = TitleStyle(workbook);
        rowIndex++;
        row = sheet1.CreateRow(rowIndex);
      
        for (int i = 0; i < fieldName.Count; i++)
        {
            row.CreateCell(i).SetCellValue(fieldName[i]);
            row.GetCell(i).CellStyle = twoTitleStyle(workbook);
        }
        rowIndex++;
      
        ///获取数据
        var DataList = JsonConvert.DeserializeObject<List<T>>(HttpGet(serviceAddress).data);

        var rowStyle = workbook.CreateCellStyle();
        rowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 居中
        rowStyle.VerticalAlignment= VerticalAlignment.Center;   //设置居中

        for (int i = 0; i < DataList.Count; i++)
        {
            row = sheet1.CreateRow(rowIndex);
            row.CreateCell(0).SetCellValue(i + 1);
            row.GetCell(0).CellStyle = rowStyle;
            var Date = DataList[i].GetType().GetProperties();
            for (int j = 1; j < fieldName.Count; j++)
            {
                row.CreateCell(j).SetCellValue(Date[j].GetValue(DataList[i], null)==null?"": Date[j].GetValue(DataList[i], null).ToString());
                row.GetCell(j).CellStyle = rowStyle;
            }
            rowIndex++;
        }
        var FileName = "c:\\ExcelFile\\" + Guid.NewGuid().ToString()+ ".xls";
        // 判断目标目录是否存在如果不存在则新建之
        if (!Directory.Exists("c:\\ExcelFile)"))
            Directory.CreateDirectory("c:\\ExcelFile");
        FileStream fs = new FileStream(FileName, FileMode.OpenOrCreate,
                                       FileAccess.ReadWrite);
        workbook.Write(fs);

        System.IO.FileInfo file = new System.IO.FileInfo(FileName);
        Response.Clear(); //清除原有会话
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF8;

        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
        //Response.AddHeader("content-disposition", "attachment;filename=" + file.Name);
        //如果为火狐浏览器
        if (Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") == -1)
        {
            Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(workbookTitle+".xls"));
        }
        else
        {
            Response.AddHeader("content-disposition", "attachment;filename=" + workbookTitle + ".xls");
        }
        // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
        Response.AddHeader("Content-Length", file.Length.ToString());

        // 指定返回的是一个不能被客户端读取的流，必须被下载 
        Response.ContentType = "application/ms-excel";

        // 把文件流发送到客户端 
        Response.WriteFile(file.FullName);
        // 停止页面的执行 
        Response.End();

    }