• 关于在读取excel的文件时候，放在服务器上就报路径错误

    就是指定这个路径：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                 else
26                 {
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             }
    View Code
     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         }
    View Code
      1  /// <summary>
  2         /// Excel转换成DataSet（.xlsx/.xls）
  3         /// </summary>
  4         /// <param name="filePath">Excel文件路径</param>
  5         /// <param name="strMsg"></param>
  6         /// <returns></returns>
  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         /// <summary>
 76         /// 获取sheet表对应的DataTable
 77         /// </summary>
 78         /// <param name="sheet">Excel工作表</param>
 79         /// <param name="strMsg"></param>
 80         /// <returns></returns>
 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             try
102             {
103                 for (int i = 0; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum
104                 {
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             catch
111             {
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                                     else
138                                         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                                     try
147                                     {
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                                     catch
153                                     {
154                                         try
155                                         {
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         }
    View Code
  • 相关阅读:
    利用 Memory Dump Diagnostic for Java (MDD4J) 分析内存管理问题
    Google开源软负载seesaw
    Commit can not be set while enrolled in a transaction
    del_cursor 批量删除游标
    java api 批量数据库操作
    eclipse invalid zip archive lib
    less,more,view一个文件时中文可以正常显示，可是VI却显示乱码呢？
    10-13 Zuul面试点之Cookie和特殊头信息处理
    10-12 Zuul面试点之Hystrix降级处理
    10-11 Zuul面试点之Hystrix整合
  • 原文地址：https://www.cnblogs.com/ZkbFighting/p/11239882.html
Copyright © 2020-2023  润新知