asp.net获取Excel中的数据
1 /// <summary> 2 /// 读取Excel表数据 3 /// </summary> 4 /// <param name="fileExt">文件后缀名(支持格式:.xls .xlsx .csv .mdb)</param> 5 /// <param name="excelFilePath">文件物理路径</param> 6 /// <param name="tableName">表名</param> 7 /// <returns>数据集</returns> 8 public static DataSet ExcelReader(string fileExt, string excelFilePath, string tableName) 9 { 10 string strConn = ""; 11 if (fileExt == ".xls" || fileExt == ".csv") 12 //2003版Excel读取 13 strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; 14 15 else if (fileExt == ".xlsx") 16 //2007版Excel读取 17 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;IMEX=1;HDR=YES';data source=" + excelFilePath; 18 19 else if (fileExt == ".mdb") 20 { 21 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFilePath + ";" + "Persist Security Info=True;"; 22 tableName = "sheet1"; 23 } 24 25 OleDbConnection conn = new OleDbConnection(strConn); 26 OleDbDataAdapter adp = new OleDbDataAdapter("Select * from " + tableName, conn); 27 DataSet ds = new DataSet(); 28 adp.Fill(ds, "Book1"); 29 30 return ds; 31 }
其它
View Code
1 /////辅助方法/ 2 /// <summary> 3 /// Datatable转换为Json 4 /// </summary> 5 /// <param name="table">Datatable对象</param> 6 public String ToJson(DataTable dt) 7 { 8 StringBuilder jsonString = new StringBuilder(); 9 if (dt.Rows.Count > 0) 10 { 11 jsonString.Append("["); 12 DataRowCollection drc = dt.Rows; 13 for (Int32 i = 0; i < drc.Count; i++) 14 { 15 jsonString.Append("{"); 16 for (int j = 0; j < dt.Columns.Count; j++) 17 { 18 String strKey = dt.Columns[j].ColumnName; 19 String strValue = drc[i][j].ToString(); 20 Type type = dt.Columns[j].DataType; 21 jsonString.Append("\"" + strKey + "\":"); 22 strValue = StringFormat(strValue, type); 23 if (j < dt.Columns.Count - 1) 24 { 25 jsonString.Append(strValue + ","); 26 } 27 else 28 { 29 jsonString.Append(strValue); 30 } 31 } 32 jsonString.Append("},"); 33 } 34 jsonString.Remove(jsonString.Length - 1, 1); 35 jsonString.Append("]"); 36 } 37 else 38 { 39 jsonString.Append("[]"); 40 } 41 return jsonString.ToString(); 42 } 43 44 #region 尼玛的私有方法 45 /// <summary> 46 /// 格式化字符型、日期型、布尔型 47 /// </summary> 48 private String StringFormat(String str, Type type) 49 { 50 if (type == typeof(String)) 51 { 52 str = String2Json(str); 53 str = "\"" + str + "\""; 54 } 55 else if (type == typeof(DateTime)) 56 { 57 str = "\"" + str + "\""; 58 } 59 else if (type == typeof(bool)) 60 { 61 str = str.ToLower(); 62 } 63 else if (type != typeof(String) && String.IsNullOrEmpty(str)) 64 { 65 str = "\"" + str + "\""; 66 } 67 return str; 68 } 69 70 /// <summary> 71 /// 过滤特殊字符 72 /// </summary> 73 private String String2Json(String s) 74 { 75 StringBuilder sb = new StringBuilder(); 76 for (Int32 i = 0; i < s.Length; i++) 77 { 78 char c = s.ToCharArray()[i]; 79 switch (c) 80 { 81 case '\"': 82 sb.Append("\\\""); break; 83 case '\\': 84 sb.Append("\\\\"); break; 85 case '/': 86 sb.Append("\\/"); break; 87 case '\b': 88 sb.Append("\\b"); break; 89 case '\f': 90 sb.Append("\\f"); break; 91 case '\n': 92 sb.Append("\\n"); break; 93 case '\r': 94 sb.Append("\\r"); break; 95 case '\t': 96 sb.Append("\\t"); break; 97 default: 98 sb.Append(c); break; 99 } 100 } 101 return sb.ToString(); 102 } 103 #endregion