Membaca File Excel Dengan Groovy


Salah satu library yang paling terkenal untuk membaca file Excel melalui Java adalah Apache POI. Tentu saja pengguna Groovy juga dapat memakai library tersebut untuk membaca file Excel yang masih dalam format binary (berakhiran xls). Akan tetapi, Microsoft Excel mulai memperkenalkan format XML sejak dirilisnya Microsoft Office XP. Format dokumen Excel yang diadopsi sejak Microsoft Office 2007 adalah Office Open XML (berakhir xlsx). Lalu, apa dampak peralihan format binary ke dalam bentuk XML? Perubahan format ini membuat saya bisa membaca file xlsx yang dibuat dengan Excel 2007 ke atas dengan gampang asalkan bahasa yang dipakai dapat membaca file XML. Sebagai contoh, saya bisa membaca file xlsx di Groovy melalui XmlSlurper-nya tanpa melibatkan library tambahan.

Sebuah file xlsx (Excel 2007 ke atas) adalah file yang mengikuti spesifikasi Open Packaging Conventions (OPC). Dengan kata lain, file xlsx pada dasarnya adalah sebuah arsip yang terkompresi dengan menggunakan format ZIP. Sebagai contoh, bila saya membuka file sample.xlsx dengan menggunakan 7-Zip, saya akan menemukan isi arsip seperti berikut ini:

Isi file xlsx yang dibuat oleh Microsoft Excel

Isi file xlsx yang dibuat oleh Microsoft Excel

File [Content_Types].xml berisi daftar seluruh XML yang ada dan jenis-nya (ContentType). Karena file xlsx adalah file ZIP, maka saya bisa membacanya dengan menggunakan class ZipFile bawaan Java. Untuk membuktikannya, saya dapat membuka Groovy Console dan mengerjakan perintah berikut ini:

Membaca XML melalui Groovy

Membaca XML melalui Groovy

Setiap file yang berada dalam arsip ZIP tersebut disebut sebagai part (karena pada dasarnya secara logika hanya ada 1 file xlsx). Workbook di Excel dideskripsikan dalam sebuah part dengan format XML yang disebut sebagai SpreadsheetML. Untuk mendapatkan lokasi part yang berisi workbook, saya perlu mencarinya di [Content_Types].xml. Saya bisa membaca XML tersebut dengan XmlSlurper dari Groovy. Sebagai contoh, kode program berikut akan mencari workbook di [Content_Types].xml:

import java.util.zip.ZipFile

ZipFile zipFile = new ZipFile('C:/sample.xlsx')
def contentTypesStream = zipFile.getInputStream(zipFile.getEntry('[Content_Types].xml'))
def typesXml = new XmlSlurper().parse(contentTypesStream)
def contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml' 
def workbook = typesXml.Override.find { it.@ContentType.text() == contentType }.@PartName.text()

Output:

/xl/workbook.xml

Seperti apa isi part /xl/workbook.xml tersebut? Berikut adalah contoh isi part tersebut:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9302"/>
    <workbookPr filterPrivacy="1" defaultThemeVersion="124226"/>
    <bookViews>
        <workbookView xWindow="240" yWindow="120" windowWidth="9555" windowHeight="5700"/>
    </bookViews>
    <sheets>
        <sheet name="student" sheetId="1" r:id="rId1"/>
        <sheet name="teacher" sheetId="2" r:id="rId2"/>
        <sheet name="classroom" sheetId="3" r:id="rId3"/>
    </sheets>
    <definedNames>
        <definedName name="LOCAL_MYSQL_DATE_FORMAT" hidden="1">REPT(LOCAL_YEAR_FORMAT,4)&LOCAL_DATE_SEPARATOR&REPT(LOCAL_MONTH_FORMAT,2)&LOCAL_DATE_SEPARATOR&REPT(LOCAL_DAY_FORMAT,2)&"
            "&REPT(LOCAL_HOUR_FORMAT,2)&LOCAL_TIME_SEPARATOR&REPT(LOCAL_MINUTE_FORMAT,2)&LOCAL_TIME_SEPARATOR&REPT(LOCAL_SECOND_FORMAT,2)
        </definedName>
    </definedNames>
    <calcPr calcId="144525"/>
</workbook>

Setiap kali membaca sebuah part, saya juga perlu membaca part lain yang disebut sebagai relationship part bila ada. Relationship part mendeklarasikan hubungan part tersebut dengan part lainnya (mirip seperti sebagai join table di database). Saya dapat menemukan relationship part pada folder dengan nama seperti _rels dimana didalamnya terdapat part dengan akhiran .rels. Sebagai contoh, relationship part untuk xl/workbook.xml adalah xl/_rels/workbook.xml.rels yang isinya terlihat seperti berikut ini:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
                  Target="worksheets/sheet3.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
                  Target="worksheets/sheet2.xml"/>
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
                  Target="worksheets/sheet1.xml"/>
    <Relationship Id="rId6" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"
                  Target="sharedStrings.xml"/>
    <Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"
                  Target="styles.xml"/>
    <Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme"
                  Target="theme/theme1.xml"/>
</Relationships>

Sekarang, saya bisa membuat kode program Groovy untuk menampilkan daftar sheet di workbook, misalnya dengan kode program seperti berikut ini:

import java.util.zip.ZipFile

ZipFile zipFile = new ZipFile('C:/sample.xlsx')
def workbookStream = zipFile.getInputStream(zipFile.getEntry('xl/workbook.xml'))
def workbookXml = new XmlSlurper().parse(workbookStream)
def referenceStream = zipFile.getInputStream(zipFile.getEntry('xl/_rels/workbook.xml.rels'))
def referenceXml = new XmlSlurper().parse(referenceStream)

workbookXml.sheets.sheet.each {
    def rId = it.@'r:id'.text()
    def targetSheet = 'xl/' + referenceXml.Relationship.find { it.@Id.text() == rId }.@Target.text()
    println "Menemukan sheet ${it.@name.text()} dengan referensi $rId ($targetSheet)"    
}

Output:

Menemukan sheet student dengan referensi rId1 (xl/worksheets/sheet1.xml)
Menemukan sheet teacher dengan referensi rId2 (xl/worksheets/sheet2.xml)
Menemukan sheet classroom dengan referensi rId3 (xl/worksheets/sheet3.xml)

Masing-masing sheet diwakili oleh part (file XML) tersendiri. Sebagai contoh, seandainya saya membuat sheet dengan isi seperti berikut ini:

Contoh isi file yang dibuat dengan Microsoft Excel

Contoh isi file yang dibuat dengan Microsoft Excel

maka worksheet tersebut akan disimpan dalam bentuk XML seperti berikut ini:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
           xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
           xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac"
           xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1:D4"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="D2" sqref="D2"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <cols>
        <col min="4" max="4" width="13.85546875" customWidth="1"/>
    </cols>
    <sheetData>
        <row r="1" spans="1:4" x14ac:dyDescent="0.25">
            <c r="A1" t="s">
                <v>0</v>
            </c>
            <c r="B1" t="s">
                <v>1</v>
            </c>
            <c r="C1" t="s">
                <v>2</v>
            </c>
            <c r="D1" t="s">
                <v>18</v>
            </c>
        </row>
        <row r="2" spans="1:4" x14ac:dyDescent="0.25">
            <c r="A2">
                <v>1</v>
            </c>
            <c r="B2" t="s">
                <v>3</v>
            </c>
            <c r="C2">
                <v>30</v>
            </c>
            <c r="D2" s="1">
                <v>31119</v>
            </c>
        </row>
        <row r="3" spans="1:4" x14ac:dyDescent="0.25">
            <c r="A3">
                <v>2</v>
            </c>
            <c r="B3" t="s">
                <v>4</v>
            </c>
            <c r="C3">
                <v>28</v>
            </c>
            <c r="D3" s="1">
                <v>31825</v>
            </c>
        </row>
        <row r="4" spans="1:4" x14ac:dyDescent="0.25">
            <c r="A4">
                <v>3</v>
            </c>
            <c r="B4" t="s">
                <v>5</v>
            </c>
            <c r="C4">
                <v>25</v>
            </c>
            <c r="D4" s="1">
                <v>16689</v>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
    <pageSetup paperSize="9" orientation="portrait" r:id="rId1"/>
    <tableParts count="1">
        <tablePart r:id="rId2"/>
    </tableParts>
