大数据量下载excel

public void download(int pageSize, int total, HttpServletRequest request,

HttpServletResponse response, Criteria criteria) {

// 引入压缩文件工具类

ZipCompress zipComp = new ZipCompress();

// 引入文件处理工具类

FileUtil fileUtil = new FileUtil();

 

int pageNumber = 0;

if (total > pageSize) {

pageNumber = total / pageSize + 1;

} else {

pageNumber = 1;

}

 

// 下载路径

String path = request.getSession().getServletContext().getRealPath("/")

+ "uploadDir" + File.separator;

// 多excel存放文件路径

String filePath = request.getSession().getServletContext()

.getRealPath("/")

+ "uploadDir"

+ File.separator

+ UUID.randomUUID()

+ File.separator;

File file = new File(filePath);

if (!file.exists()) {

file.mkdir();

}

 

// 将查询出来的所有数据分批插入到excel生成excel

if (pageNumber == 1) {

// 按照系统设置的大小查询数据

List dataList = this.psbcClientFilterDataMapper.selectDownSQL(criteria);

logger.info("数量..." + dataList.size());

// 按照系统设置的大小输出excel

try {

excelExport(response,request, dataList, criteria);

} catch (Exception e) {

e.printStackTrace();

}

} else {

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

 

// 设置分页信息

criteria.setOracleEnd(pageSize + pageSize * i);

criteria.setOracleStart(pageSize * i);

OutputStream out = null;

try {

out = new FileOutputStream(filePath + "业绩统计查询报表(" + i

+ ").xls");

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

// 按照系统设置的大小查询数据

List dataList = this.psbcClientFilterDataMapper

.selectDownSQL(criteria);

logger.info("数量..." + dataList.size());

// 按照系统设置的大小输出excel

try {

excelExport(response, dataList, out, criteria);

} catch (Exception e) {

e.printStackTrace();

}

 

}

 

// 压缩文件

try {

zipComp.compressedFile(filePath, path, "统计查询报表.zip");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

fileUtil.DeleteFolder(filePath);

}

// 下载文件

DownloadUtil.download(path + "统计查询报表.zip", response);

// 删除压缩文件

fileUtil.deleteFile(path + "统计查询报表.zip");

 

}

 

}

 

 

 

 

 

 

 

 

 

public void compressedFile(String resourcesPath,String targetPath,String targetName) throws Exception{

File resourcesFile = new File(resourcesPath);     //源文件

File targetFile = new File(targetPath);           //目的

//如果目的路径不存在，则新建

if(!targetFile.exists()){     

targetFile.mkdirs();  

}

 

//String targetName = resourcesFile.getName()+".zip";   //目的压缩文件名

FileOutputStream outputStream = new FileOutputStream(targetPath+targetName);

ZipOutputStream out = new ZipOutputStream(new BufferedOutputStream(outputStream));

 

createCompressedFile(out, resourcesFile, "");

 

out.close();  

}

 

 

 

 

 

 

 

 

 

 

 

 

 

public void createCompressedFile(ZipOutputStream out,File file,String dir) throws Exception{

//如果当前的是文件夹，则进行进一步处理

if(file.isDirectory()){

//得到文件列表信息

File[] files = file.listFiles();

//将文件夹添加到下一级打包目录

out.putNextEntry(new ZipEntry(dir+"/"));

 

dir = dir.length() == 0 ? "" : dir +"/";

 

//循环将文件夹中的文件打包

for(int i = 0 ; i < files.length ; i++){

createCompressedFile(out, files[i], dir + files[i].getName());         //递归处理

}

}

else{   //当前的是文件，打包处理

//文件输入流

FileInputStream fis = new FileInputStream(file);

 

out.putNextEntry(new ZipEntry(dir));

//进行写操作

int j =  0;

byte[] buffer = new byte[1024];

while((j = fis.read(buffer)) > 0){

out.write(buffer,0,j);

}

//关闭输入流

fis.close();

}

}

 

 

 

 

 

 

 

 

