cfspreadsheet: baca dan tulis fail Excel dalam Lucee

cfspreadsheet ialah tag Adobe ColdFusion untuk membaca, menulis dan mengemas kini fail Excel. Dalam Lucee 7 tag ini tidak disertakan dalam enjin teras, jadi kerja Excel dilakukan melalui pustaka spreadsheet-cfml oleh cfsimplicity. Artikel ini menunjukkan cara memasang pustaka itu, menulis query daripada pangkalan data ke fail .xlsx, membacanya semula, mengemas kini kandungan, dan memberi gaya kepada sel. Semua contoh kod di bawah telah dijalankan pada Lucee 7.1 dan lulus.

Apa itu cfspreadsheet dan statusnya dalam Lucee

Dalam Adobe ColdFusion, cfspreadsheet ialah tag teras dengan atribut action untuk memilih operasi seperti read, write dan update. Pembangun yang berhijrah dari ColdFusion selalunya mencari tag yang sama dalam Lucee dan mendapati ia tiada.

Dokumentasi rasmi Lucee menyatakan perkara ini secara terus: tag <cfspreadsheet> tidak disokong oleh Lucee, dan sokongan pihak ketiga tersedia melalui pustaka spreadsheet-cfml serta sambungan cfspreadsheet-lucee yang lebih lama. Apabila anda menulis <cfspreadsheet> dalam Lucee 7, enjin memulangkan ralat undefined tag [cfspreadsheet]. Begitu juga fungsi spreadsheetNew(), spreadsheetRead() dan spreadsheetWrite() tidak wujud dalam teras dan memulangkan No matching function [SPREADSHEETNEW] found.

Ini bukan bermakna Lucee tidak boleh bekerja dengan Excel. Ia bermakna laluan kerjanya berbeza: tag dan fungsi terbina itu digantikan oleh pustaka mandiri spreadsheet-cfml, yang meniru hampir semua fungsi spreadsheet ColdFusion dengan sintaks yang serupa.

Kenapa Lucee mengeluarkan spreadsheet daripada teras

Lucee memindahkan banyak ciri ke sambungan atau pustaka supaya enjin teras kekal kecil dan fleksibel. Kerja spreadsheet bergantung pada Apache POI, perpustakaan Java yang besar, jadi mengekalkannya di luar teras membenarkan Lucee dikemas kini tanpa terikat kepada versi POI tertentu.

Kesan praktikalnya: pemasangan Lucee 7 yang bersih tidak boleh membaca atau menulis fail Excel tanpa menambah pustaka. Ini berbeza dengan tag fail seperti cffile, yang berada dalam teras dan dibincangkan dalam cffile: baca, tulis, salin dan pindah fail. Sebelum menulis sebarang kod Excel, sahkan dahulu apa yang tersedia pada pelayan anda, sama seperti anda menyemak konfigurasi datasource sebelum menulis query.

Memasang pustaka spreadsheet-cfml

spreadsheet-cfml bukan sambungan enjin dan bukan pakej yang perlu dipasang. Ia sebuah pustaka mandiri: anda hanya menyalin failnya ke lokasi yang boleh dicapai oleh aplikasi anda.

Fail yang diperlukan untuk Lucee ialah Spreadsheet.cfc, osgiLoader.cfc, lib-osgi.jar, serta direktori helpers dan objects. Letakkan semuanya dalam satu folder, contohnya spreadsheetCFML, kemudian cipta objek dengan laluan titik spreadsheet = New spreadsheetCFML.Spreadsheet().

Pustaka ini memuatkan Apache POI melalui OSGi pada Lucee, jadi tiada konfigurasi tambahan diperlukan. Anda juga boleh memasangnya melalui CommandBox dengan arahan box install spreadsheet-cfml. Setelah objek wujud, semua operasi dilakukan melalui kaedah pada objek itu, bukan melalui tag.

Menulis query ke fail Excel dan membacanya semula

Operasi paling biasa ialah mengeksport hasil query ke fail Excel. Kaedah writeFileFromQuery mengambil query terus dan menulisnya ke fail, tanpa perlu membina objek workbook secara manual. Contoh berikut mengambil data daripada datasource kedai, menulisnya ke fail .xlsx, kemudian membacanya semula untuk membuktikan kitaran penuh berjalan:

// Pastikan direktori sementara wujud
if (!directoryExists("/tmp/uji-xls")) {
    directoryCreate("/tmp/uji-xls");
}

spreadsheet = New spreadsheetCFML.Spreadsheet();

// Data daripada datasource kedai, jadual produk
produk = queryExecute(
    "SELECT id, nama, harga, stok FROM produk ORDER BY id",
    [], { datasource: "kedai" }
);

// Tulis query terus ke fail xlsx
spreadsheet.writeFileFromQuery(
    data = produk,
    filepath = "/tmp/uji-xls/produk.xlsx",
    overwrite = true
);

// Baca semula sebagai query CFML; headerRow=1 memakai baris pertama sebagai nama lajur
hasil = spreadsheet.read(
    src = "/tmp/uji-xls/produk.xlsx",
    format = "query",
    headerRow = 1
);

writeOutput("Ditulis: " & produk.recordCount & " baris, dibaca semula: " & hasil.recordCount & " baris");

Blok ini membuktikan kitaran penuh: query daripada pangkalan data masuk ke fail Excel, kemudian fail itu dibaca kembali sebagai query CFML. Tiada sambungan enjin diperlukan, hanya pustaka yang disalin. Sama seperti cfquery, sentiasa lindungi query anda dengan cfqueryparam apabila ia melibatkan input pengguna, seperti dijelaskan dalam cfquery dan cfqueryparam untuk cegah suntikan SQL.

Membaca fail Excel sebagai query

Untuk membaca fail Excel sedia ada, contohnya fail yang dimuat naik oleh pengguna, guna read dengan format = "query". Hasilnya ialah query CFML yang boleh digelung dan diproses seperti mana-mana query lain. Atribut headerRow memberitahu pustaka baris mana yang mengandungi nama lajur, dan queryColumnTypes memaksa lajur tertentu menjadi jenis data CFML yang betul:

spreadsheet = New spreadsheetCFML.Spreadsheet();

// Fail Excel yang ditulis oleh manusia selalunya memulangkan semuanya sebagai teks.
// queryColumnTypes memaksa lajur menjadi jenis CFML yang betul.
data = spreadsheet.read(
    src = "/tmp/uji-xls/produk.xlsx",
    format = "query",
    headerRow = 1,
    queryColumnTypes = { "id": "integer", "harga": "numeric", "stok": "integer" }
);

writeOutput("Lajur: " & data.columnList & " | harga1=" & data.harga[1] & " (numeric: " & isNumeric(data.harga[1]) & ")");

Apabila anda membaca fail yang dimuat naik pengguna, sahkan jenis dan saiz fail itu dahulu sebelum menghantarnya ke read, kerana fail Excel boleh mengandungi makro atau struktur yang tidak dijangka. Panduan lengkap mengenainya ada dalam muat naik fail dengan selamat.

Mengemas kini fail sedia ada

Kemas kini bermula dengan membuka fail sebagai objek workbook menggunakan read tanpa format, supaya anda mendapat objek yang boleh diubah. Kemudian tambah baris dengan addRows, tukar nilai sel dengan setCellValue, dan tulis semula dengan write:

spreadsheet = New spreadsheetCFML.Spreadsheet();

// Buka fail sedia ada sebagai objek workbook
wb = spreadsheet.read( src = "/tmp/uji-xls/produk.xlsx" );

// Baris baharu untuk ditambah
baru = queryNew(
    "id,nama,harga,stok",
    "integer,varchar,double,integer",
    [ [3, "Roti", 4.50, 30] ]
);
spreadsheet.addRows( workbook = wb, data = baru );

// Tulis semula ke fail baharu
spreadsheet.write( workbook = wb, filepath = "/tmp/uji-xls/produk-kemaskini.xlsx", overwrite = true );

// Sahkan bilangan baris selepas kemas kini
semak = spreadsheet.read( src = "/tmp/uji-xls/produk-kemaskini.xlsx", format = "query", headerRow = 1 );
writeOutput("Selepas kemas kini: " & semak.recordCount & " baris, baris terakhir: " & semak.nama[3]);

Objek workbook yang dipulangkan read disimpan dalam memori, jadi kemas kini pada fail besar menggunakan banyak RAM. Lebih lanjut tentang had ini di bahagian had memori di bawah.

Gaya sel dan formula

Kelebihan utama spreadsheet-cfml berbanding pendekatan CSV ialah kawalan penuh ke atas gaya dan formula. Kaedah formatRow memberi gaya kepada seluruh baris, dan setCellFormula menulis formula Excel seperti SUM:

spreadsheet = New spreadsheetCFML.Spreadsheet();

data = queryNew(
    "nama,harga",
    "varchar,double",
    [ ["Kopi", 12.5], ["Teh", 8.0] ]
);

wb = spreadsheet.new( xmlFormat = true );
spreadsheet.addRows( workbook = wb, data = data, includeQueryColumnNames = true );

// Tebalkan baris pengepala (baris 1)
spreadsheet.formatRow( workbook = wb, format = { bold = true }, row = 1 );

// Formula jumlah pada baris 4, lajur 2
spreadsheet.setCellFormula( workbook = wb, formula = "SUM(B2:B3)", row = 4, column = 2 );

spreadsheet.write( workbook = wb, filepath = "/tmp/uji-xls/gaya.xlsx", overwrite = true );
writeOutput("Fail dengan gaya dan formula ditulis.");

Pustaka ini juga menyokong format nombor, warna, jidar, pengesahan data dan pemformatan bersyarat, kesemuanya didokumenkan dalam rujukan fungsi di wiki projek.

Jadual operasi read, write dan update

Tiga operasi asas dipetakan kepada kaedah pustaka seperti berikut:

Untuk tulis yang lebih halus, guna new untuk membina workbook kosong, addRows untuk mengisi data, kemudian write. Untuk kemas kini pada satu sel, setCellValue(workbook, nilai, baris, lajur) menggantikan nilai pada kedudukan tepat.

Lajur Excel lawan jenis data CFML

Excel menyimpan sel secara longgar, manakala CFML mempunyai jenis data yang tegas. Apabila anda menulis query CFML ke fail, nombor dan tarikh biasanya kekal sebagai nombor dan tarikh kerana pustaka membaca jenis lajur query itu. Tetapi apabila anda membaca fail Excel yang ditulis oleh manusia, semuanya sering kembali sebagai teks.

Dua alat untuk mengawal jenis data ialah queryColumnTypes pada read, seperti contoh di atas, dan datatypes pada addRows atau writeFileFromQuery. Tarikh perlu perhatian khusus: Excel menyimpan tarikh sebagai nombor siri, dan pustaka menukarnya kepada objek tarikh CFML apabila jenisnya dinyatakan. Tanpa pemetaan ini, lajur tarikh boleh muncul sebagai nombor seperti 45000 dan bukannya tarikh sebenar, manakala nombor yang dibaca sebagai teks tidak boleh dijumlahkan tanpa penukaran manual.

Had memori dan fail besar

Pustaka ini memuatkan workbook ke dalam memori, sama seperti kebanyakan perpustakaan Excel. Fail dengan puluhan ribu baris boleh memakan ratusan megabait RAM, jadi jangan memproses fail muat naik yang besar tanpa had saiz yang munasabah. read dan write yang berjalan pada fail besar juga menyekat utas permintaan, yang boleh melambatkan keseluruhan aplikasi.

Untuk data yang sangat besar, pertimbangkan dua laluan. Pertama, guna newStreamingXlsx untuk menulis fail dalam mod strim supaya memori tidak meningkat mengikut bilangan baris. Kedua, kalau pengguna hanya perlu memuat turun data dan bukan fail Excel berformat, tulis CSV sahaja dengan writeCsv atau terus dengan cffile, seperti dibincangkan dalam cffile: baca, tulis, salin dan pindah fail. CSV jauh lebih ringan dan cukup untuk kebanyakan eksport data.

Soalan Lazim

Adakah tag cfspreadsheet tersedia dalam Lucee 7?

Tidak. Tag <cfspreadsheet> tidak disokong oleh Lucee dan memulangkan ralat undefined tag apabila digunakan. Kerja Excel dalam Lucee dilakukan melalui pustaka spreadsheet-cfml oleh cfsimplicity, bukan melalui tag terbina.

Bolehkah saya menggunakan fungsi spreadsheetNew dalam Lucee?

Tidak. Fungsi spreadsheetNew(), spreadsheetRead() dan spreadsheetWrite() tidak wujud dalam teras Lucee. Pustaka spreadsheet-cfml menyediakan kaedah setara seperti spreadsheet.new(), spreadsheet.read() dan spreadsheet.write() pada objeknya sendiri.

Bagaimana membaca fail Excel yang dimuat naik pengguna dengan selamat?

Sahkan jenis dan saiz fail dahulu, hadkan saiz muat naik, dan simpan fail di luar webroot sebelum menghantarnya ke read. Rujuk panduan muat naik fail yang selamat untuk langkah penuh pengesahan.

Bagaimana menulis data yang sangat besar tanpa kehabisan memori?

Guna newStreamingXlsx untuk menulis dalam mod strim, atau tulis CSV sahaja apabila format Excel tidak diperlukan. CSV jauh lebih ringan dan tidak memuatkan workbook penuh ke dalam memori.

Bagaimana memastikan lajur nombor dan tarikh tidak menjadi teks?

Nyatakan jenis dengan queryColumnTypes semasa membaca, atau datatypes semasa menulis. Ini memberitahu pustaka untuk menukar nombor siri Excel kepada tarikh dan mengekalkan nombor sebagai nombor, bukannya teks.

Sumber

• Dokumentasi tag cfspreadsheet, Lucee
• Excel Processing in Lucee 7, dokumentasi Lucee
• spreadsheet-cfml, cfsimplicity di GitHub
• Rujukan fungsi spreadsheet-cfml, wiki projek