Export in. net using Excel (Summary)

First, add Microsoft Office Web Components to the project> Add reference> com, and then define an owc. spreadsheetclass: dim xlsheet as new owc. spreadsheetclass, and then you can add data to sheet, using xlsheet. cells (2, 2) = "table filled content" format to add data, use xlsheet. range ("B3: F3") sets the cell style.
Finally, save the Excel file on the hard disk using xlsheet. activesheet. Export (path, owc. sheetexportactionenum. ssexportactionnone.
The advantage of this method is that you do not need to assign system permissions. The disadvantage is that the format attribute settings of cells are not rich enough, and some attributes cannot be set or are difficult to set.
2. Use Excel application
Before using this method, you must first set permissions for the ASPNET user. Otherwise Program It cannot be executed. The process of setting permissions is as follows:
Run mongodcomcnfg.exe "at runtime to start" Distributed COM Configuration Attribute"
Double-click "Microsoft Excel application" in the Application List box"
Select the "Security" tab
Select "use custom Access Permissions" and click "edit ".
Add a local "ASPnet" user with the "Allowed access" permission"
In the "Security" tab, select the "use custom launch permission" button and click "edit ".
Add a local "ASPnet" user with the "Allowed access" permission"
The above work can be performed once on the server that needs to execute Excel export.
In the future, select "Project-> Add reference" in each project and select the "com" tab to add "Microsoft Excel X. "x Object Library" is in the project, and you can execute the Excel export operation later.
When exporting data, first define the Excel application, Excel workbooks, and Excel worksheets variables:
Dim excelapp as Excel. Application
Dim excelbook as Excel. Workbook
Dim excelsheet as Excel. Worksheet
Excelapp = new excel. Application
Excelbook = excelapp. workbooks. Add
Excelsheet = excelbook. worksheets (1)
Then you can put the data in the Excel sheet, and you can easily set the attribute parameters of the cell. For example, you can use excelsheet. Rows (introwindex). rowheight = dblrowheight to set the Row Height.
This method can be used to implement almost all Excel functions. If you do not know how to implement a function using Excel application, you can use "record macro-execute Operation-view macro" in Excel Code "To obtain the attributes and methods used to implement this function.
To save the data to the hard disk, you only need to use the excelbook. saveas (filename) method.
The advantage of this method is that it is powerful and can implement almost all Excel functions. The disadvantage is that permission configuration is required, and the running time is relatively long and the resource usage is relatively high. After the end, there may be residual Excel processes in the system.
3. Directly save as a text file
Directly Save the data as a text file in the HTML table format, and change the extension to XLS to open it in Excel, or follow the XML format supported after Office XP, the functions of this format are equally powerful. The disadvantage is that you need to know various functions or XML expression methods in Excel cell format, which requires a large workload.
This method does not require permission configuration (you only need to be able to write files to the hard disk), and the execution speed is fast. The disadvantage is that you need to know a lot of XML or HTML expressions in Excel format, and you have a lot of work to prepare in the early stage. First, add Microsoft Office Web Components to the project> Add reference> com, and then define an owc. spreadsheetclass: dim xlsheet as new owc. spreadsheetclass, and then you can add data to sheet, using xlsheet. cells (2, 2) = "table filled content" format to add data, use xlsheet. range ("B3: F3") sets the cell style.
Finally, save the Excel file on the hard disk using xlsheet. activesheet. Export (path, owc. sheetexportactionenum. ssexportactionnone.
The advantage of this method is that you do not need to assign system permissions. The disadvantage is that the format attribute settings of cells are not rich enough, and some attributes cannot be set or are difficult to set.
2. Use Excel application
Before using this method, you must first set permissions for the ASPNET user, otherwise the program will not be able to execute. The process of setting permissions is as follows:
Run mongodcomcnfg.exe "at runtime to start" Distributed COM Configuration Attribute"
Double-click "Microsoft Excel application" in the Application List box"
Select the "Security" tab
Select "use custom Access Permissions" and click "edit ".
Add a local "ASPnet" user with the "Allowed access" permission"
In the "Security" tab, select the "use custom launch permission" button and click "edit ".
Add a local "ASPnet" user with the "Allowed access" permission"
The above work can be performed once on the server that needs to execute Excel export.
In the future, select "Project-> Add reference" in each project and select the "com" tab to add "Microsoft Excel X. "x Object Library" is in the project, and you can execute the Excel export operation later.
When exporting data, first define the Excel application, Excel workbooks, and Excel worksheets variables:
Dim excelapp as Excel. Application
Dim excelbook as Excel. Workbook
Dim excelsheet as Excel. Worksheet
Excelapp = new excel. Application
Excelbook = excelapp. workbooks. Add
Excelsheet = excelbook. worksheets (1)
Then you can put the data in the Excel sheet, and you can easily set the attribute parameters of the cell. For example, you can use excelsheet. Rows (introwindex). rowheight = dblrowheight to set the Row Height.
This method can be used to implement almost all Excel functions. If you do not know how to implement a function using Excel application, you can use the "record macro-execute Operation-view macro code" method in Excel to obtain the attributes and methods used to implement this function.
To save the data to the hard disk, you only need to use the excelbook. saveas (filename) method.
The advantage of this method is that it is powerful and can implement almost all Excel functions. The disadvantage is that permission configuration is required, and the running time is relatively long and the resource usage is relatively high. After the end, there may be residual Excel processes in the system.
3. Directly save as a text file
Directly Save the data as a text file in the HTML table format, and change the extension to XLS to open it in Excel, or follow the XML format supported after Office XP, the functions of this format are equally powerful. The disadvantage is that you need to know various functions or XML expression methods in Excel cell format, which requires a large workload.
This method does not require permission configuration (you only need to be able to write files to the hard disk), and the execution speed is fast. The disadvantage is that you need to know a lot of XML or HTML expressions in Excel format, and you have a lot of work to prepare in the early stage.