Exporting data to excel in C#dataset

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.