POI Set Excel Style

POI Set Excel Style

Some summary of operations that need to format Excel cells may be used in POI:

Get the Workbook object first:

Hssfworkbook wb = new Hssfworkbook ();

Hssfsheet sheet = Wb.createsheet ();

Hssfcellstyle SetBorder = Wb.createcellstyle ();

First, set the background color:

Setborder.setfillforegroundcolor ((short) 13);//Set Background color
Setborder.setfillpattern (Hssfcellstyle.solid_foreground);

Second, set the border:

Setborder.setborderbottom (Hssfcellstyle.border_thin); Bottom Frame
Setborder.setborderleft (Hssfcellstyle.border_thin);//left Border
Setborder.setbordertop (Hssfcellstyle.border_thin);//Top Border
Setborder.setborderright (Hssfcellstyle.border_thin);//Right Border

Third, set the center:

Setborder.setalignment (Hssfcellstyle.align_center); Center

Four, set the font:

Hssffont font = Wb.createfont ();
Font.setfontname ("Blackbody");
Font.setfontheightinpoints ((short) 16);//Set Font size

Hssffont Font2 = Wb.createfont ();
Font2.setfontname ("Imitation _gb2312");
Font2.setboldweight (hssffont.boldweight_bold);//Bold display
Font2.setfontheightinpoints ((short) 12);

Setborder.setfont (font);//Select the font format you want to use

Five, set the column width:

Sheet.setcolumnwidth (0, 3766); The first parameter represents the column ID (starting at 0), and the 2nd parameter represents the Width value

Six, set the automatic line wrapping:

Setborder.setwraptext (TRUE);//Set line wrapping

Seven, merge the cells:

Region Region1 = new Region (0, (short) 0, 0, (short) 6);

Parameter 1: line number parameter 2: Starting column number parameter 3: line number parameter 4: terminating column number
Sheet.addmergedregion (Region1);

Eight, plus border

Hssfcellstyle cellstyle= Wookbook.createcellstyle ();
Cellstyle.setalignment (Hssfcellstyle.align_center);
Cellstyle.setborderbottom (Hssfcellstyle.borderborder_medium);
Cellstyle.setbottombordercolor (HSSFColor.BLACK.index);
Cellstyle.setborderleft (Hssfcellstyle.border_medium);
Cellstyle.setleftbordercolor (HSSFColor.BLACK.index);
Cellstyle.setborderright (Hssfcellstyle.border_medium);
Cellstyle.setrightbordercolor (HSSFColor.BLACK.index);
Cellstyle.setbordertop (Hssfcellstyle.border_medium);
Cellstyle.settopbordercolor (HSSFColor.BLACK.index);

Also attached: A complete small example of a

Development environment: IntelliJ idea 10.0.2

@ResponseBody

@RequestMapping (value = "/reportforms/joinstocktaking/exportstorage.api")

