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

Source: Internet
Author: User

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 ();
}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.