openxml - Different excel column appearance when working with open xml -
this question edited several times , found problem , solution. edited post other users use.
created excel file using open xml. created excel file opens no errors. has following problems:
- the size of column set correctly, shown bigger expected ( bigger template file).
below image shows template file:
, next image shows created file using open xml :
see,the columns of second file wider, while there no difference between values.
edit
the code begins following lines:
using openxml=documentformat.openxml; using excelspreadsheet = documentformat.openxml.spreadsheet; using packaging = documentformat.openxml.packaging;
packaging.spreadsheetdocument spreadsheet = packaging.spreadsheetdocument.create(filename, documentformat.openxml.spreadsheetdocumenttype.workbook); packaging.extendedfilepropertiespart extendedfilepropertiespart = spreadsheet.addextendedfilepropertiespart(); generateextendedfilepropertiespart(extendedfilepropertiespart); packaging.corefilepropertiespart corefilepropertiespart = spreadsheet.addcorefilepropertiespart(); generatecorefilepropertiespart(corefilepropertiespart); packaging.workbookpart workbookpart = spreadsheet.addworkbookpart(); workbookpart.workbook = new excelspreadsheet.workbook(); //setting style part setsheetstyle(workbookpart); //generated using xml tools packaging.themepart themepart = workbookpart.addnewpart<packaging.themepart>(); generatethemepart(themepart); packaging.worksheetpart worksheetpart = workbookpart.addnewpart<packaging.worksheetpart>(); worksheetpart.worksheet = new excelspreadsheet.worksheet(); excelspreadsheet.sheetdimension sheetdimension1 = new excelspreadsheet.sheetdimension() { reference = "a12:aa17" }; worksheetpart.worksheet.append(sheetdimension1); excelspreadsheet.columns columns = new excelspreadsheet.columns(); (uint col = 0; col < propertiesofcolumns.length; col++) { uint32 rangeindex = col + 1; excelspreadsheet.column column1 = new excelspreadsheet.column() { min = (openxml.uint32value)rangeindex, max = (openxml.uint32value)rangeindex, width = propertiesofcolumns[col].width,//width of every column customwidth = openxml.booleanvalue.fromboolean(true) }; columns.append(column1); } worksheetpart.worksheet.append(columns); excelspreadsheet.sheetdata sheetdata = new excelspreadsheet.sheetdata(); worksheetpart.worksheet.append(sheetdata); excelspreadsheet.pagemargins pagemargins1 = new excelspreadsheet.pagemargins() { left = 0.7d, right = 0.7d, top = 0.75d, bottom = 0.75d, header = 0.3d, footer = 0.3d }; worksheetpart.worksheet.append(pagemargins1); excelspreadsheet.sheets sheets = workbookpart.workbook.appendchild<excelspreadsheet.sheets>(new excelspreadsheet.sheets()); excelspreadsheet.sheet sheet = new excelspreadsheet.sheet() { id = workbookpart.getidofpart(worksheetpart), sheetid = 1, name = "mysheet" }; sheets.append(sheet); worksheetpart.worksheet.sheetviews = new excelspreadsheet.sheetviews(); excelspreadsheet.sheetview sheetview = new excelspreadsheet.sheetview() { righttoleft = true, tabselected = true, zoomscale=(openxml.uint32value)60u, zoomscalenormal=(openxml.uint32value)60u, workbookviewid = (documentformat.openxml.uint32value)0u }; worksheetpart.worksheet.sheetviews.append(sheetview); //and on
i more suspicious setsheetstyle:
private static void setsheetstyle(packaging.workbookpart workbookpart ) { //fonts excelspreadsheet.fonts fonts = new excelspreadsheet.fonts() { count = (openxml.uint32value)2u, knownfonts = true }; excelspreadsheet.font font1 = new excelspreadsheet.font(); excelspreadsheet.bold bold1 = new excelspreadsheet.bold(); excelspreadsheet.fontsize fontsize1 = new excelspreadsheet.fontsize() { val = 16d }; excelspreadsheet.color color1 = new excelspreadsheet.color() { theme = (openxml.uint32value)1u }; excelspreadsheet.fontname fontname1 = new excelspreadsheet.fontname() { val = "b nazanin" }; excelspreadsheet.fontfamilynumbering fontfamilynumbering1 = new excelspreadsheet.fontfamilynumbering() { val = 2 }; excelspreadsheet.fontscheme fontscheme1 = new excelspreadsheet.fontscheme() { val = excelspreadsheet.fontschemevalues.minor }; font1.append(fontsize1); font1.append(bold1); font1.append(color1); font1.append(fontname1); font1.append(fontfamilynumbering1); font1.append(fontscheme1); excelspreadsheet.font font2 = new excelspreadsheet.font(); excelspreadsheet.bold bold2 = new excelspreadsheet.bold(); excelspreadsheet.fontsize fontsize2 = new excelspreadsheet.fontsize() { val = 11d }; excelspreadsheet.color color2 = new excelspreadsheet.color() { theme = (openxml.uint32value)1u }; excelspreadsheet.fontname fontname2 = new excelspreadsheet.fontname() { val = "calibri" }; excelspreadsheet.fontfamilynumbering fontfamilynumbering2 = new excelspreadsheet.fontfamilynumbering() { val = 2 }; excelspreadsheet.fontscheme fontscheme2 = new excelspreadsheet.fontscheme() { val = excelspreadsheet.fontschemevalues.minor }; //font2.append(bold1); font2.append(fontsize2); font2.append(color2); font2.append(fontname2); font2.append(fontfamilynumbering2); font2.append(fontscheme2); fonts.append(font1); fonts.append(font2); //fills excelspreadsheet.fills fills = new excelspreadsheet.fills() { count = (openxml.uint32value)3u }; excelspreadsheet.fill fill1 = new excelspreadsheet.fill(); excelspreadsheet.patternfill patternfill1 = new excelspreadsheet.patternfill() { patterntype = excelspreadsheet.patternvalues.none }; fill1.append(patternfill1); excelspreadsheet.fill fill2 = new excelspreadsheet.fill(); excelspreadsheet.patternfill patternfill2 = new excelspreadsheet.patternfill() { patterntype = excelspreadsheet.patternvalues.gray125 }; fill2.append(patternfill2); excelspreadsheet.fill fill3 = new excelspreadsheet.fill(); excelspreadsheet.patternfill patternfill3 = new excelspreadsheet.patternfill() { patterntype = excelspreadsheet.patternvalues.solid }; excelspreadsheet.foregroundcolor foregroundcolor1 = new excelspreadsheet.foregroundcolor() { rgb = "ffffcc66" }; excelspreadsheet.backgroundcolor backgroundcolor1 = new excelspreadsheet.backgroundcolor() { indexed = (openxml.uint32value)64u }; patternfill3.append(foregroundcolor1); patternfill3.append(backgroundcolor1); fill3.append(patternfill3); fills.append(fill1); fills.append(fill2); fills.append(fill3); //borders excelspreadsheet.borders borders = new excelspreadsheet.borders() { count = (openxml.uint32value)2u }; excelspreadsheet.border border1 = new excelspreadsheet.border(); excelspreadsheet.leftborder leftborder1 = new excelspreadsheet.leftborder(); excelspreadsheet.rightborder rightborder1 = new excelspreadsheet.rightborder(); excelspreadsheet.topborder topborder1 = new excelspreadsheet.topborder(); excelspreadsheet.bottomborder bottomborder1 = new excelspreadsheet.bottomborder(); excelspreadsheet.diagonalborder diagonalborder1 = new excelspreadsheet.diagonalborder(); border1.append(leftborder1); border1.append(rightborder1); border1.append(topborder1); border1.append(bottomborder1); border1.append(diagonalborder1); excelspreadsheet.border border2 = new excelspreadsheet.border(); excelspreadsheet.leftborder leftborder2 = new excelspreadsheet.leftborder() { style = excelspreadsheet.borderstylevalues.thin }; color1 = new excelspreadsheet.color() { indexed = (openxml.uint32value)64u }; leftborder2.append(color1); excelspreadsheet.rightborder rightborder2 = new excelspreadsheet.rightborder() { style = excelspreadsheet.borderstylevalues.thin }; color2 = new excelspreadsheet.color() { indexed = (openxml.uint32value)64u }; rightborder2.append(color2); excelspreadsheet.topborder topborder2 = new excelspreadsheet.topborder() { style = excelspreadsheet.borderstylevalues.thin }; excelspreadsheet.color color3 = new excelspreadsheet.color() { indexed = (openxml.uint32value)64u }; topborder2.append(color3); excelspreadsheet.bottomborder bottomborder2 = new excelspreadsheet.bottomborder(); excelspreadsheet.diagonalborder diagonalborder2 = new excelspreadsheet.diagonalborder(); border2.append(leftborder2); border2.append(rightborder2); border2.append(topborder2); border2.append(bottomborder2); border2.append(diagonalborder2); borders.append(border1); borders.append(border2); excelspreadsheet.cellformats cellformats = new excelspreadsheet.cellformats() { count = (openxml.uint32value)3u }; //default cell format (not applied) excelspreadsheet.cellformat cellformat1 = new excelspreadsheet.cellformat() { numberformatid = (openxml.uint32value)0u, fontid = (openxml.uint32value)0u, fillid = (openxml.uint32value)0u, borderid = (openxml.uint32value)0u, formatid = (openxml.uint32value)0u }; //style header excelspreadsheet.cellformat cellformat2 = new excelspreadsheet.cellformat() { numberformatid = (openxml.uint32value)0u, fontid = (openxml.uint32value)0u, fillid = (openxml.uint32value)2u, borderid = (openxml.uint32value)1u, formatid = (openxml.uint32value)0u, applyfont = true, applyfill = true, applyborder = true, applyalignment=true }; excelspreadsheet.alignment alignment1 = new excelspreadsheet.alignment() { horizontal = excelspreadsheet.horizontalalignmentvalues.center, vertical = excelspreadsheet.verticalalignmentvalues.center, wraptext = true }; cellformat2.append(alignment1); //style rows excelspreadsheet.cellformat cellformat3 = new excelspreadsheet.cellformat() { numberformatid = (openxml.uint32value)0u, fontid = (openxml.uint32value)1u, fillid = (openxml.uint32value)0u, borderid = (openxml.uint32value)1u, formatid = (openxml.uint32value)0u, applyfont = true, applyborder = true }; cellformats.append(cellformat1); cellformats.append(cellformat2); cellformats.append(cellformat3); //cell style excelspreadsheet.cellstyles cellstyles = new excelspreadsheet.cellstyles() { count = (openxml.uint32value)2u }; excelspreadsheet.cellstyle cellstyle1 = new excelspreadsheet.cellstyle() { name = "normal", formatid = (openxml.uint32value)0u, builtinid = (openxml.uint32value)0u }; cellstyles.append(cellstyle1); //excelspreadsheet.cellstyle cellstyle2 = new excelspreadsheet.cellstyle() { name = "normal", formatid = (openxml.uint32value)3u, builtinid = (openxml.uint32value)0u }; //cellstyles.append(cellstyle2); workbookpart.addnewpart<packaging.workbookstylespart>(); workbookpart.workbookstylespart.stylesheet = new excelspreadsheet.stylesheet(); excelspreadsheet.stylesheet stylesheet1 = workbookpart.workbookstylespart.stylesheet; stylesheet1.append(fonts); stylesheet1.append(fills); stylesheet1.append(borders); stylesheet1.append(cellformats); stylesheet1.append(cellstyles); //.append(stylesheet1); workbookpart.workbookstylespart.stylesheet.save(); }
i found clue 1 of questions. second problem because cell style set have different font . here link me make code. font of column heading , measurement units , rulers in excel
change following code set font of cellstyle:
excelspreadsheet.cellstyle cellstyle1 = new excelspreadsheet.cellstyle() { name = "normal", formatid = (openxml.uint32value)1u, builtinid = (openxml.uint32value)0u };
i set formatid = (openxml.uint32value)1u means set second font("calibri 11") cell style .
Comments
Post a Comment