透過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, ]
https://canadaph24.pro/# rate canadian pharmacies
http://canadaph24.pro/# canadian discount pharmacy
best online pharmacy india Generic Medicine India to USA buy prescription drugs from india
https://canadaph24.pro/# canadian online pharmacy reviews
world pharmacy india Generic Medicine India to USA world pharmacy india
buying from online mexican pharmacy: cheapest mexico drugs – medicine in mexico pharmacies
mexico drug stores pharmacies mexican pharmacy mexico pharmacies prescription drugs
Simply desire to say your article is as surprising. The clarity in your post is just cool and i can assume you’re an expert on this subject. Fine with your permission allow me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the rewarding work.
фильтрующие сизод
http://indiaph24.store/# online shopping pharmacy india
Online medicine home delivery buy medicines from India cheapest online pharmacy india
http://indiaph24.store/# indian pharmacy paypal
brand ketoconazole – cheap lotrisone order sporanox 100mg
canadian pharmacy uk delivery Prescription Drugs from Canada canadian pharmacy 365
top 10 online pharmacy in india: buy medicines from India – india pharmacy
best online pharmacies in mexico Online Pharmacies in Mexico mexican pharmaceuticals online
https://canadaph24.pro/# legal canadian pharmacy online
Since the admin of this web page is working, no uncertainty very quickly it will be well-known, due to its quality contents.
В Подмосковье и Москве ландшафтный дизайн киржач. г.Москва, Россия.
http://indiaph24.store/# best online pharmacy india
buying prescription drugs in mexico mexican drugstore online mexican pharmaceuticals online
zanaflex medication
http://canadaph24.pro/# canadian pharmacy antibiotics
https://indiaph24.store/# indian pharmacy
reputable indian online pharmacy Generic Medicine India to USA п»їlegitimate online pharmacies india
Всё о радиаторах отопления https://heat-komfort.ru/ – выбор радиатора, монтаж, обслуживание.
mexico drug stores pharmacies: mexican rx online – best online pharmacies in mexico
http://indiaph24.store/# п»їlegitimate online pharmacies india
http://mexicoph24.life/# buying from online mexican pharmacy
canadian pharmacy king Certified Canadian Pharmacies canadian pharmacy antibiotics
zofran beipackzettel
mexican border pharmacies shipping to usa mexico pharmacies prescription drugs mexican pharmacy