C # data import/export Excel files and winForm export Execl 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 output the file address to the browser; one is to directly write the file output stream to the browser. In Response output, \ t data is separated. When execl is exported, \ 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.
Copy codeThe Code is 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 use the ContentType attribute, which defaults to text/html. At this time, the output is hypertext, that is, our common webpage format is sent to the client, if it is changed to ms-excel, the excel format will be output, that is, the workbook format will be output to the client, then 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. We generally want to export data on the DataGrid Control.
Copy codeThe Code is as follows:
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 of all the information of your select statement.

For ease of use, the writing method is as follows:
Copy codeThe Code 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. Export the data in the DataSet, that is, to export the information of each row in the table in the DataSet in the ms-excel format to the http stream, so that it is OK. Dsds datasdatasdatasdatasdatasdatasdatasdatasdatasdatasdatas: the file name should be full, and the package suffix should be included, for example, execl2006.xls
Copy codeThe Code is 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 = "";

// 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.
Copy codeThe Code is as follows:
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 Field Format of the date type to center-aligned.
}
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; // you can specify center-aligned fields.
}
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 to light yellow. There are 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 [], excel. Cells [rowSum, 2]). Borders [XlBordersIndex. xlEdgeLeft]. Weight = XlBorderWeight. xlThick; // set the left line to bold
XSt. get_Range (excel. Cells [], excel. Cells [4, colIndex]). Borders [XlBordersIndex. xlEdgeTop]. Weight = XlBorderWeight. xlThick; // set the upper line to 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 right line.
XSt. get_Range (excel. cells [rowSum, 2], excel. cells [rowSum, colIndex]). borders [XlBordersIndex. xlEdgeBottom]. weight = XlBorderWeight. xlThick; // you can specify the width of the lower line.
//
// Display Results
//
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 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 a folder on the server 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:
Copy codeThe Code is 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 process to import the data to 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 );
// 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:
Copy codeThe Code is 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; // 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]. 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 );

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, data is read on the server, and data is output to the browser (client) in ms-execl format in Response on the server. In winform, read data to the client (because the winform Runtime is the client), call the office component installed on the client, and write the read data in the execl workbook.
Copy codeThe Code is as follows:
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. appSettings ["downloadurl"]. toString () + DateTime. today. toString ("yyyyMMdd") + new Random (DateTime. now. millisecond ). next (1, 10000 ). toString () + ". csv "; // save it to the web. the path specified by downloadurl in config. The file format is the current date + 4-digit random number.
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 downloaded.
Response. WriteFile (name); // send the file stream to the client
Response. End ();