There are two ways for datatable download as Excel

Using system;
Using Excel = Microsoft. Office. InterOP. Excel;
Using system. reflection;
Using system. Data;
Using system. Web;
Using system. text;
Using system. IO;
Using Microsoft. Office. InterOP. Excel;

Namespace CCC. Utility
{
/// <Summary>
/// Summary description for generatereportexcel.
/// </Summary>
Public class generatereportexcel
{
Public generatereportexcel ()
{
//
// Todo: Add constructor logic here
//
}

Public static void datatable2excel (system. Data. datatable DT)
{
String filename = datetime. Now. tostring ("yyyymmddhhmmss") + ". xls ";

String strbulidexporthtml = buildexporthtml (DT );

Directoryinfo di = new directoryinfo (".");
String strlogfolder = Di. fullname + "\ output \\";
String strfilename = strlogfolder + filename;

Streamwriter Sw = new streamwriter (strfilename, false, system. Text. encoding. Default );
Sw. Write (strbulidexporthtml );
Sw. Flush ();
Sw. Close ();

}

/// <Summary>
///
/// </Summary>
/// <Param name = "DT"> </param>
/// <Returns> </returns>
Public static string buildexporthtml (system. Data. datatable DT)
{
String result = string. empty;
Int readcnt = DT. Rows. count;
Int colcount = DT. Columns. count;

Int pagerecords = 50000;
Result = "<? XML version = \ "1.0 \" encoding = \ "gb2312 \"?> ";
Result + = "<? MSO-application progid = \ "Excel. Sheet \"?> ";
Result + = "<workbook xmlns = \" urn: Schemas-Microsoft-com: Office: spreadsheet \"";
Result + = "xmlns: O = \" urn: Schemas-Microsoft-com: Office \"";
Result + = "xmlns: x = \" urn: Schemas-Microsoft-com: Office: Excel \"";
Result + = "xmlns: Ss = \" urn: Schemas-Microsoft-com: Office: spreadsheet \"";
Result + = "xmlns: html = \" http://www.w3.org/tr/rec-html40\ "> ";

String strtitlerow = "";

Strtitlerow = "<row SS: autofitheight = '0'> ";
For (Int J = 0; j <colcount; j ++)
{
Strtitlerow + = "<cell> <data SS: TYPE = \" string \ ">" + dt. columns [J]. columnname + "</data> </cell> ";
}
Strtitlerow + = "</row> ";

Stringbuilder strrows = new stringbuilder ();

Int page = 1;
Int CNT = 1;
Int sheetcolnum = 0;
For (INT I = 0; I <readcnt; I ++)
{
Strrows. append ("<row SS: autofitheight = \" 0 \ "> ");
For (Int J = 0; j <colcount; j ++)
{

If (Dt. Columns [J]. datatype. Name = "datetime" | DT. Columns [J]. datatype. Name = "smalldatetime ")
{
If (Dt. Rows [I] [J]. tostring ()! = String. Empty)
{
Strrows. append ("<cell> <data SS: TYPE = \" string \ ">" + convert. todatetime (DT. rows [I] [J]. tostring ()). tostring ("mm DD, YYYY") + "</data> </cell> ");
}
Else
Strrows. append ("<cell> <data SS: TYPE = \" string \ "> </data> </cell> ");
}
Else
{
Strrows. append ("<cell> <data SS: TYPE = \" string \ ">" + dt. rows [I] [J]. tostring (). trim () + "</data> </cell> ");
}
}
Strrows. append ("</row> ");
CNT ++;

If (CNT> = pagerecords + 1)
{
Sheetcolnum = CNT + 1;
Result + = "<worksheet SS: Name = \" sheet "+ Page. tostring () + "\"> <Table SS: expandedcolumncount = \ "" + colcount. tostring () + "\" SS: expandedrowcount = \ "" + sheetcolnum. tostring () + "\" X: fullcolumns = \ "1 \" X: fullrows = \ "1 \" SS: defacolumcolumnwidth = \ "104 \" SS: defaultrowheight = \ "13.5 \"> "+ strtitlerow. tostring () + strrows. tostring () + "</table> </worksheet> ";
Strrows. Remove (0, strrows. Length );
CNT = 1;
Page ++;

}
}
Sheetcolnum = CNT + 1;
Result = Result + "<worksheet SS: Name = 'sheet" + Page. tostring () + "'> <Table SS: expandedcolumncount ='" + colcount. tostring () + "'ss: expandedrowcount = '" + sheetcolnum. tostring () + "'X: fullcolumns = '1' X: fullrows = '1' SS: defaultcolumnwidth = '000000' SS: defaultrowheight = '13. 5 '> "+ strtitlerow. tostring () + strrows. tostring () + "</table> </worksheet> </workbook> ";
Return result;
}

Public static void datatabletoexcel (system. Data. datatable tmpdatatable)

{
String filename = datetime. Now. tostring ("yyyymmddhhmmss") + ". xls ";

Directoryinfo di = new directoryinfo (".");
String strlogfolder = Di. fullname + "\ output \\";
String strfilename = strlogfolder + filename;

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 );

}

}
}