Read and Write Excel files in ASP. NET

ASP. NET applications often encounter the need to read data from an Excel file or write data into an Excel file. In general, there are four solutions to read and write Excel files in ASP. NET.

1.1.1 use OLE DB

Use Ole
DB can read Excel files by querying the database, because an Excel table can be regarded as a data table to some extent. The main difference between the two is that the data engine used is not
Same. To access Excel using ole db, you must have Microsoft Access Data component on your computer.
2.6 (madc2.6) or later, and the "extended properties = Excel 8.0" must be declared on the connection string. Here, the specified Excel
If the version number is higher than 8.0, an error may occur. Therefore, Excel 8.0 must be used.

The other statements are the same as accessing the database. Open the connection, fill in the dataset, and then close the connection. For example, the following implementation code:

// Create a data link

String strcon = "provider = Microsoft. Jet. oledb.4.0; Data Source = c: \ sample.xls; extended properties = Excel 8.0 ";

Oledbconnection myconn = new oledbconnection (strcon );

String strcom = "select * from [sheet1 $]";

Myconn. open ();

// Open the data link to obtain a dataset

Oledbdataadapter mycommand = new oledbdataadapter (strcom, myconn );

// Create a DataSet object

Mydataset = new dataset ();

// Get your own DataSet object

Mycommand. Fill (mydataset, "[sheet1 $]");

// Close the data link

Myconn. Close ();

Code 7-1

 

The advantage of using this solution is that it is easy to deploy without other server components, but its disadvantages are also obvious, when you use it to read files in Excel 2003 or later formats, data may be lost and an Excel file cannot be generated.

1.1.2 use office main interoperability assembly

Another solution is to use the main office interoperability Assembly. In this way, you need to install Office 2003 on the server. However, it can precisely control all aspects of Excel files, including formats, fonts, and colors.

The following code demonstrates how to read the value of a cell in an Excel file:

String excelfilepath = @ "D: \ book1.xls ";

Excel. Application myexcel = new excel. applicationclass ();

Object omissing = system. reflection. Missing. value;

Myexcel. application. workbooks. Open (excelfilepath, omissing,

Omissing, omissing)
;

Excel. Workbook mybook = myexcel. workbooks [1];

Excel. worksheet mysheet = (Excel. worksheet) mybook. worksheets [1];

System. Data. datatable dt = new system. Data. datatable ("mytable ");

DT. Columns. Add ("f1", system. type. GetType ("system. String "));

DT. Columns. Add ("F2", system. type. GetType ("system. String "));

DT. Columns. Add ("F3", system. type. GetType ("system. String "));

DT. Columns. Add ("F4", system. type. GetType ("system. String "));

DT. Columns. Add ("F5", system. type. GetType ("system. String "));

Dataset myds = new dataset ();

Myds. Tables. Add (DT );

Datarow myrow;

Myds. Clear ();

For (INT I = 2; I <= 4; I ++) // The first behavior title, not read

{

Myrow = myds. Tables ["mytable"]. newrow ();

For (Int J = 1; j <= 5; j ++)

{

Excel. Range r = (Excel. Range) mysheet. cells [I, j];

String strvalue = R. Text. tostring ();

String AA = strvalue;

String columnname = "F" + J. tostring ();

Myrow [columnname] = strvalue;

}

Myds. Tables ["mytable"]. Rows. Add (myrow );

}

Datagrid1.datasource = myds. Tables ["mytable"]. defaultview;

Datagrid1.databind ();

Code 7-2

 

The following code demonstrates how to generate an Excel file and write values to it:

String filename = "";

Excel. applicationclass oexcel;

Oexcel = new excel. applicationclass ();

Oexcel. usercontrol = false;

Excel. workbookclass WB = (Excel. workbookclass) oexcel. workbooks. Add (system. reflection. Missing. value );

For (INT I = 1; I <= 5; I ++)

{

Oexcel. cells [I, 1] = I. tostring ();

Oexcel. cells [I, 2] = "'100 columns ";

Oexcel. cells [I, 3] = "3rd columns ";

Oexcel. cells [I, 4] = "'100 columns ";

}

WB. Saved = true;

Filename = request. physicalapplicationpath + "test.xls ";

Oexcel. activeworkbook. savecopyas (filename );

Oexcel. Quit ();

System. gc. Collect ();

Response. Redirect (request. applicationpath + "/test.xls ");

Code 7-3

 

In fact, for ASP. NET, this is not a good solution, the reason is that this solution
Components are used on the server, which often leads to some problems that have never been encountered. If an error occurs when processing an Excel file, the whole thread will die there, no Excel process on the server
The Excel file cannot be deleted, and the subsequent Excel file cannot be processed. Therefore, it is a big problem to restart the server.

1.1.3 use serverdocument

In the previous solution, the main office interoperability assembly has a big problem because
The Office components used by the client are used on the server, because these office components are not considered to be called in the service at the beginning of the design. Use Excel files on the server
The solution recommended by Microsoft is to use serverdocument.

When you have Microsoft Visual Studio 2005 tools
The Microsoft Office System Custom Microsoft Office Word 2003 document or
Microsoft Office Excel 2003 workbooks can store data in embedded data islands without starting Excel or word
You can access data island.

