[S-John]

Hi folks.

I am exporting data to an excel workbook and I am having difficulties formatting Prices (originally set as doubles)in a column as currency. ($####.##)

#Region "Exports to Excel"

    Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click

        Dim dialog As DialogResult = MessageBox.Show("Are you sure you want to generate the excel?", "Delete confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

        If dialog = DialogResult.No Then
            'Do not generate Excel
        Else
            'Generate the Excel
            Dim oExcelApp As Excel.Application
            Dim oWkBk As Excel.Workbook
            Dim oSheet As Excel.Worksheet

            Dim oRnge As Excel.Range

            Dim iMaxXCoordinate As Integer = 0
            Dim iMaxYCoordinate As Integer = 0

            ' Start Excel and get Application object.
            oExcelApp = CreateObject("Excel.Application")
            oExcelApp.Visible = True

            ' Get a new workbook.
            oWkBk = oExcelApp.Workbooks.Add
            oSheet = oWkBk.ActiveSheet

            '******************HEADER SECTION**********************************

            'If motors controllers exist, add that section to excel
            If lstMotorController.Count > 0 Then
                iMaxYCoordinate = 2
                oSheet.Range("A" & iMaxYCoordinate, "I" & iMaxYCoordinate).Merge()
                oSheet.Cells(HeaderRow, 1).value = "MOTOR PARTS"
                With oSheet.Range("A" & iMaxYCoordinate, "I" & iMaxYCoordinate)
                    .Font.Bold = True
                    .Font.Size = "12"
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .Interior.ColorIndex = 44
                End With
            End If

            iMaxYCoordinate = 3
            '*************EXPORT MOTOR PARTS TO EXCEL*************
            oSheet.Cells(iMaxYCoordinate, PartSections.PartNumber).Value = "Part Number"
            oSheet.Cells(iMaxYCoordinate, PartSections.Manufacturer).Value = "Manufacturer"
            oSheet.Cells(iMaxYCoordinate, PartSections.Description).Value = "Description"
            oSheet.Cells(iMaxYCoordinate, PartSections.Vendor).Value = "Vendor"
            oSheet.Cells(iMaxYCoordinate, PartSections.Quantity).value = "Quantity"
            oSheet.Cells(iMaxYCoordinate, PartSections.Price).value = "Price"
            oSheet.Cells(iMaxYCoordinate, PartSections.ExtPrice).value = "Ext Price"
            oSheet.Cells(iMaxYCoordinate, PartSections.Status).value = "Status"
            oSheet.Cells(iMaxYCoordinate, PartSections.InSock).value = "In Stock"

            With oSheet.Range("A" & iMaxYCoordinate, "I" & iMaxYCoordinate)
                .Font.Bold = True
                .Font.Size = "12"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .Borders.Color = Color.Black
                .Interior.ColorIndex = 44
            End With

            'Add each motor to the excel
            For iCount As Integer = 0 To lstMotorController.Count - 1
                iMaxYCoordinate = 4
                oSheet.Cells(iMaxYCoordinate, PartSections.PartNumber) = lstMotorController.Item(iCount).MotorControllerPartNumber
                oSheet.Cells(iMaxYCoordinate, PartSections.Manufacturer) = lstMotorController.Item(iCount).MotorControllerManufacturer
                oSheet.Cells(iMaxYCoordinate, PartSections.Description) = lstMotorController.Item(iCount).MotorControllerDescription
                oSheet.Cells(iMaxYCoordinate, PartSections.Vendor) = lstMotorController.Item(iCount).MotorControllerVendor
                oSheet.Cells(iMaxYCoordinate, PartSections.Quantity) = lstMotorController.Item(iCount).MotorControllerQuantity
                oSheet.Cells(iMaxYCoordinate, PartSections.Price) = lstMotorController.Item(iCount).MotorControllerPrice
                oSheet.Cells(iMaxYCoordinate, PartSections.ExtPrice) = "=E" & iMaxYCoordinate & "*F" & iMaxYCoordinate
            Next

            With oSheet.Range("A" & iMaxYCoordinate, "G" & iMaxYCoordinate)

            End With
            '*****************END EXPORT MOTOR CONTROLLERS TO EXCEL*************

[Whazabazaa]

Maybe this linkwill help.

[S-John]

I think I have to format the range of cells and dimension the range but I am not sure on how to go about doing this. I have tried a few different ways that I have found online but non of them work or seem completely relevant.

[S-John]

I got it now:

    oSheet.Range("F" & iMaxYCoordinate, "G" & iMaxYCoordinate).Cells.NumberFormat = "$0.00"

[doc_guru]

This Export Data to Excel on codeproject is truly helpful, except it is in C#