C #. Net reading and writing Excel

C # simple web development framework developed to save development information management system time and improve development efficiency
Build a basic web development framework (mainly including database processing, page framework and tools) to form a rapid development architecture of ASP. NET information management system.
I. framework implements list query and Chart Display Based on XML Customization
2. The list provides complex query conditions, merged columns (grouping and summation processing), summarized rows, and drill-through data and charts.
3. Editing and configuration supports adding and modifying data in a single table. You can easily expand the new fields in the master table on the editing page implemented by developers.
Enables developers to focus on system business implementation, saving development time and improving development efficiency

Log on to http: // 121.18.78.216/

 

Ace. oledb reads Excel Data

String strconn = "provider = Microsoft. Ace. oledb.12.0; Data Source =" + filename + "; extended properties = 'excel"
+ (Filename. endswith ("xls", stringcomparison. currentcultureignorecase )? "8": "12") + ". 0; HDR = yes '";
Dataset DS = new dataset ();
Using (oledbconnection conn = new oledbconnection (strconn ))
{
Conn. open ();
// Return the Excel schema, including the name, type, Creation Time, and modification time of each sheet.
Datatable dtsheetname = conn. getoledbschematable (oledbschemaguid. Tables, new object [] {null, "table "});

// String array containing the table name in Excel
For (int K = 0; k <dtsheetname. Rows. Count; k ++)
{
String strtablename = dtsheetname. Rows [k] ["table_name"]. tostring ();
String STR = "select * from [" + strtablename + "]";
Oledbdataadapter da = new oledbdataadapter (STR, Conn );
Da. Fill (DS, strtablename. Replace ("$", ""). tolower ());
Da. Dispose ();
}
Conn. Close ();
Conn. Dispose ();
}
Return Ds;

Read in XML format:

/// <Summary>
/// Obtain the data of the specified column in the index sheet of the Excel table XML
/// </Summary>
/// <Param name = "Doc"> Excel XML </param>
/// <Param name = "Index"> worksheet index </param>
/// <Param name = "row"> row index </param>
/// <Param name = "col"> column index </param>
/// <Returns> </returns>
Public static string getcelldata (xmldocument doc, int index, int row, int col)
{
If (Doc = NULL)
{
Return NULL;
}
Else
{
Xmlnamespacemanager xnm = new xmlnamespacemanager (Doc. nametable );
Xnm. addnamespace (prefix, SSNs );
Return xmlhelper. getvalue (Doc. documentelement. childnodes [Index]
. Selectnodes ("Table/row", xnm) [row]
. Childnodes [col]
. Childnodes [0]);
}
}
Excel in XML format

/// <Summary>
/// Obtain the Excel standard XML
/// </Summary>
/// <Returns> </returns>
Public static xmldocument getexcel ()
{
Xmldocument Doc = new xmldocument ();
Stringbuilder sbody = new stringbuilder ();
Sbody. append ("<? XML version =/"1.0/" encoding =/"UTF-8/"?> ");
Sbody. append ("<? MSO-application progid =/"Excel. Sheet/"?> ");
Sbody. append ("<workbook xmlns =/" urn: Schemas-Microsoft-com: Office: spreadsheet /"");
Sbody. append ("xmlns: O =/" urn: Schemas-Microsoft-com: Office /"");
Sbody. append ("xmlns: x =/" urn: Schemas-Microsoft-com: Office: Excel /"");
Sbody. append ("xmlns: Ss =/" urn: Schemas-Microsoft-com: Office: spreadsheet /"");
Sbody. append ("xmlns: html =/" http://www.w3.org/TR/REC-html40/ "> ");
Sbody. append ("<documentproperties xmlns =/" urn: Schemas-Microsoft-com: Office/"> ");
Sbody. append ("<author> Jia Shiyi </author> ");
Sbody. append ("<lastauthor> Jia Shiyi </lastauthor> ");
Sbody. append ("<created>" + datetime. Today. tostring (constants. date_formart) + "</created> ");
Sbody. append ("</documentproperties> ");
Sbody. append ("<styles> ");
Sbody. append ("<style SS: Id =/" default/"SS: Name =/" normal/"> ");
Sbody. append ("<alignment SS: horizontal =/" Left/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"/> ");
Sbody. append ("<interior/> ");
Sbody. append ("<numberformat/> ");
Sbody. append ("<protection/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" scenter/"> ");
Sbody. append ("<alignment SS: horizontal =/" center/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" sright/"> ");
Sbody. append ("<alignment SS: horizontal =/" right/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" sbold/"> ");
Sbody. append ("<alignment SS: horizontal =/" Left/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"SS: bold =/"1/"/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" scenterbold/"> ");
Sbody. append ("<alignment SS: horizontal =/" center/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"SS: bold =/"1/"/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" sdate/"> ");
Sbody. append ("<alignment SS: horizontal =/" Left/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"/> ");
Sbody. append ("<numberformat SS: format =/" short date/"/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" stime/"> ");
Sbody. append ("<alignment SS: horizontal =/" Left/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"/> ");
Sbody. append ("<numberformat SS: format =/" yyyy/M/d // H: mm; @/"/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" slink/"SS: Name =/" hyperlink/"> ");
Sbody. append ("<alignment SS: horizontal =/" Left/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"SS: color =/"# 0000ff/" SS: underline =/"Single/"/> ");
Sbody. append ("</style> ");
Sbody. append ("<style SS: Id =/" spercent/"> ");
Sbody. append ("<alignment SS: horizontal =/" right/"SS: vertical =/" center/"/> ");
Sbody. append ("<font SS: fontname =/" /"X: charset =/" 134/"SS: size =/" 12/"/> ");
Sbody. append ("<numberformat SS: format =/" percent/"/> ");
Sbody. append ("</style> ");
Sbody. append ("</styles> ");
Sbody. append ("</workbook> ");
Doc. loadxml (sbody. tostring ());
Return Doc;
}

Source code download: http://download.csdn.net/source/3160593