C # + Excel

Using system;
Using system. Collections. Generic;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. text;
Using system. Windows. forms;
Using Microsoft. Office. InterOP. Excel;
Using system. Data. sqlclient;
Using system. Data. oledb;
Using system. reflection;
Namespace excelprj
{
/// <Summary>
/// In the Excel system, the main file excel.exe is a COM component. You can use the exel.exe file in the. Net project to control the EXCEL function.
/// Interacting with COM components is implemented by using the Wrapper class and proxy mechanisms. The packaging class enables. net Program The interface provided by the COM component can be identified, while the proxy class provides access to the COM interface.
/// </Summary>
Public partial class form1: Form
{
Public form1 ()
{
Initializecomponent ();
}
Private void button3_click (Object sender, eventargs E)
{
Exporttasks (BIND (), datagridview1 );
}
// If Excel is installed on a computer, export the table content to excel.
Public void exporttasks (Dataset tasksdata, datagridview tasksgridview)
{
// Define the Excel component interface to be used
// Define the application object, which indicates the entire EXCEL program
Microsoft. Office. InterOP. Excel. Application excelapp = NULL;
// Define the workbook object, which indicates the working thin
Microsoft. Office. InterOP. Excel. Workbook workbook;
// Defines the worksheet object, which indicates a worksheet in execel.
Microsoft. Office. InterOP. Excel. worksheet Ws = NULL;
// Defines the range object, which represents the cell area.
Microsoft. Office. InterOP. Excel. Range R;
Int ROW = 1; int cell = 1;
Try
{
// Initialize the Application Object excelapp
Excelapp = new Microsoft. Office. InterOP. Excel. Application ();
// Create a task list on the first worksheet of the workbook
Workbook = excelapp. workbooks. Add (xlwbatemplate. xlwbatworksheet );
Ws = (worksheet) Workbook. worksheets [1];
// Name the worksheet as "Task Management"
WS. Name = "task management ";
# Region create table column headers
// Traverse all columns in the data table
Foreach (maid Cs IN tasksgridview. columns)
{
// If you do not want to display the primary key
If (CS. headertext! = "No ")
{
WS. cells [row, cell] = cs. headertext;
R = (range) WS. cells [row, cell];
WS. get_range (R, R). horizontalalignment = Microsoft. Office. InterOP. Excel. xlvalign. xlvaligncenter;

// Set the column style here
Cell ++;
}
}

// Create a row and output the data view record to the corresponding Excel Cell
For (INT I = 2; I <tasksdata. Tables [0]. Rows. Count; I ++)
{
For (Int J = 1; j <tasksdata. Tables [0]. Columns. Count; j ++)
{

WS. cells [I, j] = tasksdata. Tables [0]. Rows [I] [J]. tostring ();
// R = (range) WS. cells [I, j];
Range Rg = (range) WS. get_range (WS. cells [I, j], WS. cells [I, j]);
RG. entirecolumn. columnwidth = 20;
// RG. Columns. autofit ();
RG. numberformatlocal = "@";
}
}
# Endregion
}
Catch (exception ex)
{
MessageBox. Show (ex. tostring ());
}

// Display Excel
Excelapp. Visible = true;

}
Private void button5_click (Object sender, eventargs E)
{
Dataset DS = BIND ();
Datagridview1.datasource = Ds. Tables [0];
}
Private dataset BIND ()
{
Sqlconnection conn = new sqlconnection ("Server =.; database = testmanage; Integrated Security = sspi ");
Sqldataadapter da = new sqldataadapter ("select fnumber, fexamnum, fname, fsex, fjobadd, fcardid, fbirdate from stuinfo", Conn );
Dataset DS = new dataset ();
Da. Fill (DS );
Return Ds;
}
Private void button2_click (Object sender, eventargs E)
{
Savefiledialog SFD = new savefiledialog ();
SFD. Title = "select the path to store exported Excel files ";
SFD. filename = system. datetime. Now. tow.datestring () + "-Student Information ";
SFD. Filter = "Excel document (*. xls) | *. xls ";
SFD. showdialog ();

If (SFD. filename! = "")
{

Microsoft. Office. InterOP. Excel. Application excelapp = new Microsoft. Office. InterOP. Excel. Application ();
If (excelapp = NULL)
{
MessageBox. Show ("an Excel object cannot be created. Excel may not be installed on your machine ");
}
Else
{
Microsoft. Office. InterOP. Excel. workbooks = excelapp. workbooks;
Microsoft. Office. InterOP. Excel. Workbook workbook = workbooks. Add (xlwbatemplate. xlwbatworksheet );
Microsoft. Office. InterOP. Excel. worksheet = (worksheet) Workbook. worksheets [1];
Dataset DS = BIND ();
For (INT I = 1; I <Ds. Tables [0]. Rows. Count; I ++)
{
For (Int J = 1; j <Ds. Tables [0]. Columns. Count; j ++)
{
If (I = 1)
{
Worksheet. cells [I, j] = maid [J]. headertext;

}
Worksheet. cells [I + 1, J] = Ds. Tables [0]. Rows [I] [J]. tostring ();
}
}
// Save Method 1: Save the workbook
// Workbook. saveas (@ "F: \ cdata.xls ",
// Missing. Value, missing. Value, missing. value,
// Microsoft. Office. InterOP. Excel. xlsaveasaccessmode. xlnochange, missing. Value, missing. Value, missing. value,
// Missing. Value, missing. value );
// Method 2: Save the worksheet
// Worksheet. saveas (@ "F: \ cdata2.xls ",
// Missing. Value, missing. value,
// Missing. Value, missing. Value, missing. value );
//// Method 3
// Workbook. Saved = true;
// Workbook. savecopyas (SFD. filename );

System. runtime. interopservices. Marshal. releasecomobject (worksheet );
Worksheet = NULL;
System. runtime. interopservices. Marshal. releasecomobject (workbook );
Workbook = NULL;
Workbooks. Close ();
System. runtime. interopservices. Marshal. releasecomobject (workbooks );
Workbooks = NULL;
Excelapp. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (excelapp );
Excelapp = NULL;
MessageBox. Show ("Excel exported! ");
}
}

}
Private void button4_click (Object sender, eventargs E)
{
String strexcelfilename = @ "f :\\ 2007-07-16-student information .xls ";
String strsheetname = "sheet1 ";
# Region ASPnet correct Excel operation
//// Source Definition
// String strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + strexcelfilename + "; extended properties = 'excel 8.0; HDR = no; IMEX = 1 '";
/// SQL statement
// String strexcel = "select * from [" + strsheetname + "$]";
//// Define the stored data table
// Dataset DS = new dataset ();
/// Connect to the data source
// Oledbconnection conn = new oledbconnection (strconn );
// Conn. open ();
/// Adapt to the data source
// Oledbdataadapter adapter = new oledbdataadapter (strexcel, Conn );
// Adapter. Fill (DS, "res ");
// Conn. Close ();
//// In general, the first row of the. Excel table is the column name.
// Datagridview2.datasource = Ds. Tables ["res"];
# Endregion
# Region COM component read complex Excel
Microsoft. Office. InterOP. Excel. Application excelapp = NULL;
Microsoft. Office. InterOP. Excel. Workbook workbook;
Microsoft. Office. InterOP. Excel. worksheet Ws = NULL;
Try
{
Excelapp = new Microsoft. Office. InterOP. Excel. Application ();
Workbook = excelapp. workbooks. open (@ "f :\\ book1.xls", missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value );
Ws = (worksheet) Workbook. worksheets [1];

// Excel is 256 columns by default ..
MessageBox. Show (WS. cells. Columns. Count. tostring ());
Excelapp. Quit ();
}
Catch (exception ex)
{
Throw ex;
}
# Endregion
}
}
} E