Excel文件上传,后台初次认定的只是个有大小的文件流,要想获取上传的数据,则需要用到NPOl
获取文件流
1 HttpPostedFileBase filebase=Request.Files[0]; //单独文件访问 2 Stream inputstream=filebase.InputStream; //获取excel文档流 3 string filename=filebase.FileName;
Excel文件流转换成datatable
1 IWorkbook workbook = new XSSFWorkbook(filestream); //.xlsx 2 ISheet sheet=workbook.GetSheetAt(0); 3 4 DataTable table = new DataTable(); 5 6 IRow headerRow = sheet.GetRow(headerRowIndex); 7 8 if (headerRow == null) return table; 9 10 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells 11 int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 12 13 //handling header(处理标题). 14 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 15 { 16 string headColumnName = GetCellValue(headerRow.GetCell(i)); //GetCellValue()根据Excel列类型获取列的值 17 18 while (table.Columns.Contains(headColumnName)) headColumnName = headColumnName + "+"; 19 20 DataColumn column = new DataColumn(headColumnName); 21 table.Columns.Add(column); 22 23 } 24 for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) 25 { 26 IRow row = sheet.GetRow(i); 27 DataRow dataRow = table.NewRow(); 28 if (row != null && row.FirstCellNum > -1) 29 { 30 for (int j = row.FirstCellNum; j < cellCount; j++) 31 { 32 if (row.GetCell(j) != null) 33 { 34 if (dataRow.ItemArray.Length > j) 35 { 36 dataRow[j] = GetCellValue(row.GetCell(j)); 37 } 38 } 39 } 40 table.Rows.Add(dataRow); 41 } 42 } 43 return table;