poi实现excel导出导入工具类

工具类"/>

poi实现excel导出导入工具类

一、poi实现excel数据导出工具类

• 代码如下:

public class TestController<T> {// 2007 版本以上 最大支持1048576行public  final static String  EXCEl_FILE_2007 = "2007";// 2003 版本 最大支持65536 行public  final static String  EXCEL_FILE_2003 = "2003";/*** <p>* 导出无头部标题行Excel <br>* 时间格式默认：yyyy-MM-dd hh:mm:ss <br>* </p>** @param title 表格标题* @param dataset 数据集合* @param out 输出流* @param version 2003 或者 2007，不传时默认生成2003版本*/public void exportExcel(String title, Collection<T> dataset, OutputStream out, String version) {if(StringUtils.isEmpty(version) || EXCEL_FILE_2003.equals(version.trim())){exportExcel2003(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");}else{exportExcel2007(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");}}/*** <p>* 导出带有头部标题行的Excel <br>* 时间格式默认：yyyy-MM-dd hh:mm:ss <br>* </p>** @param title 表格标题* @param headers 头部标题集合* @param dataset 数据集合* @param out 输出流* @param version 2003 或者 2007，不传时默认生成2003版本*/public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String version) {if(StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())){exportExcel2003(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");}else{exportExcel2007(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");}}/*** <p>* 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>* 此版本生成2007以上版本的文件 (文件后缀：xlsx)* </p>** @param title*            表格标题名* @param headers*            表格头部标题集合* @param dataset*            需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的*            JavaBean属性的数据类型有基本数据类型及String,Date* @param out*            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中* @param pattern*            如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"*/@SuppressWarnings({ "unchecked", "rawtypes" })public void exportExcel2007(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {// 声明一个工作薄XSSFWorkbook workbook = new XSSFWorkbook();// 生成一个表格XSSFSheet sheet = workbook.createSheet(title);// 设置表格默认列宽度为15个字节sheet.setDefaultColumnWidth(20);// 生成一个样式XSSFCellStyle style = workbook.createCellStyle();// 设置这些样式style.setFillForegroundColor(new XSSFColor(java.awt.Color.gray));style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(XSSFCellStyle.BORDER_THIN);style.setBorderLeft(XSSFCellStyle.BORDER_THIN);style.setBorderRight(XSSFCellStyle.BORDER_THIN);style.setBorderTop(XSSFCellStyle.BORDER_THIN);style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 生成一个字体XSSFFont font = workbook.createFont();font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");font.setColor(new XSSFColor(java.awt.Color.BLACK));font.setFontHeightInPoints((short) 11);// 把字体应用到当前的样式style.setFont(font);// 生成并设置另一个样式XSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE));style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);style2.setBorderRight(XSSFCellStyle.BORDER_THIN);style2.setBorderTop(XSSFCellStyle.BORDER_THIN);style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 生成另一个字体XSSFFont font2 = workbook.createFont();font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);// 把字体应用到当前的样式style2.setFont(font2);// 产生表格标题行XSSFRow row = sheet.createRow(0);XSSFCell cellHeader;for (int i = 0; i < headers.length; i++) {cellHeader = row.createCell(i);cellHeader.setCellStyle(style);cellHeader.setCellValue(new XSSFRichTextString(headers[i]));}// 遍历集合数据，产生数据行Iterator<T> it = dataset.iterator();int index = 0;T t;Field[] fields;Field field;XSSFRichTextString richString;Pattern p = Patternpile("^//d+(//.//d+)?$");Matcher matcher;String fieldName;String getMethodName;XSSFCell cell;Class tCls;Method getMethod;Object value;String textValue;SimpleDateFormat sdf = new SimpleDateFormat(pattern);while (it.hasNext()) {index++;row = sheet.createRow(index);t = (T) it.next();// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值fields = t.getClass().getDeclaredFields();for (int i = 0; i < fields.length; i++) {cell = row.createCell(i);cell.setCellStyle(style2);field = fields[i];fieldName = field.getName();getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);try {tCls = t.getClass();getMethod = tCls.getMethod(getMethodName, new Class[] {});value = getMethod.invoke(t, new Object[] {});// 判断值的类型后进行强制类型转换textValue = null;if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Float) {textValue = String.valueOf((Float) value);cell.setCellValue(textValue);} else if (value instanceof Double) {textValue = String.valueOf((Double) value);cell.setCellValue(textValue);} else if (value instanceof Long) {cell.setCellValue((Long) value);}if (value instanceof Boolean) {textValue = "是";if (!(Boolean) value) {textValue = "否";}} else if (value instanceof Date) {textValue = sdf.format((Date) value);} else {// 其它数据类型都当作字符串简单处理if (value != null) {textValue = value.toString();}}if (textValue != null) {matcher = p.matcher(textValue);if (matcher.matches()) {// 是数字当作double处理cell.setCellValue(Double.parseDouble(textValue));} else {richString = new XSSFRichTextString(textValue);cell.setCellValue(richString);}}} catch (SecurityException e) {e.printStackTrace();} catch (NoSuchMethodException e) {e.printStackTrace();} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();} finally {// 清理资源}}}try {workbook.write(out);} catch (IOException e) {e.printStackTrace();}}/*** <p>* 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>* 此方法生成2003版本的excel,文件名后缀：xls <br>* </p>** @param title*            表格标题名* @param headers*            表格头部标题集合* @param dataset*            需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的*            JavaBean属性的数据类型有基本数据类型及String,Date* @param out*            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中* @param pattern*            如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"*/@SuppressWarnings({ "unchecked", "rawtypes" })public void exportExcel2003(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {// 声明一个工作薄HSSFWorkbook workbook = new HSSFWorkbook();// 生成一个表格HSSFSheet sheet = workbook.createSheet(title);// 设置表格默认列宽度为15个字节sheet.setDefaultColumnWidth(20);// 生成一个样式HSSFCellStyle style = workbook.createCellStyle();// 设置这些样式style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 生成一个字体HSSFFont font = workbook.createFont();font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");font.setColor(HSSFColor.WHITE.index);font.setFontHeightInPoints((short) 11);// 把字体应用到当前的样式style.setFont(font);// 生成并设置另一个样式HSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(HSSFColor.WHITE.index);style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);style2.setBorderRight(HSSFCellStyle.BORDER_THIN);style2.setBorderTop(HSSFCellStyle.BORDER_THIN);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 生成另一个字体HSSFFont font2 = workbook.createFont();font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 把字体应用到当前的样式style2.setFont(font2);// 产生表格标题行HSSFRow row = sheet.createRow(0);HSSFCell cellHeader;for (int i = 0; i < headers.length; i++) {cellHeader = row.createCell(i);cellHeader.setCellStyle(style);cellHeader.setCellValue(new HSSFRichTextString(headers[i]));}// 遍历集合数据，产生数据行Iterator<T> it = dataset.iterator();int index = 0;T t;Field[] fields;Field field;HSSFRichTextString richString;Pattern p = Patternpile("^//d+(//.//d+)?$");Matcher matcher;String fieldName;String getMethodName;HSSFCell cell;Class tCls;Method getMethod;Object value;String textValue;SimpleDateFormat sdf = new SimpleDateFormat(pattern);while (it.hasNext()) {index++;row = sheet.createRow(index);t = (T) it.next();// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值fields = t.getClass().getDeclaredFields();for (int i = 0; i < fields.length; i++) {cell = row.createCell(i);cell.setCellStyle(style2);field = fields[i];fieldName = field.getName();getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);try {tCls = t.getClass();getMethod = tCls.getMethod(getMethodName, new Class[] {});value = getMethod.invoke(t, new Object[] {});// 判断值的类型后进行强制类型转换textValue = null;if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Float) {textValue = String.valueOf((Float) value);cell.setCellValue(textValue);} else if (value instanceof Double) {textValue = String.valueOf((Double) value);cell.setCellValue(textValue);} else if (value instanceof Long) {cell.setCellValue((Long) value);}if (value instanceof Boolean) {textValue = "是";if (!(Boolean) value) {textValue = "否";}} else if (value instanceof Date) {textValue = sdf.format((Date) value);} else {// 其它数据类型都当作字符串简单处理if (value != null) {textValue = value.toString();}}if (textValue != null) {matcher = p.matcher(textValue);if (matcher.matches()) {// 是数字当作double处理cell.setCellValue(Double.parseDouble(textValue));} else {richString = new HSSFRichTextString(textValue);cell.setCellValue(richString);}}} catch (SecurityException e) {e.printStackTrace();} catch (NoSuchMethodException e) {e.printStackTrace();} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();} finally {// 清理资源}}}try {workbook.write(out);} catch (IOException e) {e.printStackTrace();}}
}

