在.net中使用NPOI导入导出

NPOI导入


前台:

1 @using (Html.BeginForm("ImportExcel", "Admin", FormMethod.Post, new { enctype = "multipart/form-data" }))
2 {
3 <input type="submit" value="NPOI" />
4 }
View Code

 

后台：

  1 public ActionResult ImportExcel()
  2 {
  3 
  4 string FileName;
  5 string savePath;
  6 string fileEx;
  7 HttpPostedFileBase file = Request.Files["files"];
  8 string filename = Path.GetFileName(file.FileName);
  9 fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
 10 string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
 11 FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
 12 string path = AppDomain.CurrentDomain.BaseDirectory + "/Uploads/OutExcel/";
 13 savePath = Path.Combine(path, FileName);
 14 file.SaveAs(savePath);
 15 DataTable dt = GetExcelDataTable(savePath);
 16 return View();
 17 }
 18 
 19 //获取Table
 20 public static DataTable GetExcelDataTable(string filePath)
 21 
 22 {
 23 HSSFWorkbook Workbook;
 24 
 25 DataTable table = new DataTable();
 26 
 27 try
 28 
 29 {
 30 
 31 using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
 32 
 33 {
 34 
 35 //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
 36 
 37 string fileExt = Path.GetExtension(filePath).ToLower();
 38 
 39 if (fileExt == ".xls")
 40 
 41 {
 42 
 43 Workbook = new HSSFWorkbook(fileStream);
 44 }
 45 else if (fileExt == ".xlsx")
 46 {
 47 Workbook = new HSSFWorkbook(fileStream);
 48 
 49 }
 50 
 51 else
 52 
 53 {
 54 
 55 Workbook = null;
 56 
 57 }
 58 
 59 }
 60 
 61 }
 62 
 63 catch (Exception ex)
 64 
 65 {
 66 
 67 throw ex;
 68 
 69 }
 70 
 71 
 72 //定位在第一个sheet
 73 HSSFSheet sheet = (HSSFSheet)Workbook.GetSheetAt(0);
 74 
 75 //第一行为标题行
 76 
 77 HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
 78 
 79 int cellCount = headerRow.LastCellNum;
 80 
 81 int rowCount = sheet.LastRowNum;
 82 
 83 
 84 //循环添加标题列
 85 
 86 for (int i = headerRow.FirstCellNum; i < cellCount; i++)
 87 
 88 {
 89 
 90 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
 91 
 92 table.Columns.Add(column);
 93 
 94 }
 95 
 96 
 97 //数据
 98 
 99 for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
100 
101 {
102 HSSFRow row = (HSSFRow)sheet.GetRow(i);
103 DataRow dataRow = table.NewRow();
104 
105 if (row != null)
106 
107 {
108 
109 for (int j = row.FirstCellNum; j < cellCount; j++)
110 
111 {
112 
113 if (row.GetCell(j) != null)
114 
115 {
116 
117 dataRow[j] = GetCellValue((HSSFCell)row.GetCell(j));
118 
119 }
120 
121 }
122 
123 }
124 
125 table.Rows.Add(dataRow);
126 
127 }
128 
129 return table;
130 
131 }
132 
133 private static string GetCellValue(HSSFCell cell)
134 
135 {
136 
137 if (cell == null)
138 
139 {
140 
141 return string.Empty;
142 
143 }
144 
145 
146 switch (cell.CellType)
147 
148 {
149 
150 case CellType.BLANK:
151 
152 return string.Empty;
153 
154 case CellType.BOOLEAN:
155 
156 return cell.BooleanCellValue.ToString();
157 
158 case CellType.ERROR:
159 
160 return cell.ErrorCellValue.ToString();
161 
162 case CellType.NUMERIC:
163 
164 case CellType.Unknown:
165 
166 default:
167 
168 return cell.ToString();
169 
170 case CellType.STRING:
171 
172 return cell.StringCellValue;
173 
174 case CellType.FORMULA:
175 
176 try
177 
178 {
179 
180 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
181 
182 e.EvaluateInCell(cell);
183 return cell.ToString();
184 
185 }
186 
187 catch
188 
189 {
190 
191 return cell.NumericCellValue.ToString();
192 
193 }
194 
195 }
196 
197 }
View Code

 

 

NPOI导出

 1 public void OutExcel()
 2 {
 3 //1.创建工作簿对象
 4 HSSFWorkbook work = new HSSFWorkbook();
 5 //2.创建工作表
 6 work.CreateSheet("sheet1");
 7 //获取名称为Sheet1的工作表
 8 HSSFSheet sheet = (HSSFSheet)work.GetSheet("sheet1");
 9 //3.创建行row
10 sheet.CreateRow(0);
11 //获取Sheet1工作表的首行
12 HSSFRow Row = (HSSFRow)sheet.GetRow(0);
13 Row.CreateCell(0).SetCellValue("图书ID");
14 Row.CreateCell(1).SetCellValue("图书类别名");
15 Row.CreateCell(2).SetCellValue("图书备注");
16 //获取数据
17 List<BookType> list = bktBll.GetBookType();
18 for (int i = 0; i < list.Count(); i++)
19 {
20 //sheet.CreateRow(i);
21 HSSFRow rows = (HSSFRow)sheet.CreateRow(i + 1);
22 rows.CreateCell(0).SetCellValue(list[i].BTID);
23 rows.CreateCell(1).SetCellValue(list[i].BTName);
24 rows.CreateCell(2).SetCellValue(list[i].Remark);
25 }
26 
27 //4.创建流对象并设置存储Excel文件的路径
28 //MemoryStream ms = new MemoryStream();
29 //work.Write(ms);
30 
31 //Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("WS" + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
32 
33 //Response.BinaryWrite(ms.ToArray());
34 
35 //Response.Flush();
36 
37 //Response.End();
38 //work = null;
39 
40 //ms.Close();
41 
42 //ms.Dispose();
43 //保存到硬盘
44 string path = AppDomain.CurrentDomain.BaseDirectory + "/Uploads/OutExcel/" + "信息.xls";
45 using (FileStream stream = new FileStream(path, FileMode.Create))
46 {
47 work.Write(stream);
48 }
49 //发送到浏览器
50 return File(new FileStream(path, FileMode.Open), "application/ms-excel", "信息.xls");
51 }
View Code

 

posted @ 2020-07-01 15:45  栖白  阅读(41)  评论(0)  编辑  收藏  举报