Bongkar data di Excel. Beradab

Ada tugas-tugas di industri TI yang, dengan latar belakang kesuksesan dalam data besar , pembelajaran mesin , blockchain, dan tren mode lainnya, terlihat benar-benar tidak menarik, tetapi selama beberapa dekade tidak berhenti menjadi relevan untuk seluruh pasukan pengembang. Ini akan menjadi yang lama sebagai tugas dunia menciptakan dan mengunggah dokumen Excel, yang dihadapi semua orang yang pernah menulis aplikasi untuk bisnis.





Apa kemungkinan membangun file Excel pada prinsipnya?

1. Makro VBA. Saat ini, untuk alasan keamanan, ide menggunakan makro paling sering tidak cocok.
2. Otomasi Excel dengan program eksternal melalui API. Membutuhkan Excel di mesin yang sama dengan program yang menghasilkan laporan Excel. Pada saat klien tebal dan ditulis dalam bentuk aplikasi Windows desktop, metode ini cocok (meskipun tidak berbeda dalam kecepatan dan keandalan), dalam kenyataan saat ini ini hampir tidak dapat dicapai.
3. Hasilkan file XML-Excel secara langsung. Seperti yang Anda ketahui, Excel mendukung format XML untuk menyimpan dokumen, yang berpotensi dapat dihasilkan / dimodifikasi menggunakan cara apa pun yang bekerja dengan XML. File ini dapat disimpan dengan ekstensi .xls, dan meskipun, sebenarnya bukan file xls, Excel membukanya dengan baik. Pendekatan ini cukup populer, tetapi kelemahannya mencakup fakta bahwa solusi apa pun yang didasarkan pada pengeditan langsung format XML-Excel adalah "hack" sekali saja tanpa generalisasi.
4. Akhirnya, dimungkinkan untuk menghasilkan file Excel menggunakan pustaka sumber terbuka, di mana Apache POI sangat dikenal. Pengembang POI Apache telah melakukan pekerjaan besar pada rekayasa balik format dokumen MS Office biner, dan terus mempertahankan dan mengembangkan perpustakaan ini selama bertahun-tahun. Hasil rekayasa terbalik ini, misalnya, digunakan di Open Office untuk mengimplementasikan pelestarian dokumen dalam format yang kompatibel dengan MS Office.

Menurut pendapat saya, ini adalah metode terakhir yang sekarang lebih disukai untuk menghasilkan dokumen yang kompatibel dengan MS Office. Di satu sisi, itu tidak memerlukan instalasi perangkat lunak berpemilik di server, dan di sisi lain, itu menyediakan API kaya yang memungkinkan Anda untuk menggunakan semua fungsi MS Office.

Tetapi penggunaan langsung Apache POI memiliki kelemahan. Pertama, ini adalah perpustakaan Java, dan jika aplikasi Anda ditulis dalam lebih dari satu bahasa JVM, Anda tidak dapat menggunakannya. Kedua, itu adalah perpustakaan tingkat rendah yang bekerja dengan konsep-konsep seperti "sel", "kolom", "font". Oleh karena itu, “secara langsung” prosedur tertulis untuk menghasilkan dokumen dengan cepat berubah menjadi “mie” kode yang sulit dibaca, di mana tidak ada pemisahan menjadi model data dan presentasi, sulit untuk membuat perubahan dan, secara umum, rasa sakit dan malu. Dan kesempatan yang bagus untuk mendelegasikan tugas ke programmer yang paling tidak berpengalaman - biarkan dia memilih.

Tapi itu bisa sangat berbeda. Proyek Gambang di bawah LGPL, dibangun berdasarkan Apache POI, didasarkan pada ide yang diperkirakan memiliki sejarah 15 tahun. Dalam proyek di mana saya berpartisipasi, itu digunakan dalam kombinasi dengan berbagai platform dan bahasa - dan, karena berbagai bentuk yang dibuat dengan bantuannya dalam berbagai proyek, mungkin sudah ada ribuan. Ini adalah proyek Java yang dapat berfungsi baik sebagai utilitas baris perintah dan sebagai perpustakaan (jika Anda memiliki kode dalam bahasa JVM, Anda dapat menghubungkannya sebagai ketergantungan Maven).

Gambang menerapkan prinsip memisahkan model data dari penyajiannya. Dalam prosedur pengunggahan, Anda perlu membuat data dalam format XML (tanpa khawatir tentang sel, font dan garis pemisah), dan Xylophone, menggunakan templat Excel dan deskriptor yang menjelaskan cara merayapi file XML Anda dengan data, akan menghasilkan hasilnya, seperti yang ditunjukkan pada diagram:


Templat dokumen (templat xls / xlsx) terlihat seperti ini:


