透過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,720 Responses

  1. Slonmob表示:

    r7irw

    zz9cq

    bgl1

  2. elaijas表示:

    No doubt, the days of bookmarking are over, but you can still use a browser-side bookmarking solution for those Pinterest boards and websites you like. Well, there is another bit of help if you need extra features and a rounded look.
    The Chrome addon is what you need. Highlight and press Ctrl+D and choose among the boards displayed in a small pop-up window.
    As you can expect, Pinterest Save Button does not replace the functionality of the markdown addon https://bertenshaardhouttilburg.nl/wp-content/uploads/2022/06/kamlpat.pdf
    ec5d62056f elaijas

  3. rayezab表示:

    If you are more interested in this particular feature, we recommend you to look at related articles that shed some light on how to delete sensitive files.

    TortoiseSVN is a lightweight Source-code repository manager based on the same principles as the Subversion Version Control System. TortoiseSVN is a Windows application that was originally made by Justin Jacobson and is nowadays maintained by developers from all over the world.

    By the way, if you’re wondering why I have https://chatbook.pk/upload/files/2022/06/w9Y9WwHI4ZWgv6FYL8Sx_04_11dbff2dcaa7be3340fa68c3ea397003_file.pdf
    ec5d62056f rayezab

  4. darwcha表示:

    For instance, you can use your mouse and keyboard, while using other devices simultaneously.

    GUIDE

    BASIC1

    INFO4

    KNOWN1

    SETTINGS3

    EXIT

    Usage: click on the file and then double click on the icon in the tools.

    InputMapper is still under development and some of the features may not work as they are expected. That is why I am recommending you to get the trial version before you https://talkotive.com/upload/files/2022/06/MRJaDZ43imq9aBm5oCH5_04_f3d30049923f1553cea02ae102c86bae_file.pdf
    ec5d62056f darwcha

  5. godfward表示:

    .
    Components of detector:
    – Server modules (detector and counter server)
    – Network monitor (FTP, File Transfer Protocol)
    – Client software (in & out to the target system)
    – Active directory (windows AD & active directory)
    – Short MD5 (MD5 hash value) scanner (used for detecting an unauthorised connection to your system)
    Quick Edit add webkit shell extension to use hot keys ctrl+k and ctrl+u https://www.mycoportal.org/portal/checklists/checklist.php?clid=1134
    ec5d62056f godfward

  6. meiupwo表示:

    Originally Posted by DreamFox49

    If you are really a fan of porn sites, you might find this program a must download tool. Perhaps this cool app is actually something that will make you appreciating porn even more. RMBJedi is what it is gonna do.

    The software that allows you to offer a series of new titles to prepare for illegal downloads. Recently, people have become unable to access pirated movies, using thepiratebay.se website and for https://library.big-bee.net/portal/checklists/checklist.php?clid=1725
    ec5d62056f meiupwo

  7. alamark表示:

    1-11 of 42

    What’s new

    Loading…

    Thanks for rating! Please let us know if you like the fonts!

    AppBrain Editor’s review

    Thanks for rating! Please let us know if you like the fonts!

    AppBrain Editors

    AppBrain Editors

    Office 2016 icon pack by Zubor S. – apk.aibuilder

    App Details

    Version

    3.0

    Rating https://wordnonbustrori.wixsite.com/nalhohoman/post/yamaha-x-s-yxg50-vsti-plugin-download-exclusive
    ec5d62056f alamark

  8. yelgia表示:

    Expression Blend is highly customizable and it can be expanded with you as you use it by implementing your own features with tools like the Resource System or command sets that can be easily extended, by using Shaper, as well as by creating your own theme packs with Designer.)$ are less than $c$ with probability less than $1- \frac{\beta}{c}$.

    The following gives the proof.

    1. (covariance vs covariance of $( https://lichenportal.org/cnalh/checklists/checklist.php?clid=9119
    ec5d62056f yelgia

  9. verpayn表示:

    RecSound Core and Control

    This is the “Downloads sub-tab” of installer. To uninstall it, open it and select “Uninstall” from the “Help” menu, then click “Remove”. Thanks

    As it is the first time you download and install a program via Tridion SP1, a step-by-step installation instruction is provided to guide you through the process. If you are having problems installing, please follow these steps:

    Open Tr https://bryophyteportal.org/frullania/checklists/checklist.php?clid=6771
    ec5d62056f verpayn

  10. valdkee表示:

    Businesses that embrace this type of software can show a marked improvement in their efficiency and profitability. You are able to precisely control the way you conduct all your business which in turn has a positive impact on the way your company is run.
    Taking this approach is the first step towards a successful organization, but only if you have the experience and patience to find out what ProjectForge can offer.

    Welcome to our second in a series of posts on what to expect when starting out a business. https://wakelet.com/wake/3bsruVN6arAVEbC0sCT5N
    ec5d62056f valdkee

  11. xireafto表示:

    For this reason it is thoughted to support robust fingerprint identification by the usage of special probability models, that understand if images are reliable enough, so they know when to apply shortcuts. With this aims we propose a new implementation of a robust fingerprint recognition algorithm for better localization of core points. These are detected by applying a circular, Sobel and Gaussian filters, followed by appropriate threshold techniques. Nevertheless, the filtered image is fed into a collection of robust features: invariant moments, center of gravity and https://pra-namorar.paineldemonstrativo.com.br/upload/files/2022/06/3VLEnYvLQw7XRKExmBVa_04_ac0770d739f403288218732b90a2f308_file.pdf
    ec5d62056f xireafto

  12. celequir表示:

     

    WindowsGetClockTowerSample_

    A+ review for the “Vista” edition:

    More than just beautiful imagery…
    Yes, there is a whole lot of eye candy in this screensaver. 
    But probably the best part of it has to be the music. It’s not only an aural delight, but the tempo coupled with the music perfectly sync up to the architecture itself. This in turn makes you believe that you’re actually in one of those http://www.lavitrinatv.cl/?p=5617
    ec5d62056f celequir

  13. frangia表示:

    In the market, a ton of games are coming out with features, one of which is an Online Gaming feature that allows players to engage in battles with their friends through the Internet. In this article, we will take a look at the Top 5 Online Games you can play on PC that are compatible with Windows 7 and above.

    To play these games on Windows 7, you need to update the graphics driver of your PC to a newer version. Here are the requirements:

    To https://murmuring-savannah-34832.herokuapp.com/VRChat_Torrent_Download_key.pdf
    ec5d62056f frangia

  14. Slonmob表示:

    ai6qu

    40mzc

    hhp4

  15. fauniss表示:

    It’s highly recommended for those who just need a reliable alarms utility.

    Install My Alarm Clock:

    The download button has been added a few days ago to the official My Alarm Clock website.
    Once you click it, you are prompted to choose the type of license you have, whether you want a free version or a paid one, and afterwards, you are prompted to update and launch the installation wizard.
    It took me four tries to finalize this simple installation https://www.viverosmogan.net/profile/tinctoughregacafe/profile
    66cf4387b8 fauniss

  16. geoyav表示:

    Plus, since it is a fully digital product, you can use them whenever and wherever you want!

    Slowly but surely, the expansion of the Unite Franchise in Austria is on a good path.
    The number of regions of the mark in Austria is currently at the double digit level of 50% and there is a good chance that this figure will be significantly increased over the next few years.
    As you can see in the following figures, Unite has gained almost ten percent in recent https://www.nebc.edu/profile/masumiromisvi/profile
    66cf4387b8 geoyav

  17. rammad表示:

    Javasoft has released a software called JEdit 3.5.8. This latest release is a free update for the earlier version 3.5.4. Also, you will get JDegrade 1.3.1 for free and an installation code for free. It’s an amazing tool as this one is capable of decomposing files into easy to understand chunks. These easy to understand chunks are called levels. The levels are named as 001, 000, 000 https://www.quantockhills.com/profile/How-To-Get-Involuntary-Manslaughter-Free-Full-Version/profile
    66cf4387b8 rammad

  18. neikedd表示:

    The emergence of next generation sequencing (NGS) technologies has the potential to radically transform the way cancer is diagnosed, seen and treated. Despite the recent availability of NGS technologies, there is still much to learn about the clinical impact of their implementation for cancer diagnosis, biology and management of cancer. An expert in this area is invited to make a presentation during a one-hour forum entitled “Next Generation Sequencing Approaches for Cancer Diagnosis, Biology and Therapy” to be held on https://www.liturgyofthehours.org/profile/better/profile
    66cf4387b8 neikedd

  19. raquald表示:

    The “manual” mode uses an external CAT tool, which allows you to translate the texts manually.
    Here’s the complete tutorial on how to use the AUTOCAD TRANS Extension.

    A:

    There are two components to your problem.
    1) You are not storing the text types as part of the block information.
    This is needed to pass the string to the catalog.
    CAT.Connect(3, “Text Object stored in the block”, ” https://www.iqrafoundation.com/profile/Raduga-391-Crack-Free-Download/profile
    66cf4387b8 raquald

  20. cirfel表示:

    Limitations:
    · Copyright of the MU-Trace application is the sole property of me!
    · The Software User Agreement is given at the download site of the MU-Trace application. You may also visit
    · The Software isn’t endorsed or supported by Microsoft
    · The Software is free software according to the GNU General Public Licence
    · Some other users had issues with using the Software
    · MU-Trace https://www.biztailor.co.jp/profile/Realtek-11n-Usb-Wireless-Lan-Driver-Utility-Windows-7-UPD-Downloadl/profile
    66cf4387b8 cirfel

  21. axeu19表示:

    stromectol 3mg information ivermectin tablets dosage

  22. pkcj82表示:

    azithromycin 3 day pack zithromax z pak azithromycin dosage zithromax over counter

  23. ebs16s表示:

    generic name for ventolin hfa albuterol hfa dose

  24. AndrewEcork表示:

    viagar vs cialis buy tadalafil gnereic cialis

發佈留言

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