How to apply styles/fonts to XSLX cells?
Posted: Wed Mar 25, 2015 1:36 amHello,
I'm trying to create an xlsx spreadsheet with various cell styles (bold, bordered, etc.) For that I'm creating a styles.xml Part:
where sb is a StyleSheetBuilder class that looks like this:
when referencing styles while creating cells will c.setS(0), it does not have any affect what so ever.
What am I doing wrong?? [I'm stuck with this for a couple of days now]
Thank you in advance!
I'm trying to create an xlsx spreadsheet with various cell styles (bold, bordered, etc.) For that I'm creating a styles.xml Part:
- Code: Select all
WorksheetPart sheet = xlsUtils.createWorkSheet(pkg);
Styles styles = new Styles(new PartName("/xl/styles.xml"));
styles.setJaxbElement(sb.createStyleSheet());
where sb is a StyleSheetBuilder class that looks like this:
- Code: Select all
package xlsx;
import javax.xml.bind.JAXBElement;
import org.xlsx4j.sml.CTBooleanProperty;
import org.xlsx4j.sml.CTBorder;
import org.xlsx4j.sml.CTBorderPr;
import org.xlsx4j.sml.CTBorders;
import org.xlsx4j.sml.CTCellStyle;
import org.xlsx4j.sml.CTCellStyleXfs;
import org.xlsx4j.sml.CTCellStyles;
import org.xlsx4j.sml.CTCellXfs;
import org.xlsx4j.sml.CTColor;
import org.xlsx4j.sml.CTDxfs;
import org.xlsx4j.sml.CTExtension;
import org.xlsx4j.sml.CTExtensionList;
import org.xlsx4j.sml.CTFill;
import org.xlsx4j.sml.CTFills;
import org.xlsx4j.sml.CTFont;
import org.xlsx4j.sml.CTFontFamily;
import org.xlsx4j.sml.CTFontName;
import org.xlsx4j.sml.CTFontSize;
import org.xlsx4j.sml.CTFonts;
import org.xlsx4j.sml.CTIntProperty;
import org.xlsx4j.sml.CTPatternFill;
import org.xlsx4j.sml.CTStylesheet;
import org.xlsx4j.sml.CTTableStyles;
import org.xlsx4j.sml.CTXf;
import org.xlsx4j.sml.STPatternType;
import xlsx.XlsCell.FONT_COLOR;
public class StyleSheetBuilder {
private void setFontSize(long size, CTFont font){
CTFontSize fontSize = new CTFontSize();
fontSize.setVal(size);
JAXBElement<CTFontSize> fontSizeJaxbE = XlsxUtils.getSmlObjectFactory().createCTFontSz(fontSize);
font.getNameOrCharsetOrFamily().add(fontSizeJaxbE);
}
private void setFontColor(FONT_COLOR color, CTFont font){
CTColor fontCol = new CTColor();
fontCol.setRgb(FONT_COLOR.RED.getRgbvalue().getBytes());
fontCol.setTheme( new Long(1) );
fontCol.setTint( new Double(0.0) );
fontCol.setParent(font);
JAXBElement<CTColor> element1 = XlsxUtils.getSmlObjectFactory().createCTFontColor(fontCol);
font.getNameOrCharsetOrFamily().add(element1);
}
private void setFontName(String name, CTFont font){
CTFontName fontName = new CTFontName();
fontName.setVal("Calibri");
JAXBElement<CTFontName> element2 = XlsxUtils.getSmlObjectFactory().createCTFontName(fontName);
font.getNameOrCharsetOrFamily().add(element2);
}
private void addBoldStyle(CTFont font){
JAXBElement<CTBooleanProperty> styleBold = XlsxUtils.getSmlObjectFactory().createCTFontB(new CTBooleanProperty());
font.getNameOrCharsetOrFamily().add(styleBold);
}
private void createFonts(CTStylesheet styleSheet){
CTFonts fonts = new CTFonts();
fonts.setCount(1L);
CTFont calibriBold = new CTFont();
setFontSize(15, calibriBold);
// setFontColor(FONT_COLOR.BLACK, calibriBold);
setFontName("Calibri", calibriBold);
addBoldStyle(calibriBold);
// Create object for family (wrapped in JAXBElement)
CTFontFamily fontfamily = XlsxUtils.getSmlObjectFactory().createCTFontFamily();
JAXBElement<org.xlsx4j.sml.CTFontFamily> fontfamilyWrapped = XlsxUtils.getSmlObjectFactory().createCTFontFamily(fontfamily);
calibriBold.getNameOrCharsetOrFamily().add( fontfamilyWrapped);
fontfamily.setVal( 2 );
// Create object for charset (wrapped in JAXBElement)
CTIntProperty intproperty = XlsxUtils.getSmlObjectFactory().createCTIntProperty();
JAXBElement<org.xlsx4j.sml.CTIntProperty> intpropertyWrapped = XlsxUtils.getSmlObjectFactory().createCTFontCharset(intproperty);
calibriBold.getNameOrCharsetOrFamily().add( intpropertyWrapped);
intproperty.setVal( 186 );
// Create object for scheme (wrapped in JAXBElement)
// CTFontScheme fontscheme = XlsxUtils.getSmlObjectFactory().createCTFontScheme();
// JAXBElement<org.xlsx4j.sml.CTFontScheme> fontschemeWrapped = XlsxUtils.getSmlObjectFactory().createCTFontScheme(fontscheme);
// calibriBold.getNameOrCharsetOrFamily().add( fontschemeWrapped);
// fontscheme.setVal(org.xlsx4j.sml.STFontScheme.MINOR);
fonts.getFont().add(calibriBold);
CTFont calibriBold1 = new CTFont();
setFontSize(18L, calibriBold1);
// setFontColor(FONT_COLOR.RED, calibriBold1);
setFontName("Calibri", calibriBold1);
addBoldStyle(calibriBold1);
fonts.getFont().add(calibriBold1);
styleSheet.setFonts(fonts);
}
private void createFills(CTStylesheet styleSheet){
CTFills fills = new CTFills();
fills.setCount(1L);
CTFill fill0 = new CTFill();
CTPatternFill patfill0 = new CTPatternFill();
patfill0.setPatternType(STPatternType.NONE);
fill0.setPatternFill(patfill0);
CTColor color0 = new CTColor();
color0.setTheme(0L);
color0.setTint(-0.14999847407452621d);
CTColor color1 = new CTColor();
color1.setIndexed(64L);
fills.getFill().add(fill0);
styleSheet.setFills(fills);
}
private void createBorders(CTStylesheet styleSheet){
CTBorders borders = new CTBorders();
borders.setCount(1L);
CTBorder emptyBorder = new CTBorder();
emptyBorder.setLeft(new CTBorderPr());
emptyBorder.setRight(new CTBorderPr());
emptyBorder.setTop(new CTBorderPr());
emptyBorder.setBottom(new CTBorderPr());
emptyBorder.setDiagonal(new CTBorderPr());
borders.getBorder().add(emptyBorder);
styleSheet.setBorders(borders);
}
private void createDefaultXf(CTStylesheet styleSheet){
CTCellStyleXfs xcfs = new CTCellStyleXfs();
xcfs.setCount(1L);
CTXf xcf = new CTXf();
xcf.setFontId(0L);
xcf.setFillId(0L);
xcf.setBorderId(0L);
xcf.setApplyFont(true);
xcfs.getXf().add(xcf);
CTXf xcf1 = new CTXf();
xcf1.setFontId(0L);
xcf1.setFillId(0L);
xcf1.setBorderId(0L);
xcf1.setApplyFont(true);
xcfs.getXf().add(xcf1);
styleSheet.setCellStyleXfs(xcfs);
}
private void createXFCombinations(CTStylesheet styleSheet){
CTCellXfs xfs = new CTCellXfs();
xfs.setCount(2L);
CTXf xf0 = new CTXf();
xf0.setFontId(0L);
xf0.setFillId(0L);
// xf0.setApplyFont(true);
xf0.setBorderId(0L);
xf0.setXfId(0L);
xfs.getXf().add(xf0);
CTXf xf1 = new CTXf();
xf1.setFontId(0L);
xf1.setFillId(0L);
// xf1.setApplyFont(true);
xf1.setBorderId(0L);
xf1.setXfId(1L);
xfs.getXf().add(xf1);
CTXf xf2 = new CTXf();
xf2.setFontId(0L);
xf2.setFillId(0L);
// xf1.setApplyFont(true);
xf2.setBorderId(0L);
xf2.setXfId(1L);
xfs.getXf().add(xf2);
CTXf xf3 = new CTXf();
xf3.setFontId(0L);
xf3.setFillId(0L);
// xf1.setApplyFont(true);
xf3.setBorderId(0L);
xf3.setXfId(1L);
xfs.getXf().add(xf3);
// CTCellAlignment align = new CTCellAlignment();
// align.setHorizontal(STHorizontalAlignment.CENTER);
// xf10.setAlignment(align);
//
// xfs.getXf().add(xf10);
//
styleSheet.setCellXfs(xfs);
}
private void createDefaultCellStyle(CTStylesheet styleSheet){
CTCellStyles cstyles = new CTCellStyles();
cstyles.setCount(1L);
CTCellStyle cstyle = new CTCellStyle();
cstyle.setName("Standard");
cstyle.setXfId(0L);
cstyle.setBuiltinId(0L);
cstyles.getCellStyle().add(cstyle);
styleSheet.setCellStyles(cstyles);
}
private void addOtherFields(CTStylesheet styleSheet){
//<editor-fold defaultstate="collapsed" desc="DXFS">
CTDxfs dxfs = new CTDxfs();
dxfs.setCount(0L);
styleSheet.setDxfs(dxfs);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Table Styles">
CTTableStyles tstyles = new CTTableStyles();
tstyles.setCount(0L);
tstyles.setDefaultTableStyle("TableStyleMedium2");
tstyles.setDefaultPivotStyle("PivotStyleLight16");
styleSheet.setTableStyles(tstyles);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Extensions">
CTExtensionList extlist = new CTExtensionList();
CTExtension ext = new CTExtension();
ext.setUri("{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}");
extlist.getExt().add(ext);
styleSheet.setExtLst(extlist);
//</editor-fold>
}
public CTStylesheet createStyleSheet() {
CTStylesheet ss = XlsxUtils.getSmlObjectFactory().createCTStylesheet();
createFonts(ss);
createFills(ss);
createBorders(ss);
createDefaultXf(ss);
createXFCombinations(ss);
createDefaultCellStyle(ss);
addOtherFields(ss);
return ss;
}
}
when referencing styles while creating cells will c.setS(0), it does not have any affect what so ever.
What am I doing wrong?? [I'm stuck with this for a couple of days now]
Thank you in advance!