Sebagai aturan, pengadaan template semacam itu disediakan oleh pelanggan. Pelanggan yang terlibat dengan senang hati mengambil bagian dalam pembuatan templat: mulai dari memilih formulir yang tepat dari "Konsultan" atau menciptakan satu dari awal, dan diakhiri dengan ukuran font dan lebar garis pemisah. Keuntungan dari templat ini adalah pengeditan kecil untuk membuatnya mudah dilakukan bahkan ketika laporan sepenuhnya dikembangkan.

Ketika pekerjaan "desain" selesai, pengembang tetap

1. Buat prosedur untuk mengunduh data yang diperlukan dalam format XML.
2. Buat deskriptor yang menjelaskan prosedur untuk menelusuri elemen file XML dan menyalin fragmen templat ke laporan yang dihasilkan
3. Ikat sel template ke elemen file XML menggunakan ekspresi XPath .

Dengan mengunggah ke XML, semuanya kurang lebih jelas: cukup pilih representasi XML yang sesuai dari data yang diperlukan untuk mengisi formulir. Apa itu deskriptor?

Jika formulir yang kami buat tidak memiliki elemen duplikat dengan angka yang berbeda (seperti garis faktur, yang berbeda dalam faktur yang berbeda), maka deskriptor akan terlihat seperti ini:

<element name="root"> <output range="A1:Z100"/> </element> 

Root di sini adalah nama elemen root dari file data XML kami, dan range A1: Z100 adalah rentang sel persegi dari templat yang akan disalin ke hasilnya. Selain itu, seperti yang dapat dilihat dari ilustrasi sebelumnya, bidang wildcard yang nilainya diganti dengan data dari file XML memiliki format ~{XPath-} (tilde, curly bracket, ekspresi XPath relatif terhadap elemen XML saat ini, menutup bracket curly).

Bagaimana jika kita perlu mengulang elemen dalam laporan? Secara alami, mereka dapat direpresentasikan sebagai elemen dari file data XML, dan deskriptor membantu untuk membantunya dengan cara yang benar. Pengulangan elemen dalam laporan dapat memiliki arah vertikal (misalnya, ketika kami menyisipkan garis faktur), dan horizontal (saat kami menyisipkan kolom laporan analitis). Pada saat yang sama, kita bisa menggunakan nesting elemen XML untuk merefleksikan nesting deep berulang dari elemen laporan berulang, seperti yang ditunjukkan dalam diagram:


Kotak merah menandai sel-sel yang akan menjadi sudut kiri atas fragmen persegi panjang berikutnya yang dilaporkan oleh generator laporan.

Ada opsi lain yang mungkin untuk elemen berulang: lembar dalam buku kerja Excel. Kemampuan untuk mengatur iterasi semacam itu juga tersedia.

Pertimbangkan contoh yang sedikit lebih rumit. Misalkan kita perlu mendapatkan ringkasan laporan seperti berikut:


Biarkan pengguna memilih rentang tahun untuk dibongkar, oleh karena itu baris dan kolom dibuat secara dinamis dalam laporan ini. Representasi XML dari data untuk laporan seperti itu mungkin terlihat seperti ini:


Kami bebas memilih nama tag yang Anda sukai, strukturnya juga bisa sewenang-wenang, tetapi dengan memperhatikan kemudahan konversi ke laporan. Sebagai contoh, saya biasanya menulis nilai-nilai yang ditampilkan pada sheet ke dalam atribut, karena ini menyederhanakan ekspresi XPath (akan lebih mudah jika terlihat seperti @ ).

Template untuk laporan seperti itu akan terlihat seperti ini (bandingkan ekspresi XPath dengan nama atribut dari tag yang sesuai):


Sekarang sampai pada bagian yang paling menarik: membuat pegangan. Karena ini adalah laporan yang dirakit secara dinamis sepenuhnya, deskriptor agak rumit, dalam praktiknya (ketika kita hanya memiliki "header" dari dokumen, baris dan "footer") semuanya biasanya jauh lebih sederhana. Berikut adalah uraian yang diperlukan dalam kasus ini:


Elemen-elemen deskriptor sepenuhnya dijelaskan dalam dokumentasi . Singkatnya, elemen dasar dari deskriptor berarti yang berikut:

• elemen - transisi ke mode membaca elemen file XML. Ini bisa menjadi elemen dasar dari deskriptor, atau berada di dalam iteration . Atribut name dapat digunakan untuk mengatur berbagai filter untuk elemen, misalnya
  • name="foo" - elemen dengan nama tag foo
  • name="*" - semua elemen
  • name="tagname[@attribute='value']" - elemen dengan nama dan nilai atribut tertentu
  • name="(before)" , name="(after)" - "virtual" elemen yang mendahului iterasi dan tutup iterasi.