</worksheet>

Setiap baris di sheet diwakili oleh <row>. Setiap baris memiliki satu atau lebih <c> yang mewakili sel. Sebuah <c> memiliki nilai yang didefinisikan oleh <v>. Karena semua ini didefinisikan dalam bentuk XML, maka saya dapat membacanya melalui XmlSlurper seperti pada contoh kode program Groovy berikut ini:

import java.util.zip.ZipFile

ZipFile zipFile = new ZipFile('C:/sample.xlsx')
def sheet1Stream = zipFile.getInputStream(zipFile.getEntry('xl/worksheets/sheet1.xml'))
def sheet1Xml = new XmlSlurper().parse(sheet1Stream)
def referenceStream = zipFile.getInputStream(zipFile.getEntry('xl/worksheets/_rels/sheet1.xml.rels'))
def referenceXml = new XmlSlurper().parse(referenceStream)

sheet1Xml.sheetData.row.each { row ->
    row.c.each { c ->
        print "${c.v.text()}; "
    }
    println ''
}

Output:

0; 1; 2; 18; 
1; 3; 30; 31119; 
2; 4; 28; 31825; 
3; 5; 25; 16689;

Ada yang aneh, bukan? Teks yang saya ketik tidak muncul melainkan diganti dengan angka. Selain itu, kolom untuk tanggal juga berada dalam bentuk numerik. Hanya kolom ke-3 yang memang dalam bentuk angka yang berhasil dibaca dengan baik.

Untuk menghemat ukuran file, Excel menyimpan seluruh teks (string) yang unik pada xl/sharedStrings.xml. Bila atribut t dari <c> bernilai s, maka hal tersebut berarti nilai dari <v> adalah referensi (berdasarkan indeks) ke teks yang ada di xl/sharedStrings.xml. Contoh isi xl/sharedStrings.xml adalah:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="23" uniqueCount="19">
    <si>
        <t>id</t>
    </si>
    <si>
        <t>name</t>
    </si>
    <si>
        <t>age</t>
    </si>
    <si>
        <t>jocki</t>
    </si>
    <si>
        <t>lena</t>
    </si>
    ...
</sst>

Dengan demikian, agar dapat menampilkan teks, saya perlu mengubah kode program saya menjadi seperti berikut ini:

import java.util.zip.ZipFile

ZipFile zipFile = new ZipFile('C:/sample.xlsx')
def sheet1Stream = zipFile.getInputStream(zipFile.getEntry('xl/worksheets/sheet1.xml'))
def sheet1Xml = new XmlSlurper().parse(sheet1Stream)
def referenceStream = zipFile.getInputStream(zipFile.getEntry('xl/worksheets/_rels/sheet1.xml.rels'))
def referenceXml = new XmlSlurper().parse(referenceStream)
def sharedStringStream = zipFile.getInputStream(zipFile.getEntry('xl/sharedStrings.xml'))
def sharedStringXml = new XmlSlurper().parse(sharedStringStream)

sheet1Xml.sheetData.row.each { row ->
    row.c.each { c ->
        def nilai = c.v.text()
        if (c.@t.text() == 's') {
            nilai = sharedStringXml.si[nilai as Integer].t.text()
        }
        print "${nilai}; "
    }
    println ''
}

Output:

id; name; age; birthdate; 
1; jocki; 30; 31119; 
2; lena; 28; 31825; 
3; snake; 25; 16689;

