Java使用POI库对excel进行操作

news/2024/9/24 5:31:13/

excel转为图片

这个操作是要根据excel一行一行画出来的

java">package com.gxuwz.zjh.util;import java.awt.BasicStroke;
import java.awt.Color;
import java.awt.Font;
import java.awt.FontMetrics;
import java.awt.Graphics2D;
import java.awt.RenderingHints;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import javax.imageio.ImageIO;
import com.gxuwz.zjh.entity.Grid;
import com.gxuwz.zjh.entity.UserCell;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;/*** @author FangZenglin* @date 2023年10月13日16:38*/
@Component
public class ExcelZhuanTuPian {public  void toImage(String dizhipath,String mubiaopath){test(dizhipath,mubiaopath);}public static void test(String path,String mubiaopath)   {int sheetNum = 0;int imageWidth = 0;int imageHeight = 0;InputStream inputStream;try {inputStream = new FileInputStream(path);Workbook workbook;Sheet sheet;List<List<String>> excelList;int bgColorFlag = 1;if (path.split("\\.")[1].equals("xlsx")) {workbook = new XSSFWorkbook(inputStream);excelList = readXlsx(path, sheetNum);} else {workbook = new HSSFWorkbook(inputStream);excelList = readXls(path, sheetNum);bgColorFlag = 0;}sheet = workbook.getSheetAt(sheetNum);List<CellRangeAddress> rangeAddress = sheet.getMergedRegions();int totalRow = excelList.size() + 1;int totalCol = excelList.get(0).size();UserCell[][] cells = new UserCell[totalRow + 1][totalCol + 1];int[] rowPixPos = new int[totalRow + 1];rowPixPos[0] = 0;int[] colPixPos = new int[totalCol + 1];colPixPos[0] = 0;for (int i = 0; i < totalRow - 1; i++) {for (int j = 0; j < totalCol; j++) {cells[i][j] = new UserCell();cells[i][j].setCell(sheet.getRow(i).getCell(j));cells[i][j].setRow(i);cells[i][j].setCol(j);boolean ifShow = !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight());cells[i][j].setShow(ifShow);float widthPix = !ifShow ? 0: (sheet.getColumnWidthInPixels(j));if (i == 0) {imageWidth += widthPix;}colPixPos[j + 1] = (int) (widthPix * 1.15 + colPixPos[j]);}boolean ifShow = (i >= 0);ifShow = ifShow && !sheet.getRow(i).getZeroHeight();float heightPoint = !ifShow ? 0 : (sheet.getRow(i).getHeightInPoints());imageHeight += heightPoint;rowPixPos[i + 1] = (int) (heightPoint * 96 / 72) + rowPixPos[i];}imageHeight = imageHeight * 96 / 72;imageWidth = imageWidth * 115 / 100;List<Grid> grids = new ArrayList<Grid>();for (int i = 0; i < totalRow - 1; i++) {for (int j = 0; j < totalCol; j++) {Grid grid = new Grid();grid.setX(colPixPos[j]);grid.setY(rowPixPos[i]);grid.setWidth(colPixPos[j + 1] - colPixPos[j]);grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);grid.setRow(cells[i][j].getRow());grid.setCol(cells[i][j].getCol());grid.setShow(cells[i][j].isShow());int[] isInMergedStatus = isInMerged(grid.getRow(),grid.getCol(), rangeAddress);if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {continue;} else if (isInMergedStatus[0] != -1&& isInMergedStatus[1] != -1) {int lastRowPos = isInMergedStatus[0] > totalRow - 1 ? totalRow - 1 : isInMergedStatus[0];int lastColPos = isInMergedStatus[1] > totalCol - 1 ? totalCol - 1 : isInMergedStatus[1];grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);}Cell cell = cells[i][j].getCell();if (cell != null) {CellStyle cs = cell.getCellStyle();grid.setBgColor(cs.getFillForegroundColorColor());org.apache.poi.ss.usermodel.Font font = workbook.getFontAt(cs.getFontIndex());grid.setFont(font);grid.setFtColor(cs.getFillBackgroundColorColor());String strCell;CellType cellType = cell.getCellTypeEnum();switch (cellType) {case STRING:strCell = cell.getStringCellValue();break;case NUMERIC:String str = String.valueOf(cell.getNumericCellValue());if(str.contains("E")){String LeftEStr = str.toString().split("E")[0];strCell = LeftEStr.split("\\.")[0]+LeftEStr.split("\\.")[1];}else{strCell = String.valueOf(cell.getNumericCellValue());}break;case BLANK:strCell = "";break;case FORMULA:try {strCell = String.valueOf(cell.getNumericCellValue());} catch (IllegalStateException e) {strCell = String.valueOf(cell.getRichStringCellValue());}break;default:strCell = "";break;}if (cell.getCellStyle().getDataFormatString().contains("0.00%")) {try {double dbCell = Double.valueOf(strCell);strCell = new DecimalFormat("0.00").format(dbCell * 100) + "%";} catch (NumberFormatException e) {}}grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);}grids.add(grid);}}BufferedImage image = new BufferedImage(imageWidth, imageHeight,BufferedImage.TYPE_INT_RGB);Graphics2D g2d = image.createGraphics();g2d.setColor(Color.white);g2d.fillRect(0, 0, imageWidth, imageHeight);g2d.setRenderingHint(RenderingHints.KEY_ANTIALIASING,RenderingHints.VALUE_ANTIALIAS_ON);g2d.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING,RenderingHints.VALUE_TEXT_ANTIALIAS_ON);g2d.setRenderingHint(RenderingHints.KEY_STROKE_CONTROL,RenderingHints.VALUE_STROKE_NORMALIZE);g2d.setRenderingHint(RenderingHints.KEY_TEXT_LCD_CONTRAST, 140);g2d.setRenderingHint(RenderingHints.KEY_FRACTIONALMETRICS,RenderingHints.VALUE_FRACTIONALMETRICS_ON);g2d.setRenderingHint(RenderingHints.KEY_RENDERING,RenderingHints.VALUE_RENDER_QUALITY);for (Grid g : grids) {if (!g.isShow()) {continue;}// 绘制背景色if (bgColorFlag == 1) {// Excel2010以及更高-->使用原单元格背景色g2d.setColor(g.getBgColor() == null ? Color.white : g.getBgColor());} else {// Excel2007以及更低-->使用白色作为背景色g2d.setColor(Color.white);}g2d.fillRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());g2d.setColor(Color.black);g2d.setStroke(new BasicStroke(1));g2d.drawRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());g2d.setColor(g.getFtColor());Font font = g.getFont();if (font == null) {continue;}FontMetrics fm = g2d.getFontMetrics(font);int strWidth = fm.stringWidth(g.getText());g2d.setFont(font);g2d.drawString(g.getText(),g.getX() + (g.getWidth() - strWidth) / 2,g.getY() + (g.getHeight() - font.getSize()) / 2+ font.getSize());}// 表格最后一行有可能不显示，手动画上一行g2d.drawLine(0, imageHeight - 1, imageWidth - 4, imageHeight - 1);g2d.drawLine(imageWidth-1 , 0, imageWidth-1 , imageHeight-1);g2d.dispose();ImageIO.write(image, "png", new File(mubiaopath));workbook.close();} catch (FileNotFoundException e1) {e1.printStackTrace();} catch (IOException e) {e.printStackTrace();}System.out.println("----Output to PNG file Success!----");}/*** 判断Excel中的单元格是否为合并单元格** @param row* @param col* @param rangeAddress* @return 如果不是合并单元格返回{-1,-1},如果是合并单元格并且是一个单元格返回{lastRow,lastCol},*         如果是合并单元格并且不是第一个格子返回{0,0}*/private static int[] isInMerged(int row, int col,List<CellRangeAddress> rangeAddress) {int[] isInMergedStatus = { -1, -1 };for (CellRangeAddress cra : rangeAddress) {if (row == cra.getFirstRow() && col == cra.getFirstColumn()) {isInMergedStatus[0] = cra.getLastRow();isInMergedStatus[1] = cra.getLastColumn();return isInMergedStatus;}if (row >= cra.getFirstRow() && row <= cra.getLastRow()) {if (col >= cra.getFirstColumn() && col <= cra.getLastColumn()) {isInMergedStatus[0] = 0;isInMergedStatus[1] = 0;return isInMergedStatus;}}}return isInMergedStatus;}public static List<List<String>> readXlsx(String path, int pageNum)   {List<List<String>> resultList = new ArrayList<List<String>>();InputStream in;XSSFWorkbook xssfWorkbook;try {in = new FileInputStream(path);xssfWorkbook = new XSSFWorkbook(in);XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(pageNum);if (xssfSheet != null) {for (int rowNum = 0; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {XSSFRow xssfRow = xssfSheet.getRow(rowNum);int minColIX = xssfRow.getFirstCellNum();int maxColIX = xssfRow.getLastCellNum();List<String> rowList = new ArrayList<String>();for (int colIX = minColIX; colIX < maxColIX; colIX++) {XSSFCell cell = xssfRow.getCell(colIX);if (cell != null) {rowList.add(cell.toString());}}resultList.add(rowList);}}xssfWorkbook.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return resultList;}// 读取xls文件内容public static List<List<String>> readXls(String path, int num)  {List<List<String>> resultList = new ArrayList<List<String>>();InputStream in;HSSFWorkbook hssfWorkbook;try {in = new FileInputStream(path);hssfWorkbook = new HSSFWorkbook(in);HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(num);// 获取当前页if (hssfSheet != null) {for (int rowNum = 0; rowNum < hssfSheet.getLastRowNum() + 1; rowNum++) {HSSFRow hssfRow = hssfSheet.getRow(rowNum);// 获取行数据int minColIX = hssfRow.getFirstCellNum();// 第一行数据int maxColIX = hssfRow.getLastCellNum();// 总行数List<String> rowList = new ArrayList<String>();// 遍历该行，处理该行数据for (int colIX = minColIX; colIX < maxColIX; colIX++) {HSSFCell cell = hssfRow.getCell(colIX);// 获取单元格if (cell != null) {rowList.add(cell.toString());}}resultList.add(rowList);}}hssfWorkbook.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return resultList;}
}

