export from datagridview to excel problem with format

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

gowermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
When writing OpenXML, I find out how Excel (or Word) writes the styles, by saving a test workbook in XML format.
0
gowermanAuthor Commented:
yes but how to do that
0
Bob LearnedCommented:
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
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Bob LearnedCommented:
Also in Excel 2007, that would File | Save As | Other Formats | Excel Workbook (.xlsx), and then open with a text editor (like Notepad).
0
gowermanAuthor Commented:
I work with excel 2003
0
Bob LearnedCommented:
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
grayeCommented:
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
gowermanAuthor Commented:
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
Bob LearnedCommented:
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
gowermanAuthor Commented:
can you put that code in my, Im not very familiar with XML and have no idea where to put that.
0
Bob LearnedCommented:
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
gowermanAuthor Commented:
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
Bob LearnedCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowermanAuthor Commented:
close to solve my problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.