Solved

export from datagridview to excel problem with format

Posted on 2009-07-06
15
1,755 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Capture logon name 13 42
Add a range in an Excel graph 5 36
Manually enter date in datepicker 24 36
Filling Blank Cells 14 19
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

895 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

18 Experts available now in Live!

Get 1:1 Help Now