Public Ajaxresponse exportstorage (@RequestBody Storagemodel model) throws Exception {

if (logger.isdebugenabled ())

Logger.debug ("Tmpdir is, {}", System.getproperty (Java_io_tmpdir));

int row = 1;


Hssfworkbook workbook = new Hssfworkbook ();

Hssfsheet Hssfsheet = Workbook.createsheet ();

Hssfcellstyle style = Workbook.createcellstyle ();

Style.setfillbackgroundcolor (hssfcellstyle.least_dots);

Style.setfillpattern (hssfcellstyle.least_dots);


Set a border in Excel (the border of the header of a table)

Style.setalignment (Hssfcellstyle.align_center);

Style.setborderbottom (Hssfcellstyle.border_medium);

Style.setbottombordercolor (HSSFColor.BLACK.index);

Style.setborderleft (Hssfcellstyle.border_medium);

Style.setleftbordercolor (HSSFColor.BLACK.index);

Style.setborderright (Hssfcellstyle.border_medium);

Style.setrightbordercolor (HSSFColor.BLACK.index);

Style.setbordertop (Hssfcellstyle.border_medium);

Style.settopbordercolor (HSSFColor.BLACK.index);


Set font

Hssffont font = Workbook.createfont ();

Font.setfontheightinpoints ((short) 14); Font height

Font.setfontname ("Blackbody"); Font


Style.setfont (font);

Hssfrow firstrow = Hssfsheet.createrow ((short) 0);

Hssfcell FirstCell = Firstrow.createcell (0);

Firstrow.setheight ((short) 400);

Set the background in Excel

Style.setfillforegroundcolor (HSSFColor.GREEN.index);

Style.setfillbackgroundcolor (HSSFColor.GREEN.index);

Firstcell.setcellvalue (New hssfrichtextstring ("storeroom"));

Firstcell.setcellstyle (style);


Hssfcell Secondcell = Firstrow.createcell (1);

Firstrow.setheight ((short) 400);

Style.setfillforegroundcolor (HSSFColor.GREEN.index);

Style.setfillbackgroundcolor (HSSFColor.GREEN.index);

Secondcell.setcellvalue (New hssfrichtextstring ("Reservoir area"));

Secondcell.setcellstyle (style);


Hssfcell Threecell = Firstrow.createcell (2);

Firstrow.setheight ((short) 400);

Style.setfillforegroundcolor (HSSFColor.GREEN.index);

Style.setfillbackgroundcolor (HSSFColor.GREEN.index);

Threecell.setcellvalue (New hssfrichtextstring ("Item number"));

Threecell.setcellstyle (style);


Hssfcell Fourcell = Firstrow.createcell (3);

Firstrow.setheight ((short) 400);

Style.setfillforegroundcolor (HSSFColor.GREEN.index);

Style.setfillbackgroundcolor (HSSFColor.GREEN.index);

Fourcell.setcellvalue (New hssfrichtextstring ("Item name"));

Fourcell.setcellstyle (style);


Hssfcell Fivecell = Firstrow.createcell (4);

Firstrow.setheight ((short) 400);

Style.setfillforegroundcolor (HSSFColor.GREEN.index);

Style.setfillbackgroundcolor (HSSFColor.GREEN.index);

Fivecell.setcellvalue (New Hssfrichtextstring ("Number of Libraries"));

Fivecell.setcellstyle (style);


Hssfcell Sixcell = Firstrow.createcell (5);

Firstrow.setheight ((short) 400);

Style.setfillforegroundcolor (HSSFColor.GREEN.index);

Style.setfillbackgroundcolor (HSSFColor.GREEN.index);

Sixcell.setcellvalue (New hssfrichtextstring ("Locked Quantity"));

Sixcell.setcellstyle (style);


Set column widths

Hssfsheet.setcolumnwidth (0, 7000);

Hssfsheet.setcolumnwidth (1, 8000);

Hssfsheet.setcolumnwidth (2, 4000);

Hssfsheet.setcolumnwidth (3, 6000);

Hssfsheet.setcolumnwidth (4, 4000);

Hssfsheet.setcolumnwidth (5, 4000);



list<?> list = Joinstocktackingservice.findjoinstoragebyterm (Model.getwarehouse (), Model.getStockArea (), Model.getmaterialcode (), Model.getmaterialname ());

for (Object object:list) {

Object[] objects = (object[]) Object;

Storage Storage = (Storage) objects[0];

Warehouse Warehouse = (Warehouse) objects[1];

Stockarea Stockarea = (stockarea) objects[2];

Material Material = (Material) objects[3];



Set a border in Excel

Hssfcellstyle CellStyle = Workbook.createcellstyle ();

Cellstyle.setalignment (Hssfcellstyle.align_center);

Cellstyle.setborderbottom (Hssfcellstyle.border_medium);

Cellstyle.setbottombordercolor (HSSFColor.BLACK.index);

Cellstyle.setborderleft (Hssfcellstyle.border_medium);

Cellstyle.setleftbordercolor (HSSFColor.BLACK.index);

Cellstyle.setborderright (Hssfcellstyle.border_medium);

Cellstyle.setrightbordercolor (HSSFColor.BLACK.index);

Cellstyle.setbordertop (Hssfcellstyle.border_medium);

Cellstyle.settopbordercolor (HSSFColor.BLACK.index);



Hssfrow Hssfrow = Hssfsheet.createrow ((short) row);

Hssfcell Firsthssfcell = Hssfrow.createcell (0);//Storeroom

Firsthssfcell.setcelltype (hssfcell.cell_type_string);

Firsthssfcell.setcellvalue (New Hssfrichtextstring (Warehouse.getname ()));

Firsthssfcell.setcellstyle (CellStyle);//Set the style of the cell


Hssfcell Secondhssfcell = Hssfrow.createcell (1);

Secondhssfcell.setcelltype (hssfcell.cell_type_string);

Secondhssfcell.setcellvalue (New Hssfrichtextstring (Stockarea.getname ()));

Secondhssfcell.setcellstyle (CellStyle);//Set the style of the cell


Hssfcell Threehssfcell = Hssfrow.createcell (2);

Threehssfcell.setcelltype (hssfcell.cell_type_string);

Threehssfcell.setcellvalue (New Hssfrichtextstring (Material.getcode ()));

Threehssfcell.setcellstyle (CellStyle);//Set the style of the cell


Hssfcell Fourhssfcell = Hssfrow.createcell (3);

Fourhssfcell.setcelltype (hssfcell.cell_type_string);

Fourhssfcell.setcellvalue (New Hssfrichtextstring (Material.getname ()));

Fourhssfcell.setcellstyle (CellStyle);//Set the style of the cell


Hssfcell Fivehssfcell = Hssfrow.createcell (4);

Fivehssfcell.setcelltype (hssfcell.cell_type_string);

Fivehssfcell.setcellvalue (New Hssfrichtextstring (String.valueof (Storage.getqty ()));

Fivehssfcell.setcellstyle (CellStyle);//Set the style of the cell


Hssfcell Sixhssfcell = Hssfrow.createcell (5);

Sixhssfcell.setcelltype (hssfcell.cell_type_string);

Sixhssfcell.setcellvalue (New Hssfrichtextstring (String.valueof (storage.getqtylocked ()));

Sixhssfcell.setcellstyle (CellStyle);//Set the style of the cell


row++;

}

String NewFileName = String.Format ("%s.%s", "joinstocktaking-" + (new Date ()). GetTime (), "xls");

String Uploadpath = Fileutils.contractpath (System.getproperty (Java_io_tmpdir), newfilename);

FileOutputStream fOut = new FileOutputStream (Uploadpath);

Workbook.write (FOut);

Fout.flush ();

Fout.close ();


Return ajaxresponse.createsuccess (NewFileName);

}

POI set Excel Style (GO)