Solved

export from datagridview to excel problem with format

Posted on 2009-07-06
15
1,747 Views
Last Modified: 2013-11-27
Hi,

I'm exporting from datagridview to excel using XML format and I have one problem all data are display as text in excel.

Can somebody help mi with that I try to change format to number, integer but then I got error and excel couldn't open table
Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName As String)
 

        ' Open the file and write the headers

        Dim fs As New IO.StreamWriter(fileName, False)

        fs.WriteLine("<?xml version=""1.0""?>")

        fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")

        fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
 

        ' Create the styles for the worksheet

        fs.WriteLine("  <ss:Styles>")

        ' Style for the column headers

        fs.WriteLine("    <ss:Style ss:ID=""1"">")

        fs.WriteLine("      <ss:Font ss:Bold=""1""/>")

        fs.WriteLine("      <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _

            "ss:WrapText=""1""/>")

        fs.WriteLine("      <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")

        fs.WriteLine("    </ss:Style>")

        ' Style for the column information

        fs.WriteLine("    <ss:Style ss:ID=""2"">")

        fs.WriteLine("      <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")

        fs.WriteLine("    </ss:Style>")

        fs.WriteLine("  </ss:Styles>")
 

        ' Write the worksheet contents

        fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")

        fs.WriteLine("  <ss:Table>")

        For i As Integer = 0 To grdView.Columns.Count - 1

            fs.WriteLine(String.Format("    <ss:Column ss:Width=""{0}""/>", _

            grdView.Columns.Item(i).Width))

        Next

        fs.WriteLine("    <ss:Row>")

        For i As Integer = 0 To grdView.Columns.Count - 1

            fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""1"">" & _

                "<ss:Data ss:Type=""double"">{0}</ss:Data></ss:Cell>", _

                grdView.Columns.Item(i).HeaderText))

        Next

        fs.WriteLine("    </ss:Row>")
 

        ' Check for an empty row at the end due to Adding allowed on the DataGridView

        Dim subtractBy As Integer, cellText As String

        If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1

        ' Write contents for each cell

        For i As Integer = 0 To grdView.RowCount - subtractBy

            fs.WriteLine(String.Format("    <ss:Row ss:Height=""{0}"">", _

                grdView.Rows(i).Height))

            For intCol As Integer = 0 To grdView.Columns.Count - 1

                cellText = grdView.Item(intCol, i).Value

                ' Check for null cell and change it to empty to avoid error

                If cellText = vbNullString Then cellText = ""

                fs.WriteLine(String.Format("      <ss:Cell ss:StyleID=""2"">" & _

                    "<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _

                    cellText.ToString))

            Next

            fs.WriteLine("    </ss:Row>")

        Next
 

        ' Close up the document

        fs.WriteLine("  </ss:Table>")

        fs.WriteLine("</ss:Worksheet>")

        fs.WriteLine("</ss:Workbook>")

        fs.Close()
 

        'Open the file in Microsoft Excel

        '10 = SW_SHOWDEFAULT

        ShellEx(Me.Handle, "Open", filepath, "", "", 10)

    End Sub

Open in new window

0
Comment
Question by:gowerman
  • 7
  • 6
15 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24790477
When writing OpenXML, I find out how Excel (or Word) writes the styles, by saving a test workbook in XML format.
0
 

Author Comment

by:gowerman
ID: 24791806
yes but how to do that
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24793796
It would be good to know what version of Excel you are working with, because I have 2007, and the XML schemas are different.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24793812
Also in Excel 2007, that would File | Save As | Other Formats | Excel Workbook (.xlsx), and then open with a text editor (like Notepad).
0
 

Author Comment

by:gowerman
ID: 24802323
I work with excel 2003
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24803411
Here is an XML 2003 example, showing how to format a cell as currency:

Base Style ID = s16

  <Style ss:ID="s16" ss:Name="Currency">
   <NumberFormat
    ss:Format="_(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;* \(#,##0.00\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)"/>
  </Style>
 
Inheriting style ID = s17

  <Style ss:ID="s17" ss:Parent="s16">
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
  </Style>

Table with 1 row and 1 cell, with s17 style applied:

  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row>
    <Cell ss:StyleID="s17"><Data ss:Type="Number">78.900000000000006</Data></Cell>
   </Row>
  </Table>

