Excel in ASP. NET: (3) accessing worksheet data

You have created an Excel. Application Object and opened the workbook. Now you can traverse the worksheet and cells to obtain data. Not all cells have data, but most of them are empty. Therefore, xmldocument is a good idea to save the obtained data. xmldocuemnt can also be returned to the client browser as the result of scriptmethod, use JavaScript to generate tables and present results. In my experience, creating a table on the server only consumes 1% of the Excel operation time (actual data: the Excel operation takes 22.3 seconds, and the table is generated 0.06 seconds. The gap is huge ).

You can traverse a worksheet in the following ways:

Sheets = Workbook. sheets; For (INT I = 1; I <= sheets. Count; I ++) // note that the object array access in Excel starts with 1! {Excel. worksheet sheet = (Excel. worksheet) sheets. get_item (I); // get the worksheet object ...... releasecomobject (sheet); // release the COM Object sheet = NULL ;}

The Excel table ranges from a A-IV column to a total of 256 columns, 65536 rows, and many cells are empty, full traversal consumes a lot of time, so we should limit a range:

Excel. range range = sheet. get_Range ("A1", "Z128"); // ranges from A1 to Z128. Pay attention to Excel. the Range object will generate reference count Excel. range rowRange = range. rows; // row object, with "s", will generate reference count Excel. range colRange = range. columns; // column object, also generates reference count object [,] data = (object [,]) range. get_Value (Type. missing); // obtain all data in the range at a time. Note that the value is obtained! For (int r = rowRange. count; r> = 1; r --) // traverse rows in descending order {for (int c = 1; c <= colRange. count; c ++) // sequential access column {if (data [r, c]! = Null) // if the cell at the [r, c] position has no data, the value is null, which can detect empty cells {Excel. range cell = (Excel. range) range. get_Item (r, c );...... releaseComObject (cell); // release reference cell = null ;}}}

In Excel 2003, the Value field is accessed through the cell. Value Attribute. In Excel 2007, the Value is cell. Value2.

Explanations for Traversing rows in inverted order:
Assume that the XML format is as follows:
<Worksheet name = "SheetXXX">
<ROW>
<CELL> XXXXXXX </CELL>
</ROW>
</WORKSHEET>
Because the Excel WORKSHEET is in the header, the back is basically empty. When filling XML, the CELL element is generated first, inserted into the ROW element, and then the ROW is inserted into the WORKSHEET element. When traversing rows in inverted order, if the behavior is empty, excluding the cell data, the ROW's ChildNodes. the value of Count is 0. If the ChildNodes of WORKSHEET is empty, it indicates that the ROW data is not included, and the ROW is not inserted into the WORKSHEET. This situation changes until the first row containing the data cell is encountered. At this time, the row will be added to the ChildNodes of WORKSHEET. In this way, you can skip rows without data at the end and skip empty rows in the middle of the rows with data.

The sample code for inserting row elements is as follows:

If (row_elem.ChildNodes.Count> 0 | sheet_elem.ChildNodes.Count> 0) // insert {sheet_elem.InsertBefore (row_elem, sheet_elem.FirstChild) if there are rows or empty rows );}