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
