Code parsing for working with Excel in ASP.

One. To create an Excel document using the Excel object model:
1. Create a simple document

1 Try2 {3//Create an Excel program object4Microsoft.Office.Interop.Excel.Application Excel =NewMicrosoft.Office.Interop.Excel.Application ();5      //When you create workbook, workbook contains a single number of tables6Excel. SheetsInNewWorkbook =1;7      //Create Workbook8 Excel. Workbooks.Add ();9 Ten      //Create first worksheet OneWorksheet sheet = Excel. activeworkbook.sheets[1]; A  　　   //Worksheet sheet = (Worksheet) Excel. ACTIVEWORKBOOK.WORKSHEETS[1]; -                  -  　　   //set a name for a worksheet theSheet. Name ="Student"; -Sheet. cells[1,1] =txtName.Text; -Sheet. cells[1,2] =Txtage.text; -Sheet. cells[1,3] =Txthabit.text; +  -      //Show Excel +Excel. Visible =true; A      //Live Hibernation 2000 Ms atSystem.Threading.Thread.Sleep ( -); -  -      //Save the current active workbook - Excel. ActiveWorkbook.SaveAs ( -       　　　　 //environment.currentdirectory + "/test.xls",//file name -       　　　　 "F:/test.xls", inXlfileformat.xlworkbooknormal//format of saved files - ); to  +      //close the currently active workbook - Excel. Activeworkbook.close (); the      //exit the Excel application * Excel. Quit (); $ }Panax Notoginseng Catch(Exception ex) - { the                  +      Throw NewException (ex. Message); A}

2. Formatting the document

1 //Set Font style2Range Ranrow = sheet. Range[sheet. cells[2,2], sheet. cells[2,Ten]];3RanRow.Font.Bold =true;//Font Bold4RanRow.Font.Color = Color.seagreen;//Font Color5 //Cell Manipulation6Ranrow.horizontalalignment=xlhalign.xlhaligncenter;//Alignment7Ranrow. Interior.Color = Color.seagreen;//Inner Border Style8Range. MergeCells =true;//Merge Cells9 //Border Style:TenRanborder.borderaround (xllinestyle.xlcontinuous, Xlborderweight.xlthick);//area outer border style OneRange. Borders.LineStyle = xllinestyle.xldouble;//Inner Border Style A //Set the ID number, date display format -Range. NumberFormat ="0"; -Range. NumberFormatLocal ="yyyy-m-d";

Two. Export DataGridView data to an Excel document using the object model
1. Use DataGridView as the data source directly in the form design interface code to populate the Excel table directly with the displayed values.
2. Passing DataGridView as an argument to a public class requires adding and importing using System.Windows.Forms in the public class;
Three. Import the Excel document into the DataTable

1         //Create a DataTable object and add columns2System.Data.DataTable dt =NewSystem.Data.DataTable ();3          for(inti =0; I <Ten; i++)4         {5Dt. Columns.Add (NewDataColumn ());6         }7 8Worksheet sheet =NULL;9         foreach(Worksheet shinchExcel. Activeworkbook.worksheets)Ten         { One             if(Sh. Name = ="Sheet1") A             { -Sheet =sh; -             } the             if(Sheet! =NULL) -             { -                 intR =2; -                  while(true) +                 { -Range rname = sheet. Cells[r,3] asRange; +                     if(RName.Text.ToString (). Trim (). Length = =0) A                     { at                          Break; -                     } -DataRow row =dt. NewRow (); -  -  -                      for(inti =0; I <Ten; i++) in                     { -Row[i] = sheet. Cells[r, i +1]. Text; to  +                     } - dt. Rows.Add (row); ther++; *                 } $             }Panax Notoginseng}

Four. Import Excel content using ADO

1         //Connection String2         stringConstr ="provider=microsoft.jet.oledb.4.0;data source= ' e:/n-tier/lesson8/basic information for students. xls '; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 '";3         stringsql =string. Format ("SELECT * FROM [{0}$]","Sheet1");4DataTable dt =NewDataTable ();5         using(OleDbConnection con =NewOleDbConnection (constr))6         {7 con. Open ();8OleDbDataAdapter Odda =Newoledbdataadapter (sql, con);9 Odda. Fill (DT);Ten         } One         returnDt

Code parsing for working with Excel in ASP.