|
Hello:
I have been using this method to successfully export arbitarily large DataSets (mostly with just one populated DataTable) to Excel. Now (since my company is really cheap, and not buying enough Excel licenses for everyone), I would like to do the same thing for OpenOffice Calc as well. However, OpenOffice Calc does not appear able to read the XML files this generates (it trys to do a Text Conversion as if it were importing a delimited text file). Is there a similar method to generate a file (XML or otherwise) that OpenOffice Calc can read?
Would greatly appreciate any assistance!
Regards,
Geoffrey Roberts
|
|
|
|
|
Nevermind, I used to code posted by Sanjeev Kumar Singh to generate an Excel 2000 binary file directly -- that works like a charm! Thanks again to Sanjeev as well as to the original poster and all contributers to CodeProject -- you guys are the best!
Geoffrey
|
|
|
|
|
Thanks a million for posting this. I had spent many hours trying to figure this out.
corcaigh
|
|
|
|
|
How can you set the path where the file is saved?
|
|
|
|
|
you can set the path using Request.PhysicalApplicationPath
the code looks somthing like:
String fPath = Request.PhysicalApplicationPath + "/Report/report.xls" ;
ExportToExcel(ds, fPath) ;
** Note: It is saving file on server, not your local PC.
Hope this help.
|
|
|
|
|
Useful code, thanks! I like the way it properly types the dataset columns which is better than another export code library that I've tried.
Warning!!!
Just one thing to watch out for, make sure that the characters < > " ' & are properly represented as xml entities. If you copy the code above directly, you will find errors in some datasets. There are a couple of replacements missing. Use the following instead:-
switch (rowType.ToString())
{
case "System.String":
string XMLstring = x[y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "&");
XMLstring = XMLstring.Replace(">", ">");
XMLstring = XMLstring.Replace("<", "<");
XMLstring = XMLstring.Replace("'", "'");
XMLstring = XMLstring.Replace("\"", """);
I had an idea about speeding things up with a memory stream but the output get truncated.
Happy days ! Nik Bean, CraftedLogic.net
modified on Friday, April 24, 2009 11:59 AM
|
|
|
|
|
Converted to VB, also handles multiple Data Tables in the dataset creates a new Sheet for every Data Table and names the Sheet same as Data Table Name, also a few bug fixes.
Public Shared Sub exportToExcel(ByVal source As DataSet, ByVal fileName As String)
Dim excelDoc As System.IO.StreamWriter
excelDoc = New System.IO.StreamWriter(fileName)
Const startExcelXML As String = "<xml version>" & vbCr & vbLf & "<Workbook " & "xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & 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 & " <Styles>" & vbCr & vbLf & " " & "<Style ss:ID=""Default"" ss:Name=""Normal"">" & vbCr & vbLf & " " & "<Alignment ss:Vertical=""Bottom""/>" & vbCr & vbLf & " <Borders/>" & vbCr & vbLf & " <Font/>" & vbCr & vbLf & " <Interior/>" & vbCr & vbLf & " <NumberFormat/>" & vbCr & vbLf & " <Protection/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""BoldColumn"">" & vbCr & vbLf & " <Font " & "x:Family=""Swiss"" ss:Bold=""1""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""StringLiteral"">" & vbCr & vbLf & " <NumberFormat" & " ss:Format=""@""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " <Style " & "ss:ID=""Decimal"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""0.0000""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""Integer"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""0""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " <Style " & "ss:ID=""DateLiteral"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""mm/dd/yyyy;@""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "</Styles>" & vbCr & vbLf & " "
Const endExcelXML As String = "</Workbook>"
Dim rowCount As Integer = 0
Dim sheetCount As Integer = 0
excelDoc.Write(startExcelXML)
For Each dt As DataTable In source.Tables
sheetCount += 1
excelDoc.Write("<Worksheet ss:Name=""" & dt.TableName & """>")
excelDoc.Write("<Table>")
excelDoc.Write("<Row>")
For x As Integer = 0 To dt.Columns.Count - 1
excelDoc.Write("<Cell ss:StyleID=""BoldColumn""><Data ss:Type=""String"">")
excelDoc.Write(source.Tables(0).Columns(x).ColumnName)
excelDoc.Write("</Data></Cell>")
Next
excelDoc.Write("</Row>")
For Each x As DataRow In dt.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
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & """>")
excelDoc.Write("<Table>")
End If
excelDoc.Write("<Row>")
'ID=" + rowCount + "
For y As Integer = 0 To dt.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()
XMLstring = XMLstring.Trim()
XMLstring = XMLstring.Replace("&", "&")
XMLstring = XMLstring.Replace(">", ">")
XMLstring = XMLstring.Replace("<", "<")
excelDoc.Write("<Cell ss:StyleID=""StringLiteral"">" & "<Data ss:Type=""String"">")
excelDoc.Write(XMLstring)
excelDoc.Write("</Data></Cell>")
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"
excelDoc.Write("" & "")
excelDoc.Write(XMLDatetoString)
excelDoc.Write("")
Exit Select
Case "System.Boolean"
excelDoc.Write("" & "")
excelDoc.Write(x(y).ToString())
excelDoc.Write("")
Exit Select
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"
excelDoc.Write("" & "")
excelDoc.Write(x(y).ToString())
excelDoc.Write("")
Exit Select
Case "System.Decimal", "System.Double"
excelDoc.Write("" & "")
excelDoc.Write(x(y).ToString())
excelDoc.Write("")
Exit Select
Case "System.DBNull"
excelDoc.Write("" & "")
excelDoc.Write("")
excelDoc.Write("")
Exit Select
Case Else
Throw (New Exception(rowType.ToString() & " not handled."))
End Select
Next
excelDoc.Write("</Row>")
Next
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
Next ' Data Table
excelDoc.Write(endExcelXML)
excelDoc.Close()
End Sub
|
|
|
|
|
I am currently using this code to export data to an excel spreadsheet but when I try to re-import the file, it gives me the error of "External table is not in the expected format." So the one thing I realized, that even though I use
string strFileName = "ExportFile.xls";
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);
It seems that the file is still actually an XML spreadsheet. So when I go into the exported file, if I then do a file save as, the first option selected is an XML document but when I change it to a Microsoft Office Excel Workbook and try to re-import, the import goes smoothly.
Is there any way of saving this export file from this code directly to a MOExcelWorkbook?
|
|
|
|
|
|
hi, This is really nice code. but i have one question. i have only 'Time' in my DataSet Not 'Date'. so when it converts it to date time it gives me value like "1899-12-31T09:20:41.000", which gives me error, can you show me right direction, as i have to calculate total time so can not convert it in string.
Thank in advance!
Nishant
|
|
|
|
|
At the beginning of the function I write out style formats for the excel document. you'll want to change the following style tag to your preferred time format (not sure what the format can be):
<Style ss:ID="DateLiteral">
<NumberFormat ss:Format="mm/dd/yyyy;@"/>
</Style>
|
|
|
|
|
Haii ALl,
Can any one pla tell me how to add multiple sheets in excell using c#
|
|
|
|
|
Study the code. Tell me what you've tried so far.
|
|
|
|
|
Can u tell me how to add header ,datetime and footer in this i able to export the data in to different worksheet but i want to add header in each worksheet and datetime when user print could any tell me how i can do that, i will be appreciated, thanks for the reply
|
|
|
|
|
Good article! I made a variation on the vb.net version of the previous poster, so it does no longer support styles, which makes it simpler and more general IMHO. I turned it into a class and compiled it as dll. I also extended it with the possibility to include a sheet name, where the sheetname becomes something like "Sheetn_xxxxxx" where xxxxx represents the name you give as parameter and n the sheet order number.
Here is the call:
Dim myStream As New System.IO.StringWriter()
Dim dstox As New DatasetToExcel.DatasetToExcel
myStream = dstox.exportToExcel(dsTotalSalesJanuary, "Jan2009")
myStream.Close()
And here is the code:
Imports System.Text
Public Class DatasetToExcel
Public Function exportToExcel(ByVal source As DataSet, ByVal sheetName As String)
Dim excelDoc As New System.IO.StringWriter()
Dim sb As New StringBuilder
sb.Append("<?xml version=""1.0""?>")
sb.Append(vbCrLf)
sb.Append("<?mso-application progid=""Excel.Sheet""?>")
sb.Append(vbCrLf)
sb.Append("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")
sb.Append(vbCrLf)
sb.Append("xmlns =""urn:schemas-microsoft-com:office:office""")
sb.Append(vbCrLf)
sb.Append("xmlns:x=""urn:schemas-microsoft-com:office:excel""")
sb.Append(vbCrLf)
sb.Append("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
sb.Append(vbCrLf)
sb.Append("xmlns:html=""http://www.w3.org/TR/REC-html40"">")
sb.Append(vbCrLf)
Dim startExcelXML As String = sb.ToString
Const endExcelXML As String = "</Workbook>"
Dim rowCount As Integer = 0
Dim sheetCount As Integer = 1
'WORKSHEET AND TABLE TAGS:
excelDoc.Write(startExcelXML)
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & "_" & sheetName & """>")
excelDoc.Write("<Table>")
'CREATE HEADER ROW:
excelDoc.Write("<Row>")
For x As Integer = 0 To source.Tables(0).Columns.Count - 1
excelDoc.Write("<Cell><Data ss:Type=""String"">")
excelDoc.Write(source.Tables(0).Columns(x).ColumnName)
excelDoc.Write("</Data></Cell>")
Next
excelDoc.Write("</Row>")
'INSERT THE DATA ROWS:
For Each x As DataRow In source.Tables(0).Rows
rowCount += 1
If rowCount = 64000 Then
rowCount = 0
sheetCount += 1
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & """>")
excelDoc.Write("<Table>")
End If
excelDoc.Write("<Row>")
For y As Integer = 0 To source.Tables(0).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()
XMLstring = XMLstring.Trim()
XMLstring = XMLstring.Replace("&", "&")
XMLstring = XMLstring.Replace(">", ">")
XMLstring = XMLstring.Replace("<", "<")
excelDoc.Write("<Cell>" & "<Data ss:Type=""String"">")
excelDoc.Write(XMLstring)
excelDoc.Write("</Data></Cell>")
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"
excelDoc.Write("<Cell ss:StyleID=""DateLiteral"">" & "<Data ss:Type=""DateTime"">")
'excelDoc.Write("<Cell>" & "<Data ss:Type=""DateTime"">")
excelDoc.Write(XMLDatetoString)
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Boolean"
excelDoc.Write("<Cell>" & "<Data ss:Type=""String"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"
excelDoc.Write("<Cell>" & "<Data ss:Type=""Number"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Decimal", "System.Double"
excelDoc.Write("<Cell>" & "<Data ss:Type=""Number"">")
excelDoc.Write(Replace(x(y).ToString(), ",", "."))
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.DBNull"
excelDoc.Write("<Cell>" & "<Data ss:Type=""String"">")
excelDoc.Write("")
excelDoc.Write("</Data></Cell>")
Exit Select
Case Else
Throw (New Exception(rowType.ToString() & " not handled."))
End Select
Next
excelDoc.Write("</Row>")
Next
'INSERT THE CLOSING TAGS:
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write(endExcelXML)
excelDoc.Close()
'RETURN THE DOCUMENT AS STRINGWRITER
Return excelDoc
End Function
End Class
Thank you Xodiak for an excellent article!
|
|
|
|
|
I took what you had converted it to VB.NET and then changed from StreamWriter to StringWriter. Now it asks if you want to save the Excel doc. Thaought someone else may be interested to see it. The code you wrote works great. Thanks so much for posting it.
<pre>
Protected Sub exportToExcel(ByVal source As DataSet)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim excelDoc As New System.IO.StringWriter()
'Dim excelDoc As System.IO.StreamWriter
'excelDoc = New System.IO.StreamWriter(fileName)
Const startExcelXML As String = "<xml version>" & vbCr & vbLf & "<Workbook " & "xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCr & vbLf & " xmlns =""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 & " <Styles>" & vbCr & vbLf & " " & "<Style ss:ID=""Default"" ss:Name=""Normal"">" & vbCr & vbLf & " " & "<Alignment ss:Vertical=""Bottom""/>" & vbCr & vbLf & " <Borders/>" & vbCr & vbLf & " <Font/>" & vbCr & vbLf & " <Interior/>" & vbCr & vbLf & " <NumberFormat/>" & vbCr & vbLf & " <Protection/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""BoldColumn"">" & vbCr & vbLf & " <Font " & "x:Family=""Swiss"" ss:Bold=""1""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""StringLiteral"">" & vbCr & vbLf & " <NumberFormat" & " ss:Format=""@""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " <Style " & "ss:ID=""Decimal"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""0.0000""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""Integer"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""0""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " <Style " & "ss:ID=""DateLiteral"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""mm/dd/yyyy;@""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "</Styles>" & vbCr & vbLf & " "
Const endExcelXML As String = "</Workbook>"
Dim rowCount As Integer = 0
Dim sheetCount As Integer = 1
'
' <xml version>
' <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
' xmlns ="urn:schemas-microsoft-com:office:office"
' xmlns:x="urn:schemas-microsoft-com:office:excel"
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
' <Styles>
' <Style ss:ID="Default" ss:Name="Normal">
' <Alignment ss:Vertical="Bottom"/>
' <Borders/>
' <Font/>
' <Interior/>
' <NumberFormat/>
' <Protection/>
' </Style>
' <Style ss:ID="BoldColumn">
' <Font x:Family="Swiss" ss:Bold="1"/>
' </Style>
' <Style ss:ID="StringLiteral">
' <NumberFormat ss:Format="@"/>
' </Style>
' <Style ss:ID="Decimal">
' <NumberFormat ss:Format="0.0000"/>
' </Style>
' <Style ss:ID="Integer">
' <NumberFormat ss:Format="0"/>
' </Style>
' <Style ss:ID="DateLiteral">
' <NumberFormat ss:Format="mm/dd/yyyy;@"/>
' </Style>
' </Styles>
' <Worksheet ss:Name="Sheet1">
' </Worksheet>
' </Workbook>
'
excelDoc.Write(startExcelXML)
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & """>")
excelDoc.Write("<Table>")
excelDoc.Write("<Row>")
For x As Integer = 0 To source.Tables(0).Columns.Count - 1
excelDoc.Write("<Cell ss:StyleID=""BoldColumn""><Data ss:Type=""String"">")
excelDoc.Write(source.Tables(0).Columns(x).ColumnName)
excelDoc.Write("</Data></Cell>")
Next
excelDoc.Write("</Row>")
For Each x As DataRow In source.Tables(0).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
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & """>")
excelDoc.Write("<Table>")
End If
excelDoc.Write("<Row>")
'ID=" + rowCount + "
For y As Integer = 0 To source.Tables(0).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()
XMLstring = XMLstring.Trim()
XMLstring = XMLstring.Replace("&", "&")
XMLstring = XMLstring.Replace(">", ">")
XMLstring = XMLstring.Replace("<", "<")
excelDoc.Write("<Cell ss:StyleID=""StringLiteral"">" & "<Data ss:Type=""String"">")
excelDoc.Write(XMLstring)
excelDoc.Write("</Data></Cell>")
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"
excelDoc.Write("<Cell ss:StyleID=""DateLiteral"">" & "<Data ss:Type=""DateTime"">")
excelDoc.Write(XMLDatetoString)
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Boolean"
excelDoc.Write("<Cell ss:StyleID=""StringLiteral"">" & "<Data ss:Type=""String"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"
excelDoc.Write("<Cell ss:StyleID=""Integer"">" & "<Data ss:Type=""Number"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Decimal", "System.Double"
excelDoc.Write("<Cell ss:StyleID=""Decimal"">" & "<Data ss:Type=""Number"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.DBNull"
excelDoc.Write("<Cell ss:StyleID=""StringLiteral"">" & "<Data ss:Type=""String"">")
excelDoc.Write("")
excelDoc.Write("</Data></Cell>")
Exit Select
Case Else
Throw (New Exception(rowType.ToString() & " not handled."))
End Select
Next
excelDoc.Write("</Row>")
Next
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write(endExcelXML)
Response.Write(excelDoc)
excelDoc.Close()
End Sub
</pre>
|
|
|
|
|
Hello, do you think write this example in vb.net ?
Fernando.
|
|
|
|
|
Here is the code if your dataset contains multiple datatables. Pretty simple change.
public static void exportToExcel(DataSet source, string fileName)
{
System.IO.StreamWriter excelDoc;
excelDoc = new System.IO.StreamWriter(fileName);
const string startExcelXML = "<xml version="">\r\n<workbook hold=" /> " xmlns="\" urn:schemas-microsoft-com:office:spreadsheet\"\r\n""="" mode="hold"> " xmlns =\"urn:schemas-microsoft-com:office:office\"\r\n " +
"xmlns:x=\"urn:schemas- microsoft-com:office:" +
"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
"office:spreadsheet\">\r\n <styles>\r\n " +
"\r\n " +
"<alignment ss:vertical="\"Bottom\"/">\r\n <borders />" +
"\r\n <font />\r\n <interior />\r\n <numberformat />" +
"\r\n <protection />\r\n </alignment>\r\n " +
"\r\n <font hold=" /> " x:family="\"Swiss\"" ss:bold="\"1\"/" xmlns:x="#unknown">\r\n </font>\r\n " +
"\r\n <numberformat"> " ss:Format=\"@\"/>\r\n \r\n \r\n <numberformat hold=" /> " ss:format="\"0.0000\"/">\r\n </numberformat>\r\n " +
"\r\n <numberformat hold=" /> " ss:format="\"0\"/">\r\n </numberformat>\r\n \r\n <numberformat hold=" /> " ss:format="\"mm/dd/yyyy;@\"/">\r\n </numberformat>\r\n " +
"\r\n ";
const string endExcelXML = "";
int rowCount = 0;
int sheetCount = 0;
excelDoc.Write(startExcelXML);
foreach (DataTable dt in source.Tables)
{
sheetCount++;
excelDoc.Write("<worksheet ss:name="\" sheet""="" sheetcount="" xmlns:ss="#unknown">");
excelDoc.Write("");
excelDoc.Write("<row>");
for (int x = 0; x < dt.Columns.Count; x++)
{
excelDoc.Write("<cell ss:styleid="\" boldcolumn\""="">");
excelDoc.Write(dt.Columns[x].ColumnName);
excelDoc.Write("");
}
excelDoc.Write("");
foreach (DataRow x in dt.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("<worksheet ss:name="\" sheet""="" sheetcount="" xmlns:ss="#unknown">");
excelDoc.Write("");
}
excelDoc.Write("<row>"); //ID=" + rowCount + "
for (int y = 0; y < dt.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("<cell ss:styleid="\" stringliteral\""="">" +
"");
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("<cell ss:styleid="\" dateliteral\""="">" +
"");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("");
break;
case "System.Boolean":
excelDoc.Write("<cell ss:styleid="\" stringliteral\""="">" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
excelDoc.Write("<cell ss:styleid="\" integer\""="">" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("<cell ss:styleid="\" decimal\""="">" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.DBNull":
excelDoc.Write("<cell ss:styleid="\" stringliteral\""="">" +
"");
excelDoc.Write("");
excelDoc.Write("");
break;
default:
throw (new Exception(rowType.ToString() + " not handled."));
}
}
excelDoc.Write("");
}
excelDoc.Write("");
excelDoc.Write(" ");
}
excelDoc.Write(endExcelXML);
excelDoc.Close();
}
|
|
|
|
|
first : thanks for useful method.
i have a datagridview in a from and i want to export the data of this datagridview to excel file ,
in this method,the input argument is DATASET and i want to change datagridbiew content to dataset to using ur method,how can i do that?
best regards.
|
|
|
|
|
what is the data grid view bound to?
|
|
|
|
|
Thanks for the code. It solves a lot of issues. I have one problem but it thinks it's an excel problem. When I try and open the files, after running the code, I get errors due to some of the year values being less than 1900.
Any ideas?
Thanks!
jeff
|
|
|
|
|
I have a temporary fix for this issue. If the date is less than 1900, I treat it as a string.
|
|
|
|
|
Do you mean that you face a problem when you put a year less then 1900, or you are just inserting time and you see 1900 is calculated with it automatically.
can you please give more details so we can help you about it.
|
|
|
|
|
Friends,
I'm having problem to export a DataSet using XML, because the client's Office is Version 2000.
When the file .xls is opened, only is show some tags of XML and don't show datas of DataSet.
How can I solve this problem?
Hugs,
Reginaldo Pereira
|
|
|
|
|
Helow,
Please help me?
I don't know how to I solve this?
Reginaldo
|
|
|
|
|