First I thought adding DataSet into DataGrid(dg) using DataSource and then using dg.RenderControl(_htmlTextWriter) would work. However, DataGrid can render only one DataTable and thus that method was failed.
So, I tried to embed Microsoft.Office.Interop.Excel component and use its classes. However, I got stuck with permission errors. If a user does not have OFFICE and if he/she does not know how to change the permission setting, this will bring hell lot of distraction to the user.
Then again, I had to change the method. I surfed the google and there is this Excel.xsl (ref. 1) to use to transform the DataSet XML to Excel XML.
ref. 1
/***********************************************************************************/
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-scripts"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
/***********************************************************************************/
So I used the code (ref. 2) to check what the XMLDocument's schema is like. It starts with NewDataSet and then with DataTable's Name for each DataTable.
ref. 2
/***********************************************************************************/
string filename = Server.MapPath("XmlDoc.xml");
//Create the FileStream to write with.
System.IO.FileStream stream = new System.IO.FileStream
(filename, System.IO.FileMode.Create);
//Create an XmlTextWriter with the fileStream.
System.Xml.XmlTextWriter xmlWriter =
new System.Xml.XmlTextWriter(stream,
System.Text.Encoding.Unicode);
XmlDataDocument xmlDataDoc = new XmlDataDocument(excelBook);
xmlDataDoc.Save(xmlWriter);
//excelBook.WriteXml(xmlWriter);
xmlWriter.Close();
/***********************************************************************************/
So, using the code (ref. 3) transform the DataSet XML (ref. 2) using XSL (ref. 1)
ref. 3
/***********************************************************************************/
XmlDataDocument doc = new XmlDataDocument(_DataSet);
StringWriter sww = new StringWriter();
XslCompiledTransform xtt = new XslCompiledTransform();
xtt.Load(Server.MapPath("Excel.xsl"));
xtt.Transform(doc, null, sww);
/***********************************************************************************/
Now, we had the Excel XML schema. Yet, the Excel.xsl is adding all the DataTables in
Thus, I thought adding "for-each" under the
Later, with the help of my colleague, I found out that adding each DataTable's XML data into STRING object and remove the duplicated XML tags such as "". As the DataTables are looped through, the "Workbook" tag is added seperately(ref. 4)
ref.4
/***********************************************************************************/
System.Text.StringBuilder swAll = new System.Text.StringBuilder();
swAll.Append(@"
/***********************************************************************************/
After all, the multisheets creation works fine. Now, I need to consider about the STYLES. I take wiki_excel_styles as an example. So I added the
ref. 5
/***********************************************************************************/
/* Append Styles */
swAll.Append(@"
/***********************************************************************************/
In that case, each node in the XML should be looped through to add the attributes: StyleID.
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList node = root.SelectNodes("/Workbook/Worksheet/Table");
The above code does not work as the EXCEL Schema has namespaces in it. After fiddling for some times, again with the help of my colleague, I gained the knowledge of useful XmlNamespaceManager. Changing the code (ref. 6) by adding Namespace could enable the XmlDocument to select the XPath node.
ref.6
/***********************************************************************************/
XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmlDoc.NameTable);
nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList node = root.SelectNodes("/ss:Workbook/ss:Worksheet/ss:Table", nsmgr);
xxxxxxxxxxxxxxxxxxxxxxx
XmlAttribute _attr = xmlDoc.CreateAttribute("ss", "StyleID", "urn:schemas-microsoft-com:office:spreadsheet");
_attr.Value = "s12";
cells.Attributes.Append(_attr);
/***********************************************************************************/
Everything's working fine and now need to adjust the column. AutoFitWidth does not work for TEXT; works only for NUMBERs. So I need to add something like "
Adding the following code (ref. 7) let the very first column expand with the specified width.
ref. 7
/***********************************************************************************/
XmlNode _column = xmlDoc.CreateNode(XmlNodeType.Element, "ss", "Column", "urn:schemas-microsoft-com:office:spreadsheet");
XmlAttribute _colattr = xmlDoc.CreateAttribute("ss", "Width", "urn:schemas-microsoft-com:office:spreadsheet");
_colattr.Value = "300";
_column.Attributes.Append(_colattr);
/***********************************************************************************/
you can also check on this website for sample project too.
There may be a problem with file extension such as xls and xlsx. This site tells about Excel 2007 XML format.
Great! Well done! now i can enjoy my weekend :P
