@*前台
1 /// <summary>
2 /// 导出Excel
3 /// </summary>
4 public void ExcelExprot()
5 {
6 //创建Excel文件的对象
7 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
8 //添加一个sheet
9 NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
10
11 List<Thebookfor> list = GetAll();
12
13 //给sheet1添加第一行的头部标题
14 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
15 row1.CreateCell(0).SetCellValue("序号");
16 row1.CreateCell(1).SetCellValue("书名");
17 row1.CreateCell(2).SetCellValue("ISBN");
18 row1.CreateCell(3).SetCellValue("出版者");
19 row1.CreateCell(4).SetCellValue("中图分类法");
20 row1.CreateCell(5).SetCellValue("申请时间");
21 //将数据逐步写入sheet1各个行
22 for (int i = 0; i < list.Count; i++)
23 {
24 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
25 rowtemp.CreateCell(0).SetCellValue(list[i].ThebookforID.ToString());
26 rowtemp.CreateCell(1).SetCellValue(list[i].BookName.ToString());
27 rowtemp.CreateCell(2).SetCellValue(list[i].ISBN.ToString());
28 rowtemp.CreateCell(3).SetCellValue(list[i].bookmaker.ToString());
29 rowtemp.CreateCell(4).SetCellValue(list[i].BookclassifyID.ToString());
30 rowtemp.CreateCell(5).SetCellValue(list[i].timeofmaking.ToString());
31 }
32
33 // 写入到客户端
34 MemoryStream ms = new MemoryStream();
35 book.Write(ms);
36 ms.Seek(0, SeekOrigin.Begin);
37
38 ms.Flush();
39 ms.Position = 0;
40 //编辑完后 通过response输出
41
42 Response.Clear();
43 Response.Buffer = true;
44 Response.Charset = "UTF8";
45 Response.ContentEncoding = System.Text.Encoding.UTF8;
46 Response.ContentType = "application/msexcel";
47 Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("第一批电脑派位生名册.xls"));
48 Response.BinaryWrite(ms.ToArray());
49 Response.Flush();
50 Response.End();
51
52 }
53
54 private List<Thebookfor> GetAll()
55 {
56 string sql = "select * from Thebookfor";
57 DataTable datas = DBhelper.QuerySql(sql);
58 string t = Newtonsoft.Json.JsonConvert.SerializeObject(datas);
59 List<Thebookfor> ex = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Thebookfor>>(t);
60 return ex;
61 }
*@
1 <form action="/Home/TestExcel" enctype="multipart/form-data" method="post">
2 <text>选择上传文件：（工作表名为“Sheet1”，“电脑号”在A1单元格。）</text>
3 <input name="file" type="file" id="file" />
4 <input type="submit" name="Upload" value="批量导入第一批电脑派位名册" />
5 </form>
6
7
8
9
10
11
12 1 /// <summary>
13 2 /// Excel导入
14 3 /// </summary>
15 4 /// <param name="filePath"></param>
16 5 /// <returns></returns>
17 6 [HttpPost]
18 7 public ActionResult TestExcel(FormCollection form)
19 8 {
20 9 HttpPostedFileBase file = Request.Files[0];
21 10 string path = Server.MapPath("\Models");
22 11 path += "\" + file.FileName;
23 12 file.SaveAs(path);
24 13
25 14 ImportExcelFile(path);
26 15 return View();
27 16 }
28 17
29 18
30 19 /// <summary>
31 20 /// Excel导入
32 21 /// </summary>
33 22 /// <param name="filePath"></param>
34 23 /// <returns></returns>
35 24 public DataTable ImportExcelFile(string filePath)
36 25 {
37 26 HSSFWorkbook hssfworkbook;
38 27 #region//初始化信息
39 28 try
40 29 {
41 30 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
42 31 {
43 32 hssfworkbook = new HSSFWorkbook(file);
44 33 }
45 34 }
46 35 catch (Exception e)
47 36 {
48 37 throw e;
49 38 }
50 39 #endregion
51 40
52 41 ISheet sheet = hssfworkbook.GetSheetAt(3);
53 42 DataTable table = new DataTable();
54 43 IRow headerRow = sheet.GetRow(0);//第一行为标题行
55 44 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
56 45 int rowCount = sheet.LastRowNum - 2;
57 46
58 47 for (int i = headerRow.FirstCellNum; i < cellCount; i++)
59 48 {
60 49 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
61 50 table.Columns.Add(column);
62 51 }
63 52 for (int i = (sheet.FirstRowNum + 4); i <= rowCount; i++)
64 53 {
65 54 IRow row = sheet.GetRow(i);
66 55 DataRow dataRow = table.NewRow();
67 56
68 57 if (row != null)
69 58 {
70 59 for (int j = row.FirstCellNum; j < cellCount; j++)
71 60 {
72 61 if (row.GetCell(j) != null)
73 62 dataRow[j] = GetCellValue(row.GetCell(j));
74 63 }
75 64 }
76 65 table.Rows.Add(dataRow);
77 66 }
78 67 using (SqlBulkCopy abc = new SqlBulkCopy(SqlConnectionFactory.Connection))
79 68 {
80 69 abc.BatchSize = table.Rows.Count;
81 70 abc.BulkCopyTimeout = 11;
82 71 abc.DestinationTableName = "ExcelTable";
83 72 for (int i = 0; i < table.Columns.Count; i++)
84 73 {
85 74 abc.ColumnMappings.Add(table.Columns[i].ColumnName, i);
86 75 }
87 76 abc.WriteToServer(table);
88 77 }
89 78 return table;
90 79 }
91 80 /// <summary>
92 81 /// 根据Excel列类型获取列的值
93 82 /// </summary>
94 83 /// <param name="cell">Excel列</param>
95 84 /// <returns></returns>
96 85 private static string GetCellValue(ICell cell)
97 86 {
98 87 if (cell == null)
99 88 return string.Empty;
100 89 switch (cell.CellType)
101 90 {
102 91 case CellType.Blank:
103 92 return string.Empty;
104 93 case CellType.Boolean:
105 94 return cell.BooleanCellValue.ToString();
106 95 case CellType.Error:
107 96 return cell.ErrorCellValue.ToString();
108 97 case CellType.Numeric:
109 98 case CellType.Unknown:
110 99 default:
111 100 return cell.ToString();
112 101 case CellType.String:
113 102 return cell.StringCellValue;
114 103 case CellType.Formula:
115 104 try
116 105 {
117 106 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
118 107 e.EvaluateInCell(cell);
119 108 return cell.ToString();
120 109 }
121 110 catch
122 111 {
123 112 return cell.NumericCellValue.ToString();
124 113 }
125 114 }
126 115 }