NPOI with VB how to set cellstyle dataformat to DATE

Question

I'm using VB and NPOI to write DATE data to an excel ROW.

I'm trying to use the following code to set the dataformat of the cell to "mm-dd-yy", but I keep getting an index out of range error when setting the cell.cellstyle.

There are a number of C examples of working code, but I'm trying this from VB.

        Dim CELLfont as HSSFFont = XLworkbook.CreateFont 
            CELLfont.FontName = "Arial"
            CELLfont.IsBold = True 

        Dim CELLstyle As HSSFCellStyle = XLworkbook.CreateCellStyle 

        With CELLstyle
                .BorderRight = BorderStyle.Double  
                .SetFont(CELLfont)  
                .DataFormat = XLworkbook.CreateDataFormat().GetFormat( "mm-dd-yy" )
        End With


        For C As Integer = 0 to DTforEXCELdata.Columns.Count - 1
             XLrow = XLsheet.CreateRow(XLrowCOUNTER)

             XLrow.CreateCell(C)
             XLrow.Cells(C).CellStyle = CELLstyle '**Error index out of range is here**
             XLrow.Cells(C).SetCellValue(DATEvalue)
        Next

ajakblackgoat was correct. Just had the iteration in the incorrect place. — gcronin774, Mar 20 '19 at 18:16

score 0 · Answer 1 · answered Mar 19 '19 at 17:19

0

You're applying the cellStyle to a negative number cell

For C As Integer = 0 to DTforEXCELdata.Columns.Count - 1

This is giving DTforEXCELdata.Columns.Count the value of 0

Step through the code that produces it and see if you can find out why

answered Mar 19 '19 at 17:19

Badja

857
1
8
33

thank you Badja, but its not step - 1, its just DTforEXCELdata.Columns.Count - 1 – gcronin774 Mar 20 '19 at 18:12

score 0 · Answer 2 · answered Mar 20 '19 at 03:34

From the sample code given above, you create a new row instance each time you create new cell for each datatable columns.

You should create the row before iterating your columns to fill in your data for each cells in that new row.

   XLrow = XLsheet.CreateRow(XLrowCOUNTER) ' <-- to here

   For C As Integer = 0 to DTforEXCELdata.Columns.Count - 1
         'XLrow = XLsheet.CreateRow(XLrowCOUNTER) ' <-- move this code
         XLrow.CreateCell(C)
         XLrow.Cells(C).CellStyle = CELLstyle '**Error index out of range is here**
         XLrow.Cells(C).SetCellValue(DATEvalue)
   Next

The above will fix the error you experiencing.

NPOI with VB how to set cellstyle dataformat to DATE

2 Answers2