asp.net C # operation Excel detailed

ASP tutorial. NET C # operations Excel detailed

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>


The main file Excel.exe in an Excel system is itself a COM component that enables you to control Excel by referencing the Exel.exe file in a. NET project


Interoperability with COM components is achieved by using the mechanism of "wrapper class" (wrapper Class) and "proxy". Wrapper classes Enable. NET programs to recognize interfaces provided by COM components, while proxy classes provide access to COM interfaces


</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 your computer, side export table contents to Excel


public void Exporttasks (DataSet tasksdata, DataGridView Tasksgridview)


{


Define the Excel component interface to use


Defines the Application object, which represents the entire Excel program


Microsoft.office.interop.excel.application excelapp = null;


Defines the workbook object, which represents the workbook


Microsoft.office.interop.excel.workbook workbook;


Defines a Worksheet object that represents a sheet in a execel


Microsoft.office.interop.excel.worksheet Ws=null;


Defines a Range object that represents a range of cells


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 in the workbook


Workbook = Excelapp.workbooks.add (Xlwbatemplate.xlwbatworksheet);


WS = (worksheet) workbook.worksheets[1];


Name the worksheet named "Task Management"


Ws.name = "task management";


#region Create a column header for a table


Traverse all columns in a datasheet


foreach (DataGridViewColumn cs in Tasksgridview.columns)


{


If you don't want to show the primary key,


if (Cs.headertext!= "number")


{


Ws.cells[row, cell] = Cs.headertext;


R = (range) Ws.cells[row, cell];


Ws.get_range (R, r). HorizontalAlignment = Microsoft.office.interop.excel.xlvalign.xlvaligncenter;





The style used here to set the column


cell++;


}


}





Create rows and output Data View records to corresponding Excel cells


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 ());


}





Show 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 = "Please select the path to store the exported Excel file";


Sfd.filename = system.datetime.now.toshortdatestring () + "-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 ("Cannot create an Excel object, you may not have Excel installed on your machine");


}


Else


{


Microsoft.office.interop.excel.workbooks workbooks = excelapp.workbooks;


Microsoft.office.interop.excel.workbook workbook = Workbooks.Add (Xlwbatemplate.xlwbatworksheet);


microsoft.office.interop.excel.worksheet 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] = Datagridview1.columns[j].headertext;





}


Worksheet.cells[i+1, J] = ds.tables[0].rows[i][j].tostring ();


}


}


Save Way One: Save workbook


Workbook.saveas (@ "F:cdata.xls",


Missing.value,missing.value,missing.value,missing.value,missing.value,


Microsoft.office.interop.excel.xlsaveasaccessmode.xlnochange,missing.value,missing.value,missing.value,


Missing.value,missing.value);


Save mode Two: Save worksheet


Worksheet.saveas (@ "F:cdata2.xls",


Missing.Value, Missing.Value, Missing.Value, Missing.Value,


Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);


Save Way Three


Workbook.saved = true;


Workbook.savecopyas (Sfd.filename);





System.runtime.interops Tutorial ervices.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 ("Export Excel complete!");


}


}





}


private void Button4_Click (object sender, EventArgs e)


{


String strexcelfilename = @ "f:2007-07-16-student information. xls";


String strsheetname = "Sheet1";


#region The ASPNET operation Excel is correct


The definition of the source


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 data tables that are stored


DataSet ds = new DataSet ();


Connecting to a data source


OleDbConnection conn = new OleDbConnection (strconn);


Conn.Open ();


Fit to a data source


OleDbDataAdapter adapter = new OleDbDataAdapter (STREXCEL, conn);


Adapter.fill (ds, "res");


Conn.close ();


General circumstances. The first row of the Excel table is the column name


Datagridview2.datasource = ds.tables["res"];


#endregion


Reading complex Excel #region COM component


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 defaults to 256 columns ...


MessageBox.Show (Ws.cells.columns.count.tostring ());


Excelapp.quit ();


}


catch (Exception ex)


{


Throw ex;


}


#endregion


}


}


}

Some Excel operation detailed

i) using dynamically created methods

First create an Excel object, using Comobj:
var excelapp:variant;
Excelapp: = Createoleobject (' Excel.Application ');

1 Displays the current window:
Excelapp.visible: = true;

2) Change the Excel title bar:
Excelapp.caption: = ' application calls Microsoft Excel ';

3 Add a new workbook:
Excelapp.workbooks.add;

4 Open a workbook that already exists:
Excelapp.workbooks.open (' C:exceldemo.xls ');

5) Set the 2nd worksheet as the active worksheet:
Excelapp.worksheets[2].activate;
Or
excelapp.workssheets[' Sheet2 '].activate;

6) Assigning values to cells:
Excelapp.cells[1,4].value: = ' first row, fourth column ';

