C # Data import/export Excel file and WinForm Export execl Summary _ Practical Tips

First, the method of exporting execl in asp.net:

There are two ways to export execl in ASP.net, which is to store the exported files under a server folder, and then export the file addresses to the browser, and the file output stream directly to the browser. When response output, T-delimited data, when exporting execl, is equivalent to a column, \ n is equivalent to wrapping.

1, the entire HTML output EXECL

This method outputs all the content in HTML, such as buttons, tables, pictures, and so on to Execl.

Copy Code code as follows:

Response.Clear ();
Response.buffer= true;
Response.appendheader ("Content-disposition", "Attachment;filename=" +datetime.now.tostring ("yyyyMMdd") + ". xls");
Response.contentencoding=system.text.encoding.utf8;
Response.ContentType = "application/vnd.ms-excel";
This. EnableViewState = false;

Here we take advantage of the ContentType property, its default property is text/html, then output to hypertext, that is, our common Web page format to the client, if changed to Ms-excel will output Excel format, that is, in the format of the spreadsheet output to the client, Then the browser will prompt you to download the save. The ContentType properties also include: Image/jpeg;text/html;image/gif;vnd.ms-excel/msword. Similarly, we can export (export) pictures, Word documents, and so on. This property is also used for the following methods.

2. Export data from the DataGrid control Execl

Although the above method realizes the function of export, it also guides all the output information in the HTML such as button, page box and so on. What we typically export is data, data on the DataGrid control.

Copy Code code as follows:

System.Web.UI.Control Ctl=this. DATAGRID1;
DATAGRID1 is the control you drag and drop on the form
HttpContext.Current.Response.AppendHeader ("Content-disposition", "Attachment;filename=excel.xls");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding =system.text.encoding.default;
HttpContext.Current.Response.ContentType = "Application/ms-excel";
Ctl. Page.enableviewstate =false;
System.IO.StringWriter tw = new System.IO.StringWriter ();
System.Web.UI.HtmlTextWriter HW = new System.Web.UI.HtmlTextWriter (TW);
Ctl. RenderControl (HW);
HttpContext.Current.Response.Write (TW. ToString ());
HttpContext.Current.Response.End ();

If your DataGrid uses pagination, it exports the information from the current page, which is the information displayed in the DataGrid. Instead of all the information for your SELECT statement.

For ease of use, write the following methods:

Copy Code code as follows:

public void Dgtoexcel (System.Web.UI.Control ctl)
{
HttpContext.Current.Response.AppendHeader ("Content-disposition", "Attachment;filename=excel.xls");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding =system.text.encoding.default;
HttpContext.Current.Response.ContentType = "Application/ms-excel";
Ctl. Page.enableviewstate =false;
System.IO.StringWriter tw = new System.IO.StringWriter ();
System.Web.UI.HtmlTextWriter HW = new System.Web.UI.HtmlTextWriter (TW);
Ctl. RenderControl (HW);
HttpContext.Current.Response.Write (TW. ToString ());
HttpContext.Current.Response.End ();
}

Usage: dgtoexcel (DATAGRID1);

3, the data in the dataset to export Execl

With the above idea, is to be exported in the information, output (Response) client, so that it can be exported. Then it is OK to export the data in the dataset, that is, to response the rows in the dataset to the HTTP stream in ms-excel format. Note: The parameter DS should be a dataset populated with a datasheet with a full name, including a suffix name, such as Execl2006.xls

Copy Code code as follows:

public void Createexcel (DataSet ds,string FileName)
{
HttpResponse resp;
RESP = Page.Response;
Resp. ContentEncoding = System.Text.Encoding.GetEncoding ("GB2312");
Resp. Appendheader ("Content-disposition", "attachment;filename=" +filename);
String Colheaders= "", ls_item= "";

Defines a Table object and a row object, and initializes its value with a dataset.
DataTable Dt=ds. Tables[0];
Datarow[] Myrow=dt. Select ();//can resemble dt. Select ("id>10") in the form of data filtering purposes
int i=0;
int CL=DT. Columns.count;


Gets the column headings for the datasheet, with \ t split between the headings, followed by a carriage return after the last column heading
for (i=0;i<cl;i++)
{
if (i== (CL-1))//last column, add \ n
{
Colheaders +=dt. Columns[i]. Caption.tostring () + "\ n";
}
Else
{
Colheaders+=dt. Columns[i]. Caption.tostring () + "T";
}

}
Resp. Write (colheaders);
Writes the obtained data information to the HTTP output stream

Process data line by row
foreach (DataRow row in myrow)
{
The current row data is written to the HTTP output stream and the null Ls_item is placed so that the downlink data
for (i=0;i<cl;i++)
{
if (i== (CL-1))//last column, add \ n
{
Ls_item +=row[i]. ToString () + "\ n";
}
Else
{
Ls_item+=row[i]. ToString () + "\ T";
}

}
Resp. Write (Ls_item);
Ls_item= "";

}
Resp. End ();
}

