POI向Excel中插入图片

于 2022-08-03 14:47:06 发布
阅读量1.5k 收藏 1
点赞数
分类专栏： 后端 文章标签： java apache
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/CSDNlzy/article/details/126140175
版权 
package com.xiangyu.bigdata.xycom.execl;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.security.Principal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.xiangyu.bigdata.xycom.model.EsComEquipmentDetail;
import com.xiangyu.bigdata.xycom.model.EsComEquipmentHospital;
import com.xiangyu.bigdata.xycom.service.EsComEquipmentDetailService;
import com.xiangyu.bigdata.xycom.service.EsComEquipmentHospitalService;
import com.xiangyu.bigdata.xycom.util.ActionResult;
import com.xiangyu.bigdata.xycom.util.Global;
import com.xiangyu.bigdata.xycom.util.ParamsUtils;
import com.xiangyu.bigdata.xycom.util.StringUtils;
import com.xiangyu.bigdata.xycom.word.StoreImage;

import org.apache.commons.codec.binary.Base64;
@SpringBootApplication
@RestController
@RequestMapping("com")
public class EsComEquipmentDetailExcel extends ActionResult
{
    /**
	 * 
	 */
    private static final long serialVersionUID = 1L;
    @Autowired
    private EsComEquipmentDetailService escomequipmentdetailService;
    @Autowired
	private EsComEquipmentHospitalService escomequipmenthospitalService;
    @RequestMapping("importEsComEquipmentDetailExcel")
    public ActionResult importEsComEquipmentDetail(@RequestParam(value = "filename") MultipartFile file)
    {
        try
        {
            String fileName = file.getOriginalFilename();
            // 获取文件后缀
            String prefix = fileName.substring(fileName.lastIndexOf("."));
            // 用uuid作为文件名，防止生成的临时文件重复
            final File excelFile = File.createTempFile(UUID.randomUUID().toString(), prefix);
            // MultipartFile to File
            file.transferTo(excelFile);
            List<List<Object>> excleDataList = ReadExcel.readExcel(excelFile);
            for (int i = 1; i < excleDataList.size(); i++)
            {
                EsComEquipmentDetail ecd = new EsComEquipmentDetail();
                //ecd.setFname(excleDataList.get(i).get(1).toString());
                //ecd.setFvalue(excleDataList.get(i).get(2).toString());
                escomequipmentdetailService.add(ecd);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return ActionResult.Succeed();
    }

    @RequestMapping("outEsComEquipmentDetailExcel")
    public void outEsComEquipmentDetail(@RequestParam("fid") Integer fid,@RequestParam Map<String, String> parameters, Map<String, Object> model, Principal principal, HttpServletResponse response)
    {
        try
        {
        	List<EsComEquipmentDetail> list = escomequipmentdetailService.selectEquipmentDetailByHospitalCode(fid+"");
            String title = "记录";
            String excelName = "部门表";
            String[] rowsName = new String[]
            { "序号", "名称", "编码", "备注" };
            List<Object[]> dataList = new ArrayList<Object[]>();
            for (int i = 0; i < list.size(); i++)
            {
                EsComEquipmentDetail data = list.get(i);
                Object[] objs = new Object[rowsName.length];
                objs[0] = i;
                //objs[1] = data.getFname();
                //objs[2] = data.getFvalue();
                dataList.add(objs);
            }
            ExportExcel ex = new ExportExcel(title, rowsName, dataList, excelName);
            ex.export(response);
        }
        catch (Exception e)
        {
        }
    }
    @RequestMapping("outEsComEquipmentDetailExcels")
    public void outEsComEquipmentDetailExcels(@RequestParam("fid") Integer fid,@RequestParam Map<String, String> parameters, Map<String, Object> model, Principal principal, HttpServletResponse response)
    {
    	try
    	{
    		List<EsComEquipmentDetail> list = escomequipmentdetailService.selectEquipmentDetailByHospitalCode(fid+"");
    		EsComEquipmentHospital escomequipmenthospital = escomequipmenthospitalService.findByPrimaryKey(fid);
    		String sumCount = null;
    		int countSum = 0;
    		
    		String sumTotalPrice = null;
    		int countSumTotalPrice = 0;
    		for (EsComEquipmentDetail esComEquipmentDetail : list) {
    			int count = StringUtils.changeToInt(esComEquipmentDetail.getFcount());
    			countSum+= count;
    			
    			int countTotalPrice = StringUtils.changeToInt(esComEquipmentDetail.getTotalPrice());
    			countSumTotalPrice+= countTotalPrice;
			}
    		sumCount = countSum +"";
    		sumTotalPrice = countSumTotalPrice +"";
    		String excelName = escomequipmenthospital.getHospitalName();
			// 开始创建Excel
			Workbook wb = new HSSFWorkbook();
			// 创建Sheet
			Sheet sheet = wb.createSheet("记录");
			// 列宽度自适应
			sheet.autoSizeColumn(1, true);
			
			// 设置字体
			Font font1 = wb.createFont();
			font1.setFontHeightInPoints((short) 11);// 字号
			font1.setFontName("宋体");

			Font font = wb.createFont();
			font.setFontHeightInPoints((short) 24);// 字号
			font.setFontName("宋体");
			font.setBold(true);

			Font font3 = wb.createFont();
			font3.setFontHeightInPoints((short) 13);// 字号
			font3.setFontName("宋体");
			font3.setBold(true);

			Font font4 = wb.createFont();
			font4.setFontHeightInPoints((short) 11);// 字号
			font4.setFontName("宋体");
			font4.setBold(true);

			// 创建行,下标都是从0开始
			Row row = sheet.createRow(0);
			// 创建一个单元格,第一列,下标都是从0开始
			Cell cell = row.createCell(0);
			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
			// 给单元格设置值
			CellStyle style2 = wb.createCellStyle();
			style2.setFont(font);
			style2.setAlignment(HorizontalAlignment.CENTER);
			cell.setCellValue(excelName);
			cell.setCellStyle(style2);

			CellStyle style3 = wb.createCellStyle();
			style3.setFont(font1);
			style3.setAlignment(HorizontalAlignment.CENTER);
			style3.setVerticalAlignment(VerticalAlignment.CENTER);
			// 列宽度自适应
			sheet.setColumnWidth(0, sheet.getColumnWidth(7) * 30 / 10);
			sheet.setColumnWidth(1, sheet.getColumnWidth(7) * 30 / 10);
			sheet.setColumnWidth(2, sheet.getColumnWidth(7) * 30 / 10);
			sheet.setColumnWidth(3, sheet.getColumnWidth(7) * 50 / 10);
			sheet.setColumnWidth(4, sheet.getColumnWidth(7) * 35 / 10);
			sheet.setColumnWidth(5, sheet.getColumnWidth(7) * 45 / 10);
			sheet.setColumnWidth(6, sheet.getColumnWidth(7) * 35 / 10);
			sheet.setColumnWidth(7, sheet.getColumnWidth(7) * 35 / 10);
			CellStyle style = wb.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setFont(font1);

			CellStyle style4 = wb.createCellStyle();
			style4.setAlignment(HorizontalAlignment.CENTER);
			style4.setFont(font4);

			Row row2 = sheet.createRow(1);
			// 创建一个单元格,第一列,下标都是从0开始
			Cell cell1 = row2.createCell(0);
			cell1.setCellValue("产品名称");
			Cell cell2 = row2.createCell(1);
			cell2.setCellValue("型号");
			Cell cell3 = row2.createCell(2);
			cell3.setCellValue("报价");
			Cell cell4 = row2.createCell(3);
			cell4.setCellValue("经销商价格");
			Cell cell5 = row2.createCell(4);
			cell5.setCellValue("数量");
			Cell cell6 = row2.createCell(5);
			cell6.setCellValue("总价");
			Cell cell7 = row2.createCell(6);
			cell7.setCellValue("产品介绍");
			Cell cell8 = row2.createCell(7);
			cell8.setCellValue("产品参数");
			Cell cell9 = row2.createCell(8);
			cell9.setCellValue("图片");
			Cell cell10 = row2.createCell(9);
			cell10.setCellValue("收费编码");
			Cell cell11 = row2.createCell(10);
			cell11.setCellValue("收费标准");
			Cell cell12 = row2.createCell(11);
			cell12.setCellValue("适应症");
			
			cell1.setCellStyle(style4);
			cell2.setCellStyle(style4);
			cell3.setCellStyle(style4);
			cell4.setCellStyle(style4);
			cell5.setCellStyle(style4);
			cell6.setCellStyle(style4);
			cell7.setCellStyle(style4);
			cell8.setCellStyle(style4);
			cell9.setCellStyle(style4);
			cell10.setCellStyle(style4);
			cell11.setCellStyle(style4);
			cell12.setCellStyle(style4);
			
			// 开始第三行 数据
			for (int i = 0; i < list.size(); i++) {
				EsComEquipmentDetail esComEquipmentDetail = list.get(i);
						Row rowsa = sheet.createRow(2 + i);
						
						Cell cell110 = rowsa.createCell(0);
						cell110.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getEquipmentName()));
						cell110.setCellStyle(style3);
						Cell cell111 = rowsa.createCell(1);
						cell111.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getEquipmentModel()));
						cell111.setCellStyle(style3);
						Cell cell122 = rowsa.createCell(2);
						cell122.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getPrice()));
						cell122.setCellStyle(style3);
						Cell cell1222 = rowsa.createCell(3);
						cell1222.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getFreserv3()));
						cell1222.setCellStyle(style3);
						Cell cell13 = rowsa.createCell(4);
						cell13.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getFcount()));
						cell13.setCellStyle(style3);
						Cell cell14 = rowsa.createCell(5);
						cell14.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getTotalPrice()));
						cell14.setCellStyle(style3);

						Cell cell15 = rowsa.createCell(6);
						cell15.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getFdescribe()));
						cell15.setCellStyle(style3);

						Cell cell16 = rowsa.createCell(7);
						cell16.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getFparam()));
						cell16.setCellStyle(style3);

						Cell cell17 = rowsa.createCell(8);
						String imageUrl = StringUtils.changeToString(esComEquipmentDetail.getEquipmentUrl());
						if(StringUtils.isBlank(imageUrl)) {
							cell17.setCellValue("无图片");
							cell17.setCellStyle(style3);
						}else {
							rowsa.setHeightInPoints(100);
							cellImage(wb,sheet,i,imageUrl);
						}

						Cell cell18 = rowsa.createCell(9);
						cell18.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getPriceCode()));
						cell18.setCellStyle(style3);

						Cell cell20 = rowsa.createCell(10);
						cell20.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getFreserv2()));
						cell20.setCellStyle(style3);
						
						Cell cell21 = rowsa.createCell(11);
						cell21.setCellValue(StringUtils.changeToString(esComEquipmentDetail.getIndication()));
						cell21.setCellStyle(style3);
						
			}
			Row finalRow = sheet.createRow(list.size() + 2);
			
			Cell cell86 = finalRow.createCell(0);
			cell86.setCellValue("合计");
			cell86.setCellStyle(style3);
			Cell cell33 = finalRow.createCell(4);
			cell33.setCellValue(sumCount);
			cell33.setCellStyle(style3);
			Cell cell44 = finalRow.createCell(5);
			cell44.setCellValue(sumTotalPrice);
			cell44.setCellStyle(style3);
			 
			if (wb != null) {
				try {
					// 具体的大家可以下来去了解
					OutputStream output = response.getOutputStream();
					// 清空缓存
					response.reset();
					// 定义浏览器响应表头，顺带定义下载名，比如students
					response.setContentType("application/msexcel;charset=UTF-8");
					// response.setCharacterEncoding("UTF-8");
					String fileName = URLEncoder.encode(excelName, "UTF-8");
					output = response.getOutputStream();
					response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
					// 定义下载的类型，标明是excel文件
					response.setContentType("application/vnd.ms-excel;charset=UTF-8");
					// 这时候把创建好的excel写入到输出流
					wb.write(output);
					// 养成好习惯，出门记得随手关门
					output.close();

				} catch (IOException e) {
					e.printStackTrace();
					// return ActionResult.Failed();
				}
			}
    	}
    	catch (Exception e)
    	{
    	}
    }
    //Excel单元格插入图片
    public void cellImage(Workbook wb,Sheet sheet,int i,String imageUrl) throws Exception {
    	FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;//图片
    	try {
    		StoreImage Blo = new StoreImage();
			File url = Blo.insertBlobRemote(ParamsUtils.pathForOSSOriginal(imageUrl));
			String imgs = null;
			InputStream ins;
			byte[] picdatas = null;
			try {
				ins = new FileInputStream(url);
				picdatas = new byte[ins.available()];
				ins.read(picdatas);
				ins.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			//BASE64Encoder encoders = new BASE64Encoder();
//			url.delete();1
			
			imgs = Base64.encodeBase64String(picdatas);
    		
			// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray  
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();  
			//将图片读到BufferedImage  
			//bufferImg = ImageIO.read(new File("C:/Users/uctimes/Desktop/1.jpg"));
			bufferImg = ImageIO.read(url);
//			bufferImg = ImageIO.read(new File(url));
			url.delete();
			// 将图片写入流中  
			ImageIO.write(bufferImg, "png", byteArrayOut);  
			// 利用HSSFPatriarch将图片写入EXCEL  
			Drawing patriarch = sheet.createDrawingPatriarch(); 
			/**
			             * 该构造函数有8个参数
			             * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
			             * 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
			 * excel中的cellNum和rowNum的index都是从0开始的
			 *  
			 */  
			//图片一导出到单元格B5中
			HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,  
			        (short) 7, i+2, (short)8, i+3);
			// 插入图片  
			patriarch.createPicture(anchor, wb.addPicture(byteArrayOut  
			        .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    @RequestMapping("outEsComEquipmentDetailExcelByPage")
    public ActionResult outEsComEquipmentDetailByPage(@RequestParam(name = "pageNum", required = false) Integer pageNum,
            @RequestParam(name = "pageSize", required = false) Integer pageSize, @RequestParam(name = "search_key", required = false) String search_key,
            @RequestParam Map<String, String> parameters, Map<String, Object> model, Principal principal, HttpServletResponse response)
    {
        try
        {
            Integer midpage;
            if (StringUtils.changeToInt(pageNum) == 0)
            {
                midpage = 1;
            }
            else
            {
                midpage = StringUtils.changeToInt(pageNum);
            }
            Integer pageNumInteger;
            Integer pageSizeInteger;
            if (StringUtils.isBlank(pageSize))
            {
                pageNumInteger = (midpage - 1) * Global.APP_PAGESIZE;
                pageSizeInteger = Global.APP_PAGESIZE;
            }
            else
            {
                pageNumInteger = (midpage - 1) * StringUtils.changeToInt(pageSize);
                pageSizeInteger = StringUtils.changeToInt(pageSize);
            }
            List<EsComEquipmentDetail> list = escomequipmentdetailService.listEsComEquipmentDetailAjaxByPage(pageNumInteger, pageSizeInteger,search_key);
            String title = "记录";
            String excelName = "部门表";
            String[] rowsName = new String[]
            { "序号", "名称", "编码", "备注" };
            List<Object[]> dataList = new ArrayList<Object[]>();
            for (int i = 0; i < list.size(); i++)
            {
                EsComEquipmentDetail data = list.get(i);
                Object[] objs = new Object[rowsName.length];
                objs[0] = i;
                //objs[1] = data.getFname();
                //objs[2] = data.getFvalue();
                dataList.add(objs);
            }
            ExportExcel ex = new ExportExcel(title, rowsName, dataList, excelName);
            ex.export(response);
            return ActionResult.Succeed(list);
        }
        catch (Exception e)
        {
            return ActionResult.Failed(e);
        }
    }

    public static void inputStreamToFile(InputStream ins, File file)
    {
        try
        {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1)
            {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

csdnlzy

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值