I am writing zip codes to a column using aspose.Cells. Sometimes the zip code is in the format 01667. When I write to the cell, it comes up as "1667" (loses the 0). I am using styles and setting the Custom property to "00000", but it still comes back as "1667" in the spreadsheet. It works this way regardless of whether I use PutValue to add the value as a string OR and integer. How do I have it keep the leading 0?
Below is my code.
Dim nColStyleIndexZip As Integer = oExcel.Styles.Add()
Dim oColStyleZip As Aspose.Cells.Style = oExcel.Styles(nColStyleIndexZip)
With oColStyleZip
.VerticalAlignment = TextAlignmentType.Top
.HorizontalAlignment = TextAlignmentType.Left
.Borders(Aspose.Cells.BorderType.TopBorder).LineStyle = CellBorderType.Dotted
.Borders(Aspose.Cells.BorderType.BottomBorder).LineStyle = CellBorderType.Dotted
.Borders(Aspose.Cells.BorderType.LeftBorder).LineStyle = CellBorderType.Thin
.Borders(Aspose.Cells.BorderType.RightBorder).LineStyle = CellBorderType.Thin
.Custom = "00000"
.IsTextWrapped = True
End With
... Later in code...
'Add the Zip Field
btColumn += CByte(1)
oCell = oCells(nRow, btColumn)
oCell.PutValue(sZip)
oCell.Style = oColStyleZip
NOTE: sZip is a string, but I also tried oCell.PutValue(CINT(sZip)) and had the same result.
Help!
Hi,
Thanks for considering Aspose.
Which version of Aspose.Cells you are using. I use Aspose.Cells 4.1 and it 's working fine with your code:
Dim oExcel As Workbook = New Workbook()
Dim sheet As Worksheet = oExcel.Worksheets(0)
Dim nColStyleIndexZip As Integer = oExcel.Styles.Add()
Dim oColStyleZip As Aspose.Cells.Style = oExcel.Styles(nColStyleIndexZip)
With oColStyleZip
.VerticalAlignment = TextAlignmentType.Top
.HorizontalAlignment = TextAlignmentType.Left
.Borders(Aspose.Cells.BorderType.TopBorder).LineStyle = CellBorderType.Dotted
.Borders(Aspose.Cells.BorderType.BottomBorder).LineStyle = CellBorderType.Dotted
.Borders(Aspose.Cells.BorderType.LeftBorder).LineStyle = CellBorderType.Thin
.Borders(Aspose.Cells.BorderType.RightBorder).LineStyle = CellBorderType.Thin
.Custom = "00000"
.IsTextWrapped = True
End With
'Add the Zip Field
Dim oCells As Cells = sheet.Cells
Dim oCell As Cell = oCells(0, 0)
Dim sZip As String = "01667"
oCell.PutValue(CInt(sZip))
oCell.Style = oColStyleZip
oExcel.Save("d:\testingleadingzero1.xls")
Best Regards,
Hi,
Thanks for considering Aspose.
Which version of Aspose.Cells you are using. I use Aspose.Cells 4.1 and it 's working fine with your code (Please check the output file attached):
Dim oExcel As Workbook = New Workbook()
Dim sheet As Worksheet = oExcel.Worksheets(0)
Dim nColStyleIndexZip As Integer = oExcel.Styles.Add()
Dim oColStyleZip As Aspose.Cells.Style = oExcel.Styles(nColStyleIndexZip)
With oColStyleZip
.VerticalAlignment = TextAlignmentType.Top
.HorizontalAlignment = TextAlignmentType.Left
.Borders(Aspose.Cells.BorderType.TopBorder).LineStyle = CellBorderType.Dotted
.Borders(Aspose.Cells.BorderType.BottomBorder).LineStyle = CellBorderType.Dotted
.Borders(Aspose.Cells.BorderType.LeftBorder).LineStyle = CellBorderType.Thin
.Borders(Aspose.Cells.BorderType.RightBorder).LineStyle = CellBorderType.Thin
.Custom = "00000"
.IsTextWrapped = True
End With
'Add the Zip Field
Dim oCells As Cells = sheet.Cells
Dim oCell As Cell = oCells(0, 0)
Dim sZip As String = "01667"
oCell.PutValue(CInt(sZip))
oCell.Style = oColStyleZip
oExcel.Save("d:\testingleadingzero1.xls")
Best Regards,