[ebolisa]

Hi,

I’m exporting datagridview data to Excel via dataset.
Col 1 of my datagridview contains dates and the grid shows the dates w/o time.
Once exported the dates to Excel, they are then formatted via

wSheet.Range("B:B").NumberFormat = "ddMM"

The above code works fine for dates having a single digit day. For days 10 and above, the dates are shown as dd/MM/yyyy 00:00 in the excel sheet.

Does anyone know why this is happening?

Thank you.
Excel 2007 and using Microsoft.Office.Interop.Excel

EDIT:
If days are single digit, month is swapped with day when writing to Excel from a dataset.

Attached image(s)

• 

This post has been edited by ebolisa: 16 March 2012 - 01:46 AM

[CharlieMay]

With that line of code I don't see a problem but I'm wondering if you're executing this line before or after you export. I have seen excel do weird things with the data that it receives regardless of how you set the cells up. So is this line being executed after excel is filled or before?

[ebolisa]

Before saving the file.
here's my code...

 'verfying the datagridview having data or not
        If ((Me.dgw.Columns.Count = 0) Or (Me.dgw.Rows.Count = 0)) Then
            Exit Sub
        End If

        'Start a new workbook in Excel.
        Dim Excel As Object = CreateObject("Excel.Application")
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
        'Dim range As Microsoft.Office.Interop.Excel.Range
        Dim outFileName As String _
        = Me.txbSave.Text.Trim & "\" & Me.txbNewFile.Text.Trim

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

        'Export from datagrid to new Excel sheet
        Try

            '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 Me.dgw.ColumnCount - 1
                dset.Tables(0).Columns.Add(Me.dgw.Columns(i).HeaderText)
            Next
            'add rows to the table
            Dim dr1 As DataRow
            For i As Integer = 0 To Me.dgw.RowCount - 1
                dr1 = dset.Tables(0).NewRow
                For j As Integer = 0 To Me.dgw.Columns.Count - 1
                    dr1(j) = Me.dgw.Rows(i).Cells(j).Value
                Next
                dset.Tables(0).Rows.Add(dr1)
            Next

            'Export dt
            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

        Catch ex As Exception
            MsgBox("Error de conversión.", MsgBoxStyle.Critical, "Error")
        End Try

        '============= FORMATTING

        Try
           
            'Format cell dimension
            wSheet.Columns("A:A").ColumnWidth = 6 'Asiento
            wSheet.Columns("B:B").ColumnWidth = 4 'Fecha
            wSheet.Columns("C:C").ColumnWidth = 12 'cuenta
            wSheet.Columns("D:D").ColumnWidth = 40 'Concepto
            wSheet.Columns("E:E").ColumnWidth = 14 'Importe
            wSheet.Columns("F:F").ColumnWidth = 14 'Haber
            wSheet.Columns("G:G").ColumnWidth = 12 'cuenta

            'formatting date
            wSheet.Range("B:B").NumberFormat = "ddMM"

            'delete row
            wSheet.Rows("1:1").delete()

            'alphabetize column 1
            wSheet.Columns.Sort(wSheet.Range("A3", "A10"), _
                             Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , , , , , _
                                  Microsoft.Office.Interop.Excel.XlYesNoGuess.xlGuess, , , _
                                  Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, , _
                                  Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal)
            wSheet.Columns.Sort(wSheet.Range("A3", "A10"), _
                                 Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , , _
                                 Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , _
                                 Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, , , _
                                 Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, _
                                 Microsoft.Office.Interop.Excel.XlSortMethod.xlStroke, _
                                 Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, _
                                 Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, _
                                 Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal)

            'Naming the worksheet.
            wSheet.Name = "Banco " & Me.txbAsiAlfa.Text.Trim
            _sheetname = wSheet.Name

        Catch ex As Exception
            MsgBox("Error de formato.", MsgBoxStyle.Critical, "Error")
        End Try

        'SAVING

        Try

            'Check if file exists if not, save file
            Dim button As DialogResult

            If File.Exists(outFileName) Then
                button = MessageBox.Show _
                    ("Ya existe un archivo llamado " & Me.txbNewFile.Text & ", ¿Quiere reemplazarlo?", _
                      "¡Ojo!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, _
                     MessageBoxDefaultButton.Button1)
                If button = System.Windows.Forms.DialogResult.Yes Then
                    Excel.DisplayAlerts = False
                    wBook.SaveAs(outFileName, True)
                    MsgBox("El archivo se ha guardado correctamente en " & Me.txbSave.Text)
                Else
                    Dim i As String
                    _sheetname = ""
                    _sheetname = "bancoBK.xls"
                    i = InputBox("Introduzca el nombre del archivo", "", _sheetname, 200, 200)
                    outFileName = Me.txbSave.Text.Trim & "\" & i
                    wBook.SaveAs(outFileName, True)
                    MsgBox("El archivo se ha guardado correctamente en " & Me.txbSave.Text)
                End If
            End If

        Catch ex As Exception
            MsgBox("Error a guardar el archivo. Revise la direccion", MsgBoxStyle.Exclamation, "Error")
            wSheet = Nothing
            wBook = Nothing
            Excel.Quit()
            Excel = Nothing
            GC.Collect()
            Exit Sub
        End Try

        Excel.Visible = True
        ReleaseObject(wSheet)
        ReleaseObject(wBook)
        ReleaseObject(Excel)
        GC.Collect()

