Summary of ASP. NET Excel file generation process

Reposted from: summary of the history of a difficult ASP. NET Excel file generation by Xuan soul's blog

 

The last task of the statistics system is to export data to Excel. as it was the first time, I learned and made a great deal of effort. After that, I made a summary.

The development environment is as follows:

Operating System: Windows 7

Development Tool: vs2010

. NET: 4.0

Server System: Windows2003

Office version: 2003

The main problems involved in this article:

1) how to generate an Excel file

2) Merge cell and numeric column identifiers to letter column identifiers

3) Cause of "exception from HRESULT: 0x800A03EC"

4) "Empty object" exception and server deployment

5) "80080005 exception" and permission Configuration

1. method for generating Exel

To generate an Excel file, call the local Office COM component to operate the Excel file. After creating a project, add a reference to Microsoft. Office. Interop. Excel of the corresponding Office version, as shown in Figure 1-1.

Figure 1-1 add Microsoft. Office. Interop. Excel reference

For convenience, here is an example program to illustrate my process. The overall structure of the sample program is shown in code list 1-1.

Code List 1-1 sample program structure

Using System;

Using System. Collections. Generic;

Using System. Linq;

Using System. Text;

Using Excel = Microsoft. Office. Interop. Excel;

Namespace excel

{

Class Program

{

Private Excel. Application app = null;

Private Excel. Workbook workbook = null;

Private Excel. Worksheet worksheet = null;

Private Excel. Range workSheet_range = null;

Static void Main (string [] args)

{

}

Public void createExcel ()

{

}

Public void addData (int row, int col, string data, string format, string cell1, string cell2)

{

}

Public void export ()

{

}

}

}

First, I declare the four fields as follows:

1) Excel. Application app. The Application Object indicates the Excel Application itself. The Application object exposes a large amount of information about running applications, options applied to the instance, and the objects of the current user opened in the instance.

2) Excel. Workbook workbook. The Excel. Workbook class indicates a single Workbook in an Excel application.

3) Excel. Worksheet worksheet. Excel provides the Sheets set as the attribute of the Microsoft. Office. Interop. Excel. Workbook object, but there is no Sheet class in Excel. On the contrary, each member of the Sheets set is a Microsoft. Office. Interop. Excel. Worksheet object, or a Microsoft. Office. Interop. Excel. Chart object.

4) Excel. Range workSheet_range. Excel. Range objects are the most common objects in Excel applications. Before being able to process any Range in an Excel file, it must be represented as a Range object and its methods and attributes must be processed. The Range object indicates a cell, a row, a column, a Range of cells that contain one or more cell blocks (can be continuous or discontinuous), or even a group of cells in multiple worksheets.

The createExcel () method is used to create an Excel instance, that is, the four variables declared for initialization. The implementation code is shown in List 1-2 of the Code.

Code List 1-2 initialization variable

Public void createExcel ()

{

App = new Excel. Application ();

App. Visible = true;

Workbook = app. Workbooks. Add (1); // create a workbook

Worksheet = (Excel. Worksheet) workbook. Sheets [1]; // create a worksheet

}

For example, in code list 1-2, the Workbook object is created by the Application object, and the Worksheet object is created by the Workbook object.

The addData () method is used to add data to a table. The four parameters are row, column, data, and data format. The range start lattice and range end lattice. The implementation code is shown in code 1-3.

Code List 1-3 add data

Worksheet. Cells [row, col] = data;

WorkSheet_range = worksheet. get_Range (cell1, cell2 );

WorkSheet_range.Borders.Color = System. Drawing. Color. Black. ToArgb ();

WorkSheet_range.NumberFormat = format;

Each grid in Excel is uniquely identified by the coordinates of rows and columns. The row ID is A number, from 1 to n, and the column ID is A letter, from A to Z, from AA to AZ .......

The workSheet_range object is initialized by the worksheet. get_Range (cell1, cell2) method. The process from cell1 to cell2 is processed as a whole. WorkSheet_range.NumberFormat.

The export () method is used to export the generated Excel file, as shown in code list 1-4.

Code List 1-4 Export Excel

Public void export ()

{

Workbook. SaveCopyAs (@ "D:/aa.xls ");

App. Quit ();

}

