在導入之前都需要將上傳的文件保存到伺服器,所以避免重覆的寫這些代碼,先貼出上傳文件並保存到伺服器指定路徑的代碼。 protected void btnImport_Click(object sender, EventArgs e) { Random random = new Random(); Im ...
在導入之前都需要將上傳的文件保存到伺服器,所以避免重覆的寫這些代碼,先貼出上傳文件並保存到伺服器指定路徑的代碼。
protected void btnImport_Click(object sender, EventArgs e) { Random random = new Random(); ImportClass Import = new ImportClass(); //保存文件的虛擬路徑 string path = "Import/"; //獲取選擇的文件名 string fileName = FileUpload1.FileName; //獲取文件擴展名稱 string fileExt = Path.GetExtension(fileName); //生成新文件名 string newName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + random.Next(0, 9999).ToString(); //獲取指定虛機路徑的物理路徑 string fullPath = HttpContext.Current.Server.MapPath(path); //上傳文件保存路徑 string savePath = fullPath + newName + fileExt; //保存文件到伺服器 FileUpload1.SaveAs(savePath); try { //獲取導入的數據 DataSet ds = Import.ImportExcel(savePath); if (ds != null && ds.Tables.Count > 0) { //這裡可以寫插入資料庫的方法 } } catch (Exception ex) { throw; } }View Code
第一種:OleDB
public DataSet ImportExcel(string filePath) { DataSet ds = null; OleDbConnection conn; string strConn = string.Empty; string sheetName = string.Empty; try { // Excel 2003 版本連接字元串 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1;'"; conn = new OleDbConnection(strConn); conn.Open(); } catch { // Excel 2007 以上版本連接字元串 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; conn = new OleDbConnection(strConn); conn.Open(); } //獲取所有的 sheet 表 DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); ds = new DataSet(); for (int i = 0; i < dtSheetName.Rows.Count; i++) { DataTable dt = new DataTable(); dt.TableName = "table" + i.ToString(); //獲取表名 sheetName = dtSheetName.Rows[i]["TABLE_NAME"].ToString(); OleDbDataAdapter oleda = new OleDbDataAdapter("select * from [" + sheetName + "]", conn); oleda.Fill(dt); ds.Tables.Add(dt); } return ds; }View Code
除了讀取過程不太靈活之外,這種讀取方式還有個弊端就是,當 Excel 數據量很大時。會非常占用記憶體,當記憶體不夠時會拋出記憶體溢出的異常,不過一般都能適用了。
第二種:Microsoft.Office.Interop.Excel.dll
public DataSet ImportExcel(string filePath) { DataSet ds = null; DataTable dt = null; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = null; Microsoft.Office.Interop.Excel.Worksheet worksheet = null; Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel.Range range = null; object missing = System.Reflection.Missing.Value; try { if (excel == null) { return null; } //打開 Excel 文件 workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //獲取所有的 sheet 表 sheets = workbook.Worksheets; ds = new DataSet(); for (int i = 1; i <= sheets.Count; i++) { //獲取第一個表 worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i); int rowCount = worksheet.UsedRange.Rows.Count; int colCount = worksheet.UsedRange.Columns.Count; int rowIndex = 1; //起始行為 1 int colIndex = 1; //起始列為 1 DataColumn dc; dt = new DataTable(); dt.TableName = "table" + i.ToString(); //讀取列名 for (int j = 0; j < colCount; j++) { range = worksheet.Cells[rowIndex, colIndex + j]; dc = new DataColumn(); dc.DataType = Type.GetType("System.String"); dc.ColumnName = range.Text.ToString().Trim(); //添加列 dt.Columns.Add(dc); } //讀取行數據 for (int k = 1; k < rowCount; k++) { DataRow dr = dt.NewRow(); for (int l = 0; l < colCount; l++) { range = worksheet.Cells[rowIndex + k, colIndex + l]; //使用 range.Value.ToString(); 或 range.Value2.ToString(); 或 range.Text.ToString(); 都可以獲取單元格的值 dr[l] = range.Text.ToString(); } dt.Rows.Add(dr.ItemArray); } ds.Tables.Add(dt); } } catch (Exception ex) { throw; } return ds; }View Code
這種方法首先需要安裝有 office Excel,並且是一個單元格一個單元格的讀取,所以性能會比較差。
未完待續。。。