public static void exportExcelAndPic(HttpServletResponse response, List<List<String>> excelData, String sheetName, String fileName, int columnWidth, String title, int colNum,Map<String, List<SysAttMain>> attMainMap) throws Exception {
//声明一个工作簿
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
HSSFCellStyle titleStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
font.setFontName("黑体");
font.setFontHeightInPoints((short) 10);//设置字体大小
font.setBold(true);
titleStyle.setFont(font);
HSSFCellStyle contentStyle = workbook.createCellStyle();
contentStyle.setBorderBottom(BorderStyle.THIN); //下边框
contentStyle.setBorderLeft(BorderStyle.THIN);//左边框
contentStyle.setBorderTop(BorderStyle.THIN);//上边框
contentStyle.setBorderRight(BorderStyle.THIN);//右边框
contentStyle.setAlignment(HorizontalAlignment.CENTER);
// contentStyle.setWrapText(true);
HSSFFont contentFont = workbook.createFont();
contentFont.setFontName("黑体");
contentFont.setFontHeightInPoints((short) 10);//设置字体大小
contentFont.setBold(false);
contentStyle.setFont(contentFont);
//通过的颜色
HSSFCellStyle passStyle = workbook.createCellStyle();
passStyle.setBorderBottom(BorderStyle.THIN); //下边框
passStyle.setBorderLeft(BorderStyle.THIN);//左边框
passStyle.setBorderTop(BorderStyle.THIN);//上边框
passStyle.setBorderRight(BorderStyle.THIN);//右边框
passStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFFont passFont = workbook.createFont();
passFont.setFontName("黑体");
passFont.setColor(HSSFColor.BRIGHT_GREEN.index);
passFont.setFontHeightInPoints((short) 10);//设置字体大小
passFont.setBold(false);
passStyle.setFont(passFont);
//未通过的颜色
HSSFCellStyle unPassStyle = workbook.createCellStyle();
unPassStyle.setBorderBottom(BorderStyle.THIN); //下边框
unPassStyle.setBorderLeft(BorderStyle.THIN);//左边框
unPassStyle.setBorderTop(BorderStyle.THIN);//上边框
unPassStyle.setBorderRight(BorderStyle.THIN);//右边框
unPassStyle.setAlignment(HorizontalAlignment.CENTER);
unPassStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
unPassStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFFont unPassFont = workbook.createFont();
unPassFont.setFontName("黑体");
unPassFont.setColor(HSSFColor.RED.index);
unPassFont.setFontHeightInPoints((short) 10);//设置字体大小
unPassFont.setBold(false);
unPassStyle.setFont(unPassFont);
HSSFCellStyle singleStyle = workbook.createCellStyle();
singleStyle.setAlignment(HorizontalAlignment.LEFT);
singleStyle.setFont(contentFont);
//生成一个表格,设置表格名称
HSSFSheet sheet = workbook.createSheet(sheetName);
CellRangeAddress cellRangeTitle = new CellRangeAddress(0,0,0,colNum-1);
sheet.addMergedRegion(cellRangeTitle);
if(title!=null){
HSSFRow rowheader = sheet.createRow(0);
HSSFCell cellheader = rowheader.createCell(0);
cellheader.setCellStyle(titleStyle);
cellheader.setCellValue(title);
//合并单元格后的边框设置
/*RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeTitle, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeTitle, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeTitle, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeTitle, sheet);*/
}
//设置表格列宽度
sheet.setDefaultColumnWidth(columnWidth);
//写入List<List<String>>中的数据
int rowIndex = 1;
for(List<String> data : excelData){
if(data.size()==1){
CellRangeAddress cellRangeData = new CellRangeAddress(rowIndex,rowIndex,0,colNum-1);
sheet.addMergedRegion(cellRangeData);
HSSFRow dataheader = sheet.createRow(rowIndex);
HSSFCell cellDataheader = dataheader.createCell(0);
cellDataheader.setCellStyle(singleStyle);
cellDataheader.setCellValue(data.get(0));
//合并单元格后的边框设置
/*RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeData, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeData, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeData, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeData, sheet);*/
rowIndex++;
}else{
//创建一个row行,然后自增1
HSSFRow row = sheet.createRow(rowIndex++);
//遍历添加本行数据
for (int i = 0; i < data.size(); i++) {
if(rowIndex == 1 || rowIndex == 2 ){
//创建一个单元格
HSSFCell cell = row.createCell(i);
cell.setCellStyle(contentStyle);
//创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(data.get(i));
//将内容对象的文字内容写入到单元格中
if(text.toString().equals("已导入")){
text.applyFont(passFont);
}else if(text.toString().contains("此数据")){
cell.setCellStyle(unPassStyle);
text.applyFont(unPassFont);
}else{
cell.setCellStyle(contentStyle);
}
cell.setCellValue(text);
}else{
if(i < 7 ){
//创建一个单元格
HSSFCell cell = row.createCell(i);
cell.setCellStyle(contentStyle);
//创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(data.get(i));
//将内容对象的文字内容写入到单元格中
if(text.toString().equals("已导入")){
text.applyFont(passFont);
}else if(text.toString().contains("此数据")){
cell.setCellStyle(unPassStyle);
text.applyFont(unPassFont);
}else{
cell.setCellStyle(contentStyle);
}
cell.setCellValue(text);
}else{
String id= data.get(i);
List<SysAttMain> sysAttMains = attMainMap.get(id);
List<Map<String, Object>> attByteList = attService.getAttByteList(sysAttMains);
if(!ArrayUtil.isEmpty(attByteList)){
for (int j = 0; j < attByteList.size(); j++) {
try {
Map<String, Object> attBytMap = attByteList.get(j);
String suffix = (String) attBytMap.get("suffix");
byte[] byteAtt = (byte[]) attBytMap.get("byte");
//创建一个单元格
int cellNum = 7+j;
HSSFCell cell = row.createCell(cellNum);
int pictureIndex = workbook.addPicture(byteAtt, Workbook.PICTURE_TYPE_JPEG);
CreationHelper helper = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
// 图片插入坐标
anchor.setCol1(cellNum);
anchor.setRow1(row.getRowNum());
// 插入图片
Picture pict = drawing.createPicture(anchor, pictureIndex);
sheet.autoSizeColumn(10);
// 指定我想要的长宽
pict.resize(1, 1);
} catch(Exception exception) {
exception.printStackTrace();
}
}
}
}
}
}
}
}
//准备将Excel的输出流通过response输出到页面下载
//八进制输出流
response.setContentType("application/octet-stream;charset=utf-8");
//设置导出Excel的名称
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
response.setCharacterEncoding("UTF-8");
//刷新缓冲
response.flushBuffer();
//workbook将Excel写入到response的输出流中,供页面下载该Excel文件
workbook.write(response.getOutputStream());
//关闭workbook
workbook.close();
}catch (Exception e){
}finally {
//关闭workbook
workbook.close();
}
}
导出数据,并把图片也导出在Excel里面参考代码