透過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...

16,717 Responses

  1. Jeffreyvop表示:

    india online pharmacy: indian pharmacy – cheapest online pharmacy india

  2. франшизу [url=https://franshizy32.ru]франшизу[/url] .

  3. StephenJem表示:

    https://mexicanpharmi.com/# reputable mexican pharmacies online

  4. покупку франшизы franshizy32.ru .

  5. Velas_bvSt表示:

    Расслабьтесь с велас ароматическими свечами, какой аромат выбрать?, Погрузитесь в мир ароматов с велас ароматическими свечами
    aromas para difusor aromas para difusor .

  6. открыть бизнес по франшизе открыть бизнес по франшизе .

  7. Willardvem表示:

    https://mexicanpharmi.com/# buying from online mexican pharmacy

  8. Property Brothers debuted on the W Network on January 4, 2011, and aired on the channel till its proprietor, Corus Leisure, decided to shift the channel’s focus to more dramatic content material.

  9. StephenJem表示:

    https://indiapharmi.com/# indianpharmacy com

  10. MatthewHen表示:

    indian pharmacies safe [url=http://indiapharmi.com/#]Indian online pharmacy ship to USA[/url] best india pharmacy

  11. Na5 was additionally inaccurate, because the knight would have a tough time getting back into play while Ding created a strong central presence.

  12. Willardvem表示:

    https://indiapharmi.com/# indian pharmacies safe

  13. I’m amazed, I have to admit. Seldom do I encounter a blog that’s both educative and amusing, and let me tell you, you’ve hit the nail on the head. The problem is something which not enough folks are speaking intelligently about. I’m very happy that I stumbled across this during my hunt for something relating to this.

  14. Jeffreyvop表示:

    how to fix ed: Cheapest drug prices Canada – top erection pills

  15. франшизы для малого бизнеса франшизы для малого бизнеса .

  16. MatthewHen表示:

    natural treatments for ed Best Canadian online pharmacy over the counter erectile dysfunction pills

  17. Ernestzow表示:

    mexican border pharmacies shipping to usa: Mexican pharmacy ship to usa – pharmacies in mexico that ship to usa

  18. Ernestzow表示:

    medication from mexico pharmacy: Cheapest online pharmacy – buying from online mexican pharmacy

  19. StephenJem表示:

    https://canadianpharmi.com/# reasons for ed

  20. Jeffreyvop表示:

    buy prescription drugs online without: canadian pharmacy – viagra without a doctor prescription

  21. MatthewHen表示:

    mexican online pharmacies prescription drugs Purple pharmacy online ordering reputable mexican pharmacies online

發佈留言

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