NPOI 将多个DataTable数据导入到excel中,并输出到浏览器下载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
/// <summary>
/// 将多个DataTable数据导入到excel中
/// </summary>
/// <param name="dts">要导入的数据集合</param>
/// <param name="strExcelFileName">定义Excel文件名</param>
/// <param name="indexType">给个 1 就行</param>
public static bool DataTableToExcels(List<DataTable> dts, string strExcelFileName, int indexType)
{
    bool BSave = false;
    try
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        DataSet set = new DataSet();
        ICellStyle HeadercellStyle = workbook.CreateCellStyle();
        foreach (DataTable dt in dts)
        {
            ISheet sheet = workbook.CreateSheet(dt.TableName);
 
 
            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("¥#,##0.00");
            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;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.Boldweight = (short)FontBoldWeight.Normal;
            cellStyle.SetFont(cellfont);
            if (indexType == 1)
            {
                //建立内容行
                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);
                }
            }
            if (dt.TableName == "工程款统计表")
            {
                sheet.ShiftRows(0, sheet.LastRowNum, 1);
                var newrow = sheet.CreateRow(0);
                newrow.CreateCell(1).SetCellValue("工程来款统计");
                var cell1 = sheet.GetRow(0).GetCell(1);
                cell1.CellStyle = HeadercellStyle;
                newrow.CreateCell(11).SetCellValue("内包结算");
                var cell2 = sheet.GetRow(0).GetCell(11);
                cell2.CellStyle = HeadercellStyle;
                newrow.CreateCell(17).SetCellValue("付款");
                var cell3 = sheet.GetRow(0).GetCell(17);
                cell3.CellStyle = HeadercellStyle;
                newrow.CreateCell(18).SetCellValue("剩余可用资金");
                var cell4 = sheet.GetRow(0).GetCell(18);
                cell4.CellStyle = HeadercellStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 10));//起始行,结束行,起始列,结束列
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 18, 21));//起始行,结束行,起始列,结束列
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 11, 16));//起始行,结束行,起始列,结束列
            }
            if (dt.TableName == "项目经理来款和其他来款统计表")
            {
                sheet.ShiftRows(0, sheet.LastRowNum, 1);
                var newrow = sheet.CreateRow(0);
                newrow.CreateCell(1).SetCellValue("项目经理来款统计");
                var cell1 = sheet.GetRow(0).GetCell(1);
                cell1.CellStyle = HeadercellStyle;
                newrow.CreateCell(7).SetCellValue("其他来款统计");
                var cell2 = sheet.GetRow(0).GetCell(7);
                cell2.CellStyle = HeadercellStyle;
                
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 6));//起始行,结束行,起始列,结束列
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 7, 10));//起始行,结束行,起始列,结束列 
            }
        }
 
        //设置导出文件路径
        string path = HttpContext.Current.Server.MapPath("Export/");
        //设置新建文件路径及名称
        //string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";
        string savePath = new BaseManage.Attach.UploadFile().getExcelPath() + strExcelFileName + DateTime.Now.ToString("yyyy_MM_dd_HH_mm") + ".xls";
 
        FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
        workbook.Write(file);
 
        //创建一个 IO 流
        MemoryStream ms = new MemoryStream();
        //写入到流
        workbook.Write(ms);
        //转换为字节数组
        byte[] bytes = ms.ToArray();
        file.Write(bytes, 0, bytes.Length);
        file.Flush();
        //还可以调用下面的方法,把流输出到浏览器下载
        OutputClient(bytes, strExcelFileName);
        //释放资源
        bytes = null;
        ms.Close();
        ms.Dispose();
        file.Close();
        file.Dispose();
        File.Delete(savePath);
 
        workbook.Close();
        // sheet = null;
        workbook = null;
 
 
        file.Flush();
        file.Close();
        BSave = true;
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    return BSave;
}
/// <summary>
/// 流输出到浏览器下载
/// </summary>
/// <param name="bytes"></param>
public static void OutputClient(byte[] bytes, string name)
{
    HttpResponse response = HttpContext.Current.Response;
 
    response.Buffer = true;
 
    response.Clear();
    response.ClearHeaders();
    response.ClearContent();
 
    response.ContentType = "application/vnd.ms-excel";
    response.AddHeader("Content-Length", bytes.Length.ToString());
    response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", name + DateTime.Now.ToString("yyyyMMddHHmm")));
 
    response.Charset = "GB2312";
    response.ContentEncoding = Encoding.GetEncoding("GB2312");
 
    response.BinaryWrite(bytes);
    response.Flush();
 
    response.Close();
}

  

posted @   夕阳炒饭的颜色  阅读(168)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 25岁的心里话
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示