asp.net summary of common ways to export excel data

  • 2021-01-25 07:24:46
  • OfStack

In this paper, examples of asp.net 1 some commonly used excel data export methods, but also introduced in the data import or export may encounter a summary of some problems, to share with you for your reference. I hope this article will be helpful to you. The specific implementation method is as follows:

1. Generated by dataset

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, using DataSet Initialize its value
   DataTable dt=ds.Tables[0];
   DataRow[] myRow=dt.Select(""); 
   // typeid=="1" When exported as EXCEL Format file; typeid=="2" When exported as XML Format file
   if(typeid=="1")
   {
    // Gets the column headings of the data table, and between the headings t Segmentation, and finally 1 Enter a carriage return character after the heading of each column
    for(i=0;i     colHeaders+=dt.Columns[i].Caption.ToString()+"t";
    colHeaders +=dt.Columns[i].Caption.ToString() +"n";   
    // to HTTP The data information written to the output stream
    resp.Write(colHeaders); 
    // Process the data row by row   
    foreach(DataRow row in myRow)
    {
     // In the current row, the data is retrieved column by column, and between the data is obtained by column t Split and add a carriage return at the end n
     for(i=0;i      ls_item +=row[i].ToString() + "t";     
     ls_item += row[i].ToString() +"n";
     // Current row data is written HTTP Output stream, and empty ls_item For downlink data     
     resp.Write(ls_item);
     ls_item="";
    }
   }
   else
   {
    if(typeid=="2")
    { 
     // from DataSet In direct export XML Data and write HTTP The output stream
     resp.Write(ds.GetXml());
    }    
   }
   // Write the data in the buffer to HTTP Header file
   resp.End();
}

2. Generated by datagrid

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 for this one

public void OutputExcel(DataView dv,string str) 
{
   //
   // TODO: Add the constructor logic here
   //
   //dv To print to Excel The data, str Is the title
   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 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 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;// Set the date-type field format to center alignment
     }
     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;// Sets the character type field format to center alignment
     }
     else
     {
      excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
     }
    }
   }
   //
   // loading 1 A total line
   //
   int rowSum = rowIndex + 1;
   int colSum = 2;
   excel.Cells[rowSum,2] = " A combined ";
   xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
   //
   // Sets 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;// Set to light yellow, total 56 Kind of
   //
   // Gets 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;
   //
   // Sets the report table to the most appropriate 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 Cross-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 the 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 the left line to be bold
   xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;// Set the top edge to be bold
   xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;// Set the right line to be bold
   xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;// Set bottom edge to bold
   //
   // According to the effect
   //
   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 for " File download / Save as " Dialog box specifies the default file name
   Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
   // Add headers, specify file sizes, and let the browser show download progress
   Response.AddHeader("Content-Length", file.Length.ToString());
   
   // The specified return is 1 Streams that cannot be read by the client must be downloaded
   Response.ContentType = "application/ms-excel";
   
   // Send the file stream to the client
   Response.WriteFile(file.FullName);
   // Stop the execution of the page
  
   Response.End();
}


Import and export some problem summaries in EXCEL

1. Add a reference to the project:
Right-click the Project Explorer reference > Add a reference -- > Select the.NET TAB > Choose Microsoft. Office. Interop. Excel -- -- > Sure;

The version number of the NET component is 12.0.0.0. In this example, it is the version of Office2007:
2. In the project use Microsoft. Office. Interop. Excel:
If you want to use Microsoft. Office. Interop. Excel, you first need to reference namespace in the project:
using Microsoft.Office.Interop.Excel;
3. Create Excel.Application related objects

// To establish Application object 
        Microsoft.Office.Interop.Excel.Application myExcel = new Application();
  // To establish Workbooks object
         Workbooks myBooks = myExcel.Application.Workbooks;
     // To establish 1 a System.Reflection.Missing the object object
        object oMissing = System.Reflection.Missing.Value;

4. Open or create the book file for Excel
// Open the Excel File, note the" ExccelFilePath "For the Excel The physical address of the 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);
     // new Workseet object , Here is the worksheet to be operated on , there are two ways to get the worksheet you are currently working on: using the index value of the worksheet or using the name of the worksheet, which defaults to" sheet1 " / " Sheet2 "Etc.
     Worksheet mySheet = (Worksheet)myBook.Worksheets[1];
    // If it's new EXCEL Workbook, the following two lines need to be set to ensure that there is a workbook 1 A worksheet,
    Workbook workbook1 = excel1.Workbooks.Add(true);
    Worksheet mySheet= (Worksheet)workbook1.Worksheets["sheet1"];
    // Set up the EXCEL Whether the object displays an interface. Default is false No display interface
    myExcel.Visble=true;

