Export a DataSet to Microsoft Excel without the use of COM objects

You must Sign in to use this message board.

First PrevNext

The XML directive and xmlns spacing both need this simple fix.

mkamoski21-Feb-19 4:49

mkamoski

21-Feb-19 4:49

...

All,

This is just FYI.

The XML directive and xmlns spacing both need this simple fix.

See code fix below, for the startExcelXML constant, etc.

<pre lang="c#">
const string startExcelXML = "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\r\n<Workbook " +
"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
" xmlns Blush | :O

=\"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 " +
"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
"<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
"\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
"\r\n <Protection/>\r\n </Style>\r\n " +
"<Style ss:ID=\"BoldColumn\">\r\n <Font " +
"x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
" ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"Decimal\">\r\n <NumberFormat " +
"ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
"ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
"ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
"</Styles>\r\n ";
</pre>

HTH.

Thanks.

-- Mark Kamoski

...

Excel will complain "The file format and extension of 'test.xls' don't match" here, but is still works.

mkamoski21-Feb-19 4:47

mkamoski

21-Feb-19 4:47

... FYI, When opening the resulting XML file, Excel will complain... The file format and extension of 'test.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway? ...but it still works. Just FYI. HTH. Thanks. -- Mark Kamoski ...
Sign in·View Thread

My vote of 5

BrianLaF3-Aug-12 10:32

BrianLaF

3-Aug-12 10:32

My vote of 5. Nice and Simple. Thanks.
Sign in·View Thread

How to define 1000 grouping symbol for decimal number

botngot835-Feb-12 23:03

botngot83

5-Feb-12 23:03

What should be defined for the xml schema to format 1000 grouping symbol decimal number. For examaple, the number 1.234,56 have the decimal symbol as ',' and 1000 grouping symbol as '.'. The sample code above just define the decimal symbol only. Thanks in advance and looking for any response.
Sign in·View Thread

Re: How to define 1000 grouping symbol for decimal number

botngot838-Mar-12 15:08

botngot83

8-Mar-12 15:08

Hello, is there anyone can help me this matter???? please!!!!
Sign in·View Thread

My vote of 5

SavindraSingh29-Jan-12 23:51

SavindraSingh

29-Jan-12 23:51

Very nicely done and very helpful post.
Sign in·View Thread

Microsoft Office Excel cannot open or save any more documents

squidder1411-Jan-12 4:44

squidder14

11-Jan-12 4:44

This works perfectly when I am testing in Visual Studio, however when I deploy to my production server (Win 2008 R2 Standard, ASP.NET 4.0) and click Open at the IE prompt I get the error message:

-------------------------------
Microsoft Office Excel cannot open or save any more documents because there is not enough available memory or disk space.

• To make more memory available, close workbooks or programs you no longer need.

• To free disk space, delete files you no longer need from the disk you are saving to.
-------------------------------

Any help would be greatly appreciated.

Many thanks
Paul

A simpler, and free, class to export from a DataSet into an Excel 2007 .xlsx file.

Michael Gledhill1-Dec-11 2:03

Michael Gledhill

1-Dec-11 2:03

Have a read of this:

It's a completely free library, lets you export a DataSet (or DataTable or List<>) into a "genuine" Excel 2007 .xlsx file.

http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

All of the source code is given, free of charge, aswell as a demo application.

It's very easy to add to your own applications, you just need to call one function, passing in an Excel filename, and your data source:

DataSet ds = CreateSampleData();
string excelFilename = "C:\\Sample.xlsx";
CreateExcelFile.CreateExcelDocument(ds, excelFilename);

It doesn't get much simpler than that....

My vote of 5

Rajesh Kumar Chekuri16-Nov-11 20:09

Rajesh Kumar Chekuri

16-Nov-11 20:09

nice....
Sign in·View Thread

Very nice code

Victorio A Pellicano7-Nov-11 8:31

Victorio A Pellicano

7-Nov-11 8:31

It should absolutely NOT be this hard to export from C# (native, paid-for, proprietary MS product) to Excel (native, paid-for, proprietary MS product). Don't they sell us on using this crap because it's "intergrated"??

In any case, it works great. The only thing that came up in my usage was that it doesn't have a case handler for Byte[] arrays. Thankfully, I don't care about that, so I just added this bit of code and it worked like a charm:

case "System.Byte[]":
    break;

*FIX* blank worksheet

aarharden30-Oct-11 11:14

aarharden

30-Oct-11 11:14

This piece "urn:schemas- microsoft-com:office" needs to be "urn:schemas-microsoft-com:office" Also remove the xml version tag at the top and it should works great.
Sign in·View Thread

My vote of 5

murat8938-Jul-11 2:34

murat893

8-Jul-11 2:34

Thank you very much
Sign in·View Thread

Just a quick note about your System.DateTime case....

jp2code27-Jun-11 6:07

jp2code

27-Jun-11 6:07

Your code above for `System.DateTime` is certainly not intuitive.

This "one liner" with accompanying explanation comment is faster and easier:

case "System.DateTime":
  excelDoc.Write(string.Format("<Cell ss:StyleID=\"DateLiteral\"><Data ss:Type=\"DateTime\">{0:s}</Data></Cell>", x[y]));
  // {0:s}={0:yyyy'-'MM'-'dd'T'HH':'mm':'ss} SortableDateTimePattern, culture independent
  break;

Reference: String Format for DateTime [C#][^]

~Joe
Avoid Sears Home Improvement!

My vote of 5

Arjun14521-Jun-11 23:46

Arjun145

21-Jun-11 23:46

Great article
Sign in·View Thread

Hello hello

skelmannen10-Nov-10 10:03

skelmannen

10-Nov-10 10:03

Sorry for my ingnorance here but I would like to connect this method with a button click event,,,how will I solve that? Kindly Simon
Sign in·View Thread

New VB version...

Jalapeno Bob5-Nov-10 6:00

Jalapeno Bob

5-Nov-10 6:00

This article was almost the answer to my prayers. Unfortunately, by company rules, I am bound to VB and all text files, including XML files, must be "properly formatted."

Here is my VB version of your code:

Module ExportToExcel

    Private Const startExcelXML As String = _
    "<xml version> " & vbCrLf & _
    "<Workbook  " & vbCrLf & _
    " xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" " & vbCrLf & _
    " xmlns:o=""urn:schemas-microsoft-com:office:office"" " & vbCrLf & _
    " xmlns:x=""urn:schemas-microsoft-com:office:excel"" " & vbCrLf & _
    " xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"" > " & vbCrLf

    Private Const stylesXML As String = _
    vbTab & "<Styles>" & vbCrLf & _
    vbTab & vbTab & "<Style ss:ID=""Default"" ss:Name=""Normal"">" & vbCrLf & _
    vbTab & vbTab & vbTab & "<Alignment ss:Vertical=""Bottom""/>" & vbCrLf & _
    vbTab & vbTab & vbTab & "<Borders/>" & vbCrLf & _
    vbTab & vbTab & vbTab & "<Font/>" & vbCrLf & _
    vbTab & vbTab & vbTab & "<Interior/>" & vbCrLf & _
    vbTab & vbTab & vbTab & "<NumberFormat/>" & vbCrLf & _
    vbTab & vbTab & vbTab & "<Protection/>" & vbCrLf & _
    vbTab & vbTab & "</Style>" & vbCrLf & _
    vbTab & vbTab & "<Style ss:ID=""BoldColumn"">" & vbCrLf & _
    vbTab & vbTab & vbTab & "<Font x:Family=""Swiss"" ss:Bold=""1""/>" & vbCrLf & _
    vbTab & vbTab & "</Style>" & vbCrLf & _
    vbTab & vbTab & "<Style ss:ID=""StringLiteral"">" & vbCrLf & _
    vbTab & vbTab & vbTab & "<NumberFormat ss:Format=""@""/>" & vbCrLf & _
    vbTab & vbTab & "</Style>" & vbCrLf & _
    vbTab & vbTab & "<Style ss:ID=""Decimal"">" & vbCrLf & _
    vbTab & vbTab & vbTab & "<NumberFormat ss:Format=""0.0000""/>" & vbCrLf & _
    vbTab & vbTab & "</Style>" & vbCrLf & _
    vbTab & vbTab & "<Style ss:ID=""Integer"">" & vbCrLf & _
    vbTab & vbTab & vbTab & "<NumberFormat ss:Format=""0""/>" & vbCrLf & _
    vbTab & vbTab & "</Style>" & vbCrLf & _
    vbTab & vbTab & "<Style ss:ID=""DateLiteral"">" & vbCrLf & _
    vbTab & vbTab & vbTab & "<NumberFormat ss:Format=""mm/dd/yyyy;@""/>" & vbCrLf & _
    vbTab & vbTab & "</Style>" & vbCrLf & _
    vbTab & vbTab & "<Style ss:ID=""DateSpecial"">" & vbCrLf & _
    vbTab & vbTab & vbTab & "<NumberFormat ss:Format=""yyyy-MM""/>" & vbCrLf & _
    vbTab & vbTab & "</Style>" & vbCrLf & _
    vbTab & "</Styles>" & vbCrLf

    Private Const endExcelXML As String = "</Workbook>" & vbCrLf

    Public Sub ExportToExcel(ByVal source As DataSet, ByVal fileName As String)

        Dim excelDoc As System.IO.StreamWriter

        excelDoc = New System.IO.StreamWriter(fileName)

        Dim rowCount As Integer = 0
        Dim sheetCount As Integer = 1
        '     /*
        '    <xml version>
        '    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        'xmlns:  o = "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(stylesXML)
        excelDoc.WriteLine(vbTab & "<Worksheet ss:Name=""Sheet" + _
            sheetCount.ToString + """>")
        excelDoc.WriteLine(vbTab & vbTab & "<Table>")

        excelDoc.WriteLine(vbTab & vbTab & vbTab & "<Row>")
        For x As Integer = 0 To source.Tables(0).Columns.Count - 1
            excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & _
                "<Cell ss:StyleID=""BoldColumn"">")
            excelDoc.Write(vbTab & vbTab & vbTab & vbTab & vbTab)
            excelDoc.Write("<Data ss:Type=""String"">")
            excelDoc.Write(source.Tables(0).Columns(x).ColumnName)
            excelDoc.Write("</Data>" & vbCrLf)
            excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & "</Cell>")
        Next x
        excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & "</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.ToString + """>")
                excelDoc.Write("<Table>")
            End If

            excelDoc.WriteLine(vbTab & vbTab & vbTab & "<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.WriteLine(vbTab & vbTab & vbTab & vbTab & _
                            "<Cell ss:StyleID=""StringLiteral"">")
                        excelDoc.Write(vbTab & vbTab & vbTab & vbTab & vbTab)
                        excelDoc.Write("<Data ss:Type=""String"">")
                        excelDoc.Write(XMLstring)

                    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 = CDate(x(y))
                        Dim XMLDatetoString As String = "" ' Excel Converted Date

                        XMLDatetoString = XMLDate.Year.ToString() & _
                             "-" & _
                             IIf(XMLDate.Month < 10, _
                               "0" & XMLDate.Month.ToString(), _
                               XMLDate.Month.ToString()).ToString & _
                             "-" & _
                             IIf(XMLDate.Day < 10, _
                               "0" & XMLDate.Day.ToString(), _
                               XMLDate.Day.ToString()).ToString & _
                             "T" & _
                             IIf(XMLDate.Hour < 10, _
                               "0" & XMLDate.Hour.ToString(), _
                               XMLDate.Hour.ToString()).ToString & _
                              ":" & _
                              IIf(XMLDate.Minute < 10, _
                                "0" & XMLDate.Minute.ToString(), _
                                XMLDate.Minute.ToString()).ToString & _
                              ":" & _
                              IIf(XMLDate.Second < 10, _
                                "0" & XMLDate.Second.ToString(), _
                                XMLDate.Second.ToString()).ToString & _
                              ".000"
                        excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & _
                            "<Cell ss:StyleID=""DateSpecial"">")
                        excelDoc.Write(vbTab & vbTab & vbTab & vbTab & vbTab)
                        excelDoc.Write("<Data ss:Type=""DateTime"">")
                        excelDoc.Write(XMLDatetoString)

                    Case "System.Boolean"
                        excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & _
                            "<Cell ss:StyleID=""StringLiteral"">")
                        excelDoc.Write(vbTab & vbTab & vbTab & vbTab & vbTab)
                        excelDoc.Write("<Data ss:Type=""String"">")
                        excelDoc.Write(x(y).ToString())

                    Case "System.Int16", "System.Int32", "System.Int64", _
                            "System.Byte"
                        excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & _
                            "<Cell ss:StyleID=""Integer"">")
                        excelDoc.Write(vbTab & vbTab & vbTab & vbTab & vbTab)
                        excelDoc.Write("<Data ss:Type=""Number"">")
                        excelDoc.Write(x(y).ToString())

                    Case "System.Decimal", "System.Double"
                        excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & _
                            "<Cell ss:StyleID=""Decimal"">")
                        excelDoc.Write(vbTab & vbTab & vbTab & vbTab & vbTab)
                        excelDoc.Write("<Data ss:Type=""Number"">")
                        excelDoc.Write(x(y).ToString())

                    Case "System.DBNull"
                        excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & _
                            "<Cell ss:StyleID=""StringLiteral"">")
                        excelDoc.Write(vbTab & vbTab & vbTab & vbTab & vbTab)
                        excelDoc.Write("<Data ss:Type=""String"">")
                        excelDoc.Write("")

                    Case Else
                        Throw (New Exception(rowType.ToString() + " not handled."))

                End Select
                excelDoc.Write("</Data>" & vbCrLf)
                excelDoc.WriteLine(vbTab & vbTab & vbTab & vbTab & "</Cell>")
            Next y
            excelDoc.WriteLine(vbTab & vbTab & vbTab & "</Row>")
        Next x

        excelDoc.WriteLine(vbTab & vbTab & "</Table>")
        excelDoc.WriteLine(vbTab & "</Worksheet>")
        excelDoc.Write(endExcelXML)

        excelDoc.Close()
    End Sub     ' ExportToExcel

