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

  1. MichaelPlela表示:

    clomid clomid tablets where to buy cheap clomid online

  2. charan表示:

    Category:Label-making softwareQ:

    Where was Jesus born?

    Where was Jesus born according to the Orthodox Catholic Christian Church?
    Please be inclusive of different versions of Christianity.

    A:

    The Catholic Church reaffirms the doctrine of the birth of Jesus according to the Gospel of Matthew:

    [In Matthew 1:18]
    καὶ αὐτὸς Ἰησοῦ http://chatroom.thabigscreen.com:82/upload/files/2022/05/C2RGiaJDAcRvbFfKoRPP_19_9c399f70d35e4649ab1c2fd36e843d32_file.pdf 05e1106874 charan

  3. ventho表示:

    FxImage Compressor is made by FORump’s Cute Pictures Image Resizer.
    * If your photos are protected by a password, you will be prompted to enter this when saving them, and each time you output your photos they will be saved without the password. Use of an image that is attached to an email is not permitted.
    You can define which resolution you want to use, and the output save path is outputted automatically by FORump while you drag the photos, https://www.medtechcapital.ch/goto.php?url_link=https://kettlapsocen.weebly.com

    6add127376 ventho

  4. wescwhoo表示:

    Features

    Provides accurate, reliable tracking of time over the Internet

    Super-secure

    With Stratum 1 security, ClockWatch is able to authenticate clients and prevent potentially malicious hardware from tampering with your content

    Software package includes StarSync, StarPACM.exe, and setup instructions.Q:

    MySQL: Unable to start the server in a Docker container

    I am trying to start the server in the container.

    docker run https://gacumeci.weebly.com

    6add127376 wescwhoo

  5. cereglow表示:

    Free – download ARMu today!Q:

    Multiple NSFileManager in different MainThread

    I currently have two NSFileManagers, I and two NSMutableURLRequest’s, and two NSObject subclasses as below.
    However the problem I am facing is that I am seeing a main thread issue.
    My initialisation code is:
    Private global NSFileManager *fromURLDownload = [[NSFileManager alloc] init];
    NSURL * https://torginfosystems.ru/bitrix/redirect.php?event1=&event2=&event3=&goto=https://astanhermfond.weebly.com

    6add127376 cereglow

  6. ferecai表示:

    Hence if you are looking for a full featured pro level solution then certainly the cross platform 64 bit capable UVI Workstation could be better suited. More on the above VST plugin in the next section, including a screenshot to compare the menus to the plugin.Instruments however, any stand alone VST plugin, would normally require you to encode the sample rate etc. into the settings properties of the plugin. Thats where GrooveBox comes in, as you can use the GrooveBox http://namu.com.ua/bitrix/redirect.php?goto=https://tlemninihat.weebly.com

    6add127376 ferecai

  7. vcwhjwmf表示:

    buy provigil generic modafinil cheap modafinil 200mg without prescription

  8. helcou表示:

    There are far better tools (such as CLIZAudio Pro) when it comes to converting files, but AVS Audio Converter packs out almost everything you’d need.While not quite the disaster that some predicted, the Congressional Budget Office (CBO) did score the House Republican’s latest tax cut bill to see if it would add to the deficit. They found only a negligible 0.1 percent deficit for the next ten years.

    But while we’re only a https://images.google.cv/url?q=https://icpavegi.weebly.com

    6add127376 helcou

  9. melielle表示:

    The following table describes the currently supported features of the Windows Server AppFabric package.

    Table of the officially released components of Windows Server AppFabric

    Feature
    | Components
    | Value |
    | — |
    | Application Connector Server | Active directory, IIS, ASP.NET, WCF and Windows Workflow Foundation |
    | Application Gateway Service | IIS and Windows Workflow Foundation |
    | Application Load Balancer Service | IIS and Windows Workflow Foundation http://www.emergencyservicescrossing.com/lcjsjobdetail.php?&source=ZipRecruiter&keyword=&location=&url=https://sentenoki.weebly.com

    6add127376 melielle

  10. bandelet表示:

    This tool is available free to download from

    Juniper NVP Emergency Management System is a unified and complete emergency management solution which combines a complete Communications, Planning, coordination, and operations. It offers separate modules for local, regional, and national levels but works with its own LDAP based user and LDAP private information.

    Citadel Config platform is an easy to use Enterprise level management framework enabling features like a config/ https://twcmail.de/deref.php?https://fecomandge.weebly.com

    6add127376 bandelet

  11. bojubrrn表示:

    provigil pills generic provigil 200mg

  12. darnad表示:

    Meanwhile, it is possible to set up a bug label, which determines the type of bug that has to be tracked based on the label.
    You will have noticed that no matter which edition of the software you choose, performance will quite easily be on par with its chief competitors. And since it has more than 9 years of operation, it should come as no surprise that this piece of software has seen more than nine updates.
    It is available for all kinds of operating systems, including: Windows https://diakyalima.weebly.com

    6add127376 darnad

  13. hilwari表示:

    You don’t need to know programming to make this! You can change the look of your own software using XP Look.

    10.

    Oxen Stencil Pro – from cgtools –
    Graphics & Imaging… Oxen Stencil Pro is a powerful set of design tools (Pixel Pusher, Pixel Sketch, Pixel Fire, Pixel Scissors, Pixel Mariner and Pixel Animator) that allows you to easily create stencils. Oxen Sten http://glbnews.com/url.html?p=https://kyolepounre.weebly.com

    6add127376 hilwari

  14. philsav表示:

    After disabling its annoying ads and broken functionality, FeedMe 3.7.1 has been released. This maintenance release gives users multiple enhancements to its view menu that lets you remove the ads and reformat your news reader’s view before downloading your RSS feeds.

    A hot-fix release of the popular open source RSS aggregator Crunchzilla has been made available. This release fixes the handling of bug IDs in its trending page as well as the issues caused by several Apple’s latest OS releases https://www.google.md/url?sa=t&url=https://unalgedtio.weebly.com

    6add127376 philsav

  15. zaczav表示:

    Unlimited security
    You may use CRYPTED-EMAIL as long as you own it and continue to use it regularly. The tool can also be used to provide 2-factor authentication for your online accounts, protecting them against any attempt to open the files sent in email, including malicious links. Just attach them to an email and use CRYPTED-EMAIL to enable the protection.
    Download CRYPTED-EMAIL for free from: http://www.serbiancafe.com/lat/diskusije/new/redirect.php?url=https://dercmipeeso.weebly.com

    6add127376 zaczav

  16. bayeidd表示:

    18. iEfiUP
    Probably one of the most straightforward photo editing software programs for Windows is iEfiUP. It does not require any knowledge from the user, allowing him/her to get the job done at their ease.

    Why?
    Because iEfiUP is equipped with convenient tools that make the editing process easy and intuitive. Moreover, due to a variety of functions and a clean interface, iEfiUP is fairly easy to get around even for some http://www.reachwaterfront.com/frame.asp?frameurl=https://rimerloso.weebly.com

    6add127376 bayeidd

  17. delralst表示:

    User feedback highlights its strong efficiency of removing internet browser history, tabs, cookies, temporary files and other contents after the scan has been finished.

    Tablet PC, Smartphone apps, Wearables
    Hybrid Passwords Thief is a must have passwords management tool for those who run the Top 10 electronic shopping websites such as Google Play, Apple iTunes, Amazon, among others. It features ability to manage passwords, auto log-in and auto fill all your passwords saving time in most of important situations https://precbeauprevle.weebly.com

    6add127376 delralst

  18. venddeb表示:

    Some functions may not work as expected. As a result, we can only recommend using it for some simple tasks, and not as a serious resume application.
    We tested Student Resume Writer Personal Edition on Windows 10, Microsoft Office 365 ProPlus edition 15.34, its latest version.
    Pros
    It is absolutely free, so it is a complete no-cost solution.
    It is not complicated to work with, allowing you to create a resume without any issues.
    The interface is easy to handle.
    The application won’t take up a lot of resources.
    Cons
    It’s been inactive for a long time. https://ringchildpapu.weebly.com

    6add127376 venddeb

  19. ofaupri表示:

    The calculator supports square root operations with an easy to use method and can also display reverse and irregular decimal degrees as a zoom in.
    WP34s also displays the analogue line plot, shows the selected system of paper and displays icons to control data typing.

    More and more businesses are looking to make the most of the web to reduce overheads and increase visibility. Through a series of sessions you will learn the essentials of HTML5 and CSS3 as well as how to design a creative and http://www.103.kz/iframe/?id=10357093&ref=http://antibay-streed.blogspot.com&url=https://meoveraloo.weebly.com

    6add127376 ofaupri

  20. jangovi表示:

    However, that does not mean Quick Flash Player has died; it has only gone dormant, waiting for its revival.
    General Features
    File upload
    You can upload videos and photos just as you would when using the e-mail application or the File > Import selection box. If you want to create a project-based gallery, you can just drag-and-drop video files there.
    Hot keys
    Just as all the applications that support HTML5 browsers do, Quick Flash Player offers many https://areromco.weebly.com

    6add127376 jangovi

  21. garyal表示:

    ■ Keynote
    ■ Photoshop CS6 or higher
    Image download
    EPS 2 is a custom ERP (Enterprise Resource Planning) software application that is designed to organize and automate your event production for management, control and sales. It’s that important in this day & age to have access to a well organized and well managed event planning system.
    For event photographers this means:
    ■ Background job. It loads in non-stop.
    https://tisdinetti.weebly.com

    6add127376 garyal

  22. MichaelPlela表示:

    stromectol for sale ivermectin without a doctor prescription prescribing stromectol

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

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