/*
-
导出excel
-
*/
public void listExcel(,ttpServletResponse response) throws IOException {
lists = activityCopywriterMapper.selectByExample(example);
//表头
List title = new ArrayList();
title.add(“姓名”);
title.add(“年龄”);
title.add(“等级”);
title.add(“内容”);
title.add(“附图”);
//创建HSSFWorkbook对象(excel的文档对象)
XSSFWorkbook wb = new XSSFWorkbook();
try {
// 建立新的sheet对象(excel的表单)
XSSFSheet sheet = wb.createSheet(“sheet1”);
//创建一个XXF的样式模板
XSSFCellStyle xssfCellStyle =wb.createCellStyle();
//设置换行
xssfCellStyle.setWrapText(true);
//设置水平垂直居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
//设置每一列的列宽 前面是列坐标后面是宽度
//设置图片附件宽度
for (int i=1 ;i<100;i++){
sheet.setColumnWidth(i,5000);
}
//创建第一行
Row row=sheet.createRow(0);
Cell cell=null;
//单元格
int i=0;
//插入第一行数据的表头
for (String s : title) {
cell=row.createCell(i++);
cell.setCellValue(s);
cell.setCellStyle(xssfCellStyle);
}
//行数
int rowNum=1;
List VOS = changeVO(lists);
for (VO vo : VOS) {
int lattice=0;
Row nrow=sheet.createRow(rowNum);
//设置行高
nrow.setHeight((short) 5000);
Cell ncell=nrow.createCell(0);
ncell.setCellValue(VO.getUsername());
//在每一列里使用XSSF的样式应用给这一单元格
ncell.setCellStyle(xssfCellStyle);
Cell ncell1=nrow.createCell(1);
ncell1.setCellValue(VO.getSpokenScore());
ncell1.setCellStyle(xssfCellStyle);
Cell ncell2=nrow.createCell(2);String [] classifyType = {"群组","人","活跃"}; ncell2.setCellValue(classifyType[VO.getClassifyType()-1]); ncell2.setCellStyle(xssfCellStyle); Cell ncell3=nrow.createCell(3); ncell3.setCellValue(VO.getContent()); ncell3.setCellStyle(xssfCellStyle); //图片所在列数 int imgUrlIndex = 4; for (Attach attach :VO.getActivityCopywriterAttachList()) { Cell ncellImg=nrow.createCell(4); String imgUrl=activityCopywriterAttach.getContent(); //下载网络图片 URL url = null; url = new URL(imgUrl); DataInputStream dataInputStream = new DataInputStream(url.openStream()); ByteArrayOutputStream output = new ByteArrayOutputStream(); byte[] buffer = new byte[1024]; int length; //将下载的网络图片给byteArrayOutputStream对象 while ((length = dataInputStream.read(buffer)) > 0) { output.write(buffer, 0, length); } //imgUrlIndex图片位置 图片搜索在行数rowNum XSSFClientAnchor anchor = new XSSFClientAnchor(3000, 3000, 3000, 3000, (short) imgUrlIndex, rowNum, (short) imgUrlIndex+1, rowNum+1); XSSFDrawing patriarch = sheet.createDrawingPatriarch(); // 插入图片 把ByteArrayOutputStream加入到单元格并且设置格式 patriarch.createPicture(anchor, wb.addPicture(output.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG)); //图片位置 imgUrlIndex++; } //行数+1 rowNum++; } //写出 wb.write(response.getOutputStream());
}finally {
//关闭流
wb.close();
}
}