This paper illustrates some common methods of Excel data export in ASP.net, and also introduces some problems that may be encountered in data import or export, and share them for everyone's reference. I hope the article will help you. The implementation methods are as follows:
1, generated by the dataset
Copy Code code 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;
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
Copy Code code as follows:
public void Toexcel (System.Web.UI.Control ctl)
{
& nbsp; 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
Copy Code code as follows:
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, 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;
Notice when you choose. NET component, the 12.0.0.0 of this example is the version of Office2007:
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
Copy Code code as follows:
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
Copy Code code as follows:
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:
Copy Code code as follows:
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:
Copy Code code as follows:
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
Copy Code code as follows:
Mysheet.cells[2, 1]. Borderaround (xllinestyle.xlcontinuous, Xlborderweight.xlthin, xlcolorindex.xlcolorindexautomatic, NULL);//Draw Line
④, merging cells
Copy Code code as follows:
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
Copy Code code as follows:
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:
Copy Code code as follows:
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
Copy Code code as follows:
int rowsint = MySheet.UsedRange.Cells.Rows.Count;
Vi. Save and exit of Excel files
1, Excel saves and exits
Copy Code code as follows:
Mybook.save ();
Mybooks.close ();
Myexcel.quit ();
2, Excel to specify the file to save
Copy Code code as follows:
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:
Copy Code code 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, 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 ():
Copy Code code as follows:
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:
Copy Code code as follows:
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:
. 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:
① installs Office Excel software on the server.
② enter Dcomcnfg.exe to start Component Services in the start-> run
③ Double-click "Component Services"-> "Computer"-> "My Computer"-> "DCOM Configuration"
④ find Microsoft Excel application in DCOM configuration, right-click on it, and then click Properties to pop up the Microsoft Excel Application Properties dialog box
⑤ Click on the "Logo" tab and select "Interactive User"
⑥ Click on 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.
⑦ is still the "Security" tab, click "Customize" on "Access", click "Edit", and add a "network SERVICE" user in the Pop-up Security dialog box, and give "local access" permission.
⑧ If an error 8000401a occurs after an interactive user setting, you can cancel the interactive user and specify ADMINISTRATR to temporarily resolve the problem. Further solutions have yet to be explored.
⑨ with 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.
I hope this article will help you with the ASP.net program design.
