当前位置：首页 > news >正文

【工具】Java Excel转图片

news 来源：原创 2024/9/23 7:20:54

【工具】Java Excel转图片

package com.yj.luban.modules.office.excel;import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.imageio.ImageIO;
import java.awt.Color;
import java.awt.Font;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;public class ExcelToImg {public static void main(String[] args) throws IOException {// Excel 文件路径String excelFilePath = "D:\\WORK\\workspace_tools\\Office\\excelToImg\\工时.xlsx";FileInputStream excelFile = new FileInputStream(new File(excelFilePath));// 创建 Workbook 对象Workbook workbook = new XSSFWorkbook(excelFile);Sheet sheet = workbook.getSheetAt(0);  // 获取第一个工作表// 创建一个临时的 BufferedImage 用于测量文本宽度BufferedImage tempImage = new BufferedImage(1, 1, BufferedImage.TYPE_INT_RGB);Graphics2D tempGraphics = tempImage.createGraphics();Font font = new Font("Arial", Font.PLAIN, 12);tempGraphics.setFont(font);FontMetrics fontMetrics = tempGraphics.getFontMetrics();// 动态计算每列的宽度int totalColumns = getMaxColumns(sheet);int[] columnWidths = new int[totalColumns];int rowHeight = fontMetrics.getHeight() + 10;  // 行高根据字体高度动态调整// 遍历所有单元格内容，计算最大列宽for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) continue;for (int colIndex = 0; colIndex < totalColumns; colIndex++) {Cell cell = row.getCell(colIndex);if (cell != null) {String cellValue = cell.toString();int textWidth = fontMetrics.stringWidth(cellValue) + 10;  // 加 10 像素边距columnWidths[colIndex] = Math.max(columnWidths[colIndex], textWidth);} else {columnWidths[colIndex] = Math.max(columnWidths[colIndex], 100);  // 设置默认最小宽度}}}// 计算图像总宽度和总高度int imageWidth = 50;  // 初始边距for (int colWidth : columnWidths) {imageWidth += colWidth;}int imageHeight = (sheet.getLastRowNum() + 1) * rowHeight + 100;  // 加上顶部和底部边距// 创建最终的 BufferedImageBufferedImage image = new BufferedImage(imageWidth, imageHeight, BufferedImage.TYPE_INT_RGB);Graphics2D graphics = image.createGraphics();// 设置白色背景graphics.setColor(Color.WHITE);graphics.fillRect(0, 0, imageWidth, imageHeight);// 设置字体graphics.setColor(Color.BLACK);graphics.setFont(font);// 起始坐标int startX = 50;int startY = 50;// 绘制每个单元格的内容和样式for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) continue;int x = startX;for (int colIndex = 0; colIndex < totalColumns; colIndex++) {Cell cell = row.getCell(colIndex);String cellValue = (cell != null) ? cell.toString() : "";// 绘制单元格内容graphics.drawString(cellValue, x + 5, startY + rowIndex * rowHeight + rowHeight / 2);// 绘制单元格边框if (cell != null) {CellStyle cellStyle = cell.getCellStyle();drawCellBorders(graphics, x, startY + rowIndex * rowHeight, columnWidths[colIndex], rowHeight, cellStyle);}// 移动到下一个单元格的位置x += columnWidths[colIndex];}}// 释放资源graphics.dispose();workbook.close();tempGraphics.dispose();// 保存图片ImageIO.write(image, "png", new File("D:\\WORK\\workspace_tools\\Office\\excelToImg\\a.png"));System.out.println("Excel 样式和边框转换为图片成功！");}// 获取最大列数private static int getMaxColumns(Sheet sheet) {int maxColumns = 0;for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row != null && row.getLastCellNum() > maxColumns) {maxColumns = row.getLastCellNum();}}return maxColumns;}// 绘制单元格边框private static void drawCellBorders(Graphics2D graphics, int x, int y, int width, int height, CellStyle style) {// 设置边框颜色为黑色graphics.setColor(Color.BLACK);// 绘制顶部边框if (style.getBorderTop() != BorderStyle.NONE) {graphics.drawLine(x, y, x + width, y);}// 绘制底部边框if (style.getBorderBottom() != BorderStyle.NONE) {graphics.drawLine(x, y + height, x + width, y + height);}// 绘制左侧边框if (style.getBorderLeft() != BorderStyle.NONE) {graphics.drawLine(x, y, x, y + height);}// 绘制右侧边框if (style.getBorderRight() != BorderStyle.NONE) {graphics.drawLine(x + width, y, x + width, y + height);}}
}

设置指定字体

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;public class ExcelToStyledImageWithBorders {public static void main(String[] args) throws IOException {// Excel 文件路径String excelFilePath = "example.xlsx";FileInputStream excelFile = new FileInputStream(new File(excelFilePath));// 创建 Workbook 对象Workbook workbook = new XSSFWorkbook(excelFile);Sheet sheet = workbook.getSheetAt(0);  // 获取第一个工作表// 创建一个临时的 BufferedImage 用于测量文本宽度BufferedImage tempImage = new BufferedImage(1, 1, BufferedImage.TYPE_INT_RGB);Graphics2D tempGraphics = tempImage.createGraphics();// 使用 SimSun 字体，支持中文字符Font font = new Font("SimSun", Font.PLAIN, 12);tempGraphics.setFont(font);FontMetrics fontMetrics = tempGraphics.getFontMetrics();// 动态计算每列的宽度int totalColumns = getMaxColumns(sheet);int[] columnWidths = new int[totalColumns];int rowHeight = fontMetrics.getHeight() + 10;  // 行高根据字体高度动态调整// 遍历所有单元格内容，计算最大列宽for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) continue;for (int colIndex = 0; colIndex < totalColumns; colIndex++) {Cell cell = row.getCell(colIndex);if (cell != null) {String cellValue = cell.toString();int textWidth = fontMetrics.stringWidth(cellValue) + 10;  // 加 10 像素边距columnWidths[colIndex] = Math.max(columnWidths[colIndex], textWidth);} else {columnWidths[colIndex] = Math.max(columnWidths[colIndex], 100);  // 设置默认最小宽度}}}// 计算图像总宽度和总高度int imageWidth = 50;  // 初始边距for (int colWidth : columnWidths) {imageWidth += colWidth;}int imageHeight = (sheet.getLastRowNum() + 1) * rowHeight + 100;  // 加上顶部和底部边距// 创建最终的 BufferedImageBufferedImage image = new BufferedImage(imageWidth, imageHeight, BufferedImage.TYPE_INT_RGB);Graphics2D graphics = image.createGraphics();// 设置白色背景graphics.setColor(Color.WHITE);graphics.fillRect(0, 0, imageWidth, imageHeight);// 设置字体，确保支持中文graphics.setFont(font);graphics.setColor(Color.BLACK);// 起始坐标int startX = 50;int startY = 50;// 绘制每个单元格的内容和样式for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) continue;int x = startX;for (int colIndex = 0; colIndex < totalColumns; colIndex++) {Cell cell = row.getCell(colIndex);String cellValue = (cell != null) ? cell.toString() : "";// 绘制单元格内容graphics.drawString(cellValue, x + 5, startY + rowIndex * rowHeight + rowHeight / 2);// 绘制单元格边框if (cell != null) {CellStyle cellStyle = cell.getCellStyle();drawCellBorders(graphics, x, startY + rowIndex * rowHeight, columnWidths[colIndex], rowHeight, cellStyle);}// 移动到下一个单元格的位置x += columnWidths[colIndex];}}// 释放资源graphics.dispose();workbook.close();tempGraphics.dispose();// 保存图片ImageIO.write(image, "png", new File("excel_styled_with_borders_image.png"));System.out.println("Excel 样式和边框转换为图片成功！");}// 获取最大列数private static int getMaxColumns(Sheet sheet) {int maxColumns = 0;for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row != null && row.getLastCellNum() > maxColumns) {maxColumns = row.getLastCellNum();}}return maxColumns;}// 绘制单元格边框private static void drawCellBorders(Graphics2D graphics, int x, int y, int width, int height, CellStyle style) {// 设置边框颜色为黑色graphics.setColor(Color.BLACK);// 绘制顶部边框if (style.getBorderTop() != BorderStyle.NONE) {graphics.drawLine(x, y, x + width, y);}// 绘制底部边框if (style.getBorderBottom() != BorderStyle.NONE) {graphics.drawLine(x, y + height, x + width, y + height);}// 绘制左侧边框if (style.getBorderLeft() != BorderStyle.NONE) {graphics.drawLine(x, y, x, y + height);}// 绘制右侧边框if (style.getBorderRight() != BorderStyle.NONE) {graphics.drawLine(x + width, y, x + width, y + height);}}
}

支持公式固定了一个字体

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;public class ExcelToStyledImageWithBordersAndFormula {public static void main(String[] args) throws IOException {// Excel 文件路径String excelFilePath = "example.xlsx";FileInputStream excelFile = new FileInputStream(new File(excelFilePath));// 创建 Workbook 对象Workbook workbook = new XSSFWorkbook(excelFile);Sheet sheet = workbook.getSheetAt(0);  // 获取第一个工作表// 创建 FormulaEvaluator 对象来解析公式FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();// 创建一个临时的 BufferedImage 用于测量文本宽度BufferedImage tempImage = new BufferedImage(1, 1, BufferedImage.TYPE_INT_RGB);Graphics2D tempGraphics = tempImage.createGraphics();// 使用 SimSun 字体，支持中文字符Font font = new Font("SimSun", Font.PLAIN, 12);tempGraphics.setFont(font);FontMetrics fontMetrics = tempGraphics.getFontMetrics();// 动态计算每列的宽度int totalColumns = getMaxColumns(sheet);int[] columnWidths = new int[totalColumns];int rowHeight = fontMetrics.getHeight() + 10;  // 行高根据字体高度动态调整// 遍历所有单元格内容，计算最大列宽for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) continue;for (int colIndex = 0; colIndex < totalColumns; colIndex++) {Cell cell = row.getCell(colIndex);if (cell != null) {String cellValue = getCellValue(cell, formulaEvaluator);  // 获取单元格值，包含公式解析int textWidth = fontMetrics.stringWidth(cellValue) + 20;  // 加 20 像素边距columnWidths[colIndex] = Math.max(columnWidths[colIndex], textWidth);} else {columnWidths[colIndex] = Math.max(columnWidths[colIndex], 100);  // 设置默认最小宽度}}}// 计算图像总宽度和总高度int imageWidth = 100;  // 初始边距，增加更多的边距以防止截断for (int colWidth : columnWidths) {imageWidth += colWidth;}int imageHeight = (sheet.getLastRowNum() + 1) * rowHeight + 100;  // 加上顶部和底部边距// 创建最终的 BufferedImageBufferedImage image = new BufferedImage(imageWidth, imageHeight, BufferedImage.TYPE_INT_RGB);Graphics2D graphics = image.createGraphics();// 设置白色背景graphics.setColor(Color.WHITE);graphics.fillRect(0, 0, imageWidth, imageHeight);// 设置字体，确保支持中文graphics.setFont(font);graphics.setColor(Color.BLACK);// 起始坐标int startX = 50;int startY = 50;// 绘制每个单元格的内容和样式for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) continue;int x = startX;for (int colIndex = 0; colIndex < totalColumns; colIndex++) {Cell cell = row.getCell(colIndex);String cellValue = (cell != null) ? getCellValue(cell, formulaEvaluator) : "";// 绘制单元格内容graphics.drawString(cellValue, x + 5, startY + rowIndex * rowHeight + rowHeight / 2);// 绘制单元格边框if (cell != null) {CellStyle cellStyle = cell.getCellStyle();drawCellBorders(graphics, x, startY + rowIndex * rowHeight, columnWidths[colIndex], rowHeight, cellStyle);}// 移动到下一个单元格的位置x += columnWidths[colIndex];}}// 释放资源graphics.dispose();workbook.close();tempGraphics.dispose();// 保存图片ImageIO.write(image, "png", new File("excel_styled_with_borders_and_formula_image.png"));System.out.println("Excel 样式和边框、公式转换为图片成功！");}// 获取单元格值，并解析公式private static String getCellValue(Cell cell, FormulaEvaluator formulaEvaluator) {switch (cell.getCellType()) {case STRING:return cell.getStringCellValue();case NUMERIC:return String.valueOf(cell.getNumericCellValue());case BOOLEAN:return String.valueOf(cell.getBooleanCellValue());case FORMULA:// 使用 FormulaEvaluator 解析公式CellValue evaluatedValue = formulaEvaluator.evaluate(cell);switch (evaluatedValue.getCellType()) {case STRING:return evaluatedValue.getStringValue();case NUMERIC:return String.valueOf(evaluatedValue.getNumberValue());case BOOLEAN:return String.valueOf(evaluatedValue.getBooleanValue());default:return " ";}default:return " ";}}// 获取最大列数private static int getMaxColumns(Sheet sheet) {int maxColumns = 0;for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row != null && row.getLastCellNum() > maxColumns) {maxColumns = row.getLastCellNum();}}return maxColumns;}// 绘制单元格边框private static void drawCellBorders(Graphics2D graphics, int x, int y, int width, int height, CellStyle style) {// 设置边框颜色为黑色graphics.setColor(Color.BLACK);// 绘制顶部边框if (style.getBorderTop() != BorderStyle.NONE) {graphics.drawLine(x, y, x + width, y);}// 绘制底部边框if (style.getBorderBottom() != BorderStyle.NONE) {graphics.drawLine(x, y + height, x + width, y + height);}// 绘制左侧边框if (style.getBorderLeft() != BorderStyle.NONE) {graphics.drawLine(x, y, x, y + height);}// 绘制右侧边框if (style.getBorderRight() != BorderStyle.NONE) {graphics.drawLine(x + width, y, x + width, y + height);}}
}