Solved

export from datagridview to excel problem with format

Posted on 2009-07-06
15
1,799 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
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
 

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

707 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