7 Sets the width of the specified column in: Number of characters, in the first column example:
Excelapp.activesheet.columns[1].columnswidth: = 5;

8 Sets the height of the specified row (in points) (1 lb = 0.035 cm), with the second behavior:
Excelapp.activesheet.rows[2].rowheight: = 1/0.035; 1 centimeters

9 Insert a page break before line 8th:
Excelapp.worksheets[1].rows[8].pagebreak: = 1;

10 Delete the page break before column 8th:
Excelapp.activesheet.columns[4].pagebreak: = 0;

11 Specify the width of the border line:
excelapp.activesheet.range[' b3:d4 '].borders[2].weight: = 3;
1-Left 2-right 3-top 4-bottom 5-oblique () 6-oblique (/)

12 clears the first row of column fourth cell formulas:
excelapp.activesheet.cells[1,4].clearcontents;

13 set the first line of font properties:
Excelapp.activesheet.rows[1].font.name: = ' official script ';
Excelapp.activesheet.rows[1].font.color: = Clblue;
Excelapp.activesheet.rows[1].font.bold: = true;
Excelapp.activesheet.rows[1].font.underline: = true;

14) for Page setup:

A. Header:


Excelapp.activesheet.pagesetup.centerheader: = ' report demo ';


B. Footer:


Excelapp.activesheet.pagesetup.centerfooter: = ' &p page ';


C. Header to top margin 2cm:


Excelapp.activesheet.pagesetup.headermargin: = 2/0.035;


D. Footer End margin 3cm:


Excelapp.activesheet.pagesetup.headermargin: = 3/0.035;


E. Top margin 2cm:


Excelapp.activesheet.pagesetup.topmargin: = 2/0.035;


F. Bottom margin 2cm:


Excelapp.activesheet.pagesetup.bottommargin: = 2/0.035;


G. Left margin 2cm:


Excelapp.activesheet.pagesetup.leftmargin: = 2/0.035;


H. Right margin 2cm:


Excelapp.activesheet.pagesetup.rightmargin: = 2/0.035;


I. Page Horizontal Center:


excelapp.activesheet.pagesetup.centerhorizontally: = 2/0.035;


J. Page Center vertically:


excelapp.activesheet.pagesetup.centervertically: = 2/0.035;


K. Print Cell network cable:


Excelapp.activesheet.pagesetup.printgridlines: = true;

15 Copy operation:

A. Copy the entire worksheet:
Excelapp.activesheet.used.range.copy;
B. Copy designated Area:
excelapp.activesheet.range[' A1:e2 '].copy;
C. Start pasting from A1 position:
Excelapp.activesheet.range. [' A1 '].pastespecial;
D. Start pasting from the end of the file:
excelapp.activesheet.range.pastespecial;

16) Insert a row or column:
A. Excelapp.activesheet.rows[2].insert;
B. Excelapp.activesheet.columns[1].insert;

17) Delete one row or column:
A. Excelapp.activesheet.rows[2].delete;
B. Excelapp.activesheet.columns[1].delete;

18 Print Preview Worksheet:
Excelapp.activesheet.printpreview;

19 Print output Worksheet:
Excelapp.activesheet.printout;

20) Worksheet Save:
If not excelapp.activeworkbook.saved then
Excelapp.activesheet.printpreview;

21) Save the worksheet as:
Excelapp.saveas (' C:exceldemo1.xls ');

22) Discard the disk:
Excelapp.activeworkbook.saved: = true;

23 Close the workbook:
Excelapp.workbooks.close;

24 Exit Excel:
Excelapp.quit;

(ii) using the Delphi control method
In the form, add ExcelApplication, ExcelWorkbook, and Excelworksheet respectively.

1) Open Excel
Excelapplication1.connect;

2 Displays the current window:
Excelapplication1.visible[0]:=true;

3) Change the Excel title bar:
Excelapplication1.caption: = ' application calls Microsoft Excel ';

4 Add a new workbook:
Excelworkbook1.connectto (Excelapplication1.workbooks.add (emptyparam,0));
5 Add a new worksheet:
var temp_worksheet: _worksheet;
Begin
Temp_worksheet:=excelworkbook1.
Worksheets.add (emptyparam,emptyparam,emptyparam,emptyparam,0) as _worksheet;
Excelworksheet1.connectto (Temp_worksheet);
End
6 open a workbook that already exists:
Excelapplication1.workbooks.open (C:a.xls
Emptyparam,emptyparam,emptyparam,emptyparam,
Emptyparam,emptyparam,emptyparam,emptyparam,
emptyparam,emptyparam,emptyparam,emptyparam,0)

7) Set the 2nd worksheet as the active worksheet:
Excelapplication1.worksheets[2].activate; Or
excelapplication1.workssheets[' Sheet2 '].activate;

