Use OLE DB to manipulate excel in ASP.

The recent project needs to export Excel data form, tried several methods, all feel not comfortable, then the eldest brother shouted I went to see OLE DB, I went to the world to learn a bit, it feels pretty good.

began to find some code on the Internet,

Then you need to configure the connection string

Office 2007 installed on my computer:
So the connection string is: "Provider=microsoft.ace.oledb.12.0;data source=" + filepath + "; Extended properties=\ "Excel 12.0; Hdr=yes;\ "";

Hdr=yes indicates that the first row contains column names and does not include the first row when calculating the number of rows

IMEX 0: Import mode, 1: Export mode: 2 Mixed Mode

Run on vs and find an error

Online to find a reason, found that many people have encountered this problem, so the answer is quickly found, the original is not installed "AccessDatabaseEngine.exe" this plugin.

Then to download the installation online, the previous code will be able to run.

Here are some simple action statements:

1. reading data from Excel

String str1= "select * from [sheet1$]";

2. Update the data in Excel

String str2= "Update [sheet1$] set fieldname1= ' AAA ' where filedname2= ' 30 '";

3. Writing data to Excel

String str3= "INSERT into [sheet1$] (fieldname1,fieldname2,...) VALUES (' A ', ' B ',...)";

For Excel tables that are not standard structured, you can use the following methods:

4. Reading data

String str4= "select * from [Sheet1$a3:f20]";

5. Updating data

String str5= "Update [SHEET1$A9:F15] set fieldname= ' BBB ' where anotherfieldname= ' B3 '";

6. Inserting data

String str6= "INSERT into [SHEET1$A9:F15] (fildname1,fieldname2,..) VALUES (' A ', ' B ',...)";

Here are some of my test code:

Writing data to Excel

String strFilePath = "E:\\excel1.xls";        String str1 = "INSERT INTO [sheet1$] (merchant ID, merchant name) VALUES (' DJ001 ', ' click Technology ')",//excel1.xls must already exist, and there are already column names        Doolesql (str1, strFilePath);

Create a new Excel table and write data

String filepaths = "E:\\excel2.xls";        String stra= "CREATE TABLE customerinfo ([CustomerID] varchar,[customer] VarChar)";        String strb = "INSERT INTO CustomerInfo (Customerid,customer) VALUES (' DJ001 ', ' click Technology ')";        Doolesql (stra,filepaths);        Doolesql (strb,filepaths);

The Doolesql functions in the above two codes are as follows:

protected void Doolesql (String sql, string filepath)    {        OleDbConnection conn = new OleDbConnection ();        Conn. ConnectionString = "Provider=microsoft.ace.oledb.12.0;data source=" + filepath + "; Extended properties=\ "Excel 12.0; Hdr=yes;\ "";        Try        {//Open connection            Conn. Open ();        }        catch (Exception e)        {            //response.write (e.tostring ());        }        OleDbCommand Olecommand = new OleDbCommand (SQL, conn);        Try        {//EXECUTE statement            Olecommand. ExecuteNonQuery ();        }        catch (Exception eee)        {            //response.write (eee. ToString ());            Conn. Close ();        }        Finally        {            Conn. Close ();//Shut Down database        }        Conn. Close ();    }

Export data from a DataTable to Excel

protected void Button3_Click (object sender, EventArgs e) {sqlhelp Sqla = new Sqlhelp ();        String Strfaca = "SELECT * from Toapower ORDER by LoginID ASC"; DataTable DTA = Sqla.        Getdatatable (Strfaca); Sqla.        SQLClose ();        String Modelpath = "E:\\staff.xlsx";        String Realpath = "E:\\staff.xls";    Datatable2excel (dta,realpath,500); }///<summary>////////<param Name= "DataTable" > Data Source < /param>//<param name= "FileName" > The path to save </param>//<param name= "Rowscount" > When a worksheet has the highest number rows Count, when it is over, creates a new worksheet.  </param>///<returns> Generate success returns TRUE, otherwise false</returns> public static bool Datatable2excel (DataTable DataTable, String fileName, int rowscount) {bool RT = false;//for return value if (dataTable = = null && ro        Wscount < 1) {return false; } int rowNum = datatable.rows.count;//Gets the number of rows inT colnum = datatable.columns.count;//Gets the number of columns int sheetnum = (rowNum-1)/Rowscount + 1; Gets the number of worksheets string sqltext = "";//Column name with type string sqlvalues = "";//value string colcaption = "";//Column name fo                R (int i = 0; i < Colnum; i++) {if (I! = 0) {SQLText + = ",";            Colcaption + = ","; } SQLText + = "[" + datatable.columns[i]. Caption.tostring () + "] varchar";//Generate the title with VarChar column colcaption + = "[" + datatable.columns[i]. Caption.tostring () + "]";//Generate column header} String sConnectionString = "Provider=microsoft.ace.oledb.12.0;data sourc E= "+ FileName +"; Extended properties=\ "Excel 12.0;        Hdr=yes;\ "";        OleDbConnection cn = new OleDbConnection (sConnectionString); try {//To determine if the file exists, first delete if (File.exists (FileName)) {File.delete (fi            Lename);   } int sheet = 1;//number of tables int dbrow = 0;//data rows         Open the connection CN.            Open (); while (sheet <= sheetnum) {string sqlcreate = "CREATE TABLE [sheet" + sheet.                ToString () + "] (" + SQLText + ")";                OleDbCommand cmd = new OleDbCommand (Sqlcreate, CN); Create the Excel file cmd.                ExecuteNonQuery ();                    for (int srow = 0; srow < Rowscount; srow++) {sqlvalues = "";                        for (int col = 0; col < colnum; col++) {if (col! = 0)                        {sqlvalues + = ","; } sqlvalues + = "'" + datatable.rows[dbrow][col]. ToString () + "'";//concatenation of the value statement} String queryString = "INSERT into [Sheet" + Sheet.                    ToString () + "] (" + colcaption + ") VALUES (" + sqlvalues + ")";                    Cmd.commandtext = queryString; Cmd. ExecuteNonQuery ();/Insert Data dbrow++;//the number of rows in the current data increment if (Dbrow >= rowNum) {                    The current number of rows of data equals rownum when exiting the loop break;            }} sheet++;        } RT = true; } catch {} finally {CN.        Close ();    } return RT;   }

The general situation is this way, I still have some small problems, such as the export of Excel documents, not the strict meaning of the Excel document, when opened this will happen

If you have a solution, please advise OH.

Use OLE DB to manipulate excel in ASP.