读取Excel内容生成PNG图片
本文转载自 心淡人也懒 的博客
https://blog.csdn.net/u014730287/article/details/80280480#commentBox
另外这位博主还参考了 丁丁-全栈工程师的博客
https://www.cnblogs.com/newflydd/p/4912662.html
首先给两位大佬 点赞 交叉点赞 托马斯回旋点赞
本人略微修改，具体修改文末有说明。
1.需要的jar包
<!-- 读取Excel文件内容 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
2.需要的pojo类
Grid
import java.awt.Color;
import java.awt.Font;
import java.io.Serializable;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFColor;
public class Grid implements Serializable{
private static final long serialVersionUID = 1L;
private boolean show;
private int row; // 对应Excel中的row,也可以理解为cells[i][j]的i
private int col; // 对应Excel中的col,也可以理解为cells[i][j]的j
private int x; // x坐标
private int y; // y坐标
private int width;
private int height;
private String text;
private Font font;
//= new Font("微软雅黑", Font.PLAIN, 12);
private Color bgColor = null;
private Color ftColor = null;
public Grid() {
super();
}
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public int getCol() {
return col;
}
public void setCol(int col) {
this.col = col;
}
public int getX() {
return x;
}
public void setX(int x) {
this.x = x;
}
public int getY() {
return y;
}
public void setY(int y) {
this.y = y;
}
public boolean isShow() {
return show;
}
public void setShow(boolean show) {
this.show = show;
}
public int getWidth() {
return width;
}
public void setWidth(int width) {
this.width = width;
}
public int getHeight() {
return height;
}
public void setHeight(int height) {
this.height = height;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
public Color getBgColor() {
return bgColor;
}
/**
* 将poi.ss.usermodel.Color 转换成 java.awt.Color
* <a href="http://home.cnblogs.com/u/309701/" target="_blank">@param</a>
* color
*/
public void setBgColor(org.apache.poi.ss.usermodel.Color color) {
this.bgColor = poiColor2awtColor(color);
}
public void setBgColor(java.awt.Color color) {
this.bgColor = color;
}
public Color getFtColor() {
return ftColor;
}
public void setFtColor(org.apache.poi.ss.usermodel.Color color) {
this.ftColor = poiColor2awtColor(color);
}
public Font getFont() {
return font;
}
public void setFont(org.apache.poi.ss.usermodel.Font font) {
if (font != null) {
this.font = new java.awt.Font(font.getFontName(), Font.BOLD, font.getFontHeight() / 20 + 2);
}
}
private java.awt.Color poiColor2awtColor(org.apache.poi.ss.usermodel.Color color) {
Color awtColor = null;
if (color instanceof XSSFColor) { // .xlsx
XSSFColor xc = (XSSFColor) color;
String rgbHex = xc.getARGBHex();
if (rgbHex != null) {
awtColor = new Color(Integer.parseInt(rgbHex.substring(2), 16));
}
} else if (color instanceof HSSFColor) { // .xls
HSSFColor hc = (HSSFColor) color;
short[] s = hc.getTriplet();
if (s != null) {
awtColor = new Color(s[0], s[1], s[2]);
}
}
return awtColor;
}
@Override
public String toString() {
return "Grid [show=" + show + ", row=" + row + ", col=" + col + ", x="
+ x + ", y=" + y + ", width=" + width + ", height=" + height
+ ", text=" + text + ", font=" + font + ", bgColor=" + bgColor
+ ", ftColor=" + ftColor + "]";
}
}
UserCell
import java.awt.Color;
import java.io.Serializable;
import org.apache.poi.ss.usermodel.Cell;
public class UserCell implements Serializable{
private static final long serialVersionUID = 1L;
private Cell cell;
private int row;
private int col;
private boolean show;
private String text="";
private Color color=null;
public UserCell() {
super();
}
public Cell getCell() {
return cell;
}
public void setCell(Cell cell) {
this.cell = cell;
}
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public int getCol() {
return col;
}
public void setCol(int col) {
this.col = col;
}
public boolean isShow() {
return show;
}
public void setShow(boolean show) {
this.show = show;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
public Color getColor() {
return color;
}
public void setColor(Color color) {
this.color = color;
}
@Override
public String toString() {
return "UserCell [cell=" + cell + ", row=" + row + ", col=" + col
+ ", show=" + show + ", text=" + text + ", color=" + color
+ "]";
}
}
3.核心代码
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 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.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.km.pojo.Grid;
import com.km.pojo.UserCell;
@Controller
public class A_Controller {
// getImageCode
@RequestMapping("/ss")
public void ss() {
//Excel2010或更高版本
String path = "C:\\Users\\Jakieenchan\\Desktop\\aa.xlsx";
//Excel2007或更低版本
//String path = "C:\\Users\\Jakieenchan\\Desktop\\bb.xls";
test(path);
}
public void test(String path) {
// 设置基本参数
int sheetNum = 0;// 读取表格第几页 ->0才是第一页
int imageWidth = 0;// 图片宽度
int imageHeight = 0;// 图片高度
// 创建字节输入流
InputStream inputStream;
try {
inputStream = new FileInputStream(path);
// 创建workbook、sheet对象、存储内容集合
Workbook workbook;
Sheet sheet;
List<List<String>> excelList;
// 用于判断单元格背景颜色设置
int bgColorFlag = 1;
if (path.split("\\.")[1].equals("xlsx")) {
// 上传的excel是2010以及更高版本
// 初始化workbook 对象
workbook = new XSSFWorkbook(inputStream);
// 读取xlsx文件内容
excelList = readXlsx(path, sheetNum);
} else {
// 上传的excel是2007以及更低版本
// 初始化workbook 对象
workbook = new HSSFWorkbook(inputStream);
// 读取xlsx文件内容
excelList = readXls(path, sheetNum);
// excel版本过低，无法正确显示原单元格背景颜色，故flag设置为0，使用白色作为背景颜色
bgColorFlag = 0;
}
// 初始化sheet对象
sheet = workbook.getSheetAt(sheetNum);
// 获取整个sheet中合并单元格组合的集合
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)); // 如果该单元格是隐藏的，则置宽度为0
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()); // 如果该单元格是隐藏的，则置高度为0
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:
//判断单元格内容 是否是 数值较大的数字 （即是否用了E表示）
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.dispose();
ImageIO.write(image, "png", new File("D:\\Test\\aa.png"));
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 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;
}
// 读取xlsx文件内容
public List<List<String>> readXlsx(String path, int pageNum) {
//创建集合，存储单元格内容
List<List<String>> resultList = new ArrayList<List<String>>();
// 读取Excel的准备
InputStream in;
XSSFWorkbook xssfWorkbook;
try {
in = new FileInputStream(path);
xssfWorkbook = new XSSFWorkbook(in);
// 遍历每一页，并循环处理当前页
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(pageNum);// 获取当前页
// 开始读取Excel内容
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 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;
}
}
注意
- 图片生成位置在D:\Test\aa.png（如果没有此文件夹，请提前创建）
修改之处
- 纠正了原博客的循环控制错误，使得程序运行中不再出现空指针异常
- 读取Excel文件内容后关闭了流
- 完善了对Excel2007以及更低版本的内容解析
- 针对单元格内数字数值较大的情况，修改了解析方式，使得如1.4512514551E10之类的数字可以1.4512514551的形式绘制到图片上
- 修改中发现Excel2007（以及更低版本）无法用相同的方法获取背景颜色，修改后，对此类版本的单元格背景颜色默认为白色。Excel2010以及更高版本可以正常显示原来的单元格背景颜色
最后
以上代码仍然有修改空间，烦请各位大佬多多指教。
扫一扫
5626
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