5. 1 Some more important operations for Excel
1. Get the ES76en object
(1) Get Range object with 1 cell:

// Select the first 1 Line, the first 1 The cell of the column is Range object 
            Range r = (Excel.Range)mySheet.Cells[1, 1];
          // Select multiple contiguous cells as Range object
   Range r=(Excel.Range)Range.get_Range("A1:F3")

Assign a value to a cell or fetch a value from a cell:
// Has chosen the Range Object assignment: 
   r.Text=" China ";
       // Not to choose Range Object assignment :
        mySheet.Cells[1,2].Text=" China ";
     // Has chosen the Range Object value:
   String strValue= r.Text;
       // Not to choose Range Value of object :
    String  strValue=  mySheet.Cells[1,2].Text;

(3) Set borders for cells
mySheet.Cells[2, 1].BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null);// Line drawing 

(4) Merge cells
// Before merging cells, select the cell to be merged as Range object 
            Range r=Range.get_Range("A1:F3") ;
  // The merged cells are now set
         r.MergeCells = true;

⑤, Set the font, font size, background color and other properties of the cell
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 the color

⑥, delete line 1:
// First get the row to delete Range
    Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mySheet.Rows[sendedRow[1], Type.Missing];
   // Note that the deleted line number is replaced with the following line number after deleting the row. If deleting line by line, delete from the largest line number to the smallest line number first
       range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);

⑦ Get the number of rows with data
int rowsint = mySheet.UsedRange.Cells.Rows.Count;

6. EXCEL file save and exit

1. Save and exit EXCEL

myBook.Save();
   myBooks.Close();
   myExcel.Quit();

2, EXCEL specified file save

myBook.Close(true, FilePath +_file_Name, null);

7. Release EXCLE resources and end the EXCEL process
There are a lot of users in this aspect are talking about a variety of methods, after my practice, the following aspects can really end the task process of EXCEL:
1. Put all of the above operations on EXCEL into one method.
EXCEL = EXCEL; EXCEL = null; EXCEL = EXCEL; EXCEL = EXCEL; EXCEL = EXCEL;
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 and execute the operation EXCEL above, add GC.Collect() after the operation EXCEL: EXCEL: GC.Collect() : GC.Collect();
// In the following methods OutPutEXCEL() And the way to do that is to output EXCEL File for EXCEL Method of operation 
private void killExcel()
{
  outPutEXCEL();
  GC.Collect();
  GC.WaitForPendingFinalizers();
}

GC.Collect.Collect.EXCEL.Collect.EXCEL.Collect (); GC.Collect(); GC.Collect(); GC.Collect(); GC.Collect(); EXCEL.Collect () The business of operating EXCEL is written in a single block, and "GC" never ends the EXCEL process. In WEB applications, this phenomenon is a terrible thing. The reason is that GC does not clean up junk memory in this block.

Call the killEXCEL() method in a business event:

protected void LinkButton3_Click(object sender, EventArgs e)
{
  // export EXCEL
  killExcel();
}

8. 1 Basic Settings of some permissions:

There is no problem in debugging the program in the development environment using the above methods. After it is published to the server, the program still cannot run normally. The following permissions are required:

NET export Excel 80070005 error:

net: {00024500-0000-0000-C000-000000000046} : Unable to retrieve a component from an COM factory where CLSID is {00024500-0000-0000-C000-000000000046} : 80070005
The specific configuration method is as follows:
① Install Excel software for office on the server.
② in the "beginning "- > Enter dcomcnfg in "Run".exe starts "Component Service".
③ Double click "Component Services "- in turn > "Computer "- > "My computer "- > "DCOM configuration"
④ In the "DCOM configuration ", find "Microsoft Excel application ", right-click on it, and then click "Properties". The "Microsoft Excel application properties "dialog box will pop up
⑤ Click the "Identity" TAB and select "Interactive User".
⑥ Click the "security" label, in the "start and activate permissions" click "custom ", and then click the corresponding" edit "button, in the pop-up" security "dialog box filled with 1 "NETWORK SERVICE" user (pay attention to choose this computer name), and give it" local start "and" local activation "permissions.
⑦ still "security" label, click "custom" on "access permissions ", then click" edit ", in the pop-up "security" dialog box also fill in 1 "NETWORK SERVICE" user, and then give "local access" permissions.
If error 8000401a occurs after setting the interactive user, you can cancel the interactive user and designate it as administratr, which can temporarily solve the problem. A further solution remains to be explored.
⑨ With the Settings in point 8, opening Excel might show "object references or links cannot be used" and cell pastings cannot be performed. The reason is unknown, it will disappear after unsetting.

I hope this article is helpful to the asp.net program design.


Related articles: