Summary of methods for exporting Excel data in asp.net

1, generated by the dataset

The code is as follows

Copy Code

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; Defines a Table object as a row pair, and initializes its value with a dataset. DataTable Dt=ds. Tables[0]; Datarow[] Myrow=dt. Select (""); Export as an Excel format file when typeid== "1", and export as an XML format file when typeid== "2" if (typeid== "1") { Get the column headings of the datasheet, divide the headings by T, and add the carriage return after the last column heading for (I=0;i Colheaders+=dt. Columns[i]. Caption.tostring () + "T"; Colheaders +=dt. Columns[i]. Caption.tostring () + "n"; Writes the obtained data information to the HTTP output stream Resp. Write (colheaders); Process data line by row foreach (DataRow row in myrow) { In the current row, the data is obtained by column, and the data is separated by a T, and the end is added with a return character n For (I=0;i Ls_item +=row[i]. ToString () + "T"; Ls_item + = Row[i]. ToString () + "n"; The current row data is written to the HTTP output stream and the null Ls_item is placed so that the downlink data Resp. Write (Ls_item); Ls_item= ""; } } Else { if (typeid== "2") { Export XML data directly from the dataset and write to the HTTP output stream Resp. Write (ds. GETXML ()); } } Data in the write buffer to the HTTP header file Resp. End (); }

2, generated by the DataGrid

The code is as follows

Copy Code

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, this use DataView

The code is as follows

Copy Code

public void Outputexcel (DataView dv,string str) { // TODO: Add constructor logic here // 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, http://www.111cn.net 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 (); }

Import and export Some of the problem totals in Excel

Add a reference to the project:

Right-click the reference--> of the Project Explorer to add a reference--> selection. NET tab--> Select Microsoft.office.interop.excel--> to determine (pictured below);

Notice when you choose. NET component version number, the figure is 12.0.0.0 is the Office2007 version:

Ii. use of Microsoft.Office.Interop.Excel in the project:

If you want to use Microsoft.Office.Interop.Excel, you first need to refer to the namespace in your project:

Using Microsoft.Office.Interop.Excel;

Iii. establishment of Excel.Application related objects

Create a 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;

Open or create a new Excel book file

Open the Excel file and note that the "Exccelfilepath" is the physical address of the Excel file on the server, including the file name

Workbook MyBook = Mybooks.open (exccelfilepath,omissing, omissing, omissing, omissing, omissing, oMissing, oMissing, omissing, omissing, omissing, omissing, omissing);

Creates a new Workseet object, where the worksheet you want to manipulate has two ways to get a worksheet: Use the index value of the worksheet or the name of the worksheet by default: "Sheet1"/"Sheet2", and so on

Worksheet MySheet = (worksheet) mybook.worksheets[1];

If you are creating a new Excel workbook, you need to set the following two lines to make sure that you have a worksheet in the workbook,

Workbook Workbook1 = Excel1. Workbooks.Add (TRUE);
Worksheet mysheet= (worksheet) Workbook1. Worksheets["Sheet1"];

Sets whether an Excel object displays an interface, and defaults to false without displaying the interface

Myexcel.visble=true;

V. Some of the more important actions for Excel

1. Get Range Object

①, gets a Range object for a cell:

Select the cell in the first row and first column as a Range object

Range r = (excel.range) mysheet.cells[1, 1];

Select multiple contiguous cells as a Range object

Range r= (Excel.Range) range.get_range ("A1:f3")

②, assign a value to a cell, or remove a cell's value:

The assignment of a Range object has been selected:

R.text= "China";

Assignment of Range object is not selected:

mysheet.cells[1,2]. Text= "China";

The Range object's value has been selected:

String strvalue= R.text;

The value of the Range object is not selected:

String strvalue= mysheet.cells[1,2]. Text;

③, set borders on cells

Mysheet.cells[2, 1]. Borderaround (xllinestyle.xlcontinuous, Xlborderweight.xlthin, xlcolorindex.xlcolorindexautomatic, NULL);//Draw Line

④, merging cells

Select the cell you want to merge as a Range object before merging cells

Range R=range.get_range ("a1:f3");

Then set the merged cells now

R.mergecells = true;

⑤, set the cell font, font size, background color, and other properties

Mysheet.cells[1, 1]. Font.Name = "Blackbody";
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 color

⑥, delete a row:

Gets the range of the row to be deleted first

Microsoft.Office.Interop.Excel.Range Range = (Microsoft.Office.Interop.Excel.Range) mysheet.rows[sendedrow[1], Type.Missing];

Note the line number that is deleted after the row is deleted is replaced by the following line, and if you delete the row by line, remove from the largest line number to the smallest line number
Range. Delete (MICROSOFT.OFFICE.INTEROP.EXCEL.XLDELETESHIFTDIRECTION.XLSHIFTUP);

⑦, getting the number of rows with data

int rowsint = MySheet.UsedRange.Cells.Rows.Count;

Vi. Save and exit of Excel files

1, Excel saves and exits

Mybook.save ();
Mybooks.close ();
Myexcel.quit ();

2, Excel to specify the file to save

Mybook.close (True, FilePath +_file_name, NULL);

Vii. Releasing resources for Excle objects and ending the Excel process

There are a lot of users in this area are talking about a variety of methods, after my practice, the following can really do to end the task process of Excel:

1. Put all the above Excel operations into one method,

2. When you operate Excel, you will not immediately release and assign null values using object one by one:

System.Runtime.InteropServices.Marshal.ReleaseComObject (MySheet);

Mysheet=null;

System.Runtime.InteropServices.Marshal.ReleaseComObject (MyBook);

Mybook=null;//http://www.111cn.net

System.Runtime.InteropServices.Marshal.ReleaseComObject (MyBooks);

Mybooks=null;

System.Runtime.InteropServices.Marshal.ReleaseComObject (Myexcel);

Myexcel=null;

3, and then create a new method, and in this method to perform the new operation of the Excel method, and after the operation of the Excel method to add Gc.collect ():

The Outputexcel () method in the following method is the way to output Excel files for Excel operations

private void Killexcel ()
{

Outputexcel ();
Gc. Collect ();
Gc. WaitForPendingFinalizers ();
}

Many netizens are introducing the use of Gc.collect () to release Excel's resources to end Excel, if the GC. Collect (); " With operations Excel's business written in a block, "GC" is never able to end the Excel process, in the Web application, this phenomenon is very scary thing. The reason is that the GC does not clean up the garbage memory in this program block.

4. Call the Killexcel () method in the business event:

protected void Linkbutton3_click (object sender, EventArgs e)
{
Export Excel
Killexcel ();
}

Eight, some basic settings of permissions:

Using the above methods in the development environment, the debugger does not have a problem, etc. after publishing to the server, the program still does not work, you need to do the following permissions settings:

1,. NET export Excel encountered the 80070005 error solution:


Retrieving a component with a CLSID of {00024500-0000-0000-c000-000000000046} in a COM class factory failed because the following error occurred: 80070005 basically. NET export Excel file, all need to configure this, do not configure some times Yes, and configuration should not be the basic error.


The specific configuration method is as follows:


1: Install Office Excel software on the server.


2: In the "Start"-> "Run" Enter Dcomcnfg.exe start "Component Services"


3: Double-click "Component Services"-> "Computer"-> "My Computer"-> "DCOM Configuration"


4: Find "Microsoft Excel application" in "DCOM Configuration", right-click on it, and click "Properties" to pop up the Microsoft Excel Application Properties dialog box


5: Click on the "Logo" tab, select "Interactive User"


6: Click the "Security" tab and click "Customize" on "Start and Activate Permissions". Then click on the corresponding "edit" button, in the pop-up "Security" dialog box to fill in a "Network SERVICE" User (note to select this computer name), and give it "local startup" and "Local activation" Permissions.


7: Remain the "Security" tab, click "Customize" on "Access", then click "Edit", and add a "network SERVICE" user in the Pop-up Security dialog box, and give "local access" permission.


8. If you have an error 8000401a after the interactive user settings, you can cancel the interactive user, designated as ADMINISTRATR, to temporarily resolve the problem. Further solutions have yet to be explored.


9. After you use the 8th setting, opening Excel may appear "cannot use object references or links" and cannot be pasted in cells. The reason is unknown, the cancellation can disappear.

The above is I in the recent development of a little experience, is collated into documents, for the fight in the process of developing a line of friends to share, would like to see the Netizen can name help solve the "cannot use object reference or link" problem.