End Module  ' ExportToExcel

Would this approach create a lot of xls files on the web server?

eric.yu.cn12-Oct-10 23:28

eric.yu.cn

12-Oct-10 23:28

When applying this approach on an ASP.Net web page, to support concurrent operations, it is better to export to a new excel file each time. However, after the user opened and viewed this xls file, the excel file will stay on the server. Is it possible to avoid saving xls files on the web server, or delete them after requests end?
Sign in·View Thread

Giving error in office 2007. Please help.

raghav196-Sep-10 1:46

raghav19

6-Sep-10 1:46

Hi all, When i am trying to open excel in office 2007, it is giving XML parser error. But it is working fine in office 2003. I am not able to find the root cause of this problem. Any help regarding this, will be really appreciated. Thanks in advance. Kind regards, Raghav
Sign in·View Thread

Excel created as blank

Peeter1231-Sep-10 19:14

Peeter123

1-Sep-10 19:14

The excel shows up as blank when opened when i am using response.contenttype. Actually i want the user to have open/save option. Any help on this will be highly appreciated. Thanks in Advance
Sign in·View Thread

good article~~~ ^^

Metalzang11-Aug-10 14:41

Metalzang

11-Aug-10 14:41

thanks... Xodiak
Sign in·View Thread

[question] Re: good article~~~ ^^

