Java exports a report in Excel as a picture format (c) in other implementations

The previous article introduced the robot robot implementation using Java, and then generated a PNG image of the data stream on the Clipboard

But after continuous testing by bloggers, on servers that rely entirely on remote desktops without a terminal display

The use of the method is not feasible, because once the remote Desktop, and then disconnect the desktop, the system is executed in the background of the program will be all cut to the black screen

So the blogger had to use another common way to generate PNG

This is to use the Apache Company POI tool to parse the entire Excel

Read every cell, data, format, and border of Excel

It needs to take into account the merging of cells, the hiding of rows and so on complex problems

Finally, use Java 2d to draw the data you read to a PNG image.

The first one is excel as it is

The following is a report drawn using Java 2D:

Paste the source code, which involves using a POI to read the cell background color, font foreground color and other color operations, or more complex.

Package com.newflyig.jpg;/** * width:pix=getcolumnwidthinpixels*1.15 * HEIGHT:PIX=GETHEIGHTINPOINTS*96/72 * * This example reads the Excel report file and tries to convert the report to a PNG image without distinction * using POI to read Excel data * Using Java 2D to draw PNG * TODO This example basically implements all the style output for common Excel. However, after the Office2007 version added conditional style functionality, because the POI API can not read the conditional style, it is not possible to implement * in the future by means of keyword tagging, in Excel, you need to add conditional styles in the cells with annotations, using Java calculations to draw out the style again * * Import Java.awt.basicstroke;import java.awt.color;import Java.awt.font;import Java.awt.fontmetrics;import Java.awt.graphics2d;import Java.awt.image.bufferedimage;import Java.io.file;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.ss.usermodel.cellstyle;import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.ss.usermodel.workbookfactory;import Org.apache.poi.ss.util.cellrangeaddress;import Org.apache.poi.xssf.usermodel.xssffont;import Sun.awt.sunhints;public class Drawfromexcel {public Static void Main (string[] args) throws Exception {//Given two initial values, mark out the export area, two rows and columns combination of cells int[] FromIndex = {0, 0};int[] Toindex = {17, };int ImageWidth = 0;int imageheight = 0; Workbook WB = workbookfactory.create (New File ("d:/2014 January campaign Report 140116.xlsx")); Sheet Sheet = Wb.getsheet ("test"); list<cellrangeaddress> rangeaddress = Sheet.getmergedregions (); Gets the collection of merged cell combinations in the entire sheet//first makes a preliminary boundary detection, if the specified range is illegal then throws an exception int rowsum = Sheet.getphysicalnumberofrows (); int colsum = Sheet.getrow (0). Getphysicalnumberofcells (); if (fromindex[0] > Rowsum | | fromindex[0] > toindex[0] | | toindex[0] & Gt Rowsum) {throw new Exception ("The RowIndex of the area is wrong!");}  if (fromindex[1] > Colsum | | fromindex[1] > toindex[1] | | toindex[1] > colsum) {throw new Exception ("The Colindex Of the area is wrong! ");} Calculates the size of the two-dimensional cell array that actually needs to be loaded into memory, rejecting the hidden column int rowsize = toindex[0]+1;int Colsize = toindex[1]+1;//traversing the area that needs to be scanned usercell[][] cells = NE W usercell[rowsize][colsize];int[] Rowpixpos = new Int[rowsize + 1];rowpixpos[0] = 0;int[] Colpixpos = new Int[colsize + 1];colpixpos[0] = 0;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= (I>=fromindex[0]) && (j>=fromindex[1]);//First row to be between the specified areas Ifshow=ifshow & &! (Sheet.iscolumnhidden (j) | | Sheet.getrow (i). Getzeroheight ());//The second row can not hide cells[i][j].setshow (ifshow);// Calculate the area width of float Widthpix =!ifshow? 0:sheet.getcolumnwidthinpixels (j); If the cell is hidden, the width is 0if (i = = Fromindex[0]) {imagewidth + = Widthpix;} COLPIXPOS[J+1] = (int) (WIDTHPIX * 1.15 + colpixpos[j]);} Calculates the area height of a Boolean ifshow= (I>=fromindex[0]);//Line sequence in the middle of the specified area ifshow=ifshow &&!sheet.getrow (i). getzeroheight ();//Line sequence cannot hide float Heightpoint =!ifshow? 0:sheet.getrow (i). Getheightinpoints (); If the cell is hidden, the height is 0imageHeight + = heightpoint;rowpixpos[i+1] = (int) (Heightpoint * 96/72) + rowpixpos[i];} ImageHeight = ImageHeight * 96/72;imagewidth = ImageWidth * 115/100;wb.close ();  list<grid> grids = new arraylist<grid> (); for (int i = 0, i < rowsize; i++) {for (int j = 0; j < Colsize; J + +) {grid Grid = new Grid ();//Set coordinates and width-height 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 ());//Determine if merge cell int[] Isinmergedstatus = isinmerged (Grid.getrow (), Grid.getcol (), rangeaddress); if (isinmergedstatus[0] = = 0 && isinmergedstatus[1] = = 0) {//This cell is a merged cell and is not the first cell , you need to skip this cycle, do not draw continue;} else if (isinmergedstatus[0]! =-1 && isinmergedstatus[1]! =-1) {//This cell is a merged cell and belongs to the first cell, you need to adjust the grid size int Lastrowpo S=isinmergedstatus[0]>rowsize-1?rowsize-1:isinmergedstatus[0];int lastcolpos=isinmergedstatus[1]> Colsize-1?colsize-1:isinmergedstatus[1];grid.setwidth (Colpixpos[lastcolpos + 1]-colpixpos[j]); Grid.sEtheight (Rowpixpos[lastrowpos + 1]-rowpixpos[i]);} Cell background color CellStyle cs = Cells[i][j].getcell (). Getcellstyle (); if (cs.getfillpattern () = = Cellstyle.solid_foreground) Grid.setbgcolor (Cells[i][j].getcell (). Getcellstyle (). Getfillforegroundcolorcolor ());// Set font Org.apache.poi.ss.usermodel.Font font = Wb.getfontat (Cs.getfontindex ()); Grid.setfont (font);//Set font foreground color if (font instanceof Xssffont) {Xssffont XF = (xssffont) font;grid.setftcolor (Xf.getxssfcolor ());} Set text string Strcell = ""; Switch (Cells[i][j].getcell (). Getcelltype ()) {Case HSSFCell.CELL_TYPE_NUMERIC:strCell = String.valueof (Cells[i][j].getcell (). Getnumericcellvalue ()); Break;case HSSFCell.CELL_TYPE_STRING:strCell = cells[ I][j].getcell (). Getstringcellvalue (); Break;case HSSFCell.CELL_TYPE_BOOLEAN:strCell = string.valueof (Cells[i][j]. Getcell (). Getbooleancellvalue ()), break;case HSSFCell.CELL_TYPE_FORMULA:try {Strcell = string.valueof (Cells[i][j]. Getcell (). Getnumericcellvalue ());} catch (IllegalStateException e) {Strcell = String.valueOf (Cells[i][j].getcell (). Getrichstringcellvalue ());} Break;default:strcell = "";} if (Cells[i][j].getcell (). Getcellstyle (). Getdataformatstring (). Contains ("0.00%")) {try{double dbcell= Double.valueof (Strcell) strcell=new DecimalFormat ("#.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 ();//Smooth font g2d.setrenderinghint (sunhints.key_antialiasing, SunHints.VALUE_ Antialias_off); G2d.setrenderinghint (sunhints.key_text_antialiasing, Sunhints.value_text_antialias_default); G2d.setrenderinghint (Sunhints.key_stroke_control, Sunhints.value_stroke_default); G2d.setRenderingHint ( Sunhints.key_text_antialias_lcd_contrast, G2d.setrenderinghint (Sunhints.key_fractionalmetrics, Sunhints.value_fractionalmetrics_off); G2d.setrenderinghint (Sunhints.key_renderiNG, Sunhints.value_render_default); G2d.setcolor (Color.White); g2d.fillrect (0, 0, imagewidth, imageheight);//Draw Table for (Grid g:grids) {if (!g.isshow ()) continue;//draws the background color G2d.setcolor (g.getbgcolor () = = null? Color.white:g.getbgcolor ()); G2d.fillrect (G.getx (), G.gety (), G.getwidth (), G.getheight ());//Draw Border G2d.setcolor ( Color.Black); G2d.setstroke (new Basicstroke (1)), G2d.drawrect (G.getx (), G.gety (), G.getwidth (), G.getheight ());// Draw text, centered display G2d.setcolor (G.getftcolor ()); Font font = G.getfont (); FontMetrics fm = g2d.getfontmetrics (font), int strwidth = Fm.stringwidth (G.gettext ());//Gets the text width to be drawn g2d.setfont (font); G2d.drawstring (G.gettext (), G.getx () + (G.getwidth ()-strwidth)/2,g.gety () + (G.getheight ()-font.getsize ())/2 + font . GetSize ());} G2d.dispose (); Imageio.write (image, "PNG", New File ("D:/test.png")); System.out.println ("Output to PNG file success!");} /** * Determines whether cells in Excel are merged cells * * @param row * @param col * @param rangeaddress * @return If the merged cell is not returned { -1,-1}, if it is a merged cell and is a unit Grid returns {LASTROW,LASTCOL}, * if it is a merged cell and is not the first lattice return {0,0} */private static int[] isinmerged (int row, int col, list<cellrangeaddress> R angeaddress) {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;}}

　　

Java exports a report in Excel as a picture format (c) in other implementations