Recently processed an ASP.NET project, where there are a lot of reports need to operate. Customers are a financial bureau and businesses in the development zone. Therefore, these reports are financial statements. (Friends who have done reports must know the most complicated financial statements in the report, and not to be a typical Chinese statement, and is a group report, cross-report, three-dimensional general report, etc.) If you use common report tools, For example: crystal reports, such as a relatively popular Web report, dozens of reports will definitely waste the valuable project time in the draw interface.
Our customer group has a characteristic, the computer operation is generally, but generally familiar with Excel, the output of the report or the form of expression is not large, but the format of the report must meet their daily way. Because their jobs say that there is a tool software is essential, that is, MS Office. They are almost like a variety of typography in Excel. If you take a crystal statement or a hard-drawn report such as a wishful report to them, they will definitely take your hard work and EXCEL files in his computer or even a Word file, it is likely to The head of your report refers to the blood, because their reference is their own Office document. I believe that there is no report that there is no report of the report tools can be compared with Excel or Word's flexibility (maybe some companies doing reports are not convinced, don't fight, huh, huh!), Because Word or Excel is not at all. Report tools but office software!
After analyzing the above situation, I decided to take an Excel report in the project (strictly said that Excel is not reporting tool, as described above). Simultaneous ways are used. This is also the point I want to mention in this article. Why use a sample table? We don't care about Excel's master's research, they are experts (words, don't tell, ^ _ ^). They may also require colorful reports, but there is no relationship, give me a sample! (Give me a sample, I can hold the whole need, imitation of Galileo, ^ _ ^) tell me what the cell fills in, remember not to change too much (if I don't have a way, if content Always change, do not use this method, preceding abilities!), Other formats, lines, fonts, colors, mergers, what do you want to change! The content of the form is performed, and the data is performed, and the normal form is filld by the normal form. Of course, the format of the form does not have to be exactly the same as the Excel form (if it is also tired), stored in the database table (can Modify, if this is not necessary to skip the place where it will be involved later). Where you need to generate a report, extract the corresponding content from the database, open the sample, a radish, pit, fill in the blank, haha! It is so simple. How beautiful is it to do, how beautiful is it, huh, huh!
The last report wants to show users or preview what to do, let him download the Excel file, haha! (So, the user see is the Excel file he design or wants! Eighth roads! Ok!)
It's not that line! The following is the actual example of the project!
1. Since it is an Excel report, you want to reference the Excel component, the location is as follows: E: / proGram files / microsoft office / office / excel9.olb2.reportExcelData.cs file is a custom class for the excel report, more than 30 Method, 4000 multi-line code.
Using system;
Using system.data;
Using system.data.sqlclient;
Using system.io;
Namespace wjk.zhkcga
{
///
/// Specially dealt with the class of Excel report files
/// by wjk
///
Public Class ReportExceldata
{
Private dbhelper mydbhelper = new dbhelper ();
Private function myfunc = new function ();
Private approvedata myApprove = new approvedata ();
DBHELPER.STRCTSQLPARAM [] myparam1 = new dbhelper.strctsqlparam [1];
String strsql;
int RET, ICOUNT;
Excel.ApplicationClass myapp;
Excel.Workbook mybook;
Excel.worksheet mysheet;
Public reportExceldata ()
{
//
// TODO: Add constructor logic here
//
}
///
/// Financial Association Fund Application Form
///
/// Material number
/// Temporary file path
/// Create a temporary file user
///
Public int WriteExcel_JJ_BOKUAN (Int ID, String String, String Strunitecode, String String Strerr)
{
STRERR = ""
#Region sample file
String strsamplename = "[sample table] infrastructure fund grant application form;
String strsamplefile = strpath "file // sample table //" strsamplename ".xls";
String strreportfile = strpath "file // report //" StruniteCode "//" StrreportName ".xls";
String strreportfiledb = "// file // Report //" StruniteCode "//" StrreportName ".xls";
IF (! file.exists (strsamplefile))
{
STRERR = "The sample form does not exist";
Return -1;
}
Directory.createdDirectory (StrPath "File // Report //" Strunitecode );if (File.exists (StrreportFile)
File.delete (strreportfile);
File.copy (strsamplefile, strreportfile);
#ndregion
#Region write files
myapp = null;
Mybook = null;
mysheet = NULL;
Object omissiong = system.reflection.missing.value;
MyApp = New Excel.ApplicationClass ();
myapp.visible = false;
Myapp.workbooks.open (Strreportfile, Omissiong, Omissiong, Omissiong, Omissiong, Omissiong, Omissiong, Omissiong, OmissionG, Omissiong, Omissiong, Omissiong, Omissiong, OmissionG, OMISSIONG);
Mybook = myapp.workbooks [1];
MySheet = (Excel.Worksheet) Mybook.activesheet;
#Region a radish a pit
DataSet MySet;
strsql = "SELECT project name, project plan total investment, annual plan investment, contract cost, financial share fund, project unit financial expenditure,"
"The project unit capital balance, the planned investment finance is not dial, the project unit applied for allocation, the financial department to develop funds, the financial department approved allocation,"
"Remarks, invitation units, please sign, invitation unit, head of the unit, financial person in charge, project leader, contact number"
"From infrastructure _ infrastructure fund grant application form where material number =" ID "ORDER BY serial number";
Ret = mydbhelper.runsqlreturndataset (strsql, out myset, out iCount, outstrer);
IF (RET == 0) Return 0;
// IF (iCount <1) return -1;
INT IROW = 7; // Initial line number
For (int i = 0; i { #Region write cell MySheet.cells [Irow I, 1] = MySet.Tables [0] .ROWS [I] [0] .tostring (); MySheet.cells [Irow I, 2] = MySet.Tables [0] .ROWS [I] [1] .tostring (); MySheet.cells [Irow I, 3] = MySet.Tables [0] .ROWS [I] [2] .tostring (); MySheet.cells [IROW I, 4] = MySet.Tables [0]. ROWS [I] [3] .tostring (); MySheet.cells [IROW I, 5] = MySet.Tables [0] .ROWS [i] [4] .tostring (); MySheet.cells [iRow I, 6] = MySet.Tables [0] .ROWS [I] [5] .tostring (); MySheet.cells [iRow I, 7] = MySet.Tables [0] .rows [i] [6] .tostring (); MySheet.cells [iRow i, 8] = MySet.Tables [0] .rows [i] [7] .tostring (); mysheet.cells [iRow i, 9] = MySet.Tables [0] .rows [ I] [8] .toString (); MySheet.cells [iRow I, 10] = MySet.Tables [0] .ROWS [I] [9] .tostring (); MySheet.cells [iRow I, 11] = MySet.Tables [0] .ROWS [I] [10] .tostring (); MySheet.cells [IROW I, 12] = MySet.Tables [0] .ROWS [I] [11] .tostring (); #ndregion } IF (iCount> 0) { MySheet.cells [16,9] = MySet.Tables [0] .ROWS [0] [12] .tostring (); Mysheet.cells [17, 10] = mySet.Tables [0] .ROWS [0] [13] .tostring (); MySheet.cells [17, 12] = MySet.Tables [0] .ROWS [0] [14] .tostring (); Mysheet.cells [19, 2] = mySet.Tables [0] .ROWS [0] [15] .tostring (); MySheet.cells [19,5] = mySet.Tables [0]. ROWS [0] [16] .tostring (); MySheet.cells [19,8] = MySet.Tables [0] .ROWS [0] [17] .tostring (); Mysheet.cells [19,11] = mySet.Tables [0]. ROWS [0] [18] .tostring (); } MySet.dispose (); #REGION total content STRSQL = "SELECT SUM (total investment in project plan), SUM (annual plan investment), sum (contract cost)," "SUM (financial payment), sum (project unit financial expenditure), SUM (project unit fund balance)," "SUM (planned to invest finance is not dial), SUM (project unit application appropriation), SUM (fiscal departments formulated funding)," "SUM (fiscal departments)" "from infrastructure _ infrastructure fund grant application form where material number =" ID; Ret = mydbhelper.runsqlreturndataset (strsql, out myset, out iCount, outstrer); IF (RET == 0) Return 0; Irow = 13; // initial line number For (int i = 0; i { #Region specific parameters MySheet.cells [IROW I, 2] = MySet.Tables [0] .ROWS [I] [0] .tostring (); MySheet.cells [Irow I, 3] = MySet.Tables [0] .ROWS [i] [1] .tostring (); MySheet.cells [Irow I, 4] = MySet.Tables [0] .ROWS [I] [2] .tostring (); MySheet.cells [IROW I, 5] = MySet.Tables [0] .ROWS [I] [3] .tostring (); MySheet.cells [IROW I, 6] = MySet.Tables [0] .rows [i] [4] .tostring (); MySheet.cells [IROW I, 7] = MySet.Tables [0] .rows [i] [5] .tostring (); mysheet.cells [iRow i, 8] = MySet.tables [0] .rows [ I] [6] .toString (); MySheet.cells [Irow I, 9] = MySet.Tables [0] .ROWS [I] [7] .tostring (); MySheet.cells [IROW I, 10] = MySet.Tables [0] .ROWS [I] [8] .tostring (); MySheet.cells [iRow I, 11] = mySet.Tables [0] .ROWS [i] [9] .tostring (); #ndregion } #ndregion #ndregion Mybook.save () ;; Mybook.close (true, strreportfile, true); //myapp.quit (); System.Runtime.InteropServices.Marshal.ReleaseComobject (mysheet); System.Runtime.InteropServices.Marshal.ReleaseComobject (MyBook); System.Runtime.InteropServices.Marshal.ReleaseComobject (myapp); Gc.collect (); #ndregion #Region write database String strdate = datetime.now.toString ("YYYY-MM-DD HH: MM: SS"); STRSQL = "Update attachment table set path = '" strreportfiledb ", type ='. xls ', date ='" strdate ", operator = ' strunitecode "' where serial number = " ID; Return mydbhelper.runsql (strsql, outstrer); #ndregion } / * The following efforts to save 30 similar operations * / } 3.Excel2000 and Excel2002 are different for Excel's reference objects, one is Excel9.olb; the other is Excel10.olb; it can be referenced to the corresponding directory! At the same time, the parameters below Excel2000 and Excel2002 are different. The parameters below Excel2000 will be less than one OMISSIONG. It is OK (as simple!). 4. When operating Excel, an Excel process may occur, which cannot be exited, and the solution is to turn off the Excel process after saving and closing mybook, don't close the Excel process (//myapp.quit ();). This result is that there is always an Excel process on the server. But it doesn't matter, it is much better than starting N an Excel process! (Try a lot of methods from netizens, almost no use, huh, huh) 5. ASP_NET users may appear ink to perform Excel, configure DCOM. Run DCMCNFG.EXE, find the Excel application, configure its properties, identification level, "None", identity logo selection "Interactive User", security page, startup, and access to Everyone. Note: Check if there is a WINWORD process in the current process, if there is, and cannot be completed, then restart your computer. Run your code again is OK. This will not have enough permissions in this way. Document to this temporary end. I think it can solve the headache web report, welcome to discuss each other! More documents, all in www.430000.net!