图片合并,模拟插入电子公章

java">package com.gxuwz.zjh.util;import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import javax.imageio.ImageIO;/*** @author FangZenglin* @date 2023年10月13日15:30*/
@Component
@Slf4j
public class ChaGongZhang {public static String s = "i61PdAClDxIs/8/6iSGOQQ==";public  void chagongzhang_suodui(String path_biao,String path_gongzhang,String path_mubiao) {try {BufferedImage image1 = ImageIO.read(new File(path_biao));BufferedImage image2 = ImageIO.read(new File(path_gongzhang));// 加载公章//    BufferedImage image3 = ImageIO.read(new File(path_gongzhang.replace("1","2")));int maxWidth = Math.max(image1.getWidth(), image2.getWidth());int maxHeight = Math.max(image1.getHeight(), image2.getHeight());BufferedImage combinedImage = new BufferedImage(maxWidth, maxHeight, BufferedImage.TYPE_INT_ARGB);Graphics2D g2d = combinedImage.createGraphics();g2d.drawImage(image1, 0, 0, null);// 计算第二张图片的位置int x = 150;int y = 365;//所队部门意见/***int x = 480;*int y = 350;//人训部门意见*//***int x = 780;*int y = 350;//单位主管领导*//***int x = 1250;*int y = 350;//单位主要领导*/g2d.drawImage(image2, x, y, null);//   g2d.drawImage(image3, x, y, null); 公章插入ImageIO.write(combinedImage, "png", new File(path_mubiao));g2d.dispose();log.info("图片合并成功！");} catch (IOException e) {e.printStackTrace();}}public  void chagongzhang_renxun(String path_biao,String path_gongzhang,String path_mubiao) {try {// 加载第一张图片BufferedImage image1 = ImageIO.read(new File(path_biao));// 加载第二张图片BufferedImage image2 = ImageIO.read(new File(path_gongzhang));// 加载公章// BufferedImage image3 = ImageIO.read(new File(path_gongzhang.replace("1","2")));int maxWidth = Math.max(image1.getWidth(), image2.getWidth());int maxHeight = Math.max(image1.getHeight(), image2.getHeight());BufferedImage combinedImage = new BufferedImage(maxWidth, maxHeight, BufferedImage.TYPE_INT_ARGB);Graphics2D g2d = combinedImage.createGraphics();g2d.drawImage(image1, 0, 0, null);int x = 450;int y = 380;//人训部门意见/***int x = 780;*int y = 350;//单位主管领导*//***int x = 1250;*int y = 350;//单位主要领导*/g2d.drawImage(image2, x, y, null);//   g2d.drawImage(image3, x, y, null); 公章插入ImageIO.write(combinedImage, "png", new File(path_mubiao));g2d.dispose();log.info("图片合并成功！");} catch (IOException e) {e.printStackTrace();}}public  void chagongzhang_zhuguanlingdao(String path_biao,String path_gongzhang,String path_mubiao) {try {// 加载第一张图片BufferedImage image1 = ImageIO.read(new File(path_biao));// 加载第二张图片BufferedImage image2 = ImageIO.read(new File(path_gongzhang));// 加载公章//   BufferedImage image3 = ImageIO.read(new File(path_gongzhang.replace("1","2")));int maxWidth = Math.max(image1.getWidth(), image2.getWidth());int maxHeight = Math.max(image1.getHeight(), image2.getHeight());BufferedImage combinedImage = new BufferedImage(maxWidth, maxHeight, BufferedImage.TYPE_INT_ARGB);Graphics2D g2d = combinedImage.createGraphics();g2d.drawImage(image1, 0, 0, null);int x = 745;int y = 350;//单位主管领导/***int x = 1250;*int y = 350;//单位主要领导*/g2d.drawImage(image2, x, y, null);//   g2d.drawImage(image3, x, y, null); 公章插入ImageIO.write(combinedImage, "png", new File(path_mubiao));g2d.dispose();log.info("图片合并成功！");} catch (IOException e) {e.printStackTrace();}}public  void chagongzhang_zhuyaolingdao(String path_biao,String path_gongzhang,String path_mubiao) {try {// 加载第一张图片BufferedImage image1 = ImageIO.read(new File(path_biao));// 加载第二张图片BufferedImage image2 = ImageIO.read(new File(path_gongzhang));// 加载公章//    BufferedImage image3 = ImageIO.read(new File(path_gongzhang.replace("1","2")));int maxWidth = Math.max(image1.getWidth(), image2.getWidth());int maxHeight = Math.max(image1.getHeight(), image2.getHeight());BufferedImage combinedImage = new BufferedImage(maxWidth, maxHeight, BufferedImage.TYPE_INT_ARGB);Graphics2D g2d = combinedImage.createGraphics();g2d.drawImage(image1, 0, 0, null);int x = 1215;int y = 350;//单位主要领导g2d.drawImage(image2, x, y, null);//   g2d.drawImage(image3, x, y, null); 公章插入ImageIO.write(combinedImage, "png", new File(path_mubiao));g2d.dispose();log.info("图片合并成功！");} catch (IOException e) {e.printStackTrace();}}}

http://www.ppmy.cn/news/1449358.html

相关文章

【项目经验】Redis Sentinel从工程中下线并对业务迁移-(二)

【项目经验】Redis Sentinel从工程中下线并对业务迁移-(二)

在上篇文章【项目经验】Redis Sentinel从工程中下线并对业务迁移-进行中-CSDN博客有说到迁移的计划。最近一直按照计划进行迁移&#xff0c;期间遇到了不少问题。总结如下&#xff1a; 一、key未设置过期时间 redis基于内存存储&#xff0c;主要作用是缓存。当大量的key未设置…
阅读更多...
Linux下启动jenkins报错问题解决

Linux下启动jenkins报错问题解决

jenkins端口报错 java.io.IOException: Failed to start Jettyat winstone.Launcher.<init>(Launcher.java:209)at winstone.Launcher.main(Launcher.java:496)at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at java.base/jdk.int…
阅读更多...
Docker与Linux容器：“探索容器化技术的奥秘”

Docker与Linux容器：“探索容器化技术的奥秘”

目录 一、Docker概述 二、容器技术的起源&#xff1a; 三、Linux容器 四、Docker的出现 五、Docker容器特点&#xff1a; 六、Docker三大概念&#xff1a; 容器&#xff1a; 镜像&#xff1a; 仓库&#xff1a; 七、Docker容器常用命令 一、Docker概述 在云原生时代&…
阅读更多...
手动实现简易版RPC(三)

手动实现简易版RPC(三)

手动实现简易版RPC(三) 往期内容 手动实现简易版RPC&#xff08;一&#xff09;&#xff1a;RPC简介及系统架构 手动实现简易版RPC&#xff08;二&#xff09;&#xff1a;简单RPC框架实现 前言 接上两篇博客我们实现了最简易RPC框架&#xff0c;接下来的几期重点在简易版…
阅读更多...
抖音IP打造品牌规划流量运营方案推广计划书

抖音IP打造品牌规划流量运营方案推广计划书

【干货资料持续更新&#xff0c;以防走丢】 抖音IP打造品牌规划流量运营方案推广计划书 部分资料预览 资料部分是网络整理&#xff0c;仅供学习参考。 50页可编辑&#xff08;完整资料包含以下内容&#xff09; 目录 详细的抖音运营方案&#xff0c;帮助品牌在抖音平台上提升…
阅读更多...
长亭安全运营实习一面

长亭安全运营实习一面

这次面试感觉真的跟面试官日常聊天一样。 首先百年不变的就是自我介绍&#xff1b; 跟我讲了一下这个岗位大概是干什么的&#xff0c;看看我的预期是什么&#xff1f; 然后问了俩仨个问题&#xff0c;都很基础&#xff0c;将的sql注入&#xff0c;ssrf这些为什么会产生&#xf…
阅读更多...
保存钉钉群直播回放下载：直播回放下载步骤详解

保存钉钉群直播回放下载：直播回放下载步骤详解

今天&#xff0c;我们就来拨开云雾&#xff0c;揭开保存钉钉群直播回放的神秘面纱。教会你们如何下载钉钉群直播回放 首先用到的工具我全部打包好了&#xff0c;有需要的自己下载一下 钉钉群直播回放工具下载&#xff1a;https://pan.baidu.com/s/1WVMNGoKcTwR_NDpvFP2O2A?p…
阅读更多...
NFS服务器（linux-linux）

NFS服务器（linux-linux）

目录 简介 NFS背景介绍 生产应用场景 NFS工作原理 示例图 流程 NFS的使用 安装 配置文件 主配置文件分析 实验1 NFS账户映射 实验2&#xff1a; 实验3 autofs自动挂载服务 产生原因 安装 配置文件分析 实验4 实验5 简介 NFS背景介绍 NFS是一种古老的用于…
阅读更多...
最新文章

Copyright @ 2022~2023