Exporting data to excel in C#dataset

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Web;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Data;
Using System.Reflection;
Using Microsoft.Office;
Using Excel = Microsoft.Office.Interop.Excel;
Using Excel;

<summary>
Summary description of Inoutinfotoexcel
</summary>
public class Inoutinfotoexcel
{
Public Inoutinfotoexcel ()
{
//
TODO: Add constructor logic here
//
}

<summary>
Export Excel single sheet (the exported Excel file name can only be in English, Chinese words are garbled and only one sheet can be exported)
</summary>
<param name= "DS" ></param>
<param name= "FileName" ></param>
public void Toexcel (DataSet ds, String FileName)
{
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = system.text.encoding.utf8;//Set output stream to Simplified Chinese
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding ("GB2312");
HttpContext.Current.Response.ContentType = "Application/ms-excel";//Set Output file type to excel file
HttpContext.Current.Response.AppendHeader ("Content-disposition", "attachment;filename=" + "+" + filename);
Format numbers as text
String strstyle = "<style>td{mso-number-format:\" \\@\ ";} </style> ";
System.IO.StringWriter tw = new System.IO.StringWriter ();//define StringWriter Output Object
HtmlTextWriter HW = new HtmlTextWriter (TW);//define HtmlTextWriter object
Ctl. RenderControl (HW);//output Excel using the RenderControl method
DataGrid dg = new DataGrid ();
Dg. DataSource = ds. Tables[0];
Dg. DataBind ();
Dg. RenderControl (HW);
Hw. WriteLine (Strstyle);
GridView GV = new GridView ();
Gv. DataSource = ds. Tables[0];
Gv. DataBind ();
Gv. RenderControl (HW);
HttpContext.Current.Response.Write (TW. ToString ());//Output
HttpContext.Current.Response.Flush ();
HttpContext.Current.Response.End ();
}
<summary>
Export an Excel file (multiple sheet)
</summary>
<param name= "DV" > dataset[array for export]</param>
<param name= "Tmpexpdir" > The virtual path of the exported folder (a folder is exported to this folder in the program)/</param>
<param name= "Reffilename" > file name, for example test.xls</param>
<param name= the name of "SheetName" >sheet If you export multiple sheet[several leases]</param>
<param name= "Sheetsize" > The number of data rows each sheet contains, excluding header rows. So, for 65536 rows of data, set this value to 65535</param>
<param name= "Setborderline" > whether to add border lines to the data after the export is complete </param>
public bool Webexporttoexcel_1 (dataset[] DV, String tmpexpdir, String reffilename, string[] sheetname, int sheetsize, BOOL Setborderline)
{
int Rowstodividesheet = sheetsize;//calculates the number of sheet rows
int sheetcount = dv. Length;
Gc. Collect ();//Recycle other rubbish
Application Excel; _workbook xBk; _worksheet xSt = null;

Excel = new Excel.applicationclass (); XBk = Excel. Workbooks.Add (TRUE);
Declare the variables to be used in the loop
int dvrowstart = 0;
int dvrowend; int rowIndex = 0; int colindex = 0;
Operate on all sheet
for (int sheetindex = 0; Sheetindex < sheetcount; sheetindex++)
{
RowIndex = 1; Colindex = 1; Set the initialized rows and columns
Calculate Start line
Dvrowstart = 1; Sheetindex * ROWSTODIVIDESHEET;
Calculate End Line
Dvrowend = Rowstodividesheet; Dvrowstart + RowsToDivideSheet-1;
if (Dvrowend > Dv[sheetindex]. Tables[0]. Rows.Count)
{dvrowend = Dv[sheetindex]. Tables[0]. Rows.Count + 1; }
Create a sheet
if (null = = XSt)
{xSt = (excel._worksheet) xBk.Worksheets.Add (Type.Missing, Type.Missing, 1, type.missing);}
else {xSt = (excel._worksheet) xBk.Worksheets.Add (Type.Missing, xSt, 1, type.missing);}
Set SheetName
Xst.name = null;
Xst.name = Sheetname[sheetindex]. ToString ();
if (Sheetcount > 1)
{Xst.name + = "1";}
Get title
foreach (DataColumn col in Dv[sheetindex]. Tables[0]. Columns)
{//Set title format
Xst.get_range (Excel. Cells[rowindex, Colindex], Excel. Cells[rowindex, Colindex]). HorizontalAlignment = Xlvalign.xlvaligncenter;
Set Title Center alignment
Xst.get_range (Excel. Cells[rowindex, Colindex], Excel. Cells[rowindex, Colindex]). Font.Bold = true;
Fill in the values and proceed to the next column
Excel. Cells[rowindex, colindex++] = Col. ColumnName;
}
Get data from a dataset table
int drvindex;
for (Drvindex = Dvrowstart; Drvindex <= dvRowEnd-1; drvindex++)
//{
New row, current cell moved to beginning of line
rowindex++;
Colindex = 1;
for (int i = 1; I <= Dv[sheetindex]. Tables[0]. Columns.count; i++)
//    {
String AA = Dv[sheetindex]. Tables[0]. ROWS[0][I-1]. ToString ();
Excel. Cells[rowindex, Colindex] = "'" + AA + "";
colindex++;
//    }
//}
The following code is corrected. There is a problem with the code commented above.
foreach (DataRow Dr in Dv[sheetindex]. Tables[0]. Rows)
{
New row, current cell moved to beginning of line
rowindex++;
Colindex = 1;
for (int i = 1; I <= Dv[sheetindex]. Tables[0]. Columns.count; i++)
{
String AA = Dr[i-1]. ToString ();
Excel. Cells[rowindex, Colindex] = "'" + AA + "";
colindex++;
}
}
Excel.Range alldatawithtitlerange = Xst.get_range (Excel. Cells[1, 1], Excel. Cells[rowindex, Colindex]);
Alldatawithtitlerange.select ();
AllDataWithTitleRange.Columns.AutoFit ();
if (setborderline)
            { AllDataWithTitleRange.Borders.LineStyle = 1; }

}//sheet Cycle End
String absfilename = HttpContext.Current.Server.MapPath (System.IO.Path.Combine (Tmpexpdir, reffilename));
Xbk.savecopyas (Absfilename); Xbk.close (false, NULL, NULL);
Excel. Quit ();
System.Runtime.InteropServices.Marshal.ReleaseComObject (XBK);
System.Runtime.InteropServices.Marshal.ReleaseComObject (Excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (xSt);
XBk = null; Excel = null; XSt = null; Gc. Collect ();
return true;
}
}

Exporting data to excel in C#dataset