透過Java解析Excel檔案
也是一個工作中遇到的情境,很多單位想讓內部人員利用Excel維護資料(因為維護上比較簡單方便),且想讓這份資料透過網頁呈現給一般網友閱讀,所以整體後端的流程應該是:
- 將維護的Excel透過網頁介面上傳
- 將上傳的Excel轉換成XML
- 網頁去讀取這份XML來呈現
透過以上的三個步驟,就可以完成客戶想要效果。
本範例展示的是讀取Excel的XLS格式(新版Excel為XLSX格式),並轉換成陣列的方法(寫入XML在前面的範例就有了),我們選用讀取Excel的Library是Apache POI,而檔內已經有以下的資料,其中Total是公式欄位,計算Price x Quantity:
ID | Name | Price | Quantity | Total |
100001 | 產品名稱1 | 10 | 5 | 50 |
100002 | 產品名稱2 | 20 | 10 | 200 |
100003 | 產品名稱3 | 30 | 15 | 450 |
以下是讀取的程式範例:
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, ]
cheapest online pharmacy india [url=https://indianpharmstar.com/#]indian pharm star[/url] cheapest online pharmacy india
Как купить аттестат 11 класса с официальным упрощенным обучением в Москве
Приобретение диплома ПТУ с сокращенной программой обучения в Москве
Реально ли приобрести диплом стоматолога? Основные этапы
V3Events and Weddings knows how important wedding planning is to make a vacation spot marriage ceremony in Delhi NCR memorable.
It is feasible to create an event on Fb, but all the knowledge resembling date, place, and time, and schedule, link to register – and invite your contacts to attend.
Всё, что нужно знать о покупке аттестата о среднем образовании без рисков
online medication: canadianpharm1st.com – prescription drugs online without doctor
These audio system must rather symbolize the forerunners of the principle Paṣ̌tūn movement towards the east, but when and where they split off is at present unattainable to say.
In Part 1B, the vaccine would be accessible to individuals over age 75 and important staff together with first responders, correction officers, postal employees, grocery retailer workers, teachers, college support employees, clergy, public transit workers, daycare workers, and manufacturing workers.
Whether you are a real Aquarius or simply love astrology, this pendant is a superb way to wear your star signal with delight.
reputable indian pharmacies indian pharmacy reputable indian online pharmacy
reputable mexican pharmacies online: mexicanpharmeasy.com – best online pharmacies in mexico
Confidence will be found within the smallest particulars, from the addition of cute gloves to a gorgeous, rhinestone-coated headpiece.
Aw, thi was a vety gpod post. Fihding thhe tim annd actual
eeffort to make a veery good article… butt wwhat can I say… I procrastinate a llot and ever seem too gett anmything done.
best india pharmacy: indian pharm – reputable indian online pharmacy
The park anchors a large tourism trade primarily based primarily in close by Gatlinburg and Pigeon Forge, which consists of such attractions as Dollywood, essentially the most visited ticketed attraction in Tennessee, Ober Gatlinburg, and Ripley’s Aquarium of the Smokies.
вывод из запоя в екатеринбурге вывод из запоя в екатеринбурге .
вывод из запоя на дому екатеринбург вывод из запоя на дому екатеринбург .
I really like seeing musicians who stay true to their hearts and who refuse to be boxed in, as a result of so many people do attempt to pigeonhole you in a single type or another.
вывод из запоя на дому в екатеринбурге http://www.zavitai.mybb.social/viewtopic.php?id=92/ .
Coolzino kasyno na ?ywo Coolzino kasyno na ?ywo .
Как купить аттестат 11 класса с официальным упрощенным обучением в Москве
buy medicines online in india: indian pharmacy – pharmacy website india
Historically mangalsutra is necklace through which a set of some auspicious gold and black beads is strung collectively.
вывод из запоя в екатеринбурге http://mozaisk.anihub.me/viewtopic.php?id=4372 .
mexico drug stores pharmacies: MexicanPharmEasy – mexican mail order pharmacies
Secondly, I’m sticking round right here on Neocities, however I made a one web page microblog on Nekoweb for fun.
Купить диплом Барнаул
выведение из запоя http://www.motik13.0pk.me/viewtopic.php?id=2004 .