poi解析Excel2007

poi解析excel 2007 xlsx 文件

支持的表头格式为下图：

是通过表头文字设置默认列顺序，只要表头名不变，列顺序可以随便调整不影响读取数据

import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.io.Serializable;import java.io.UnsupportedEncodingException;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Map;import java.util.regex.Pattern;import org.activiti.engine.impl.util.json.JSONException;import org.activiti.engine.impl.util.json.JSONObject;import org.apache.http.HttpEntity;import org.apache.http.HttpResponse;import org.apache.http.NameValuePair;import org.apache.http.client.ClientProtocolException;import org.apache.http.client.HttpClient;import org.apache.http.client.entity.UrlEncodedFormEntity;import org.apache.http.client.methods.HttpPost;import org.apache.http.impl.client.DefaultHttpClient;import org.apache.http.message.BasicNameValuePair;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.ss.usermodel.CellValue;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.thinkgem.jeesite.common.config.Global;import com.thinkgem.jeesite.common.mapper.JsonMapper;import com.thinkgem.jeesite.common.proxy.HttpClientHelper;public class ImportFeeBillExcelXlsx {private final String[] colsType = new String[] { "房屋地址", "租客", "水费", "电费" , "水电费总额"};private final String[] cols = new String[] { "房屋地址", "姓名", "手机号", "上月读数","本月读数", "实用方数", "单价", "计费周期", "费用", "上月读数", "本月读数", "实用度数", "单价","计费周期", "费用" , "水电费总额"};/** * 错误定义 */// 上传文件不正确private final String OUT_OR_NO = "outOrNo";private final String DATA_NULL = "不能为空";private final String DATA_DATE = "周期格式不正确";public final String SUCCESS = "success"; // 成功private Logger logger = LoggerFactory.getLogger(getClass());private XSSFFormulaEvaluator evaluator = null;/** * 处理excel * @param inputStream excel流 * @param map  * @return */public String dealExcelXlsx(InputStream inputStream, Map<String, Object> map) {String rightFile = "";XSSFWorkbook workbook = this.getWorkBook(inputStream);evaluator = new XSSFFormulaEvaluator(workbook);// 获取sheet数int sheetsCount = workbook.getNumberOfSheets();// 顺序读写sheetfor (int i = 0; i < sheetsCount; i++) {// 存放excel中的数据List<Bill> list = new ArrayList<Bill>();// 错误信息List<String> errorList = new ArrayList<String>();// 验证文件是否正确 和判断非空 数据类型rightFile = this.validateExcel(workbook, i, list, errorList);if ("".equals(rightFile)) {if (list.size() == 0) {return "{\"ret\":1,\"msg\":\"数据为空\"}";                }String feeJson = JsonMapper.toJsonString(list);System.out.println("feejson= " + feeJson);try {String url = "/impoport/data";HttpClient httpclient = new DefaultHttpClient();HttpPost httppost = new HttpPost(url);// 创建参数队列List<NameValuePair> formParams = new ArrayList<NameValuePair>();formParams.add(new BasicNameValuePair("feeJson", feeJson));formParams.add(new BasicNameValuePair("token", map.get("token").toString()));httppost.setEntity(new UrlEncodedFormEntity(formParams, "UTF-8"));HttpResponse httpResponse = httpclient.execute(httppost);HttpEntity resEntity = httpResponse.getEntity();String responseText = null;if (resEntity != null) {logger.info("----------------------------------------");logger.info(httpResponse.getStatusLine().toString());logger.info("返回长度: " + resEntity.getContentLength());logger.info("返回类型: " + resEntity.getContentType());// 获取返回信息InputStream in = resEntity.getContent();responseText = HttpClientHelper.getStringByInputStream(in);logger.info("responseText = " + responseText);JSONObject jsonObject = new JSONObject(responseText);if (in != null) {in.close();}return jsonObject.toString();}} catch (UnsupportedEncodingException e) {e.printStackTrace();} catch (ClientProtocolException e) {e.printStackTrace();} catch (IllegalStateException e) {e.printStackTrace();} catch (JSONException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}} else {// 上传文件不正确if (OUT_OR_NO.equals(rightFile)) {return "{\"ret\":1,\"msg\":\"标头不正确\"}";}}}return "success";}/** * 验证数据是否为空和模板是否正确 *  * @param theForm *            导入BO * @return String 信息 */private String validateExcel(XSSFWorkbook workbook, int sheetIndex, List<Bill> result, List<String> errorList) {// 读取第一个工作薄XSSFSheet sheet = workbook.getSheetAt(sheetIndex);// 记录列的顺序int cellNum[] = new int[cols.length];boolean flag = validateExcelHead(sheet, cellNum);if (flag) {// 得到行数int rowCount = sheet.getLastRowNum();XSSFRow row = null;for (int i = 2; i <= rowCount; i++) {row = sheet.getRow(i);// 判断后面为空if (row == null) {continue;}// 房屋地址if ("".equals(getCellFormatValue(row.getCell(cellNum[0])))) {errorList.add((i + 1) + cols[0] + DATA_NULL);}// 租客姓名if ("".equals(getCellFormatValue(row.getCell(cellNum[1])))) {errorList.add((i + 1) + cols[1] + DATA_NULL);}// 租客手机号if ("".equals(getCellFormatValue(row.getCell(cellNum[2])))) {errorList.add((i + 1) + cols[2] + DATA_NULL);}// 水费上月读数if ("".equals(getCellFormatValue(row.getCell(cellNum[3])))) {errorList.add((i + 1) + cols[3] + DATA_NULL);}// 水费本月读数if ("".equals(getCellFormatValue(row.getCell(cellNum[4])))) {errorList.add((i + 1) + cols[4] + DATA_NULL);}// 水费实用方数if ("".equals(getCellFormatValue(row.getCell(cellNum[5])))) {errorList.add((i + 1) + cols[5] + DATA_NULL);}// 水费单价if ("".equals(getCellFormatValue(row.getCell(cellNum[6])))) {errorList.add((i + 1) + cols[6] + DATA_NULL);}// 水费计费周期if ("".equals(getCellFormatValue(row.getCell(cellNum[7])))) {errorList.add((i + 1) + cols[7] + DATA_NULL);} else if(getCellFormatValue(row.getCell(cellNum[7])).split("-").length != 2) {errorList.add((i + 1) + cols[13] + DATA_DATE);}// 水费费用if ("".equals(getCellFormatValue(row.getCell(cellNum[8])))) {errorList.add((i + 1) + cols[8] + DATA_NULL);}// 电费上月读数if ("".equals(getCellFormatValue(row.getCell(cellNum[9])))) {errorList.add((i + 1) + cols[9] + DATA_NULL);}// 电费本月读数if ("".equals(getCellFormatValue(row.getCell(cellNum[10])))) {errorList.add((i + 1) + cols[10] + DATA_NULL);}// 电费实用方数if ("".equals(getCellFormatValue(row.getCell(cellNum[11])))) {errorList.add((i + 1) + cols[11] + DATA_NULL);}// 电费单价if ("".equals(getCellFormatValue(row.getCell(cellNum[12])))) {errorList.add((i + 1) + cols[12] + DATA_NULL);}// 电费计费周期if ("".equals(getCellFormatValue(row.getCell(cellNum[13])))) {errorList.add((i + 1) + cols[13] + DATA_NULL);} else if(getCellFormatValue(row.getCell(cellNum[13])).split("-").length != 2) {errorList.add((i + 1) + cols[13] + DATA_DATE);}// 电费费用if ("".equals(getCellFormatValue(row.getCell(cellNum[14])))) {errorList.add((i + 1) + cols[14] + DATA_NULL);}// 水电费总额if ("".equals(getCellFormatValue(row.getCell(cellNum[15])))) {errorList.add((i + 1) + cols[15] + DATA_NULL);}// 没有错误if (errorList.size() == 0) {Bill bill = new Bill();bill.setHouseinfo(getCellFormatValue(row.getCell(cellNum[0])));bill.setName(getCellFormatValue(row.getCell(cellNum[1])));bill.setPhone(getCellFormatValue(row.getCell(cellNum[2])));List<ImportFeeBillExcelXlsx.Fee> fees = new ArrayList<ImportFeeBillExcelXlsx.Fee>();Fee fee = new Fee();fee.setName("水费");fee.setLastRead(getCellFormatValue(row.getCell(cellNum[3])));fee.setCurRead(getCellFormatValue(row.getCell(cellNum[4])));fee.setCurUse(getCellFormatValue(row.getCell(cellNum[5])));fee.setDanjia(getCellFormatValue(row.getCell(cellNum[6])));String period = getCellFormatValue(row.getCell(cellNum[7]));fee.setStartDate(period.split("-")[0]);fee.setEndDate(period.split("-")[1]);fee.setPirce(getCellFormatValue(row.getCell(cellNum[8])));Fee fee1 = new Fee();fee1.setName("电费");fee1.setLastRead(getCellFormatValue(row.getCell(cellNum[9])));fee1.setCurRead(getCellFormatValue(row.getCell(cellNum[10])));fee1.setCurUse(getCellFormatValue(row.getCell(cellNum[11])));fee1.setDanjia(getCellFormatValue(row.getCell(cellNum[12])));String period1 = getCellFormatValue(row.getCell(cellNum[13]));fee1.setStartDate(period1.split("-")[0]);fee1.setEndDate(period1.split("-")[1]);fee1.setPirce(getCellFormatValue(row.getCell(cellNum[14])));fees.add(fee);fees.add(fee1);bill.setFees(fees);result.add(bill);}}} else {return OUT_OR_NO;}return "";}/** * 取导入文件 *  * @param theForm *            导入BO * @return Workbook 信息 */private XSSFWorkbook getWorkBook(InputStream in) {// HSSFWorkbook workBook = null;XSSFWorkbook workBook = null;try {workBook = new XSSFWorkbook(in);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return workBook;}/** * 判断表头,是否是模板中的表头 *  * @param row * @param num * @return */private boolean validateExcelHead(XSSFSheet sheet, int[] num) {XSSFRow row = sheet.getRow(0);// 获取列总数int cellCount = row.getLastCellNum();// 判断列数是否相等if (cellCount < cols.length) {return false;}// 获取第二行数据XSSFRow row1 = sheet.getRow(1);// colStart,colEnd 记录 cols 开始结束位置int count = 0, colStart = 0, colEnd = 0;// 获取每个单元格的内容for (int j = 0; j < cellCount; j++) {// 获取指定单元格的内容String cellContent = getCellFormatValue(row.getCell((short) j));if (!cellContent.equals("")) {colStart = 0;colEnd = 0;for (int i = 0; i < colsType.length; i++) {if (cellContent.equals(colsType[i])) {if (cellContent.equals("租客")) {colStart = 1;colEnd = 3;} else if (cellContent.equals("水费")) {colStart = 3;colEnd = 9;} else if (cellContent.equals("电费")) {colStart = 9;colEnd = 15;}break;}}}String cellContent1 = getCellFormatValue(row1.getCell((short) j));if (colEnd != 0) {for (int c = colStart; c < colEnd; c++) {// 判断列名是否在excel文件列表中if (cols[c].equals(cellContent1)) {num[c] = j;count++;break;}}} else {// 判断列表头是否为空if ("".equals(cellContent1)) {for (int c = 0; c < num.length; c++) {if (cols[c].equals(cellContent)) {num[c] = j;count++;break;}}}}}if (count != cols.length) {return false;}return true;}/** * 根据HSSFCell类型设置数据 *  * @param cell * @return */private String getCellFormatValue(XSSFCell cell) {String cellvalue = "";if (cell != null) {// 判断当前Cell的Typeswitch (cell.getCellType()) {// 如果当前Cell的Type为NUMERIC 判断单元格的值是否为数字类型case XSSFCell.CELL_TYPE_NUMERIC:// 判断当前的cell是否为Dateif (HSSFDateUtil.isCellDateFormatted(cell)) {// 如果是Date类型则，转化为Data格式// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00// cellvalue = cell.getDateCellValue().toLocaleString();// 方法2：这样子的data格式是不带带时分秒的：2011-10-12Date date = cell.getDateCellValue();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");cellvalue = sdf.format(date);} else {// 处理数字出现的各种情况，包括手机号，小数，整数DecimalFormat df = new DecimalFormat("#.#########");cellvalue = df.format(cell.getNumericCellValue());}break;// 公式case XSSFCell.CELL_TYPE_FORMULA:try {CellValue cellValue = evaluator.evaluate(cell);switch (cellValue.getCellType()) { // 判断公式类型case XSSFCell.CELL_TYPE_BOOLEAN:cellvalue = String.valueOf(cellValue.getBooleanValue());break;case XSSFCell.CELL_TYPE_NUMERIC:// 处理日期if (HSSFDateUtil.isCellDateFormatted(cell)) {Date date = cell.getDateCellValue();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");cellvalue = sdf.format(date);} else {DecimalFormat df = new DecimalFormat("#.#########");cellvalue = df.format(cell.getNumericCellValue());}break;case XSSFCell.CELL_TYPE_STRING:cellvalue = cellValue.getStringValue();break;case XSSFCell.CELL_TYPE_BLANK:cellvalue = "";break;case XSSFCell.CELL_TYPE_ERROR:cellvalue = "";break;case XSSFCell.CELL_TYPE_FORMULA:cellvalue = "";break;}} catch (Exception e) {cellvalue = cell.getStringCellValue().toString();cell.getCellFormula();}break;// 如果当前Cell的Type为STRIN 判断单元格的值是否为字符串类型case XSSFCell.CELL_TYPE_STRING:// 取得当前的Cell字符串cellvalue = cell.getRichStringCellValue().getString();break;// 判断单元格的值是否为布尔类型case XSSFCell.CELL_TYPE_BOOLEAN:cellvalue = String.valueOf(cell.getBooleanCellValue());break;// 空值case XSSFCell.CELL_TYPE_BLANK:cellvalue = "";break;case XSSFCell.CELL_TYPE_ERROR: // 故障System.out.println(" 读取文件存在故障");break;// 默认的Cell值default:cellvalue = "";}} else {cellvalue = "";}return cellvalue.trim();}/** * 正则验证 *  * @param value *            行数 * @param regex *            正则表达式 * @return boolean 信息 */@SuppressWarnings("unused")private boolean validateValueForNumber(String value, String... regex) {int enableNo = 0;boolean enable = false;String[] regexList = regex;for (String s : regexList) {enable = Pattern.matches(s, value);if (enable) {enableNo++;}}return 0 < enableNo ? true : false;}@SuppressWarnings("unused")private boolean validateValueForDate(String value) {boolean enable = true;SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");try {format.parse(value);} catch (Exception e) {enable = false;}return enable;}@SuppressWarnings("unused")private String getForDate(String value) {SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");Date d = new Date();try {d = format.parse(value);} catch (ParseException e) {e.printStackTrace();}String str = format.format(d);return str;}@SuppressWarnings("unused")private class Bill implements Serializable{private static final long serialVersionUID = 1L;private String name;private String phone;private String houseinfo;private List<Fee> fees;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getHouseinfo() {return houseinfo;}public void setHouseinfo(String houseinfo) {this.houseinfo = houseinfo;}public List<Fee> getFees() {return fees;}public void setFees(List<Fee> fees) {this.fees = fees;}}@SuppressWarnings("unused")private class Fee implements Serializable{private static final long serialVersionUID = 1L;private String name;private String lastRead;private String curRead;private String curUse;private String danjia;private String pirce;private String endDate;private String startDate;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getLastRead() {return lastRead;}public void setLastRead(String lastRead) {this.lastRead = lastRead;}public String getCurRead() {return curRead;}public void setCurRead(String curRead) {this.curRead = curRead;}public String getCurUse() {return curUse;}public void setCurUse(String curUse) {this.curUse = curUse;}public String getDanjia() {return danjia;}public void setDanjia(String danjia) {this.danjia = danjia;}public String getPirce() {return pirce;}public void setPirce(String pirce) {this.pirce = pirce;}public String getEndDate() {return endDate;}public void setEndDate(String endDate) {this.endDate = endDate;}public String getStartDate() {return startDate;}public void setStartDate(String startDate) {this.startDate = startDate;}}}