Creating Excel/Word/HTML Document Using C#

Introduction

This article describes a simple approach to creating Excel/Word/HTML documents using C#, ADO.Net dataset. Codes internally generate XML and XSL documents and then transform them into well-formatted HTML documents. Since both Excel and Word support HTML thus we can create these documents from HTML sources. We can also change the appearance of data using CSS.

Create document function

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

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

CreateDocument function internally calls CreateXMLDocument() and CreateXSLDocument(), then transforms the XML document using XSL document and creates the 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 existence
        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

The following are a 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 on the number of records per sheet ( you can populate only 65536 rows per sheet)