B4J Question How to modify cell values of Excel files

[xulihang]

I am trying to translate an excel file. Cell string will be extracted to single files and after the translation is done, the translation should be filled to the original cell.

I can read the excel cell values with the code below. But I cannot change their values since cells are read-only.

Do I have to create a new excel file or is there a way to directly modify these cells.

Sub Read
    Dim wb As PoiWorkbook
    wb.InitializeExisting(File.DirApp,"test.xlsx","")
    Dim sheet1 As PoiSheet
    sheet1= wb.GetSheet(0)

    For Each row As PoiRow In sheet1.Rows
        For Each cell As PoiCell In row.Cells
            File.WriteString(File.DirApp,row.RowNumber&"-"&cell.ColumnIndex&".html",cell.Value)
        Next
    Next
    wb.Close
   
End Sub

Thanks.

 

[xulihang]

I found that use row.CreateCellString() can modify existing cells' values.

row.CreateCellString(cell.ColumnIndex,File.ReadString(File.DirApp,row.RowNumber&"-"&cell.ColumnIndex&".html"))

 

[xulihang]

It seems that cell style should be transferred from previous cells to new cells. I come up with the following code, which will generate a new xlsx file.

Sub Refill
    Dim wb As PoiWorkbook
    wb.InitializeExisting(File.DirApp,"test.xlsx","")
    
    Dim sheet1 As PoiSheet
    sheet1= wb.GetSheet(0)
    Dim rowIndex As Int=1
    
    For Each row As PoiRow In sheet1.Rows
        
        Dim cellIndex As Int=1
        For Each cell As PoiCell In row.Cells
            Dim filename As String=row.RowNumber&"-"&cell.ColumnIndex&".html"
            If File.Exists(File.DirApp,filename) Then
                Dim cellStyle As PoiCellStyle=cell.CellStyle
                Dim newCell As PoiCell
                newCell=row.CreateCellString(cell.ColumnIndex,File.ReadString(File.DirApp,row.RowNumber&"-"&cell.ColumnIndex&".html"))
                newCell.CellStyle=cellStyle
            End If
            cellIndex=cellIndex+1
        Next
        rowIndex=rowIndex+1
    Next
    wb.Save(File.DirApp,"new.xlsx")
    wb.Close
End Sub