透過Java解析Excel檔案
也是一個工作中遇到的情境,很多單位想讓內部人員利用Excel維護資料(因為維護上比較簡單方便),且想讓這份資料透過網頁呈現給一般網友閱讀,所以整體後端的流程應該是:
- 將維護的Excel透過網頁介面上傳
- 將上傳的Excel轉換成XML
- 網頁去讀取這份XML來呈現
透過以上的三個步驟,就可以完成客戶想要效果。
本範例展示的是讀取Excel的XLS格式(新版Excel為XLSX格式),並轉換成陣列的方法(寫入XML在前面的範例就有了),我們選用讀取Excel的Library是Apache POI,而檔內已經有以下的資料,其中Total是公式欄位,計算Price x Quantity:
ID | Name | Price | Quantity | Total |
100001 | 產品名稱1 | 10 | 5 | 50 |
100002 | 產品名稱2 | 20 | 10 | 200 |
100003 | 產品名稱3 | 30 | 15 | 450 |
以下是讀取的程式範例:
package CDIT.stanley; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; public class excelToXMLFullSample { static ArrayList<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>(); public static void main (String[] args) throws FileNotFoundException{ excelData.clear(); String xlsPath = "C:\\Projects\\Javas\\sample.xls"; InputStream inputStream = null; inputStream = new FileInputStream (xlsPath); POIFSFileSystem fileSystem = null; try { fileSystem = new POIFSFileSystem (inputStream); @SuppressWarnings("resource") HSSFWorkbook workBook = new HSSFWorkbook (fileSystem); HSSFSheet sheet = workBook.getSheetAt (0); Iterator<?> rows = sheet.rowIterator (); while (rows.hasNext ()){ HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cells = row.cellIterator (); ArrayList<String> rowData = new ArrayList<String>(); while (cells.hasNext ()){ HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellTypeEnum()){ case STRING :{ rowData.add(cell.getStringCellValue()); break; } case NUMERIC : { rowData.add((int)cell.getNumericCellValue() + ""); break; } case FORMULA :{ switch(cell.getCachedFormulaResultTypeEnum()) { case STRING: rowData.add(cell.getStringCellValue ()); break; case NUMERIC: rowData.add((int)cell.getNumericCellValue() + ""); break; default: rowData.add(""); break; } } default: rowData.add(""); break; } } excelData.add(rowData); } } catch(IOException e){ System.out.println("IOException " + e.getMessage()); System.out.println("轉換失敗,請檢查Excel檔案與格式是否正確"); } for(int i=0 ; i<excelData.size(); i++){ System.out.println("Excel Row "+ i +" Data : " + excelData.get(i)); } } }
程式會把Excel內的資料轉換為ArrayList,輸出內容如下:
Excel Row 0 Data : [Product ID, Product Name, Product Price, Quantity, Total] Excel Row 1 Data : [100001, 產品名稱1, 10, 5, 50, ] Excel Row 2 Data : [100002, 產品名稱2, 20, 10, 200, ] Excel Row 3 Data : [100003, 產品名稱3, 30, 15, 450, ]
yuleamo 341c3170be https://yaapoo.com/upload/files/2022/05/SP1KMB2ryEDhiKA7Qwtm_17_00a54d9a0bd274edc2d1211aa58c0ce2_file.pdf
modafinil 100mg usa
alanjaq 341c3170be https://www.bevnp.com/wp-content/themes/begin4.4/inc/go.php?url=https://klealumni.com/upload/files/2022/05/hmtmcJ57y4dzSGnfWahy_17_3801429aa2ff7cba3d50fc54bdc2b137_file.pdf
franlau 341c3170be https://kakakunavi.jp/detail.php?url=https://worlegram.com/upload/files/2022/05/JOiIC6zbzlA71GaGFMXW_16_ae90b4d7c8d09d6e24cb47bbbb5f4733_file.pdf
order modafinil 200mg generic
pavljani 341c3170be http://www.jschell.de/link.php?url=http://bookmarkwebs.com/upload/files/2022/05/MxwJTYECeZKCd9BQ2cBx_17_bf87c0eaa24606973704f5a830eb9cfc_file.pdf
prescribing stromectol stromectol without a doctor prescription prescribing stromectol
nkuarch 341c3170be http://www.garagebiz.ru/?URL=https://cromaz.com.br/upload/files/2022/05/LnTS5EkLjpOefhrkMKgs_17_057211fe7665da04c7244e5b8b4af153_file.pdf
tswaaus 807794c184 https://images.google.lv/url?q=https://www.embracingintuition.ca/profile/deerborndeerborn/profile
redkri 807794c184 https://images.google.is/url?q=https://www.comdd.fr/profile/opeleaopeleaharkon/profile
gawzon 807794c184 http://www.touristx.de/tx-vakanz/func/goto.php?url=https://www.mont21.org/profile/Tycho2/profile
naylean 807794c184 http://maps.google.com.lb/url?q=https://www.adrianacatano.com/profile/FlashDecompiler-Crack-Free-For-Windows-Latest2022/profile
modafinil canada
kyzzeve 807794c184 http://aitaina.jp/m/mt4i/index.cgi?id=5&mode=redirect&ref_eid=455&url=https://www.oasisnet.co/profile/Apache-CXF-Activation-WinMac-Updated2022/profile
haliigr 807794c184 http://print-xxl.net/cs/click.system?navid=77&url=https://www.boltefitness.com/profile/panseahgaileena/profile
betlaza 807794c184 http://b3sm.org/__media__/js/netsoltrademark.php?d=https://www.modernlymixed.com/profile/Ultimate-Tech-Console-Crack-Incl-Product-Key-Free-Download-March2022/profile
yabadel 807794c184 http://hramacek.de/url?q=https://www.hkiti.com.hk/profile/hartfordforsaken/profile
flowalmy 807794c184 https://befonts.com/checkout/redirect?url=https://www.otkimwiggins.com/profile/Texture-Cutter-Free-For-Windows-2022/profile
edreme 807794c184 http://cse.google.com.co/url?sa=t&url=https://sampreetroy.wixsite.com/samroy/profile/comancheharmens/profile
couven 807794c184 http://mivzakon.co.il/news/news_site.asp?url=https://www.liveforzfoundation.org/profile/lisabetelisabete/profile
modafinil 200mg uk order modafinil generic buy provigil without prescription
buying from canadian online pharmacies prescription drugs without doctor approval internet pharmacy no prior prescription
canpat 7bd55e62be https://www.aveiroblocos.com.br/profile/mygnonettemygnonette/profile
brand provigil 100mg order provigil online cheap modafinil drug
erythromycin eye ointment
perjako 7bd55e62be https://www.kotatsu.co.jp/profile/zindeluryanntaryah/profile
https://erythromycin1m.com/# erythromycin ophth oint 3.5gm
babyhild 7bd55e62be https://www.moon-planner.com/profile/trystiannejareff/profile
order provigil online order generic provigil 100mg
germraib 7bd55e62be https://www.theboxified.com/profile/Solucionario-Ingenieria-De-Control-Moderna-3-Edicion-13-marsav/profile