Scenarios for generating Excel reports from DataView (C #)

Excel| Generate Excel Preface:
A few days ago a colleague asked me how to use C # to guide the data into the Excel file, was more busy without
Gu to go to study, today specially studied a bit, basically buttoned up, the following is a specific description of how to
The data in the DataView is stored in the Excel file in a certain format.
Body:
First, to reference an Excel component, I started out with Office XP, not
Success, and then put XP to kill, loaded 2k, it was successful, so here is the share of office 2k under
Referencing related components to implement functionality, referencing Microsoft in a COM tag in the project
Excel 9.0 The Object Library, when added successfully, there are three additional references in the reference:
Excel, Office, Vbide.





Second, the specific code.
Using System;
Using System.Data;
Using Excel;
Using System.IO;
Namespace test.excelcom
{
<summary>
Import data from DataView into an Excel file
Author: rexsp
Created: 2004-4-4
</summary>
public class Outputexcel
{
#region Private Members
<summary>
DataView of data
</summary>
Private DataView Dv=null;
<summary>
Table title
</summary>
private string Title=null;
<summary>
Output file path
</summary>
private string Outfilepath=null;
<summary>
Enter file name
</summary>
private string Inputfilepath=null;
#endregion
#region Public Properties
<summary>
DataView of data
</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 Constructors
Public Outputexcel ()
{
}
Public Outputexcel (DataView dv,string title)
{
//
TODO: Add constructor logic here
//
}
#endregion
#region Public methods
public void Createexcel ()
{
int rowindex=4;//Line Start coordinate
int colindex=1;//column Start coordinates
ApplicationClass Myapp=null;
Workbook mybook=null;
Worksheet mysheet=null;
If the file does not exist, copy the template file as an output file
This is not a good place to create a file with File.create, because XLS
The empty file also has a fixed format, unlike the text, there may be other
Through the program directly generated by the method of Excel, you can try to try the
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,omissiong,
Omissiong,omissiong,omissiong,omissiong,omissiong,omissiong,omissiong,omissiong);
MYBOOK=MYAPP.WORKBOOKS[1];
mysheet= (worksheet) Mybook.activesheet;

//
Get 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;
Format title with center alignment
}
//
Get 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;//The field format of the date type is centered
}
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;//The field format of the character type is centered
}
Else
{
Mysheet.cells[rowindex,colindex] = Row[col. ColumnName]. ToString ();
}
}
}
//
Load an aggregate 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 section
//
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;//is set to light yellow with a total of 56
//
Get the title of the entire report
//
mysheet.cells[2,2] = title;
//
Set the title format for 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 fit the 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.xlhaligncenteracrossselection;
//
Draw Border
//
Mysheet.get_range (Mysheet.cells[4,2],mysheet.cells[rowsum,colindex]). Borders.LineStyle = 1;
Mysheet.get_range (mysheet.cells[4,2],mysheet.cells[rowsum,2]). Borders[xlbordersindex.xledgeleft]. Weight = xlborderweight.xlthick;//Set left line bold
Mysheet.get_range (Mysheet.cells[4,2],mysheet.cells[4,colindex]). Borders[xlbordersindex.xledgetop]. Weight = xlborderweight.xlthick;//Set top edge Bold
Mysheet.get_range (Mysheet.cells[4,colindex],mysheet.cells[rowsum,colindex]). Borders[xlbordersindex.xledgeright]. Weight = xlborderweight.xlthick;//Set the right line bold
Mysheet.get_range (Mysheet.cells[rowsum,2],mysheet.cells[rowsum,colindex]). Borders[xlbordersindex.xledgebottom]. Weight = xlborderweight.xlthick;//Set Bottom edge Bold
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: When you manipulate Excel, the Excel process may be locked and cannot be retired
, the workaround is to not close Excel after you save and close the MyBook (workbook)
Process (//myapp.quit ();). The result is that the server always has an Excel
Process. There may be insufficient permissions for the Asp_net user to manipulate Excel and configure DCOM. Transport
Row Dcomcnfg.exe, locate the Excel application, configure its properties, authentication level
Select "None", identify "interactive user", Security page, start and access to
Everyone Note: See if there are any winword processes in the current process, if any and
Cannot be ended, restart the computer. Run your code again that's OK. After that
There will be no lack of permissions.
Third, 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 generation Excel";
Ope. Inputfilepath=server.mappath ("Sample.xls");
Ope. Outfilepath=server.mappath ("Test.xls");
Ope. Createexcel ();
#endregion
Note: The first half of this code read my article, "A quick access to a subscription entry."
The reader of the case should recognize, in fact, a collection of data in the class to populate
DataView in the process, followed by the call. Sample.xls is a new, empty
Sample.xls, and then after the execution, the Test.xls document is generated and I execute the
The results are shown below: