B4J Question Exporting settings/map file to an Excel document

[trueboss323]

This is from my B4A thread here . I am using KeyValueStore2 to save user settings into the default file. I want to export the map or settings file it creates and be able to open it and manually edit settings in Excel. Then be able to convert it back to the original file format. With B4J , i'd like to create a program where it does it automatically for me. Any help please?

 

[Erel]

KVS.ListKeys returns all the keys in the database. You need to get the values using these keys and write them to an Excel workbook: jPOI - Supports Microsoft Excel xls and xlsx Workbooks

 

[trueboss323]

KVS.ListKeys returns all the keys in the database. You need to get the values using these keys and write them to an Excel workbook: jPOI - Supports Microsoft Excel xls and xlsx Workbooks

Here's what i got so far:

Private Sub CreateWorkbook
    Dim wb As PoiWorkbook
    wb.InitializeNew(True) 'create new xlsx workbook
    Dim sheet1 As PoiSheet = wb.AddSheet("Sheet 1", 0)
    Dim row As PoiRow = sheet1.CreateRow(0)

    Dim titleRow As PoiRow = sheet1.CreateRow(0) 'first row
    titleRow.CreateCellString(0, "key")
    titleRow.CreateCellString(1, "value")
  
    For Each key In kvs.ListKeys
        Dim row As PoiRow = sheet1.CreateRow(1) 'second row
        row.CreateCellString(0,key)
        row.CreateCellString(1,kvs.Get(key))

    Next

    wb.Save(File.DirApp, "1.xlsx")
    wb.Close
End Sub

It only gives me the last key in the array for the second row , instead of listing all the keys.

 

[Erel]

Change your code to:

Dim row As PoiRow = sheet1.CreateRow(sheet1.LastRowNumber+1)

 

[trueboss323]

Thanks Erel, that worked great! Now how to convert it back to the KVS format?

 

[Erel]

Check the localizator converter source code: https://www.b4x.com/android/forum/t...localize-your-b4x-applications.68751/#content
It reads the Excel workbook and creates a SQLite database. You need to read the data and put it back in KVS.

 

[trueboss323]

With KVS2 it's a bit different than from SQL. I'm not familiar with what format it is in, and KVS doesn't give me much options.

Private Sub BuildKVS (Xls As String, Output As String)
    kvs.Initialize(fc.InitialDirectory, "datastore2")
    Try
        Dim wb As PoiWorkbook
        wb.InitializeExisting(Xls, "", "")
        Dim sheet As PoiSheet = wb.GetSheet(0)
        Dim headerRow As PoiRow = sheet.GetRow(0)
        Dim keylist As List
      
  
        For Each cell As PoiCell In headerRow.Cells
            Log(keylist)
        Next
        For Each row As PoiRow In sheet.Rows
            If row.RowNumber = 0 Then Continue
            Dim key As String
            For Each cell As PoiCell In row.Cells
                If cell.ColumnIndex = 0 Then
                    key = cell.ValueString
                Else
                End If
            Next
        Next
    '    sql.TransactionSuccessful
        wb.Close
        Log("Database created successfully!")
    Catch

        Log(LastException)
      
        Log("Error: " & LastException)

    End Try
 
End Sub

I'm a bit lost here.

 

[DonManfred]

"datastore2" is a SqLite Database....

Open if in the usual way, check the table and their layout...

See Class-Source of kvs2 for more details

'creates the main table (if it does not exist)
Private Sub CreateTable
    sql1.ExecNonQuery("CREATE TABLE IF NOT EXISTS main(key TEXT PRIMARY KEY, value NONE)")
End Sub

 

[Erel]

KVS is simpler than SQL. You just need to call KVS.Put(<key>, <value>)

 

[trueboss323]

I still can't seem to figure it out. It just gives me more errors and problems. I don't even know if I'm doing it correctly:

Dim kvs As KeyValueStore
kvs.Initialize(fc.InitialDirectory, "datastore2")

    Private sql As SQL
    File.Delete(TextField3.Text, "")
    sql.InitializeSQLite(TextField3.Text, "converted", True)
    sql.BeginTransaction
    Try
        sql.ExecNonQuery("CREATE TABLE data (key TEXT, value TEXT, PRIMARY KEY (key, key))")
        Dim wb As PoiWorkbook
        wb.InitializeExisting(Xls, "", "")
        Dim sheet As PoiSheet = wb.GetSheet(0)
        Dim keyRow As PoiRow = sheet.GetRow(0)
        If keyRow.IsInitialized = False Then
            cutils.ShowNotification2("", "Key row not found!", cutils.ICON_ERROR, MainForm)
            Return
        End If
        Dim langs As List
    For Each cell As PoiCell In keyRow.Cells
        If cell.ValueString.ToLowerCase = "value" Then Continue
            langs.Add(cell.ValueString)
        Next
        For Each row As PoiRow In sheet.Rows
            If row.RowNumber = 0 Then Continue
            Dim key As String
            For Each key In kvs.ListKeys
                If cell.ColumnIndex = 0 Then
                    kvs.Put(key, kvs.Get)
                Else
                    Dim lang As String = langs.Get(cell.ColumnIndex - 1)
                    sql.ExecNonQuery2("INSERT INTO data VALUES (?, ?, ?)", Array (key.ToLowerCase, lang, cell.ValueString))
                End If
            Next
        Next
        sql.TransactionSuccessful
        wb.Close
        Log("Finished!")

 

[Erel]

I don't understand what you are doing.

Are you trying to read data from the database? Use DBUtils.ExecuteMemoryTable to read all the data and then insert it with KVS.Put.
Shouldn't take more than 4 lines of code.

For Each row() As String In DBUtils.ExecuteMemoryTable(SQL, ...)
   KVS.Put(row(0), row(1))
Next