将分批分页查询的数据导出成excel表格

将分批分页查询的数据导出成excel表格

将数据库中的数据按照一定的过滤和排序规则，进行分批分页查询后生成Excel或者其他形式的报表数据，然后提供前端给下载是一个比较常见的业务场景，而且一般还会限定最大的行数，或者由前端指定行数。其实这个逻辑比较简单，分批查询数据，用几个for循环就可以搞定。但是报表很多的话，写起来也有点烦。于是想到把分页数据包装成流，简化api。 定义了几个简单的类，只要查询数据的Service层方法实现PagingQuery接口，而Domain实现CsvLine接口即可。

CsvLine.java

/**
 * @author zouk
 **/
public interface CsvLine {

    /**
     * generate a line
     *
     * @return a csv line
     */
    String[] csvLine();

}

PagingQuery.java

import java.util.List;

/**
 * @author zouk
 **/
public interface PagingQuery<T extends CsvLine> {

    /**
     * paging query data base
     *
     * @param offset offer for sql
     * @param limit  limit for sql
     * @return list of CsvLine
     */
    List<T> query(int offset, int limit);

}

CsvLineStreamBuilder.java

import java.util.Iterator;
import java.util.function.Supplier;
import java.util.stream.Stream;

/**
 * @author zouk
 **/
public class CsvLineStreamBuilder {

    @SuppressWarnings("unchecked")
    public static Stream<String[]> create(final PagingQuery pagingQuery, final int limit, final int queryPageSize) {
        Supplier<String[]> supplier = new Supplier<String[]>() {
            Iterator<CsvLine> iterator = pagingQuery.query(0, queryPageSize).iterator();
            String[] nextLine;
            int count = 0;
            boolean noMore = false;

            @Override
            public String[] get() {
                if (count > limit || noMore) {
                    return new String[]{};
                }
                if (iterator.hasNext()) {
                    nextLine = iterator.next().csvLine();
                } else {
                    iterator = pagingQuery.query(count, queryPageSize).iterator();
                    if (iterator.hasNext()) {
                        nextLine = iterator.next().csvLine();
                    } else {
                        noMore = true;
                        --count;
                        nextLine = new String[]{};
                    }
                }
                ++count;
                return nextLine;
            }
        };
        return Stream.generate(supplier).limit(limit);
    }

}

ExcelWriter.java

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.function.Consumer;
import java.util.stream.Stream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * @author zouk
 **/
public class ExcelWriter {

    private static final int ROW_WINDOW_SIZE = 100;

    public static void streamToExcel(Stream<String[]> stream, String[] fieldNames, String excelFile) throws IOException {
        SXSSFWorkbook wb = new SXSSFWorkbook(ROW_WINDOW_SIZE);
        Sheet sh = wb.createSheet();
        Row firstRow = sh.createRow(0);
        for (int colNum = 0; colNum < fieldNames.length; ++colNum) {
            Cell cell = firstRow.createCell(colNum);
            cell.setCellValue(fieldNames[colNum]);
        }
        stream.forEachOrdered(new Consumer<String[]>() {
            int rowNum = 1;

            @Override
            public void accept(String[] csvLine) {
                Row row = sh.createRow(rowNum);
                for (int colNum = 0; colNum < csvLine.length; ++colNum) {
                    Cell cell = row.createCell(colNum);
                    cell.setCellValue(csvLine[colNum]);
                }
                ++rowNum;
            }
        });
        FileOutputStream fOut = new FileOutputStream(excelFile);
        wb.write(fOut);
        fOut.close();
        wb.dispose();
    }

}

转载于:https://my.oschina.net/u/1393056/blog/1633680