透過Java解析Excel檔案

也是一個工作中遇到的情境,很多單位想讓內部人員利用Excel維護資料(因為維護上比較簡單方便),且想讓這份資料透過網頁呈現給一般網友閱讀,所以整體後端的流程應該是:

  1. 將維護的Excel透過網頁介面上傳
  2. 將上傳的Excel轉換成XML
  3. 網頁去讀取這份XML來呈現

透過以上的三個步驟,就可以完成客戶想要效果。

本範例展示的是讀取Excel的XLS格式(新版Excel為XLSX格式),並轉換成陣列的方法(寫入XML在前面的範例就有了),我們選用讀取Excel的Library是Apache POI,而檔內已經有以下的資料,其中Total是公式欄位,計算Price x Quantity:

IDNamePriceQuantityTotal
100001產品名稱110550
100002產品名稱22010200
100003產品名稱33015450

以下是讀取的程式範例:

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, ]

You may also like...

7,724 Responses

  1. RichardEnect表示:

    п»їcipro generic antibiotics cipro

  2. piopary表示:

    lasix prescription ГЃlvarez Icaza J, CENCOS

  3. Rickyjaick表示:

    https://buytadalafil.icu/# generic tadalafil from india

  4. Rickyjaick表示:

    https://buytadalafil.icu/# tadalafil soft gel capsule

  5. piopary表示:

    Shaarani S, et al lasix iv po conversion Monte, USA 2022 05 29 11 09 01

  6. Ginniepisn表示:

    write my college essay me
    my custom essay
    fast custom essay

  7. ascetacle表示:

    Leg cramps and myalgia have been reported commonly in patients receiving Tamoxifen PCH lasix and potassium Concomitant use of aspirin is not recommended

  8. Admitle表示:

    buy lasix Capecitabine non adherence Exploration of magnitude, nature and contributing factors

  9. LABAKAWLBIXqa表示:

    order lyrica without prescription cheap pregabalin purchase lyrica

  10. Rickyjaick表示:

    https://buytadalafil.icu/# best price tadalafil 20 mg

  11. Admitle表示:

    This report will use HPAC to examine both the overall binding and site specific interactions of glipizide with HSA, based on frontal analysis and zonal elution competition studies diuretic lasix buy Human papillomavirus HPV infection, particularly, by HPV16 and 18, is a well established cause of cervical cancer 2

  12. XsaYrolLiZgt表示:

    On this site u can buy all known killa snus for very good price!

  13. LABAKAWLBIXfr表示:

    pregabalin usa pregabalin 75 mg for sale pregabalin 75 mg uk

  14. Admitle表示:

    Therefore in one embodiment the invention provides combinations based on a crystalline form of cyclopropyl 3 3 6 morpholin 4 ylmethyl 1H benzoimidazol 2 yl 1H pyrazol 4 yl urea lactate salt particularly the L lactate characterized by peaks in the X ray diffraction pattern at 12 lasix side effects in elderly

  15. Ginniepisn表示:

    custom essay company
    buy college essay
    college essay help online

  16. Admitle表示:

    how fast does lasix work 5 or more in control samples are listed

  17. Admitle表示:

    paroxetine and protriptyline both increase serotonin levels when to hold lasix B Numbers of mice with metastatic lesions are quantified with percentages of positive mice shown in the table for each cell type and treatment group

  18. piopary表示:

    lasix pill identifier Sidney PjLxnMggekzT 6 20 2022

  19. Ginniepisn表示:

    admissions essay help
    best college essay writing services
    romeo and juliet essay help

  20. Admitle表示:

    Food and nutrient intake and risk of cataract does lasix make you tired Women whose breast cancer is confined to the milk ducts and has not spread into the fatty breast tissue or to other regions of the body have a near 100 survival rate when the cancer is surgically removed either by breast conserving therapy or mastectomy

發佈回覆給「Yonbusly」的留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。