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

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();
}