就是指定这个路径：C:\Program Files (x86)\IIS Express
因为在上传到服务器的时候，服务器读取的是在服务器上的路径，所以正确的思路应该是
把上传的Excel存在服务器上，再去读取服务器上的excel，这样就行了。
有个弊端就是，需要定期去清除存在服务器上的Excel。
1 try 2 { 3 string sheetname = TextBox1.Text.Trim(); 4 if (string.IsNullOrEmpty(sheetname)) 5 { 6 ShowAlertMessage("sheetname不可为空！"); 7 return; 8 } 9 string username = Context.Items["UserName"].ToString();10 HttpPostedFile upLoadPostFile = FileUpload1.PostedFile;11 string upLoadPath = upLoadPostFile.FileName;12 int upLoadPathLength = upLoadPath.Split('\\').Length;13 string FileName = upLoadPath.Split('\\')[upLoadPathLength - 1].ToString();14 15 if (upLoadPath == "")16 {17 ShowAlertMessage("请选择上传文件！");18 return;19 }20 string excelType = upLoadPath.Split('.')[1].ToString();21 if (excelType != "xls" && excelType != "xlsx")22 {23 ShowAlertMessage("此文件不是xls或者xlsx格式，请重新选择上传文件格式！");24 }25 else26 {27 28 DataSet ds = GetExcelData(upLoadPostFile, sheetname, FileName);29 DataTable dtInfo = ds.Tables[0];30 for (int i = 0; i < dtInfo.Rows.Count; i++)31 {32 string strOrderSn = dtInfo.Rows[i][0].ToString();33 if (string.IsNullOrEmpty(strOrderSn))34 {35 ShowAlertMessage("读取数据结束");36 }37 DataTable dtOrder = InvoiceData.GetOrderHead(strOrderSn).Tables[0];38 if (dtOrder.Rows.Count == 0)39 {40 throw new Exception("ChangeOrderStatusReturnGoods Error:订单不存在：" + strOrderSn + "");41 }42 string OrderStatus = dtOrder.Rows[0]["OrderStatus"].ToString();43 if (OrderStatus != "2")44 {45 InvoiceData.UpdateStatusByReturnGoods(strOrderSn);46 OrderData.OrderOperationLog(strOrderSn, username, DateTime.Now, "退货更改订单状态");47 }48 }49 }50 ShowAlertMessage("状态更改完毕！");51 }52 catch (Exception ex)53 {54 CustomValidator1.ErrorMessage = ex.Message;55 CustomValidator1.IsValid = false;56 }
1 public static DataSet GetExcelData(HttpPostedFile upLoadPostFile, string sheetName, string FileName) 2 { 3 string strPath = ConfigurationManager.AppSettings.Get("ReturnGoodsUploadExcelPath").ToString(); 4 Directory.CreateDirectory(strPath); 5 Guid guidnew = Guid.NewGuid(); 6 string NewPath = strPath + @"\" + DateTime.Now.ToString("yyyy-MM-dd")+"_"+""+guidnew.ToString()+"" + FileName; 7 upLoadPostFile.SaveAs(NewPath); 8 DataSet ds = InvoiceData.ExcelToDataSet(NewPath, ""); 9 return ds;10 } 1 ///2 /// Excel转换成DataSet（.xlsx/.xls） 3 /// 4 /// Excel文件路径 5 /// 6 ///7 public static DataSet ExcelToDataSet(string filePath, string strMsg) 8 { 9 strMsg = ""; 10 DataSet ds = new DataSet(); 11 DataTable dt = new DataTable(); 12 string fileType = Path.GetExtension(filePath).ToLower(); 13 string fileName = Path.GetFileName(filePath).ToLower(); 14 try 15 { 16 ISheet sheet = null; 17 int sheetNumber = 0; 18 FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); 19 if (fileType == ".xlsx") 20 { 21 // 2007版本 22 XSSFWorkbook workbook = new XSSFWorkbook(fs); 23 sheetNumber = workbook.NumberOfSheets; 24 for (int i = 0; i < sheetNumber; i++) 25 { 26 string sheetName = workbook.GetSheetName(i); 27 sheet = workbook.GetSheet(sheetName); 28 if (sheet != null) 29 { 30 dt = GetSheetDataTable(sheet, strMsg); 31 if (dt != null) 32 { 33 dt.TableName = sheetName.Trim(); 34 ds.Tables.Add(dt); 35 } 36 else 37 { 38 MessageBox.Show("Sheet数据获取失败，原因：" + strMsg); 39 } 40 } 41 } 42 } 43 else if (fileType == ".xls") 44 { 45 // 2003版本 46 HSSFWorkbook workbook = new HSSFWorkbook(fs); 47 sheetNumber = workbook.NumberOfSheets; 48 for (int i = 0; i < sheetNumber; i++) 49 { 50 string sheetName = workbook.GetSheetName(i); 51 sheet = workbook.GetSheet(sheetName); 52 if (sheet != null) 53 { 54 dt = GetSheetDataTable(sheet, strMsg); 55 if (dt != null) 56 { 57 dt.TableName = sheetName.Trim(); 58 ds.Tables.Add(dt); 59 } 60 else 61 { 62 MessageBox.Show("Sheet数据获取失败，原因：" + strMsg); 63 } 64 } 65 } 66 } 67 return ds; 68 } 69 catch (Exception ex) 70 { 71 strMsg = ex.Message; 72 return null; 73 } 74 } 75 /// 76 /// 获取sheet表对应的DataTable 77 /// 78 /// Excel工作表 79 /// 80 ///81 public static DataTable GetSheetDataTable(ISheet sheet, string strMsg) 82 { 83 strMsg = ""; 84 DataTable dt = new DataTable(); 85 string sheetName = sheet.SheetName; 86 int startIndex = 0;// sheet.FirstRowNum; 87 int lastIndex = sheet.LastRowNum; 88 //最大列数 89 int cellCount = 0; 90 IRow maxRow = sheet.GetRow(0); 91 for (int i = startIndex; i <= lastIndex; i++) 92 { 93 IRow row = sheet.GetRow(i); 94 if (row != null && cellCount < row.LastCellNum) 95 { 96 cellCount = row.LastCellNum; 97 maxRow = row; 98 } 99 }100 //列名设置101 try102 {103 for (int i = 0; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum104 {105 dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());106 //DataColumn column = new DataColumn("Column" + (i + 1).ToString());107 //dt.Columns.Add(column);108 }109 }110 catch111 {112 strMsg = "工作表" + sheetName + "中无数据";113 return null;114 }115 //数据填充116 for (int i = startIndex; i <= lastIndex; i++)117 {118 IRow row = sheet.GetRow(i);119 DataRow drNew = dt.NewRow();120 if (row != null)121 {122 for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)123 {124 if (row.GetCell(j) != null)125 {126 ICell cell = row.GetCell(j);127 switch (cell.CellType)128 {129 case CellType.Blank:130 drNew[j] = "";131 break;132 case CellType.Numeric:133 short format = cell.CellStyle.DataFormat;134 //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理135 if (format == 14 || format == 31 || format == 57 || format == 58)136 drNew[j] = cell.DateCellValue;137 else138 drNew[j] = cell.NumericCellValue;139 if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)140 drNew[j] = cell.NumericCellValue.ToString("#0.00");141 break;142 case CellType.String:143 drNew[j] = cell.StringCellValue;144 break;145 case CellType.Formula:146 try147 {148 drNew[j] = cell.NumericCellValue;149 if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)150 drNew[j] = cell.NumericCellValue.ToString("#0.00");151 }152 catch153 {154 try155 {156 drNew[j] = cell.StringCellValue;157 }158 catch { }159 }160 break;161 default:162 drNew[j] = cell.StringCellValue;163 break;164 }165 }166 }167 }168 dt.Rows.Add(drNew);169 }170 return dt;171 }