The Workbook object is used to Save an Excel file by using the Save, SaveCopyAs, and SaveAs methods. The SaveCopyAs method is used here. Save and call the Quit () method of the Application object to release resources.

Everything works properly locally, as shown in test result 1-2.

Figure 1-2 Test Results

2. Merge cell and numeric column identifiers to letter column identifiers

When merging cells, you must pass the column ID represented by letters to Excel. range object. the Merge (int) method, but we use numerical identification to retrieve data and fill data. At this time, it must be in the form of 1-3.

Figure 1-3 letter Column Identification in Excel

The algorithms provided in code list 1-5 can solve this problem.

Code List 1-5 numeric column identifiers to letter column identifiers

Public string ToName (int index)

{

If (index <= 0)

Throw new Exception ("invaild parameter ");

Index --;

List <string> chars = new List <string> ();

Do

{

If (chars. Count> 0)

Index --;

Chars. Insert (0, (char) (index % 26 + (int) 'A'). ToString ());

Index = (int) (index-index % 26)/26 );

} While (index> 0 );

 

Return String. Join (string. Empty, chars. ToArray ());

}

Iii. "cause of exception from HRESULT: 0x800A03EC"

An error "exception from HRESULT: 0x800A03EC" may occur accidentally. The reason is very simple. The rows and columns in Excel start from 1. If we habitually give it to worksheet. cells [0, 0] Or worksheet. cells [1, 0] Or worksheet. if Cells [0, 1] is assigned a value, this exception is thrown.

4. "object reference not set to object instance" and server deployment

Everything on the client runs normally. Next, deploy the DLL on the server. However, the server does not have to install the Office at all. I have been searching for a long time on the website and have not found a solution for this method. I had to install the Office. I thought everything was fine, but every time I was prompted "I didn't set the object reference to the object instance ". Only by throwing an exception can we know that the main fields in the sample program are empty, that is, the local COM is not successfully called. Find Region.

Copy EXCEL. EXE from the server to the local machine, start TlbImp from the command line of VS2010, and execute the command TlbImp/out: Interop. Excel. dll Excel.exe. Generate Interop. Excel. dll.

Remove Microsoft. office. interop. excel. dll reference, add Interop. excel. dll, set using Excel = Microsoft. office. interop. excel, which is using Excel = Interop. excel, local running program no problem. When the server-side program is updated again, a new exception occurs: The component whose CLSID is {00024500-0000-0000-c000-0000000000000046} in the COM class factory fails to be retrieved because of the following error:80080005.

5. "80080005 exceptions" and permission Configuration

"80080005 exception" is caused by insufficient permissions of the application to operate the COM component. The following operations are available:

1) control panel-> Administrative Tools-> component services-> Computer-> my computer-> DCOM-> Microsoft Excel applications

2) Click properties to open the Properties dialog box for this application.

3) Click the ID tab and select an interactive user.

4) Click the Default Security tab. Set the access permissions of ASP. NET users on the current server.

5) Click Edit default value for the startup permission. Set the access permissions of ASP. NET users.

At this point, the program runs successfully.

6. Close the Excel Process

If you export an Excel file in this way, the server starts an Excel process. This may cause two problems. One is server resource consumption, and the other is that when the number of processes reaches the upper limit, an exception is thrown and the COM call fails. At this time, we need to try to end the Excel process. I used the process kill method, similar to the following practice:

Private void DoExcel ()
{
Microsoft. Office. Interop. Excel. Application application = new Microsoft. Office. Interop. Excel. Application ();
// Release all referenced resources here
Application. Quit ();
KillExcel (application );
}

[DllImport ("User32.dll", CharSet = CharSet. Auto)]
Public static extern int GetWindowThreadProcessId (IntPtr hwnd, out int ID );
Public static void KillExcel (Microsoft. Office. Interop. Excel. Application excel)
{
IntPtr t = new IntPtr (excel. Hwnd); // obtain the handle. The specific function is to obtain the memory entry.

Int k = 0;
GetWindowThreadProcessId (t, out k); // obtain the unique identifier k of the process.
System. Diagnostics. Process p = System. Diagnostics. Process. getprocpolicyid (k); // get a reference to Process k
P. Kill (); // closes process k
}