Excel in ASP. NET: (5) Save

After reading the data, you can generate an HTML page for online editing in the client browser (generate a table and add the online editing function will be described in subsequent sections, this topic is similar to Google's online spreadsheet feature. Of course, we only need the feature of Yangchun. The editing result can be submitted to the server for processing in XML format. This processing is mainly saved as an XLSX spreadsheet. Because the ASP. NET environment is used, it is more convenient to use Atlas, and ScriptMethod can easily submit client data asynchronously.

First, define the data format submitted by the client:

<?xml version="1.0" encoding="utf-8" ? ><DATA>    <WORKSHEET NAME="XXXX">        <CELL row="1" col="2" hasFormula="false" formula="" value="Test Test" />        ....            </WORKSHEET>    <WORKSHEET NAME="YYYY">        <CELL row="3" col="3" hasFormula="true" formula="=SUM(A1:A2)" value="0" />        ....            </WORKSHEET>     ....</DATA>

There will be N <WORKSHEET>, which are distinguished by the NAME attribute, which is also the worksheet name. Its subnodes have M <CELL>. Note that for <WORKSHEET> whose NAME is YYYY, the formula and value of the first <CELL> are not equal. In practice. range. format setting may result in inconsistent values with the actual display results. To handle the problem perfectly, take a closer look at Excel. range. format, it is impossible to parse the Format definition by yourself.

Now let's take a look at how to save the data submitted by the user:

For (int n = sheet_node_list.Count-1; n> = 0; n --) // traverse all <WORKSHEET> elements {if (sheet_node_list [n]. attributes ["NAME"]. value = sheet. name) // find the corresponding Excel file. worksheet: Compare {XmlNode sheet_node = sheet_node_list [n] by name; // obtain <WORKSHEET> int row = 0, col = 0; // row, column Excel. range cells = (Excel. range) sheet. cells; // obtain Excel. worksheet. the Range object references foreach (XmlNode cell_node in sheet_node.ChildNodes) // traverses all <Cells> {Row = int. parse (cell_node.Attributes ["row"]. value); // obtain the column coordinate col = int. parse (cell_node.Attributes ["col"]. value); Excel. range cell = (Excel. range) cells. get_Item (row, col); // obtain Excel. A Range-type cell that references if (bool. parse (cell_node.Attributes ["hasFormula"]. value) = true) // user data inclusion formula? {Cell. formula = cell_node.Attributes ["formula"]. value; // write formula} else cell. value2 = cell_node.Attributes ["value"]. value; // otherwise, write the text releaseComObject (cell); // release the cell = null;} // foreach cell node releaseComObject (cells); // release the Cells = null ;} // if sheet name matched} // for sheet node list

Now you can save the file:

Workbook. save (); // Save the workbook directly. saveAs (filename, // save as Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Excel. xlSaveAsAccessMode. xlNoChange, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing );

To avoid prompts or other things you don't want to see, you can add the following statement before opening the workbook:

App. Visible = false; // The Excel application is invisible to app. DisplayAlerts = false; // no warning is displayed.

However, in our case, it seems useless to say that nothing can be seen.