This article is away shows you how to convert from xml to Excel vise versa and here the code shows that:
1- Conversion from XML to Xslt:
There are two ways to do that one using workbook method, this way is long as you must create each cell and its layout and name each cell by the data you want which is not dynamic to export data into Excel way, and the second method which I use here is using xml document and xml path document and XslTransform this all just create the excel file as output containing any number of cells dynamically you must not to create each cell alone.
Using the code:
using System.IO;
using System.Xml.Xsl;
using System.Xml.XPath;
private void createXML_ConverXSL()
{
Excel_file="Excel.xsl"; //contains schema
XslTransform xsl=new XslTransform();
xsl.Load(Excel_file);
XPathDocument doc = new XPathDocument("xmlfile.xml");
Stream strmTemp = new FileStream("Outputfile.xls",FileMode.OpenOrCreate);
xsl.Transform(doc, null, strmTemp, null);
strmTemp.Close();
}
This all the code you will write to convert from any xml file to Excel file and the file "Excel.xsl" here:
Just open new notepad copy and past the next schema and name it excel.xsl or any name and use it in xsl.load("name.xsl"):
The schema:
2- Converting from XSLT to XML
The code below shows you how to read xsl sheet and convert it to xml document
Using the code:
OleDbConnection oleDBCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + (strxlsFileName) + ";" +
"Extended Properties=Excel 8.0;");
oleDBCon.Open();
DataTable dt = new DataTable();
dt = oleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt != null)
{
String[] excelSheets = new String[dt.Rows.Count];
// Add the sheet name to the string array.
cboSheetnames.Items.Clear();
foreach(DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
System.Web.UI.WebControls.ListItem item = new System.Web.UI.WebControls.ListItem();
item.Value=strSheetTableName;
item.Text=strSheetTableName.Substring(0,strSheetTableName.Length-1);
cboSheetnames.Items.Add(item);
}
cboSheetnames.SelectedIndex=0;
}
oleDBCon.Close();
Note this way reads all the sheets in the one excel file so I use a combo box to show all the sheets and you can choose any of them and there is another function getting every sheet in data set as follow:
DataSet ds_excel= new DataSet();
ds_excel.Clear();
oleDBCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + (strxlsFileName) + ";" +
"Extended Properties=Excel 8.0;");
oleDBCon.Open();
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT * FROM ["+strSheetName+"$]",oleDBCon);
objAdapter1.SelectCommand.ExecuteNonQuery();
objAdapter1.Fill(ds_excel);
dataGrid1.DataSource = ds_excel.Tables[0].DefaultView;
oleDBCon.Close();
ds.WriteXml("filename");
You can put this code in a button function and when you choose the sheet from the combo and click the button the previous function executed and filling the data set.
Conclusion:
The first point shows you how to convert from xml to xsl in speed and easy and codeless way this a dynamic than you create each cell and then pass data to it, this way general for any number of cells.
And the second point shows how to convert it back again from xsl to xml document even it contains may sheets in the same file.
