C # Three Data Writing Methods to excel

 

First, write the data in the DataGrid to excel in the form of a stream. The format exists in the form of html.

 

Response. Clear ();

Response. Buffer = true;

Response. Charset = "GB2312 ";

Response. AppendHeader ("Content-Disposition", "attachment?filename=dialouttemplate.xls ");

 

// If it is set to GetEncoding ("GB2312"), the exported file will contain garbled characters !!!

Response. ContentEncoding = System. Text. Encoding. UTF8;

Response. ContentType = "application/ms-excel"; // set the output file type to an excel file.

 

 

// Response. ContentType = "application/vnd. ms-excel"; // output type

// Response. Charset = "";

 

// Close ViewState

EnableViewState = false;

System. IO. StringWriter tw = new System. IO. StringWriter (); // write information to a string

System. Web. UI. HtmlTextWriter hw = new System. Web. UI. HtmlTextWriter (tw); // write a series of consecutive HTML-specific characters and texts on the WEB form page.

// This class provides the Formatting Function used by ASP. NET Server controls to render HTML content to the client.

// Obtain the control HTML

 

Dg. RenderControl (hw); // output the table content to the HtmlTextWriter object.

 

// Write the HTML back to the browser

Response. Write (tw. ToString ());

Response. Flush ();

Response. End ();

 

 

 

Type 2: write data from the data source to excel as a file stream. The format exists in txt format.

 

FileStream fs = new FileStream (Server. MapPath ("report_export/DialoutTemplate.xls"), FileMode. Create, FileAccess. Write );

StreamWriter rw = new StreamWriter (fs, Encoding. Default); // create a StreamWriter to prepare for writing;

 

DataTable dt = GetDataTableSource ();

 

Int count = dt. Columns. Count;

String head = "";

String values = "";

 

For (int I = 0; I <count; I ++)

{

String h = dt. Columns [I]. ColumnName + "\ t ";

String v = dt. Rows [0] [I]. ToString () + "\ t ";

 

Head + = h;

Values + = v;

}

Rw. WriteLine (head );

Rw. WriteLine (values );

 

Rw. Close ();

Fs. Close ();

 

Response. Redirect ("report_export/DialoutTemplate.xls ");

 

 

 

Third: directly write data from the data source to excel. The format exists in the form of xls.

 

Data can be imported directly, and the digital format can be automatically converted to the text format, which can be reduced

 

You can also reserve the number of lines in the format of converting numbers to text,

 

Customizable

 

 

 

Excel. Application xlApp;

Excel. Workbook xlBook;

Excel. Workbooks xlBooks;

// Excel. Range xlRange;

Excel. Sheets xlsheets;

Excel. Worksheet xlSheet;

Int k = 0;

Try

{

String strCurrentPath = Server. MapPath ("report_export/DialoutTemplate.xls ");

String FilePath = strCurrentPath;

 

FileInfo fi = new FileInfo (FilePath );

If (fi. Exists) // checks whether the object already Exists. if so, delete it!

{

Fi. Delete ();

}

 

XlApp = new Excel. Application ();

XlBooks = xlApp. Workbooks;

XlBook = xlBooks. Add (Type. Missing );

Xlsheets = xlBook. Worksheets;

IntPtr intptr = new IntPtr (xlApp. Hwnd );

XlSheet = (Excel. Worksheet) xlsheets. get_Item (1 );

 

DataTable dt = GetDataTableSource ();

 

Int count = dt. Columns. Count;

For (int I = 0; I <count; I ++)

{

String h = dt. Columns [I]. ColumnName;

String v = dt. Rows [0] [I]. ToString ();

 

(Excel. Range) xlSheet. Cells [1, I + 1]). Value2 = h;

Excel. Range r1 = xlSheet. get_Range (xlSheet. Cells [1, 1], xlSheet. Cells [1, I + 1]);

R1.NumberFormatLocal = "@";

(Excel. Range) xlSheet. Cells [2, I + 1]). Value2 = v;

 

Excel. Range r2 = xlSheet. get_Range (xlSheet. Cells [2, 1], xlSheet. Cells [2, I + 1]);

R2.NumberFormatLocal = "@";

}

 

For (int j = 1; j <500; j ++)

{

Excel. Range r = xlSheet. get_Range (xlSheet. Cells [2 + j, 1], xlSheet. Cells [2 + j, count]);

R. NumberFormatLocal = "@";

}

 

XlBook. saveAs (FilePath, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Excel. xlSaveAsAccessMode. xlNoChange, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing );

XlBook. Close (false, Type. Missing, Type. Missing );

XlBooks. Close ();

XlApp. Quit ();

Response. Redirect ("report_export/DialoutTemplate.xls ");

GetWindowThreadProcessId (intptr, out k );

System. Diagnostics. Process p = System. Diagnostics. Process. getprocpolicyid (k );

P. Kill ();

 

}

Catch (Exception ex)

{

Response. Write (ex. Message );

}

Finally

{

// XlRange = null;

XlSheet = null;

XlBook = null;

XlApp = null;

}

 

From Feige's column