asp.net上传Excel文件并读取内容，自定义上传控件样式
一、页面增加上传控件，并在上传时判断是否是Excel文件（根据后缀名判断）：
1 <table> 2 <tr> 3 <td> 4 <span style="color: Red; clear: both; vertical-align: middle;">*</span> Excel文件： 5 </td> 6 <td> 7 <a> 8 <input type="text" id="des" readonly="readonly" /> 9 </a> 10 </td> 11 <td> 12 <asp:UpdatePanel ID="UpdatePanel1" runat="server"> 13 <ContentTemplate> 14 <a href="javascript:;" id="aFileUpload" class="files"> 15 <input type="file" runat="server" id="FileUpload" title="Select file" name="FileUpload" onchange="document.getElementById('des').value='';if(this.files[0].name.indexOf('.xls')<=0){alert('Please select excel file !'); 16 document.getElementById('des').value=''; this.value = null; return;};document.getElementById('des').value=this.files[0].name;" /> 17 </a> 18 </ContentTemplate> 19 <Triggers> 20 <asp:PostBackTrigger ControlID="btnOK" /> 21 </Triggers> 22 </asp:UpdatePanel> 23 </td> 24 </tr> 25 <tr> 26 <td></td> 27 <td> 28 <asp:Button ID="btnOK" runat="server" Text="确定" OnClick="btnOK_Click" /> 29 </td> 30 </tr> 31 </table>
二、增加css样式，用于去掉上传控件默认的样式，使用自定义样式：
1 <style> 2 /*file容器样式*/ 3 a.files { 4 margin: 0 auto; 5 float: left; 6 width: 30px; 7 height: 30px; 8 overflow: hidden; 9 display: block; 10 border: 1px solid #d7d7d7; 11 background: url(browse-l.png) left top no-repeat; 12 text-decoration: none; 13 } 14 /*file设为透明，并覆盖整个触发面*/ 15 a.files input { 16 margin-left: -270px; 17 font-size: 24px; 18 cursor: pointer; 19 filter: alpha(opacity=0); 20 opacity: 0; 21 } 22 /*取消点击时的虚线框*/ 23 a.files, a.files input { 24 outline: none; /*ff*/ 25 hide-focus: expression(this.hideFocus=true); /*ie*/ 26 } 27 </style>
三、上传并读取Excel方法：
1 protected void btnOK_Click(object sender, EventArgs e) 2 { 3 string toFileFullPath = ""; //物理完整路径 4 string filePath = ""; //上传后的文件路径 5 if (HttpContext.Current != null) 6 toFileFullPath = HttpContext.Current.Server.MapPath("~"); 7 //检查是否有该路径，没有就创建 8 if (!Directory.Exists(toFileFullPath)) 9 Directory.CreateDirectory(toFileFullPath); 10 HttpFileCollection files = Request.Files; 11 if (files.Count > 0) 12 { 13 string fileName = files[0].FileName; 14 string ext = System.IO.Path.GetExtension(fileName);//获取后缀名 15 if (ext == ".xlsx") 16 fileName = "更改的名称.xlsx"; 17 else if (ext == ".xls") 18 fileName = "更改的名称.xls"; 19 filePath = Path.Combine(toFileFullPath, fileName); 20 files[0].SaveAs(filePath); 21 } 22 DataSet ds = ExcelToDataSet(filePath); 23 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) 24 { 25 //只能读取有表头的列 26 Response.Write(ds.Tables[0].Rows[i][0].ToString());//获取第一列数据 27 Response.Write(ds.Tables[0].Rows[i][1].ToString());//获取第二列数据 28 } 29 } 30 31 /// <summary> 32 /// 功能：导入Excel 33 /// 方式：NPOI 34 /// 支持格式：支持xls和xlsx格式 35 /// 调用注意：默认第一行为表头，从第2行开始读取数据 36 /// </summary> 37 /// <param name="strFileName">excel 文件路径</param> 38 /// <returns></returns> 39 private DataSet ExcelToDataSet(string strFileName) 40 { 41 ISheet sheet = null; 42 DataSet ds = new DataSet(); 43 DataTable dt = new DataTable(); 44 IWorkbook workbook = null; 45 try 46 { 47 FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read); 48 //if (strFileName.IndexOf(".xlsx") > 0) // 2007版本 49 // workbook = new XSSFWorkbook(file); 50 //else if (strFileName.IndexOf(".xls") > 0) // 2003版本 51 // workbook = new HSSFWorkbook(file); 52 workbook = WorkbookFactory.Create(file); 53 //获取sheet 54 sheet = workbook.GetSheetAt(0); 55 if (sheet != null) 56 { 57 IRow headerRow = sheet.GetRow(0); 58 int cellCount = headerRow.LastCellNum; //一行最后一个cell的编号 即总的列数 59 for (int j = 0; j < cellCount; j++) 60 { 61 ICell cell = headerRow.GetCell(j); 62 if (cell == null) 63 { 64 string strColumnName = (j++).ToString(); //判断如果第一列之后还存在多列，列名必须不同，否则报错 65 dt.Columns.Add(strColumnName); 66 continue; 67 } 68 dt.Columns.Add(cell.ToString()); 69 } 70 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 71 { 72 IRow row = sheet.GetRow(i); 73 DataRow dataRow = dt.NewRow(); 74 if (row != null) 75 { 76 for (int j = row.FirstCellNum; j < cellCount; j++) 77 { 78 if (row.GetCell(j) != null) 79 dataRow[j] = row.GetCell(j).ToString(); 80 } 81 dt.Rows.Add(dataRow); 82 } 83 } 84 ds.Tables.Add(dt); 85 } 86 return ds; 87 } 88 catch (Exception ex) 89 { 90 Console.WriteLine("Exception: " + ex.Message); 91 return null; 92 } 93 }
附件：
读取Excel需要添加插件（附插件地址）：读取Excel插件.rar
百度网盘链接：https://pan.baidu.com/s/1otEqimEI3NOCHsc5olSM6A
提取码：u7zv
