札记一

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

posted on 2012-06-08 08:03 可惜我是程序员. 阅读(145) 评论(0) 编辑收藏举报