C # export an Excel Summary

I. Method for exporting Execl in asp.net:

There are two ways to export Execl in asp.net: one is to store the exported file under a folder on the server, and then the file address

Output on the browser; one is to directly write the file output stream to the browser. When Response outputs, separate \ t data to export

In execl, \ n is equivalent to line feed.

1. Output execl all the html

This method outputs all html content, such as buttons, tables, and images, to Execl.
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;

The ContentType attribute is used here. The default attribute is text/html, and the output is hyper text, which is a common web page.

If the format is changed to ms-excel, the excel format is output, that is, the workbook format is output to the client.

The browser will prompt you to download and save. ContentType attributes include: image/JPEG; text/HTML; image/GIF; vnd. ms-

Excel/msword. Likewise, we can also output (export) images and Word documents. The following method also uses this attribute.

2. Export the data in the DataGrid Control to Execl

Although the above method achieves the export function, it also imports all the output information in html such as buttons and paging boxes. While we generally

The data to be exported is the data on the DataGrid Control.
System. Web. UI. Control ctl = this. DataGrid1;
// DataGrid1 is the control you drag and drop in 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 paging, it exports the information of the current page, that is, it exports the information displayed in the DataGrid. Instead

All information about your select statement.

For ease of use, the writing method is 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. Export the data in DataSet to Execl

With the above idea, we can export the exported information to the Response client. Set DataSet

Data export in, that is, the information of each row in the table in DataSet, in ms-excel format Response to http stream, So OK

. Dsds datasdatasdatasdatasdatasdatasdatasdatasdatasdatasdatas: the file name should be full, and the package suffix should be included, for example, execl2006.xls

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 = "";
 
// Define the table object and row object, and use DataSet to initialize the value.
DataTable dt = ds. Tables [0];
DataRow [] myRow = dt. Select (); // you can filter data in the form of dt. Select ("id> 10 ").
Int I = 0;
Int cl = dt. Columns. Count;

// Obtain the titles of each column in the data table, separated by \ t, and a carriage return is added after the title of the last column.
For (I = 0; I <cl; I ++)
{
If (I = (cl-1) // The last column, with \ n
{
ColHeaders + = dt. Columns [I]. Caption. ToString () + "\ n ";
}
Else
{
ColHeaders + = dt. Columns [I]. Caption. ToString () + "\ t ";
}

}
Resp. Write (colHeaders );
// Write the obtained data to the HTTP output stream

// Process data row by row
Foreach (DataRow row in myRow)
{
// Write the data in the current row to the HTTP output stream, and leave ls_item empty for downstream data
For (I = 0; I <cl; I ++)
{
If (I = (cl-1) // The last column, with \ n
{
Ls_item + = row [I]. ToString () + "\ n ";
}
Else
{
Ls_item + = row [I]. ToString () + "\ t ";
}

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

}
Resp. End ();
}

4. Export dataview to execl

This method is available if you want to export execl statements that are more variable or with irregular rows and columns.
Public void OutputExcel (DataView dv, string str)
{
// Dv indicates the data to be output to Excel, and str indicates 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;
 
//
// Obtain the 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; // set the title format to center and align
}
 
//
// Obtain 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; // set the format of the datetime field to center

Qi
}
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; // set the format of the ignore field to center

Qi
}
Else
{
Excel. Cells [rowIndex, colIndex] = row [col. ColumnName]. ToString ();
}
}
}
//
// Load a total 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 part
//
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; // set it to light yellow, with 56 in total
//
// Obtain the title of the entire report
//
Excel. Cells [2, 2] = str;
//
// Set the title format of 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 the optimum 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. xlhaligncenter1_ssselection;
//
// Draw a 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; // you can specify whether to bold the left line.
XSt. get_Range (excel. Cells [4, 2], excel. Cells [4, colIndex]). Borders [XlBordersIndex. xlEdgeTop]. Weight

= XlBorderWeight. xlThick; // you can specify whether the upper line is bold.
XSt. get_Range (excel. Cells [4, colIndex], excel. Cells [rowSum, colIndex]). Borders