Metalzang11-Aug-10 15:43

Metalzang

11-Aug-10 15:43

I can't speak English well. Please note that... how to convert tag from dataset to excel sheet like a excel ALT+Enter function key ?
Sign in·View Thread

Very useful

PoojaGRD10-Aug-10 23:52

PoojaGRD

10-Aug-10 23:52


Sign in·View Thread

Schema source question

Consulting Mechanic20-May-10 11:55

Consulting Mechanic

20-May-10 11:55

Just a quick question, where did you find the information regarding the xml schema information? I see some of the items I want to modify but need the source so I do not cause any other problems. Thanks
Sign in·View Thread

Re: Schema source question

Xodiak20-May-10 12:07

Xodiak

20-May-10 12:07

1. Open excel 2. Preform any formatting you want to do to an excel cell 3. File -> Save As xml document 4. Open in notepad to see formatting styles that were exported with the XML document.
Sign in·View Thread

Exporting Dataset to Excel 2007 with Hyperlink in vb.net or C#.net

MohanMbnr29-Mar-10 1:00

MohanMbnr

29-Mar-10 1:00

Hello sir, I am developing a application in vb.net sir. My requirement is Export dataset(More than 1 lakh records)to Excel 2007 with hyperllink in one column. Thanx Mohan Goud
Sign in·View Thread

Last Visit: 31-Dec-99 18:00 Last Update: 20-Jul-24 14:19

Refresh

12 3 4 5 6 7 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Comments and Discussions