org.apache.poi3.1.7 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 */@Servicepublic 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; ???}}