POI导出数据

private ResourceConfig rc;

public void copyright_exportAllData(Long jobId,String siteName,String keyWords,Integer days,String status,String dateStartTime,String dateEndTime,HttpServletResponse response,HttpServletRequest request) {

//根据days获取时间
if(days != null) {
Date date = new Date();
dateStartTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(DateUtils.addDays(date, -days));
dateEndTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
}
List<Map<String,Object>> copyright_exportList = fbigzTortDataService.selectBySerarch(jobId,status,siteName,keyWords,dateStartTime,dateEndTime);

int sheetNum=1;//工作薄sheet编号 
int currentRowCount=1;//当前的行号
int perPageNum = 50000;//每个工作薄显示50000条数据  
String title = "侵权数据";
int count = 0;
String title_content = "侵权/盗版内容";
String downloadName =  "侵权盗版监管_";

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(title+sheetNum);

BufferedImage bufferImg = null;
setCellHead(wb, sheet,title_content);
for(int i = 0; i<copyright_exportList.size();i++) {
Map<String,Object> map = copyright_exportList.get(i);
String tort_tortPicture = (String) map.get("tortPicture");
String tort_worksName = (String) map.get("worksName");
String tort_title = (String) map.get("title");
String tort_url = (String) map.get("url");
String tort_title_url = tort_title+"\r\n"+tort_url;  //拼装url

String tort_siteName = (String) map.get("siteName");
Date tort_dataCreateTime =  (Date) map.get("dataCreateTime");
String tort_status = (String) map.get("status");


count = setRowValue(wb, sheet, count, i,bufferImg,tort_tortPicture,tort_worksName,tort_title_url,tort_siteName,tort_dataCreateTime,tort_status);
//超过50000条数据，分sheet
if(currentRowCount % perPageNum == 0) {
sheet=null;
sheetNum++;//工作薄编号递增1
if(currentRowCount != copyright_exportList.size()) {
sheet = wb.createSheet(title+sheetNum);//创建一个新的工作薄
setCellHead(wb, sheet,title_content);
count = 0;
}
}
currentRowCount++;//当前行号递增1
}
//打印并关闭
exportDownloadCloseStream(wb,response,downloadName);

}

private void setCellHead(HSSFWorkbook wb, HSSFSheet sheet,String title_content) {
sheet.autoSizeColumn(1, true);
sheet.setDefaultColumnWidth((short)29);
//创建表头
HSSFRow firstRow = sheet.createRow(0);
Cell cell = firstRow.createCell((short) 0);
firstRow.setHeight((short) 1000);//设置高度

// 定义单元格格式，添加单元格表样式，并添加到工作簿
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        
        //设置表头填充颜色
        cellStyleTitle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        cellStyleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//循环为表头填充颜色
for(int i = 0; i < 6; i++) {
HSSFCell cellTitle = firstRow.createCell(i);
//cellTitle.setCellValue("X46");
cellTitle.setCellStyle(cellStyleTitle);
switch(i){
case 0:
cellTitle.setCellValue("图片");
break;
case 4:
cellTitle.setCellValue("监管日期");
break;
case 5:
cellTitle.setCellValue("状态");
break;
default: 
cellTitle.setCellValue(title_content);
}
}
//1.1创建合并单元格对象  
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,1,3);//起始行,结束行,起始列,结束列
//2.1加载合并单元格对象  
sheet.addMergedRegion(callRangeAddress);
cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置居中

}

private int setRowValue(HSSFWorkbook wb, HSSFSheet sheet, int count, int i,BufferedImage bufferImg,String tortPicture,String title,String siteName,String url,Date content_dataCreateTime,String status) {
count++;
//1.1创建合并单元格对象  
CellRangeAddress callRangeAddress = new CellRangeAddress(count,count,1,3);//起始行,结束行,起始列,结束列
//2.1加载合并单元格对象  
sheet.addMergedRegion(callRangeAddress);

HSSFRow row = sheet.createRow(count);
row.setHeight((short) 1500);//设置其他行高度


ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();  
try {
//获取项目路径
String basePath = rc.getFilePath();
String nullImagePath = rc.getFilePathNullImage();
//判断非空
if(tortPicture == null) {
bufferImg = ImageIO.read(new File(nullImagePath));
}else {
try {
bufferImg = ImageIO.read(new File(basePath+tortPicture));
}catch (Exception e) {
bufferImg = ImageIO.read(new File(nullImagePath));
}
}
ImageIO.write(bufferImg, "jpeg", byteArrayOut);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 0, (count), (short) 1, (count+1));//表示的是 第0列第一行
anchor.setAnchorType(3);
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

//设置图片大小
row.setHeightInPoints(5*sheet.getDefaultRowHeightInPoints());
} catch (IOException e) {
e.printStackTrace();
}

//设置自动换行
HSSFCellStyle cellS = wb.createCellStyle();  
HSSFCell cellone = row.createCell((short)1);     
cellS.setWrapText(true);//先设置为自动换行    
cellS.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellone.setCellStyle(cellS);                            

String content_title = "";
if(title == null) {
content_title = "";
}else {
content_title = title;
}

String content_siteName = "";
if(null != siteName) {
content_siteName = siteName;
}
cellone.setCellValue(new HSSFRichTextString(content_title+"\r\n"+url+"\r\n"+content_siteName));  
// 监测时间
String dataCreateTime = DateUtil.formatDate((Date) content_dataCreateTime, DateUtil.FORMAT_PATTERN_SECOND);
row.createCell(4).setCellValue(dataCreateTime);


String result = "";
if(status.equals("1")) {
result = "已转办";
}else if(status.equals("2")){
result = "待转办";
}else if(status.equals("0")){
result = "全部";
}
row.createCell(5).setCellValue(result);
return count;
}

/**
* 打印excel并关闭流
* @param wb
* @param response
*/
private void exportDownloadCloseStream(HSSFWorkbook wb,HttpServletResponse response,String downloadName) {
OutputStream ouputStream = null;
try {
String filename = downloadName + new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xlsx";  
response.setContentType("multipart/form-data");
response.setHeader("text/html;","charset=utf-8");
response.setHeader("Content-disposition","attachment;filename=" +  new String(filename.getBytes("gbk"),"iso-8859-1"));
ouputStream = response.getOutputStream();     
wb.write(ouputStream);
} catch (FileNotFoundException e) {
            JOptionPane.showMessageDialog(null, "导出失败!");
            e.printStackTrace();
        } catch (IOException e) {
            JOptionPane.showMessageDialog(null, "导出失败!");
            e.printStackTrace();
        } finally {
try {
ouputStream.flush();    
ouputStream.close();
} catch (IOException e) {
JOptionPane.showMessageDialog(null, "导出失败!");
e.printStackTrace();
} 

        }

public class ResourceConfig {
private static Logger LOGGER = Logger.getLogger(ResourceConfig.class);

public String getFilePath() {
String path = this.getValue("/opt/tomcat/file");
return path;
}
public String getFilePathNullImage() {
String path = this.getValue("/opt/tomcat/webapps/ROOT/static/css/images/null.jpg");
return path;
}
}