首页 > 代码库 > 阶段总结 2014-05-22

阶段总结 2014-05-22

1:float保存2位小数:

    f1.ToString("F2")

2:datatable列求和:

dt.Compute("sum([企业数量])", "true")

同时:如果字段为中文,取值用[中文字段] 方式。

3:NPOI 导出EXCEL(需要手动导入NPOI文件)

单Sheet:

 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         }
View Code

多Sheet:

 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         }
View Code

4:导出Excel

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

Response.AddHeader("Content-Disposition", "attachment; filename="+System.Web.HttpUtility.UrlEncode("中文",System.Text.Encoding.UTF8)+".xls");

5:Oracle中sql语句 输出保留2位小数的操作:

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 总体扩展率
View Code