I made something to read data from Excel and write XML, and put it here for future use.
Excelhelper. CS
Code Using system;
Using system. Data;
Using system. Data. oledb;
Namespace readexecelandwritexml
{
Public class excelhelper
{
Public static datatable getlist (string path, string sheetname)
{
VaR conn =
String. Format (@ "provider = Microsoft. Jet. oledb.4.0; Data Source = {0}; extended properties = Excel 8.0;", PATH );
Using (VAR oledbconnection = new oledbconnection (conn ))
{
// Sheetname is the name of the table in Excel, for example, sheet1
VaR SQL = string. Format ("select * from [{0} $]", sheetname );
VaR cmd = new oledbcommand (SQL, oledbconnection );
VaR ad = new oledbdataadapter (CMD );
VaR DS = new dataset ();
Try
{
Ad. Fill (DS );
Return Ds. Tables [0];
}
Catch (exception ex)
{
Console. writeline (ex. Message );
Return NULL;
}
}
}
}
}
Xmlhelper. CS
Code Using system;
Using system. Data;
Using system. IO;
Using system. xml;
Namespace readexecelandwritexml
{
Public class xmlhelper
{
/* <Root>
* <Item>
* <O> 123 <o>
* <K> 111123 </k>
* </Item>
* </Root>
*/
Private const string Path = @ "E: \ study \ app \ readexecelandwritexml \ data \ IDs. xml ";
/// <Summary>
/// Read XML data
/// </Summary>
Public static void readxml ()
{
VaR document = new xmldocument ();
Document. Load (PATH );
VaR list = Document. getelementsbytagname ("item ");
Foreach (xmlnode node in List)
{
Console. writeline ("O: {0}, K: {1}", node. childnodes [0]. innertext, node. childnodes [1]. innertext );
}
}
/// <Summary>
/// Use selectsinglenode in xpath
/// </Summary>
/// <Param name = "oid"> </param>
Public static void selectsinglenode (int oid)
{
VaR document = new xmldocument ();
Document. Load (PATH );
Xmlnode node = Document. selectsinglenode (string. Format ("// root/item [O = '{0}']/K", OID ));
If (node! = NULL)
{
Console. writeline (node. innertext );
}
}
/// <Summary>
/// Write XML based on the data in the datatable, compliant with the reservation Specification
/// </Summary>
/// <Param name = "DT"> </param>
Public static void write (datatable DT)
{
VaR document = new xmldocument ();
If (file. exists (PATH ))
{
File. Delete (PATH );
}
VaR root = Document. createelement ("root ");
Foreach (datarow row in DT. Rows)
{
VaR item = Document. createelement ("item ");
VaR o = Document. createelement ("O ");
VaR K = Document. createelement ("K ");
VaR OID = convert. toint32 (row ["column1"]);
VaR KID = convert. toint32 (row ["column2"]);
O. innertext = oid. tostring ();
K. innertext = kid. tostring ();
Item. appendchild (O );
Item. appendchild (k );
Root. appendchild (item );
}
Document. appendchild (Root );
Document. Save (PATH );
}
}
}
Tools for future reference