Also, note that the Data.Type = "Number".

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<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"

 xmlns:html="http://www.w3.org/TR/REC-html40">

 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

  <Author>Robert Learned</Author>

  <LastAuthor>Robert Learned</LastAuthor>

  <Created>2009-07-08T13:16:29Z</Created>

  <LastSaved>2009-07-08T13:17:11Z</LastSaved>

  <Company>Assurant</Company>

  <Version>12.00</Version>

 </DocumentProperties>

 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">

  <DownloadComponents/>

 </OfficeDocumentSettings>

 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

  <WindowHeight>11445</WindowHeight>

  <WindowWidth>18975</WindowWidth>

  <WindowTopX>120</WindowTopX>

  <WindowTopY>45</WindowTopY>

  <ProtectStructure>False</ProtectStructure>

  <ProtectWindows>False</ProtectWindows>

 </ExcelWorkbook>

 <Styles>

  <Style ss:ID="Default" ss:Name="Normal">

   <Alignment ss:Vertical="Bottom"/>

   <Borders/>

   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>

   <Interior/>

   <NumberFormat/>

   <Protection/>

  </Style>

  <Style ss:ID="s16" ss:Name="Currency">

   <NumberFormat

    ss:Format="_(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;* \(#,##0.00\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)"/>

  </Style>

  <Style ss:ID="s17" ss:Parent="s16">

   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>

  </Style>

 </Styles>

 <Worksheet ss:Name="Sheet1">

  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"

   x:FullRows="1" ss:DefaultRowHeight="15">

   <Row>

    <Cell ss:StyleID="s17"><Data ss:Type="Number">78.900000000000006</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <PageSetup>

    <Header x:Margin="0.3"/>

    <Footer x:Margin="0.3"/>

    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>

   </PageSetup>

   <Selected/>

   <ProtectObjects>False</ProtectObjects>

   <ProtectScenarios>False</ProtectScenarios>

  </WorksheetOptions>

 </Worksheet>

</Workbook>

Open in new window

0
 
LVL 41

Expert Comment

by:graye
ID: 24806574
I've got a class that takes a DataTable (from any source) and converts it into an XLS workbook/worksheet.   It uses "office automation" to directly create an XLS object on the client PC... so it's not using the XML technique as in your examples above.   This would allow you to take direct control over the formatting of the data.
Let me know if you'd like me to post the VB.Net source code (it's about 300 lines)
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:gowerman
ID: 24813024
no thanks,
I found http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/a329ac53-a571-4020-946a-158c02c1bd01

the guy there said he change intCol and it works but I don't know were he change that
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24813441
I know that you can apply a numeric style to the column.

 <Style ss:ID="s62">
   <NumberFormat ss:Format="Fixed"/>
  </Style>

...

  <Column ss:StyleID="s62" ss:Width="50.25"/>
 
0
 

Author Comment

by:gowerman
ID: 24814280
can you put that code in my, Im not very familiar with XML and have no idea where to put that.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24815925
You write a style here:

       fs.WriteLine("    <ss:Style ss:ID=""2"">")
        fs.WriteLine("      <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
        fs.WriteLine("    </ss:Style>")
        fs.WriteLine("  </ss:Styles>")

and the columns here:

        For i As Integer = 0 To grdView.Columns.Count - 1
            fs.WriteLine(String.Format("    <ss:Column ss:Width=""{0}""/>", _
            grdView.Columns.Item(i).Width))
        Next

You would need to determine if the column needs to be a number format, and then write the additional ss:StyleID attribute.
0
 

Author Comment

by:gowerman
ID: 24820937
I have this lines of code all ready but still I get only text, as I mention et I'm zero with XML so please step bye step
Thanks
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 24840498
1) Use the same code that you use to write Style.ID = "2" to write Style.ID = "s62".  Just copy and paste this block:

        fs.WriteLine("    <ss:Style ss:ID=""2"">")
        fs.WriteLine("      <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
        fs.WriteLine("    </ss:Style>")
        fs.WriteLine("  </ss:Styles>")

     and change the values to what you need.

2) Figure out how you will determine that a column needs to be a numeric column.  Use an If...Then block to write normally:

            fs.WriteLine(String.Format("    <ss:Column ss:Width=""{0}""/>", _
            grdView.Columns.Item(i).Width))

   or with the numeric style

          <Column ss:StyleID="s62" ss:Width="50.25"/>

 
0
 

Author Closing Comment

by:gowerman
ID: 31600088
close to solve my problem
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now