[XlBordersIndex. xlEdgeRight]. Weight = XlBorderWeight. xlThick; // you can specify the width of the line on the right.
XSt. get_Range (excel. Cells [rowSum, 2], excel. Cells [rowSum, colIndex]). Borders

[XlBordersIndex. xlEdgeBottom]. Weight = XlBorderWeight. xlThick; // You Can bold the following line.
//
// Display Results
//
Excel. Visible = true;
 
// XSt. Export (Server. MapPath (".")

+ "\" + This. xlfile. Text + ". xls", SheetExportActionEnum. ssExportActionNone, Microsoft. Office. Interop. OW

C. 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 the header information and specify the default file name for the "download/Save as" dialog box
Response. AddHeader ("Content-Disposition", "attachment; filename =" + Server. UrlEncode

(File. Name ));
// Add header information and specify the file size so that the browser can display the download progress.
Response. AddHeader ("Content-Length", file. Length. ToString ());

// Specify a stream that cannot be read by the client and must be downloaded.
Response. ContentType = "application/ms-excel ";

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

Response. End ();
}

In the above aspects, execl data to be exported is directly output to the browser file stream. The following method is to first save it to the server

In a folder, and then send the file to the client. In this way, the exported files can be permanently stored for other functions.

5. Export the execl file to the server and then download it.

Ii. How to export Execl in winForm:

1. Method 1:

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. Missi

Ng, 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 process to import the data to Excel
DumpData (dt, oCells );
// Save
OSheet. SaveAs (sFile, Excel. XlFileFormat. xlTemplate, Type. Missing,

Type. Missing, Excel. XlSaveAsAccessMode. xlNoChange, Type. Missing );
OBook. Close (false, Type. Missing, Type. Missing );
// Exit Excel and release the called COM Resource
OExcel. Quit ();

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

Private void KillProcess (string processName)
{
System. Diagnostics. Process myproc = new System. Diagnostics. Process ();
// Obtain all opened Processes
Try
{
Foreach (Process thisproc in Process. GetProcessesByName (processName ))
{
If (! Thisproc. CloseMainWindow ())
{
Thisproc. Kill ();
}
}
}
Catch (Exception Exc)
{
Throw new Exception ("", Exc );
}
}
2. Method 2:

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; // canceled
// 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 ("an Excel object cannot be created, maybe your computer has not installed Excel ");
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]. ColumnsIdea [I]. 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 );

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> 1)
{
Range. Borders

[Excel. XlBordersIndex. xlInsideVertical]. ColorIndex = Excel. XlColorIndex. xlColorIndexAutomatic;
}
Workbook. Close (missing, missing, missing );
XlApp. Quit ();
}

Iii. Notes:

Although execl export is implemented, the implemented Code varies in asp.net and winform programs. In asp.net

Is to read data on the server side, and then output data in ms-execl format to the browser (client) in Response );

In winform, data is read to the client (because the winform running end is the client), and then the office installed on the client is called.

Component to write the read data in the execl workbook.
 
SqlConnection conn = new SqlConnection (System. Configuration. ConfigurationSettings. deleettings

["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. deleettings ["downloadurl"]. ToString ()

+ DateTime. Today. ToString ("yyyyMMdd") + new Random (DateTime. Now. Millisecond). Next

(10000). ToString () + ". csv"; // store it in the path specified by downloadurl in web. config. The file format is the current date + 4 bits

Number of machines
FileStream fs = new FileStream (name, FileMode. Create, FileAccess. Write );
StreamWriter sw = new StreamWriter (fs, System. Text. Encoding. GetEncoding ("gb2312 "));
Sw. WriteLine ("automatic number, name, age ");
Foreach (DataRow dr in dt. Rows)
{
Sw. WriteLine (dr ["ID"] + "," + dr ["vName"] + "," + dr ["iAge"]);
}
Sw. Close ();
Response. AddHeader ("Content-Disposition", "attachment; filename =" + Server. UrlEncode (name ));
Response. ContentType = "application/ms-excel"; // specify that the returned stream cannot be read by the client and must be

Download
Response. WriteFile (name); // send the file stream to the client
Response. End ();