• 测试导出:
• 创建导出实体类

@Data
public class Use {@ApiModelProperty(notes = "案件编号", name = "申请号")private String caseNumber;@ApiModelProperty(notes = "合同编号" , name ="合同号")private String contractNumber;@ApiModelProperty(notes = "合同金额", name ="合同金额")private BigDecimal contractAmount = new BigDecimal(0);@ApiModelProperty(notes = "账户余额", name = "账户余额")private BigDecimal accountBalance = new BigDecimal(0);@ApiModelProperty(notes = "逾期期数", name ="逾期期数")private Integer overduePeriods;
}

• 导出方法:

public void exportCaseInfoDistributed(User user) throws IOException {logger.info("{数据开始导出.......................}");long begin = System.currentTimeMillis(); //开始时间// FileOutputStream fileOutputStream = null;// String filePath=FileUtils.getTempDirectoryPath().concat(File.separator).concat(DateTime.now().toString("yyyyMMddhhmmss") + "数据表.xls");//  fileOutputStream = new FileOutputStream(filePath);TestController<Use> util = new TestController<Use>();List<Use> uses = new ArrayList<>();Object[] exportData = caseInfoDistributedRepository.findExportData(user.getCompanyCode());if(exportData.length>0){for (int i = 0; i < exportData.length; i++) {Use use = new Use();Object[] exportDatum =(Object[]) exportData[i];use.setCaseNumber((String) exportDatum[0]);use.setContractNumber((String) exportDatum[1]);use.setContractAmount((BigDecimal) exportDatum[2]);use.setAccountBalance((BigDecimal) exportDatum[3]);use.setOverduePeriods((Integer) exportDatum[4]);uses.add(use);}}long endData = System.currentTimeMillis();logger.debug("查询数据用时:"+(endData-begin));String[] strings = {"申请号","合同号","合同金额","账户余额","逾期期数"};util.exportExcel("待分配案件列表", strings, uses, new FileOutputStream("D:\\test.xls"), TestController.EXCEL_FILE_2003);/*FileSystemResource resource = new FileSystemResource(filePath);MultiValueMap<String, Object> param = new LinkedMultiValueMap<>();param.add("file", resource);ResponseEntity<String> url = restTemplate.postForEntity("http://file-service/api/uploadFile/addUploadFileUrl", param, String.class);logger.debug(url.getBody());*/long end = System.currentTimeMillis();//结束时间logger.debug("导出成功,总用时"+(end-begin));
}

 二、poi实现excel数据导入工具类

•  代码如下：

/*** 功能: [POI实现把Excel数据导入到数据库]* 作者: JML* 版本: 1.0*/
public class ImportExcel{//正则表达式 用于匹配属性的第一个字母private static final String REGEX = "[a-zA-Z]";/*** 功能: Excel数据导入到数据库* 参数: originUrl[Excel表的所在路径]* 参数: startRow[从第几行开始]* 参数: endRow[到第几行结束*                  (0表示所有行;*                  正数表示到第几行结束;*                  负数表示到倒数第几行结束)]* 参数: clazz[要返回的对象集合的类型]*/public static List<?> importExcel(String originUrl,int startRow,int endRow,Class<?> clazz) throws IOException {//是否打印提示信息boolean showInfo=true;return doImportExcel(originUrl,startRow,endRow,showInfo,clazz);}/*** 功能:真正实现导入*/private static List<Object> doImportExcel(String originUrl,int startRow,int endRow,boolean showInfo,Class<?> clazz) throws IOException {// 判断文件是否存在File file = new File(originUrl);if (!file.exists()) {throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");}HSSFWorkbook wb = null;FileInputStream fis=null;List<Row> rowList = new ArrayList<Row>();try {fis = new FileInputStream(file);// 去读Excelwb = new HSSFWorkbook(fis);Sheet sheet = wb.getSheetAt(0);// 获取最后行号int lastRowNum = sheet.getLastRowNum();if (lastRowNum > 0) { // 如果>0，表示有数据out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：",showInfo);}Row row = null;// 循环读取for (int i = startRow; i <= lastRowNum + endRow; i++) {row = sheet.getRow(i);if (row != null) {rowList.add(row);out("第" + (i + 1) + "行：",showInfo,false);// 获取每一单元格的值for (int j = 0; j < row.getLastCellNum(); j++) {String value = getCellValue(row.getCell(j));if (!value.equals("")) {out(value + " | ",showInfo,false);}}out("",showInfo);}}} catch (IOException e) {e.printStackTrace();} finally{wb.close();}return returnObjectList(rowList,clazz);}/*** 功能:获取单元格的值*/private static String getCellValue(Cell cell) {Object result = "";if (cell != null) {switch (cell.getCellType()) {case Cell.CELL_TYPE_STRING:result = cell.getStringCellValue();break;case Cell.CELL_TYPE_NUMERIC:result = cell.getNumericCellValue();break;case Cell.CELL_TYPE_BOOLEAN:result = cell.getBooleanCellValue();break;case Cell.CELL_TYPE_FORMULA:result = cell.getCellFormula();break;case Cell.CELL_TYPE_ERROR:result = cell.getErrorCellValue();break;case Cell.CELL_TYPE_BLANK:break;default:break;}}return result.toString();}/*** 功能:返回指定的对象集合*/private static List<Object> returnObjectList(List<Row> rowList,Class<?> clazz) {List<Object> objectList=null;Object obj=null;String attribute=null;String value=null;int j=0;try {objectList=new ArrayList<Object>();Field[] declaredFields = clazz.getDeclaredFields();for (Row row : rowList) {j=0;obj = clazz.newInstance();for (Field field : declaredFields) {attribute=field.getName().toString();value = getCellValue(row.getCell(j));setAttrributeValue(obj,attribute,value);j++;}objectList.add(obj);}} catch (Exception e) {e.printStackTrace();}return objectList;}/*** 功能:给指定对象的指定属性赋值*/private static void setAttrributeValue(Object obj,String attribute,String value) {//得到该属性的set方法名String method_name = convertToMethodName(attribute,obj.getClass(),true);Method[] methods = obj.getClass().getMethods();for (Method method : methods) {/*** 因为这里只是调用bean中属性的set方法，属性名称不能重复* 所以set方法也不会重复，所以就直接用方法名称去锁定一个方法* （注：在java中，锁定一个方法的条件是方法名及参数）*/if(method.getName().equals(method_name)){Class<?>[] parameterC = method.getParameterTypes();try {/**如果是(整型,浮点型,布尔型,字节型,时间类型),* 按照各自的规则把value值转换成各自的类型* 否则一律按类型强制转换(比如:String类型)*/if(parameterC[0] == int.class || parameterC[0]==java.lang.Integer.class){// value = value.substring(0, value.lastIndexOf("."));method.invoke(obj,Integer.valueOf(value));break;}else if(parameterC[0] == float.class || parameterC[0]==java.lang.Float.class){method.invoke(obj, Float.valueOf(value));break;}else if(parameterC[0] == double.class || parameterC[0]==java.lang.Double.class){method.invoke(obj, Double.valueOf(value));break;}else if(parameterC[0] == byte.class || parameterC[0]==java.lang.Byte.class){method.invoke(obj, Byte.valueOf(value));break;}else if(parameterC[0] == boolean.class|| parameterC[0]==java.lang.Boolean.class){method.invoke(obj, Boolean.valueOf(value));break;}else if(parameterC[0] == boolean.class|| parameterC[0]==java.math.BigDecimal.class){method.invoke(obj, new BigDecimal(value));break;}else if(parameterC[0] == java.util.Date.class){SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Date date=null;try {date=sdf.parse(value);} catch (Exception e) {e.printStackTrace();}method.invoke(obj,date);break;}else{method.invoke(obj,parameterC[0].cast(value));break;}} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();} catch (SecurityException e) {e.printStackTrace();}}}}/*** 功能:根据属性生成对应的set/get方法*/private static String convertToMethodName(String attribute,Class<?> objClass,boolean isSet) {/** 通过正则表达式来匹配第一个字符 **/Pattern p = Patternpile(REGEX);Matcher m = p.matcher(attribute);StringBuilder sb = new StringBuilder();/** 如果是set方法名称 **/if(isSet){sb.append("set");}else{/** get方法名称 **/try {Field attributeField = objClass.getDeclaredField(attribute);/** 如果类型为boolean **/if(attributeField.getType() == boolean.class||attributeField.getType() == Boolean.class){sb.append("is");}else{sb.append("get");}} catch (SecurityException e) {e.printStackTrace();} catch (NoSuchFieldException e) {e.printStackTrace();}}/** 针对以下划线开头的属性 **/if(attribute.charAt(0)!='_' && m.find()){sb.append(m.replaceFirst(m.group().toUpperCase()));}else{sb.append(attribute);}return sb.toString();}/*** 功能:输出提示信息(普通信息打印)*/private static void out(String info, boolean showInfo) {if (showInfo) {System.out.print(info + (showInfo ? "\n" : ""));}}/*** 功能:输出提示信息(同一行的不同单元格信息打印)*/private static void out(String info, boolean showInfo, boolean nextLine) {if (showInfo) {if(nextLine){System.out.print(info + (showInfo ? "\n" : ""));}else{System.out.print( info );}}}
}

•  测试方法：

    public static void main(String[] args) throws Exception {importExcel();}public static String importExcel() throws Exception{String originUrl="D:\\测试.xls";int startRow=1;int endRow=0;List<Use> bookList = (List<Use>) ImportExcel.importExcel(originUrl, startRow, endRow, Use.class);System.out.println(bookList);return "success";}