Export a DataSet to Microsoft Excel without using the COM objects

It is a routine job to export data from MS SQL Server database to MS Excel, or import data from MS Excel to MS SQL Server database. There are lot of solutions based on MS Excel COM object, which make youe application depends on MS Excel, and you must install MS Excel application on you web application server. This kind of solutions are discouraged for the following reasons.

• For security reason, your customer maybe would not like to install Excel application on the web application server.
• The Excel process will expense large amount of memory of the web server. When there exists lots of instances of Excel process, it will use out the memory of the web server. In addition, you need manage the lifecycle of Excel process in your code to release resources shared by Excel.

So I prefer to not use Excel COM objects.

1. Sinlge DataTable in DataSet

Public Class DataExporter

Public Shared Function ExportToExcel(ByVal source As DataTable) As String

Dim sb As StringBuilder = New StringBuilder()

Const startExcelXML As String = "" & vbCr & vbLf & _
"" xmlns:o=""urn:schemas-microsoft-com:office:office""" & vbCr & vbLf & " " & _
"xmlns:x=""urn:schemas- microsoft-com:office:" & "excel""" & vbCr & vbLf & _
" xmlns:ss=""urn:schemas-microsoft-com:" & "office:spreadsheet"">" & vbCr & vbLf & _
" " & vbCr & vbLf & " " & "" & vbCr & vbLf & " " & "" & vbCr & vbLf & " " & _
"" & vbCr & vbLf & " " & vbCr & vbLf & " " & _
"" & vbCr & vbLf & " " & vbCr & vbLf & " " & _
"" & vbCr & vbLf & " "
Const endExcelXML As String = ""

Dim rowCount As Integer = 0
Dim sheetCount As Integer = 1

sb.Append(startExcelXML)

