C # develop an EXCEL report

Recently, it is not difficult to export an Excel file or a summary of an excel file with a complex table header. Basically, the Excel file has been exported. The summary of a complex table header excel file has also been implemented using the Active control, however, the efficiency is not ideal. Finally, a group of friends reminded me to try OPENDATASOURCE/DATAROWSET, and found that the efficiency was good.
First, let's talk about exporting Excel. For example, if I want to export an excel file

 

First, create an excel template with only the header

 

Because the result is a complete data of two actions, and the first four columns are merged. I don't know how many rows of data exist, so I need to use macros. The following is the macro code for automatically merging rows written by me.

Sub Macro1 () ''macro1 macro'' Dim I As IntegerDim count As IntegerDim rownum As Integercount = Sheet1.UsedRange. rows. count 'Here we get the number of record rows in sheet3 For I = 6 To count Step 2' and merge the first four columns of Range ("A" & I &": A "& (I + 1 )&""). select Range ("A" & (I + 1 )). activate With Selection. horizontalAlignment = xlCenter. verticalAlignment = xlCenter. wrapText = False. orientation = 0. addIndent = False. indentLevel = 0. shrinkToFit = False. readingOrder = xlContext. mergeCells = False End With Selection. merge Range ("B" & I & ": B" & (I + 1 )&""). select Range ("B" & (I + 1 )). activate With Selection. horizontalAlignment = xlCenter. verticalAlignment = xlCenter. wrapText = False. orientation = 0. addIndent = False. indentLevel = 0. shrinkToFit = False. readingOrder = xlContext. mergeCells = False End With Selection. merge Range ("C" & I & ": C" & (I + 1 )&""). select Range ("C" & (I + 1 )). activate With Selection. horizontalAlignment = xlCenter. verticalAlignment = xlCenter. wrapText = False. orientation = 0. addIndent = False. indentLevel = 0. shrinkToFit = False. readingOrder = xlContext. mergeCells = False End With Selection. merge Range ("D" & I & ": D" & (I + 1 )&""). select Range ("D" & (I + 1 )). activate With Selection. horizontalAlignment = xlCenter. verticalAlignment = xlCenter. wrapText = False. orientation = 0. addIndent = False. indentLevel = 0. shrinkToFit = False. readingOrder = xlContext. mergeCells = False End With Selection. mergeNext iEnd SubSub auto_open () 'Open excel for automatic execution. You can add a method to delete all macro Call Macro1End Sub after the first execution.

 

The preparation of the excel template is over now. The following figure shows how to import data. OPENROWSET is used to import data,

INSERT
OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'excel 8.0; Database = F: \ budget execution (document No. ).xls', [Sheet3 $])
Select * from # temp

For convenience, I wrote a separate stored procedure and inserted the data into the temporary table # temp (because the format in the original table may be different from that in the template ), finally, execute the preceding SQL statement. This is a complete excel export function.
The process of merging an Excel file with a complex table header is similar to the preceding one. It also creates a template to write a macro (mainly to control the format ). The merging process is also stored in the stored process. Use OPENDATASOURCE/DATAROWSET to retrieve the excel Data to be merged (note that you need to remove the table header and you can determine the number of rows in the template header) to insert a temporary table, import the template together.

The following describes the main problems encountered during the development process. You must first enable OPENDATASOURCE/DATAROWSET

Exec sp_configure 'show advanced options', 1

Reconfigure

Exec sp_configure 'ad Hoc Distributed Queries ', 1

Reconfigure

When using OPENDATASOURCE/DATAROWSET, the corresponding excel file must be closed. Otherwise, the following similar error will be reported.

 

 

Secondly, OPENDATASOURCE/DATAROWSET cannot operate on excel across servers. To use it across servers, you must use shared files. the SQL server must be able to access the shared files on the server where excel is located, otherwise, the database will be installed on the server where excl is located.

One of the most painful problems is that Microsoft is no longer supported in WIN2008. JET. OLEDB.4.0. You must download the latest Microsoft. ACE. OLEDB.12.0. JET. replace OLEDB.4.0 with Microsoft. ACE. OLEDB.12.0. If it is a 64-bit system, you need to install 64 Microsoft. ACE. OLEDB.12.0, but 64-bit Microsoft. ACE. OLEDB.12.0 is not compatible with 32-bit office. Therefore, in WIN2008x64, office cannot be installed with OPENDATASOURCE/DATAROWSET. What is the http://www.microsoft.com/zh-cn/download/details.aspx of Microsoft. ACE. OLEDB.12.0? Id = 13255

Question added: When Microsoft. ACE. OLEDB.12.0 is used, it is no problem to read the excel file, but the insert time is a little different. Therefore, insert into must be written in this way.
OPENROWSET ('Microsoft. ACE. OLEDB.12.0 ', 'excel 8.0; Database = c: \ ttest.xls;', 'select B FROM [sheet1 $] ')
Select B from table1. Why haven't you understood it? Write down