org.apache.poi3.1.7 Excle并发批量导入导出
org.apache.poi3.1.7 升级，需要修改设置方式：
1、org.apache.poi3.1.4 的设置单元格：
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直
org.apache.poi3.1.7的设置单元格，格式为：
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStylestyle.setAlignment(HorizontalAlignment.CENTER);// 居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);;//垂直
2、同时在设置边框时候，也有相应的同样问题，HSSFCellStyle 中同样报错没有其中的值
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
需要升级一下方式：
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
3、代码分享：EXCLE导入导出，二话不说直接上代码：
import com.fasterxml.jackson.annotation.JsonIgnore; import com.ppdai.wechat.contract.model.CoverBuildingInfo; import com.ppdai.wechat.contract.request.BatchInsertBuildingRequest; import com.ppdai.wechat.spring.entity.OutputResult; import com.ppdai.wechat.spring.service.CoverBuildingMService; import com.ppdai.wechat.spring.util.CommonUtil; import com.ppdai.wechat.spring.util.StringUtil; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; 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 org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.beans.PropertyDescriptor; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.util.*; import java.util.concurrent.*; /** * Description:Excel解析 * Created by xiaoyongyong on 2017/11/15. * Version: 1.0 */ @Service public class AwardExcelReader { @Autowired private CoverBuildingMService coverBuildingMService; private Logger logger = LoggerFactory.getLogger(AwardExcelReader.class); private static CountDownLatch latch = new CountDownLatch(10); private static ExecutorService executorService = Executors.newFixedThreadPool(5); private int pageIndex = 0; /** * Excel的导出数据和格式设定 * Excel 2003及以下的版本。一张表最大支持65536行数据，256列。也就是说excel2003完全不可能满足百万数据导出的需求。 * Excel 2007-2010版本。一张表最大支持1048576行，16384列； * * @param data title对应的属性 * @param titles 导出Excle的列头 * @param list 查询的list集合 * @param response HttpServletResponse * @param fileName 文件名 * @throws Exception Exception */ public static <T> void excelData(String[] data, String[] titles, List<T> list, HttpServletResponse response, String fileName) throws Exception { // 生成提示信息， response.setContentType("application/vnd.ms-excel"); try (OutputStream os = response.getOutputStream()) { // 进行转码，使其支持中文件名 String codeFileName = java.net.URLEncoder.encode(fileName, "UTF-8"); response.setHeader("content-disposition", "attachment;filename=" + codeFileName + ".xlsx"); // 生成工作簿对象 SXSSFWorkbook workbook = new SXSSFWorkbook(); //产生工作表对象 SXSSFSheet sheet = workbook.createSheet(); //循环表头 for (int i = 0; i < titles.length; i++) { //设置表列宽 sheet.setColumnWidth((short) i, 25 * 256); } //设置统一单元格的高度 sheet.setDefaultRowHeight((short) 300); //样式1 CellStyle style = workbook.createCellStyle(); // 样式对象 style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直 style.setAlignment(HorizontalAlignment.CENTER); // 水平 style.setWrapText(true); //设置是否能够换行，能够换行为true style.setBorderBottom(BorderStyle.THIN); //设置下划线，参数是黑线的宽度 style.setBorderLeft(BorderStyle.THIN); //设置左边框 style.setBorderRight(BorderStyle.THIN); //设置有边框 style.setBorderTop(BorderStyle.THIN); //设置上边框 //设置标题字体格式 Font font = workbook.createFont(); //设置字体样式 font.setFontHeightInPoints((short) 20); //设置字体大小 font.setFontName("Courier New"); //设置字体，例如：宋体 List<Field> fieldList = new ArrayList<>(); //支持子类父类两级 fieldList.addAll(Arrays.asList(list.get(0).getClass().getDeclaredFields())); fieldList.addAll(Arrays.asList(list.get(0).getClass().getSuperclass().getDeclaredFields())); Map<String, Field> fieldMap = new HashMap<>(); for (Field field : fieldList) { if ("serialVersionUID".equals(field.getName())) continue; field.setAccessible(true); fieldMap.put(field.getName(), field); } //创建第一行 SXSSFRow row = sheet.createRow(0); //为第一行的所有列赋值 for (int i = 0; i < titles.length; i++) { SXSSFCell cell = row.createCell(i); cell.setCellValue(titles[i]); } //循环list集合，把数据写到Excel if (!list.isEmpty()) { int i = 1; for (T tt : list) { // 创建除第一行的一下data行 SXSSFRow sxssfRow = sheet.createRow(i++); String val = ""; // 创建一行的所有列并为其赋值 for (int v = 0; v < data.length; v++) { Field field = fieldMap.get(data[v]); if (!field.isAnnotationPresent(JsonIgnore.class)) { Object fieldValue = new PropertyDescriptor(field.getName(), tt.getClass()).getReadMethod().invoke(tt); if (fieldValue == null) { val = ""; } else { val = fieldValue.toString(); } } sxssfRow.createCell(v).setCellValue(val); } } } workbook.write(os); } catch (IOException e) { e.printStackTrace(); } } /** * 批量读取Excle * @param uploadFile 上传的Excle文件 * @param pageSize 多线程解析excle的行数 * @throws Exception */ public void importExcel(MultipartFile uploadFile, Integer pageSize) throws Exception { //解析excel 2007 版本文件 String awardName = uploadFile.getOriginalFilename().substring(0, uploadFile.getOriginalFilename().indexOf(".")); XSSFWorkbook workbook = new XSSFWorkbook(uploadFile.getInputStream());// XSSFSheet sheet = workbook.getSheetAt(0); int totalRows = sheet.getLastRowNum() + 1;//一共有多少行 if (totalRows == 0) { throw new Exception("请填写数据！"); } try { List<Future> futures = new ArrayList<>(); for (int i = 0; i < 10; i++) { futures.add(executorService.submit(new AwardExcelReader.ReaderImport(pageSize, totalRows, sheet, awardName))); } for (Future future : futures) { if (future.get() != null) { latch.countDown(); } } latch.await();//命令发送后指挥官处于等待状态，一旦cdAnswer为0时停止等待继续往下执行 } catch (Exception e) { pageIndex = 0; logger.error("importExcel处理异常，异常信息", e); } finally { pageIndex = 0; System.gc(); } } private class ReaderImport implements Callable<Object> { private Integer pageSize; private Integer totalRows; private XSSFSheet sheet; private String awardName; ReaderImport(Integer pageSize, Integer totalRows, XSSFSheet sheet, String awardName) { this.pageSize = pageSize; this.totalRows = totalRows; this.sheet = sheet; this.awardName = awardName; } @Override public Object call() throws Exception { start(pageSize, totalRows, sheet, awardName); return 1; } } private void start(Integer pageSize, Integer totalRows, XSSFSheet sheet, String awardName) throws Exception { while (true) { //1、批量读取Excel数据，分批次查询，一次查询1000条 BatchInsertBuildingRequest request = new BatchInsertBuildingRequest(); synchronized (this) { pageIndex++; List<CoverBuildingInfo> coverBuildingInfos = new ArrayList<>(); for (int rowIndex = pageIndex * pageSize - pageSize == 0 ? 0 : pageIndex * pageSize - pageSize + 1; rowIndex <= pageIndex * pageSize; rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); if (row == null) { continue; } if (StringUtil.isNullOrEmpty(CommonUtil.getCellValue(row.getCell(0)))) { continue; } CoverBuildingInfo coverBuildingInfo = new CoverBuildingInfo(); coverBuildingInfo.setAwardName(awardName); coverBuildingInfo.setAward(CommonUtil.getCellValue(row.getCell(0))); coverBuildingInfo.setRemark(String.valueOf(pageIndex)); coverBuildingInfos.add(coverBuildingInfo); } request.setCoverBuildingInfos(coverBuildingInfos); if (pageIndex > CommonUtil.getTotalPage(pageSize, totalRows)) { break; } } OutputResult baseResponse = coverBuildingMService.batchInsertBuilding(request); if (baseResponse.getResult() != 0) { logger.error("批量写入数据异常，异常信息", baseResponse.getResultMessage()); } } } }
public class CommonUtil { public static Integer getTotalPage(Integer pageSize, Integer totalCount) { Integer totalPage; if (totalCount % pageSize == 0) { totalPage = totalCount / pageSize; } else { totalPage = totalCount / pageSize + 1; } return totalPage; } /** * 获取Cell内容 * @param cell cell * @return String */ public static String getCellValue(Cell cell) { String cellValue = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case STRING: cellValue = cell.getStringCellValue(); break; case NUMERIC: cellValue = cell.getNumericCellValue() + ""; break; case BLANK: break; default: break; } } return cellValue; } }
下面是实用类：
@RequestMapping("/export")
public void export(HttpServletResponse response, @RequestParam(required = false) Integer activityId) throws Exception {
long s1 = System.currentTimeMillis();
String[] titles = new String[]{"奖励名称", "奖励", "是否赠送", "修改时间"};
String[] data = new String[]{"awardName", "award", "useful", "updatetime"};
List<CoverBuildingBO> list = new ArrayList<>();
long start = System.currentTimeMillis();
reader.excelData(data, titles, list, response, fileName);
long spend = System.currentTimeMillis() - start;
long s2 = System.currentTimeMillis() - s1;
System.out.println("文件总数：" + list.size() + "条,excel生成耗时：" + spend + "毫秒" + ",总耗时：" + s2 + "毫秒.");
}
