透過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, ]
http://surprisemenow.com/?p=27915
75260afe70 enrchri
100mg viagra viagra pills online doctor prescription for viagra
https://stylovoblecena.com/imgsrc-ru-kids/
75260afe70 osbjair
https://gravesendflorist.com/ui-browser-3-0-2/
75260afe70 phizave
http://seoburgos.com/?p=2826
75260afe70 geoverd
https://atrium-vision.fr/download-halsey-new-americana-official-music-video-mp3-0430-min-free-full-download-all-music/
75260afe70 vankhri
http://tekbaz.com/2022/05/25/шєшщщљщ-гїгеј·-зй‡ћи±е/
75260afe70 nikcom
http://www.ndvadvisers.com/candid-tight-ass-48-capture20201015002710335-imgsrc-ru/
75260afe70 kaffcomp
http://villa-mette.com/?p=1644
75260afe70 birivan
http://kramart.com/model-girls-mixed-9-imgsrc-ru/
75260afe70 deawel
viagra over the counter walmart generic viagra walmart best place to buy generic viagra online
order viagra online viagra pills buying viagra online
stromectol for sale can ivermectin be given orally stromectol tablets for humans
erythromycin ointment erythromycin online
stromectol 12 mg tablets stromectol 12 mg tablets stromectol 12 mg tablets
erythromycin ingredients https://erythromycin1m.com/#
buy provigil 100mg online cheap
order modafinil online buy provigil 200mg sale
buy modafinil 100mg provigil price modafinil tablet
viagra sildenafil citrate 100mg for sale sildenafil 20 mg
buy modafinil 100mg for sale order provigil 200mg online cheap provigil 200mg usa
modafinil oral
modafinil 100mg us cost provigil
where to buy liquid cialis price of cialis 20 mg cialis 20 mg price
modafinil 200mg price order modafinil 200mg without prescription
order modafinil sale modafinil 200mg canada
stromectol for sale stromectol without a doctor prescription prescribing stromectol
modafinil cost provigil oral
Grammarly Microsoft Office Word 97 – 2007 is a peculiar product that can catch misspelled words within the body of any document that you are working on in any Microsoft Office application (Word, Excel, PowerPoint and OneNote). The utility can be
…
WordBulk Merge MS Office 2003 – 2007 – Free Version is a versatile utility that can merge MS office 2003, 2007 file formats into one single XML text file in a batch mode. It has helped lots of users to https://wakelet.com/wake/33MQMSCz9U6ISYzrRCdWz 8cee70152a fiejan
If you would like to find a better solution for your purpose, or have any feedback, feel free to give us some feedback via the user panel.Q:
C# TextBox.Text Navigate to the end of the sentence
I have my program set up so it has a Timer and every tick it updates the text of a label. For example, each second it updates it as
label.Text += “: ” + Timer.Elapsed. http://sharedsuccessglobal.com/marketplace/upload/files/2022/05/hLDrxACLWsaruDEyCdE9_19_a30fb0fbaef31119b03e4160bd757ea8_file.pdf 05e1106874 falbets