B4J Question How to modify cell values of Excel files

xulihang · Jun 13, 2019

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.

B4X:

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 · Jun 13, 2019

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

B4X:

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

xulihang · Jun 13, 2019

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.

B4X:

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

B4J Question How to modify cell values of Excel files

xulihang

Active Member

xulihang

Active Member

xulihang

Active Member

Similar Threads

B4J Question How to modify cell values of Excel files

xulihang

Active Member

xulihang

Active Member

xulihang

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency