java使用POI进行 Excel文件解析
package com.timevale.esign.vip.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import net.sf.json.JSONObject; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFCell; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.timevale.esign.db.bean.ErrRefLog; import com.timevale.esign.db.utils.StringUtil; import com.timevale.esign.db.utils.UUIDGenerator; import com.timevale.esign.vip.bean.ExcelDataBean; import com.timevale.esign.vip.constants.ExcelConstans; /** * 类名：ExcellUtils.java <br/> * 功能说明：excel解析工具类 <br/> * 修改历史： <br/> * 1.[2015年10月13日上午9:59:25]创建类 by hewu */ public class ExcelUtil { Workbook wb = null; List<String[]> dataList = new ArrayList<String[]>(100); private Logger LOG = LoggerFactory.getLogger(ExcelUtil.class); /** * 功能说明：构造器 * @param path 修改历史：<br/> * 1.[2015年10月13日下午3:52:19] 创建方法 by hewu */ public ExcelUtil(final String path) { try { final InputStream inp = new FileInputStream(path); this.wb = WorkbookFactory.create(inp); } catch (FileNotFoundException e) { LOG.error("error to find excel File .", e); e.printStackTrace(); } catch (InvalidFormatException e) { LOG.error("error to InvalidFormat excel File .", e); e.printStackTrace(); } catch (IOException e) { LOG.error("error to deal excel File and found a IOException.", e); e.printStackTrace(); } } public ExcelUtil() { super(); } /** * 功能说明：读取Excel所有数据，包含header * @param sheetIndex sheet下标 * @return <br/> * 修改历史：<br/> * 1.[2015年10月13日下午3:21:47] 创建方法 by hewu */ public final ExcelDataBean readExcel(int sheetIndex) { ExcelDataBean bean = new ExcelDataBean(); int columnNum = 0; final Sheet sheet = this.wb.getSheetAt(sheetIndex); String name = sheet.getRow(0).getCell(0).getStringCellValue().trim(); if (name.contains("企业")) { bean.setType(ExcelConstans.USER_TYPE_ORGANIZE); } else { bean.setType(ExcelConstans.USER_TYPE_PERSON); } if (sheet.getRow(0) != null) { columnNum = sheet.getRow(0).getLastCellNum() - sheet.getRow(0).getFirstCellNum(); } if (columnNum > 0) { // for (Row row : sheet) { int rowNum = sheet.getLastRowNum(); for (int k = 1; k <= rowNum; k++) { final String[] singleRow = new String[columnNum]; int n = 0; for (int i = 0; i < columnNum; i++) { final Cell cell = sheet.getRow(k).getCell(i, Row.CREATE_NULL_AS_BLANK); switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: singleRow[n] = ""; break; case Cell.CELL_TYPE_BOOLEAN: singleRow[n] = Boolean.toString(cell .getBooleanCellValue()); break; // 数值 case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { singleRow[n] = String.valueOf(cell .getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); String temp = cell.getStringCellValue(); // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串 if (temp.indexOf(".") > -1) { singleRow[n] = String.valueOf(new Double(temp)) .trim(); } else { singleRow[n] = temp.trim(); } } break; case Cell.CELL_TYPE_STRING: singleRow[n] = cell.getStringCellValue().trim(); break; case Cell.CELL_TYPE_ERROR: singleRow[n] = ""; break; case Cell.CELL_TYPE_FORMULA: cell.setCellType(Cell.CELL_TYPE_STRING); singleRow[n] = cell.getStringCellValue(); if (singleRow[n] != null) { singleRow[n] = singleRow[n].replaceAll("#N/A", "") .trim(); } break; default: singleRow[n] = ""; break; } n++; } // 如果第一行为空，跳过 final StringBuffer str1 = new StringBuffer(); for (int t = 0; t < columnNum; t++) { str1.append(singleRow[t]); } if (!StringUtil.isNull(str1.toString())) { this.dataList.add(singleRow); } } } bean.setColData(dataList); return bean; } /** * 功能说明：读取Excel，返回Excel最大行index值，实际行数要加1 * @param sheetIndex sheet下标 * @return <br/> * 修改历史：<br/> * 1.[2015年10月13日下午3:21:58] 创建方法 by hewu */ public final int getRowNum(final int sheetIndex) { final Sheet sheet = this.wb.getSheetAt(sheetIndex); return sheet.getLastRowNum(); } /** * 功能说明：读取Excel,返回数据的列数 * @param sheetIndex sheet下标 * @return int <br/> * 修改历史：<br/> * 1.[2015年10月13日下午3:22:07] 创建方法 by hewu */ public final int getColumnNum(final int sheetIndex) { final Sheet sheet = this.wb.getSheetAt(sheetIndex); final Row row = sheet.getRow(0); if (row != null && row.getLastCellNum() > 0) { return row.getLastCellNum(); } return 0; } /** * 读取Excel,获取某一行数据 * @param sheetIndex sheet下标 * @param rowIndex 计数从0开始，rowIndex为0代表header行 * @return String[] 修改历史：<br/> * 1.[2015年10月13日下午3:22:07] 创建方法 by hewu */ public final String[] getRowData(final int sheetIndex, final int rowIndex) { String[] dataArray = null; if (rowIndex > this.getColumnNum(sheetIndex)) { return dataArray; } else { dataArray = new String[this.getColumnNum(sheetIndex)]; return this.dataList.get(rowIndex); } } /** * 功能说明：读取Excel获取某一列数据 * @param sheetIndex sheet下标 * @param colIndex 列下标 * @return <br/> * 修改历史：<br/> * 1.[2015年10月13日下午3:22:46] 创建方法 by hewu */ public final String[] getColumnData(final int sheetIndex, final int colIndex) { String[] dataArray = null; if (colIndex > this.getColumnNum(sheetIndex)) { return dataArray; } else { if (this.dataList != null && this.dataList.size() > 0) { dataArray = new String[this.getRowNum(sheetIndex) + 1]; int index = 0; for (String[] rowData : this.dataList) { if (rowData != null) { dataArray[index] = rowData[colIndex]; index++; } } } } return dataArray; } /** * 功能说明：导出excel（按照参数 userType导出个人/企业的失败日志） * @param templatePath 导出模板路径 * @param ErrRefLogs 错误对象 * @param userType 用户类型 * @return String 导出的excel的临时文件<br/> * 修改历史：<br/> * 1.[2015年10月13日下午8:06:48] 创建方法 by hewu */ public String writeExcel(String templatePath, List<ErrRefLog> errRefLogs, int userType) { String onceFile = ""; onceFile = FileUtil.Copy(new File(templatePath), getClass() .getResource("/errlog").getFile() + UUIDGenerator.getUUID() + ".xlsx"); FileInputStream input = null; Workbook workbook = null; String regStr = "[\\【\\,\\】]"; try { input = new FileInputStream(new File(onceFile)); workbook = WorkbookFactory.create(input); } catch (Exception e) { e.printStackTrace(); this.LOG.error("error to create write Excel", e); } final Sheet sheet = workbook.getSheetAt(0); if (userType == ExcelConstans.USER_TYPE_PERSON) { // 解析每一条数据,增加行 for (int i = 0; i < errRefLogs.size(); i++) { ErrRefLog log = errRefLogs.get(i); final Row row = sheet.createRow(i + ExcelConstans.EXCEL_START_INDEX); // 增加列数据 // ErrRefLog errRefLog = ErrRefLogs.get(i); final CellStyle ss = workbook.createCellStyle(); ss.setLocked(false); final Cell nameC = row.createCell(0); nameC.setCellStyle(ss); nameC.setCellType(XSSFCell.CELL_TYPE_STRING); nameC.setCellValue(log.getName());// 写入内容 final Cell mobileC = row.createCell(1); mobileC.setCellType(XSSFCell.CELL_TYPE_STRING); mobileC.setCellStyle(ss); mobileC.setCellValue(log.getMobile());// 写入内容 final Cell idNoC = row.createCell(2); idNoC.setCellType(XSSFCell.CELL_TYPE_STRING); idNoC.setCellStyle(ss); idNoC.setCellValue(log.getIdNo());// 写入内容 // final Cell emailC = row.createCell(3); // emailC.setCellType(XSSFCell.CELL_TYPE_STRING); // emailC.setCellStyle(ss); // emailC.setCellValue(log.getEmail());// 写入内容 final Cell companyC = row.createCell(3); companyC.setCellType(XSSFCell.CELL_TYPE_STRING); companyC.setCellStyle(ss); companyC.setCellValue(log.getOrgan());// 写入内容 StringBuffer errReason = new StringBuffer(); final Cell reasonC = row.createCell(4); reasonC.setCellType(XSSFCell.CELL_TYPE_STRING); reasonC.setCellStyle(ss); if(log.getErrReason().contains("【")){ String [] reason = log.getErrReason().split(regStr); errReason.append(reason[0]+"【"); errReason.append("*"+reason[1].subSequence(reason[1].length()-1, reason[1].length())+","+reason[2].substring(0, 6)+"****"); errReason.append("】"+reason[3]); reasonC.setCellValue(errReason.toString()); }else { reasonC.setCellValue(log.getErrReason()); } } } else if (userType == ExcelConstans.USER_TYPE_ORGANIZE) { // 解析每一条数据,增加行 for (int i = 0; i < errRefLogs.size(); i++) { final ErrRefLog log = errRefLogs.get(i); final JSONObject obj = JSONObject.fromObject(log .getContentJson()); final Row row = sheet.createRow(i + ExcelConstans.EXCEL_START_INDEX); // 增加列数据 // ErrRefLog errRefLog = ErrRefLogs.get(i); final CellStyle ss = workbook.createCellStyle(); ss.setLocked(false); ss.setAlignment(CellStyle.ALIGN_CENTER); // 企业名称 final Cell nameC = row.createCell(0); nameC.setCellStyle(ss); nameC.setCellType(XSSFCell.CELL_TYPE_STRING); nameC.setCellValue(log.getName()); // 手机号 final Cell mobileC = row.createCell(1); mobileC.setCellType(XSSFCell.CELL_TYPE_STRING); mobileC.setCellStyle(ss); mobileC.setCellValue(log.getMobile()); // 组织机构代码证号 final Cell organCodeC = row.createCell(2); organCodeC.setCellType(XSSFCell.CELL_TYPE_STRING); organCodeC.setCellStyle(ss); organCodeC.setCellValue(log.getOrganCode()); // 邮箱 // final Cell emailC = row.createCell(3); // emailC.setCellType(XSSFCell.CELL_TYPE_STRING); // emailC.setCellStyle(ss); // emailC.setCellValue(log.getEmail()); // 单位类型 final Cell companyC = row.createCell(3); companyC.setCellType(XSSFCell.CELL_TYPE_STRING); companyC.setCellStyle(ss); companyC.setCellValue(obj.getInt("organType")); // 注册类型 final Cell userTypeC = row.createCell(4); userTypeC.setCellType(XSSFCell.CELL_TYPE_STRING); userTypeC.setCellStyle(ss); userTypeC.setCellValue(obj.getInt("userType")); // 工商注册号 final Cell regCodeC = row.createCell(5); regCodeC.setCellType(XSSFCell.CELL_TYPE_STRING); regCodeC.setCellStyle(ss); regCodeC.setCellValue(obj.getString("regCode")); // 法定代表姓名 final Cell legalNameC = row.createCell(6); legalNameC.setCellType(XSSFCell.CELL_TYPE_STRING); legalNameC.setCellStyle(ss); legalNameC.setCellValue(obj.getString("legalName")); // 法定代表人身份证号 final Cell legalIdNoC = row.createCell(7); legalIdNoC.setCellType(XSSFCell.CELL_TYPE_STRING); legalIdNoC.setCellStyle(ss); legalIdNoC.setCellValue(obj.getString("legalIdNo")); // 法定代表人归属地 final Cell legalAreaC = row.createCell(8); legalAreaC.setCellType(XSSFCell.CELL_TYPE_STRING); legalAreaC.setCellStyle(ss); legalAreaC.setCellValue(obj.getInt("legalArea")); // 代理人姓名 final Cell agentNameC = row.createCell(9); agentNameC.setCellType(XSSFCell.CELL_TYPE_STRING); agentNameC.setCellStyle(ss); agentNameC.setCellValue(obj.getString("agentName")); // 代理人姓名 final Cell agentIdNoC = row.createCell(10); agentIdNoC.setCellType(XSSFCell.CELL_TYPE_STRING); agentIdNoC.setCellStyle(ss); agentIdNoC.setCellValue(obj.getString("agentIdNo")); // 公司地址 final Cell addressC = row.createCell(11); addressC.setCellType(XSSFCell.CELL_TYPE_STRING); addressC.setCellStyle(ss); addressC.setCellValue(obj.getString("address")); // 经营范围 final Cell scopeC = row.createCell(12); scopeC.setCellType(XSSFCell.CELL_TYPE_STRING); scopeC.setCellStyle(ss); scopeC.setCellValue(obj.getString("scope")); // 失败原因 StringBuffer errReason = new StringBuffer(); final Cell reasonC = row.createCell(13); reasonC.setCellType(XSSFCell.CELL_TYPE_STRING); reasonC.setCellStyle(ss); if(log.getErrReason().contains("【")){ String [] reason = log.getErrReason().split(regStr); errReason.append(reason[0]+"【"); errReason.append("*"+reason[1].subSequence(reason[1].length()-1, reason[1].length())+","+reason[2].substring(0, 6)+"****"); errReason.append("】"+reason[3]); reasonC.setCellValue(errReason.toString()); }else { reasonC.setCellValue(log.getErrReason()); } } } else { } // 新建一输出流 FileOutputStream fout; try { fout = new FileOutputStream(onceFile); // 存盘 workbook.write(fout); fout.flush(); // 结束关闭 fout.close(); } catch (FileNotFoundException e) { e.printStackTrace(); this.LOG.error("fileNotFoundException", e); } catch (IOException e) { e.printStackTrace(); this.LOG.error("IOException", e); } return onceFile; } // public static void main(String[] args) { // // 导入excel // // ExcelUtils utils = new ExcelUtils("D:\\用户导入模版.xls"); // ExcelUtil utils = new ExcelUtil("d:\\企业用户导入模版 .xlsx"); // List<String[]> list = utils.readExcel(0); // for (int i = 0; i < list.size(); i++) { // String[] str = list.get(i); // for (int j = 0; j < str.length; j++) { // System.out.println(str[j]); // } // // } // //导出excel // ExcelUtils utils = new ExcelUtils( // ExcelConstans.ERR_ORGANIZE_ACCOUNT_EXPORT_DIR); // List<ErrRefLog> accounts = new ArrayList<ErrRefLog>(); // String path = utils.writeExcel( // ExcelConstans.ERR_ORGANIZE_ACCOUNT_EXPORT_DIR, accounts, 2); // System.out.println(path); // } // public static void main(String[] args) { // String regStr = "[\\【\\,\\】]"; // String s = "我是【张三,430381199007086018】使用"; // System.out.println(s.split(regStr)[2]); // // } }
文件解析的相关依赖包可以去maven库下载
