导出功能简写

导出功能简写

package cn.exrick.manager.controller;

import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import cn.exrick.manager.vo.Student;

/**

• @author

• @version
  */
  public class Demo11 {
  public static void main(String[] args) {
  try {
  new Demo11().download();
  System.out.println(“导出完成”);
  } catch (Exception e) {
  e.printStackTrace();
  }
  }

  public void download() throws Exception {

   int totalCount= 800001;// 查询数据库数据总量，假设 200万数据，分2个sheet页码，每个页码内分5此查询数据库
   int sheetCount = 300000; // 每个sheet存放多少数据
   int pageSize = 100000;// 每次查询多少
   int sheetPage = totalCount % sheetCount > 0 ? (totalCount / sheetCount) + 1 : totalCount/sheetCount;// sheet页数
   int totalPage = totalCount % pageSize > 0 ? (totalCount / pageSize) + 1 : totalCount/ pageSize;// 共查询几次数据库
   int page = (sheetCount/pageSize);// 每个sheet页最多查询数据库次数
   /*1.创建poi导出数据对象*/
   SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);
   List<String> list = Arrays.asList(new String[] { "姓名", "性别" });// 声明表头信息
  

// FileOutputStream out = new FileOutputStream(“D:\5555.xlsx”);
FileOutputStream out = new FileOutputStream(“D:\12qa11.xlsx”);
for (int s = 1; s <= sheetPage; s++) {// 遍历每个sheet页码
System.out.println(“第几个sheet页码：” + s);
/2.创建sheet页/
SXSSFSheet sheet = sxssfWorkbook.createSheet(“学生” + s);
sheet.setRandomAccessWindowSize(-1);
// 第一行设置表头信息并填充信息
SXSSFRow headRow = sheet.createRow(0);
for (int k = 0; k < list.size(); k++) {
headRow.createCell(k).setCellValue(list.get(k));
}
int p=pages-page;
for (int j=0; j < page&&p+j<totalPage; j++) {
System.out.println(“第几次查询：” + (p+j+1));
// 查询出的数据，根据条件和页码count和count
List downloadData = null;
if(p+j==totalPage-1) {
downloadData = new ArrayList<>();
downloadData.add(new Student(“a”, 16,“b1”,“c2”,“d3”,“e4”,“f5”,“g6”,“h7”,“r8”,“g9”,“k10”,“j11”));
}else {
downloadData = downloadData(pageSize);
}
int b = pageSize * j;
for (int i = 0; i < downloadData.size(); i++) {
headRow = sheet.createRow(b + i + 1);
headRow.createCell(0).setCellValue(downloadData.get(i).getName());
headRow.createCell(1).setCellValue(downloadData.get(i).getAge());
headRow.createCell(2).setCellValue(downloadData.get(i).getName1());
headRow.createCell(3).setCellValue(downloadData.get(i).getName2());
headRow.createCell(4).setCellValue(downloadData.get(i).getName3());
headRow.createCell(5).setCellValue(downloadData.get(i).getName4());
headRow.createCell(6).setCellValue(downloadData.get(i).getName5());
headRow.createCell(7).setCellValue(downloadData.get(i).getName6());
headRow.createCell(8).setCellValue(downloadData.get(i).getName7());
headRow.createCell(9).setCellValue(downloadData.get(i).getName8());
headRow.createCell(10).setCellValue(downloadData.get(i).getName9());
headRow.createCell(11).setCellValue(downloadData.get(i).getName10());
headRow.createCell(12).setCellValue(downloadData.get(i).getName11());
}
downloadData.clear();//清除上一个list缓存
}
}
sxssfWorkbook.write(out);
// 关闭流
// outputStream.close();
// out.close();
// sxssfWorkbook.close();
sxssfWorkbook.dispose();
// createFile(sxssfWorkbook);
out.close();
sxssfWorkbook.close();
}

@SuppressWarnings("resource")
private void createFile(SXSSFWorkbook sxssfWorkbook) throws Exception {
	HttpServletResponse response = null;
	// 下载导出
	String filename = UUID.randomUUID().toString();
	// 设置头信息

// response.setCharacterEncoding(“UTF-8”);
// response.setContentType(“application/vnd.ms-excel”);
// //设置成xlsx格式
// response.setHeader(“Content-Disposition”,“attachment;filename=”+ URLEncoder.encode(filename + “.xlsx”,“UTF-8”));
// 创建输出流
// ServletOutputStream outputStream = response.getOutputStream();
//
// FileOutputStream out = new FileOutputStream(“D:\12qa.xlsx”);
// 写入数据
// sxssfWorkbook.write(out);
// 关闭流
// outputStream.close();
// out.close();
// sxssfWorkbook.close();
// sxssfWorkbook.dispose();
}

private List<Student> downloadData(int pageSize) {

	List<Student> list = new ArrayList<>();
	for (int i = 0; i < pageSize; i++) {
		list.add(new Student("a" + i, 16,"b1"+i,"c2"+i,"d3"+i,"e4"+i,"f5"+i,"g6"+i,"h7"+i,"r8"+i,"g9"+i,"k10"+i,"j11"+i));
	}
	return list;
}

}