Generate an Excel Advanced Report

Not long ago, I said that I would like to generate the following Excel report. I tried a lot of methods. I suddenly thought of Excel reference, Macro. I tried it and found that the result was good.

You can refer to this method to generate an Excel file in any format.ProgramTo write a row, think about how complicated it is. It is more complicated to set an Excel format. in addition, the program cycle efficiency is also very slow, as my method can be directly downloaded and printed directly, all the formats have been set. okay, I don't want to talk much about it. I think it's easy to use it for promotion. For more information, see http://www.cnbolgs.com/xiaobier. here's why Xie:

The database I use here is SQL Server. Let's talk about the ideas:

Create an Excel Style first. For example, the title here is fixed, the Department and date are dynamic, the column name is fixed, and the data in the middle is dynamic, the name of the department examiner is dynamic, and others are static.

Set the style first, so that the style is prepared first:

Why do we need to leave two rows for the statistical function setting of 10th rows? The Excel formula has been set for the total row, for example, C10 is sum (C8: C9) and so on, if you insert rows into the middle of, then Excel

The formula of the 10th rows will be automatically expanded. For example, if sum (C8: C9) is used, the insert row will automatically become sum (C8: C10). We should know that. and insert the row to the middle of in the format of the first row of the root

The format of the Dynamic Data zone is set here.

In the third row, I asked him to reference A2 in sheet2. The Department is B2 and the attendance is C2.

Is sheet2, where sheetdate, department, and partition are column names, used to insert records to the three fields of sheet2

The main data area in is placed in sheet3. The following is the sheet3 style, which is also the column name.

Sheet2 has only one record, so it can be referenced directly at the corresponding position of sheet1. how can we make sheet1 reference the data in sheet3, because the records in sheet3 are dynamic,

I don't know how many rows there are, so I want to use macros. Writing macros is actually quite simple. I didn't write many lines here.Code.

 

Code

Sub Macro1 ()
'
' Macro1 macro
' Macro is recorded by xiaobier. Time:
'
' Shortcut: Ctrl + q
'

Dim I As   Integer
Dim Count As   Integer
Dim Rownum As   Integer
Count = Sheet3.usedrange. Rows. Count -   1   ' Here we get the number of record lines in sheet3 and delete the first row of the column name.

Sheets ( " Sheet1 " ). Select ' Select sheet1

' Insert rows between 8 and 9 in sheet1
For I =   3   To Count step 1
Sheet1.range ( " A9 " ). Select
Selection. entirerow. insert
Next I

Rownum= 1

'Reference the data row in sheet1 to the data in sheet3.
ForI= 8 ToCount+ 7Step1

Range ( " A "   & I) = Rownum

Range ( " B "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, sheet3! R [-6] C [-1], ") "

Range ( " C "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " D "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " E "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " F "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " G "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " H "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " I "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " J "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " K "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " L "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " M "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " N "   & I). formular1c1 =   " = If (sheet3! R [-6] C [-1]> 0, value (sheet3! R [-6] C [-1]), ") "

Range ( " O "   & I) =   Null
Rownum = Rownum +   1
Next I

End sub

'This subroutine allows the macro to be automatically called once an Excel file is opened.
SubAuto_open ()
CallMacro1
End sub

Isn't it easy? If you don't want to write a macro, record it. If you want to do anything, record it. Then copy the recording code.

The style file is created. to generate an Excel file, call system. Io. file. Copy to copy it to the target name. No other operations are performed.

Then, use SQL OpenRowSet to insert records into the sheet3 and sheet2 of the target file name.

 

Code
Set   @ SQL = ' OpenRowSet ( '' Microsoft. Jet. oledb.4.0 '' , '' Excel 8.0; HDR = yes; database = ' + @ Path + @ Fname + ''' , [Sheet3 $]) '  
Exec ( ' Insert ' + @ SQL + ' (Name, late, patient, privacy, cyesis, suckle, children, relatives, delay, rest, law, adjust, year) Select * from # sheetrst3 ' )

-- Note that the column names above correspond to those in sheet3.
  Set   @ SQL = ' OpenRowSet ( '' Microsoft. Jet. oledb.4.0 '' , '' Excel 8.0; HDR = yes; database = '   + @ Path + @ Fname + ''' , [Sheet2 $]) '  
Exec ( ' Insert ' + @ SQL + ' (Sheetdate, department, partition) Select ''' + @ Date + ''' , ''' + @ Deptname + ''' , ''' + @ Operator + '''' )

 

Success...