Excel 操作(未完待续)
Excel导入 数据库
View Code
1 private void GetExcelData07(string str) 2 { 3 try 4 { 5 strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + str + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; 6 OleDbConnection myConn = new OleDbConnection(strCon); 7 myConn.Open(); 8 DataTable dtTables = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 9 myConn.Close(); 10 DataSet myDataSet = new DataSet(); 11 if (dtTables.Rows.Count > 0) 12 { 13 lb_Tag.Items.Clear(); 14 foreach (DataRow dr in dtTables.Rows) 15 { 16 string sSName = dr["TABLE_NAME"].ToString(); 17 if (!sSName.Contains("Print_Titles") && !sSName.Contains("Print_Area")) lb_Tag.Items.Add(sSName); 18 } 19 } 20 } 21 catch (Exception ex) 22 { 23 System.Windows.Forms.MessageBox.Show(ex.Message, "您选择的数据导入的EXCEL不是正确的EXCEL！"); 24 } 25 } 26 27 private void GetExcelData03(string str) 28 { 29 try 30 { 31 strCon = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ='" + str + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; 32 OleDbConnection myConn = new OleDbConnection(strCon); 33 myConn.Open(); 34 DataTable dtTables = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 35 myConn.Close(); 36 if (dtTables.Rows.Count > 0) 37 { 38 lb_Tag.Items.Clear(); 39 foreach (DataRow dr in dtTables.Rows) 40 { 41 string sSName = dr["TABLE_NAME"].ToString(); 42 if (!sSName.Contains("Print_Titles") && !sSName.Contains("Print_Area")) lb_Tag.Items.Add(sSName); 43 } 44 } 45 } 46 catch (Exception ex) 47 { 48 System.Windows.Forms.MessageBox.Show(ex.Message, "您选择的数据导入的EXCEL不是正确的EXCEL！"); 49 } 50 } 51 52 private DataSet SelectTable(string sSName) 53 { 54 DataSet myDataSet = new DataSet(); 55 OleDbConnection myConn = new OleDbConnection(strCon); 56 string strCom = String.Format("select * from [{0}]", sSName); 57 myConn.Open(); 58 OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); 59 myCommand.Fill(myDataSet); 60 myConn.Close(); 61 return myDataSet; 62 } 63 64 65 private void btn_Select_Click(object sender, EventArgs e) 66 { 67 OpenFileDialog MyFilePath = new OpenFileDialog(); 68 MyFilePath.Title = "打开文件"; 69 MyFilePath.Filter = "Excel2003文件|*.xls|Excel2007文件|*.xlsx"; 70 if (MyFilePath.ShowDialog() == DialogResult.OK) 71 { 72 73 str_filepath = MyFilePath.FileName.ToString(); 74 tb_FileName.Text = MyFilePath.FileName.ToString(); 75 } 76 else 77 { 78 return; 79 } 80 DataTable dt = new DataTable(); 81 if (str_filepath.Contains(".xlsx")) 82 { 83 GetExcelData07(str_filepath); 84 } 85 else 86 { 87 GetExcelData03(str_filepath); 88 } 89 ds = SelectTable(lb_Tag.Items[0].ToString()); 90 if (ds != null && ds.Tables.Count > 0) 91 { 92 gridControl.DataSource = CreateDataTable(ds); 93 } 94 else 95 { 96 tb_FileName.Text = string.Empty; 97 } 98 }
OleDbConnection访问Excel 定义过多字段异常
View Code
1 在查询中，Excel的spreedsheet的列数有256的限制。 2 因此在查询时odCommand.CommandText = "SELECT * FROM ["+sheet+"]"; 改成 "SELECT * FROM ["+sheet+"A:IU]" 3 行数应该也有限制。但是行数限制要比列数宽松。这里就没有对行做限制。 4 这样暂时就解决问题了。