EDIT:
I just noticed that the original problem is with the export part of the code where it’s inverting month and day as shown in the attached img.

Attached image(s)

• 

This post has been edited by ebolisa: 16 March 2012 - 12:13 AM

[CharlieMay]

OK, so you're formatting your column b to ddMM format. First of all,you should receive a result like 1402 so your data doesn't even look like it's getting updated

are you wanting day and then month or month and then day because right now it looks like it's displaying month and then day which is what I would expect from ddMM but again, ddMM should only display 4 numbers in that column. Meaning 03/16/2012 should display 1603 of course this is assuming a date format on your computer of mm/dd/yyyy

[ebolisa]

The end results should be ddmm, it’s a format needed for an accounting program.
The prior image shows the full date to show clearly the swapping between the month and the day. The image below shows the actual results.

Thank you.

Attached image(s)

• 

[CharlieMay]

So you're saying B1 is a date of Feb 01? Are you sure it's not Jan 2nd?

Are these fields populating your datagrid from different tables? Could one be a Date Type and one be a string type that for some reason can't be converted properly when placed in the spreadsheet?

[ebolisa]

The attached img is from my grid where the dates are shown w/o errors and clearly.
I import the data from an Excel sheet, then I manipulate it, adding columns and rows and send it back to a new worksheet. When I do the process manually, it works but when I pass it through the grid happens what happens. I could use the

=TEXT(A1,"ddmm") 

programmatically if I knew how because the end results do not need to be a date value.

Attached image(s)

• 

This post has been edited by ebolisa: 16 March 2012 - 12:16 PM

[CharlieMay]

OK, I think this is what is happening

1/2/2012 mm/dd/yyyy works but so does dd/mm/yyyy but when you get to anything above 12 it can't work both ways. In other words 10/13/2012 mm/dd/yyyy cannot be confused with 13/10/2012 because there are not 13 months. So it's more than likely a locale problem with the date format of the system.

Now, I've not looked into this but there is another method
wSheet.Range("B:B").NumberFormat = "ddMM" is what you have

have you looked at .NumberFormatLocal. Here is a link.

It states:
Gets or sets the format code for the NamedRange control in the language of the user.

EDIT:
Also, you might try moving that line up prior to filling so that excel knows the format of the cell when data is placed.

This post has been edited by CharlieMay: 16 March 2012 - 12:41 PM

[CharlieMay]

OK as far as your previous post on using =TEXT(A1, 'ddmm") I think in your range there is a .Formula where you can enter the formula for that range.

Something like:

wSheet.Range("B:B").Formula = "=Text("B:B", "ddmm")

[ebolisa]

I'm about to give up. I'll adj the dates manually.

wSheet.Range("B:B").Formula = "=Text(B:B, ddmm)"

The above code produces a circular error.

Thanks anyways.

[CharlieMay]

Yea, sorry, didn't catch that. But it would in each cell in excel so you would have to put that formula in a new cell and hide the old one just like you would do in excel. Just change the row:col information accordingly.

Now...
Give Up??? What's the fun in that. Apparently, you've not felt the pleasure of snapping your arm while patting yourself on the back when you finally reach the solution

Have you just thought of formatting this in .Net and passing it in the format you want to begin with instead of trying to let Excel understand everything?

Even if this is a problem with system date formats. You could (knowing the format you have) parse the information and rebuild it and add it. Or if it works with the mm/dd format in vb, use the datetime methods that it has which would make this easier to handle.

[ebolisa]

Well, basically I don’t understand how can the date format change within the column. Till I found an explication to my enigma, makes no sense to me to find a parch. Interesting enough, I don’t have this problem when using a DLL from Winnovative http://www.winnovati...l-library.aspx. This library does allow me to format the dates using the same process. If I find an answer to my problem, I will post it.
Thank you.

[CharlieMay]

Take a look at this:
scroll down to date literals

[ebolisa]

CharlieMay,

Thanks for the link. Found out that if I export the dates in a number format, and then convert them to ”ddmm”, the trick works… well, at least manually.
The line below doesn’t work, if you can help me out with it, I really appreciated it.

wSheet.Range("B:B").NumberFormat = "=DATAVALUE (wSheet.Range(2)[-6]"

Attached image(s)

• 

[softwareskill]

Try this to export dataset to excel in c# as well as vb: http://www.freedotne...el-spreadsheet/

Here's how you can Export Gridview to excel in c# as well as vb http://www.freedotne...el-spreadsheet/