8) assigning values to cells:
Excelapplication1.cells[1,4].value: = ' first row, fourth column ';

9 Sets the width of the specified column in: Number of characters, in the first column example:
Excelapplication1.activesheet.columns[1].columnswidth: = 5;

10 sets the height of the specified row (in points) (1 lb = 0.035 cm), with the second behavior:
Excelapplication1.activesheet.rows[2].rowheight: = 1/0.035; 1 centimeters

11 Insert a page break before line 8th:
Excelapplication1.worksheets[1].rows[8].pagebreak: = 1;

12 Delete the page break before column 8th:
Excelapplication1.activesheet.columns[4].pagebreak: = 0;

13 Specify the width of the border line:
excelapplication1.activesheet.range[' b3:d4 '].borders[2].weight: = 3;
1-Left 2-right 3-top 4-bottom 5-oblique () 6-oblique (/)

14 clears the first row of column fourth cell formulas:
excelapplication1.activesheet.cells[1,4].clearcontents;

15 set the first line of font properties:
Excelapplication1.activesheet.rows[1].font.name: = ' official script ';
Excelapplication1.activesheet.rows[1].font.color: = Clblue;
Excelapplication1.activesheet.rows[1].font.bold: = true;
Excelapplication1.activesheet.rows[1].font.underline: = true;

16) for Page setup:


A. Header:


Excelapplication1.activesheet.pagesetup.centerheader: = ' report demo ';


B. Footer:


Excelapplication1.activesheet.pagesetup.centerfooter: = ' &p page ';


C. Header to top margin 2cm:


Excelapplication1.activesheet.pagesetup.headermargin: = 2/0.035;


D. Footer End margin 3cm:


Excelapplication1.activesheet.pagesetup.headermargin: = 3/0.035;


E. Top margin 2cm:


Excelapplication1.activesheet.pagesetup.topmargin: = 2/0.035;


F. Bottom margin 2cm:


Excelapplication1.activesheet.pagesetup.bottommargin: = 2/0.035;


G. Left margin 2cm:


Excelapplication1.activesheet.pagesetup.leftmargin: = 2/0.035;


H. Right margin 2cm:


Excelapplication1.activesheet.pagesetup.rightmargin: = 2/0.035;


I. Page Horizontal Center:


excelapplication1.activesheet.pagesetup.centerhorizontally: = 2/0.035;


J. Page Center vertically:


excelapplication1.activesheet.pagesetup.centervertically: = 2/0.035;


K. Print Cell network cable:


Excelapplication1.activesheet.pagesetup.printgridlines: = true;

17 Copy operation:

A. Copy the entire worksheet:
Excelapplication1.activesheet.used.range.copy;

B. Copy designated Area:
excelapplication1.activesheet.range[' A1:e2 '].copy;

C. Start pasting from A1 position:
Excelapplication1.activesheet.range. [' A1 '].pastespecial;

D. Start pasting from the end of the file:
excelapplication1.activesheet.range.pastespecial;

18) Insert a row or column:
A. Excelapplication1.activesheet.rows[2].insert;
B. Excelapplication1.activesheet.columns[1].insert;

19) Delete one row or column:
A. Excelapplication1.activesheet.rows[2].delete;
B. Excelapplication1.activesheet.columns[1].delete;

20 Print Preview Worksheet:
Excelapplication1.activesheet.printpreview;

21 Print output Worksheet:
Excelapplication1.activesheet.printout;

22) Worksheet Save:
If not excelapplication1.activeworkbook.saved then
Excelapplication1.activesheet.printpreview;

23) Save the worksheet as:
Excelapplication1.saveas (' C:exceldemo1.xls ');

24) Discard the disk:
Excelapplication1.activeworkbook.saved: = true;

25 Close the workbook:
Excelapplication1.workbooks.close;

26 Exit Excel:
Excelapplication1.quit;
Excelapplication1.disconnect;

(iii) Use Delphi control excle two-dimensional diagram
In the form, add ExcelApplication, ExcelWorkbook and Excelworksheet respectively.
var asheet1,achart, Range:variant;

1 Select the first worksheet when the first work sheet
ASHEET1:=EXCELAPPLICATION1.WORKBOOKS[1].WORKSHEETS[1];

2) Add a two-dimensional graph
Achart:=asheet1.chartobjects.add (100,100,200,200);

3 Choose the form of two-dimensional graphs
achart.chart.charttype:=4;

4 assigning values to two-dimensional graphs
Series:=achart.chart.seriescollection;
RANGE:=SHEET1!R2C3:R3C9;
Series.add (range,true);
5) plus the title of the two-dimensional graph
Achart.chart.hastitle:=true;
achart.chart.charttitle.characters.text:= ' excle two-dimensional map '

6) Change the caption font size of the two-dimensional graph
achart.chart.charttitle.font.size:=6