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:

  1. the size of column set correctly, shown bigger expected ( bigger template file).
    below image shows template file: the template excel file
    , next image shows created file using open xml :
    enter image description here
    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

Popular posts from this blog

how to proxy from https to http with lighttpd -

android - Automated my builds -

python - Flask migration error -