sb.Append("")
sb.Append("

")
sb.Append("")

For x As Integer = 0 To source.Columns.Count - 1
sb.Append("")
sb.Append(source.Columns(x).ColumnName)
sb.Append("")
Next
sb.Append("

")

For Each x As DataRow In source.Rows
rowCount += 1
'if the number of rows is > 64000 create a new page to continue output
If rowCount = 64000 Then
rowCount = 0
sheetCount += 1
sb.Append("

")
sb.Append(" ")
sb.Append("")
sb.Append("")
End If

sb.Append("")
'ID=" + rowCount + "
For y As Integer = 0 To source.Columns.Count - 1
Dim rowType As System.Type
rowType = x(y).[GetType]()
Select Case rowType.ToString()
Case "System.String"
Dim XMLstring As String = x(y).ToString()
Const quote As Char = """"
XMLstring = XMLstring.Trim()
XMLstring = XMLstring.Replace("&", "&")
XMLstring = XMLstring.Replace(">", ">")
XMLstring = XMLstring.Replace("XMLstring = XMLstring.Replace("'", "'")
XMLstring = XMLstring.Replace(quote, """)

sb.Append("" & "")
sb.Append(XMLstring)
sb.Append("")
Exit Select
Case "System.DateTime"
'Excel has a specific Date Format of YYYY-MM-DD followed by
'the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
'The Following Code puts the date stored in XMLDate
'to the format above
Dim XMLDate As DateTime = DirectCast(x(y), DateTime)
Dim XMLDatetoString As String = ""
'Excel Converted Date
XMLDatetoString = (((((XMLDate.Year.ToString() & "-") + _
(If(XMLDate.Month < 10, "0" & XMLDate.Month.ToString(), XMLDate.Month.ToString())) & "-") + _
(If(XMLDate.Day < 10, "0" & XMLDate.Day.ToString(), XMLDate.Day.ToString())) & "T") + _
(If(XMLDate.Hour < 10, "0" & XMLDate.Hour.ToString(), XMLDate.Hour.ToString())) & ":") + _
(If(XMLDate.Minute < 10, "0" & XMLDate.Minute.ToString(), XMLDate.Minute.ToString())) & ":") + _
(If(XMLDate.Second < 10, "0" & XMLDate.Second.ToString(), XMLDate.Second.ToString())) & ".000"
sb.Append("" & "")
sb.Append(XMLDatetoString)
sb.Append("")
Exit Select
Case "System.Boolean"
sb.Append("" & "")
sb.Append(x(y).ToString())
sb.Append("")
Exit Select
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"
sb.Append("" & "")
sb.Append(x(y).ToString())
sb.Append("")
Exit Select
Case "System.Decimal", "System.Double"
sb.Append("" & "")
sb.Append(x(y).ToString())
sb.Append("")
Exit Select
Case "System.DBNull"
sb.Append("" & "")
sb.Append("")
sb.Append("")
Exit Select
Case Else
Throw (New Exception(rowType.ToString() & " not handled."))
End Select
Next ' iterate columns end
sb.Append("")

Next ' iterate rows end

sb.Append("

")
sb.Append(" ")

sb.Append(endExcelXML)

Return sb.ToString()

End Function

End Class

2. Multiple DataTables in a DataSet

public static void exportToExcel(DataSet source, string fileName)

{

System.IO.StreamWriter excelDoc;

excelDoc = new System.IO.StreamWriter(fileName);
const string startExcelXML = "rn"xmlns="urn:schemas-microsoft-com:office:spreadsheet"rn" +
" xmlns:o="urn:schemas-microsoft-com:office:office"rn " +
"xmlns:x="urn:schemas- microsoft-com:office:" +
"excel"rn xmlns:ss="urn:schemas-microsoft-com:" +
"office:spreadsheet">rn rn " +
"rn " +
"rn " +
"rn "ss:ID="Decimal">rn "ss:Format="0.0000"/>rn rn " +
"rn "ss:ID="DateLiteral">rn "ss:Format="mm/dd/yyyy;@"/>rn rn " +
"rn ";
const string endExcelXML = "";

int rowCount = 0;
int sheetCount = 1;
/*

xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">











*/
excelDoc.Write(startExcelXML);
excelDoc.Write("");
excelDoc.Write("

");
excelDoc.Write("");
for(int x = 0; x < source.Tables[0].Columns.Count; x++)
{
excelDoc.Write("");
excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
excelDoc.Write("");
}
excelDoc.Write("");
foreach(DataRow x in source.Tables[0].Rows)
{
rowCount++;
//if the number of rows is > 64000 create a new page to continue output
if(rowCount==64000)
{
rowCount = 0;
sheetCount++;
excelDoc.Write("");
excelDoc.Write(" ");
excelDoc.Write("");
excelDoc.Write("");
}
excelDoc.Write(""); //ID=" + rowCount + "
for(int y = 0; y < source.Tables[0].Columns.Count; y++)
{
System.Type rowType;
rowType = x[y].GetType();
switch(rowType.ToString())
{
case "System.String":
string XMLstring = x[y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&","&");
XMLstring = XMLstring.Replace(">",">");
XMLstring = XMLstring.Replace("excelDoc.Write("" +
"");
excelDoc.Write(XMLstring);
excelDoc.Write("");
break;
case "System.DateTime":
//Excel has a specific Date Format of YYYY-MM-DD followed by
//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
//The Following Code puts the date stored in XMLDate
//to the format above
DateTime XMLDate = (DateTime)x[y];
string XMLDatetoString = ""; //Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() +
"-" +
(XMLDate.Month < 10 ? "0" +
XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" +
(XMLDate.Day < 10 ? "0" +
XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" +
(XMLDate.Hour < 10 ? "0" +
XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" +
(XMLDate.Minute < 10 ? "0" +
XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
":" +
(XMLDate.Second < 10 ? "0" +
XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
".000";
excelDoc.Write("" +
"");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("");
break;
case "System.Boolean":
excelDoc.Write("" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
excelDoc.Write("" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.DBNull":
excelDoc.Write("" +
"");
excelDoc.Write("");
excelDoc.Write("");
break;
default:
throw(new Exception(rowType.ToString() + " not handled."));
}
}
excelDoc.Write("");
}
excelDoc.Write("");
excelDoc.Write(" ");
excelDoc.Write(endExcelXML);
excelDoc.Close();
}

Actually this approach exports data to an xml-formatting string, and the file you exported is not really an Excel file. The end users must open the exported .xls file with MS Excel application and use "Save As" to reconstruct it as a "real" Excel file. Otherwise end users can not use it to import data to MS SQL Server database.

References

[@more@]