NPOI Read and write removes format control of the checkbox

Question

Writing a read Excel file removes the cell linked to that checkbox using NPOI.

I have tried using a FileStream to read an Excel file with a checkbox (with a cell link using Format Control), then writing it using another FileStream removes the cell linked to that checkbox.

Dim xssfWorkBook As XSSFWorkbook
' Read the Excel template
Using fs As New FileStream(src, FileMode.Open, FileAccess.Read)
    xssfWorkBook = New XSSFWorkbook(fs)
End Using

Dim sheet As ISheet = xssfWorkBook.GetSheetAt(0)
Dim row As IRow = sheet.GetRow(4)
Dim A4Cell As ICell = row.GetCell(0) ' Linked cell of the checkbox control
A4Cell.SetCellValue("1") ' Set "0" -> "1" (TRUE)

' Write to Excel file
Using fs As New FileStream(dest, FileMode.Create, FileAccess.Write)
    xssfWorkBook.Write(fs)
End Using

I expect the checkbox to retain the linked cell.

score 0 · Accepted Answer · answered Aug 06 '19 at 01:36

Used HSSF to solve the issue with linked cell being erased. This only works on Excel files with .xls as the extension.

Dim hssfWorkBook As HSSFWorkbook
' Read the Excel template
Using fs As New FileStream(src, FileMode.Open, FileAccess.Read)
    hssfWorkBook= New HSSFWorkbook(fs)
End Using

NPOI Read and write removes format control of the checkbox

1 Answers1