|
-
July 24th, 2012, 10:10 AM
#1
[RESOLVED] [VS201] Formatting Excel Export
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.
Code:
#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.
-
July 24th, 2012, 10:39 AM
#2
Re: [VS201] Formatting Excel Export
Where is the code that generates the column headers?
One observation: HeaderRow in
Code:
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?)
-
July 24th, 2012, 10:56 AM
#3
Re: [VS201] Formatting Excel Export
Sorry about that I have been moving code around trying to fix the situation and I sent the code without this:
Code:
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.
-
July 24th, 2012, 12:13 PM
#4
Re: [VS201] Formatting Excel Export
This is a guess:
PartSections is a class with PartNumber an integer. The line:
Code:
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:
Code:
oSheet.Cells(iMaxYCoordinate, PartSections.PartNumber).Value = "Part Number"
and then increment iMaxYCoordinate
-
July 24th, 2012, 12:47 PM
#5
Re: [VS201] Formatting Excel Export
I got it to work! Now I just need to clean it up. Thank you!
Code:
#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*************
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote