4 Replies - 4682 Views - Last Post: 25 July 2012 - 01:58 AM Rate Topic: -----

#1 S-John  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 51
  • Joined: 13-July 12

[VS2010] Excel Export--Format Column as Currency

Posted 24 July 2012 - 11:27 AM

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*************



Is This A Good Question/Topic? 0
  • +

Replies To: [VS2010] Excel Export--Format Column as Currency

#2 Whazabazaa  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 24-July 12

Re: [VS2010] Excel Export--Format Column as Currency

Posted 24 July 2012 - 12:26 PM

Maybe this linkwill help.
Was This Post Helpful? 0
  • +
  • -

#3 S-John  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 51
  • Joined: 13-July 12

Re: [VS2010] Excel Export--Format Column as Currency

Posted 24 July 2012 - 12:30 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 S-John  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 51
  • Joined: 13-July 12

Re: [VS2010] Excel Export--Format Column as Currency

Posted 24 July 2012 - 01:13 PM

I got it now:
    oSheet.Range("F" & iMaxYCoordinate, "G" & iMaxYCoordinate).Cells.NumberFormat = "$0.00"


Was This Post Helpful? 1
  • +
  • -

#5 doc_guru  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-March 12

Re: [VS2010] Excel Export--Format Column as Currency

Posted 25 July 2012 - 01:58 AM

This Export Data to Excel on codeproject is truly helpful, except it is in C#
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1