C # implement access Import and Export Excel

I, AccessSlaveExcelImport Data

1.UsedExcelTable format and content

 

Implementation

Oledbconnection con = new oledbconnection (); <br/> try <br/> {<br/> openfiledialog openfile = new openfiledialog (); // open the file dialog box. <Br/> openfile. Filter = ("Excel file (*. xls) | *. xls"); // suffix. <Br/> If (openfile. showdialog () = dialogresult. OK) <br/>{< br/> string filename = openfile. filename; <br/> int Index = filename. lastindexof ("//"); // name of the captured file <br/> filename = filename. substring (index + 1); <br/> conexcel. connectionstring = "provider = Microsoft. jet. oledb.4.0; Data Source = "+ <br/> application. startuppath + "// appdata. mdb "; <br/> // import EXCEL to access <br/> // distinct: delete duplicate rows in Excel. <br/>/ /[Excel name]. [sheet name] $ <br/> // where not in: inserts records that are not repeated. </P> <p> string SQL = "insert into users2 (User ID, user name) Select distinct * from [Excel 8.0; database = "+ <br/> filename +"]. [name $] Where user no. Not in (Select User No. From users2) "; <br/> oledbcommand COM = new oledbcommand (SQL, con); <br/> con. open (); <br/> COM. executenonquery (); <br/> MessageBox. show ("imported data succeeded", "imported data", messageboxbuttons. OK, messageboxicon. information); <br/>}< br/> catch (exception ex) <br/>{< br/> MessageBox. show (ex. tostring (); <br/>}< br/> finally <br/> {<br/> con. close (); <br/>}

 

2. Access to export Excel

Oledbconnection con = new oledbconnection (); <br/> try <br/>{< br/> savefiledialog SaveFile = new savefiledialog (); <br/> SaveFile. filter = ("Excel file (*. XLS) | *. xls "); // specify the file suffix as an Excel file. <Br/> If (SaveFile. showdialog () = dialogresult. OK) <br/>{< br/> string filename = SaveFile. filename; <br/> If (system. io. file. exists (filename) <br/>{< br/> system. io. file. delete (filename); // delete a file if it exists. <Br/>}< br/> int Index = filename. lastindexof ("//"); // obtain the last/index <br/> filename = filename. substring (index + 1); // obtain the Excel name (the path of the new table to the savefiledialog path) <br/> // select * into create a new table. <Br/> // [[Excel 8.0; database = Excel name]. [sheet name] $ is not allowed for creating a new sheet table, and $ is required for inserting data into the sheet table. <br/> // sheet can store up to 65535 data records. <Br/> string SQL = "select top 65535 * into [Excel 8.0; database =" + filename + "]. [user information] From users2 "; <br/> con. connectionstring = "provider = Microsoft. jet. oledb.4.0; Data Source = "+ application. startuppath + "// appdata. mdb "; // put the database under the DEBUG directory. <Br/> oledbcommand COM = new oledbcommand (SQL, con); <br/> con. open (); <br/> COM. executenonquery (); </P> <p> MessageBox. show ("exported data", "exported data", messageboxbuttons. OK, messageboxicon. information); <br/>}< br/> catch (exception ex) <br/>{< br/> MessageBox. show (ex. tostring (); <br/>}< br/> finally <br/> {<br/> con. close (); <br/>}