3 Ways to export database data with Excel

There are 3 main ways to export database data in Excel: Using Excel.Application interface, OLE DB, HTML tabel tags

Method 1--excel.application Interface:

First, you need to Excel.dll this file to make sure that you have MS Office installed on your machine, in the Office installation directory (.. /microsoft office/office11/, the specific directory depends on its own installation) found in Excel.exe, and then put in the (.. /microsoft Visual Studio 8/sdk/v2.0/bin) directory, enter "CD c:/program files/microsoft Visual Studio 8/sdk/v2.0/in cmd Bin (The specific directory depends on your installation) ", and then enter" TlbImp EXCEL. EXE Excel.dll ". The Excel.dll file can then be found in the Bin folder. It can be referenced in the project by then.

To use namespaces

Using Excel;

Specific code:

/// <summary>///export data from SQL Server to Excel (using an Excel class that is not valid when Office is not installed)/// </summary>/// <param name= "Exeldt" >Datasets to export</param>/// <param name= "FileName" >output to the file directory</param> Public Static voidSqlservertoexcel (DataSet Exeldt,stringfileName) {    intColindex =1, RowIndex =1;    Excel.Application Excel;    Workbook Wbook;    Worksheet Wsheet; Try{Excel=NewExcel.Application (); Wbook= Excel. APPLICATION.WORKBOOKS.ADD (true); Wsheet= wbook.worksheets[1] asWorksheet; //Excel. Visible = true;    }    Catch{Win32.msgbox (0,"you may not have Office installed, please install and then use this feature","",0); return; }    Try    {        foreach(DataColumn Colinchexeldt.tables[0]. Columns) {wsheet.cells[1, Colindex] = Col. ColumnName; colindex++; }        foreach(DataRow rowinchexeldt.tables[0]. Rows) {RowIndex++; Colindex =0; foreach(DataColumn Colinchexeldt.tables[0]. Columns) {Colindex++; if(Colindex = =1) {wsheet.cells[rowindex, Colindex]="'"+Row[col. ColumnName].                ToString (); }                Else{wsheet.cells[rowindex, Colindex]=Row[col. ColumnName].                ToString (); }            }        }        //set up a query prompt to suppress pop-up saves and overridesExcel. DisplayAlerts =false; Excel. Alertbeforeoverwriting=false; //SaveWsheet.saveas (FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,        Missing.Value, Missing.Value, Missing.Value, Missing.Value);    Wbook.save (); }    Catch(System.Exception) {Win32.msgbox (0,"output Excel has errors, make sure that Excel is not closed","",0); return; }    finally{Excel.    Quit (); }}

One of the things to note is:

Wsheet.saveas (FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value,             Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Wbook.save ();

This will not bring up the Save dialog box when you save it.

Remember to close Excel, or you'll always have

finally {    Excel. Quit ();}

Method 2--with OLE DB:

I think it's best to use OLE DB because you don't have to install Office, you can export Excel, and it's faster.

Specific code:

/// <summary>///export data from SQL Server to Excel (using OLE DB)/// </summary>/// <param name= "Exeldt" >Datasets to export</param>/// <param name= "FilePath" >output to the file directory</param>/// <returns>Information</returns> Public Static stringSqlservertoexcel (System.Data.DataTable Exeldt,stringFilePath) {    if(Exeldt = =NULL)    {        return "The data cannot be empty"; }    //total number of rows and columns of the dataset    introws =ExelDt.Rows.Count; intcols =ExelDt.Columns.Count; if(Rows = =0)    {        return "No Data"; } StringBuilder SB=NewStringBuilder (); stringStrcon ="Provider = microsoft.jet.oledb.4.0; Data Source ="+ FilePath +"; Extended Properties=excel 8.0"; OleDbCommand cmd=NewOleDbCommand (); OleDbConnection myconn=NewOleDbConnection (Strcon); Try    {        //Create a fileMyConn.Open (); //Create a tableCmd. Connection =myconn; Sb. Append ("CREATE TABLE"); Sb. Append (Exeldt.tablename+"(");  for(inti =0; i < cols; i++)        {            if(I < cols-1) {sb. Append (string. Format ("{0} varchar,", Exeldt.columns[i].            ColumnName)); }            Else{sb. Append (string. Format ("{0} varchar)", Exeldt.columns[i].            ColumnName)); }} Cmd.commandtext=sb.        ToString (); Cmd.    ExecuteNonQuery (); }    Catch(Exception ex) {return "failed to build Exel file:"+Ex.    ToString (); }     //---------------------------------------------------------------------------------     //Inserting DataSb. Remove (0, sb.     Length); Sb. Append ("INSERT into"); Sb. Append (Exeldt.tablename+" ( ");  for(inti =0; i < cols; i++)    {        if(I < cols-1) sb. Append (Exeldt.columns[i]. ColumnName+","); Elsesb. Append (Exeldt.columns[i]. ColumnName+") VALUES ("); }      for(inti =0; i < cols; i++)    {        if(I < cols-1) sb. Append ("@"+ Exeldt.columns[i]. ColumnName +","); Elsesb. Append ("@"+ Exeldt.columns[i]. ColumnName +")"); } Cmd.commandtext=sb.    ToString (); OleDbParameterCollection param=cmd.     Parameters;  for(inti =0; i < cols; i++) {param. ADD (NewOleDbParameter ("@"+Exeldt.columns[i].    ColumnName, OleDbType.VarChar)); }      //traverse a DataTable to insert data into a new Excel file    foreach(DataRow rowinchexeldt.rows) { for(inti =0; I < Param. Count; i++) {Param[i]. Value=Row[i]; } cmd.    ExecuteNonQuery (); } cmd.     Connection.close (); return "data has been successfully imported into Excel";}

Among them note:

string " ""; Extended Properties=excel 8.0";

where "Excel 8.0" is indicated using MS Office 2003, the other versions are not used, and are unsure whether you can just modify the version number.

Method 3--with the HTML tabel tag:

This specific I did not try to achieve, here is a thought, the data will be exported with <tabel>, <tr>, <td> these several tags output to an HTML file, and then change the extension to. xls.

3 Ways to export database data with Excel