Moving to the practical side of the issue, what is the best way to generate an Excel spreadsheet on the web server? There are several different scenarios. If you are using Microsoft.Reporting.WebForms.ReportViewer control, there is nothing else to do because the control itself supports export to Excel. Of course, the control merely renders a report, which needs to be defined and generated first.
If Microsoft Office is installed, you could add a reference to Excel COM server and have the entire object model at your disposal. This is very powerful technology, but it requires Excel to be deployed to each web server which isn't really practical. Another downside - all these out-of-process calls to COM objects are expensive in terms of system performance.
What if we want a lean, lightweight solution that doesn't rely on any additional components? Well, we could generate a text document in comma-separated format. Excel will be able to open such a document, but it will look very primitive.
Now the good news: there is a way to generate professionally looking Excel spreadsheets from a pure ASP.NET web application. What we generate is technically not a spreadsheet, but an XML stream which conforms to the Office Excel XML schema (several schemas, to be precise). This format has been supported in Excel since Office XP. It is different from another XML-based format Microsoft adopted in Office 2007, Office Open XML. The latter is represented by a set of files placed in a ZIP archive, while the former is a single uncompressed XML file.
In order to avoid manipulating strings and StringBuilders and use an object model instead, I will create a set of classes to represent different elements of the schema (Workbook, Worksheet, Cell, Row, etc.). One way of doing this would be to generate code from schema using XSD.EXE /classes syntax. Here is how WorkBook class may look like (with fields instead of properties for brevity):
public class WorkBook : IGenerateOutput
{
public List<WorkSheet> WorkSheets = new List<WorkSheet>();
public List<Style> Styles = new List<Style>();
public string Author;
public string Company;
public string Title;
public WorkSheet CreateWorkSheet()
{
WorkSheet ws = new WorkSheet(String.Format("Sheet{0}", this.WorkSheets.Count + 1));
this.WorkSheets.Add(ws);
return ws;
}
public Style CreateStyle(string styleId)
{
Style s = new Style(styleId);
this.Styles.Add(s);
return s;
}
public string GetOfficeXml()
{
// Instantiate XmlTextWriter and call GenerateOutput()
}
public void GenerateOutput(XmlTextWriter writer)
{
writer.WriteStartElement("Workbook");
writer.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("xmlns:o", "urn:schemas-microsoft-com:office:office");
writer.WriteAttributeString("xmlns:x", "urn:schemas-microsoft-com:office:excel");
writer.WriteAttributeString("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("xmlns:html", "http://www.w3.org/TR/REC-html40");
if (this.Styles.Count > 0)
{
writer.WriteStartElement("Styles");
foreach (Style style in this.Styles)
{
style.GenerateOutput(writer);
}
writer.WriteEndElement();
}
foreach (WorkSheet sheet in this.WorkSheets)
{
sheet.GenerateOutput(writer);
}
}
}
WorkBook features a couple of factory methods to create new styles and worksheets; it uses chain of responsibility pattern to delegate responsibility for rendering Xml to its child elements. Other classes look very similar:
public class WorkSheet : IGenerateOutput
{
public List<Row> Rows = new List<Row>();
public List<Column> Columns = new List<Column>();
public string _Name;
public WorkSheet(string name)
{
this.Name = name;
}
public Row CreateRow()
{
Row r = new Row();
this.Rows.Add(r);
return r;
}
public Column CreateColumn()
{
Column c = new Column();
this.Columns.Add(c);
return c;
}
public void GenerateOutput(XmlTextWriter writer)
{
writer.WriteStartElement("Worksheet");
writer.WriteAttributeString("ss:Name", this.Name);
writer.WriteStartElement("Table");
if (this.Columns.Count > 0)
{
foreach (Column c in this.Columns)
{
c.GenerateOutput(writer);
}
}
foreach (Row r in this.Rows)
{
r.GenerateOutput(writer);
}
writer.WriteEndElement();
writer.WriteEndElement();
}
}
So, in order to export some data to Excel, all we need to do is instantiate WorkBook class, create one or more worksheets, add rows with cells, and then call GetOfficeXml() method:
WorkBook wb = new WorkBook();
wb.Author = "Riyad Mammadov";
wb.Title = "Test";
WorkSheet ws = wb.CreateWorkSheet();
Row r = ws.CreateRow();
r.CreateCell(CellDataType.Number, "12345");
r.CreateCell(CellDataType.String, "Hello, World");
string xml = wb.GetOfficeXml();
