POI Export Excel

Java uses the POI component to export Excel reports, can export Excel reports can also use the JXL component, but JXL think for the POI function is limited, JXL should not be able to load Excel to insert floating layer picture, poi can be very good to achieve the output of Excel various functions, This article introduces the example of POI export Excel function, calculates the common function realization and the place that Excel should pay attention to, adopt Poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar, Poi-scratchpad-3.8-20120326.jar

Output table

Poi output Excel is the most basic output table table, the following is the output area, total sales (million), total profit (million) simple table,
Create a Hssfworkbook object to output Excel to the output stream

Hssfworkbook wb = new Hssfworkbook (); Hssfsheet sheet = wb.createsheet ("table");  Create TABLE Workbook object[][] Datas = {{"area", "total sales (million)", "total profit (million) Simple form"}, {"Jiangsu province", 9045,  2256}, {"Guangdong province", 3000, 690}; Hssfrow Row; Hssfcell cell;for (int i = 0; i < datas.length; i++) {    row = Sheet.createrow (i);//CREATE TABLE row for    (int j = 0; J < da Tas[i].length; J + +) {        cell = Row.createcell (j);//Create Cell        cell.setcellvalue (string.valueof (Datas[i][j]) based on table row;}    } Wb.write (New FileOutputStream ("/users/mike/table.xls"));              

Set table row height, column width

Sometimes the table text is more, you need to set the table column width, when you set the table row height and column widths must be created after all Hssfrow and Hssfcell,
That is, the entire table is created after it is set up, because when the cell is merged, the Width cell set before the merge is wider than the width of the setting. Sheet.setcolumnwidth Setting the column width value needs to be converted to the width value of Excel, using the tool class: Msexcelutil,excel width is not pixels need to be converted

Hssfworkbook wb = new Hssfworkbook (); Hssfsheet sheet = wb.createsheet ("table");  Create TABLE Workbook object[][] Datas = {{"area", "total sales (million)", "total profit (million) Simple form"}, {"Jiangsu province", 9045,  2256}, {"Guangdong province", 3000, 690}; Hssfrow Row; Hssfcell cell;for (int i = 0; i < datas.length; i++) {    row = Sheet.createrow (i);//CREATE TABLE row for    (int j = 0; J < da Tas[i].length; J + +) {        cell = Row.createcell (j);//Create Cell        cell.setcellvalue (string.valueof (Datas[i][j]) based on table row;}    } Sets the row height and column width after the table is created for (int i = 0; i < datas.length; i++) {    row = Sheet.getrow (i);    Row.setheightinpoints (30);//Set line height}for (int j = 0; J < Datas[0].length; J + +) {    Sheet.setcolumnwidth (J, Msexcelutil.pixel2widthunits (160)); Set column width}wb.write (new FileOutputStream ("/users/mike/table1.xls"));                

Set Excel cell Styles

Cell can be set to left, center, right, up and down center, set border, set border color, set cell background color, etc., Excel set cell has a Hssfcellstyle class can set style, cell color is troublesome, Excel color corresponds to a subscript value, We can use a custom color, but the subscript value starts at 11, the first 1-10 is used by the POI, and the color is mapped to the subscript value by the Palette.setcoloratindex method, the following Cellstyle.setfillforegroundcolor ( Bgindex) Set the background color when the set subscript value is not a color, a subscript value such as 11 cannot be repeatedly set color, or Excel cell display is black, the following background color using subscript value bgindex= 11, Border color use subscript value bdindex=12

Short ColorIndex = 10; Hssfpalette palette = Wb.getcustompalette ();//Custom colors color RGB = Color.green;short Bgindex = ColorIndex + +; Background color subscript value Palette.setcoloratindex (Bgindex, (Byte) rgb.getred (), (byte) Rgb.getgreen (), (byte) rgb.getblue ()); short Bdindex = ColorIndex + +; Border color subscript value RGB = Color.black;palette.setcoloratindex (Bdindex, (Byte) rgb.getred (), (byte) Rgb.getgreen (), (byte) Rgb.getblue ()); for (int i = 0; i < datas.length; i++) {row = Sheet.createrow (i);//CREATE TABLE row for (int j = 0; J < dat As[i].length;        J + +) {cell = Row.createcell (j);//Create Cell Cell.setcellvalue (string.valueof (DATAS[I][J)) based on table row;        Hssfcellstyle CellStyle = Wb.createcellstyle (); Cellstyle.setfillforegroundcolor (Bgindex);        Bgindex background color subscript value Cellstyle.setfillpattern (cellstyle.solid_foreground);        Cellstyle.setborderbottom (Hssfcellstyle.border_thin);        Cellstyle.setborderleft (Hssfcellstyle.border_thin);        Cellstyle.setbordertop (Hssfcellstyle.border_thin); CellStyle.Setborderright (Hssfcellstyle.border_thin);        Bdindex border Color subscript value Cellstyle.setbottombordercolor (bdindex);        Cellstyle.setleftbordercolor (Bdindex);        Cellstyle.setrightbordercolor (Bdindex);        Cellstyle.settopbordercolor (Bdindex);        Cellstyle.setalignment (Hssfcellstyle.align_center);        Cellstyle.setverticalalignment (Hssfcellstyle.vertical_center);    Cell.setcellstyle (CellStyle);                 }}

Cell text Set font style

The cell text can be set to font size, color, italic, bold, underline, and so on.

Hssfcellstyle CellStyle = Wb.createcellstyle (); Hssffont font = Wb.createfont (); Font.setitalic (true); Font.setunderline (Hssffont.u_single); Font.setboldweight ( Hssffont.boldweight_bold); font.setfontheightinpoints (short); Cellstyle.setfont (font);                

Merge cells

Sheet can resemble HTML merged cells, specify the start line (calculated from 0), merge cell last row, start column (starting from 0), merge cell last column four parameter values

Cellrangeaddress region = new Cellrangeaddress (0,//First row        0,//Last row        0,//First column        2//Last Col umn); sheet.addmergedregion (region);                

Add a picture to the cell

A cell is not only text, numeric values, but also pictures, you need to specify the number of rows that the picture occupies, the number of start columns, the number of rows at the end, and the number of columns at the end.
Support for PNG, JPEG, EMF, etc.

Add Image byte[] bt = Fileutils.readfiletobytearray (New File ("/users/mike/pie.png")); int pictureidx = Wb.addpicture (BT, Workbook.picture_type_png); Creationhelper helper = Wb.getcreationhelper ();D rawing Drawing = Sheet.createdrawingpatriarch (); Clientanchor anchor = Helper.createclientanchor (); anchor.setcol1 (0); Number of picture start columns Anchor.setrow1 (4); The number of pictures started Anchor.setcol2 (3); The number of picture end columns Anchor.setrow2 (25);//Picture End line number Drawing.createpicture (anchor, PICTUREIDX);                

Insert a floating layer picture in Excel like a div in HTML

When you insert a picture in Excel, the POI export Excel does not appear to insert the picture by absolute position x, Y, you can calculate the approximate position of x, y values in the row height and column widths in which cell,
Then, like (6), insert a picture, only specify the number of start of the picture, the number of start columns, picture.resize () will make the picture according to the actual size of the picture to expand.

Add picture int pictureidx = Wb.addpicture (BT, workbook.picture_type_png); Creationhelper helper = Wb.getcreationhelper ();D rawing Drawing = Sheet.createdrawingpatriarch (); Clientanchor anchor = Helper.createclientanchor (); anchor.setcol1 (0); Number of picture start columns Anchor.setrow1 (4); The number of pictures to start with the picture image = Drawing.createpicture (anchor, PICTUREIDX);p icture.resize ();                

Draw Slash in cell

Excel cell draw slash another article in detail: Poi Excel Slash header

Length Conversion Msexcelutil

Cell width and height in Excel are not pixel values, PPT values, so you need to convert, Msexcelutil is a conversion tool class

public class Msexcelutil {public static final short excel_column_width_factor = 256;public static final int Unit_offset_le Ngth = 7;public static final int[] Unit_offset_map = new int[] {0, $,, 109, 146, 182, 219};/** * pixel units to Exce L Width units (units of 1/256th of a character width) *  * @param pxs * @return */public static short pixel2widthunits (i NT Pxs) {Short widthunits = (short) (Excel_column_width_factor * (pxs/unit_offset_length)); Widthunits + = Unit_offset_map [(pxs% unit_offset_length)];return widthunits;}  /** * Excel Width units (units of 1/256th of a character width) to pixel units *  * @param widthunits * @return */public static int widthunits2pixel (int widthunits) {int pixels = (widthunits/excel_column_width_factor) * unit_offset_length;i NT Offsetwidthunits = widthunits% excel_column_width_factor;pixels + = Math.Round (offsetwidthunits/((float) EXCEL_ Column_width_factor/unit_offset_length)); return pixels;}}             

Complete example

Poi export Excel source code

Import Java.awt.color;import java.io.file;import java.io.fileoutputstream;import org.apache.commons.io.FileUtils; Import Org.apache.poi.hssf.usermodel.hssfcell;import Org.apache.poi.hssf.usermodel.hssfcellstyle;import Org.apache.poi.hssf.usermodel.hssffont;import Org.apache.poi.hssf.usermodel.hssfpalette;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.cellstyle;import Org.apache.poi.ss.usermodel.clientanchor;import Org.apache.poi.ss.usermodel.creationhelper;import Org.apache.poi.ss.usermodel.drawing;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.ss.util.cellrangeaddress;public Final class Testexportexcel {public static void main (string[] args) thr        OWS Exception {Hssfworkbook wb = new Hssfworkbook ();  Hssfsheet sheet = wb.createsheet ("table");(million yuan) simple form "}, {" Jiangsu province ", 9045, 2256}, {" Guangdong province ", 3000, 690}};        Hssfrow Row;                Hssfcell cell;        Short ColorIndex = 10;        Hssfpalette palette = Wb.getcustompalette ();        Color RGB = Color.green;         Short Bgindex = ColorIndex + +;        Palette.setcoloratindex (Bgindex, (Byte) rgb.getred (), (byte) Rgb.getgreen (), (byte) rgb.getblue ());        Short Bdindex = ColorIndex + +;        RGB = Color.Black;                Palette.setcoloratindex (Bdindex, (Byte) rgb.getred (), (byte) Rgb.getgreen (), (byte) rgb.getblue ()); for (int i = 0; i < datas.length; i++) {row = Sheet.createrow (i);//CREATE TABLE row for (int j = 0; J < da Tas[i].length;                                 J + +) {cell = Row.createcell (j);//Create Cell Cell.setcellvalue (string.valueof (DATAS[I][J)) based on table row;                Hssfcellstyle CellStyle = Wb.createcellstyle (); if (i = = 0 | | i = = 1) {Cellstyle.setfillforegroundcolor (bgindex);//bginDex Background Color subscript value Cellstyle.setfillpattern (cellstyle.solid_foreground);                } cellstyle.setborderbottom (Hssfcellstyle.border_thin);                Cellstyle.setborderleft (Hssfcellstyle.border_thin);                Cellstyle.setbordertop (Hssfcellstyle.border_thin);                Cellstyle.setborderright (Hssfcellstyle.border_thin);                Bdindex border Color subscript value Cellstyle.setbottombordercolor (bdindex);                Cellstyle.setleftbordercolor (Bdindex);                Cellstyle.setrightbordercolor (Bdindex);                                Cellstyle.settopbordercolor (Bdindex);                Cellstyle.setalignment (Hssfcellstyle.align_center);                                Cellstyle.setverticalalignment (Hssfcellstyle.vertical_center);                    if (i = = Datas.length-1 && j = = datas[0].length-1) {Hssffont font = Wb.createfont ();             Font.setitalic (TRUE);       Font.setunderline (Hssffont.u_single);                    Font.setboldweight (Hssffont.boldweight_bold);                    Font.setfontheightinpoints ((short) 14);                Cellstyle.setfont (font);            } cell.setcellstyle (CellStyle);        }}//Add image byte[] bt = Fileutils.readfiletobytearray (New File ("/users/mike/pie.png"));        int pictureidx = Wb.addpicture (BT, workbook.picture_type_png);        Creationhelper helper = Wb.getcreationhelper ();        Drawing Drawing = Sheet.createdrawingpatriarch ();        Clientanchor anchor = Helper.createclientanchor ();        ANCHOR.SETDX1 (Msexcelutil.pixel2widthunits (60));        Anchor.setdy1 (Msexcelutil.pixel2widthunits (60));        Anchor.setcol1 (0);       Anchor.setrow1 (4);       Anchor.setcol2 (3);        Anchor.setrow2 (25);                Drawing.createpicture (anchor, PICTUREIDX); Merge cell cellrangeaddress region = new Cellrangeaddress (0,//FIrst row 0,//last row 0,//First column 2//last column);                Sheet.addmergedregion (region);            Sets the row height and column width after the table is created for (int i = 0; i < datas.length; i++) {row = Sheet.getrow (i);        Row.setheightinpoints (30); } for (int j = 0; J < Datas[0].length; J + +) {Sheet.setcolumnwidth (J, Msexcelutil.pixel2widthunits (160        ));    } wb.write (New FileOutputStream ("/users/mike/table6.xls")); }    }





Transferred from: http://www.anyrt.com/blog/list/poiexcel.html

POI Export Excel