Embedding Built-In Swift Data with Xlsx in iOS
My new app is going to have a built-in filled SwiftData. When I was building one of my own app, supporting to study for public license test. I considered about how to construct database and manage it conveniently.
I didn’t want to make any server or tool. The popular solution was Excel(xlsx) file. We can input various and huge arranged contents and there is already a good tool, Microsoft Excel.
So I decided to make the database using a spreadsheet.
Setting up Spreadsheet
Spreadsheet file is able to have many sheets, so I used them as table. If the app has Characters, Skills, Effects, the sheet names would be like this.
Then We can fill the table as below. This is skills sheet.
Each skills can refers to their parent book by character_id as relation key.
Doesn’t you have Microsoft Office? Don’t worry we have Google Spreadsheets!
Embeding Spreadsheet
To use xlsx file in our app, we ensure the app contains the file.
Install CoreXLSX
Now we are ready to read it, but how to read .xlsx file? Long years ago I used a Pods XlsxReaderWriter. I tried to find SPM version to avoid use CocoaPods and found CoreXLSX supporting SPM.
Let’s get started with it.
First of all, I installed the package
let project = Project(
name: "ThirdParty",
packages: [...,
.remote(
url: "https://github.com/CoreOffice/CoreXLSX",
requirement: .upToNextMinor(from: "0.14.1"))],
targets: [
.target(
...,
dependencies: [...,
.package(product: "CoreXLSX", type: .runtime)]
),
Excel Repository
I created a new module responsible controlling the excel using the package.
First requirement is creating XLSXFile with the file path.
So we need to get the url to the the file.
let url: URL! = Bundle.main.url(forResource: "data", withExtension: "xlsx")I checked if the file instance is created well.
public class ExcelRepositoryProvider: ExcelRepository {
var url: URL
init(url: URL) {
self.url = url
}
public func loadCharacters() {
guard let xlsx = XLSXFile(filepath: url.path(percentEncoded: false)) else {
return
}
print("excel opened")
}
}However I encountered build issues.
I solved a similar issue in the previous article, therefore I tried to use same solution and solved the problem.
product: .staticFramework,Actually I saw Undefined symbol error again. The reason was that module name is same to the class name. So I had to add a prefix to the name.
As a result, opening the spreadsheet has been succeed.
Reading Sheets
We see only sheets while opening the xlsx file, but xlsx has workbook internal and the workbook has worksheets. The worksheets are what we are seeing in real world.
parseWorkbooks will return workbooks, however my excel file has only one workbook.
guard let workbook = try? xlsx.parseWorkbooks().first else {
debugPrint("workbook open failed. \(url.path(percentEncoded: false))")
return
}
debugPrint("workbook parsed.")Each workbook has sheets, but it is not collection differently to the it’s name. We should use sheets.items instead.
for sheet in workbook.sheets.items {
debugPrint("worksheet. name", sheet.name)
}
Reading Cells
According to the official document, sheets have data. However I couldn’t see such a property. I looked into the library sources and found the reason.
Workbook.Sheet doesn’t have the property,
but Worksheet has it.
However I couldn’t get it by parseWorkbooks. The only way to obtain Worksheet is to use parseWorksheet method.
The method requires a path as a parameter. Therefore I should call parseWorksheetPathsAndNames first.
guard let sheetPaths = try? xlsx.parseWorksheetPathsAndNames(workbook: workbook) else {
debugPrint("worksheet path failed. \(url.path(percentEncoded: false))")
return
}
for sheetPath in sheetPaths {
debugPrint("worksheet. name", sheetPath.name, "path", sheetPath.path)
}The paths are formatted like “xl/worksheets/sheetX.xml”
Now I can access to the data of the worksheet.
debugPrint("worksheet open. name", sheetPath.name)
guard let sheetData = sheet.data else {
continue
}
debugPrint("worksheet rows. ", sheetData.rows)And the data has rows and columns, but it’s a little complex.
So I make the log concise.
for row in sheetData.rows {
debugPrint("row", row.reference)
for cell in row.cells {
debugPrint("cell", cell.value)
}
}I found a weird thing. There is only numbers, no letters as value on console
Why this happened? According to the guide, xlsx reuses strings using Shared String. In practice stringValue method requires a parameter of the type.
Strings in spreadsheet internals are frequently represented as strings shared between multiple worksheets. To parse a string value from a cell you should use
stringValue(_: SharedStrings)function onCelltogether withparseSharedString()onXLSXFile.
So I should get the shared strings before opening the sheets.
guard let sharedStrings = try? xlsx.parseSharedStrings() else {
debugPrint("shared string obtain failed")
return
}And call stringValue with it.
Now I can see cell values actually.
This way is easy, but if there are extra values like this?
Reading Cells using Rows
We can get cells by given rows or columns.
However the type of Rows and Columns are Collection. According to the definition, Column means ColumnReference and Row is Unsigned Integer.
So we can get all cells by Row number.
let headerRow: UInt = 2
let cells = sheet.cells(atRows: [headerRow])
for cell in cells {
let cellValue = cell.stringValue(sharedStrings) ?? ""
debugPrint("cell \(cell.reference.column.value) = \(cellValue)")
}
Loading Headers
Now we know how to read cells. I will create structs to contains data from xlsx and give suffix Info to the structures.
public struct QBCharacterInfo {
public var id: UInt
public var name: String
}I defined loadHeaders to make easy finding the column by name.
public func loadHeaders(of sheetName: String, in sheet: Worksheet) -> [String : String] {
guard let sharedStrings else {
return [:]
}
return sheet.cells(atRows: [Self.headerRow])
.reduce(into: [String : String]()) { dict, cell in
let columnId = cell.reference.column.value
let cellValue = cell.stringValue(sharedStrings) ?? ""
dict[columnId] = cellValue
}
}
...
let headers = loadHeaders(of: "characters", in: sheet)
Loading Row
To map the fields of a row to the properties, we will give the headers. Each Cell Reference has Column Information, so we can know which cell is responsible for the header.
public func loadCells(of row: UInt, with headers: [String : String], in sheet: Worksheet) -> [String : Cell] {
return sheet.cells(atRows: [row])
.reduce(into: [String : Cell]()) { dict, cell in
let columnId = cell.reference.column.value
guard let column = headers[columnId] else {
return
}
dict[column] = cell
}
}
...
let cells = loadCells(of: Self.headerRow + 1, with: headers, in: sheet)
for cell in cells {
let columnId = cell.key
let cellValue = cell.value.stringValue(sharedStrings!) ?? ""
debugPrint("row \(columnId) = \(cellValue)")
}
Generating Info with Row
We can now obtain each field values, it means it is possible to generate infos. I will create info instances row by row infinite.
while true {The row number is also going to be increased.
row += 1There is no type for row, a row is collection of cell.
let cells = loadCells(of: Self.headerRow + row, with: headers, in: sheet)Using the header name, we can get fields we want.
guard let idCell = cells["id"], let nameCell = cells["name"] else {
break
}Finally we can make instance with them.
let character: QBCharacterInfo = .init(id: UInt(idCell.value ?? "") ?? 0,
name: nameCell.stringValue(sharedStrings)!)
debugPrint("row", character)However the loop never done. So I validated id value.
let id = UInt(idCell.value ?? "") ?? 0
guard id > 0 else {
break
}And I found the id value is invalid. In practice id cell’s value is 1.0.
So casting to UInt directly is not working. We should cast it Double first.
let id = UInt(Double(idCell.value ?? "") ?? 0)If the id is not positive, it is invalid, therefore we can stop looping.
guard id > 0 else {
break
}Before finishing the method, we should append a new character into list to use later.
characters.append(character)With the same approach, I created more load methods.
public func loadCharacters() ..
public func loadCharacterSkills() ..
public func loadCharacterSkillEffects() ..Synchronizing Swift Data
Our final purpose is to use Swift Data, so the last task is appending data into Swift Data from Excel Infos generated as Instances.
Excel Synchronizer will be responsible to sync excel to core data.
public struct ExcelSynchronizer {
let excel: ExcelRepository
let model: DatabaseRepositoryI will use it only to making built-in core data before deploying the app, therefore I don’t push into Shared.
The synchronizer should have sync.. method for each Table type.
public func sync() throws {
syncCharacters()
syncCharacterSkills()
syncSkillEffects()
}If the model matched to the excel row is not exist, I will create new one and sync to it.
public func syncCharacters() throws{
for info in excel.characters {
var model = try database.fetchCharacter(by: info.id) ?? database.createCharacter(from: info)
info.sync(model)
}
}Sync methods just clone properties
extension QBCharacterInfo {
public func sync(to model: QBCharacter){
model.id = id
model.name = name
}
}The relationship should be set on outside of sync.
guard let character = try database.fetchCharacter(by: info.characterId) else {
continue
}
if model.owner == nil {
model.owner = character
character.skills.append(model)
}Finally I run synchronization via Logic
switch action {
case let .syncData(excel):
return .run { [excel]send in
excel.load()
let syncer: ExcelSynchronizer = .init(excel: excel, database: database)
try await syncer.sync()
await send(.updateSyncState(.completed))
}I didn’t specify the path, so the database file is created under Libary directory. How can see the default path? I used NSPersistentContainer before but it is for Core Data. So I print another approach.
public func printUrl() {
guard let url = container.configurations.first?.url else {
return
}
debugPrint("Swift Data Url: \(url)")
}Now I can see the url and open the directory.
SQLite
The file’s extension is store, not sqlite. Luckily to us, we can open with SQLite viewer by renaming it as .sqlite.
However I couldn’t see any data.
According to GPT, store-wal has changes temporary to improve performance and patched to sqlite periodically. So to see the data in .sqlite , we should rename wal and shm also.
Now I can see the rows
However I didn’t want to embed wal and shm files, therefore I looked for ways how to force flush wal .
I attempted this code, but it returned OK and did nothing.
sqlite3_wal_checkpoint_v2(db, nil, SQLITE_CHECKPOINT_FULL, nil, nil) == SQLITE_OKI guess it is opened by ModelContainer, so we can’t configure it by code. So I ran the sqlite command in terminal.
sqlite3 …./default.store
PRAGMA journal_mode=DELETE
This command flushes and removes WAL and SHM.
And I could see the wal file is empty after relaunching my app.
Embedding SQLite
Now let’s embed this sqlite file!
First embed the store in App project.
We can specify store url to ModelContainer. I can’t use both url and isStoredInMemoryOnly parameters in one constructor.
let modelConfiguration: ModelConfiguration
= forPreview ? ModelConfiguration(schema: schema,
url: storeUrl)
: ModelConfiguration(schema: schema,
isStoredInMemoryOnly: forPreview) url: storeUrl)How to get the path to store file? It’s simple, we don’t have to use FileManager.
let storeUrl = URL.applicationSupportDirectory
.appending(path: "default")
.appendingPathExtension("store")We can’t load database from the embeded store even if specifying the url. So I cloned it to the default path before initializing ModelContainer.
let embededStoreUrl = Bundle.main.url(forResource: "default", withExtension: "store")!
do {
if !FileManager.default.fileExists(atPath: storeUrl.path) {
try FileManager.default.copyItem(at: embededStoreUrl, to: storeUrl)
}This approach can’t replace built-in core database after updating app. Maybe we should check if this launch is first in this version.
Please let me know if you have a way to turn off WAL using Swift Data.
I embedded excel file instead of SQLite in some apps to use update through google drive.
If you found any helpful from this post, please give it a round of applause 👏. Explore more iOS-related content in my other posts.
You can give me claps up to 50 if you keep pressing the button :)
For additional insights and updates, check out my LinkedIn profile. Thank you for your support!
Troubleshooting
Undefined symbol: CoreXLSX.XLSXFile…
product: .staticFramework,
