~chenjiahe/hx_common.git

			@@ -1,24 +1,31 @@
			package com.hx.util;

			import java.io.File;
			import java.io.FileInputStream;
			import java.io.FileOutputStream;
			import com.hx.exception.TipsException;
			import org.apache.poi.POIXMLDocument;
			import org.apache.poi.hssf.usermodel.*;
			import org.apache.poi.openxml4j.opc.OPCPackage;
			import org.apache.poi.poifs.filesystem.POIFSFileSystem;
			import org.apache.poi.ss.usermodel.Cell;
			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.xssf.usermodel.XSSFCell;
			import org.apache.poi.xssf.usermodel.XSSFRow;
			import org.apache.poi.xssf.usermodel.XSSFSheet;
			import org.apache.poi.xssf.usermodel.XSSFWorkbook;
			import org.springframework.web.multipart.MultipartFile;

			import java.io.*;
			import java.text.DateFormat;
			import java.text.SimpleDateFormat;
			import java.util.ArrayList;
			import java.util.Date;

			import java.util.List;
			import java.util.Map;

			import com.hx.exception.TipsException;
			import org.apache.poi.hssf.usermodel.*;
			import org.apache.poi.ss.usermodel.Cell;
			import org.springframework.web.multipart.MultipartFile;


			/**
			*
			*
			* @author hjr
			*/
			public final class ExcelUtil {
			@@ -37,8 +44,8 @@
			* @throws Exception
			*/
			public static String createExcel(String outPath, String excelName,
			String[] headList, String[] fieldList,
			List<Map<String, Object>> dataList) throws Exception {
			String[] headList, String[] fieldList,
			List<Map<String, Object>> dataList) throws Exception {

			String filePath = null;
			// 创建新的Excel 工作簿
			@@ -80,7 +87,7 @@
			}

			// 新建一输出文件流
			File file = SimpleTool.createFile(outPath,excelName);
			File file = SimpleTool.createFile(outPath, excelName);
			FileOutputStream fOut = new FileOutputStream(file);
			// 把相应的Excel 工作簿存盘
			workbook.write(fOut);
			@@ -106,7 +113,7 @@
			* @throws Exception
			*/
			public static File createExcel(String[] headList, String[] fieldList,
			List<Map<String, Object>> dataList) throws Exception {
			List<Map<String, Object>> dataList) throws Exception {
			File file = File.createTempFile("temp", ".xls");
			try{
			// 创建新的Excel 工作簿
			@@ -162,6 +169,91 @@
			return file;
			}

			/**生成临时文件
			* @param headList
			* Excel文件Head标题集合
			* @param fieldList
			* Excel文件Field标题集合根据field来寻找位置填充表格
			* @param dataList
			* Excel文件数据内容部分
			* @throws Exception
			*/
			public static File createExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
			,Integer height,Integer width) throws Exception {
			File file = File.createTempFile("temp", ".xls");
			try{

			if(height == null){
			height = 450;
			}
			if(width == null){
			width = 5000;
			}
			// 创建新的Excel 工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();

			//合并的单元格样式
			HSSFCellStyle boderStyle = workbook.createCellStyle();
			//垂直居中
			boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

			// 在Excel工作簿中建一工作表，其名为缺省值
			// 如要新建一名为"效益指标"的工作表，其语句为：
			// HSSFSheet sheet = workbook.createSheet("效益指标");
			HSSFSheet sheet = workbook.createSheet();
			// 在索引0的位置创建行（最顶端的行）
			HSSFRow row = sheet.createRow(0);
			// ===============================================================
			for (int i = 0; i < headList.length; i++) {
			//高度
			row.setHeight(height.shortValue());
			sheet.setColumnWidth(i,width);
			// 在索引0的位置创建单元格（左上端）
			HSSFCell cell = row.createCell(i);
			// 定义单元格为字符串类型
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 在单元格中输入一些内容
			cell.setCellValue(headList[i]);
			cell.setCellStyle(boderStyle);
			}
			// ===============================================================
			if (dataList != null) {
			for (int n = 0; n < dataList.size(); n++) {
			// 在索引1的位置创建行
			HSSFRow row_value = sheet.createRow(n + 1);
			row_value.setHeight(height.shortValue());
			Map<String, Object> dataMap = dataList.get(n);
			// ===============================================================
			for (int i = 0; i < fieldList.length; i++) {
			// 在索引0的位置创建单元格（左上端）
			sheet.setColumnWidth(i,width);
			HSSFCell cell = row_value.createCell(i);
			// 定义单元格为字符串类型
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 在单元格中输入一些内容
			cell.setCellValue(objToString(dataMap.get(fieldList[i])));
			cell.setCellStyle(boderStyle);
			}
			// ===============================================================
			}
			}

			// 新建一输出文件流
			FileOutputStream fOut = new FileOutputStream(file);
			// 把相应的Excel 工作簿存盘
			workbook.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
			}catch (Exception e){

			}finally {
			file.deleteOnExit();
			}
			return file;
			}


			private static String objToString(Object obj) {
			if (obj == null) {
			@@ -171,10 +263,34 @@
			return (String) obj;
			} else if (obj instanceof Date) {
			return null;// DateUtil.dateToString((Date)
			// obj,DateUtil.DATESTYLE_SHORT_EX);
			// obj,DateUtil.DATESTYLE_SHORT_EX);
			} else {
			return obj.toString();
			}
			}
			}

			/**
			* 读取Excel数据
			* @param file
			* @param header
			* @return
			* @throws Exception
			*/
			public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception {

			String fileName = file.getOriginalFilename();
			if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new TipsException("上传文件格式不正确");
			}

			//判断不同格式处理方法不同
			if(fileName.matches("^.+\\.(?i)(xls)$")){
			//xls格式使用HSSF
			return readExcelByeFileData(file, header);
			}else{
			//xlsx格式使用XSSF
			return readExcelByeFileDataToXSSF(file, header);
			}
			}

			@@ -254,7 +370,11 @@
			arrayString.add("");
			//arrayString[i] = "";
			} else { // 如果EXCEL表格中的数据类型为字符串型
			arrayString.add(cell.getStringCellValue().trim());
			if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
			arrayString.add(cell.getStringCellValue().trim());
			}else{
			arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
			}
			//arrayString[i] = cell.getStringCellValue().trim();
			}
			}
			@@ -265,4 +385,324 @@
			return list;
			}

			}
			/**
			* 读取 Excel文件内容
			*
			* @param file
			* @param header 是否包括表头
			* @return
			* @throws Exception
			*/
			public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception {

			String fileName = file.getOriginalFilename();
			if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new TipsException("上传文件格式不正确");
			}

			// 结果集
			List<List<String>> list = new ArrayList<>();

			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());

			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
			int col = 0;
			// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数去除标题
			for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
			XSSFRow xssfrow = xssfSheet.getRow(j);
			if(xssfrow!=null){
			if(j == 0) {
			col = xssfrow.getPhysicalNumberOfCells();
			if(!header) {
			//不包括表头
			continue;
			}
			}
			// 单行数据
			List<String> arrayString = new ArrayList<>();
			for (int i = 0; i < col; i++) {
			XSSFCell cell = xssfrow.getCell(i);
			if (cell == null) {
			arrayString.add("");
			} else if (cell.getCellType() == 0) {
			// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
			if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
			short format = cell.getCellStyle().getDataFormat();
			if(format == 14 \|\| format == 31 \|\| format == 57 \|\| format == 58){
			//日期（中文时间格式的）
			Date d = cell.getDateCellValue();
			DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			arrayString.add(formater.format(d));
			//arrayString[i] = formater.format(d);
			}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
			Date d = cell.getDateCellValue();
			//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
			DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			arrayString.add(formater.format(d));
			//arrayString[i] = formater.format(d);
			} else {
			if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
			arrayString.add(cell.getStringCellValue());
			//arrayString[i] =cell.getStringCellValue();
			}else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
			arrayString.add(cell.getCellFormula());
			//arrayString[i] =cell.getCellFormula();
			}else if(XSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
			HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
			arrayString.add(dataFormatter.formatCellValue(cell));
			//arrayString[i] =dataFormatter.formatCellValue(cell);
			}
			}
			}
			} else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
			arrayString.add("");
			//arrayString[i] = "";
			} else { // 如果EXCEL表格中的数据类型为字符串型
			if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
			arrayString.add(cell.getStringCellValue().trim());
			}else{
			arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
			}


			//arrayString[i] = cell.getStringCellValue().trim();
			}
			}
			list.add(arrayString);
			}
			}
			}
			return list;
			}


			/*判断excel的版本/
			public static Workbook create(InputStream inp) throws Exception {
			if (!inp.markSupported()) {
			inp = new PushbackInputStream(inp, 8);
			}
			if (POIFSFileSystem.hasPOIFSHeader(inp)) {
			return new HSSFWorkbook(inp);
			}
			if (POIXMLDocument.hasOOXMLHeader(inp)) {
			return new XSSFWorkbook(OPCPackage.open(inp));
			}
			throw new IllegalArgumentException("你的excel版本目前poi解析不了");
			}

			/**读取excel文件，兼容2003和2007
			* 通过流读取Excel文件
			* @return
			* @throws Exception
			*/
			public static List<List<String>> getExcelDataCompatible(File file,boolean header) throws Exception {
			try {

			String fileName = file.getName();
			if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new TipsException("上传文件格式不正确");
			}

			// 结果集
			List<List<String>> list = new ArrayList<>();
			Workbook book = create(new BufferedInputStream(new FileInputStream(file)));

			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for(int s=0;s<book.getNumberOfSheets();s++) {
			Sheet hssfsheet = book.getSheetAt(s);
			int col = 0;
			// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数去除标题
			for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
			Row hssfrow = hssfsheet.getRow(j);
			if(hssfrow!=null){
			if(j == 0) {
			col = hssfrow.getPhysicalNumberOfCells();
			if(!header) {
			//不包括表头
			continue;
			}
			}
			// 单行数据
			List<String> arrayString = new ArrayList<>();
			for (int i = 0; i < col; i++) {
			Cell cell = hssfrow.getCell(i);
			if (cell == null) {
			arrayString.add("");
			} else if (cell.getCellType() == 0) {
			// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
			if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
			short format = cell.getCellStyle().getDataFormat();
			if(format == 14 \|\| format == 31 \|\| format == 57 \|\| format == 58){
			//日期（中文时间格式的）
			Date d = cell.getDateCellValue();
			DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			arrayString.add(formater.format(d));
			//arrayString[i] = formater.format(d);
			}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
			Date d = cell.getDateCellValue();
			//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
			DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			arrayString.add(formater.format(d));
			//arrayString[i] = formater.format(d);
			} else {
			if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
			arrayString.add(cell.getStringCellValue());
			//arrayString[i] =cell.getStringCellValue();
			}else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
			arrayString.add(cell.getCellFormula());
			//arrayString[i] =cell.getCellFormula();
			}else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
			HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
			arrayString.add(dataFormatter.formatCellValue(cell));
			//arrayString[i] =dataFormatter.formatCellValue(cell);
			}
			}
			}
			} else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
			arrayString.add("");
			//arrayString[i] = "";
			} else { // 如果EXCEL表格中的数据类型为字符串型
			arrayString.add(cell.getStringCellValue().trim());
			//arrayString[i] = cell.getStringCellValue().trim();
			}
			}
			list.add(arrayString);
			}
			}
			}
			return list;
			} catch (Exception e) {
			e.printStackTrace();
			}
			return null;
			}




			/**读取excel文件，兼容2003和2007
			* 通过流读取Excel文件
			* @return
			* @throws Exception
			*/
			public static List<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception {
			try {

			String fileName = file.getOriginalFilename();
			if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new TipsException("上传文件格式不正确");
			}

			// 结果集
			List<List<String>> list = new ArrayList<>();
			Workbook book = create(new BufferedInputStream(file.getInputStream()));

			// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for(int s=0;s<book.getNumberOfSheets();s++) {
			Sheet hssfsheet = book.getSheetAt(s);
			int col = 0;
			// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数去除标题
			for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
			Row hssfrow = hssfsheet.getRow(j);
			if(hssfrow!=null){
			if(j == 0) {
			col = hssfrow.getPhysicalNumberOfCells();
			if(!header) {
			//不包括表头
			continue;
			}
			}
			// 单行数据
			List<String> arrayString = new ArrayList<>();
			for (int i = 0; i < col; i++) {
			Cell cell = hssfrow.getCell(i);
			if (cell == null) {
			arrayString.add("");
			} else if (cell.getCellType() == 0) {
			// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
			if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
			short format = cell.getCellStyle().getDataFormat();
			if(format == 14 \|\| format == 31 \|\| format == 57 \|\| format == 58){
			//日期（中文时间格式的）
			Date d = cell.getDateCellValue();
			DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			arrayString.add(formater.format(d));
			//arrayString[i] = formater.format(d);
			}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
			Date d = cell.getDateCellValue();
			//DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
			DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			arrayString.add(formater.format(d));
			//arrayString[i] = formater.format(d);
			} else {
			if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
			arrayString.add(cell.getStringCellValue());
			//arrayString[i] =cell.getStringCellValue();
			}else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
			arrayString.add(cell.getCellFormula());
			//arrayString[i] =cell.getCellFormula();
			}else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
			HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
			arrayString.add(dataFormatter.formatCellValue(cell));
			//arrayString[i] =dataFormatter.formatCellValue(cell);
			}
			}
			}
			} else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
			arrayString.add("");
			//arrayString[i] = "";
			} else { // 如果EXCEL表格中的数据类型为字符串型
			arrayString.add(cell.getStringCellValue().trim());
			//arrayString[i] = cell.getStringCellValue().trim();
			}
			}
			list.add(arrayString);
			}
			}
			}
			return list;
			} catch (Exception e) {
			e.printStackTrace();
			}
			return null;
			}

			private static String getCellVal(Cell cell) {
			if (null == cell) {
			return "";
			}
			switch (cell.getCellType()) {
			// 数字
			case HSSFCell.CELL_TYPE_NUMERIC:
			// 日期格式的处理
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
			}
			return String.valueOf(cell.getNumericCellValue());
			// 字符串
			case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
			// 公式
			case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
			// 空白
			case HSSFCell.CELL_TYPE_BLANK:
			return "";
			case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() + "";
			// 错误类型
			case HSSFCell.CELL_TYPE_ERROR:
			return cell.getErrorCellValue() + "";
			default:
			break;
			}
			return "";
			}

			}