一、点击导入按钮,弹出文件选择框
这个方法的使用要引用下面两个命名空间:
using System.Windows.Forms;
using DevExpress.XtraEditors;
1 private void button1_Click(object sender, EventArgs e) 2 { 3 OpenFileDialog ofd = new OpenFileDialog(); 4 ofd.Title = "选择文件"; 5 ofd.Filter = "Microsoft Excel文件|*.xls;*.xlsx"; 6 ofd.FilterIndex = 1; 7 ofd.DefaultExt = "xls"; 8 if (ofd.ShowDialog() == DialogResult.OK) 9 { 10 if (!ofd.SafeFileName.EndsWith(".xls") && !ofd.SafeFileName.EndsWith(".xlsx")) 11 { 12 XtraMessageBox.Show("请选择Excel文件","文件解析失败!",MessageBoxButtons.OK,MessageBoxIcon.Error); 13 return; 14 } 15 if (!ofd.CheckFileExists) 16 { 17 XtraMessageBox.Show("指定的文件不存在","请检查!",MessageBoxButtons.OK,MessageBoxIcon.Error); 18 return; 19 } 20 //DataTable dt = NpoiOperExcel.ExcelToDataTable(ofd.FileName,true); 21 DataSet ds = NpoiOperExcel.DsExcel(ofd.FileName); 22 } 23 }
21行使用的方法,读取excel表格
1 public static DataSet DsExcel(string filePath) 2 { 3 string str = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + filePath + ";Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"";//智能读取2003版本 4 OleDbConnection con = new OleDbConnection(str); 5 try 6 { 7 con.Open(); 8 DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 9 string tablename = "", sql = ""; 10 w_import_select_table wi = new w_import_select_table();//创建的一个窗体,用于选择工作表格 11 wi.dt = dt; 12 if (wi.ShowDialog() != DialogResult.Yes) 13 { 14 return null; 15 } 16 tablename = wi.listBox1.Text.Trim(); 17 sql = "select * from [" + tablename + "]"; 18 OleDbDataAdapter da = new OleDbDataAdapter(sql, con); 19 DataSet ds = new DataSet(); 20 da.Fill(ds, tablename); 21 return ds; 22 } 23 catch (Exception ex) 24 { 25 XtraMessageBox.Show(ex.ToString()); 26 return null; 27 } 28 finally 29 { 30 if (con.State == ConnectionState.Open) 31 { 32 con.Close(); 33 } 34 } 35 }
下面就是把table传到数据库,使用表类型接收