This problem is driving me crazy. I am trying to create a small xlsx-file. Inserting data works just fine, but when I try to format a cell, I get an error when I open the created xlsx-file afterwards. There has to be something wrong with the styles.xml. Below you will find the code.
Can somebody help me out?
- Code: Select all
public class XlsxCreator {
private static Worksheet sheetfinal;
private static WorksheetPart sheet;
private static int dueyear = 2012;
public static void main(String[] args) throws Exception {
String outputfilepath = "d:/test.xlsx";
SpreadsheetMLPackage pkg = SpreadsheetMLPackage.createPackage();
Styles styles = new Styles(new PartName("/xl/styles.xml"));
styles.setJaxbElement(createStyleSheet());
pkg.addTargetPart(styles);
sheet = pkg.createWorksheetPart(new PartName("/xl/worksheets/sheet1.xml"), "Sheet1", 1);
sheetfinal = sheet.getJaxbElement();
prepareSheet();
addContent();
setMergedCells();
SaveToZipFile saver = new SaveToZipFile(pkg);
saver.save(outputfilepath);
}
private static void addContent() {
SheetData sheetData = sheetfinal.getSheetData();
Row row0 = Context.getsmlObjectFactory().createRow();
Cell cell = Context.getsmlObjectFactory().createCell();
//<editor-fold defaultstate="collapsed" desc="Set Title">
cell.setV("Testtitle");
cell.setT(STCellType.STR);
cell.setS(10L);
row0.getC().add(cell);
sheetData.getRow().add(row0);
sheetData.getRow().add(Context.getsmlObjectFactory().createRow());
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Set Header Row">
Row row1 = Context.getsmlObjectFactory().createRow();
for(int i=0;i<6;i++) {
cell = Context.getsmlObjectFactory().createCell();
cell.setV(getTitle(i, true, dueyear));
cell.setT(STCellType.STR);
row1.getC().add(cell);
}
sheetData.getRow().add(row1);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Test Data">
String[][] data = new String[4][5];
data[0][0] = "1000";
data[1][0] = "Kasse";
data[2][0] = "100";
data[3][0] = "500";
data[0][1] = "1010";
data[1][1] = "Post";
data[2][1] = "20";
data[3][1] = "1000";
data[0][2] = "1020";
data[1][2] = "Bank";
data[2][2] = "500";
data[3][2] = "100";
data[0][3] = "1060";
data[1][3] = "Wertschriften kurzfristig";
data[2][3] = "1000";
data[3][3] = "5000";
data[0][4] = "1100";
data[1][4] = "Debioren";
data[2][4] = "10000";
data[3][4] = "12000";
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Insert Data">
for(int y=0;y<5;y++) {
Row row2 = Context.getsmlObjectFactory().createRow();
for(int x=0;x<6;x++) {
cell = Context.smlObjectFactory.createCell();
if(x==1) {
cell.setV(data[x][y]);
cell.setT(STCellType.STR);
} else if(x==4 || x==5) {
CTCellFormula form = Context.getsmlObjectFactory().createCTCellFormula();
form.setValue(createFormula(y+4, x==4));
cell.setF(form);
cell.setV(form.getRef());
} else {
cell.setV(data[x][y]);
}
row2.getC().add(cell);
}
sheetData.getRow().add(row2);
}
//</editor-fold>
}
//<editor-fold defaultstate="collapsed" desc="Prepare Sheet">
private static void prepareSheet() {
CTSheetPr shpr = Context.getsmlObjectFactory().createCTSheetPr();
CTPageSetUpPr setup = Context.getsmlObjectFactory().createCTPageSetUpPr();
setup.setFitToPage(Boolean.TRUE);
shpr.setPageSetUpPr(setup);
sheetfinal.setSheetPr(shpr);
CTPageMargins margins = Context.getsmlObjectFactory().createCTPageMargins();
margins.setLeft(0.23d);
margins.setRight(0.23d);
margins.setTop(0.55d);
margins.setBottom(0.55d);
margins.setHeader(0.31d);
margins.setFooter(0.31d);
sheetfinal.setPageMargins(margins);
CTPageSetup pagesetup = Context.getsmlObjectFactory().createCTPageSetup();
pagesetup.setPaperSize(9L);
pagesetup.setScale(85L);
pagesetup.setFitToHeight(0L);
pagesetup.setOrientation(STOrientation.PORTRAIT);
pagesetup.setVerticalDpi(200L);
sheetfinal.setPageSetup(pagesetup);
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Merge Title Cells">
private static void setMergedCells() {
CTMergeCells merged = Context.smlObjectFactory.createCTMergeCells();
CTMergeCell merge = Context.smlObjectFactory.createCTMergeCell();
merged.setCount(1L);
merge.setRef("A1:F1");
merged.getMergeCell().add(merge);
sheetfinal.setMergeCells(merged);
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Prepare Header Title">
private static String getTitle(int index, boolean isBilanz, int year) {
if(index==0) {
return "Kto";
} else if(index==1) {
return "Kontobezeichnung";
} else if(index==2) {
if(isBilanz) {
return "Anfangsbestand "+year;
} else {
return String.valueOf(year-1);
}
} else if(index==3) {
if(isBilanz) {
return "Endbestand "+year;
} else {
return String.valueOf(year);
}
} else if(index==4) {
return "Veränderung";
} else if(index==5) {
return "+/- %";
} else {
return "";
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Create Formula">
private static String createFormula(int row, boolean first) {
if(first) {
return "D"+row+"-C"+row;
} else {
return "-1+(D"+row+"/C"+row+")";
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Create Stylesheet">
private static CTStylesheet createStyleSheet() {
CTStylesheet ss = Context.getsmlObjectFactory().createCTStylesheet();
//<editor-fold defaultstate="collapsed" desc="Nummerformatierung">
CTNumFmts formate = new CTNumFmts();
CTNumFmt format0 = new CTNumFmt();
format0.setNumFmtId(42L);
format0.setFormatCode("_ \"Fr.\"\\ * #,##0_ ;_ \"Fr.\"\\ * \\-#,##0_ ;_ \";Fr.\"\\ * \";-\"_ ;_ @_ ");
formate.getNumFmt().add(format0);
CTNumFmt format1 = new CTNumFmt();
format1.setNumFmtId(164L);
format1.setFormatCode("\\+\\ _ \"Fr.\"\\ * #,##0.00_ ;\\-\\ _ \"Fr.\"\\ * \\-#,##0.00_ ;_ \"Fr.\"\\ * \"-\"??_ ;_ @_ ");
formate.getNumFmt().add(format1);
CTNumFmt format2 = new CTNumFmt();
format2.setNumFmtId(165L);
format2.setFormatCode("\\+\\ 0.00%;\\-\\ 0.00%");
formate.getNumFmt().add(format2);
ss.setNumFmts(formate);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Fonts">
CTFonts fonts = new CTFonts();
fonts.setCount(3L);
//<editor-fold defaultstate="collapsed" desc="Font 1">
CTFont font0 = new CTFont();
CTFontSize sz0 = new CTFontSize();
sz0.setVal(11L);
JAXBElement<CTFontSize> element0 = Context.smlObjectFactory.createCTFontSz(sz0);
font0.getNameOrCharsetOrFamily().add(element0);
CTColor col0 = new CTColor();
col0.setTheme(1L);
JAXBElement<CTColor> element1 = Context.smlObjectFactory.createCTFontColor(col0);
font0.getNameOrCharsetOrFamily().add(element1);
CTFontName fname0 = new CTFontName();
fname0.setVal("Calibri");
JAXBElement<CTFontName> element2 = Context.smlObjectFactory.createCTFontName(fname0);
font0.getNameOrCharsetOrFamily().add(element2);
CTIntProperty famname0 = new CTIntProperty();
famname0.setVal(2);
JAXBElement<CTIntProperty> element3 = Context.smlObjectFactory.createCTFontFamily(famname0);
font0.getNameOrCharsetOrFamily().add(element3);
CTFontScheme fschema0 = new CTFontScheme();
fschema0.setVal(STFontScheme.MINOR);
JAXBElement<CTFontScheme> element4 = Context.smlObjectFactory.createCTFontScheme(fschema0);
font0.getNameOrCharsetOrFamily().add(element4);
fonts.getFont().add(font0);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Font 2">
CTFont font1 = new CTFont();
JAXBElement<CTBooleanProperty> element10 = Context.smlObjectFactory.createCTFontB(new CTBooleanProperty());
font1.getNameOrCharsetOrFamily().add(element10);
CTFontSize sz1 = new CTFontSize();
sz1.setVal(11L);
JAXBElement<CTFontSize> element5 = Context.smlObjectFactory.createCTFontSz(sz1);
font1.getNameOrCharsetOrFamily().add(element5);
CTColor col1 = new CTColor();
col1.setTheme(1L);
JAXBElement<CTColor> element6 = Context.smlObjectFactory.createCTFontColor(col1);
font1.getNameOrCharsetOrFamily().add(element6);
CTFontName fname1 = new CTFontName();
fname1.setVal("Calibri");
JAXBElement<CTFontName> element7 = Context.smlObjectFactory.createCTFontName(fname1);
font1.getNameOrCharsetOrFamily().add(element7);
CTIntProperty famname1 = new CTIntProperty();
famname1.setVal(2);
JAXBElement<CTIntProperty> element8 = Context.smlObjectFactory.createCTFontFamily(famname1);
font1.getNameOrCharsetOrFamily().add(element8);
CTFontScheme fschema1 = new CTFontScheme();
fschema1.setVal(STFontScheme.MINOR);
JAXBElement<CTFontScheme> element9 = Context.smlObjectFactory.createCTFontScheme(fschema1);
font1.getNameOrCharsetOrFamily().add(element9);
fonts.getFont().add(font1);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Font 3">
CTFont font2 = new CTFont();
JAXBElement<CTBooleanProperty> element16 = Context.smlObjectFactory.createCTFontB(new CTBooleanProperty());
font2.getNameOrCharsetOrFamily().add(element16);
CTFontSize sz2 = new CTFontSize();
sz2.setVal(14L);
JAXBElement<CTFontSize> element11 = Context.smlObjectFactory.createCTFontSz(sz2);
font2.getNameOrCharsetOrFamily().add(element11);
CTColor col2 = new CTColor();
col2.setTheme(1L);
JAXBElement<CTColor> element12 = Context.smlObjectFactory.createCTFontColor(col2);
font2.getNameOrCharsetOrFamily().add(element12);
CTFontName fname2 = new CTFontName();
fname2.setVal("Calibri");
JAXBElement<CTFontName> element13 = Context.smlObjectFactory.createCTFontName(fname2);
font2.getNameOrCharsetOrFamily().add(element13);
CTIntProperty famname2 = new CTIntProperty();
famname2.setVal(2);
JAXBElement<CTIntProperty> element14 = Context.smlObjectFactory.createCTFontFamily(famname2);
font2.getNameOrCharsetOrFamily().add(element14);
CTFontScheme fschema2 = new CTFontScheme();
fschema2.setVal(STFontScheme.MINOR);
JAXBElement<CTFontScheme> element15 = Context.smlObjectFactory.createCTFontScheme(fschema2);
font2.getNameOrCharsetOrFamily().add(element15);
fonts.getFont().add(font2);
//</editor-fold>
ss.setFonts(fonts);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Fills">
CTFills fills = new CTFills();
fills.setCount(3L);
CTFill fill0 = new CTFill();
CTFill fill1 = new CTFill();
CTFill fill2 = new CTFill();
CTPatternFill patfill0 = new CTPatternFill();
patfill0.setPatternType(STPatternType.NONE);
fill0.setPatternFill(patfill0);
CTPatternFill patfill1 = new CTPatternFill();
patfill1.setPatternType(STPatternType.GRAY_125);
fill1.setPatternFill(patfill1);
CTPatternFill patfill2 = new CTPatternFill();
patfill2.setPatternType(STPatternType.SOLID);
CTColor color0 = new CTColor();
color0.setTheme(0L);
color0.setTint(-0.14999847407452621d);
CTColor color1 = new CTColor();
color1.setIndexed(64L);
patfill2.setFgColor(color0);
patfill2.setBgColor(color1);
fill2.setPatternFill(patfill2);
fills.getFill().add(fill0);
fills.getFill().add(fill1);
fills.getFill().add(fill2);
ss.setFills(fills);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Borders">
CTBorders borders = new CTBorders();
borders.setCount(1L);
CTBorder border = new CTBorder();
border.setLeft(new CTBorderPr());
border.setRight(new CTBorderPr());
border.setTop(new CTBorderPr());
border.setBottom(new CTBorderPr());
border.setDiagonal(new CTBorderPr());
borders.getBorder().add(border);
ss.setBorders(borders);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="XF Default">
CTCellStyleXfs xcfs = new CTCellStyleXfs();
xcfs.setCount(1L);
CTXf xcf = new CTXf();
xcf.setNumFmtId(0L);
xcf.setFontId(0L);
xcf.setFillId(0L);
xcf.setBorderId(0L);
xcfs.getXf().add(xcf);
ss.setCellStyleXfs(xcfs);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="XF Combinations">
CTCellXfs xfs = new CTCellXfs();
xfs.setCount(11L);
CTXf xf0 = new CTXf();
xf0.setNumFmtId(0L);
xf0.setFontId(0L);
xf0.setFillId(0L);
xf0.setBorderId(0L);
xf0.setXfId(0L);
xfs.getXf().add(xf0);
CTXf xf1 = new CTXf();
xf1.setNumFmtId(0L);
xf1.setFontId(1L);
xf1.setFillId(0L);
xf1.setBorderId(0L);
xf1.setXfId(0L);
xf1.setApplyFont(Boolean.TRUE);
xfs.getXf().add(xf1);
CTXf xf2 = new CTXf();
xf2.setNumFmtId(0L);
xf2.setFontId(1L);
xf2.setFillId(0L);
xf2.setBorderId(0L);
xf2.setXfId(0L);
xf2.setQuotePrefix(Boolean.TRUE);
xf2.setApplyFont(Boolean.TRUE);
xfs.getXf().add(xf2);
CTXf xf3 = new CTXf();
xf3.setNumFmtId(42L);
xf3.setFontId(0L);
xf3.setFillId(0L);
xf3.setBorderId(0L);
xf3.setXfId(0L);
xf3.setApplyNumberFormat(Boolean.TRUE);
xfs.getXf().add(xf3);
CTXf xf4 = new CTXf();
xf4.setNumFmtId(164L);
xf4.setFontId(0L);
xf4.setFillId(0L);
xf4.setBorderId(0L);
xf4.setXfId(0L);
xf4.setApplyNumberFormat(Boolean.TRUE);
xfs.getXf().add(xf4);
CTXf xf5 = new CTXf();
xf5.setNumFmtId(165L);
xf5.setFontId(0L);
xf5.setFillId(0L);
xf5.setBorderId(0L);
xf5.setXfId(0L);
xf5.setApplyNumberFormat(Boolean.TRUE);
xfs.getXf().add(xf5);
CTXf xf6 = new CTXf();
xf6.setNumFmtId(0L);
xf6.setFontId(1L);
xf6.setFillId(2L);
xf6.setBorderId(0L);
xf6.setXfId(0L);
xf6.setApplyFont(Boolean.TRUE);
xf6.setApplyFill(Boolean.TRUE);
xfs.getXf().add(xf6);
CTXf xf7 = new CTXf();
xf7.setNumFmtId(42L);
xf7.setFontId(0L);
xf7.setFillId(2L);
xf7.setBorderId(0L);
xf7.setXfId(0L);
xf7.setApplyNumberFormat(Boolean.TRUE);
xf7.setApplyFill(Boolean.TRUE);
xfs.getXf().add(xf7);
CTXf xf8 = new CTXf();
xf8.setNumFmtId(42L);
xf8.setFontId(1L);
xf8.setFillId(2L);
xf8.setBorderId(0L);
xf8.setXfId(0L);
xf8.setApplyNumberFormat(Boolean.TRUE);
xf8.setApplyFont(Boolean.TRUE);
xf8.setApplyFill(Boolean.TRUE);
xfs.getXf().add(xf8);
CTXf xf9 = new CTXf();
xf9.setNumFmtId(42L);
xf9.setFontId(1L);
xf9.setFillId(0L);
xf9.setBorderId(0L);
xf9.setXfId(0L);
xf9.setApplyNumberFormat(Boolean.TRUE);
xf9.setApplyFont(Boolean.TRUE);
xfs.getXf().add(xf9);
CTXf xf10 = new CTXf();
xf10.setNumFmtId(0L);
xf10.setFontId(2L);
xf10.setFillId(0L);
xf10.setBorderId(0L);
xf10.setXfId(0L);
xf10.setApplyFont(Boolean.TRUE);
xf10.setApplyAlignment(Boolean.TRUE);
CTCellAlignment align = new CTCellAlignment();
align.setHorizontal(STHorizontalAlignment.CENTER);
xf10.setAlignment(align);
xfs.getXf().add(xf10);
ss.setCellXfs(xfs);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Cell Styles">
CTCellStyles cstyles = new CTCellStyles();
cstyles.setCount(1L);
CTCellStyle cstyle = new CTCellStyle();
cstyle.setName("Standard");
cstyle.setXfId(0L);
cstyle.setBuiltinId(0L);
cstyles.getCellStyle().add(cstyle);
ss.setCellStyles(cstyles);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="DXFS">
CTDxfs dxfs = new CTDxfs();
dxfs.setCount(0L);
ss.setDxfs(dxfs);
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Table Styles">
CTTableStyles tstyles = new CTTableStyles();
tstyles.setCount(0L);
tstyles.setDefaultTableStyle("TableStyleMedium2");
tstyles.setDefaultPivotStyle("PivotStyleLight16");
ss.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);
ss.setExtLst(extlist);
//</editor-fold>
return ss;
}
//</editor-fold>
}
News
Site map
SitemapIndex
RSS Feed