How to generate an Excel report from dataview

I. I first need to reference an Excel component. I tried it in Office XP At the beginning, no
After that, I killed XP and installed it with 2 K. So here I will share the article under 2 k office.
Reference related components to implement functions. Reference Microsoft
Excel 9.0 object library. After successful addition, there will be three more references in the reference:
Excel, office, and vbide.

Ii. Details Code .
Using system;
Using system. Data;
Using Excel;
Using system. IO;
Namespace test. excelcom
{
/// <Summary>
/// Import data from dataview to an Excel file
/// By rexsp
/// Create: 2004-4-4
/// </Summary>
Public class outputexcel
{
# Region private member
/// <Summary>
/// Dataview
/// </Summary>
Private dataview DV = NULL;
/// <Summary>
/// Table title
/// </Summary>
Private String title = NULL;
/// <Summary>
/// Output file path
/// </Summary>
Private string outfilepath = NULL;
/// <Summary>
/// Enter the file name
/// </Summary>
Private string inputfilepath = NULL;
# Endregion
# Region Public attributes
/// <Summary>
/// Dataview
/// </Summary>
Public dataview dv
{
Set {DV = value ;}
}
/// <Summary>
/// Table title
/// </Summary>
Public String title
{
Set {Title = value ;}
Get {return title ;}
}
/// <Summary>
/// Output file path
/// </Summary>
Public String outfilepath
{
Set {outfilepath = value ;}
Get {return outfilepath ;}
}
/// <Summary>
/// Input file path
/// </Summary>
Public String inputfilepath
{
Set {inputfilepath = value ;}
Get {return inputfilepath ;}
}
# Endregion

# Region Constructor
Public outputexcel ()
{
}
Public outputexcel (dataview DV, String title)
{
//
// Todo: add the constructor logic here
//
}
# Endregion
# Region Public Method
Public void createexcel ()
{
Int rowindex = 4; // start coordinate of the row
Int colindex = 1; // column start Coordinate
Applicationclass MyApp = NULL;
Workbook mybook = NULL;
Worksheet mysheet = NULL;
// If the file does not exist, copy the template file as the output file.
// Here, if file. Create is used to create a file, it is not feasible because xls
// The empty file also has a fixed format, which is different from the text. Maybe there are other
// Pass Program You can try to generate an Excel file directly.
If (! File. exists (outfilepath ))
{
File. Copy (inputfilepath, outfilepath, true );
}
MyApp = new applicationclass ();
MyApp. Visible = false;
Object omissiong = system. reflection. Missing. value;
MyApp. workbooks. Open (outfilepath, omissiong,
Omissiong, omissiong );
Mybook = MyApp. workbooks [1];
Mysheet = (worksheet) mybook. activesheet;

//
// Obtain the title
//
Foreach (datacolumn Col in DV. Table. columns)
{
Colindex ++;
Mysheet. cells [4, colindex] = col. columnname;
Mysheet. get_range (mysheet. cells [4, colindex], mysheet. cells [4, colindex]). horizontalalignment = xlvalign. xlvaligncenter;
// Set the title format to center and align
}
//
// Obtain the data in the table
//
Foreach (datarowview row in DV)
{
Rowindex ++;
Colindex = 1;
Foreach (datacolumn Col in DV. Table. columns)
{
Colindex ++;
If (Col. datatype = system. type. GetType ("system. datetime "))
{
Mysheet. cells [rowindex, colindex] = (convert. todatetime (row [col. columnname]. tostring (). tostring ("yyyy-mm-dd ");
Mysheet. get_range (mysheet. cells [rowindex, colindex], mysheet. cells [rowindex, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // set the format of the datetime field to center
}
Else
If (Col. datatype = system. type. GetType ("system. String "))
{
Mysheet. cells [rowindex, colindex] = "'" + row [col. columnname]. tostring ();
Mysheet. get_range (mysheet. cells [rowindex, colindex], mysheet. cells [rowindex, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // set the align Field Format to center
}
Else
{
Mysheet. cells [rowindex, colindex] = row [col. columnname]. tostring ();
}
}
}
//
// Load a total row
//
Int rowsum = rowindex + 1;
Int colsum = 2;
Mysheet. cells [rowsum, 2] = "Total ";
Mysheet. get_range (mysheet. cells [rowsum, 2], mysheet. cells [rowsum, 2]). horizontalalignment = xlhalign. xlhaligncenter;
//
// Set the color of the selected part
//
Mysheet. get_range (mysheet. cells [rowsum, colsum], mysheet. cells [rowsum, colindex]). Select ();
Mysheet. get_range (mysheet. cells [rowsum, colsum], mysheet. cells [rowsum, colindex]). Interior. colorindex = 19; // set to light yellow, a total of 56
//
// Obtain the title of the entire report
//
Mysheet. cells [2, 2] = title;
//
// Set the title format of the entire report
//
Mysheet. get_range (mysheet. cells [2, 2], mysheet. cells [2, 2]). Font. Bold = true;
Mysheet. get_range (mysheet. cells [2, 2], mysheet. cells [2, 2]). Font. size = 22;
//
// Set the report table to the optimum width
//
Mysheet. get_range (mysheet. cells [4, 2], mysheet. cells [rowsum, colindex]). Select ();
Mysheet. get_range (mysheet. cells [4, 2], mysheet. cells [rowsum, colindex]). Columns. autofit ();
//
// Set the title of the entire report to center across Columns
//
Mysheet. get_range (mysheet. cells [2, 2], mysheet. cells [2, colindex]). Select ();
Mysheet. get_range (mysheet. cells [2, 2], mysheet. cells [2, colindex]). horizontalalignment = xlhalign. xlhaligncenter1_ssselection;
//
// Draw a border
//
Mysheet. get_range (mysheet. cells [4, 2], mysheet. cells [rowsum, colindex]). Borders. linestyle = 1;
Mysheet. get_range (mysheet. cells [], mysheet. cells [rowsum, 2]). Borders [xlbordersindex. xledgeleft]. Weight = xlborderweight. xlthick; // You Can bold the left line.
Mysheet. get_range (mysheet. cells [], mysheet. cells [4, colindex]). Borders [xlbordersindex. xledgetop]. Weight = xlborderweight. xlthick; // You Can bold the upper line.
Mysheet. get_range (mysheet. cells [4, colindex], mysheet. cells [rowsum, colindex]). borders [xlbordersindex. xledgeright]. weight = xlborderweight. xlthick; // you can specify the width of the right line.
Mysheet. get_range (mysheet. cells [rowsum, 2], mysheet. cells [rowsum, colindex]). borders [xlbordersindex. xledgebottom]. weight = xlborderweight. xlthick; // you can specify the width of the lower line.
Mybook. Save ();;
Mybook. Close (true, outfilepath, true );
System. runtime. interopservices. Marshal. releasecomobject (mysheet );
System. runtime. interopservices. Marshal. releasecomobject (mybook );
System. runtime. interopservices. Marshal. releasecomobject (MyApp );
GC. Collect ();

}
# Endregion
}

}
Note: During Excel operations, the Excel process may be locked and cannot be returned.
After the mybook (workbook) is saved and closed, do not close the Excel file.
Process (// MyApp. Quit ();). The result is that there is always an Excel
Process. Asp_net users may have insufficient permissions to operate on Excel and configure DCOM. Operation
Dcomcnfg.exe, locate the Excel application, configure its attributes, and set the authentication level.
Select "NONE", select "Interactive User" as the ID, and access the security page.
Everyone. Note: Check whether any winword process exists in the current process.
The computer cannot be restarted. Running your code again is OK. After that
There will be no insufficient permissions.
Iii. Call
# Region test Excel
Quickitemcollection qic = new quickitemcollection ();
Qic. getallinfo ();
Dataview DV = new dataview ();
Datatable dt = new datatable ("Excel ");
DT. Columns. Add ("ID", system. type. GetType ("system. String "));
DT. Columns. Add ("itemname", system. type. GetType ("system. String "));
Int qiccount = qic. count;
For (INT I = 0; I <qiccount; I ++)
{
Datarow DR = DT. newrow ();
Dr [0] = qic [I]. ID;
Dr [1] = qic [I]. itemname;
DT. Rows. Add (DR );
}
Outputexcel ope = new outputexcel ();
Ope. DV = DT. defaultview;
Ope. Title = "test to generate EXCEL ";
Ope. inputfilepath = server. mappath ("sample.xls ");
Ope. outfilepath = server. mappath ("test.xls ");
Ope. createexcel ();
# Endregion
Note: The first half of this code has read my article "a fast access subscriber
Readers should recognize the case, that is, filling in the data in the Collection class
The process in dataview is called later. Sample.xls is a new empty
After the execution is complete, the test.xls document will be generated.