poi解析excel

最新推荐文章于 2024-03-17 13:43:49 发布

风行傲天

最新推荐文章于 2024-03-17 13:43:49 发布

阅读量231

点赞数

分类专栏： poi 文章标签： apache java servlet

本文链接：https://blog.csdn.net/fengxing_2/article/details/72957499

版权

poi 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

package com.thinkgem.jeesite.modules.cms.web.front.CRM;

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();
		// 顺序读写sheet
		for (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 {
					// http://apim.miju.net.dev/api/detail2.do?id=11292069
					String url = Global.getConfig("API_SITE_DOMAIN_house") + "/rest/oss/renth/bill/importfee";
					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的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC 判断单元格的值是否为数字类型
			case XSSFCell.CELL_TYPE_NUMERIC:
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					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_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;
		}
		
	}

}

package com.thinkgem.jeesite.modules.cms.web.front.CRM;

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();
// 顺序读写sheet
for (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 {
// http://apim.miju.net.dev/api/detail2.do?id=11292069
String url = Global.getConfig("API_SITE_DOMAIN_house") + "/rest/oss/renth/bill/importfee";
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的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC 判断单元格的值是否为数字类型
case XSSFCell.CELL_TYPE_NUMERIC:
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
// cellvalue = cell.getDateCellValue().toLocaleString();
// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
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_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;
}

}

}