Java使用POI库对excel进行操作

embedded/2024/10/18 18:28:08/

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/embedded/28328.html

相关文章

GitHub介绍，GitHub如何订阅充值？

GitHub介绍，GitHub如何订阅充值？

一、GitHub介绍 GitHub是一个面向开源及私有软件项目的托管平台&#xff0c;因为只支持git 作为唯一的版本库格式进行托管&#xff0c;故名Github。 GitHub于2008年4月10日正式上线&#xff0c;除了git代码仓库托管及基本的Web管理界面以外&#xff0c;还提供了订阅、讨论组、…
阅读更多...
揭秘APP应用分发：一站式流程与优化 - 小猪APP分发平台(推广)

揭秘APP应用分发：一站式流程与优化 - 小猪APP分发平台(推广)

APP应用分发揭秘APP应用分发&#xff1a;一站式流程与优化 - 小猪APP分发平台的定义与流程 APP应用分发揭秘APP应用分发&#xff1a;一站式流程与优化 - 小猪APP分发平台&#xff0c;简单来说&#xff0c;就是将开发完成的APP通过一些特定的渠道发布到用户手中的过程。这个流程…
阅读更多...
CSS3 animation (动画) 属性

CSS3 animation (动画) 属性

keyframes规定动画 animation 所有动画属性的简写属性&#xff0c;除了animation-play-state属性。 animation-name 动画的名称 &#xff08;必须的&#xff09; animation-duration 完成一共用的时间&#xff0c;默认是0s &#xff08;必须的&#xff09; animation-timin…
阅读更多...
无人机+大载重+长航时：油电混动多旋翼无人机技术详解

无人机+大载重+长航时：油电混动多旋翼无人机技术详解

多旋翼无人机是一种具有三个及以上旋翼轴的特殊的无人驾驶旋翼飞行器。具有稳定性强、操控简单、勤务性高、价格便宜等优势&#xff0c;因此在市场上的应用非常广泛。此外&#xff0c;利用地面供电的绳系多旋翼通过电缆向多旋翼持续传输电能&#xff0c;可以大大提高多旋翼的空…
阅读更多...
【Jenkins】持续集成与交付 （七）：Gitlab添加组、创建用户、创建项目和源码上传到Gitlab仓库

【Jenkins】持续集成与交付 （七）：Gitlab添加组、创建用户、创建项目和源码上传到Gitlab仓库

🟣【Jenkins】持续集成与交付 （七）：Gitlab添加组、创建用户、创建项目和源码上传到Gitlab仓库 1、创建组2、创建用户3、将用户添加到组中4、在用户组中创建项目5、源码上传到Gitlab仓库5.1 初始化版本控制5.2 将文件添加到暂存区5.3 提交代码到本地仓库5.4 推送代码到 Git…
阅读更多...
缓存分享(1)——Guava Cache原理及最佳实践

缓存分享(1)——Guava Cache原理及最佳实践

Guava Cache原理及最佳实践 1. Guava Cache是什么1.1 简介1.2 核心功能1.3 适用场景 2. Guava Cache的使用2.1 创建LoadingCache缓存2.2 创建CallableCache缓存 缓存的种类有很多&#xff0c;需要根据不同的应用场景来选择不同的cache&#xff0c;比如分布式缓存如redis、memca…
阅读更多...
【氮化镓】GaN器件在航天器高可靠正向转换器中应用

【氮化镓】GaN器件在航天器高可靠正向转换器中应用

文章是发表在《IEEE Journal of Emerging and Selected Topics in Power Electronics》2022年10月第10卷第5期上的一篇关于GaN（氮化镓）器件在航天器高可靠性正向转换器中应用的研究。文章的作者是匹兹堡大学电气与计算机工程系的Aidan Phillips, Thomas Cook和Brandon M. Gra…
阅读更多...
C#身份查验接口、身份证文字识别接口、金融身份验证

C#身份查验接口、身份证文字识别接口、金融身份验证

针对金融领域远程自主开户的实名认证&#xff0c;翔云人工智能开放平台提出了有针对性的解决方案。翔云实名认证API其中包含了身份证实名认证、人脸识别、人证合一和银行卡实名认证&#xff0c;可快速识别提取用户身份信息&#xff0c;实时联网权威数据源进行用户身份的验证&am…
阅读更多...
最新文章

Copyright @ 2022~2023