• iterasi - transisi ke mode iterasi. Itu hanya bisa di dalam element . Berbagai parameter dapat diatur, mis.
  • mode="horizontal" - mode keluaran mode="horizontal" (vertikal secara default)
  • index=0 - batasi iterasi ke elemen pertama yang ditemukan
• output - beralih ke mode keluaran. Atribut utama adalah sebagai berikut:
  • sourcesheet - Lembar buku templat tempat rentang keluaran diambil. Jika tidak ditentukan, maka lembar saat ini (yang terakhir digunakan) diterapkan.
  • range - kisaran templat yang akan disalin ke dokumen yang dihasilkan, misalnya, "A1: M10", atau "5: 6", atau "C: C". (Menggunakan rentang baris tipe "5: 6" dalam mode output horizontal dan rentang kolom tipe "C: C" dalam mode output vertikal akan menyebabkan kesalahan).
  • worksheet - jika ditentukan, lembar baru dibuat di file output dan posisi output bergeser ke sel A1 lembar ini. Nilai dari atribut ini, sama dengan ekspresi konstan atau XPath, diganti dengan nama sheet baru.

Pada kenyataannya, ada lebih banyak opsi dalam deskriptor, lihat dokumentasi.

Nah, sekarang saatnya untuk mengunduh Xylophone dan mulai melaporkan.
Ambil arsip dari bintray atau Maven Central (NB: pada saat membaca artikel ini, versi yang lebih baru dimungkinkan). Di folder / bin ada skrip shell, ketika Anda menjalankannya tanpa parameter, Anda akan melihat prompt tentang parameter baris perintah. Untuk mendapatkan hasilnya, kita perlu "memberi makan" ke gambang semua bahan yang disiapkan sebelumnya:

 xylophone -data testdata.xml -template template.xlsx -descr descriptor.xml -out report.xlsx 

Buka file report.xlsx dan pastikan bahwa kami mendapatkan apa yang kami butuhkan:


Karena ru.curs: perpustakaan gambang tersedia di Maven Central di bawah lisensi LGPL, perpustakaan ini dapat digunakan tanpa masalah dalam program dalam bahasa JVM apa pun. Mungkin contoh yang berfungsi sepenuhnya paling ringkas diperoleh di Groovy, kode tidak perlu komentar:

 @Grab('ru.curs:xylophone:6.1.3') import ru.curs.xylophone.XML2Spreadsheet baseDir = '.' new File(baseDir, 'testdata.xml').withInputStream { input -> new File(baseDir, 'report.xlsx').withOutputStream { output -> XML2Spreadsheet.process(input, new File(baseDir, 'descriptor.xml'), new File(baseDir, 'template.xlsx'), false, output) } } println 'Done.' 

Kelas XML2Spreadsheet memiliki beberapa versi berlebihan dari metode process statis, tetapi mereka semua bermuara untuk mentransfer "bahan" yang sama yang diperlukan untuk menyiapkan laporan.

Opsi penting yang belum saya sebutkan adalah kemampuan untuk memilih antara parser DOM dan SAX pada tahap parsing file dengan data XML. Seperti yang Anda ketahui, parser DOM memuat seluruh file ke dalam memori, membangun representasi objeknya dan memungkinkan untuk mem-bypass isinya dengan cara sewenang-wenang (termasuk berulang kali kembali ke elemen yang sama). Parser SAX tidak pernah menempatkan seluruh file data ke dalam memori, melainkan memprosesnya sebagai "aliran" elemen, mencegahnya kembali ke elemen lagi.

Menggunakan mode SAX dalam Xylophone (melalui opsi -sax command-line atau mengatur parameter useSax dari metode useSax menjadi XML2Spreadsheet.process ) dapat sangat berguna ketika Anda perlu membuat file yang sangat besar. Karena kecepatan dan profitabilitas sumber daya parser SAX, kecepatan pembuatan file meningkat beberapa kali. Ini diberikan dengan mengorbankan beberapa pembatasan kecil pada deskriptor (dijelaskan dalam dokumentasi), tetapi dalam kebanyakan kasus laporan memenuhi batasan ini, jadi saya akan merekomendasikan menggunakan mode SAX sedapat mungkin.

Saya harap Anda menyukai metode mengunggah ke Excel melalui Xylophone dan akan menghemat banyak waktu dan saraf - karena Anda menyelamatkan kami.

Dan akhirnya, tautkan lagi:

• sumber ada di sini: github.com/CourseOrchestra/xylophone
• Dokumentasinya ada di sini: courseorchestra.imtqy.com/xylophone
• semua contoh kode dari artikel ini ada di sini: github.com/inponomarev/xylophone-example .