4, will DataView export execl

This method is useful if you want to achieve a more varied or irregular execl export.

Copy Code code as follows:

public void Outputexcel (DataView dv,string str)
{
DV for data to be exported to Excel, Str is the title name
Gc. Collect ();
Application excel;//= new application ();
int rowindex=4;
int colindex=1;

_workbook XBK;
_worksheet xSt;

Excel= new ApplicationClass ();

XBK = Excel. Workbooks.Add (TRUE);

XSt = (_worksheet) xbk.activesheet;

//
Get title
//
foreach (DataColumn col in DV. Table.columns)
{
colindex++;
Excel. Cells[4,colindex] = Col. ColumnName;
Xst.get_range (Excel. Cells[4,colindex],excel. Cells[4,colindex]). HorizontalAlignment = xlvalign.xlvaligncenter;//formatting headings in center alignment
}

//
Get the data in the table
//
foreach (DataRowView row in DV)
{
RowIndex + +;
Colindex = 1;
foreach (DataColumn col in DV. Table.columns)
{
Colindex + +;
if (Col. DataType = = System.Type.GetType ("System.DateTime"))
{
Excel. Cells[rowindex,colindex] = (Convert.todatetime (row[col). ColumnName]. ToString ())). ToString ("Yyyy-mm-dd");
Xst.get_range (Excel. Cells[rowindex,colindex],excel. Cells[rowindex,colindex]). HorizontalAlignment = xlvalign.xlvaligncenter;//The field format of the date type is centered
}
Else
if (Col. DataType = = System.Type.GetType ("System.String"))
{
Excel. Cells[rowindex,colindex] = "'" +row[col. ColumnName]. ToString ();
Xst.get_range (Excel. Cells[rowindex,colindex],excel. Cells[rowindex,colindex]). HorizontalAlignment = xlvalign.xlvaligncenter;//The field format of the character type is centered
}
Else
{
Excel. Cells[rowindex,colindex] = Row[col. ColumnName]. ToString ();
}
}
}
//
Load an aggregate row
//
int rowsum = RowIndex + 1;
int colsum = 2;
Excel. cells[rowsum,2] = "Total";
Xst.get_range (Excel. Cells[rowsum,2],excel. cells[rowsum,2]). HorizontalAlignment = Xlhalign.xlhaligncenter;
//
Set the color of the selected section
//
Xst.get_range (Excel. Cells[rowsum,colsum],excel. Cells[rowsum,colindex]). Select ();
Xst.get_range (Excel. Cells[rowsum,colsum],excel. Cells[rowsum,colindex]). Interior.ColorIndex = 19;//is set to light yellow with a total of 56
//
Get the title of the entire report
//
Excel. cells[2,2] = str;
//
Set the title format for the entire report
//
Xst.get_range (Excel. Cells[2,2],excel. cells[2,2]). Font.Bold = true;
Xst.get_range (Excel. Cells[2,2],excel. cells[2,2]). Font.Size = 22;
//
Set the report table to fit the width
//
Xst.get_range (Excel. Cells[4,2],excel. Cells[rowsum,colindex]). Select ();
Xst.get_range (Excel. Cells[4,2],excel. Cells[rowsum,colindex]). Columns.AutoFit ();
//
Set the title of the entire report to center across columns
//
Xst.get_range (Excel. Cells[2,2],excel. Cells[2,colindex]). Select ();
Xst.get_range (Excel. Cells[2,2],excel. Cells[2,colindex]). HorizontalAlignment = xlhalign.xlhaligncenteracrossselection;
//
Draw Border
//
Xst.get_range (Excel. Cells[4,2],excel. Cells[rowsum,colindex]). Borders.LineStyle = 1;
Xst.get_range (Excel. Cells[4,2],excel. cells[rowsum,2]). Borders[xlbordersindex.xledgeleft]. Weight = xlborderweight.xlthick;//Set left line bold
Xst.get_range (Excel. Cells[4,2],excel. Cells[4,colindex]). Borders[xlbordersindex.xledgetop]. Weight = xlborderweight.xlthick;//Set top edge Bold
Xst.get_range (Excel. Cells[4,colindex],excel. Cells[rowsum,colindex]). Borders[xlbordersindex.xledgeright]. Weight = xlborderweight.xlthick;//Set the right line bold
Xst.get_range (Excel. Cells[rowsum,2],excel. Cells[rowsum,colindex]). Borders[xlbordersindex.xledgebottom]. Weight = xlborderweight.xlthick;//Set Bottom edge Bold
//
Display effects
//
Excel. Visible=true;

Xst.export (Server.MapPath (".") + "\ \" +this.xlfile.text+ ". xls", Sheetexportactionenum.ssexportactionnone, Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
Xbk.savecopyas (Server.MapPath (".") + "\" +this.xlfile.text+ ". xls");

ds = NULL;
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 ();
String path = Server.MapPath (this.xlfile.text+ ". xls");

System.IO.FileInfo file = new System.IO.FileInfo (path);
Response.Clear ();
Response.charset= "GB2312";
Response.contentencoding=system.text.encoding.utf8;
Add header information, specify a default filename for the file download/Save As dialog box
Response.AddHeader ("Content-disposition", "attachment; Filename= "+ server.urlencode (file. Name));
Add header information, specify file size, and allow browser to display download progress
Response.AddHeader ("Content-length", file. Length.tostring ());

Specifies that a stream that cannot be read by the client is returned and must be downloaded
Response.ContentType = "Application/ms-excel";

Send the file stream to the client
Response.WriteFile (file. FullName);
Stop the execution of a page

Response.End ();
}

The above aspect, all will export the EXECL data, directly to the browser output file stream, the following method is first saves it to the server's folder, then sends the file to the client. This allows you to persist the exported files for additional functionality.

5, the Execl file exported to the server, and then download.

Methods of exporting execl in WinForm:

1, Method 1:

Copy Code code as follows:

public void Out2excel (string stablename,string URL)
{
Excel.Application oexcel=new Excel.Application ();
Workbooks obooks;
Workbook obook;
Sheets osheets;
Worksheet osheet;
Range Ocells;
String Sfile= "", stemplate= "";
//
System.Data.DataTable dt=tableout (sTableName). Tables[0];

sfile=url+ "\\myExcel.xls";
stemplate=url+ "\\MyTemplate.xls";
//
Oexcel.visible=false;
Oexcel.displayalerts=false;
Define a new workbook
Obooks=oexcel.workbooks;
oBooks.Open (Stemplate,type.missing,type.missing,type.missing,type.missing,type.missing,type.missing, Type.missing,type.missing,type.missing,type.missing,type.missing,type.missing, Type.Missing, Type.Missing);
Obook=obooks.get_item (1);
Osheets=obook.worksheets;
osheet= (Worksheet) Osheets.get_item (1);
Name the sheet
Osheet.name= "Sheet1";

Ocells=osheet.cells;
Call the DumpData procedure to import data into Excel
DumpData (Dt,ocells);
Save
Osheet.saveas (sfile,excel.xlfileformat.xltemplate,type.missing,type.missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
Obook.close (False, type.missing,type.missing);
Quit Excel and release the invoked COM resource
oExcel.Quit ();

Gc. Collect ();
KillProcess ("Excel");
}

private void KillProcess (string processname)
{
System.Diagnostics.Process myproc= new System.Diagnostics.Process ();
Get all the open processes
Try
{
foreach (Process thisproc in Process.getprocessesbyname (processname))
{
if (!thisproc. CloseMainWindow ())
{
Thisproc. Kill ();
}
}
}
catch (Exception exc)
{
throw new Exception ("", exc);
}
}

2, Method 2:

Copy Code code as follows:

protected void Exportexcel ()
{
Gridbind ();

if (ds1==null) return;

String Savefilename= "";
BOOL Filesaved=false;
SaveFileDialog savedialog=new SaveFileDialog ();
Savedialog.defaultext = "xls";
savedialog.filter= "Excel file |*.xls";
Savedialog.filename = "Sheet1";
Savedialog.showdialog ();
Savefilename=savedialog.filename;
if (Savefilename.indexof (":") <0) return; It was ordered to cancel.
Excelapp. Workbooks.Open (App.Path & \ Project schedule. xls)


Excel.Application xlapp=new Excel.Application ();
Object Missing=system.reflection.missing.value;


if (xlapp==null)
{
MessageBox.Show ("Cannot create Excel object, you may not have Excel installed on your machine");
Return
}
Excel.Workbooks Workbooks=xlapp.workbooks;
Excel.Workbook Workbook=workbooks. ADD (Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet= (excel.worksheet) workbook. worksheets[1];//get Sheet1
Excel.Range Range;


String Oldcaption=title_label. Text.trim ();
Long totalcount=ds1. Tables[0]. Rows.Count;
Long rowread=0;
float percent=0;

Worksheet. Cells[1,1]=title_label. Text.trim ();
Write fields
for (int i=0;i<ds1. Tables[0]. columns.count;i++)
{
Worksheet. Cells[2,i+1]=ds1. Tables[0]. Columns.columnname;
Range= (excel.range) worksheet. CELLS[2,I+1];
Range. Interior.ColorIndex = 15;
Range. Font.Bold = true;

}
Write value
Caption. Visible = true;
for (int r=0;r<ds1. Tables[0]. rows.count;r++)
{
for (int i=0;i<ds1. Tables[0]. columns.count;i++)
{
Worksheet. Cells[r+3,i+1]=ds1. Tables[0]. ROWS[R];
}
rowread++;
Percent= ((float) (100*rowread))/totalcount;
This. caption.text= "Exporting data [" + percent. ToString ("0.00") + "%] ...";
Application.doevents ();
}
Worksheet. SaveAs (savefilename,missing,missing,missing,missing,missing,missing,missing,missing);

This. Caption.visible= false;
This. Caption.text= oldcaption;

Range=worksheet.get_range (worksheet. Cells[2,1],worksheet. Cells[ds1. Tables[0]. Rows.count+2,ds1. Tables[0]. Columns.count]);
Range. Borderaround (Excel.xllinestyle.xlcontinuous,excel.xlborderweight.xlthin, Excel.xlcolorindex.xlcolorindexautomatic,null);

Range. Borders[excel.xlbordersindex.xlinsidehorizontal]. ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
Range. Borders[excel.xlbordersindex.xlinsidehorizontal]. LineStyle =excel.xllinestyle.xlcontinuous;
Range. Borders[excel.xlbordersindex.xlinsidehorizontal]. Weight =excel.xlborderweight.xlthin;

if (DS1. Tables[0]. COLUMNS.COUNT&GT;1)
{
Range. Borders[excel.xlbordersindex.xlinsidevertical]. colorindex=excel.xlcolorindex.xlcolorindexautomatic;
}
Workbook. Close (missing,missing,missing);
xlApp.Quit ();
}

Third, note:

Although all are the functions of exporting execl, in asp.net and WinForm programs, the code implemented is different. In asp.net, the data is read on the server side, the data is ms-execl in the server end to response output to the browser (client), and in WinForm, the data is read to the client (because the WinForm runtime is the client), It then invokes the Office component installed by the client and writes the read data to the Execl workbook.

Copy Code code as follows:

SqlConnection conn=new SqlConnection (system.configuration.configurationsettings.appsettings["Conn"));
SqlDataAdapter Da=new SqlDataAdapter ("select * from TB1", conn);
DataSet ds=new DataSet ();
Da. Fill (ds, "Table1");
DataTable Dt=ds. tables["Table1"];
String name=system.configuration.configurationsettings.appsettings["DownloadURL"]. ToString () +datetime.today.tostring ("YyyyMMdd") +new Random (DateTime.Now.Millisecond). Next (10000). ToString () + ". csv";//stored in Web.config downloadurl specified path, file format is current date + 4-bit random number
FileStream fs=new FileStream (name,filemode.create,fileaccess.write);
StreamWriter Sw=new StreamWriter (fs,system.text.encoding.getencoding ("gb2312"));
Sw. WriteLine ("Automatic numbering, name, age");
foreach (DataRow dr in Dt. Rows)
{
Sw. WriteLine (dr["ID"]+ "," +dr["VName"]+ "," +dr["]);
}
Sw. Close ();
Response.AddHeader ("Content-disposition", "attachment; Filename= "+ server.urlencode (name));
Response.ContentType = "Application/ms-excel";//specifies that a stream that cannot be read by the client is returned and must be downloaded
Response.WriteFile (name); Send the file stream to the client
Response.End ();