Asp.net common methods for exporting excel Data, asp. netexcel

Source: Internet
Author: User

Asp.net common methods for exporting excel Data, asp. netexcel

This document describes some common excel data export methods in asp.net and summarizes some problems that may occur during data import or export. I hope the article will be helpful to you. The specific implementation method is as follows:

1. generated by dataset

Copy codeThe Code is as follows: public void CreateExcel (DataSet ds, string typeid, 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 = "";
Int I = 0;
 
// Define the table object and row object, and use DataSet to initialize its value
DataTable dt = ds. Tables [0];
DataRow [] myRow = dt. Select ("");
// When typeid = "1", the exported file is in EXCEL format; When typeid = "2", the exported file is in XML format.
If (typeid = "1 ")
{
// Obtain the titles of each column in the data table. The headers are separated by t. A carriage return is followed by the last column title.
For (I = 0; I colHeaders + = dt. Columns [I]. Caption. ToString () + "t ";
ColHeaders + = dt. Columns [I]. Caption. ToString () + "n ";
// Write the obtained data to the HTTP output stream
Resp. Write (colHeaders );
// Process data row by row
Foreach (DataRow row in myRow)
{
// In the current row, data is obtained by column. Data is separated by t, and carriage return n is added at the end.
For (I = 0; I ls_item + = row [I]. ToString () + "t ";
Ls_item + = row [I]. ToString () + "n ";
// Write the data in the current row to the HTTP output stream, and leave ls_item empty for downstream data
Resp. Write (ls_item );
Ls_item = "";
}
}
Else
{
If (typeid = "2 ")
{
// Export XML data directly from DataSet and write it to the HTTP output stream
Resp. Write (ds. GetXml ());
}
}
// Write the data in the buffer to the HTTP header file
Resp. End ();
}

2. generated by the datagrid

Copy codeThe Code is as follows: public void ToExcel (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"; // image/JPEG; text/HTML; image/GIF; vnd. ms-excel/msword
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: ToExcel (datagrid1 );
 
3. dataview

Copy codeThe Code is as follows: public void OutputExcel (DataView dv, string str)
{
//
// TODO: add the constructor logic here
//
// 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 ();
}
 
How to import and export problems in EXCEL

1. Add reference in the project:
Right-click reference of Project Resource Manager> Add reference> select. NET tab> select Microsoft. Office. Interop. Excel> OK;
 
When selecting the. NET Component, pay attention to the version number. In this example, 12.0.0.0 is the Office2007 version:
2. Use Microsoft. Office. Interop. Excel in the project:
To use Microsoft. Office. Interop. Excel, you must first reference the namespace in the project:
Using Microsoft. Office. Interop. Excel;
3. Create an Excel. Application Object
Copy codeThe Code is as follows: // create an Application Object
Microsoft. Office. Interop. Excel. Application myExcel = new Application ();
// Create a Workbooks object
Workbooks myBooks = myExcel. Application. Workbooks;
// Create a System. Reflection. Missing object
Object oMissing = System. Reflection. Missing. Value;
4. Open or create an Excel book File
  Copy codeThe Code is as follows: // open the Excel file. Note that "ExccelFilePath" is the physical address of the Excel file on the server, including the file name.
Workbook myBook = myBooks. Open (ExccelFilePath, oMissing );
// Create a new Workseet object. This is the worksheet to be operated on. You can use either the index value of the worksheet or the worksheet name to be operated on, the default name is "sheet1"/"Sheet2", etc.
Worksheet mySheet = (Worksheet) myBook. Worksheets [1];
// If you create an EXCEL Workbook, you must set the following two rows to ensure that there is a worksheet in the workbook,
Workbook workbook1 = excel1.Workbooks. Add (true );
Worksheet mySheet = (Worksheet) workbook1.Worksheets ["sheet1"];
// Set whether to display the EXCEL object interface. The default value is false.
MyExcel. Visble = true;

5. Some important Excel operations
1. Get the Range object
①. Get the Range object of a cell:
Copy codeThe Code is as follows: // select the cell of the first row and column as the Range object.
Range r = (Excel. Range) mySheet. Cells [1, 1];
// Select multiple consecutive cells as the Range object.
Range r = (Excel. Range) Range. get_Range ("A1: F3 ")
② Assign values to cells or retrieve the values of cells:
Copy codeThe Code is as follows: // you have selected a value assignment for the Range object:
R. Text = "China ";
// The Value assignment of the Range object is not selected:
MySheet. Cells [1, 2]. Text = "China ";
// You have selected the Range object value:
String strValue = r. Text;
// The value of the Range object is not selected:
String strValue = mySheet. Cells [1, 2]. Text;
③ Set borders for cells
Copy codeThe Code is as follows: mySheet. Cells [2, 1]. BorderAround (XlLineStyle. xlContinuous, XlBorderWeight. xlThin, XlColorIndex. xlColorIndexAutomatic, null); // draw a line
④ Merge Cells
  Copy codeThe Code is as follows: // before merging cells, select the cells to be merged as the Range object.
Range r = Range. get_Range ("A1: F3 ");
// Then set merge Cells
R. MergeCells = true;
⑤ Set the font, font size, background color, and other attributes of cells.
Copy codeThe Code is as follows: mySheet. Cells [1, 1]. Font. Name = "";
MySheet. Cells [1, 1]. Font. Size = 20;
MySheet. Rows ["1:1"]. RowHeight = 40;
MySheet. Cells [1, 1]. Interior. Color = Color. FromArgb (224,224,224); // set the Color
6. delete a row:
   Copy codeThe Code is as follows: // first obtain the Range of the row to be deleted
Microsoft. Office. Interop. Excel. Range range = (Microsoft. Office. Interop. Excel. Range) mySheet. Rows [sendedRow [1], Type. Missing];
// Note that the row to be deleted is replaced by the row below. If you delete the row by row, delete the row from the largest row to the smallest one.
Range. Delete (Microsoft. Office. Interop. Excel. XlDeleteShiftDirection. xlShiftUp );
7. Obtain the number of rows with data
    Copy codeThe Code is as follows: int rowsint = mySheet. UsedRange. Cells. Rows. Count;
6. Save and exit EXCEL files

1. Save and exit EXCEL
Copy codeThe Code is as follows: myBook. Save ();
MyBooks. Close ();
MyExcel. Quit ();

2. Save a specified file in EXCEL
   Copy codeThe Code is as follows: myBook. Close (true, FilePath + _ file_Name, null );
7. Release the resources of the EXCLE object and end the EXCEL Process
Many netizens have been talking about this in a variety of ways. After my practice, the following aspects can truly end the EXCEL task process:
1. Put all the above operations on EXCEL in one method,
2. After an EXCEL file is operated, no objects are immediately released and null values are assigned:
  Copy codeThe Code is as follows: System. Runtime. InteropServices. Marshal. ReleaseComObject (mysheet );
Mysheet = null;
System. Runtime. InteropServices. Marshal. ReleaseComObject (myBook );
MyBook = null;
System. Runtime. InteropServices. Marshal. ReleaseComObject (myBooks );
MyBooks = null;
System. Runtime. InteropServices. Marshal. ReleaseComObject (myExcel );
MyExcel = null;
3. Create a new method, execute the EXCEL method created above in the method, and add GC. Collect () after the EXCEL method is executed ():
Copy codeThe Code is as follows: // In the following method, the OutPutEXCEL () method is used to output an EXCEL file for EXCEL operations.
Private void killExcel ()
{
OutPutEXCEL ();
GC. Collect ();
GC. WaitForPendingFinalizers ();
}
Many netizens are introducing GC. collect () releases the resources occupied by EXCEL to end EXCEL. collect (); "is written in a program block with EXCEL operations." GC "never ends the EXCEL process. In WEB applications, this phenomenon is terrible. The reason is that GC does not clean up the junk memory in this program block.

4. Call the killEXCEL () method in a business event:
Copy codeThe Code is as follows: protected void LinkButton3_Click (object sender, EventArgs e)
{
// Export EXCEL
KillExcel ();
}
8. Basic settings of some permissions:

Using the above method to debug the program in the development environment is not a problem. After the program is published to the server, it still cannot run normally. You need to perform the following permission settings:

. NET Excel export 80070005 error solution:

An error occurred while retrieving components whose CLSID is {00024500-0000-0000-c000-0000000000000046} in the COM class factory because the following error occurs: 80070005 basically. net to export excel files, you need to configure it like this. If it is not configured, It is correct. However, after the configuration, there should be no errors.
The configuration method is as follows:
① Install the Excel software for office on the server.
② Input dcomcnfg.exe in "start"-> "run" to start "Component Service"
③ Double-click "component service"> "computer"> "my computer"> "DCOM configuration"
④ Find "Microsoft Excel application" in "DCOM configuration", right-click it, and then click "properties". The "Microsoft Excel application properties" dialog box is displayed.
⑤ Click the "ID" tab and select "Interactive User"
6. Click the "Security" tab, click "Custom" on "Start and activate Permissions", and then click the corresponding "edit" button, in the pop-up "security" dialog box, add a "network service" user (note that you must select the computer name) and grant it the "local start" and "Local activation" permissions.
7. Click the "Security" label, click "Custom" on "Access Permissions", and then click "edit ", in the pop-up "security" dialog box, add a "network service" user and grant "local access" permission.
If the error 8000401a occurs after the interactive user is set, you can cancel the interactive user and set it to administratr. This problem can be solved temporarily. Further solutions are to be discussed.
After setting "8th", "object reference or link cannot be used" and cell paste is not allowed when you open an Excel file. The reason is unknown. The setting will disappear after cancellation.

I hope this article will help you design your asp.net program.


How to export an excel table using aspnet

Use the SQL bcp command to pass the SQL statement (including the table name, column name, and file location ).
Generate the file to a dedicated temporary directory, and then read the file class for users to download.
This is an idea. I hope to continue to discuss the specific implementation code with you. Because I also need to download tens of thousands of lines.

How does aspnet export a large amount of data from SQL server2008 to excel?

I wrote a blog post before.

In the previous project, one function is to export the content in the gridview as an Excel table. I believe this function is still quite common, so I will share the code.
You must call
Using System. Web. UI. HtmlControls;
Using System. Web. UI. WebControls;
Using System. Web. UI. HtmlControls;
Because the export method must use them
If the gridview is paginated, print the current pagination as follows:
Protected void Button2_Click (object sender, EventArgs e)
{
Response. ClearContent ();
Response. AddHeader ("content-disposition", "attachment; filename=StudentSelect.xls ");
Response. ContentType = "application/msexcel ";
Response. Clear ();
Response. BufferOutput = true;
StringWriter sw = new StringWriter ();
HtmlTextWriter htw = new HtmlTextWriter (sw );
GridView1.RenderControl (htw );
Response. Write (sw. ToString ());
Response. End ();
}

If you ignore the pagination of the gridview and export all the pages, the following code is used:
Protected void Button3_Click (object sender, EventArgs e)
{
GridView1.AllowPaging = false;
BLL. AdminBLL SB = new BLL. AdminBLL ();
DataSet SD = SB. selectStudentProject ();
GridView1.DataSource = SD. Tables ["tbl_Student"];
GridView1.DataBind ();
Response. ClearContent ();
Response. AddHeader ("content-disposition", "attachment; filename=AllStudentSelect.xls ");
Response. ContentType = "application/msexcel ";
Response. Clear ();
Response. BufferOutput = true;
StringWriter sw = new StringWriter ();
HtmlTextWriter htw = new ...... remaining full text>

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.