Problems with conversion datagrid to excel

batserra

Member
Joined
Oct 6, 2008
Messages
6
Programming Experience
Beginner
Hi !

I have a problem with conversion from a grid to excel ,i hope that someone con help me...

El grid has:

0.733333 120.000 20007.047 9365.221 170.263 9305.221 170.219 9425.221 170.667 0.090 0.600
6.731220 50.000 9292.373 9562.563 171.592 9537.563 171.423 9587.563 171.894 0.034 0.538
12.111977 62.627 1250.000 9661.794 172.793 9630.481 172.414 9693.108 171.604 0.392 -5.010

Excel show me:

0,733333 120 20007,047 9365,221 170,263 9305,221 170,219 9425,221 170,667 0,09 0,6
6,73122 50 9292,373 9562,563 171,592 9537,563 171,423 9587,563 171,894 0,034 0,538
12,111977 62,627 1250 9661,794 172,793 9630,481 172,414 9693,108 171,604 0,392 -5,01

How you se ,there is a error for example with second columd, that show a wrong nomber comversion
First colum ,it's ok ,but second have to show 120.000 not 120

My code take from another page:

----------------------------------------

If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If

'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To DataGridView1.Columns.Count - 1
dr1(j) = DataGridView1.Rows(i).Cells(j).Value
Next
dset.Tables(0).Rows.Add(dr1) If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If

'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To DataGridView1.Columns.Count - 1
dr1(j) = DataGridView1.Rows(i).Cells(j).Value
Next
dset.Tables(0).Rows.Add(dr1)
Next

' Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

wBook = excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()

Dim dt As System.Data.DataTable = dset.Tables(0)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0

For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next

For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

Next
Next

wSheet.Columns.AutoFit()

Dim saveFileDialog1 As New SaveFileDialog()
saveFileDialog1.Filter = "Archivo Xls |*.xls"
saveFileDialog1.Title = "Grabar como archivo Excel"
saveFileDialog1.ShowDialog()

' If the file name is not an empty string open it for saving.
If saveFileDialog1.FileName <> "" Then
Dim strFileName As String ' = "C:\Users\llserra\Documents\testexcel2.xls"
strFileName = saveFileDialog1.FileName
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try

If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If

wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True
End If

End Sub

------------------

How i can fit it?

Thanks a lot for all
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,376
Location
Sydney, Australia
Programming Experience
10+
That's not an error. That's how Excel works. If the data is a number then it's not going to arbitrarily show leading or trailing zeroes. If you want to do that then you must either use a string rather than a number or else format the cell(s).
 

batserra

Member
Joined
Oct 6, 2008
Messages
6
Programming Experience
Beginner
That's not an error. That's how Excel works. If the data is a number then it's not going to arbitrarily show leading or trailing zeroes. If you want to do that then you must either use a string rather than a number or else format the cell(s).


thanks for all ,you are right.. :)
 
Top Bottom