ASP.Net MVC利用NPOI导入导出Excel
标签：tor files 怎么 anti 批量 catch des rem 创建excel文件
因近期项目遇到所以记录一下：
1、导出Excel
首先引用NPOI包 （NPOI自行搜索下载）
（Action一定要用FileResult）
1 [Localization]
2 ///
3 /// 导出Table的数据
4 ///
5 ///
6 public FileResult ExportExcel()
7 {
8 //string schoolname = "401";
9 //创建Excel文件的对象
10 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
11 //添加一个sheet
12 NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
13 //获取list数据
14 //DataTable dt = new DataTable();
15 //给sheet1添加第一行的头部标题
16 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
17 row1.CreateCell(0).SetCellValue(Resources.Common.ApplicationID);
18 row1.CreateCell(1).SetCellValue(Resources.Common.Order);
19 row1.CreateCell(2).SetCellValue(Resources.Common.Operation);
20 row1.CreateCell(3).SetCellValue(Resources.Common.Plant);
21 row1.CreateCell(4).SetCellValue(Resources.Common.WorkCenter);
22 row1.CreateCell(5).SetCellValue(Resources.Common.Quantity);
23 row1.CreateCell(6).SetCellValue(Resources.Common.WorkHour);
24 row1.CreateCell(7).SetCellValue(Resources.Common.Reason);
25 row1.CreateCell(8).SetCellValue("ECO#");
26 row1.CreateCell(9).SetCellValue(Resources.Common.Remark);
27 row1.CreateCell(10).SetCellValue(Resources.Common.Applicant);
28 row1.CreateCell(11).SetCellValue(Resources.Common.ApplicationStatus);
29 row1.CreateCell(12).SetCellValue(Resources.Common.ApplicationRemark);
30
31 //将数据逐步写入sheet1各个行
32 for (int i = 0; i )
33 {
34 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
35 rowtemp.CreateCell(0).SetCellValue(dt_ex.Rows[i]["ApplicationID"].ToString());
36 rowtemp.CreateCell(1).SetCellValue(dt_ex.Rows[i]["OrderNo"].ToString());
37 rowtemp.CreateCell(2).SetCellValue(dt_ex.Rows[i]["OperationNo"].ToString());
38 rowtemp.CreateCell(3).SetCellValue(dt_ex.Rows[i]["Plant"].ToString());
39 rowtemp.CreateCell(4).SetCellValue(dt_ex.Rows[i]["WorkCenter"].ToString());
40 rowtemp.CreateCell(5).SetCellValue(dt_ex.Rows[i]["Finished_Qty"].ToString());
41 rowtemp.CreateCell(6).SetCellValue(dt_ex.Rows[i]["WorkHour"].ToString());
42 rowtemp.CreateCell(7).SetCellValue(dt_ex.Rows[i]["ReasonDescription"].ToString());
43 rowtemp.CreateCell(8).SetCellValue(dt_ex.Rows[i]["ECO_No"].ToString());
44 rowtemp.CreateCell(9).SetCellValue(dt_ex.Rows[i]["Remark"].ToString());
45 rowtemp.CreateCell(10).SetCellValue(dt_ex.Rows[i]["name"].ToString());
46 rowtemp.CreateCell(11).SetCellValue(dt_ex.Rows[i]["Description"].ToString());
47 rowtemp.CreateCell(12).SetCellValue(dt_ex.Rows[i]["ApplicationRemark"].ToString());
48 }
49 // 写入到客户端
50 System.IO.MemoryStream ms = new System.IO.MemoryStream();
51 book.Write(ms);
52 ms.Seek(0, SeekOrigin.Begin);
53 return File(ms, "application/vnd.ms-excel", "Application.xls");
54 }
前台直接写就可实现：
1 div class="row">
2 div class="form-group col-sm-offset-1">
3 a class="btn btn-link" href="@Url.Action("ExportExcel","Search" )">@Resources.Common.Downloada>
4 div>
5 div>
2、导入Excel
首先说一些前台吧，mvc上传注意必须加 new { enctype = "multipart/form-data" }：
1 td>
2 @using(@Html.BeginForm("ImportStu", "ProSchool", FormMethod.Post, new { enctype = "multipart/form-data" }))
3 {
4 text>选择上传文件：（工作表名为“Sheet1”，“电脑号”在A1单元格。）text>
5 input name="file" type="file" id="file" />
6 input type="submit" name="Upload" value="批量导入第一批电脑派位名册" />
7 }
8 td>
后台实现：只传路径得出DataTable：
1 ///
2 /// Excel导入
3 ///
4 ///
5 ///
6 public DataTable ImportExcelFile(string filePath)
7 {
8 HSSFWorkbook hssfworkbook;
9 #region//初始化信息
10 try
11 {
12 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
13 {
14 hssfworkbook = new HSSFWorkbook(file);
15 }
16 }
17 catch (Exception e)
18 {
19 throw e;
20 }
21 #endregion
22
23 using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0))
24 {
25 DataTable table = new DataTable();
26 IRow headerRow = sheet.GetRow(0);//第一行为标题行
27 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
28 int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
29
30 //handling header.
31 for (int i = headerRow.FirstCellNum; i )
32 {
33 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
34 table.Columns.Add(column);
35 }
36 for (int i = (sheet.FirstRowNum + 1); i )
37 {
38 IRow row = sheet.GetRow(i);
39 DataRow dataRow = table.NewRow();
40
41 if (row != null)
42 {
43 for (int j = row.FirstCellNum; j )
44 {
45 if (row.GetCell(j) != null)
46 dataRow[j] = GetCellValue(row.GetCell(j));
47 }
48 }
49
50 table.Rows.Add(dataRow);
51 }
52 return table;
53 }
54
55 }
3、补充一个类
1 ///
2 /// 根据Excel列类型获取列的值
3 ///
4 /// Excel列
5 ///
6 private static string GetCellValue(ICell cell)
7 {
8 if (cell == null)
9 return string.Empty;
10 switch (cell.CellType)
11 {
12 case CellType.BLANK:
13 return string.Empty;
14 case CellType.BOOLEAN:
15 return cell.BooleanCellValue.ToString();
16 case CellType.ERROR:
17 return cell.ErrorCellValue.ToString();
18 case CellType.NUMERIC:
19 case CellType.Unknown:
20 default:
21 return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
22 case CellType.STRING:
23 return cell.StringCellValue;
24 case CellType.FORMULA:
25 try
26 {
27 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
28 e.EvaluateInCell(cell);
29 return cell.ToString();
30 }
31 catch
32 {
33 return cell.NumericCellValue.ToString();
34 }
35 }
36 }
得到DataTable后，就想怎么操作就怎么操作了
ASP.Net MVC利用NPOI导入导出Excel
标签：tor files 怎么 anti 批量 catch des rem 创建excel文件
原文地址：https://www.cnblogs.com/ning123/p/12706159.html
评论