Excel转图片

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357

package com.ruoyi.panel.excelToP;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import javax.imageio.ImageIO;
import java.awt.Color;
import java.awt.Font;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.*;
import java.util.List;

/**
 * width:pix=getColumnWidthInPixels*1.15
 * height:pix=getHeightInPoints*96/72
 * <p>
 * 本示例用来读取Excel报表文件，并力图将报表无差别转化成PNG图片
 * 使用POI读取Excel各项数据
 * 使用JAVA 2D绘制PNG
 * 本示例基本实现了常见Excel的所有样式输出，但Office2007以后的版本添加了条件样式功能，，所以无法实现
 * 今后可以通过关键字标注等方法，在Excel中需要加入条件样式的单元格用注解标明，使用JAVA计算得出样因为POI的API无法读取条件样式式再绘制出来
 * 中文乱码问题请查看excel中字体 在本地是否安装
 * 可以导出图片
 * <p>
 */
public class DrawFromExcel {

    public static void main(String[] args) throws IOException, InvalidFormatException {
        excelToImage("D:\\Desktop\\test\\20230131\\111.xlsx","D:\\Desktop\\test\\20230131");
        System.out.println(new Date());
    }

    /**
     * excel转图片
     *
     * @param excelUrl excel路径
     * @param path     图片存储路径
     * @throws IOException,InvalidFormatException 异常
     */
    public static void excelToImage(String excelUrl, String path) throws IOException, InvalidFormatException {
        Workbook wb = WorkbookFactory.create(new File(excelUrl));
        wb.close();
        // 获取每个Sheet表
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            // 图片宽度
            int imageWidth;
            // 图片高度
            int imageHeight;
            //第一个工作表
            Sheet sheet = wb.getSheetAt(sheetIndex);

            // 获取工作表是否存在图片
            Map<String, PictureData> maplist = null;
            if (excelUrl.endsWith(".xls")) {
                maplist = getPictures1((HSSFSheet) sheet);
            } else if (excelUrl.endsWith(".xlsx")) {
                maplist = getPictures2((XSSFSheet) sheet);
            }

            // 获取整个sheet中合并单元格组合的集合
            List<CellRangeAddress> rangeAddress = sheet.getMergedRegions();

            //检查区域内是否存在数据
            int rowSize = sheet.getPhysicalNumberOfRows();
            int colSize = sheet.getRow(0).getPhysicalNumberOfCells();
            if (rowSize == 0 || colSize == 0) {
                continue;
            }
            // 遍历需要扫描的区域
            UserCell[][] cells = new UserCell[rowSize][colSize];
            int[] rowPixPos = new int[rowSize + 1];
            rowPixPos[0] = 0;
            int[] colPixPos = new int[colSize + 1];
            colPixPos[0] = 0;

            float height = 0f;
            float width = 0f;
            for (int i = 0; i < rowSize; i++) {
                for (int j = 0; j < colSize; 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);
                    // 计算所求区域宽度 如果该单元格是隐藏的，则置宽度为0
                    float widthPix = !ifShow ? 0 : sheet.getColumnWidthInPixels(j);
                    if (i == 0) {
                        width += widthPix;
                    }
                    colPixPos[j + 1] = (int) (widthPix * 1.15 + colPixPos[j]);
                }
                // 计算所求区域高度 行序列不能隐藏
                boolean ifShow = !sheet.getRow(i).getZeroHeight();
                // 如果该单元格是隐藏的，则置高度为0
                float heightPoint = !ifShow ? 0 : sheet.getRow(i).getHeightInPoints();
                height += heightPoint;
                rowPixPos[i + 1] = (int) (heightPoint * 96 / 72) + rowPixPos[i];
            }
            imageHeight = (int) (height * 96 / 72);
            imageWidth = (int) (width * 115 / 100);

