Datatable export to excel

DatatableExportExcel

Many datatable export Excel files can be found onlineArticleBut there are not many people to implement! I have debugged and solved the problem. Now I want to share with you the following:

I. Goals:

A complete Excel table with the field name and content exported from a memory table datatable

II. Implementation steps:

1. Add reference:

This is a very important step. Many people do not debug Chengdu because this step is not done well:

You need to add a com reference in your solution and select "Microsoft Excel .... "(varies by version), paving the way for referencing the Excel-related namespaces below;

I used Excel 2007. After adding com references, for example:

 

Two referenced files are added!

2. namespace reference:

Add the following reference content:

Using Microsoft. Office. InterOP. Excel;

3. define functions:

Public static void datatabletoexcel (system. Data. datatable tmpdatatable, string strfilename)

{

If (tmpdatatable = NULL)

Return;

Int rownum = tmpdatatable. Rows. count;

Int columnnum = tmpdatatable. Columns. count;

Int rowindex = 1;

Int columnindex = 0;

Application xlapp = new applicationclass ();

Xlapp. defaultfilepath = "";

Xlapp. displayalerts = true;

Xlapp. sheetsinnewworkbook = 1;

Workbook xlbook = xlapp. workbooks. Add (true );

// Import the column name of datatable into the first row of the Excel table

Foreach (datacolumn DC in tmpdatatable. columns)

{

Columnindex ++;

Xlapp. cells [rowindex, columnindex] = Dc. columnname;

}

// Import data in datatable to excel

For (INT I = 0; I <rownum; I ++)

{

Rowindex ++;

Columnindex = 0;

For (Int J = 0; j <columnnum; j ++)

{

Columnindex ++;

Xlapp. cells [rowindex, columnindex] = tmpdatatable. Rows [I] [J]. tostring ();

}

}

// Xlbook. savecopyas (httputility. urldecode (strfilename, system. Text. encoding. utf8 ));

Xlbook. savecopyas (strfilename );

}

4. Example:

System. Data. datatable dt = ......; // Prepare your datatable

Datatabletoexcel (DT, "C :\\\ China. xls"); // call a custom function. Of course, you can write the output file as needed.

Iii. test environment:

Vs2008, Excel 2007

In fact, Excel versions 2003 and 2007 do not matter, mainly because the library file version you added is not suitable