public void excelExport(HttpServletResponse response, List dataList,

OutputStream out, Criteria criteria) throws Exception {

logger.info("进来了...");

List collist = new ArrayList();

PsbcClientFilterDataHeaderField headerField = null;

if (!criteria.getAsString("typeState").equals("0")) {

headerField = new PsbcClientFilterDataHeaderField();

headerField.setDataFlag("0");

headerField.setDataType("0");

headerField.setFieldsCode("client_name");

headerField.setFieldsName("姓名");

collist.add(headerField);

headerField = new PsbcClientFilterDataHeaderField();

headerField.setDataFlag("0");

headerField.setDataType("0");

headerField.setFieldsCode("sex");

headerField.setFieldsName("性别");

collist.add(headerField);

headerField = new PsbcClientFilterDataHeaderField();

headerField.setDataFlag("0");

headerField.setDataType("0");

headerField.setFieldsCode("age");

headerField.setFieldsName("年龄");

collist.add(headerField);

headerField = new PsbcClientFilterDataHeaderField();

headerField.setDataFlag("0");

headerField.setDataType("0");

headerField.setFieldsCode("phone_number");

headerField.setFieldsName("电话");

collist.add(headerField);

headerField = new PsbcClientFilterDataHeaderField();

headerField.setDataFlag("0");

headerField.setDataType("0");

headerField.setFieldsCode("address");

headerField.setFieldsName("地址");

collist.add(headerField);

headerField = new PsbcClientFilterDataHeaderField();

headerField.setDataFlag("3");

headerField.setDataType("0");

headerField.setFieldsCode("statis_date");

headerField.setFieldsName("统计日期");

collist.add(headerField);

headerField = new PsbcClientFilterDataHeaderField();

headerField.setDataFlag("3");

headerField.setDataType("0");

headerField.setFieldsCode("agency_num");

headerField.setFieldsName("机构编码");

collist.add(headerField);

 

} else {

collist = this.getHeadersList(criteria);

 

}

headerField = (PsbcClientFilterDataHeaderField) collist.get(0);

String names[] = headerField.getFieldsCode().split(",");

String[][] excelData = new String[dataList.size() + 1][collist.size() + 1];

for (int i = 0; i < collist.size(); i++) {

PsbcClientFilterDataHeaderField a = (PsbcClientFilterDataHeaderField) collist

.get(i);

excelData[0][i] = a.getFieldsName();

}

String dataStr = "";

if (dataList.size() > 0) {

for (int i = 0; i < dataList.size(); i++) {

Map rowMap = (Map) dataList.get(i);

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

PsbcClientFilterDataHeaderField a = (PsbcClientFilterDataHeaderField) collist

.get(j);

if (null != rowMap.get(a.getFieldsCode().toUpperCase())) {

if (a.getDataType().equals("2")) {

 

Date dataD = (Date) rowMap.get(a.getFieldsCode()

.toUpperCase());

dataStr = (String) fmtDate.format(dataD);

}

 

else {

dataStr = rowMap.get(

a.getFieldsCode().toUpperCase()).toString();

}

} else {

dataStr = "";

}

excelData[(i + 1)][j] = String.valueOf(dataStr);

}

 

}

}

 

String fileName = "统计查询报表";

String excelName = "统计查询报表";

ExcelUtil

.seedReportExcel(response, fileName, excelName, excelData, out);

 

}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

public static void seedReportExcel(HttpServletResponse response,

String filename, String sheetname, String exceldata[][],

OutputStream os) throws IOException {

 

HSSFWorkbook wb = new HSSFWorkbook(); // 创建工作本

HSSFSheet sheet = wb.createSheet(sheetname);

HSSFRow row;// =sheet.createRow(exceldata.length+1); //总行数多一行为表头

HSSFCellStyle cellStyle = wb.createCellStyle();

short rgb = 211;

cellStyle.setFillBackgroundColor(rgb);

 

// 颜色样式

HSSFCellStyle style = wb.createCellStyle();

style.setWrapText(true);

style.setFillForegroundColor((short) 31); // 44 31

style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 边框

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style.setBorderRight(HSSFCellStyle.BORDER_THIN);

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

 

// 前3行及第一列按字符处理

// 循环画行

for (int l = 0; l < exceldata.length; l++) {

// 创建行

row = sheet.createRow((short) l);

// 循环画列

for (int i = 0; i < exceldata[0].length; i++) {

 

row.setHeight((short) 300); // 设置行高

sheet.setColumnWidth((short) i, (short) 5000);// 设计列宽

HSSFCell headerCell = row.createCell((short) i);

 

if (l < 1) {

headerCell.setCellStyle(style); // 表头设置样式

}

 

headerCell.setCellType(1);

headerCell.setCellValue(exceldata[l][i]);

 

}

}

sheet.setDisplayGridlines(true);// 显示网格线

sheet.setPrintGridlines(true);// 打印网格线

 

try {

wb.write(os);// 输出

} finally {

os.flush();

os.close();

}

}