场景介绍
- 动态配置表头
- 表头多样化,可为复杂表头
- 数据量大,上千万级别导出
- poi 写excel文件
- Mybatis 大数据量导出
主要思路
- 最多行表头的行查找->使用递归
- 一行的列数查找,且每个表头的跨列查找->使用递归
- 使用队列进行遍历写入excel
public class POIexcelMake {
private String name;
private SXSSFWorkbook workBook;
private Sheet sheet;
private Row crruentRow;//当前操作的行
private int columnPosi;//当前行位置
private int rowPosi;//当前列位置
private int rowSize;//行的数量
private int columnSize;//列的数量
public static final int MAX_SIZE = 1000000;
public static final int MEMORY_SIZE = 10000;
/**
* 这个map的第一个参数是行,第二个参数是是列中被占用了的位置,类似电影院买票时,有些被买了,有些没被买,用这个标记出来
*/
private Map<Integer,Map<Integer,Integer>> excelMap;
private CellStyle style;
public void setSheet(Sheet sheet) {
this.sheet = sheet;
}
public int getRowSize() {
return rowSize;
}
public void setRowSize(int rowSize) {
this.rowSize = rowSize;
}
public String getName() {
return name;
}
public POIexcelMake(String name) {
this.name = name;
columnPosi = 0;
rowPosi = 0;
rowSize = 0;
columnSize = 0;
this.workBook = new SXSSFWorkbook(MEMORY_SIZE);
this.sheet = this.workBook.createSheet(name);
this.excelMap = new HashMap();
this.style = workBook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 创建一行,也表示当前操作的行
* @return
*/
public Row createRow(){
Row row = sheet.createRow(rowSize);
this.crruentRow = row;
rowPosi = rowSize; //当前行位置设为创建的行
columnPosi = 0;
/**
* 在这里,通过excelMap进行过滤,确认当前的行的列的位置,因为我为了方便,先设置最大值是100
*/
if(excelMap.containsKey(rowPosi)){
Map<Integer, Integer> map = excelMap.get(rowPosi);
for(int i=0;i<100;i++){
if(!map.containsKey(i)){
columnPosi = i;
break;
}
}
}
columnSize = 0;
rowSize++;
return row;
}
/**
* 创建一个长宽为1的cell
* @return
*/
public Cell createCell(){
if(this.crruentRow==null)
throw new RuntimeException("please create row first,there is no row for you to create cell");
Cell cell = createCell(columnPosi);
columnPosiForWard();
columnSize++;
return cell;
}
/**
* 创建一个指定大小的cell
* @param width
* @param height
* @return
*/
public Cell createCell(int width,int height){
int lastRow = rowPosi + height -1;
int lastCol = columnPosi + width -1;
// System.out.println(rowPosi+","+lastRow+","+columnPosi+","+lastCol);
sheet.addMergedRegion(new CellRangeAddress(rowPosi,lastRow, columnPosi, lastCol));
dealMap(width,height);
Cell cell = createCell(columnPosi);
columnPosi =lastCol;
columnPosiForWard();
columnSize++;
return cell;
}
private void dealMap(int width, int height) {
// TODO Auto-generated method stub
Integer perRowPosi = rowPosi;//获得当前行
Integer perColumnPosi = columnPosi;//获得当前行的列位置
for(int i=0;i<height-1;i++){
perRowPosi++;//获得下一行
if(!excelMap.containsKey(perRowPosi)){
excelMap.put(perRowPosi, new HashMap<Integer,Integer>());
}
Map<Integer, Integer> rowMap = excelMap.get(perRowPosi);
for(int j=0;j<width;j++){
Integer col = perColumnPosi+j;
if(!rowMap.containsKey(col)){
rowMap.put(col, col);
}
}
}
}
public SXSSFWorkbook getSXSSFWorkbook() {
return workBook;
}
private Cell createCell(int position){
Cell cell = crruentRow.createCell(position);
cell.setCellStyle(style);
return cell;
}
private void columnPosiForWard(){
columnPosi++;
//如果包含当前行,获得该行,判断当前位置是否有被使用,如果往前移一格继续判断
if(excelMap.containsKey(rowPosi)){
Map<Integer, Integer> map = excelMap.get(rowPosi);
if(map!=null){
while(map.containsKey(columnPosi)){
columnPosi++;
}
}
}
}
public static void main(String[] args){
// String str = "{\"sheetName\":\"湖南省酬金报表\",\"header\":[{\"zhName\":\"湖南省地市酬金\",\"children\":[{\"zhName\":\"长沙酬金\",\"field\":\"cs_rwd\"},{\"zhName\":\"湘潭酬金\",\"field\":\"xt_rwd\"},{\"zhName\":\"株洲酬金\",\"field\":\"zz_rwd\"}]},{\"zhName\":\"账期\",\"children\":null,\"field\":\"sett_month\"},{\"zhName\":\"结算\",\"children\":[{\"zhName\":\"已结算\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"结算率\",\"field\":\"jsl\"},{\"zhName\":\"结算单价\",\"field\":\"jsdj\"}]},{\"zhName\":\"未结算\",\"field\":\"xt_rwd\",\"children\":[{\"zhName\":\"未结算率\",\"field\":\"wjsl\"},{\"zhName\":\"未结算原因\",\"field\":\"wjsyy\"}]}]}]}";
// String str = "{\"sheetName\":\"湖南省酬金报表\",\"header\":[{\"zhName\":\"湖南省地市酬金\",\"children\":[{\"zhName\":\"长沙酬金\",\"field\":\"cs_rwd\"},{\"zhName\":\"湘潭酬金\",\"field\":\"xt_rwd\"},{\"zhName\":\"株洲酬金\",\"field\":\"zz_rwd\"}]},{\"zhName\":\"账期\",\"children\":null,\"field\":\"sett_month\"},{\"zhName\":\"全国\",\"field\":\"province\",\"children\":[{\"zhName\":\"湖南\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"长沙\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"岳麓区\",\"field\":\"cs_rwd\"},{\"zhName\":\"天心区\",\"field\":\"cs_rwd\"}]},{\"zhName\":\"株洲\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"岳麓区11\",\"field\":\"cs_rwd\"},{\"zhName\":\"天心区22\",\"field\":\"cs_rwd\"}]}]},{\"zhName\":\"湖北\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"长沙\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"岳麓区\",\"field\":\"cs_rwd\"},{\"zhName\":\"天心区\",\"field\":\"cs_rwd\"}]},{\"zhName\":\"株洲\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"岳麓区11\",\"field\":\"cs_rwd\"},{\"zhName\":\"天心区22\",\"field\":\"cs_rwd\"}]}]}]},{\"zhName\":\"结算\",\"children\":[{\"zhName\":\"已结算\",\"field\":\"cs_rwd\",\"children\":[{\"zhName\":\"结算率\",\"field\":\"jsl\"},{\"zhName\":\"结算单价\",\"field\":\"jsdj\"}]},{\"zhName\":\"未结算\",\"field\":\"xt_rwd\",\"children\":[{\"zhName\":\"未结算率\",\"field\":\"wjsl\"},{\"zhName\":\"未结算原因\",\"field\":\"wjsyy\"}]}]}]}";
String str = "{\"reportName\":\"来吧来吧相约酒吧\",\"headers\":[{\"zhName\":\"字段1\",\"field\":\"col1\",\"aCol\":0},{\"zhName\":\"字段2\",\"field\":\"col2\",\"aCol\":0},{\"zhName\":\"字段3\",\"field\":\"col3\",\"aCol\":0},{\"zhName\":\"字段4\",\"field\":\"col4\",\"aCol\":0},{\"zhName\":\"字段5\",\"field\":\"col5\",\"aCol\":0},{\"zhName\":\"字段6\",\"field\":\"col6\",\"aCol\":0},{\"zhName\":\"字段7\",\"field\":\"col7\",\"aCol\":0},{\"zhName\":\"字段8\",\"field\":\"col8\",\"aCol\":0},{\"zhName\":\"字段9\",\"field\":\"col9\",\"aCol\":0},{\"zhName\":\"字段10\",\"field\":\"col10\",\"aCol\":0},{\"zhName\":\"全国\",\"aCol\":0,\"children\":[{\"zhName\":\"湖南\",\"aCol\":0,\"children\":[{\"zhName\":\"长沙\",\"aCol\":0,\"children\":[{\"zhName\":\"岳麓区\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园\",\"field\":\"col11\",\"aCol\":0},{\"zhName\":\"莱茵城\",\"field\":\"col12\",\"aCol\":0}]},{\"zhName\":\"天心区\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园111\",\"field\":\"col13\",\"aCol\":0},{\"zhName\":\"莱茵城222\",\"field\":\"col14\",\"aCol\":0}]}]},{\"zhName\":\"株洲\",\"aCol\":0,\"children\":[{\"zhName\":\"岳麓区11\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园33\",\"field\":\"col15\",\"aCol\":0},{\"zhName\":\"莱茵城44\",\"field\":\"col16\",\"aCol\":0}]},{\"zhName\":\"天心区22\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园555\",\"field\":\"col17\",\"aCol\":0},{\"zhName\":\"莱茵城666\",\"field\":\"col18\",\"aCol\":0}]}]}]},{\"zhName\":\"湖北\",\"aCol\":0,\"children\":[{\"zhName\":\"长沙\",\"aCol\":0,\"children\":[{\"zhName\":\"岳麓区\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园\",\"field\":\"col19\",\"aCol\":0},{\"zhName\":\"莱茵城\",\"field\":\"col20\",\"aCol\":0}]},{\"zhName\":\"天心区\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园111\",\"field\":\"col21\",\"aCol\":0},{\"zhName\":\"莱茵城222\",\"field\":\"col22\",\"aCol\":0}]}]},{\"zhName\":\"株洲\",\"aCol\":0,\"children\":[{\"zhName\":\"岳麓区11\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园33\",\"field\":\"col23\",\"aCol\":0},{\"zhName\":\"莱茵城44\",\"field\":\"col24\",\"aCol\":0}]},{\"zhName\":\"天心区22\",\"aCol\":0,\"children\":[{\"zhName\":\"中电软件园555\",\"field\":\"col25\",\"aCol\":0},{\"zhName\":\"莱茵城666\",\"field\":\"col26\",\"aCol\":0}]}]}]}]},{\"zhName\":\"结算\",\"aCol\":0,\"children\":[{\"zhName\":\"已结算\",\"aCol\":0,\"children\":[{\"zhName\":\"结算率\",\"field\":\"col27\",\"aCol\":0},{\"zhName\":\"结算单价\",\"field\":\"col28\",\"aCol\":0}]},{\"zhName\":\"未结算\",\"aCol\":0,\"children\":[{\"zhName\":\"未结算率\",\"field\":\"col29\",\"aCol\":0},{\"zhName\":\"未结算原因\",\"field\":\"col30\",\"aCol\":0}]}]}],\"conditions\":[{\"field\":\"col1\",\"operator\":\"=\"},{\"field\":\"col2\",\"operator\":\"like\"},{\"field\":\"col3\",\"operator\":\"!=\"},{\"field\":\"col4\",\"operator\":\"in\"}],\"_class\":\"com.asiainfo.chnl.rwd.consumer.entity.ReportTableConfig\"}";
JSONObject jsonObject = JSONObject.parseObject(str);
// JSONObject.parseObject(str, Header.class);
String sheetName = jsonObject.getString("sheetName");
JSONArray header = jsonObject.getJSONArray("header");
int maxHigh = findMaxHigh(header);
int maxWidth = 0;
Queue<JSONObject> arrayQueue = new ArrayDeque<>();
for (int i=0;i<header.size();i++) {
JSONObject json = header.getJSONObject(i);
int width = findWidth(json);
maxWidth += width;
arrayQueue.add(json);
}
//设置sheet
POIexcelMake make = new POIexcelMake(sheetName);
int index = 0;
int temp = maxWidth;
make.createRow();
while (!arrayQueue.isEmpty()){
JSONObject remove = arrayQueue.remove();
//宽度
int aCol = remove.getIntValue("aCol");
String zhName = remove.getString("zhName");
if (index != 0 && maxWidth != 0 && index % maxWidth == 0) {
make.createRow();
System.out.println("==index=" + index + ",maxWidth=" + maxWidth);
index = 0;
maxWidth = temp;
}
JSONArray children = remove.getJSONArray("children");
//如还有孩子,加入队列
if (children != null && children.size() != 0) {
for (int i=0;i<children.size();i++) {
arrayQueue.add(children.getJSONObject(i));
}
make.createCell(aCol, 1).setCellValue(zhName);
}else {
int rowSize = make.getRowSize();
// System.out.println(rowSize);
// System.out.println("remove="+remove);
// System.out.println("index=" + index + ": " + remove);
int height = maxHigh - rowSize + 1;
if (aCol == 1 && height == 1) {
make.createCell().setCellValue(zhName);
}else {
make.createCell(aCol, height).setCellValue(zhName);
}
// System.out.println("temp" + temp);
temp--;
}
index += aCol;
}
SXSSFWorkbook hssfWorkbook = make.getSXSSFWorkbook();
String path = "/Users/sugar/Downloads/"+sheetName+".xlsx";
OutputStream out = null;
try {
out = new FileOutputStream(path);
hssfWorkbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public static int findMaxHigh(JSONArray array) {
if (array == null || array.size() == 0) {
return 0;
}else {
int max = 0;
for (int i=0;i<array.size();i++) {
JSONObject json = array.getJSONObject(i);
JSONArray children = json.getJSONArray("children");
int count = findMaxHigh(children);
if (max < count) max = count;
}
return max+1;
}
}
public static int findWidth(JSONObject object) {
JSONArray array = object.getJSONArray("children");
if (array == null || array.size() == 0) {
object.put("aCol", 1);
return 1;
}else {
int acol = 0;
for (int i=0;i<array.size();i++) {
acol += findWidth(array.getJSONObject(i));
}
object.put("aCol", acol);
return acol;
}
}
结果如下:
如有问题请指正。