引入excel poi的依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>/**
* int MOVE_AND_RESIZE = 0;//跟随单元格扩大或者缩小,就是你拖动单元格的时候,图片大小也在变
* int MOVE_DONT_RESIZE = 2;//图片固定在该单元格在左上角,并且随着单元格移动
* int DONT_MOVE_AND_RESIZE = 3;//固定在Excel某个位置,像牛皮广告一样不会动
* @param pumpName
* @param year
* @param response
*/
public void exportPumpWaterTankCleaningReport(String pumpName, String year, HttpServletResponse response){
OutputStream ouputStream = null;
HSSFWorkbook sheets = new HSSFWorkbook();
String fileName = DateUtils.getDate()+"水箱清洗记录报表数据.xlsx";
ByteArrayOutputStream byteArrayOut =null;
try{
response.setHeader("Content-Disposition", "attachment; filename=".concat(new String(fileName.getBytes("GB2312"), "ISO8859-1")));
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.ms-excel");
ouputStream = response.getOutputStream();
//创建sheet页名称
HSSFSheet hssfSheet = sheets.createSheet("水箱清洗记录报表数据");
HSSFPatriarch patriarch = hssfSheet.createDrawingPatriarch();//导出图片使用
//单元格合并
hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//序号
hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//泵房名称
hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));//上半年清洗
hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 11));//下半年清洗
// Sheet样式
HSSFCellStyle titleStyle =sheets.createCellStyle();
// 背景色的设定
titleStyle.setFillBackgroundColor(HSSFColor.GREY_50_PERCENT.index);
// 前景色的设定
titleStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
// 填充模式
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//创建标题行
HSSFRow titleRow = hssfSheet.createRow(0);
titleRow.setHeight((short) (25 * 20));
HSSFCell oneCell = titleRow.createCell(0);
oneCell.setCellValue("序号");
oneCell.setCellStyle(titleStyle);
HSSFCell twoCell = titleRow.createCell(1);
twoCell.setCellValue("泵房名称");
twoCell.setCellStyle(titleStyle);
HSSFCell threeCell = titleRow.createCell(2);
threeCell.setCellValue("上半年清洗");
threeCell.setCellStyle(titleStyle);
HSSFCell fourCell = titleRow.createCell(7);
fourCell.setCellValue("下半年清洗");
fourCell.setCellStyle(titleStyle);
titleRow = hssfSheet.createRow(1);
titleRow.setHeight((short) (25 * 20));
HSSFCell titleCell = titleRow.createCell(2);
hssfSheet.setColumnWidth(2,10*300);
titleCell.setCellValue("清洗日期");
titleCell = titleRow.createCell(3);
hssfSheet.setColumnWidth(3,10*300);
titleCell.setCellValue("清洗时间");
titleCell = titleRow.createCell(4);
hssfSheet.setColumnWidth(4,10*300);
titleCell.setCellValue("清洗人员");
titleCell = titleRow.createCell(5);
hssfSheet.setColumnWidth(5,10*400);
titleCell.setCellValue("清洗前照片");
titleCell = titleRow.createCell(6);
hssfSheet.setColumnWidth(6,10*400);
titleCell.setCellValue("清洗后照片");
titleCell = titleRow.createCell(7);
hssfSheet.setColumnWidth(7,10*300);
titleCell.setCellValue("清洗日期");
titleCell = titleRow.createCell(8);
hssfSheet.setColumnWidth(8,10*300);
titleCell.setCellValue("清洗时间");
titleCell = titleRow.createCell(9);
hssfSheet.setColumnWidth(9,10*300);
titleCell.setCellValue("清洗人员");
titleCell = titleRow.createCell(10);
hssfSheet.setColumnWidth(10,10*400);
titleCell.setCellValue("清洗前照片");
titleCell = titleRow.createCell(11);
hssfSheet.setColumnWidth(11,10*400);
titleCell.setCellValue("清洗后照片");
//往excel填充数据
int serialNumber = 1;
HSSFRow dataRow = null;
List<BufferedImage> images = new ArrayList<>();
List<String> pictureList = new ArrayList<>();
List<List<TicketReport>> lists = this.queryPumpWaterTankCleaningReport(pumpName, year);
String exportPrePictureUrl = Global.getConfig("exportPrePictureUrl");
String exportSuPictureUrl = Global.getConfig("exportSuPictureUrl");
String filePath = null;
if(lists.size() >0){
for(List<TicketReport> list:lists){
int num=2;
dataRow = hssfSheet.createRow(hssfSheet.getLastRowNum() + 1);
for(int i = 1; i <= list.size(); i++){
TicketReport ticketReport = list.get(i-1);
if(null == ticketReport.getPumpName()) {
continue;
}
if(ticketReport.getPeriod() == 2 && num==2){//表示该泵房还未填充数据，只有下半年的数据
num = num+5;//上半年和下半年之间相差5列
dataRow.createCell(0).setCellValue(serialNumber);//序号
dataRow.createCell(1).setCellValue(ticketReport.getPumpName());//泵房名称
}else if(ticketReport.getPeriod() == 1){//表示上半年数据填充,单元格向前移动
num=2;
dataRow.createCell(0).setCellValue(serialNumber);//序号
dataRow.createCell(1).setCellValue(ticketReport.getPumpName());//泵房名称
}
dataRow.setHeight((short)1600);
dataRow.createCell(num).setCellValue(ticketReport.getCompleteDate());//清洗日期
dataRow.createCell(num+1).setCellValue(null == ticketReport.getProjectOperationTime()?"":ticketReport.getProjectOperationTime());//清洗时间
dataRow.createCell(num+2).setCellValue(null == ticketReport.getOperationUserName()?"":ticketReport.getOperationUserName());//清洗人员
String beforePicture = ticketReport.getBeforePicture();
images = new ArrayList<>();
if(null != beforePicture && !"".equals(beforePicture)){
pictureList = Arrays.asList(beforePicture.split(","));
for(String picture:pictureList){
filePath = exportPrePictureUrl+ticketReport.getTicketId()+exportSuPictureUrl+ticketReport.getId()+"/"+picture;
// images.add(ImageIO.read(new File(filePath)));
images.add(toBufferedImage(Toolkit.getDefaultToolkit().getImage(filePath)));//解决ImageIO.read方法造成图片失真问题
}
}else{
dataRow.createCell(num+3).setCellValue("");//清洗前照片
}
int rowNum = hssfSheet.getLastRowNum();
int cellNum1 = num+3;
int cellNum2 = num+4;
for (BufferedImage image : images) {
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(image, "jpg", byteArrayOut);
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, (short) cellNum1, rowNum, (short) cellNum2, rowNum+1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
// 插入图片
patriarch.createPicture(anchor, sheets.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
String afterPicture = ticketReport.getAfterPicture();
images = new ArrayList<>();
if(null != afterPicture && !"".equals(afterPicture)){
pictureList = Arrays.asList(afterPicture.split(","));
for(String picture:pictureList){
filePath = exportPrePictureUrl+ticketReport.getTicketId()+exportSuPictureUrl+ticketReport.getId()+"/"+picture;
// images.add(ImageIO.read(new File(filePath)));
images.add(toBufferedImage(Toolkit.getDefaultToolkit().getImage(filePath)));
}
}else{
dataRow.createCell(num+4).setCellValue("");//清洗后照片
}
cellNum1 = num+4;
cellNum2 = num+5;
for (BufferedImage image : images) {
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(image, "jpg", byteArrayOut);
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, (short) cellNum1, rowNum, (short) cellNum2, rowNum+1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
// 插入图片
patriarch.createPicture(anchor, sheets.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
// dataRow.createCell(num+4).setCellValue(serialNumber);//清洗后照片
num = num+5;//上半年和下半年之间相差5列
}
serialNumber = serialNumber+1;
}
}
sheets.write(ouputStream);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
if(null != ouputStream){
ouputStream.close();
sheets.close();
}
if(byteArrayOut != null){
try {
byteArrayOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
}上面代码中和图片相关的代码如下：
hssfSheet.setColumnWidth(6,10*400);
hssfSheet.setColumnWidth(7,10*300);
hssfSheet.setColumnWidth(10,10*400);
hssfSheet.setColumnWidth(11,10*400);
dataRow.setHeight((short)1600);
String beforePicture = ticketReport.getBeforePicture();
images = new ArrayList<>();
if(null != beforePicture && !"".equals(beforePicture)){
pictureList = Arrays.asList(beforePicture.split(","));
for(String picture:pictureList){
filePath = exportPrePictureUrl+ticketReport.getTicketId()+exportSuPictureUrl+ticketReport.getId()+"/"+picture;
// images.add(ImageIO.read(new File(filePath)));
images.add(toBufferedImage(Toolkit.getDefaultToolkit().getImage(filePath)));//解决ImageIO.read方法造成图片失真问题
}
}else{
dataRow.createCell(num+3).setCellValue("");//清洗前照片
}
int rowNum = hssfSheet.getLastRowNum();
int cellNum1 = num+3;
int cellNum2 = num+4;
for (BufferedImage image : images) {
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(image, "jpg", byteArrayOut);
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, (short) cellNum1, rowNum, (short) cellNum2, rowNum+1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
// 插入图片
patriarch.createPicture(anchor, sheets.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
String afterPicture = ticketReport.getAfterPicture();
images = new ArrayList<>();
if(null != afterPicture && !"".equals(afterPicture)){
pictureList = Arrays.asList(afterPicture.split(","));
for(String picture:pictureList){
filePath = exportPrePictureUrl+ticketReport.getTicketId()+exportSuPictureUrl+ticketReport.getId()+"/"+picture;
// images.add(ImageIO.read(new File(filePath)));
images.add(toBufferedImage(Toolkit.getDefaultToolkit().getImage(filePath)));
}
}else{
dataRow.createCell(num+4).setCellValue("");//清洗后照片
}
cellNum1 = num+4;
cellNum2 = num+5;
for (BufferedImage image : images) {
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(image, "jpg", byteArrayOut);
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, (short) cellNum1, rowNum, (short) cellNum2, rowNum+1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
// 插入图片
patriarch.createPicture(anchor, sheets.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}以上需要注意的是：
1：setColumnWidth方法和setHeight方法可以将导出的图片放大到需要的大小
2：HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, (short) cellNum1, rowNum, (short) cellNum2, rowNum+1);这段代码，这将会决定你的图片生成到excel单元格的位置，该方法在源码中的接口如下：
public HSSFClientAnchor(int dx1,int dy1,int dx2,int dy2,short col1,int row1,short col2,int row2)
dx1:第一个单元格的开始X坐标
dy1:第一个单元格的开始y坐标
dx2:第二个单元格的开始X坐标
dy2:第二个单元格的开始y坐标
col1 图片的左上角放在第几个列cell，决定图片在哪一列
row1 图片的左上角放在第几个行cell，决定图片在哪一行
col2 图片的右下角放在第几个列cell，
row2 图片的右下角放在第几个行cell
下面放一张导出的效果：
上面代码涉及到的方法：
public static BufferedImage toBufferedImage(Image image) {
if (image instanceof BufferedImage) {
return (BufferedImage)image;
}
// This code ensures that all the pixels in the image are loaded
image = new ImageIcon(image).getImage();
// Determine if the image has transparent pixels; for this method's
// implementation, see e661 Determining If an Image Has Transparent Pixels
//boolean hasAlpha = hasAlpha(image);
// Create a buffered image with a format that's compatible with the screen
BufferedImage bimage = null;
GraphicsEnvironment ge = GraphicsEnvironment.getLocalGraphicsEnvironment();
try {
// Determine the type of transparency of the new buffered image
int transparency = Transparency.OPAQUE;
/* if (hasAlpha) {
transparency = Transparency.BITMASK;
}*/
// Create the buffered image
GraphicsDevice gs = ge.getDefaultScreenDevice();
GraphicsConfiguration gc = gs.getDefaultConfiguration();
bimage = gc.createCompatibleImage(
image.getWidth(null), image.getHeight(null), transparency);
} catch (HeadlessException e) {
// The system does not have a screen
}
if (bimage == null) {
// Create a buffered image using the default color model
int type = BufferedImage.TYPE_INT_RGB;
//int type = BufferedImage.TYPE_3BYTE_BGR;//by wang
/*if (hasAlpha) {
type = BufferedImage.TYPE_INT_ARGB;
}*/
bimage = new BufferedImage(image.getWidth(null), image.getHeight(null), type);
}
// Copy image to buffered image
Graphics g = bimage.createGraphics();
// Paint the image onto the buffered image
g.drawImage(image, 0, 0, null);
g.dispose();
return bimage;
}参考博文：https://blog.csdn.net/qq_40524941/article/details/96155733
526
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
