Code parsing for working with Excel in ASP.

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.