C # Use the myxls component to generate an Excel report.

Calling the Excel component in the background to generate an Excel file. Although the Excel file can be fully controlled and any complicated format can be generated, it has a major disadvantage, this method will generate many Excel processes and it is difficult to completely clear them. Especially when an error occurs, the entire server may crash. This article introduces an open-source component written in C # and briefly introduces the XML format supported by office2003 and later versions.

 1. Excel binary format

The Excel File Format (biff8) specification and Microsoft compounddocument (ole2) format specification documents released by OpenOffice.org provide a detailed description of the Excel binary format, we can directly operate on the office binary format document.

Myxls is an open-source component written in C #. It can be used to generate an Excel file with many tables in the format. It provides an object-based API that is very easy to use.

The DLL and code of the myxls component can be downloaded here:
Http://sourceforge.net/project/showfiles.php? Group_id = 205384 & package_id = 245371
The following is an example ..

Protected void page_load (Object sender, eventargs E)
{
String strsql = "select trade_date [date], topen [open], thigh high, tlow [low] From day_quotation where sec_code = '1' and trade_date> = '000000' order by trade_date DESC "; // SQL statement
Dataset DS = sqlhandle. executereturndataset (commandtype. Text, strsql, null );
Xlsgridview (DS, "data"); // call the xlsgridview method to generate an Excel report
}


/** // <Summary>
/// Bind a database to generate an xls report
/// </Summary>
/// <Param name = "ds"> obtain a dataset </param>
/// <Param name = "xlsname"> report table name </param>
Private void xlsgridview (Dataset ds, string xlsname)
{
Xlsdocument XLS = new xlsdocument ();

Int rowindex = 1;
Int colindex = 0;


System. Data. datatable table = Ds. Tables [0];
Worksheet sheet = XLS. Workbook. worksheets. addnamed ("sheet"); // name of the title of the status bar
Cells = sheet. cells;
Foreach (datacolumn Col in table. columns)
{
Colindex ++;
// Sheet. cells. addvaluecell (1, colindex, col. columnname); // Add the XLS header line
Cells. addvaluecell (1, colindex, col. columnname );
}

Foreach (datarow row in table. Rows)
{
Rowindex ++;
Colindex = 0;
Foreach (datacolumn Col in table. columns)
{
Colindex ++;
// Sheet. cells. addvaluecell (rowindex, colindex, row [col. columnname]. tostring (); // Add data to the XLS table
Cell cell = cells. addvaluecell (rowindex, colindex, convert. todouble (row [col. columnname]. tostring (); // convert to numeric
// If the data in your database is numeric, it is best to convert it. Otherwise, the data imported to excel is displayed as a string.
Cell. Font. fontfamily = fontfamilies. Roman; // font
Cell. Font. Bold = true; // The font is bold.
}
}
XLS. Send ();
}