NPOI操作

1.操作Excel

　　准备生成的公共方法(将数据源DataTable转换成MemoryStream)

 1         /// <summary>
 2         /// 生成Excel
 3         /// </summary>
 4         /// <param name="table">DataTable</param>
 5         /// <returns>MemoryStream</returns>
 6         public static MemoryStream BuildToExcel(DataTable table)
 7         {
 8             MemoryStream ms = new MemoryStream();
 9 
10             using (table)
11             {
12                 using (IWorkbook workbook = new HSSFWorkbook())
13                 {
14                     using (ISheet sheet = workbook.CreateSheet())
15                     {
16                         IRow headerRow = sheet.CreateRow(0);
17 
18                         // handling header.
19                         foreach (DataColumn column in table.Columns)
20                         {
21                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
22                         }
23 
24                         // handling value.
25                         int rowIndex = 1;
26 
27                         foreach (DataRow row in table.Rows)
28                         {
29                             IRow dataRow = sheet.CreateRow(rowIndex);
30 
31                             foreach (DataColumn column in table.Columns)
32                             {
33                                 dataRow.CreateCell(column.Ordinal, CellType.STRING).SetCellValue(row[column].ToString());
34                             }
35 
36                             rowIndex++;
37                         }
38 
39                         AutoSizeColumns(sheet);
40                         workbook.Write(ms);
41                         ms.Flush();
42                         ms.Position = 0;
43                     }
44                 }
45             }
46 
47             return ms;
48         }

View Code

MVC导出

 1         /// <summary>
 2         /// 下载excel文件
 3         /// </summary>
 4         /// <param name="fileName">文件名称</param>
 5         /// <param name="dataTable">数据源</param>
 6         /// <returns>FileResult</returns>
 7         private FileResult DownloadExcel(string fileName, DataTable dataTable)
 8         {
 9             MemoryStream stream = WalletComm.BuildToExcel(dataTable);
10             stream.Seek(0, SeekOrigin.Begin);
11             return File(stream, "application/vnd.ms-excel", fileName);
12         }

View Code

2.操作CSV

　　准备生成的公共方法(将数据源DataTable转换成MemoryStream)

 1         /// <summary>
 2         /// 生成CSV
 3         /// </summary>
 4         /// <param name="table">DataTable</param>
 5         /// <returns>MemoryStream</returns>
 6         public static MemoryStream BuildToCsv(DataTable table)
 7         {
 8             Encoding encode = Encoding.GetEncoding("gb2312");
 9             StringBuilder str = new StringBuilder();
10             if (table != null && table.Columns.Count > 0 && table.Rows.Count > 0)
11             {
12                 for (int i = 0; i < table.Columns.Count; i++)
13                 {
14                     str.Append(table.Columns[i].ColumnName.Replace(""", """"));
15                     if (i < table.Columns.Count - 1)
16                     {
17                         str.Append(",");
18                     }
19                 }
20 
21                 foreach (DataRow item in table.Rows)
22                 {
23                     str.Append("
");
24                     for (int i = 0; i < table.Columns.Count; i++)
25                     {
26                         if (item[i] != null)
27                         {
28                             str.Append("'").Append(item[i].ToString().Replace(""", """"));
29                         }
30 
31                         if (i < table.Columns.Count - 1)
32                         {
33                             str.Append(",");
34                         }
35                     }
36                 }
37             }
38 
39             MemoryStream stream = new MemoryStream(encode.GetBytes(str.ToString()));
40             return stream;
41         }

View Code

　　MVC导出

 1         /// <summary>
 2         /// 下载csv文件
 3         /// </summary>
 4         /// <param name="fileName">文件名称</param>
 5         /// <param name="dataTable">数据源</param>
 6         /// <returns>FileResult</returns>
 7         private FileResult DownloadCsv(string fileName, DataTable dataTable)
 8         {
 9             MemoryStream stream = WalletComm.BuildToCsv(dataTable);
10             stream.Seek(0, SeekOrigin.Begin);
11             return File(stream, "text/comma-separated-values", fileName);
12         }

View Code

　　NOPI下载