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

18,252 Responses

  1. Тут можно преобрести сейф огнестойкий в москве сейф огнестойкий

  2. нарколог на дом вывод из запоя ростов http://rubiz.forum.cool/viewtopic.php?id=3745 .

  3. алкоголизм лечение вывод из запоя ростов http://www.vip.mybb.rocks/viewtopic.php?id=7671 .

  4. Promo Codes for health supplements skidki-i-kupony.ru .

  5. Здесь можно преобрести сейф купить москва сейф купить москва

  6. LloydNoice表示:

    https://kampharm.shop/# kampharm shop

  7. сейфы表示:

    Здесь можно преобрести сейф купить москва новый сейф

  8. Тут можно преобрести купить сейф огнестойкий несгораемые сейфы

  9. Heya i am for the primary time here. I found this board and I to find It really useful & it helped me out much. I am hoping to give one thing back and help others like you helped me.

  10. вывод из запоя на дому ростов на дону вывод из запоя на дому ростов на дону .

  11. наркология вывод из запоя ростов наркология вывод из запоя ростов .

  12. вывод. из. запоя. на. дому. ростов. вывод. из. запоя. на. дому. ростов. .

  13. TimothyDuawl表示:

    Why Thailand is making it easier for travelers to stay longer
    [url=https://t.me/chat_phuket]Пхукет форум[/url]
    Maybe you want to escape the winter months at home, opting instead to take care of your business on a laptop from the comfort of your rented Phuket villa as you gaze over the Andaman Sea. Or perhaps you’re ready to step into the ring and embark on a new career as a Muay Thai fighter.

    Either way, Thailand has you covered.

    The popular Southeast Asia destination recently introduced a new five-year visa targeted at remote workers and other travelers looking to stay in the kingdom for extended periods.

    According to a statement issued by the Thai prime minister’s office, the Destination Thailand Visa (DTV) will allow eligible travelers a period of stay up to 180 days per visit, on a multiple-entry basis, within five years. (This means they will need to leave the country when their 180 days are up, and the time resets when they re-enter.)
    The government statement says the visa is open to several categories of remote workers, including digital nomads and freelancers. It’s also aimed at those looking to visit to engage in activities such as Muay Thai training or Thai cooking classes, or come for extended medical treatments.

    To apply, travelers need to show evidence that they have a minimum of 500,000 baht (about $13,800) in funds, as well as documents to support the purpose of their visit, such as a letter from a medical center or proof of employment

  14. Здесь можно преобрести сейф купить сейф москва

  15. Тут можно преобрести пожаростойкие сейфы несгораемые сейфы

  16. сейфы表示:

    Здесь можно преобрести сейф цена сейфы простые

  17. RobertHam表示:

    http://kampharm.shop/# kam pharm shop

  18. CurtisCem表示:

    kam pharm shop Kam Pharm cheapest Kamagra Kam Pharm

發佈留言

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