Here is my code:
Can anyone tell me what to do to make the remove row work?
Line 40 is the culprit.
Thank you all in advance!
Can anyone tell me what to do to make the remove row work?
Line 40 is the culprit.
Thank you all in advance!
B4J Code:
Private Sub DeleteFileFromSpreadsheet(FileID As String)
LogColor("Sub DeleteFileFromSpreadsheet started", 0xFF009901)
Dim XL As XLUtils
XL.Initialize
' Define folder and filename
Dim XLSXFolder As String = File.DirApp '"D:\Downloads" ' Set your folder path here
Dim XLSXFileName As String = "FileList.xlsx" ' Spreadsheet filename
' Create workbook and get the sheet
Dim workbook As XLWorkbookWriter = XL.CreateWriterFromTemplate(XLSXFolder, XLSXFileName)
Dim sheet As PoiSheet = workbook.PoiWorkbook.GetSheetByName("Files")
' Find the row with the FileID and delete it
Dim RowNum As Int
Dim NumberOfRows As Int = sheet.LastRowNumber + 1
LogColor("Number Of Rows In the Spreadsheet = " & NumberOfRows,0xFFFF0000)
For RowNum = 1 To NumberOfRows - 1 ' Assuming header is in the first row, we start at row 1. Row 0 contains the header.
'Note: NumberOfRows is not zero based like RowNum is. That is why the -1.
Dim Row As PoiRow = sheet.GetRow(RowNum)
Dim currentFileId As String = Row.GetCell(5).ValueString ' Assuming Thread ID is in the first column
LogColor("currentFileId = " & currentFileId,0xFFFF0000)
If currentFileId = FileID Then
' Shift rows up to remove the entry
Dim jo As JavaObject = sheet
Dim lastRowNum As Int = sheet.LastRowNumber
LogColor("Row numumber with File on it = " & RowNum,0xFFFF0000)
LogColor("Last Row in spreadsheet = " & lastRowNum,0xFFFF0000)
' Shift rows upwards to fill in the deleted row
Dim nextRow As PoiRow = sheet.GetRow(RowNum + 1)
If nextRow <> Null Then
Log("nextRow is not blank")
Dim fromrow As Int = RowNum + 1
Log("FromRow = " & fromrow)
If fromrow > lastRowNum Then
jo.RunMethod("removeRow", Array(lastRowNum))
Else
Dim torow As Int = lastRowNum
LogColor("torow = " & torow,0xFFFF0000)
Dim noofrows As Int = -1
LogColor("noofrows = " & noofrows,0xFFFF0000)
Log("")
jo.RunMethod("shiftRows", Array(fromrow,torow,noofrows))
End If
End If
'Log("File with ID " & FileID & " deleted from spreadsheet.")
Exit
End If
Next
' Save the updated workbook after deletion
Dim f As String = workbook.SaveAs(XLSXFolder, XLSXFileName, True)
'Wait For (XL.OpenExcel(f)) Complete (Success As Boolean)
ShowOpenAIFilesList
Log("FIle with ID " & FileID & " deleted from spreadsheet.")
LogColor("Sub DeleteFileFromSpreadsheet finished", 0xFFFF7100)
End Sub
