透過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, ]
cymbalta 40 mg price
https://buydoxycycline.icu/# doxylin
how to get lyrica without a prescription https://lyrica.store can i purchase generic lyrica online
On this site u can buy all known Killa for very good price!
Let us sit together to resolve all outstanding issues in a friendly manner and in a peaceful atmosphere, he said in a statement, the state run Associated Press of Pakistan reported Tuesday how many mg of lasix can you take a day mestinon lek zocor skutki uboczne Michael Kidd, the man s lawyer, will argue the unusual case before New Zealand s High Court on October 16 and is determined to take the case all the way to the Supreme Court if necessary
ivermectin pills otc – https://stromectolverb.com ivermectin tablets usa
colchicine medication
Arianna, USA 2022 06 04 08 34 44 lasix kidney function
generic levitra
doxycycline order online doxycycline hyc 100mg
lasix tablets uk
amoxicillin 875 mg discount
You actually make it seem so easy with your presentation but I find
this topic to be actually something that I think I would never understand.
It seems too complex and very broad for me. I am looking forward
for your next post, I’ll try to get the hang of it!
Miller JA, et al how to administer lasix iv push
zithromax online usa no prescription zithromax prescription
van der Kemp, A digoxin and lasix
bactrim antibiotic online prescriptions zithromax buy online no prescription
buy cheap doxycycline amoxicillin 500mg no prescription
Sentinel lymph node biopsy A Sentinel lymph node biopsy is done to check if cancer has spread to the lymph nodes does lasix lower potassium The intranasal and intratracheal delivery of the AAV vector into Rosa26 CAG LSL hSPCas9 knockin mice resulted in the formation of lung cancers within 2 months
buy zithromax online fast shipping zithromax 250
https://amoxil.best/# amoxicillin without rx
medicine from canada with no prescriptions
https://zithromax.best/# zithromax over the counter
Scottie, USA 2022 06 28 05 46 08 can lasix be crushed
best time to take lasix Table 2 Summary of Frequently Reported 1 Treatment related Adverse Events by Severity Grade III IV, Body System and COSTART Preferred Term Reported in Ovarian Cancer Patients Adverse Event Ovarian Cancer Patients treated with CAELYX n 512 No
1600 mg gabapentin
Bank of America Merrill Lynch, RBC, Barclays, Credit Suisse and UBS are lead managers on the loan side lasix prescription
can you order cialis online
Both methionine and cysteine are required for protein synthesis can lasix cause hyponatremia
torsemide to lasix 1 or otherwise known in the art and administration of hair growth promoting agent is adjusted in order to optimize hair growth