Export data to Excel using OLE DB

Original address: Http://blog.csdn.net/cpp2017/archive/2008/04/02/2245396.aspx

The use of OELDB data Access objects to manipulate Excel files, to achieve the purpose of exporting data to Excel, this method may use a few people, the disadvantage is that there must be an existing Excel file. The advantage is that the server does not need to install Excel, and the generated Excel is a standard Excel format that can be used as a data source.

Preparatory work:

1) First prepare an Excel file, the Excel file can be empty, or have a certain format as a data template to use.

2) Write permissions to the ASP. NET user (ASPNET or network SERVICE) to the directory of the operation.

The following is an example:

Note: If this template file is an empty file, you will need to create a worksheet with the creation table, otherwise you can simply use the existing table-in operation.

DataTable dt =NewDataTable (); Dt. Columns.Add ("name"); Dt. Columns.Add (" Age",typeof(int)); Dt. Columns.Add ("Phone"); Dt. Rows.Add ("Lao Zhang", +,"99213812"); Dt. Rows.Add ("Xiao Li", -,"a21313"); Dt. Rows.Add ("Xiao Wang", A,"2131434"); stringFileName = Guid.NewGuid (). ToString () +". xls"; stringSnewfullfile =Server.MapPath (FileName); Try{file.copy (Server.MapPath ("Format.xls"), snewfullfile); }            Catch(Exception er) {Response.Write (Er.                Message); return; }            stringstrconn ="provider=microsoft.jet.oledb.4.0; Persist Security info=true;data source="+ Snewfullfile +"; Extended Properties=excel 8.0;"; System.Data.OleDb.OleDbConnection Conn=NewSystem.Data.OleDb.OleDbConnection (strconn); OleDbCommand cmd=NULL; BOOLBRet =false; Try{Conn.                                Open (); CMD=NewOleDbCommand ("CREATE TABLE [Sheet4] ([name] text,[age] int,[phone] Text)", conn); Cmd.                ExecuteNonQuery (); stringstrSQL ="INSERT into [sheet4$] ([name], [age],[phone]) VALUES (?,?,?)"; CMD=NewOleDbCommand (strSQL, conn);  for(inti =0; I <3; i++) {cmd.                Parameters.Add (i.ToString (), OleDbType.VarChar); } DataView DV=dt.                DefaultView; foreach(DataRowView rowinchdv) {cmd. parameters[0]. Value = row["name"].                    ToString (); Cmd. parameters[1]. Value = (int) row[" Age"]; Cmd. parameters[2]. Value = row["Phone"].                    ToString (); Cmd.                                     ExecuteNonQuery (); } BRet=true; }            Catch(Exception er) {Response.Write (Er.            Message); }            finally            {                if(cmd! =NULL) {cmd.                Dispose (); } conn.                            Dispose (); }            if(BRet) Response.Redirect (FileName);

Export data to Excel using OLE DB