Export an Excel worksheet to import multiple datatables of a dataset to multiple sheets of an Excel worksheet.

/// <Summary>
/// Export an Excel worksheet to import multiple datatables of a dataset to multiple sheets of an Excel worksheet.
/// </Summary>
/// <Param name = "ds"> dataset </param>
/// <Param name = "tablenames"> name of each table in DS </param>
/// <Param name = "strexcelfilename"> export the excelname (yyyy-mm-dd.xls) </param>
Public void doexport (Dataset ds, string [] tablenames, string strexcelfilename)
{
Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();
Try
{
Excel. Visible = false;
// Set to prohibit the pop-up prompt box for saving and overwriting
Excel. displayalerts = false;
Excel. alertbeforeoverwriting = false;
// Add a workbook
Workbook book = excel. workbooks. Add (true );
// Add a worksheet
Worksheet sheets = (Microsoft. Office. InterOP. Excel. worksheet)
Book. worksheets. Add (missing. Value, missing. Value, 19, Microsoft. Office. InterOP. Excel. xlsheettype. xlworksheet );

For (INT I = 0; I <Ds. Tables. Count; I ++)
{
System. Data. datatable table = Ds. Tables [I];
// Obtain a worksheet
Worksheet sheet = book. worksheets [I + 1] As worksheet;
Int rowindex = 1;
Int colindex = 0;

Foreach (datacolumn Col in table. columns)
{
Colindex ++;
Sheet. cells [1, colindex] = col. columnname;
}
Foreach (datarow row in table. Rows)
{
Rowindex ++;
Colindex = 0;
Foreach (datacolumn Col in table. columns)
{
Colindex ++;
// Add a single quotation mark before a number.
String typename = row [col. columnname]. GetType (). tostring ();
Sheet. cells [rowindex, colindex] = typecheckadd (row [col. columnname]. tostring (), typename );
}
}
Sheet. Name = tablenames [I];
}

// Delete extra Sheet
For (int g = 1; G <= book. worksheets. Count; G ++)
{
Worksheet sheet = book. worksheets [g] As worksheet;
If (sheet. Name. substring (0, 5) = "sheet ")
{
Sheet. Delete ();
G --;
}
}
Book. Save ();
Book. saveas (strexcelfilename, missing. Value, missing. value,
Microsoft. Office. InterOP. Excel. xlsaveasaccessmode. xlnochange, missing. Value, missing. value );

Excel. Quit ();
Excel = NULL;
GC. Collect ();

Savexls (strexcelfilename); // save it to the selected path
}
Catch (exception E)
{
Response. Write (E. Message );
}
}
# If region is a large number, it must be enclosed in leading quotation marks to convert it into a string.
Public String typecheckadd (string cellcontent, string strtype)
{
String cellcontentadd;
Switch (strtype)
{
Case "system. String ":
Cellcontentadd = "'" + cellcontent;
Break;
Default:
Cellcontentadd = cellcontent;
Break;
}
Return cellcontentadd;
}
# Endregion
# Region Save the generated Excel file to the selected path
Protected void savexls (string fileurl)
{
System. Io. fileinfo = new system. Io. fileinfo (fileurl );
Response. Clear ();
Response. charset = "gb2312 ";
Response. contentencoding = system. Text. encoding. utf8;
Response. addheader ("content-disposition", "attachment; filename =" + server. urlencode (fileinfo. Name. tostring ()));
Response. addheader ("Content-Length", fileinfo. length. tostring ());
Response. contenttype = "application/octet-stream ";
Response. contentencoding = system. Text. encoding. default;
Response. writefile (fileurl );
}
# Endregion

For (INT I = 0; I <Ds. Tables. Count; I ++)
{
System. Data. datatable table = Ds. Tables [I];
// Obtain a worksheet
Worksheet sheet = book. worksheets [I + 1] As worksheet;
Int rowindex = 1;
Int colindex = 0;

Foreach (datacolumn Col in table. columns)
{
Colindex ++;
Sheet. cells [1, colindex] = col. columnname;
}
Foreach (datarow row in table. Rows)
{
Rowindex ++;
Colindex = 0;
Foreach (datacolumn Col in table. columns)
{
Colindex ++;
// Add a single quotation mark before a number.
String typename = row [col. columnname]. GetType (). tostring ();
Sheet. cells [rowindex, colindex] = typecheckadd (row [col. columnname]. tostring (), typename );
}
}
Sheet. Name = tablenames [I];
}

// Delete extra Sheet
For (int g = 1; G <= book. worksheets. Count; G ++)
{
Worksheet sheet = book. worksheets [g] As worksheet;
If (sheet. Name. substring (0, 5) = "sheet ")
{
Sheet. Delete ();
G --;
}
}
Book. Save ();
Book. saveas (strexcelfilename, missing. Value, missing. value,
Microsoft. Office. InterOP. Excel. xlsaveasaccessmode. xlnochange, missing. Value, missing. value );

Excel. Quit ();
Excel = NULL;
GC. Collect ();

Savexls (strexcelfilename); // save it to the selected path
}
Catch (exception E)
{
Response. Write (E. Message );
}
}
# If region is a large number, it must be enclosed in leading quotation marks to convert it into a string.
Public String typecheckadd (string cellcontent, string strtype)
{
String cellcontentadd;
Switch (strtype)
{
Case "system. String ":
Cellcontentadd = "'" + cellcontent;
Break;
Default:
Cellcontentadd = cellcontent;
Break;
}
Return cellcontentadd;
}
# Endregion
# Region Save the generated Excel file to the selected path
Protected void savexls (string fileurl)
{
System. Io. fileinfo = new system. Io. fileinfo (fileurl );
Response. Clear ();
Response. charset = "gb2312 ";
Response. contentencoding = system. Text. encoding. utf8;
Response. addheader ("content-disposition", "attachment; filename =" + server. urlencode (fileinfo. Name. tostring ()));
Response. addheader ("Content-Length", fileinfo. length. tostring ());
Response. contenttype = "application/octet-stream ";
Response. contentencoding = system. Text. encoding. default;
Response. writefile (fileurl );
}
# Endregion