透過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, ]
erythromycin oint what is the antibiotic erythromycin used to treat
AutoCAD Mobile 2015 X32 (32bit) (Product Key And Xforce Keygen)
bd86983c93 virnmar
Gsview 5 0 Keygen Crack
bd86983c93 lawycar
cialis soft tablets generic version of cialis cialis review
isunshare windows password genius full crack kid
bd86983c93 shaglen
Biwi No 1 hindi 1080p hd hindi movie
bd86983c93 krysterr
Midi Guitar 2 Crack 3
bd86983c93 terlean
micro focus visual cobol 2010 serial
bd86983c93 ranjama
generic cialis vs cialis tadalafil 20mg best price cialis price walmart
Hyperdesk Disney’s Magical theme download
bd86983c93 sadzwall
subnautica32bitdownload
bd86983c93 vlahans
I have been curious about these trends, and you have really helped me. I have just told a few of my friends about this on FaceBook and they love your content just as much as I do.
MediaCoder Pro v0.8.48.5888 Final Patched – [SH] crack
bd86983c93 rangoo
ample sound agm keygen crack
bd86983c93 efrquin
sevcik op 9 viola pdf free
bd86983c93 onenfun
Napoleon Total War Razor1911 Crack Only 45
bd86983c93 radtean
I have to say this post was certainly informative and contains useful content for enthusiastic visitors. I will definitely bookmark this website for future reference and further viewing. cheers a bunch for sharing this with us!
Hello there, just became aware of your blog through Google, and found that it is truly informative. I am going to watch out for brussels. I will appreciate if you continue this in future. Lots of people will be benefited from your writing. Cheers!
how to make cialis work better tadalafil without a doctor’s prescription cialis side effects women
Hello this is kinda of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding knowledge so I wanted to get advice from someone with experience. Any help would be enormously appreciated!
Can I just say what a relief to seek out someone who actually knows what theyre speaking about on the internet. You positively know find out how to bring a problem to mild and make it important. Extra individuals have to read this and perceive this side of the story. I cant believe youre not more in style because you positively have the gift.
Very often I go to see this blog. It very much is pleasant to me. Thanks the author
cialispills online buy tadalafil cialis generic best price that accepts mastercard
cialis prescription generic version of cialis real cialis
erythromycin dosage erythromycin ophth ointment 3.5 gm
what is erythromycin used to treat erythromycin estolate
https://lifelink.online/ludoskmhd-2020-www-skymovieshd-nl-1080p-nf-hdrip-hindi-x264-esub-mkv/
75260afe70 ranaflor
https://www.xxvll.com/1233.html
75260afe70 sausar
http://overmarket.pl/?p=15717
75260afe70 evapuri
http://www.vclouds.com.au/?p=677063
75260afe70 berala