C # Read and write Excel (ii)

C # Read and write Excel (ii) .- ,- -  -: -: the|  Category: Default Categories | Tags: | report |font size large and small subscription with "sweep" to share the article to the Circle of friends.  Use easy letter "sweep" to share the article to the Circle of friends. Download Lofter ClientPrivate voidButton1_Click (Objectsender, System.EventArgs e) { if(Openfiledialog1.showdialog () = =DialogResult.OK) {OleDbDataAdapter ada=NewOleDbDataAdapter ("select * FROM [sheet1$]","Provider=Microsoft.Jet.OLEDB.4.0;Data source="+ Openfiledialog1.filename +"; extended Properties=excel 8.0;"); DataTable DT=NewDataTable ();Try{ada.fill (dt);}Catch(Exception ex) {MessageBox.Show (ex.tostring ());} DataGrid1.DataSource=DT;} } SqlConnection Conn=NewSqlConnection"data source= (local);d atabase=northwind;user ID =sa;pwd=york"); stringstrSQL =@"SELECT * FROM OPENROWSET (MICROSOFT.JET.OLEDB.4.0, Excel 5.0;hdr=yes;database=e:\book1.xls,a$)"; SqlDataAdapter da=NewSqlDataAdapter (strSQL, conn); DataSet DS=Newdataset (); Da.fill (DS); Customergrid.datasource= ds.tables[0];usingSystem;usingSystem.Reflection;//reference this to use the missing fieldusingExcel;namespaceCExcel1 {classClass1 {[STAThread]Static voidMain (string[] args) { //Create a Application objectExcel.Application xapp=NewExcel.applicationclass (); Xapp.visible=true; //The Workbook object can be obtained in one of two ways: The following is the opening of an existing fileExcel.Workbook Xbook=xapp.workbooks._open (@"D:\Sample.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); //Xbook=xapp.workbooks.add (Missing.Value);//code for new file//Specify the sheet to be manipulated in two ways:Excel.Worksheet xsheet= (Excel.Worksheet) xbook.sheets[1]; //excel.worksheet xsheet= (excel.worksheet) xapp.activesheet;//reading data through a Range objectExcel.Range Rng1=xsheet.get_range ("A1", Type.Missing); Console.WriteLine (rng1. Value2); //read, through a Range object, but using a different interface to get rangeExcel.Range rng2= (Excel.Range) xsheet.cells[3,1]; Console.WriteLine (rng2. Value2); //Write DataExcel.Range Rng3=xsheet.get_range ("C6", Missing.Value); Rng3. Value2="Hello"; rng3. Interior.ColorIndex=6;//set the background color of range//Save method One: Save WorkbookXbook.saveas (@"D:\CData.xls", Missing.value,missing.value,missing.value,missing.value,missing.value, Excel.XlSaveAsAccessMode.xlNoChange , Missing.value,missing.value,missing.value, Missing.value,missing.value); //Save method Two: Save worksheetXsheet.saveas (@"D:\CData2.xls", Missing.value,missing.value,missing.value,missing.value, Missing.value,missing.value,missing.value, Missing.value,missing.value); //How to save threeXbook.save (); Xsheet=NULL; Xbook=NULL; Xapp.quit ();//This sentence is very important, otherwise the Excel object cannot exit from memoryxapp=NULL; } } }//ways to export Excel   Private voidExportexcel () {DataSet DS=dtsselect;//Data Source    if(ds==NULL)return; stringSavefilename=""; BOOLFilesaved=false; SaveFileDialog Savedialog=NewSaveFileDialog (); Savedialog.defaultext="xls"; Savedialog.filter="Excel file |*.xls"; Savedialog.filename="Sheet1";     Savedialog.showdialog (); Savefilename=Savedialog.filename; if(Savefilename.indexof (":") <0)return;//it was canceled.Excel.Application xlapp=NewExcel.Application (); if(xlapp==NULL) {MessageBox.Show ("Unable to create Excel object, may not have Excel installed on your machine"); return; } excel.workbooks Workbooks=Xlapp.workbooks; Excel.Workbook Workbook=workbooks.     ADD (Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet Worksheet= (excel.worksheet) workbook. worksheets[1];//get Sheet1//Write Fields     for(intI=0; I<ds. tables[0]. columns.count;i++) {worksheet. cells[1, i+1]=ds. tables[0]. Columns[i].     ColumnName; }     //Write Value        for(intR=0; R<ds. tables[0]. rows.count;r++)    {       for(intI=0; I<ds. tables[0]. columns.count;i++) {worksheet. Cells[r+2, i+1]=ds. tables[0].      Rows[r][i];     } System.Windows.Forms.Application.DoEvents (); } worksheet. Columns.EntireColumn.AutoFit ();//column widths are self-adapting.     if(cmbxtype.text!="Notification") {Excel.Range RG=worksheet.get_range (worksheet. cells[2,2],worksheet. Cells[ds. tables[0]. rows.count+1,2]); Rg. NumberFormat="00000000"; }    if(savefilename!="")    {      Try{workbook. Saved=true; Workbook.       SaveCopyAs (Savefilename); Filesaved=true; }     Catch(Exception ex) {filesaved=false; MessageBox.Show ("An error occurred while exporting the file, the file may be being opened! \ n"+Ex.      Message); }     }    Else{filesaved=false;     } xlapp.quit (); Gc. Collect ();//forcibly destroyed    if(filesaved && System.IO.File.Exists (savefilename)) System.Diagnostics.Process.Start (Savefilename);//Open Excelyou often encounter the need to read and write Excel files in your project. Actually reading Excel is very simple, first use the namespace Nexcel, and then load the file to workbook, loop read on the line. usingNexcel; Workbook WB=Workbook.getworkbook (FileStream); Sheet Sheet= WB. sheets[0]; intRowscount =sheet. Rows; for(introw =2; Row < Rowscount; row++){        stringSheet.getcell (0, Row). Contents;decimalTotalprice = Convert.todecimal (Sheet.getcell (1, Row).    Value); } Comment This forwarded to Weibo to be forwarded to Weibo read (954)| Reviews0) |use "sweep" to share the article in a circle of friends.         Use easy letter "sweep" to share the article to the Circle of friends. Like to recommend 0 people|

C # Read and write Excel (ii)