B4J Question How to modify cell values of Excel files

Discussion in 'B4J Questions' started by xulihang, Jun 13, 2019.

  1. xulihang

    xulihang Active Member Licensed User

    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.

    Code:
    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.
     
    Last edited: Jun 13, 2019
  2. xulihang

    xulihang Active Member Licensed User

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

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

    xulihang Active Member Licensed User

    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.

    Code:
    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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice