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 + "毫秒.");
}- 上一篇 »poi-java基础操作
- 下一篇 »python——pip导出导入安装包