C # implement Access Import and Export Excel

1. Access imports data from Excel. 1. OleDbConnectionconnewOleDbConnection (); try {OpenFileDialogopenFilenewOpenFileDialog (); opens the file dialog box. OpenFile. Filter (Excel file (*. xls) | *. xls); suffix

1. Access to import data from Excel 1. oleDbConnection con = new OleDbConnection (); try {OpenFileDialog openFile = new OpenFileDialog (); // open the file dialog box. OpenFile. Filter = ("Excel file (*. xls) | *. xls"); // suffix

1. Access from ExcelImportData

1. the format and content of the Excel table used

Implementation

OleDbConnection con = new OleDbConnection (); try {OpenFileDialog openFile = new OpenFileDialog (); // open the file dialog box. OpenFile. Filter = ("Excel file (*. xls) | *. xls"); // suffix. If (openFile. showDialog () = DialogResult. OK) {string filename = openFile. fileName; int index = filename. lastIndexOf ("//"); // capture the file name filename = filename. substring (index + 1); conExcel. connectionString = "Provider = Microsoft. jet. oledb.4.0; Data Source = "+ Application. startupPath + "// Appdata. mdb "; // import an excel file <strong> </strong> access // distinct: delete duplicate rows in an excel file. // [excel name]. [sheet name] add $ // where no to an existing excel table T in: insert records that are not repeated. String SQL = "insert into Users2 (User ID, user name) select distinct * from [Excel 8.0; database =" + filename + "]. [name $] where user no. not in (select User No. from Users2) "; OleDbCommand com = new OleDbCommand (SQL, con); con. open (); com. executeNonQuery (); MessageBox. show ("<strong> imported </strong> data succeeded", "<strong> imported </strong> data", MessageBoxButtons. OK, MessageBoxIcon. information) ;}} catch (Exception ex) {MessageBox. show (ex. toString ();} finally {con. close ();}

Ii. AccessExportExcel

OleDbConnection con = new OleDbConnection (); try {SaveFileDialog saveFile = new SaveFileDialog (); saveFile. filter = ("Excel file (*. xls) | *. xls "); // specify the file suffix as an Excel file. If (saveFile. showDialog () = DialogResult. OK) {string filename = saveFile. fileName; if (System. IO. file. exists (filename) {System. IO. file. delete (filename); // Delete a file if it exists.} Int index = filename. lastIndexOf ("//"); // obtain the last/index filename = filename. substring (index + 1); // obtain the excel name (the path of the new table to the SaveFileDialog path) // select * into create a new table. // [[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. // sheet can store up to 65535 data records. String SQL = "select top 65535 * into [Excel 8.0; database =" + filename + "]. [user information] from Users2 "; con. connectionString = "Provider = Microsoft. jet. oledb.4.0; Data Source = "+ Application. startupPath + "// Appdata. mdb "; // put the database under the debug directory. OleDbCommand com = new OleDbCommand (SQL, con); con. open (); com. executeNonQuery (); MessageBox. show ("<strong> exported </strong> data succeeded", "<strong> exported </strong> data", MessageBoxButtons. OK, MessageBoxIcon. information) ;}} catch (Exception ex) {MessageBox. show (ex. toString ();} finally {con. close ();}