[转]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 =