J
Joanie Brows
Guest
Okay.. so, I have this program that Ive been working on for a few weeks and thought I finally had it finished. I was testing it out with multiple files when I came across a file that had N/A in a field that should only consist of integers. Im writing out an Excel file, so in this instance, I need to write out "N/A" to the appropriate field. Simple enough, so I thought... but I was wrong.
Problem is, I have a custom class set up to hold the values of each field being written out and the variables are declared as either an integer or double. As I perform multiple calculations, I save the values in this class and then save them into a list. At the end of the program I write out the list to an Excel file.
Ive been trying different approaches all morning but havent been able to figure out how to fix this. I changed the custom class to all string variables (im just using the class to hold the values so I can write to the list, no calculations are being performed) but when I write it out, I loose leading zeros where there used to be and need to be. For example, where I used to have 0.08 is now .08. Thats not okay. I tried formatting the output, but Im doing it wrong, I started getting "###.000" written out.
Anyway, heres a condensed version of my code. Im going to just put in here what I think is relevant to my problem. If anyone has any ideas on what I need to do to fix this, without a complete rewrite (that would KILL me! lol) Id love to hear them. I know its a lot of code, but believe me, theres a lot more in there that I cut out. I just felt this was the minimum I needed to show here so you could get an idea of what I have going on. Most of the calculations are repetitive, but I wanted you to see what variables I was using for them, and also how I have them declared.
TIA!!
Joanie
Private Class dataOutput
this class is used to store all data for output file at the end of the program
Public category As String = "--"
Public prodDesc As String = "--"
Public code As Integer = 0
Public clCount As Integer = 0
Public clPer As Double = 0
Public bsCount As Integer = 0
Public bsPer As Double = 0
Public perPen As Double = 0
Public uIndex As Double = 0
Public wCL_Count As Integer = 0
Public wCL_Per As Double = 0
Public wBS_Count As Integer = 0
Public wBS_Per As Double = 0
Public wPer_Pen As Double = 0
Public wIndex As Double = 0
End Class
********* Next section that used these variables
Dim wBsCount As Integer = H3
Dim bsCount As Integer = H7
Dim wClCount As Integer = H10
Dim clCount As Integer = H14
Dim clPer As String = "--"
If H14 = 0 OrElse G14 = 0 Then
clPer = "0"
Else
Dim XclPer As Double = (H14 / G14)
clPer = XclPer.ToString("###.000")
End If
Dim bsPer As String = "--"
If H7 = 0 OrElse G7 = 0 Then
bsPer = "0"
Else
Dim XbsPer As Double = (H7 / G7)
bsPer = XbsPer.ToString("###.000")
End If
Dim perPen As String = "--"
If H14 = 0 OrElse H7 = 0 Then
perPen = "0"
Else
Dim XperPen As Double = (H14 / H7)
perPen = XperPen.ToString("###.000")
End If
Dim uIndex As String = "--"
If H14 = 0 OrElse G14 = 0 OrElse H7 = 0 OrElse G7 = 0 Then
uIndex = "0"
Else
Dim XuIndex As Double = ((H14 / G14) / (H7 / G7) * 100)
uIndex = XuIndex.ToString("###.000")
End If
Dim wClPer As String = "--"
If H13 = 0 Then
wClPer = "0"
Else
Dim XwClPer As Double = (H13 / 100)
wClPer = XwClPer.ToString("###.000")
End If
Dim wBsPer As String = "--"
If H6 = 0 Then
wBsPer = "0"
Else
Dim XwBsPer As Double = (H6 / 100)
wBsPer = XwBsPer.ToString("###.000")
End If
Dim wPerPen As String = "--"
If H11 = 0 Then
wPerPen = "0"
Else
Dim XwPerPen As Double = (H11 / 100)
wPerPen = XwPerPen.ToString("###.000")
End If
Dim wIndex As String = "--"
If H13 = 0 OrElse H6 = 0 Then
wIndex = "0"
Else
Dim XwIndex As Double = (H13 / 100) / (H6 / 100) * 100
wIndex = XwIndex.ToString("###.000")
End If
******* Skipping to next section again
Dim thisDataOutput As New dataOutput
With thisDataOutput
.category = newCategoryName
.prodDesc = thisProdDesc
.code = codeCount
.clCount = clCount
.clPer = clPer
.bsCount = bsCount
.bsPer = bsPer
.perPen = perPen
.uIndex = uIndex
.wCL_Count = wClCount
.wCL_Per = wClPer
.wBS_Count = wBsCount
.wBS_Per = wBsPer
.wPer_Pen = wPerPen
.wIndex = wIndex
dataOutputList.Add(thisDataOutput)
End With
****** This is where I"m writing the Excel file
Private Sub WriteExcelFile(ByVal outputFilename)
Try
~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
outputFilename = "C:\excel\CSV_OUTPUT_A2.xlsx"
If File.Exists(outputFilename) Then
File.Delete(outputFilename)
End If
~~> Add a New Workbook
xlWorkBook = xlApp.Workbooks.Add
~~> Set the sheet that we want to work with
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
With xlWorkSheet
.Range("A1").Value = "Category"
.Range("B1").Value = "Prod_Desc"
.Range("C1").Value = "Code"
.Range("D1").Value = "CL_Count"
.Range("E1").Value = "CL_Per"
.Range("F1").Value = "Bs_Count"
.Range("G1").Value = "Bs_Per"
.Range("H1").Value = "Per_pen"
.Range("I1").Value = "U_Index"
.Range("J1").Value = "W_CL_Count"
.Range("K1").Value = "W_CL_Per"
.Range("L1").Value = "W_Bs_Count"
.Range("M1").Value = "W_Bs_Per"
.Range("N1").Value = "W_Per_pen"
.Range("O1").Value = "W_Index"
Dim thisRow As Integer = 2
Dim FinalOutput = From L As dataOutput In dataOutputList
For Each L As dataOutput In FinalOutput
Dim aCell As String = "A" & thisRow
Dim bCell As String = "B" & thisRow
Dim cCell As String = "C" & thisRow
Dim dCell As String = "D" & thisRow
Dim eCell As String = "E" & thisRow
Dim fCell As String = "F" & thisRow
Dim gCell As String = "G" & thisRow
Dim hCell As String = "H" & thisRow
Dim iCell As String = "I" & thisRow
Dim jCell As String = "J" & thisRow
Dim kCell As String = "K" & thisRow
Dim lCell As String = "L" & thisRow
Dim mCell As String = "M" & thisRow
Dim nCell As String = "N" & thisRow
Dim oCell As String = "O" & thisRow
.Range(aCell).Value = L.category
.Range(bCell).Value = L.prodDesc
.Range(cCell).Value = L.code
.Range(dCell).Value = L.clCount
.Range(eCell).Value = L.clPer
.Range(fCell).Value = L.bsCount
.Range(gCell).Value = L.bsPer
.Range(hCell).Value = L.perPen
.Range(iCell).Value = L.uIndex
.Range(jCell).Value = L.wCL_Count
.Range(kCell).Value = L.wCL_Per
.Range(lCell).Value = L.wBS_Count
.Range(mCell).Value = L.wBS_Per
.Range(nCell).Value = L.wPer_Pen
.Range(oCell).Value = L.wIndex
thisRow += 1
Next
End With
xlWorkBook.SaveAs(outputFilename)
xlWorkBook.Close()
xlApp = Nothing
xlWorkSheet = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
-Joni
Continue reading...
Problem is, I have a custom class set up to hold the values of each field being written out and the variables are declared as either an integer or double. As I perform multiple calculations, I save the values in this class and then save them into a list. At the end of the program I write out the list to an Excel file.
Ive been trying different approaches all morning but havent been able to figure out how to fix this. I changed the custom class to all string variables (im just using the class to hold the values so I can write to the list, no calculations are being performed) but when I write it out, I loose leading zeros where there used to be and need to be. For example, where I used to have 0.08 is now .08. Thats not okay. I tried formatting the output, but Im doing it wrong, I started getting "###.000" written out.
Anyway, heres a condensed version of my code. Im going to just put in here what I think is relevant to my problem. If anyone has any ideas on what I need to do to fix this, without a complete rewrite (that would KILL me! lol) Id love to hear them. I know its a lot of code, but believe me, theres a lot more in there that I cut out. I just felt this was the minimum I needed to show here so you could get an idea of what I have going on. Most of the calculations are repetitive, but I wanted you to see what variables I was using for them, and also how I have them declared.
TIA!!
Joanie
Private Class dataOutput
this class is used to store all data for output file at the end of the program
Public category As String = "--"
Public prodDesc As String = "--"
Public code As Integer = 0
Public clCount As Integer = 0
Public clPer As Double = 0
Public bsCount As Integer = 0
Public bsPer As Double = 0
Public perPen As Double = 0
Public uIndex As Double = 0
Public wCL_Count As Integer = 0
Public wCL_Per As Double = 0
Public wBS_Count As Integer = 0
Public wBS_Per As Double = 0
Public wPer_Pen As Double = 0
Public wIndex As Double = 0
End Class
********* Next section that used these variables
Dim wBsCount As Integer = H3
Dim bsCount As Integer = H7
Dim wClCount As Integer = H10
Dim clCount As Integer = H14
Dim clPer As String = "--"
If H14 = 0 OrElse G14 = 0 Then
clPer = "0"
Else
Dim XclPer As Double = (H14 / G14)
clPer = XclPer.ToString("###.000")
End If
Dim bsPer As String = "--"
If H7 = 0 OrElse G7 = 0 Then
bsPer = "0"
Else
Dim XbsPer As Double = (H7 / G7)
bsPer = XbsPer.ToString("###.000")
End If
Dim perPen As String = "--"
If H14 = 0 OrElse H7 = 0 Then
perPen = "0"
Else
Dim XperPen As Double = (H14 / H7)
perPen = XperPen.ToString("###.000")
End If
Dim uIndex As String = "--"
If H14 = 0 OrElse G14 = 0 OrElse H7 = 0 OrElse G7 = 0 Then
uIndex = "0"
Else
Dim XuIndex As Double = ((H14 / G14) / (H7 / G7) * 100)
uIndex = XuIndex.ToString("###.000")
End If
Dim wClPer As String = "--"
If H13 = 0 Then
wClPer = "0"
Else
Dim XwClPer As Double = (H13 / 100)
wClPer = XwClPer.ToString("###.000")
End If
Dim wBsPer As String = "--"
If H6 = 0 Then
wBsPer = "0"
Else
Dim XwBsPer As Double = (H6 / 100)
wBsPer = XwBsPer.ToString("###.000")
End If
Dim wPerPen As String = "--"
If H11 = 0 Then
wPerPen = "0"
Else
Dim XwPerPen As Double = (H11 / 100)
wPerPen = XwPerPen.ToString("###.000")
End If
Dim wIndex As String = "--"
If H13 = 0 OrElse H6 = 0 Then
wIndex = "0"
Else
Dim XwIndex As Double = (H13 / 100) / (H6 / 100) * 100
wIndex = XwIndex.ToString("###.000")
End If
******* Skipping to next section again
Dim thisDataOutput As New dataOutput
With thisDataOutput
.category = newCategoryName
.prodDesc = thisProdDesc
.code = codeCount
.clCount = clCount
.clPer = clPer
.bsCount = bsCount
.bsPer = bsPer
.perPen = perPen
.uIndex = uIndex
.wCL_Count = wClCount
.wCL_Per = wClPer
.wBS_Count = wBsCount
.wBS_Per = wBsPer
.wPer_Pen = wPerPen
.wIndex = wIndex
dataOutputList.Add(thisDataOutput)
End With
****** This is where I"m writing the Excel file
Private Sub WriteExcelFile(ByVal outputFilename)
Try
~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
outputFilename = "C:\excel\CSV_OUTPUT_A2.xlsx"
If File.Exists(outputFilename) Then
File.Delete(outputFilename)
End If
~~> Add a New Workbook
xlWorkBook = xlApp.Workbooks.Add
~~> Set the sheet that we want to work with
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
With xlWorkSheet
.Range("A1").Value = "Category"
.Range("B1").Value = "Prod_Desc"
.Range("C1").Value = "Code"
.Range("D1").Value = "CL_Count"
.Range("E1").Value = "CL_Per"
.Range("F1").Value = "Bs_Count"
.Range("G1").Value = "Bs_Per"
.Range("H1").Value = "Per_pen"
.Range("I1").Value = "U_Index"
.Range("J1").Value = "W_CL_Count"
.Range("K1").Value = "W_CL_Per"
.Range("L1").Value = "W_Bs_Count"
.Range("M1").Value = "W_Bs_Per"
.Range("N1").Value = "W_Per_pen"
.Range("O1").Value = "W_Index"
Dim thisRow As Integer = 2
Dim FinalOutput = From L As dataOutput In dataOutputList
For Each L As dataOutput In FinalOutput
Dim aCell As String = "A" & thisRow
Dim bCell As String = "B" & thisRow
Dim cCell As String = "C" & thisRow
Dim dCell As String = "D" & thisRow
Dim eCell As String = "E" & thisRow
Dim fCell As String = "F" & thisRow
Dim gCell As String = "G" & thisRow
Dim hCell As String = "H" & thisRow
Dim iCell As String = "I" & thisRow
Dim jCell As String = "J" & thisRow
Dim kCell As String = "K" & thisRow
Dim lCell As String = "L" & thisRow
Dim mCell As String = "M" & thisRow
Dim nCell As String = "N" & thisRow
Dim oCell As String = "O" & thisRow
.Range(aCell).Value = L.category
.Range(bCell).Value = L.prodDesc
.Range(cCell).Value = L.code
.Range(dCell).Value = L.clCount
.Range(eCell).Value = L.clPer
.Range(fCell).Value = L.bsCount
.Range(gCell).Value = L.bsPer
.Range(hCell).Value = L.perPen
.Range(iCell).Value = L.uIndex
.Range(jCell).Value = L.wCL_Count
.Range(kCell).Value = L.wCL_Per
.Range(lCell).Value = L.wBS_Count
.Range(mCell).Value = L.wBS_Per
.Range(nCell).Value = L.wPer_Pen
.Range(oCell).Value = L.wIndex
thisRow += 1
Next
End With
xlWorkBook.SaveAs(outputFilename)
xlWorkBook.Close()
xlApp = Nothing
xlWorkSheet = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
-Joni
Continue reading...