POI大数据量导出
依赖<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apac
·
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
工具类
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.*;
public class ExportUtil {
public static final String EXCEL_XLS = ".xls";
public static final String EXCEL_XLSX = ".xlsx";
public static CellStyle initCellStyle (Workbook workbook) {
// 创建单元格样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置边框
style.setBottomBorderColor(IndexedColors.RED.index);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setFillForegroundColor(IndexedColors.YELLOW.index);
style.setWrapText(true);
//设置字体
Font font = workbook.createFont();
font.setFontName("Verdana");
font.setBold(true);
font.setFontHeight((short) 200);
font.setColor(IndexedColors.BLACK.index);
style.setFont(font);
return style;
}
public static void formatCell(Row row, CellStyle style, Object value) {
String _v = "";
if(value!=null){
_v = String.valueOf(value);
}
formatCell_public(row,style,_v);
}
public static void formatCell(Row row, CellStyle style, String value) {
formatCell_public(row,style,String.valueOf(value));
}
public static void formatCell(Row row, CellStyle style, int value) {
formatCell_public(row,style,String.valueOf(value));
}
public static void formatCell(Row row, CellStyle style, Double value) {
formatCell_public(row,style,String.valueOf(value));
}
public static void formatCell_public(Row row, CellStyle style, String value) {
int lastCellNum = row.getLastCellNum();
if(lastCellNum==-1){
lastCellNum=0;
}
Cell cell = row.createCell(lastCellNum);
cell.setCellStyle(style);
cell.setCellValue(value);
}
public static Cell createTitleCellWithWidth(Row row1, int i, CellStyle style, String name,int columnWidth) {
if(columnWidth>0){
row1.getSheet().setColumnWidth(i, columnWidth);
}
return createTitleCell(row1,i,style,name);
}
public static Cell createTitleCell(Row row1, int i, CellStyle style, String name) {
Cell cell= row1.createCell(i);
cell.setCellStyle(style);// 建Excel的单元格
cell.setCellValue(name);
return cell;
}
public static void output(String filename,Workbook workbook,HttpServletRequest request,HttpServletResponse response) throws Exception {
//
String file = request.getSession().getServletContext().getRealPath("") + "/" + filename;
FileOutputStream o = new FileOutputStream(file);
workbook.write(o);
response.setHeader("Content-disposition", "attachment;filename="
+ java.net.URLEncoder.encode(filename, "UTF-8"));//设置头信息
OutputStream out = response.getOutputStream();//获取response的输出流
FileInputStream inStream = new FileInputStream(file);
byte[] buf = new byte[4096];
int readLength;
while (((readLength = inStream.read(buf)) != -1)) {//读取文件并输出
out.write(buf, 0, readLength);
}
o.flush();
o.close();
out.flush();
out.close();
inStream.close();
File f = new File(file);
if (f.exists()) {//删除文件
f.delete();
}
}
public static void output2(Workbook workbook, HttpServletResponse response,String filename) throws Exception {
if(workbook instanceof SXSSFWorkbook){
filename = filename + ExportUtil.EXCEL_XLSX;
}else if(workbook instanceof HSSFWorkbook){
filename = filename + ExportUtil.EXCEL_XLS;
}
response.setHeader("Content-disposition", "attachment;filename="
+ java.net.URLEncoder.encode(filename, "UTF-8"));//设置头信息
OutputStream out = response.getOutputStream();
try {
workbook.write(out);
}catch (Exception e) {
e.printStackTrace();
}finally{
workbook.close();
out.close();
}
}
public static Workbook createWorkbook(int size) {
if (size > 50000) {// 根据记录数判断创建的文件格式
return new SXSSFWorkbook(10000);
} else {
return new HSSFWorkbook();
}
}
public static Sheet createSheet(int size, String sheetname) {
return createWorkbook(size).createSheet(sheetname);
}
public static Row getNextTitleRow(Sheet sheet) {
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
row.setHeight((short) 500);
return row;
}
public static void addHbflags(LinkedHashMap<String, Integer> hb_flags, String xs_name) {
if(hb_flags.containsKey(xs_name)){
hb_flags.put(xs_name, hb_flags.get(xs_name)+1);
}else{
hb_flags.put(xs_name, 1);
}
}
//根据合并标记进行单元格合并，只能进行列合并
public static void initHbflags(Sheet sheet, LinkedHashMap<String, Integer> hb_flags, int startRow, int startCol) {
CellRangeAddress region = null;
for (Iterator iterator = hb_flags.values().iterator(); iterator.hasNext();) {
Integer _index = (Integer) iterator.next();
Integer endRow = startRow+_index-1;
if(startRow<endRow){
region = new CellRangeAddress(startRow, endRow, startCol, startCol);
sheet.addMergedRegion(region);
}
startRow = endRow+1;
}
}
//没有特殊合并单元格的简单表格的导出，
public static void initExcel_pub(List list, HttpServletResponse response,String name,List<ExportColumn> columns) throws Exception {
Sheet sheet = createSheet(list.size(),name);//表格工作簿
CellStyle style = initCellStyle(sheet.getWorkbook());//表格样式
Row row0 = sheet.createRow(0);//第一行表头
row0.setHeight((short) 500);
createTitleCell(row0,0,style,sheet.getSheetName());
//合并第一行表头单元格
CellRangeAddress region = new CellRangeAddress(0, (short) 0, 0, columns.size());
sheet.addMergedRegion(region);
Row row1 = sheet.createRow(1);//第二行列名
row1.setHeight((short) 500);
int _index=0;
createTitleCellWithWidth(row1,_index++,style,"序号",1500);
for (ExportColumn c : columns) {
createTitleCellWithWidth(row1,_index++,style,c.getName(),c.getWidth());
}
if(list!=null && list.size()>0){//生成列数据
//先缓存对象的字段get方法
Map<String,Method> getMethods = initGetMethods(list.get(0).getClass(),columns);
int xuhao=1;
Row row=null;
for (Object obj : list) {
row = sheet.createRow(sheet.getLastRowNum() + 1);
formatCell(row, style, xuhao++);
for (ExportColumn c : columns) {
Method getMethod = getMethods.get(c.getColumn());
formatCell(row, style, getMethod.invoke(obj));
}
}
}
//输出
output2(sheet.getWorkbook(),response,"");
}
//根据字段名，缓存字段的get方法
private static Map<String, Method> initGetMethods(Class<? extends Object> objClass, List<ExportColumn> columns) throws Exception {
Map<String, Method> getMethods = new HashMap<String, Method>();
for (ExportColumn c : columns) {
Method getMethod = getGetMethod(objClass,c.getColumn());
getMethods.put(c.getColumn(), getMethod);
}
return getMethods;
}
private static Method getGetMethod(Class<? extends Object> objectClass, String fieldName) throws Exception {
StringBuffer sb = new StringBuffer();
sb.append("get");
sb.append(fieldName.substring(0, 1).toUpperCase());
sb.append(fieldName.substring(1));
return objectClass.getMethod(sb.toString());
}
}
自己手写的一个map的小工具用来做sheet分页用
private void poi(Workbook workbook, String sheetName, List list) {
传同一个workbook,传sheet页名字和数据
if (list == null || list.size() == 0) {
return;
}
int count = 0;
Sheet sheet = workbook.createSheet(sheetName);
Row row = sheet.createRow(0);
Map<String, String> map = (Map<String, String>) list.get(0);
for (Map.Entry<String, String> stringStringEntry : map.entrySet()) {
row.createCell(count++).setCellValue(stringStringEntry.getKey());
}
for (int i = 0; i < list.size(); i++) {
Row r = sheet.createRow(i + 1);
Map<String, Object> m = (Map<String, Object>) list.get(i);
int col = 0;
for (Map.Entry<String, Object> stringStringEntry : m.entrySet()) {
String value = String.valueOf(stringStringEntry.getValue());
r.createCell(col++).setCellValue(value.equals("null") ?"":value);
}
}
}
循环一个组,我这里用了枚举,然后通过使用上面的工具类实现多个sheet导出.
这里使用XSS的workbook来实现,可以存超过65535的数据
SXSSFWorkbook workbook = new SXSSFWorkbook(100000);
Type[] values = Type.values();
for (Type value : values) {
poi(workbook, value.getName(), map.get(value.getType()));
}
System.out.println("-------------");
ExportUtil.output2(workbook, response, gridName + "数据得分详情");
依赖类
public class ExportColumn {
public String column;
public String name;
public int width;
public ExportColumn(String c, String n, int w){
this.column = c;
this.name = n;
this.width = w;
}
public ExportColumn(String c, String n){
this.column = c;
this.name = n;
this.width = 3000;
}
public String getColumn() {
return column;
}
public void setColumn(String column) {
this.column = column;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getWidth() {
return width;
}
public void setWidth(int width) {
this.width = width;
}
}
自己要完成的业务
一个枚举和一个多线程查询
import lombok.AllArgsConstructor;
import lombok.Getter;
/**
* Description :
* Version :1.0
*/
@Getter
@AllArgsConstructor
public enum Type {
EQU_1000("1000", "锅炉"),
EQU_2000("2000", "固定式压力容器,移动式压力容器"),
EQU_3000("3000", "电梯"),
EQU_4000("4000", "起重机械"),
EQU_5000("5000", "厂车"),
EQU_6000("6000", "游乐"),
EQU_8000("8000", "管道"),
EQU_9000("9000", "索道"),
EQU("EQU","设备基本表");
private String type;
private String name;
}
查询
@Override
public Map<String, List> getMessageByArea(String area_code) throws Exception {
HashMap<String, List> map = new HashMap<>();
CountDownLatch countDownLatch = new CountDownLatch(9);
ExecutorService es = Executors.newFixedThreadPool(9);
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("1000", qualityDao.get1000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("2000", qualityDao.get2000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("4000", qualityDao.get4000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("9000", qualityDao.get9000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("3000", qualityDao.get3000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("5000", qualityDao.get5000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("6000", qualityDao.get6000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("EQU", qualityDao.getEquMessageByArea(area_code));
countDownLatch.countDown();
}
});
es.submit(new Runnable() {
@SneakyThrows
@Override
public void run() {
map.put("8000", qualityDao.get8000MessageByArea(area_code));
countDownLatch.countDown();
}
});
es.shutdown();
countDownLatch.await();
System.out.println("------------------------------------");
return map;
}
技术共进，成长同行——讯飞AI开发者社区
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)