Android Question Writing Excel file via SMB sometimes leaves a corrupt file

Frank.G

Member
Licensed User
Dear all,

if encounter a problem with my app that I am not able to solve.
The function is as follows:
1. Download an Excel file via SMB
2. Modify the file on the Tablet (adding cells and pages)
3. Then uploading the file via SMB to the server again.

Before 1. The data to add is collected on the tablet, the saving loop starts when all data is entered and save is pressed.

Below the SMB and writing Subs are shown:

B4X:
Sub get_logging
    
    SMB_init.ListFiles(smbupload_loggingpath, used_tablet)
    Wait for SMB_init_ListCompleted (Url As String, Success As Boolean, Entries() As SMBFile)
    If Not(Success) Then
        SMB_init.UploadFile(File.DirAssets, "logging.xls", smbupload_loggingpath, used_tablet)
        Wait For SMB_Init_UploadCompleted (Url As String, RemoteFile As String, Success As Boolean)
        If Not(Success) Then
            MsgboxAsync("Cannot connect to network and upload files, check connection and contact IT","Problem")
        Else
            SMB_upload.DownloadFile(smbupload_loggingpath, used_tablet, File.DirInternal, "logging.xls")
            wait for SMB_upload_DownloadCompleted (Url As String, RemoteFile As String, Success As Boolean)
            If Not(Success) Then
                MsgboxAsync("Cannot connect to network and download files, check connection and contact IT","Problem")
            Else
                write_excel
            End If
        End If
    Else
        SMB_upload.DownloadFile(smbupload_loggingpath, used_tablet, File.DirInternal, "logging.xls")
        wait for SMB_upload_DownloadCompleted (Url As String, RemoteFile As String, Success As Boolean)
        If Not(Success) Then
            MsgboxAsync("Cannot connect to network and download files, check connection and contact IT","Problem")
        Else
            write_excel
        End If
    End If
End Sub




Sub write_excel
    checkbluetooth
    GetInfo
    
    DateTime.DateFormat = "dd.MM.yyyy"
    Dim t As Int = 0
    Dim sheet_names As List
    sheet_names.Initialize
    sheet_names.Clear
    newWorkbook_r.Initialize(File.DirInternal, "logging.xls")
    sheet_r = newWorkbook_r.GetSheet(0)
    newWorkbook_w.Initialize2(File.DirInternal, "logging.xls", newWorkbook_r)
    If newWorkbook_w.NumberOfSheets = 1 And newWorkbook_w.GetSheet(0).Name = "Tabelle1" Then
        newWorkbook_w.RemoveSheet(0)
        newWorkbook_w.AddSheet(auto_project.Text,0)
    End If
    For i = 0 To newWorkbook_w.NumberOfSheets -1
        sheet_names.Add(newWorkbook_w.GetSheetNames(i))
    Next
    If sheet_names.IndexOf(auto_project.Text) <> -1 Then
        sheet_w = newWorkbook_w.GetSheet(sheet_names.IndexOf(auto_project.Text))
    Else
        newWorkbook_w.AddSheet(auto_project.Text, newWorkbook_w.NumberOfSheets)
        sheet_w = newWorkbook_w.GetSheet(newWorkbook_w.NumberOfSheets-1)
    End If
    t = sheet_w.RowsCount
    Dim cell As WritableCell
    For i = 0 To collect_list.Size -1
        cell.InitializeText(0, i + t, view_callsign.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(1, i + t, view_project.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(2, i + t, view_3lc.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(3, i + t, view_amount.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(4, i + t, view_unit.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(5, i + t, view_partnummer.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(6, i + t, view_description.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(7, i + t, view_price.Get(i))
        sheet_w.AddCell(cell)
        cell.InitializeText(8,i + t, DateTime.Date(DateTime.Now))
        sheet_w.AddCell(cell)
        
    Next
    newWorkbook_w.Write
    newWorkbook_w.Close
'    Log("Ready for upload")
    SMB_upload.UploadFile(File.DirInternal, "logging.xls", smbupload_loggingpath, used_tablet)
'    Log("Upload startet")
    wait for SMB_upload_UploadCompleted (Url As String, RemoteFile As String, Success As Boolean)
    If Not(Success) Then
        MsgboxAsync("Cannot connect to network and upload files, check connection and contact IT","Problem")
    Else
        clear_all
        blocker = False
    End If
End Sub
What happens is that the app works flawless for some days and then all of a sudden the Excel file is corrupted (all data collected is also lost) . Resulting in a crash when trying to save the next time.
It seems that the file is short as the good one was 21kB the last time used and the defect file was only 8kB.

When trying to open with Excel it shows the file as broken but and tries to repair, leaving an empty sheet.
I know the code is maybe not optimal but maybe its not the style but some really bad bug included.


Any tipps / hints what I am doing wrong.

BTW I found no possibility to check the integrety of the excel file when downloading the file or check the modified file before uploading so I really don't know if it happen during the upload/or download phase.
The app is in use by other people and I could not reproduce the probleme while I used it myself. It happens maybe once a week or once every two weeks while the app works daily mostly several times in the morning and then after noon.

regards and stay safe all
Frank
 

Frank.G

Member
Licensed User
You need to find where it fails. First step is to manually copy the Excel file in some other way and check whether it is complete or not.
Thx Erel for the response.

So from your answer I take it that the is basically correct coded (not elegant I think but never the less correct).
I try to wrap my head around some methods to save extra file versions to find the moment when and where it fails.
I will write a seperate logfile as a first step to see at what point it fails.

One more question, is the the code sensitive on a certain point to interrupts of the flow?

Maybe i come back later with another question when more info is avail.
Frank
 
Top