java excel 多sheet_Excel大批量数据多sheet导出

六. 多sheet数据导出

1. 多sheet数据导出包装类,详见com.github.sheet.wrapper包内包装类

多sheet数据导出只需将待导出数据封装入com.github.sheet.wrapper包内的Wrapper类即可实现多sheet一键导出

2. 无模板、无注解的多sheet导出com.github.sheet.wrapper.SimpleSheetWrapper

1) 调用方法

// 多sheet无模板、无注解导出

@Test

public void testBatchSimple2Excel() throws Exception {

// 生成sheet数据

List list = new ArrayList<>();

for (int i = 0; i <= 2; i++) {

//表格内容数据

List data = new ArrayList<>();

for (int j = 0; j < 1000; j++) {

// 行数据(此处是数组) 也可以是List数据

String[] rows = new String[5];

for (int r = 0; r < 5; r++) {

rows[r] = "sheet_" + i + "row_" + j + "column_" + r;

}

data.add(rows);

}

// 表头数据

List header = new ArrayList<>();

for (int h = 0; h < 5; h++) {

header.add("column_" + h);

}

list.add(new SimpleSheetWrapper(data, header, "sheet_" + i));

}

ExcelUtils.getInstance().simpleSheet2Excel(list, "K.xlsx");

}

以下是实现：

@RequestMapping(value = "/alltoexcel")

public void alltoexcel(HttpServletRequest request,HttpServletResponse response) throws Exception {

// 生成sheet数据

List list = new ArrayList<>();

String where = " 1=1 ";

where += " order by rc.rc_id desc ";

String where1 ="";

List rList = rechargeCardMapper.selectAll();

int size = rList.size();

if(size%10000==0){

for(int i=0;i

where1 = where;

where1 += " limit "+i*10000+",10000 ";

List> models =rechargeCardMapper.toexcel(where1);//一万条数据导出一次

//表格内容数据

List data = new ArrayList<>();

for (int j = 0; j < models.size(); j++) {

// 行数据(此处是数组) 也可以是List数据

String\[\] rows = new String\[5\];

rows\[0\] = models.get(j).get("rc_sn").toString();

rows\[1\] = models.get(j).get("rc_password").toString();

rows\[2\] = models.get(j).get("rc_money").toString();

rows\[3\] = models.get(j).get("rc\_lot\_number").toString();

rows\[4\] = models.get(j).get("ctime").toString();

data.add(rows);

}

// 表头数据

List header = new ArrayList<>();

header.add("卡号");

header.add("密码");

header.add("面值");

header.add("批次");

header.add("生成时间");

list.add(new SimpleSheetWrapper(data, header, "sheet_" + i));

where1 = "";

}

}else if(size%10000>0){

for(int i=0;i

where1 = where;

where1 += " limit "+i*10000+",10000 ";

List> models =rechargeCardMapper.toexcel(where1);//一万条数据导出一次

//表格内容数据

List data = new ArrayList<>();

for (int j = 0; j < models.size(); j++) {

// 行数据(此处是数组) 也可以是List数据

String\[\] rows = new String\[5\];

rows\[0\] = models.get(j).get("rc_sn").toString();

rows\[1\] = models.get(j).get("rc_password").toString();

rows\[2\] = models.get(j).get("rc_money").toString();

rows\[3\] = models.get(j).get("rc\_lot\_number").toString();

rows\[4\] = models.get(j).get("ctime").toString();

data.add(rows);

}

// 表头数据

List header = new ArrayList<>();

header.add("卡号");

header.add("密码");

header.add("面值");

header.add("批次");

header.add("生成时间");

list.add(new SimpleSheetWrapper(data, header, "sheet_" + i));

where1 = "";

}

}

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;

String filePath = request.getServletContext().getRealPath("/download");// 保存文件路径

// 判断文件夹是否存在 否：创建；是：保存在文件夹下

File downloadFile = new File(filePath);

if (!downloadFile.exists()) {

downloadFile.mkdirs();//包括父目录*

}

ExcelUtils.getInstance().simpleSheet2Excel(list, filePath+"/cards.xlsx");//充值卡表

downloadexcel(response,filePath+"/cards.xlsx");//导出后下载

}

private static void downloadexcel(HttpServletResponse response, String filePath) throws Exception {

File f = new File(filePath);

if (!f.exists()) {

response.sendError(404, "File not found!");

return;

}

BufferedInputStream bis = null;

BufferedOutputStream bos = null;

try {

// 设置response参数，可以打开下载页面

response.reset();

response.setContentType("application/vnd.ms-excel;charset=utf-8");

response.setHeader("Content-Disposition",

"attachment;filename=" + f.getName());

ServletOutputStream out = response.getOutputStream();

bis = new BufferedInputStream(new FileInputStream(f));

bos = new BufferedOutputStream(out);

byte\[\] buff = new byte\[2048\];

int bytesRead;

// Simple read/write loop.

while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {

bos.write(buff, 0, bytesRead);

}

} catch (IOException e) {

e.printStackTrace();

} finally {

try {

if (bis != null)

bis.close();

if (bos != null)

bos.close();

} catch (IOException e) {

}

}

}

Maven依赖，关键是：

com.github.crab2died

Excel4J

2.1.4-Alpha

org.apache.commons

commons-collections4

4.1

org.apache.poi

poi

3.17