C # Excel Operations

Excel apps are very common, and C # is handy for working with Excel. This article describes two ways to manipulate Excel. The first is the system's own library System.Data.OleDb; With this library operation Exce requires a distinction between two formats (. xls and. xlsx).

1. Define the connection string

            stringFileName =@".. \.. \data.xlsx"; stringex =path.getextension (fileName); stringconnstring=NULL; if(ex==". xlsx") {connstring="provider=microsoft.ace.oledb.12.0;"+"Data source="+ FileName +";"+"; Extended properties=\ "Excel 12.0; Hdr=yes;imex=1\ ""; }            Else if(ex = =". xls") {connstring="provider=microsoft.jet.oledb.4.0;"+"Data source="+ FileName +";"+"; Extended properties=\ "Excel 8.0; Hdr=yes;imex=1\ ""; }            Else            {                return; }

2. Connect Excel and read data (same as other database reads)

           OleDbConnection conn = new OleDbConnection (connstring);
Conn. Open ();

OleDbCommand oledbcmd = conn. CreateCommand ();
Oledbcmd.commandtext = "Select *from[sheet1$]";
OleDbDataAdapter apt =new OleDbDataAdapter (oledbcmd);
DataSet ds = new DataSet ();
Apt. Fill (ds, "T1");
DataTable dt = ds. tables["T1"];
for (int i=0;i<dt. rows.count;i++)
{
Console.Write (dt. Rows[i][0] + "");
Console.Write (dt. rows[i][1]+ "");
Console.Write (dt. ROWS[I][2] + "");
Console.WriteLine (dt. ROWS[I][3]);
}
Console.readkey ();

Oledbcmd.dispose ();
Conn. Close ();

Because of the use of the database language, this method can only read one table at a time (sheet), and to read multiple tables, you need to read the Excel file multiple times with String.Format.

Here is a more convenient method, reference library Aspose.Cells.dll can be easily read. It first defines the workbook (that is, the Excel file) and connects to the relevant file, then obtains the form in the Excel file through the worksheet inside the workbook, and obtains the corresponding cell data through cells. Here are the specific steps:

1. Quoting Aspose.cells

using Aspose.cells;

2, establish workbook, and connect Excel

            string @" .. \.. \new.xlsx";             string @" .. \.. \data.xlsx";             New Workbook (FileName);

3. Access to Data

Console.WriteLine (ExcelWorkBook.Worksheets.Count);//table single number in Excel            foreach(Worksheet sheetinchexcelworkbook.worksheets) {Console.WriteLine (sheet. Name+"  ");//form name in Excel            }            //get the second column of data in a formWorksheet Sheet1 = excelworkbook.worksheets[4];  for(intI=0; I<sheet1. cells.rows.count;i++) {Console.WriteLine (Sheet1. Cells[i,1].            StringValue); }

4, new Excel, you can use this library to easily build Excel files

            //New FileWorkbook newcreated =NewWorkbook ();            NewCreated.Worksheets.Clear (); //Add a formNEWCREATED.WORKSHEETS.ADD (" on"); Worksheet ws= newcreated.worksheets[" on"]; //Add DataWs. cells[0,0]. Value ="AFDSFD"; //Save FileNewcreated.save (Newcreateexcelfile);

C # Excel Operations