[RESOLVED] [VS201] Formatting Excel Export

[S_John]

I am having difficulties trying to format my excel export. The header (Motor Parts) is displayed correctly but the column headers are displaying above the motor Parts header. I think it is an issue with iMaxYCoordinate but I can't see a problem.

#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


            '*************EXPORT MOTOR PARTS TO EXCEL*************
            '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 = 35
                End With
            End If
      

            ' Format A3:I3 as bold, vertical alignment = center.
            With oSheet.Range("A" & iMaxYCoordinate, "I" & iMaxYCoordinate)
                .Font.Bold = True
                .Font.Size = "12"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .Borders.Color = Color.Black
                .Interior.ColorIndex = 35
            End With

            'Add each motor to the excel
            For iCount As Integer = 0 To lstMotorController.Count - 1
                iMaxYCoordinate = iMaxYCoordinate + 1
                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*************

            'Add each motor to the excel
            For iCount As Integer = 0 To lstMotorDisconnect.Count - 1
                iMaxYCoordinate = iMaxYCoordinate + 1
                oSheet.Cells(iMaxYCoordinate, PartSections.PartNumber) = lstMotorDisconnect.Item(iCount).MotorDisconnectPartNumber
                oSheet.Cells(iMaxYCoordinate, PartSections.Manufacturer) = lstMotorDisconnect.Item(iCount).MotorDisconnectManufacturer
                oSheet.Cells(iMaxYCoordinate, PartSections.Description) = lstMotorDisconnect.Item(iCount).MotorDisconnectDescription
                oSheet.Cells(iMaxYCoordinate, PartSections.Vendor) = lstMotorDisconnect.Item(iCount).MotorDisconnectVendor
                oSheet.Cells(iMaxYCoordinate, PartSections.Quantity) = lstMotorDisconnect.Item(iCount).MotorDisconnectQuantity
                oSheet.Cells(iMaxYCoordinate, PartSections.Price) = lstMotorDisconnect.Item(iCount).MotorDisconnectPrice
                oSheet.Cells(iMaxYCoordinate, PartSections.ExtPrice) = "=E" & iMaxYCoordinate & "*F" & iMaxYCoordinate
            Next

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

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


            'Add each motor to the excel
            For iCount As Integer = 0 To lstProtection.Count - 1
                iMaxYCoordinate = iMaxYCoordinate + 1
                oSheet.Cells(iMaxYCoordinate, PartSections.PartNumber) = lstProtection.Item(iCount).ProtectionPartNumber
                oSheet.Cells(iMaxYCoordinate, PartSections.Manufacturer) = lstProtection.Item(iCount).ProtectionManufacturer
                oSheet.Cells(iMaxYCoordinate, PartSections.Description) = lstProtection.Item(iCount).ProtectionDescription
                oSheet.Cells(iMaxYCoordinate, PartSections.Vendor) = lstProtection.Item(iCount).ProtectionVendor
                oSheet.Cells(iMaxYCoordinate, PartSections.Quantity) = lstProtection.Item(iCount).ProtectionQuantity
                oSheet.Cells(iMaxYCoordinate, PartSections.Price) = lstProtection.Item(iCount).ProtectionPrice
                oSheet.Cells(iMaxYCoordinate, PartSections.ExtPrice) = "=E" & iMaxYCoordinate & "*F" & iMaxYCoordinate
            Next

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

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

Thanks in advance for any help.

[Mur16]

Where is the code that generates the column headers?

One observation: HeaderRow in

oSheet.Cells(HeaderRow, 1).value = "MOTOR PARTS"

is not assigned a value and not in quotes so it is zero. (maybe it's a const?)

[S_John]

Sorry about that I have been moving code around trying to fix the situation and I sent the code without this:

  
 oSheet.Cells(PartSections.PartNumber).Value = "Part Number"
            oSheet.Cells(PartSections.Manufacturer).Value = "Manufacturer"
            oSheet.Cells(PartSections.Description).Value = "Description"
            oSheet.Cells(PartSections.Vendor).Value = "Vendor"
            oSheet.Cells(PartSections.Quantity).value = "Quantity"
            oSheet.Cells(PartSections.Price).value = "Price"
            oSheet.Cells(PartSections.ExtPrice).value = "Ext Price"
            oSheet.Cells(PartSections.Status).value = "Status"
            oSheet.Cells(PartSections.InSock).value = "In Stock"

They were named as HeaderRows but conflicted with the Motor Parts Header row.

[Mur16]

This is a guess:
PartSections is a class with PartNumber an integer. The line:

oSheet.Cells(PartSections.PartNumber).Value = "Part Number"

is missing a parameter. Cells requires two. My guess is that it is using row zero for the missing parameter. It maybe should read:

oSheet.Cells(iMaxYCoordinate, PartSections.PartNumber).Value = "Part Number"

and then increment iMaxYCoordinate

[S_John]

I got it to work! Now I just need to clean it up. Thank you!

#Region "Exports to Excel"
    ''' <summary>
    ''' Exports to excel
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    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**********************************

            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 = 35
            End With

            '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 = 35
                End With
            End If

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