I've got some pretty large Excel files with nearly 1M rows that are almost impossible to open and work with in Excel anymore that I'm migrating into a database. Desipte this these files will stick around for some time as other processes depend on them. This means that I'll be reading them more than once so this needs to be quite fast and what's faster than reading the raw XML? In order to try something new I'm building it with Compose Desktop & Kotlin.
The idea is to import all the data into an SQLite database and implement other functionality in the app later. In future a proper SQL Server database should be used, but since some WebServices need to be built first, it's going to take some undefined time.
My proof-of-concept app works like this:
- I create a
ReadOnlyWorkbook - I get the first sheet as a
WorksheetReader - I import this worksheet into a database
val fileName = "C:\\path\\to\\data.xlsx"
val readOnlyWorkbook = ReadOnlyWorkbook(fileName)
val dataSheet = readOnlyWorkbook.elementAt(0)
val dbName = "C:\\path\\to\\data.sqlite"
TestDb(dbName).use {
it.populate(dataSheet)
}
The ReadOnlyWorkbook is an Iterable for WorksheetReaders. When created it opens the xlsx and initializes zip-streams and the XSSFReader. Then it initializes the SharedStringTable that internally uses an in-memory SQLite database. The SharedStringTable eagerly reads all strings. I use SQLite here because it allows me to easly dump it to a file and debug the strings if necessary. The iteration over available sheets, to find their IDs, uses XPath over the workbook.xml and looks for sheet nodes.
class ReadOnlyWorkbook(val fileName: String) : AutoCloseable, Iterable<WorksheetReader> {
private val zipFile = ZipFile(fileName)
private val source = ZipFileZipEntrySource(zipFile)
private val pkg: OPCPackage = ZipPackage.open(source)
private val reader = XSSFReader(pkg)
private val sharedStrings by lazy { SharedStringTable(":memory:", reader.sharedStringsData) }
override fun close() {
pkg.close()
source.close()
zipFile.close()
}
override fun iterator() = object : Iterator<WorksheetReader> {
private val sheets: NodeList
init {
val expression = "/xl:workbook/xl:sheets/xl:sheet"
val xPath = XPathFactory.newInstance().newXPath().apply {
namespaceContext = SimpleNamespaceContext().also {
it.addNamespace("xl", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
}
}
val document = DocumentHelper.readDocument(reader.workbookData)
sheets = xPath.compile(expression).evaluate(document, XPathConstants.NODESET) as NodeList
}
private var index = 0
override fun hasNext() = index < sheets.length - 1
override fun next(): WorksheetReader {
val sheetElement = sheets.item(index++)
val id = sheetElement.attributes.getNamedItemNS("http://schemas.openxmlformats.org/officeDocument/2006/relationships", "id")
val sheetStream = reader.getSheet(id.nodeValue)
return WorksheetReader(sheetElement.attributes.getNamedItem("name").nodeValue, sheetStream, sharedStrings)
}
}
}
// https://learn.microsoft.com/en-us/dotnet/standard/data/xml/xpath-queries-and-namespaces
internal class SimpleNamespaceContext : NamespaceContext {
private val urisByPrefix: MutableMap<String, String> = HashMap()
private val prefixesByURI: MutableMap<String, MutableSet<String>> = HashMap()
init {
//addNamespace(XMLConstants.XML_NS_PREFIX, XMLConstants.XML_NS_URI)
//addNamespace(XMLConstants.XMLNS_ATTRIBUTE, XMLConstants.XMLNS_ATTRIBUTE_NS_URI)
}
fun addNamespace(prefix: String, namespaceURI: String) {
urisByPrefix[prefix] = namespaceURI
if (prefixesByURI.containsKey(namespaceURI)) {
prefixesByURI[namespaceURI]!!.add(prefix)
} else {
val set: MutableSet<String> = HashSet()
set.add(prefix)
prefixesByURI[namespaceURI] = set
}
}
override fun getNamespaceURI(prefix: String): String {
return if (urisByPrefix.containsKey(prefix)) urisByPrefix[prefix] as String else XMLConstants.NULL_NS_URI
}
override fun getPrefix(namespaceURI: String): String {
return getPrefixes(namespaceURI).next()
}
override fun getPrefixes(namespaceURI: String): Iterator<String> {
return if (prefixesByURI.containsKey(namespaceURI)) {
prefixesByURI[namespaceURI] as Iterator<String>
} else {
Collections.emptyIterator()
}
}
}
The SharedStringTable extracts text from each t node and inserts them in the same sequence into the database. I randomly picked 25k as the batch size... it's a pretty number. The imported data is commited once at the and for performance reasons. This class has only method, the stringAt that allows other modules later get the referenced string.
class SharedStringTable(dbName: String, sharedStringsData: InputStream) {
private val sqliteConnection = DriverManager.getConnection("jdbc:sqlite:$dbName").apply {
autoCommit = false // inserts are faster when not auto-committed each time
}!!
init {
if (dbName == ":memory:") {
val createTable = """
create table if not exists shared_string(
string_index integer primary key autoincrement,
string_value text
);
""".trimIndent()
with(sqliteConnection.prepareStatement(createTable)) {
executeUpdate()
}
sqliteConnection.commit()
}
val insert = "INSERT INTO shared_string(string_value) VALUES (?)"
val stmt = sqliteConnection.prepareStatement(insert)
val xmlInputFactory = XMLInputFactory.newInstance()
val batchSize = 25_000
var counter = 0
val sstEventReader = xmlInputFactory.createXMLEventReader(sharedStringsData)
val text = StringBuilder()
while (sstEventReader.hasNext()) {
val xmlEvent = sstEventReader.nextEvent()
when (xmlEvent.eventType) {
XMLEvent.START_ELEMENT -> {
when (xmlEvent.asStartElement().name.localPart) {
XLSXElements.SharedStrings.STRING_ITEM -> counter += 1
}
}
XMLEvent.END_ELEMENT -> {
when (xmlEvent.asEndElement().name.localPart) {
XLSXElements.SharedStrings.STRING_ITEM -> {
with(stmt) {
clearParameters()
setString(1, text.toString())
addBatch()
if (counter % batchSize == 0) {
executeBatch()
clearBatch()
}
}
text.clear()
}
}
}
XMLEvent.CHARACTERS -> {
text.append(xmlEvent.asCharacters().toString())
}
}
}
// execute the last batch
stmt.executeBatch()
stmt.clearBatch()
sqliteConnection.commit()
}
private val select = "select string_value from shared_string where string_index = ?"
private val stmt = sqliteConnection.prepareStatement(select)
fun stringAt(index: Int): String {
with(stmt) {
clearParameters()
setInt(1, index + 1) // +1 because the id is 1-based
val result = executeQuery()
while (result.next()) {
return result.getString("string_value")
}
}
throw IndexOutOfBoundsException("There is no string with index $index.")
}
}
The WorksheetReader is a Sequence that enumerates rows as Map<Int, Cell>. Its internal Iterator's hasNext() proceeds to the next opening row element and stops there if it finds one and it then returns true. The result is cached until the row is consumed by next(). next() collects all the cells (c elements) and returns them.
class WorksheetReader(val name: String, inputStream: InputStream, private val sharedStrings: SharedStringTable) : AutoCloseable, Sequence<Map<Int, Cell>> {
private val sstEventReader: XMLEventReader
init {
val xmlInputFactory = XMLInputFactory.newInstance()
sstEventReader = xmlInputFactory.createXMLEventReader(inputStream)
}
override fun close() {
sstEventReader.close()
}
override fun iterator() = object : Iterator<Map<Int, Cell>> {
private var hasRow: Boolean? = null
override fun hasNext(): Boolean {
if (hasRow == null) {
hasRow = false
// proceed to the next row
while (sstEventReader.hasNext()) {
if (sstEventReader.nextEvent().let { it.isStartElement && it.asStartElement().name.localPart == "row" }) {
hasRow = true // there is one more row
break
}
}
}
return hasRow!!
}
override fun next(): Map<Int, Cell> {
val path = ArrayDeque<String>()
val cells = mutableMapOf<Int, Cell>()
var attributes = mapOf<String, String>()
var v = ""
// read cells of the current row
do {
val nextEvent = sstEventReader.nextEvent()
when (nextEvent.eventType) {
XMLEvent.START_ELEMENT -> {
when (nextEvent.asStartElement().name.localPart.also(path::addLast)) {
XLSXElements.Worksheet.CELL -> {
attributes =
nextEvent
.asStartElement()
.attributes
.asSequence()
.associate {
Pair(it.name.localPart, it.value)
}
}
}
}
XMLEvent.CHARACTERS -> {
if (path.lastOrNull() == XLSXElements.Worksheet.VALUE) {
v = nextEvent.asCharacters().toString()
}
}
XMLEvent.END_ELEMENT -> {
when (nextEvent.asEndElement().name.localPart.also { path.removeLastOrNull() }) {
XLSXElements.Worksheet.ROW -> {
hasRow = null
break
}
XLSXElements.Worksheet.CELL -> {
Cell(attributes, v, sharedStrings).also { cells[it.index] = it }
}
}
}
}
} while (sstEventReader.hasNext())
return cells
}
}
}
object XLSXElements {
object SharedStrings {
const val STRING_ITEM = "si"
const val TEXT = "t"
}
object Worksheet {
const val ROW = "row"
const val CELL = "c"
const val VALUE = "v"
}
}
Cells that the WorkseetReader produces use the SharedStringTable to return the actual value of that cell. The Cell also tries to fix some dirty dates stored as strings and turns the numeric represation of Excel's date into an date. I use the index of the column with JDBC to set their respective parameters of the insert query.
class Cell(private val attributes: Map<String, String>, private val v: String, private val sharedStrings: SharedStringTable) {
val column = columnRegex.find(attributes.getValue("r"))!!.groups[1]!!.value[0]
val index = column.code - 'A'.code
val row = rowRegex.find(attributes.getValue("r"))!!.groups[1]!!.value.toInt()
val value: String by lazy {
when (attributes["s"]) {
"3" -> {
when (attributes["t"]) {
// it's most likely a date with invalid format stored as a string; try to parse it
"s" -> {
// normalize delimiters
val normalized = sharedStrings.stringAt(v.toInt()).replace('.', '/').replace('-', '/')
when (normalized.indexOf('/')) {
2 -> LocalDate.parse(normalized, DateTimeFormatter.ofPattern("dd/MM/yyyy")).format(DateTimeFormatter.ISO_DATE)
4 -> LocalDate.parse(normalized, DateTimeFormatter.ofPattern("yyyy/MM/dd")).format(DateTimeFormatter.ISO_DATE)
else -> normalized
}
}
// excel incorrectly assumes 1900 was a leap year so make it right by subtracting 2 days
else -> excelDateMin.plusDays(v.toFloat().toLong()).minusDays(2).format(DateTimeFormatter.ISO_DATE)
}
}
else -> {
when (attributes["t"]) {
"s" -> sharedStrings.stringAt(v.toInt())
else -> v
}.trim()
}
}
}
val isEmpty = value.trim() == ""
override fun toString() = value // for debugging
private companion object {
private val excelDateMin = LocalDate.of(1900, 1, 1)
private val columnRegex = "^([A-Z])".toRegex()
private val rowRegex = "(\\d+)$".toRegex()
}
}
The last piece of code is the TestDb (not the actaul name). It uses the WorksheetReader to loop over all non-empty rows and imports each of the 18 columns into the database. Here you can see why I chose to use Map<Int, Cell> as the data structure for a row. This allows me to quickly check for empty cells and turn them into null without search queries.
class TestDb(name: String) : AutoCloseable {
private val sqliteConnection = DriverManager.getConnection("jdbc:sqlite:$name").apply {
autoCommit = false // inserts are faster when not auto-committed each time
}
fun populate(worksheetReader: WorksheetReader) {
val insert = File("sql/insert_item.sql").readText()
val batchSize = 25_000
var counter = 1
sqliteConnection.prepareStatement(insert).use { stmt ->
for (row in worksheetReader.filter { row -> row.any() && row[0]?.isEmpty == false }) {
with(stmt) {
clearParameters()
for (column in 1..18) {
setString(column, row[column - 1]?.let { if (it.isEmpty) null else it.value }) // +1 for 1-based
}
setString(19, "archive")
addBatch()
if (counter++ % batchSize == 0) {
executeBatch()
clearBatch()
println(counter) // for debugging
}
}
}
stmt.executeBatch()
stmt.clearBatch()
}
sqliteConnection.commit()
}
override fun close() {
sqliteConnection.close()
}
}
I can only post part of the schema of the items table. The repository column will be later used to find recently imported batch of data so that it can be reviewed before it's moved the archive.
create table if not exists item(
id integer primary key autoincrement,
... text,
first_name text,
last_name text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
... text,
-- <-- data from excel; meta -->
created_on text not null default current_timestamp,
updated_on text,
deleted_on text,
repository text not null, -- pending, archive
... text null,
check (repository in ('pending', 'archive'))
);
The project uses the latest Kotlin version:
plugins {
// https://github.com/JetBrains/compose-multiplatform/blob/master/VERSIONING.md#kotlin-compatibility
kotlin("jvm") version "1.9.0"
id("org.jetbrains.compose") version "1.4.3"
}
and the following Gradle dependencies:
dependencies {
implementation(compose.desktop.currentOs)
implementation("org.apache.poi:poi:5.2.3")
// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
implementation("org.apache.poi:poi-ooxml:5.2.3")
// required by both apache.poi
// https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core
implementation("org.apache.logging.log4j:log4j-core:2.20.0")
// https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc
runtimeOnly("org.xerial:sqlite-jdbc:3.42.0.0")
// https://mvnrepository.com/artifact/org.jetbrains.kotlinx/kotlinx-datetime
runtimeOnly("org.jetbrains.kotlinx:kotlinx-datetime:0.4.0")
}
I'd say the performance is not bad. The code is able to import 800k rows under 30s into an SQLite database, but if you have any ideas for additional optimizations or suggestions of any kind, I'd be happy to hear about them.
