阶段总结 2014-05-22

 1 //单Sheet页导出
 2         public static Stream RenderDataTableToExcel(DataTable SourceTable)
 3         {
 4             HSSFWorkbook workbook = new HSSFWorkbook();
 5             MemoryStream ms = new MemoryStream();
 6             HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
 7             HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
 8             foreach (DataColumn column in SourceTable.Columns)
 9                 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
10             int rowIndex = 1;
11             foreach (DataRow row in SourceTable.Rows)
12             {
13                 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
14                 foreach (DataColumn column in SourceTable.Columns)
15                 {
16                     dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
17                 }
18                 rowIndex++;
19             }
20 
21             workbook.Write(ms);
22             ms.Flush();
23             ms.Position = 0;
24             sheet = null;
25             headerRow = null;
26             workbook = null;
27             return ms;
28         }

 1 private Stream RenderDataTableToExcel(DataTable[] dtSources)
 2         {
 3             HSSFWorkbook workbook = new HSSFWorkbook();
 4             MemoryStream ms = new MemoryStream();
 5             for (int k = 0; k < dtSources.Length; k++)
 6             {
 7                 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(dtSources[k].TableName.ToString());
 8 
 9                 //填充表头
10                 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
11                 foreach (DataColumn column in dtSources[k].Columns)
12                 {
13                     dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
14                 }
15 
16                 //填充内容
17                 for (int i = 0; i < dtSources[k].Rows.Count; i++)
18                 {
19                     dataRow = (HSSFRow)sheet.CreateRow(i + 1);
20                     for (int j = 0; j < dtSources[k].Columns.Count; j++)
21                     {
22                         dataRow.CreateCell(j).SetCellValue(dtSources[k].Rows[i][j].ToString());
23                     }
24                 }
25             }
26 
27             workbook.Write(ms);
28             ms.Flush();
29             ms.Position = 0;
30             return ms;
31         }

1 Response.AddHeader("Content-Disposition", string.Format("attachment;filename="+fileName+".xls"));
2 Response.BinaryWrite(ms.ToArray());
3 ms.Close();
4 ms.Dispose();

1 CASE NVL(SUM(ES_ElmTotal),0) when 0 then ‘0.00%‘ else  round(NVL(SUM(ES_ExtElmTotalNum),0)*100/NVL(SUM(ES_ElmTotal),0),2)||‘%‘ end  AS 总体扩展率