poi解析excel简单小例子
编程技术 / houtizong 发布于 3年前 134
package com.test;import java.io.File;import java.io.IOException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Set;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ParseExcel {public static void main(String[] args) {Map<String, List<List<String>>> sheetsMap = getExcelData("D:/aaa.xlsx");System.out.println("sheet total:"+sheetsMap.size());Set<String> keys = sheetsMap.keySet();//查看解析记录for(String key : keys){List<List<String>> sheets = sheetsMap.get(key);System.out.println("====================="+key+"=======================");for (int j = 0; j < sheets.size(); j++) {List<String> cellList = sheets.get(j);for (int k = 0; k < cellList.size(); k++) {System.out.print(cellList.get(k)+" ");}System.out.println();}System.out.println();}}/** * 根据指定sheet名获取数据 * @param sheetName sheet名称 * @param path指定excel文件 * @return 返回指定sheetName的sheet数据 */public static List<List<String>> getSheetByName(String sheetName,String path){// 获取所有数据Map<String, List<List<String>>> sheetsMap = getExcelData(path);// 获取指定的数据List<List<String>> list = sheetsMap.get(sheetsMap);return list;}/** * 获取excel数据 * @param path excel文件的路径 * @return 返回整个excel的数据，可根据sheet名在map中获取每个sheet的数据， * 可根据map中的list获取每行的列表数据 */public static Map<String,List<List<String>>> getExcelData(String path) {// 总数据Map<String,List<List<String>>> sheetsMap = new HashMap<String,List<List<String>>>();// 页数据List<List<String>> rowsList = null;// 行数据List<String> cellList = null;XSSFWorkbook workbook = null;File file = new File(path);try {workbook = new XSSFWorkbook(file); int sheetTotal = workbook.getNumberOfSheets(); // 1.循环遍历每一个sheetfor (int i = 0; i < sheetTotal; i++) {XSSFSheet sheet = workbook.getSheetAt(i);int rowTotal = sheet.getLastRowNum(); rowsList = new ArrayList<List<String>>(); // 2.循环遍历每一行rowfor (int j = 0; j < rowTotal; j++) {XSSFRow row = sheet.getRow(j);if (row != null) {int cellTotal = row.getLastCellNum(); cellList = new ArrayList<String>(); // 3.循环遍历每一列cellfor (int k = 0; k < cellTotal; k++) {if (row.getCell(k) != null) {// 4.判断日期，也可以用HSSFDataFormat.getBuiltinFormat("m/d/yy")替换14if(row.getCell(k).getCellStyle().getDataFormat()==14){SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");Date date = row.getCell(k).getDateCellValue();if(date!=null){cellList.add(format.format(date).toString());}}else{cellList.add(row.getCell(k).toString());}}}rowsList.add(cellList);}}// 可很据sheet名获得数据sheetsMap.put(sheet.getSheetName(), rowsList);}} catch (InvalidFormatException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {// 关闭工作簿workbook.close();} catch (IOException e) {e.printStackTrace();}}return sheetsMap;}}
日期格式源码
static { List m = new ArrayList(); putFormat(m, 0, "General"); putFormat(m, 1, "0"); putFormat(m, 2, "0.00"); putFormat(m, 3, "#,##0"); putFormat(m, 4, "#,##0.00"); putFormat(m, 5, "\"$\"#,##0_);(\"$\"#,##0)"); putFormat(m, 6, "\"$\"#,##0_);[Red](\"$\"#,##0)"); putFormat(m, 7, "\"$\"#,##0.00_);(\"$\"#,##0.00)"); putFormat(m, 8, "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)"); putFormat(m, 9, "0%"); putFormat(m, 10, "0.00%"); putFormat(m, 11, "0.00E+00"); putFormat(m, 12, "# ?/?"); putFormat(m, 13, "# ??/??"); putFormat(m, 14, "m/d/yy"); putFormat(m, 15, "d-mmm-yy"); putFormat(m, 16, "d-mmm"); putFormat(m, 17, "mmm-yy"); putFormat(m, 18, "h:mm AM/PM"); putFormat(m, 19, "h:mm:ss AM/PM"); putFormat(m, 20, "h:mm"); putFormat(m, 21, "h:mm:ss"); putFormat(m, 22, "m/d/yy h:mm"); for (int i = 23; i <= 36; i++) { putFormat(m, i, "reserved-0x" + Integer.toHexString(i)); } putFormat(m, 37, "#,##0_);(#,##0)"); putFormat(m, 38, "#,##0_);[Red](#,##0)"); putFormat(m, 39, "#,##0.00_);(#,##0.00)"); putFormat(m, 40, "#,##0.00_);[Red](#,##0.00)"); putFormat(m, 41, "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)"); putFormat(m, 42, "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)"); putFormat(m, 43, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)"); putFormat(m, 44, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)"); putFormat(m, 45, "mm:ss"); putFormat(m, 46, "[h]:mm:ss"); putFormat(m, 47, "mm:ss.0"); putFormat(m, 48, "##0.0E+0"); putFormat(m, 49, "@"); String[] ss = new String[m.size()]; m.toArray(ss); _formats = ss; }
上一篇：java线程详解
请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！
技术博客集 - 网站简介：
前后端技术：
后端基于Hyperf2.1框架开发,前端使用Bootstrap可视化布局系统生成
网站主要作用：
1.编程技术分享及讨论交流，内置聊天系统;
2.测试交流框架问题，比如：Hyperf、Laravel、TP、beego;
3.本站数据是基于大数据采集等爬虫技术为基础助力分享知识，如有侵权请发邮件到站长邮箱，站长会尽快处理;
4.站长邮箱：[email protected];
文章归档
文章标签
友情链接
