透過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, ]
mail order pharmacy india indian pharmacy indian pharmacy
https://indiaph24.store/# legitimate online pharmacies india
canadian pharmacy service Large Selection of Medications from Canada cheap canadian pharmacy
https://canadaph24.pro/# northern pharmacy canada
п»їbest mexican online pharmacies Mexican Pharmacy Online buying prescription drugs in mexico
http://indiaph24.store/# Online medicine home delivery
http://indiaph24.store/# indianpharmacy com
canada pharmacy online legit canadian pharmacy 24h com reputable canadian online pharmacy
http://mexicoph24.life/# mexico drug stores pharmacies
mexican pharmacy Mexican Pharmacy Online mexican drugstore online
http://canadaph24.pro/# buying from canadian pharmacies
PBN sites
We’ll build a web of private blog network sites!
Pros of our private blog network:
We perform everything SO THAT GOOGLE DOES NOT understand that this A privately-owned blog network!!!
1- We purchase domain names from various registrars
2- The main site is hosted on a VPS server (VPS is high-speed hosting)
3- Additional sites are on distinct hostings
4- We designate a distinct Google account to each site with verification in Google Search Console.
5- We design websites on WP, we don’t use plugins with assistance from which Trojans penetrate and through which pages on your websites are generated.
6- We do not reiterate templates and utilize only unique text and pictures
We do not work with website design; the client, if desired, can then edit the websites to suit his wishes
http://mexicoph24.life/# buying prescription drugs in mexico online
Online medicine order buy medicines from India top online pharmacy india
PBN sites
We shall create a web of private blog network sites!
Pros of our self-owned blog network:
WE DO everything so Google does not realize THAT THIS IS A privately-owned blog network!!!
1- We purchase domains from different registrars
2- The principal site is hosted on a VPS hosting (VPS is high-speed hosting)
3- The rest of the sites are on different hostings
4- We allocate a separate Google profile to each site with verification in Search Console.
5- We make websites on WP, we don’t employ plugins with aided by which Trojans penetrate and through which pages on your websites are established.
6- We never reproduce templates and use only individual text and pictures
We refrain from work with website design; the client, if wanted, can then edit the websites to suit his wishes
what is zyprexa for
mexico pharmacy mexican pharmacy mexican pharmacy
Наиболее качественная и популярная порошковая краска
mexican rx online Mexican Pharmacy Online buying prescription drugs in mexico online
Отличный сайт! Всем рекомендую! разработка котлована
PBN sites
We’ll establish a system of privately-owned blog network sites!
Benefits of our PBN network:
We execute everything SO THAT GOOGLE does not comprehend THAT THIS IS A private blog network!!!
1- We purchase domains from distinct registrars
2- The principal site is hosted on a VPS hosting (Virtual Private Server is fast hosting)
3- Additional sites are on distinct hostings
4- We attribute a unique Google ID to each site with verification in Search Console.
5- We design websites on WP, we don’t use plugins with aided by which malware penetrate and through which pages on your websites are generated.
6- We don’t repeat templates and use only distinct text and pictures
We do not work with website design; the client, if wanted, can then edit the websites to suit his wishes
http://mexicoph24.life/# medicine in mexico pharmacies
Online medicine order https://indiaph24.store/# indian pharmacy paypal
indianpharmacy com
http://mexicoph24.life/# mexico pharmacy
mexico pharmacy mexico pharmacy mexican rx online
indian pharmacies safe https://indiaph24.store/# india online pharmacy
top 10 pharmacies in india
buying prescription drugs in mexico: Mexican Pharmacy Online – mexican border pharmacies shipping to usa
https://seostrategia.ru/
https://mexicoph24.life/# mexican pharmaceuticals online
mexican pharmacy purple pharmacy mexico price list medicine in mexico pharmacies