Data island is an XML document that contains data embedded in office documents.
You can access the XML file from the office documentation.
Documentation. The data actually exists in two places, that is, the document and the separately embedded data island. Data Binding is used between data islands and documents to keep them synchronized. If the code running on the server modifies data island,
When the document is opened and the code in the document is run, the Office document is synchronized with data island.

This model has the following advantages:

L you can add the verification code to the data independent from the document. By separating verification from documents, you can port the data verification code to other documents.

L data island uses offline data filling. When the cached data item in the document contains data, the document interacts with the data island.

L because data islands can be accessed from outside, you can modify the data embedded in the document without instantiating the office, so as to support quick batch processing of documents on the server. However, you can only access the cached data, not all the data in the document.

The following code demonstrates how to use serverdocument to access data and generate an Excel document from data:

String expensedoc = @ "C: \ expensedocuments \ expenses0105.xls ";

Serverdocument sd1 = NULL;

 

Try

{

Sd1 = new serverdocument (expensedoc );

Cacheddatahostitem datahostitem1 =

Sd1.cacheddata. hostitems ["datanamespace. dataworksheet"];

 

Cacheddataitem dataitem1 = datahostitem1.cacheddata ["datacache"];

 

System. Io. stringreader schemareader = new system. Io. stringreader (dataitem1.schema );

System. Io. stringreader xmlreader = new system. Io. stringreader (dataitem1.xml );

 

Expensedata. readxmlschema (schemareader );

Expensedata. readxml (xmlreader );

}

Finally

{

If (sd1! = NULL)

{

Sd1.close ();

}

}

Code 7-4

 

String name = @ "C: \ Documents \ wordapplication3.doc ";

System. Io. filestream = NULL;

Byte [] bytes = NULL;

 

Try

{

Filestream = new system. Io. filestream (

Name, system. Io. filemode. Open, system. Io. fileaccess. Read );

 

Bytes = new byte [(INT) filestream. Length];

 

Filestream. Read (bytes, 0, (INT) filestream. Length );

}

Finally

{

If (filestream! = NULL)

{

Filestream. Close ();

}

}

Serverdocument sd1 = NULL;

Try

{

Sd1 = new serverdocument (bytes, name );

 

// Your data manipulation code goes here.

 

Sd1.save ();

Bytes = sd1.document;

// If you have a Word document, use the mime string:

Response. contenttype = "application/MSWord ";

 

// If you have an Excel Workbook, use the mime string:

// Response. contenttype = "application/vnd. MS-excel ";

 

Response. addheader ("content-disposition", "filename =" + name );

Response. Write (sd1 );

}

Finally

{

If (sd1! = NULL)

{

Sd1.close ();

}

}

Code 7-5

 

This is an ideal solution, but it has the following restrictions:

L The client must install. NET Framework 2.0, office tools for Visual Studio runtime, and Office 2003.

L an assembly embedded in the document is developed to synchronize data in views and data islands. For example, you can use a value in data islands to update the value in a cell or use the value in a cell to update the value in data islands.

L use a dedicated excel template.

 

1.1.4 use openxml sdk for the openxml format of Office 2007

If you decide that ASP. NET applications only Process office
2007 for the openxml Format documents generated, use openxml
The SDK is a better idea, because it does not need to install office on the server and has no requirements on the client. You only need to use the openxml format document, because
The openxml format has been submitted to the International Standardization Organization. Therefore, you can use any application that supports openxml to read and edit this document.

To use this scheme, you need to download the openxml SDK and use it for development. Currently, this SDK is only a CTP version. The following code demonstrates how to use the openxml SDK to read and write Excel files in openxml format.

// How to: Get worksheet information.

Public list <string> xlgetsheetinfo (string filename)

{

// Fill this collection with a list of all the sheets

List <string> sheets = new list <string> ();

 

Using (spreadsheetdocument xlpackage = spreadsheetdocument. Open (filename, false ))

{

Workbookpart workbook = xlpackage. workbookpart;

Stream workbookstr = Workbook. getstream ();

Xmldocument Doc = new xmldocument ();

Doc. Load (workbookstr );

 

Xmlnamespacemanager nsmanager = new xmlnamespacemanager (Doc. nametable );

Nsmanager. addnamespace ("default", Doc. documentelement. namespaceuri );

Xmlnodelist nodelist = Doc. selectnodes ("// default: sheets/Default: sheet", nsmanager );

 

Foreach (xmlnode node in nodelist)

{

String sheetname = string. empty;

Sheetname = node. attributes ["name"]. value;

Sheets. Add (sheetname );

}

}

Return sheets;

}

Code 7-6

 

// How to: create a new package as a Excel document.

Public static void createnewexceldocument (string document)

{

Using (spreadsheetdocument exceldoc = spreadsheetdocument. Create (document, preadsheetdocumenttype. workbook ))

{

// Set the content of the document so that excel can open it.

Workbookpart mainpart = exceldoc. addworkbookpart ();

 

Setmaindocumentcontent (mainpart );

}

}

 

// Set content of maindocumentpart.

Public static void setmaindocumentcontent (workbookpart part, string excelxml)

{

Using (Stream stream = part. getstream ())

{

Byte [] Buf = (New utf8encoding (). getbytes (docxml );

Stream. Write (BUF, 0, Buf. Length );

}

}