Teks sudah berhasil dibaca dengan baik. Lalu, bagaimana dengan tanggal? Untuk mendeteksi sebuah kolom berupa tanggal atau bukan ternyata tidak mudah! Saya harus mulai dengan memeriksa nilai atribut s dari <c> bersangkutan. Nilai ini adalah referensi ke index <xf> yang ada di tag <cellXfs> di part xl/styles.xml. Contoh isi file xl/styles.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
            xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac"
            xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    ...
    <cellXfs count="2">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
        <xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    </cellXfs>
    ...
</styleSheet>

Bila nilai numFmtId di <xf> berada di kisaran 14 sampai 22, maka sel tersebut memakai format tanggal bawaan. Akan tetapi bila nilai numFmtId di atas 163, maka sel tersebut di-format secara custom dimana format tersebut tersimpan di <numFmts>. Agar sederhana, saya hanya akan mengenali format tanggal bawaan Microsoft Excel dengan mengubah kode program saya menjadi seperti berikut ini:

import java.util.zip.ZipFile

ZipFile zipFile = new ZipFile('C:/sample.xlsx')
def sheet1Stream = zipFile.getInputStream(zipFile.getEntry('xl/worksheets/sheet1.xml'))
def sheet1Xml = new XmlSlurper().parse(sheet1Stream)
def referenceStream = zipFile.getInputStream(zipFile.getEntry('xl/worksheets/_rels/sheet1.xml.rels'))
def referenceXml = new XmlSlurper().parse(referenceStream)
def sharedStringStream = zipFile.getInputStream(zipFile.getEntry('xl/sharedStrings.xml'))
def sharedStringXml = new XmlSlurper().parse(sharedStringStream)
def styleStream = zipFile.getInputStream(zipFile.getEntry('xl/styles.xml'))
def styleXml = new XmlSlurper().parse(styleStream)

sheet1Xml.sheetData.row.each { row ->
    row.c.each { c ->
        def nilai = c.v.text()
        if (c.@t.text() == 's') {
            nilai = sharedStringXml.si[nilai as Integer].t.text()
        } else if (!c.@s.isEmpty()) {
            def numFmtId = styleXml.cellXfs.xf[c.@s.text() as Integer].@numFmtId.text()
            if ((14..22).contains(numFmtId as Integer)) {
                Calendar cal = Calendar.getInstance()
                cal.set(1900, 0, (nilai as Integer) - 1, 0, 0, 0)
                nilai = cal.getTime()
            }
        }
        print "${nilai}; "
    }
    println ''
}

Output:

id; name; age; birthdate; 
1; jocki; 30; Wed Mar 13 00:00:00 ICT 1985; 
2; lena; 28; Tue Feb 17 00:00:00 ICT 1987; 
3; snake; 25; Sun Sep 09 00:00:00 ICT 1945;

Pada kode program di atas, kerumitan lain yang saya jumpai adalah mengubah nilai tanggal dari Excel menjadi nilai tanggal di Java. Excel menyimpan tanggal sebagai selisih hari sejak tanggal 0 Januari 1900 (yup, itu sebabnya saya perlu -1 di kode program). Bagian desimal-nya (dibelakang koma) akan dianggap informasi waktu (jam, menit dan detik). Sementara itu, Java menyimpan tanggal sebagai selisih detik sejak 1 Januari 1970 (disebut juga Unix time) dalam satuan milidetik (ms).

Bila ingin membaca bagian waktu (jam dan menit), saya bisa meniru teknik yang dipakai oleh org.apache.poi.ss.usermodel.DateUtil milik Apache POI, misalnya dengan membuat kode proram seperti berikut ini:

...
value = value as Double
int days = (int) Math.floor(value)
int time = (int)((value - days) * 86400 + 0.5) * 1000
Calendar cal = Calendar.getInstance()
cal.set(1900, 0, (value as Integer) - 1, 0, 0, 0)
cal.set(Calendar.MILLISECOND, time)
value = cal.getTime()

Perihal Solid Snake
I'm nothing...

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: