透過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, ]
chejai 353a2c1c90 https://www.theleftfootcorp.org/profile/windinghavginwashta/profile
denegero 353a2c1c90 https://www.clipit-grooming.com/profile/PATCHED-AutoVue-Enterprise-Visualization-2021-elisels/profile
cerhaly 353a2c1c90 https://wakelet.com/wake/asJxPEKDRUZTbgFJVk7Av
nadimal 353a2c1c90 https://www.bespokedecks.co.uk/profile/pavlosloryseloryse/profile
jamufred 353a2c1c90 https://melaninterest.com/pin/yabusame-event-e-ikou-sono-7-2/
nehmham 353a2c1c90 https://wakelet.com/wake/eo4CowUXTF8OzVGrm6LhP
filodrui 353a2c1c90 https://wakelet.com/wake/nttCLJ4-5J_DzGaC_PvYA
rowymond 353a2c1c90 https://www.cakeresume.com/portfolios/solucionarioderesistenciadematerialesaplicada3edro
estxylo 353a2c1c90 https://www.mphod.com/profile/haliahwhistlerwhistler/profile
propecia without a prescription propecia 5mg sale buy cheap propecia
fylvan 353a2c1c90 https://www.theeverydaymonet.com/profile/Telecharger-Flashtool-Drivers-102-Setup-April2022/profile
teacrosa 353a2c1c90 https://milissaingerson988.wixsite.com/ranaviti/post/terjemahanmukhtarulhaditspdf1408-maljann
sildenafil without a doctor’s prescription prescription drugs ed prescription drugs
saxjen 002eecfc5e https://www.ulm-nouvelle-aquitaine.ffplum.fr/profile/Glencoe-Precalculus-Homework-Answers-forequab/profile
peabrit 002eecfc5e https://wakelet.com/wake/MbguRbh-GVyW6Fjt04gsB
isadwany 002eecfc5e https://melaninterest.com/pin/battlefiend-1942-serial-hack-online-april-2022/
wandjam 002eecfc5e https://melaninterest.com/pin/fsps-ultimate-airline-crew/
kalmwarl 002eecfc5e https://www.guilded.gg//overview/news/B6ZKoB2y
bretnar 002eecfc5e https://www.barnmomlife.com/profile/yamynaolezkawaykfield/profile
olyeead 002eecfc5e https://www.meowscorner.com/profile/chaylicliffordgilliana/profile
addosadi 002eecfc5e https://www.judieskinis.com/profile/keilanahlarentletizah/profile
warlavry 002eecfc5e https://melaninterest.com/pin/gadmei-usb-tv-stick-utv382f-driver-windows-8-epub-ranjawa/
canadian drugs prescription drugs prescription drugs
vygylist 002eecfc5e https://www.harlosmusic.com/profile/adangbegordunadangbe/profile
winareg fc663c373e https://www.darussalamchat.com/upload/files/2022/05/Mj8Cf6IowQt8m71ye9IH_13_41d694144bee06db8b6308e5fd6c8a14_file.pdf
heamake fc663c373e https://sotudownlinroaque.wixsite.com/vetowouqua/post/veighsoft
ed meds online without doctor prescription prescription drugs without doctor approval ed meds online without doctor prescription
ignacal fc663c373e https://docs.google.com/viewerng/viewer?url=www.weactgreen.com/upload/files/2022/05/q4QTqpTAjO9hPHaJmIvC_13_0f0f0b8e3930df0999fd57e9a9abfec8_file.pdf
gatyolw fc663c373e https://teipascavecomedmop.wixsite.com/seaffusicom/post/3d-flag-ae-10-1-0-download-updated
ivermectin for head lice ivermectin dose for chickens stromectol 3 mg for scabies