Creating Excel/Word/HTML Document Using C#

This article describes a simple approach to create Excel/Word/HTML document using C#, ADO.Net dataset. Codes internally generate a XML document and XSL document and then transform it in well formatted HTML document. Since both Excel and Word supports HTML thus we can create these documents from HTML source. We can also change appearance of data using CSS.



The CreateDocument function has four parameters i.e. path of the file, name of the file, dataset object, and the type of file to be created:

XmlXslToExcel.CreateDocument(filePath, "test", ds, XmlXslToExcel.DocumentType.Excel);

CreateDocument function internally call CreateXMLDocument() and CreateXSLDocument() and then transform the XML document using XSL document and creates specified file.

public static bool CreateDocument(string filePath, string fileName, DataSet ds, DocumentType docType)
{
            XmlDocument xmlDoc = null;
            XslCompiledTransform xslTran = null ;
            FileStream excelFileStream = null;
            bool isFileCreated;
            try
            {
                if (ds == null || ds.Tables.Count == 0) return false;
                xmlDoc = CreateXmlDocument(ds);
                xslTran = CreateXslDocument(ds);
                if (xmlDoc == null || xslTran == null) return false;
                // Append xls extention in file name to create an excel                                            

    file (we can also use doc extention to create word or html document file)
                switch (docType)
                {
                    case DocumentType.Excel:
                        filePath = filePath + "\\Excel\\" + fileName + ".xls";
                        break;
                    case DocumentType.Word:
                        filePath = filePath + "\\Word\\" + fileName + ".doc";
                        break;
                    case DocumentType.HTML:
                        filePath = filePath + "\\HTML\\" + fileName + ".htm";
                        break;
                    default:
                        filePath = filePath + "\\HTML\\" + fileName + ".htm";
                        break;
                }

                // Create FileStream object with file mode as Create
                excelFileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create);

                // Create XmlTextWriter object for the FileStream            
                System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(excelFileStream, System.Text.Encoding.Unicode);

                // Now transform xml document into specified stream
                // Or we can also specify the output file which is to be created by transformation
                xslTran.Transform(xmlDoc, null, xtw);
                //xslTran.Transform(xmlDoc, fileName);
                xtw.Flush();
                excelFileStream.Flush();
                xtw.Close();
                excelFileStream.Close();
                xtw = null;
                excelFileStream = null;
                // check for file existance
                isFileCreated = File.Exists(filePath);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (excelFileStream != null)
                    excelFileStream.Close();
                excelFileStream = null;
                xmlDoc = null;
                xslTran = null;
            }
            return isFileCreated;
}

Excel limitations:

Following are few limitations of this approach:

1. This approach will not work where you want to create multiple sheets from the available data.
2. There is also a limitation of number of records per sheet ( you can populate only 65536 rows per sheet)