Hi,
I need a solution to export a dataset to an excel file without any asp code (HttpResonpsne...) but i did not find a good example to do this...
Best thanks in advance
+8
A:
I've created a class that exports a DataGridView or DataTable to an Excel file. You can probably change it a bit to make it use your DataSet instead (iterating through the DataTables in it). It also does some basic formatting which you could also extend.
To use it, simply call ExcelExport, and specify a filename and whether to open the file automatically or not after exporting. I also could have made them extension methods, but I didn't. Feel free to.
Note that Excel files can be saved as a glorified XML document and this makes use of that.
EDIT: This used to use a vanilla StreamWriter, but as pointed out, things would not be escaped correctly in many cases. Now it uses a XmlWriter, which will do the escaping for you.
The ExcelWriter class wraps an XmlWriter. I haven't bothered, but you might want to do a bit more error checking to make sure you can't write cell data before starting a row, and such. The code is below.
public class ExcelWriter : IDisposable
{
private XmlWriter _writer;
public enum CellStyle { General, Number, Currency, DateTime, ShortDate };
public void WriteStartDocument()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
_writer.WriteStartElement("ss", "Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
WriteExcelStyles();
}
public void WriteEndDocument()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteEndElement();
}
private void WriteExcelStyleElement(CellStyle style)
{
_writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteEndElement();
}
private void WriteExcelStyleElement(CellStyle style, string NumberFormat)
{
_writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteStartElement("NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("Format", "urn:schemas-microsoft-com:office:spreadsheet", NumberFormat);
_writer.WriteEndElement();
_writer.WriteEndElement();
}
private void WriteExcelStyles()
{
_writer.WriteStartElement("Styles", "urn:schemas-microsoft-com:office:spreadsheet");
WriteExcelStyleElement(CellStyle.General);
WriteExcelStyleElement(CellStyle.Number, "General Number");
WriteExcelStyleElement(CellStyle.DateTime, "General Date");
WriteExcelStyleElement(CellStyle.Currency, "Currency");
WriteExcelStyleElement(CellStyle.ShortDate, "Short Date");
_writer.WriteEndElement();
}
public void WriteStartWorksheet(string name)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Worksheet", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("Name", "urn:schemas-microsoft-com:office:spreadsheet", name);
_writer.WriteStartElement("Table", "urn:schemas-microsoft-com:office:spreadsheet");
}
public void WriteEndWorksheet()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteEndElement();
_writer.WriteEndElement();
}
public ExcelWriter(string outputFileName)
{
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
_writer = XmlWriter.Create(outputFileName, settings);
}
public void Close()
{
if (_writer == null) throw new NotSupportedException("Already closed.");
_writer.Close();
_writer = null;
}
public void WriteExcelColumnDefinition(int columnWidth)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Column", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteStartAttribute("Width", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteValue(columnWidth);
_writer.WriteEndAttribute();
_writer.WriteEndElement();
}
public void WriteExcelUnstyledCell(string value)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
_writer.WriteValue(value);
_writer.WriteEndElement();
_writer.WriteEndElement();
}
public void WriteStartRow()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
}
public void WriteEndRow()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteEndElement();
}
public void WriteExcelStyledCell(object value, CellStyle style)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("StyleID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
switch (style)
{
case CellStyle.General:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
break;
case CellStyle.Number:
case CellStyle.Currency:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number");
break;
case CellStyle.ShortDate:
case CellStyle.DateTime:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "DateTime");
break;
}
_writer.WriteValue(value);
// tag += String.Format("{1}\"><ss:Data ss:Type=\"DateTime\">{0:yyyy\\-MM\\-dd\\THH\\:mm\\:ss\\.fff}</ss:Data>", value,
_writer.WriteEndElement();
_writer.WriteEndElement();
}
public void WriteExcelAutoStyledCell(object value)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
//write the <ss:Cell> and <ss:Data> tags for something
if (value is Int16 || value is Int32 || value is Int64 || value is SByte ||
value is UInt16 || value is UInt32 || value is UInt64 || value is Byte)
{
WriteExcelStyledCell(value, CellStyle.Number);
}
else if (value is Single || value is Double || value is Decimal) //we'll assume it's a currency
{
WriteExcelStyledCell(value, CellStyle.Currency);
}
else if (value is DateTime)
{
//check if there's no time information and use the appropriate style
WriteExcelStyledCell(value, ((DateTime)value).TimeOfDay.CompareTo(new TimeSpan(0, 0, 0, 0, 0)) == 0 ? CellStyle.ShortDate : CellStyle.DateTime);
}
else
{
WriteExcelStyledCell(value, CellStyle.General);
}
}
#region IDisposable Members
public void Dispose()
{
if (_writer == null)
return;
_writer.Close();
_writer = null;
}
#endregion
}
Then you can export your DataTable using the following:
public static void ExcelExport(DataTable data, String fileName, bool openAfter)
{
//export a DataTable to Excel
DialogResult retry = DialogResult.Retry;
while (retry == DialogResult.Retry)
{
try
{
using (ExcelWriter writer = new ExcelWriter(fileName))
{
writer.WriteStartDocument();
// Write the worksheet contents
writer.WriteStartWorksheet("Sheet1");
//Write header row
writer.WriteStartRow();
foreach (DataColumn col in data.Columns)
writer.WriteExcelUnstyledCell(col.Caption);
writer.WriteEndRow();
//write data
foreach (DataRow row in data.Rows)
{
writer.WriteStartRow();
foreach (object o in row.ItemArray)
{
writer.WriteExcelAutoStyledCell(o);
}
writer.WriteEndRow();
}
// Close up the document
writer.WriteEndWorksheet();
writer.WriteEndDocument();
writer.Close();
if (openAfter)
OpenFile(fileName);
retry = DialogResult.Cancel;
}
}
catch (Exception myException)
{
retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
}
}
}
lc
2008-12-17 08:13:32
-1: Don't attempt to generate XML using a TextWriter in this way. I'll just point out one obvious problem: if your DataTable contains a string value with angle brackets, the above code won't escape them properly.
Joe
2008-12-17 10:12:30
Good point. Just goes to show I should stop to think a bit more. I've reworked it to use an XmlWriter. Theoretically, one should do a bit more error checking before releasing it in a public library, but if you are sure you'll call things in the right order you're "safe".
lc
2008-12-17 14:56:31
There is a mistake in the second part of the code. Instead of 'foreach (object o in dataTable.Rows)' should be 'foreach (object o in row.ItemArray)'.
Aleris
2009-01-14 09:17:58
Yes, yes it should - I just fixed it. Thanks for that.
lc
2009-01-15 08:13:26
lc, I've added some code to accept a DataSet and do a foreach on the tables to add worksheets. I also plan on organizing the code a little differently and fully documenting each method in MSDN XML format. Want me to send it to you?
Nazadus
2009-03-12 13:42:06
In case anyone else runs across this: If you have forget to assign a value to a column in a datatable, the XML Writer will STOP WRITING leaving you a mal-formed XML. Took me forever to find my mistake.
Nazadus
2009-03-12 15:02:49
Sure, sounds great and I'd love a copy. Thanks! Hope some of what I had here was useful for you as I really didn't spend a whole lot of time writing it (took me longer to research the excel format, really) :). You can email it to me at gmail (lee DOT calabrese @ ...).
lc
2009-03-13 04:40:35
I may be an idiot but I had to add an instance of OpenFileDialog to the `ExcelExport()` function and change the `OpenFile()` section to `openFileDialog.FileName = fileName; openFileDialog.OpenFile();`
Refracted Paladin
2010-01-13 20:11:54
@Refracted Paladin No idiot, I must have forgotten to include that function. OpenFile() is actually an instance method that does a shell execute of the file name. Easier than adding an OpenFileDialog (but does the same thing).
lc
2010-01-27 16:39:45
@lc: I see. That makes sense. Thanks for the follow up.
Refracted Paladin
2010-01-27 21:21:18
A:
Creating excel files in .NET applications is quite common and similar questions have been asked several times before. For instance here and here. The last question asks about reading excel files, but most suggested solutions should work both ways.
Rune Grimstad
2008-12-17 08:18:41
Correct me if I'm wrong, but mave was asking for a solution not on a web server?
lc
2008-12-17 08:20:34
Ah. I misunderstood your question then. I thought you were asking for a way to make excel files without WRITING any code. I'll rephrase my answer then.
Rune Grimstad
2008-12-17 08:40:55