Springboot Excel 操作总结

上传表格

 public String upload(HttpServletResponse response, @RequestParam("file") MultipartFile file) throws IOException, InvalidFormatException {
 		//判断文件传输是否成功
        if (file.isEmpty() == true){
            return "index";
        }
        String fileName = file.getOriginalFilename();
        byte [] byteArr=file.getBytes();
        InputStream inputStream = new ByteArrayInputStream(byteArr);
        String str[] = fileName.split("\\.");
        if ("xls".equals(str[1])){
            workbook = new HSSFWorkbook(inputStream);
       }else {
            workbook = new XSSFWorkbook(inputStream);
        }
        return "index";
    }

生成表单

public String createExcel(HttpServletResponse response){
	  XSSFWorkbook wb = new XSSFWorkbook();
	  //sheet名字
	  XSSFSheet sheet = wb.createSheet("xxx测量单");
	  //设置宽度
	  //第一个参数:第几列 第二个参数: 多少字节的宽度
	  sheet.setColumnWidth(0, 25 * 256);
	  //生成第几行
	  XSSFRow row = sheet.createRow(0);
	  // 设置行高
	  row.setHeight((short) 800);
	  // 生成第几列
	  XSSFCell cell = row.createCell(0);
	  //生成样式
	  XSSFCellStyle style = createCellStyle(wb);
	  //设置这一单元格的样式
	  cell.setCellStyle(style);
	  //这一单元格的值
	  cell.setCellValue("表格内容");
	  // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
	  sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
	  //插入图片
	  url = new URL("https://m.360buyimg.com/babel/jfs/t1/109882/8/31450/102963/62e38aaaE994d3952/2c77c57cc66292df.jpg");
	  setPhoto(wb, sheet, 0, 0, 1, 2, url);
	  //浏览器下载excel
	  setBrowser(response,wb,"测量表.xlsx");
	  return "成功";
}

表单样式

private static XSSFCellStyle createCellStyle(XSSFWorkbook wb) {
	XSSFCellStyle cellStyle = wb.createCellStyle();
	//表格背景色
	cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(252, 228, 214, 1)));
	cellStyle.setFillBackgroundColor(new XSSFColor(new java.awt.Color(252, 228, 214, 1)));
	cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	//字体水平居中
	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	//字体垂直居中
	cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
	XSSFFont headerFont = wb.createFont(); // 创建字体样式
	headerFont.setColor(IndexedColors.BLACK.index);//字体颜色
	headerFont.setBold(true); //字体加粗
	headerFont.setFontName("黑体"); // 设置字体类型
	headerFont.setFontHeightInPoints((short) 20); // 设置字体大小
	cellStyle.setFont(headerFont); // 为标题样式设置字体样式
	//设置边框
	setBorder(cellStyle, BorderStyle.THICK, new XSSFColor(new java.awt.Color(47, 117, 181,1)));
	return cellStyle;
}

设置表格边框

private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }

浏览器下载表格

private static void setBrowser(HttpServletResponse response, XSSFWorkbook workbook, String fileName) {
        try {
        	//application/vnd.ms-excel
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
            response.flushBuffer();
            OutputStream out = response.getOutputStream();
            workbook.write(out);// 将数据写出去
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

插入图片

 /**
     * @Title: @Description: excel生成图片工具 @param @return @throws
     * @param ast：图左上角第几行
     * @param bst：图左上角第几列
     * @param aend：图右下角第几行
     * @param bend：图右下角第几列
     */
 public void setPhoto(XSSFWorkbook workbook, XSSFSheet sheet, int ast, int bst, int aend, int bend, URL url) {
    XSSFDrawing patriarch = sheet.createDrawingPatriarch();
    BufferedImage bufferImg = null;
    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

    try {
        bufferImg = ImageIO.read(url);
        ImageIO.write(bufferImg, "jpg", byteArrayOut);
        // anchor主要用于设置图片的属性
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) bst, ast, (short) bend, aend);
        anchor.setAnchorType(3);
        // 插入图片
        patriarch.createPicture(anchor,
                workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
    } catch (Exception e) {
        e.printStackTrace();
    }
}

前端excel导出

https://blog.youkuaiyun.com/L_ss01/article/details/126243106