            List<Grid> grids = getGrids(wb, rangeAddress, rowSize, colSize, cells, rowPixPos, colPixPos, maplist);
            // 绘图
            draw(imageWidth, imageHeight, grids, path);
        }
    }

    /**
     * 根据表格制作网格
     *
     * @param wb           excel
     * @param rangeAddress 合并单元格组合
     * @param rowSize      行大小
     * @param colSize      列大小
     * @param cells        单元格
     * @param rowPixPos    需要扫描的行大小
     * @param colPixPos    需要扫描的列大小
     * @return 网格数据
     */
    private static List<Grid> getGrids(Workbook wb, List<CellRangeAddress> rangeAddress, int rowSize, int colSize, UserCell[][] cells, int[] rowPixPos, int[] colPixPos, Map<String, PictureData> dataMap) {
        List<Grid> grids = new ArrayList<>();
        for (int i = 0; i < rowSize; i++) {
            for (int j = 0; j < colSize; j++) {
                //当前表格
                Cell cell = cells[i][j].getCell();
                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 (cell == null || (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0)) {
                    continue;
                } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                    // 此单元格是合并单元格，并且属于第一个单元格，则需要调整网格大小
                    int lastRowPos = Math.min(isInMergedStatus[0], rowSize - 1);
                    int lastColPos = Math.min(isInMergedStatus[1], colSize - 1);
                    grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                    grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);
                }
                // 单元格背景颜色
                CellStyle cs = cell.getCellStyle();
                if (cs.getFillPattern() == FillPatternType.SOLID_FOREGROUND) {
                    grid.setBgColor(cell.getCellStyle().getFillForegroundColorColor());
                }
                // 设置字体
                org.apache.poi.ss.usermodel.Font font = wb.getFontAt(cs.getFontIndex());
                grid.setFont(font);

                // 设置字体前景色
                if (font instanceof XSSFFont) {
                    XSSFFont xf = (XSSFFont) font;
                    grid.setFtColor(xf.getXSSFColor());
                }
                // 设置文本
                String strCell;
                if (cell.getCellType() == CellType.NUMERIC) {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        strCell = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
                    } else {
                        NumberFormat nf = NumberFormat.getInstance();
                        strCell = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", "");
                    }
                } else if (cell.getCellType() == CellType.STRING) {
                    strCell = String.valueOf(cell.getStringCellValue());
                } else if (cell.getCellType() == CellType.BOOLEAN) {
                    strCell = String.valueOf(cell.getBooleanCellValue());
                } else if (cell.getCellType() == CellType.ERROR) {
                    strCell = "错误类型";
                } else {
                    strCell = "";
                }
                // 如果为空可能单元格是图片
                if ("".equals(strCell)) {
                    PictureData pictureData = dataMap.get(i + "-" + j);
                    if (pictureData != null) {
                        grid.setData(pictureData.getData());
                    }
                }

                if (cell.getCellStyle().getDataFormatString().contains("0.00%")) {
                    try {
                        double dbCell = Double.parseDouble(strCell);
                        strCell = new DecimalFormat("#.00").format(dbCell * 100) + "%";
                    } catch (NumberFormatException ignored) {
                    }
                }
                grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);
                grids.add(grid);
            }
        }
        return grids;
    }

    /**
     * 绘图
     *
     * @param imageWidth  图片宽度
     * @param imageHeight 图片高度
     * @param grids       绘制图片的内容
     */
    public static void draw(int imageWidth, int imageHeight, List<Grid> grids, String path) throws IOException {
        BufferedImage image = new BufferedImage(imageWidth, imageHeight, BufferedImage.TYPE_INT_RGB);
        Graphics2D g2d = image.createGraphics();
        // 平滑字体
        g2d.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_OFF);
        g2d.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_DEFAULT);
        g2d.setRenderingHint(RenderingHints.KEY_STROKE_CONTROL, RenderingHints.VALUE_STROKE_DEFAULT);
        g2d.setRenderingHint(RenderingHints.KEY_TEXT_LCD_CONTRAST, 140);
        g2d.setRenderingHint(RenderingHints.KEY_FRACTIONALMETRICS, RenderingHints.VALUE_FRACTIONALMETRICS_OFF);
        g2d.setRenderingHint(RenderingHints.KEY_RENDERING, RenderingHints.VALUE_RENDER_DEFAULT);
        g2d.setColor(Color.white);
        g2d.fillRect(0, 0, imageWidth, imageHeight);
        // 绘制表格
        for (Grid g : grids) {
            if (!g.isShow()) {
                continue;
            }
            // 绘制背景色
            g2d.setColor(g.getBgColor() == null ? Color.white : g.getBgColor());
            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();
            FontMetrics fm = g2d.getFontMetrics(font);
            // 获取将要绘制的文字宽度
            int strWidth = fm.stringWidth(g.getText());
            g2d.setFont(font);
            // 图片
            if (g.getData() != null && g.getData().length > 0) {
                InputStream inputStream = new ByteArrayInputStream(g.getData());
                BufferedImage bg = ImageIO.read(inputStream);
                g2d.drawImage(bg.getScaledInstance(g.getWidth(), g.getHeight(), Image.SCALE_DEFAULT),
                        g.getX(),
                        g.getY(), null);
            }
            // 文字
            else {
                g2d.drawString(g.getText(),
                        g.getX() + (g.getWidth() - strWidth) / 2,
                        g.getY() + (g.getHeight() - font.getSize()) / 2 + font.getSize());
            }

        }
        g2d.dispose();
        //生成图片
        String imgUrl = path + File.separator + System.currentTimeMillis() + ".png";
        ImageIO.write(image, "png", new File(imgUrl));
    }


    /**
     * 判断Excel中的单元格是否为合并单元格
     *
     * @param row          当前行号
     * @param col          当前列号
     * @param rangeAddress 整个sheet中合并单元格组合的集合
     * @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() && col >= cra.getFirstColumn() && col <= cra.getLastColumn()) {
                isInMergedStatus[0] = 0;
                isInMergedStatus[1] = 0;
                return isInMergedStatus;
            }
        }
        return isInMergedStatus;
    }

    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet -
     * @return -
     * Z
     */
    public static Map<String, PictureData> getPictures1(HSSFSheet sheet) {
        Map<String, PictureData> map = new HashMap<>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet -
     * @return -
     */
    public static Map<String, PictureData> getPictures2(XSSFSheet sheet) {
        Map<String, PictureData> map = new HashMap<>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    // 行号-列号
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }

}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167

package com.ruoyi.panel.excelToP;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFColor;

import java.awt.*;

public class Grid {
    /**
     * 是否显示
     */
    private boolean show;
    /**
     * 对应Excel中的row,也可以理解为cells[i][j]的i
     */
    private int row;
    /**
     * 对应Excel中的col,也可以理解为cells[i][j]的j
     */
    private int col;
    /**
     * x坐标
     */
    private int x;
    /**
     * y坐标
     */
    private int y;
    private int width;
    private int height;
    private String text;
    private byte[] data;
    /**
     * 字体 new Font("微软雅黑", Font.PLAIN, 12);
     */
    private Font font;
    private Color bgColor = null;
    private Color ftColor = null;

    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 byte[] getData() {
        return data;
    }

    public void setData(byte[] data) {
        this.data = data;
    }

    public Color getBgColor() {
        return bgColor;
    }

    /**
     * 将poi.ss.usermodel.Color 转换成  java.awt.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) {
        java.awt.Color awtColor = null;
        //.xlsx
        if (color instanceof XSSFColor) {
            XSSFColor xc = (XSSFColor) color;
            String rgbHex = xc.getARGBHex();
            if (rgbHex != null) {
                awtColor = new Color(Integer.parseInt(rgbHex.substring(2), 16));
            }
        }
        //.xls
        else if (color instanceof HSSFColor) {
            HSSFColor hc = (HSSFColor) color;
            short[] s = hc.getTriplet();
            if (s != null) {
                awtColor = new Color(s[0], s[1], s[2]);
            }
        }
        return awtColor;
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

package com.ruoyi.panel.excelToP;

import org.apache.poi.ss.usermodel.Cell;

import java.awt.*;

public class UserCell implements Comparable<UserCell> {
    private Cell cell;
    private int row;
    private int col;
    private boolean show;
    private String text = "";
    private Color color = null;

    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 int compareTo(UserCell uc) {
        try {
            double meDouble = Double.parseDouble(this.getText().replace("%", ""));
            double heDouble = Double.parseDouble(uc.getText().replace("%", ""));
            if (meDouble < heDouble) {
                return -1;
            } else if (meDouble > heDouble) {
                return 1;
            }
        } catch (Exception ignored) {
        }

        return 0;
    }
}