本文章，导入导出依赖提前定义好的模板
package com.jd.nb.wishplat.man.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.Date; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang.ArrayUtils; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; 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.context.request.RequestContextHolder; import org.springframework.web.context.request.ServletRequestAttributes; import org.springframework.web.multipart.MultipartFile; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; /** * * @author zhenwei.shi * */ public class ImpAndExpExcel { public static JSONArray doImpXlsx(MultipartFile file, String[] fields, String[] requiredFields, Integer docReadStartRowIndex) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream()); JSONArray jsonArray = new JSONArray(); int startRowIndex = (null == docReadStartRowIndex?2:docReadStartRowIndex); XSSFSheet sheet = wb.getSheetAt(0); // 遍历所有行记录，sheet.getLastRowNum()获取的是最后一行的index for (int startRow = startRowIndex; startRow <= sheet.getLastRowNum(); startRow++) { // 遍历记录所有列 JSONObject jsonObj = new JSONObject(); boolean isErrorObj = false; for (int columnIndex = 0; columnIndex < fields.length; columnIndex++) { XSSFCell nowCell = getXssfCell(sheet, startRow, columnIndex); String cellValue = getXssfCellValue(nowCell); if(isRequired(fields[columnIndex],requiredFields) && StringUtils.isEmpty(cellValue)){ isErrorObj = true; break; } jsonObj.put(fields[columnIndex], cellValue); } if(!isErrorObj){ jsonArray.add(jsonObj); } } wb.close(); return jsonArray; } /** * 导入03版Excel .xls * 依据上传文件返还JSON数组对象，JSON属性为heads * @param file 导入的文件 * @param heads 定义对象的列名 * @param rowStartIndex 从第几行开始读取 * @return * @throws IOException */ @SuppressWarnings("resource") public static JSONArray doImpXls(MultipartFile file, String[] fields, Integer docReadStartRowIndex) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream()); HSSFWorkbook wb = new HSSFWorkbook(fs); JSONArray jsonArray = new JSONArray(); int startRowIndex = (null == docReadStartRowIndex?2:docReadStartRowIndex); HSSFSheet sheet = wb.getSheetAt(0);//只导入sheet第一页 // 遍历所有行记录，sheet.getLastRowNum()获取的是最后一行的index for (int startRow = startRowIndex; startRow <= sheet.getLastRowNum(); startRow++) { // 遍历记录所有列 JSONObject jsonObj = new JSONObject(); boolean isBlankObj = true; for (int columnIndex = 0; columnIndex < fields.length; columnIndex++) { HSSFCell nowCell = getHssfCell(sheet, startRow, columnIndex, true); String value = getHssfCellStringValue(nowCell); if(null!=value){ value=value.trim(); } jsonObj.put(fields[columnIndex], value); if(!StringUtils.isEmpty(value) && !"0".equals(value)){ isBlankObj = false; } } if(!isBlankObj){ jsonArray.add(jsonObj); } } return jsonArray; } /** * 导出03版Excel .xls * @param object//导出对象，可以是数组可以是对象 * @param fields//要导出对象的所需要的属性，注意跟模板书序一直 * @param docTemplatePath 如 1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls * 2：/templates/supplier/供应商管理导入模板.xls * docTemplatePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls"; * @param docWriteStartRowIndex//从模板第几行开始写入 * @return * @throws FileNotFoundException * @throws IOException */ public static void doExpXls(Object object,String[] fields,String docTemplatePath,Integer docWriteStartRowIndex) throws IOException{ doExpXls(object, fields, docTemplatePath, docWriteStartRowIndex,null); } /** * 导出03版Excel .xls * @param object//导出对象，可以是数组可以是对象 * @param fields//要导出对象的所需要的属性，注意跟模板书序一直 * @param docTemplatePath 如 1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls * 2：/templates/supplier/供应商管理导入模板.xls * docTemplatePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls"; * @param docWriteStartRowIndex//从模板第几行开始写入 * @param sheetRowsCount//sheet页数据最大行数 * @return * @throws IOException */ @SuppressWarnings("resource") public static void doExpXls(Object object,String[] fields,String docTemplatePath,Integer docWriteStartRowIndex,Integer sheetRowsCount) throws IOException{ docTemplatePath = docTemplatePath.replaceAll("\\", "/"); String projectPath = ImpAndExpExcel.class.getResource("/").getPath().replaceAll("\\", "/"); if(!docTemplatePath.contains(projectPath)){ docTemplatePath = projectPath+"/"+docTemplatePath; } FileInputStream inputStream = new FileInputStream(new File(docTemplatePath));; HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream)); JSONArray jsonArr = (JSONArray) JSONArray.toJSON(object); int everyCount = null==sheetRowsCount?50000:sheetRowsCount; int pageCount = (int)Math.ceil(jsonArr.size()/(everyCount*1.0)); //创建SHEET页 for(int i=0; i<pageCount; i++){ HSSFSheet sheet = null; if(0==i){ sheet = workbook.getSheetAt(0); }else{ sheet = workbook.cloneSheet(0); } int index = workbook.getSheetIndex(sheet); workbook.setSheetName(index, "第"+(i+1)+"页"); workbook.setSheetOrder(sheet.getSheetName(), i); } //sheet页赋值数据 for(int i=0; i<pageCount; i++){ int startIndex = i*everyCount; int endIndex = startIndex+everyCount; if(i==pageCount-1){ endIndex = jsonArr.size(); } JSONArray subjsonArr = (JSONArray)JSONArray.toJSON(jsonArr.subList(startIndex, endIndex)); int startRowIndex = (null == docWriteStartRowIndex?2:docWriteStartRowIndex); HSSFSheet sheet = workbook.getSheetAt(i); for (int j = 0; j < subjsonArr.size(); j++,startRowIndex++) { JSONObject jsonObj = subjsonArr.getJSONObject(j); for (int colIndex = 0; colIndex < fields.length; colIndex++) { HSSFCell tempCell = getHssfCell(sheet, startRowIndex, colIndex, true); tempCell.setCellValue(jsonObj.getString(fields[colIndex])); } } } ServletOutputStream out = null; String excName = docTemplatePath.substring(docTemplatePath.lastIndexOf("/")+1); try { HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse(); response.setCharacterEncoding("UTF-8"); response.setContentType("application/octet-stream; charset=UTF-8"); response.addHeader("Content-Disposition", "attachment; filename=""+new String(excName.getBytes("GB2312"),"ISO8859-1")+"";");// out = response.getOutputStream(); workbook.write(out); out.flush(); } catch (IOException e) { e.printStackTrace(); } finally{ try { out.close(); inputStream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * 获取导出03版Excel .xls的workbook * @param object//导出对象，可以是数组可以是对象 * @param fields//要导出对象的所需要的属性，注意跟模板书序一直 * @param docTemplatePath 如 1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls * 2：/templates/supplier/供应商管理导入模板.xls * docTemplatePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls"; * @param docWriteStartRowIndex//从模板第几行开始写入 * @return * @throws FileNotFoundException * @throws IOException */ public static HSSFWorkbook getHSSFWorkbook(Object object,String[] fields,String docTemplatePath,Integer docWriteStartRowIndex) throws FileNotFoundException,IOException { docTemplatePath = docTemplatePath.replaceAll("\\", "/"); String projectPath = ImpAndExpExcel.class.getResource("/").getPath().replaceAll("\\", "/"); if(!docTemplatePath.contains(projectPath)){ docTemplatePath = projectPath+"/"+docTemplatePath; } FileInputStream inputStream = new FileInputStream(new File(docTemplatePath)); HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream)); HSSFSheet sheet = workbook.getSheetAt(0); int startRowIndex = (null == docWriteStartRowIndex?2:docWriteStartRowIndex); JSONArray jsonArr = (JSONArray) JSONArray.toJSON(object); for (int i = 0; i < jsonArr.size(); i++,startRowIndex++) { JSONObject jsonObj = jsonArr.getJSONObject(i); for (int colIndex = 0; colIndex < fields.length; colIndex++) { HSSFCell tempCell = getHssfCell(sheet, startRowIndex, colIndex, true); tempCell.setCellValue(jsonObj.getString(fields[colIndex])); } } return workbook; } /** * 此方法用于下载指定文件。 * @param response 用于防止下载乱码，设置输出流的相关信息 * @param filePath 如 1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls * 2：/templates/supplier/供应商管理导入模板.xls * filePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls"; * @return true 下载成功， false 下载失败 */ public static void download(String filePath){ ServletOutputStream out = null; FileInputStream inputStream = null; filePath = filePath.replaceAll("\\", "/"); String projectPath = ImpAndExpExcel.class.getResource("/").getPath().replaceAll("\\", "/"); if(!filePath.contains(projectPath)){ filePath = projectPath+"/"+filePath; } String fileName = filePath.substring(filePath.lastIndexOf("/")+1, filePath.length()); try{ inputStream = new FileInputStream(new File(filePath)); HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse(); response.setCharacterEncoding("UTF-8"); response.setContentType("application/octet-stream; charset=UTF-8"); response.addHeader("Content-Disposition", "attachment; filename=""+new String(fileName.getBytes("GB2312"),"ISO8859-1")+"";");// out = response.getOutputStream(); int b = 0; byte[] buffer = new byte[512]; while ((b=inputStream.read(buffer)) != -1){ out.write(buffer,0,b); } out.flush(); } catch (IOException e) { e.printStackTrace(); }finally{ try { if(inputStream != null){ inputStream.close(); } } catch (IOException e) { e.printStackTrace(); } try { if(out != null){ out.close(); } } catch (IOException e) { e.printStackTrace(); } } } // 获取xlsx单元格，不存在是否创建 public static XSSFCell getXssfCell(XSSFSheet sheet, int rowIndex, int colIndex, boolean isCreate) { if (isCreate) { XSSFRow row = sheet.getRow(rowIndex); if (row == null) { row = sheet.createRow(rowIndex); row.setHeightInPoints(24);// 设置行的高度（单元格的高度） } XSSFCell cell = row.getCell(colIndex); if (cell == null) { cell = row.createCell(colIndex); } return cell; } else { return getXssfCell(sheet, rowIndex, colIndex); } } // 获取xlsx单元格 public static XSSFCell getXssfCell(XSSFSheet sheet, int rowIndex, int colIndex) { XSSFRow row = sheet.getRow(rowIndex); if (row != null) { XSSFCell cell = row.getCell(colIndex); if (cell != null) { return cell; } } return null; } // 获取xlsx单元格Cell里面的值 // 因为cell单元格有格式，所以针对不同的格式取值 public static String getXssfCellValue(XSSFCell cell) { String cellValue = ""; if(null==cell) { return cellValue; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING:// 字符串类型 cellValue = cell.getStringCellValue(); if (cellValue.trim().equals("") || cellValue.trim().length() <= 0) cellValue = ""; break; case XSSFCell.CELL_TYPE_NUMERIC: // 数值类型 if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); if (d != null) { cellValue = DateTimeUtils.getDate(d); } else { cellValue = ""; } } else { cellValue = cell.getNumericCellValue() + ""; if(cellValue.contains(".")){ cellValue = cellValue.substring(0, cellValue.indexOf(".")); } } break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 try{ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellValue = String.valueOf(cell.getNumericCellValue()); }catch (Exception e) { try{ cellValue = String.valueOf(cell.getStringCellValue()); }catch(Exception e2){ cellValue =""; } } break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: break; default: break; } if(cellValue!=null) { cellValue = cellValue.trim(); } return cellValue; } // 获取xls单元格，不存在是否创建 public static HSSFCell getHssfCell(HSSFSheet sheet, int rowIndex, int colIndex, boolean isCreate) { if (isCreate) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) { row = sheet.createRow(rowIndex); row.setHeightInPoints(24);// 设置行的高度（单元格的高度） } HSSFCell cell = row.getCell(colIndex); if (cell == null) { cell = row.createCell(colIndex); } return cell; } else { return getHssfCell(sheet, rowIndex, colIndex); } } // 获取xls单元格 public static HSSFCell getHssfCell(HSSFSheet sheet, int rowIndex, int colIndex) { HSSFRow row = sheet.getRow(rowIndex); if (row != null) { HSSFCell cell = row.getCell(colIndex); if (cell != null) { return cell; } } return null; } // 获取xls单元格Cell里面的值 // 因为cell单元格有格式，所以针对不同的格式取值 public static String getHssfCellStringValue(HSSFCell cell) { String cellValue = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING:// 字符串类型 cellValue = cell.getStringCellValue(); if (cellValue.trim().equals("") || cellValue.trim().length() <= 0) cellValue = " "; break; case HSSFCell.CELL_TYPE_NUMERIC: // 数值类型 if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); if (d != null) { cellValue = DateTimeUtils.getDate(d); } else { cellValue = ""; } } else { cellValue = cell.getNumericCellValue() + ""; if(cellValue.contains(".")){ cellValue = cellValue.substring(0, cellValue.indexOf(".")); } } break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 try{ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellValue = String.valueOf(cell.getNumericCellValue()); }catch (Exception e) { try{ cellValue = String.valueOf(cell.getStringCellValue()); }catch(Exception e2){ cellValue =""; } } break; case HSSFCell.CELL_TYPE_BLANK: cellValue = " "; break; case HSSFCell.CELL_TYPE_BOOLEAN: break; case HSSFCell.CELL_TYPE_ERROR: break; default: break; } return cellValue; } public static boolean isRequired(String checkFiled, String[] requireds) { if(StringUtils.isEmpty(checkFiled) || ArrayUtils.isEmpty(requireds)) { return false; } for(String required : requireds) { if(required.equals(checkFiled)) { return true; } } return false; } }
模板样子
