c# - NPOI Excel Format not working -

-

i have function generating excel file using npoi library. witch part working fine got problem style's seem function dodgy. focus number formats:

i decimals display as: 12.156.235,33 example

according reserach on google format should should be: "#,##0.00" value shows as: 12156235,33 , cell type set general instead of number

it looks alot of people have problems this. hope there solution cos else whanted managed npoi library , not whant switch now.

the procedure:

idataformat dataformat; public string generateexcel(exceldata data) {     var dateformat = dataformat.getformat("dd.mm.yyyy hh:mm:ss");     var decimalformat = dataformat.getformat("#,##0.00");             string xlspath, xlsfile, xlsname;     xlspath = environment.getenvironmentvariable("temp");     xlsname = path.getrandomfilename().replace(".", ""); //datetime.now.tostring("yyyy-mm-dd");     xlsname += ".xlsx";     xlsfile = path.combine(xlspath, xlsname);     if (file.exists(xlsfile))         file.delete(xlsfile);     workbook = new xssfworkbook();     dataformat = workbook.createdataformat();     xssfcellstyle style = (xssfcellstyle)workbook.createcellstyle();     npoi.ss.usermodel.icell cell;     dictionary<int, int> maxcolsize = new dictionary<int, int>();     string title;     int rr;     foreach (var sheet in data.sheets)     {         maxcolsize.clear();         if(sheet.hidden != null)             foreach (var h in sheet.hidden)             {                 sheet.hiderow(h);             }          worksheet = (xssfsheet)workbook.createsheet(sheet.title);         rr = 0;         if (sheet.titles!=null && sheet.titles.length > 0)         {             worksheet.createrow(rr);             rr++;             (int t = 0; t < sheet.titles.length; t++)             {                 title = (string)sheet.titles[t].title;                 maxcolsize.add(t, title.length);                 cell = worksheet.getrow(0).createcell(t, npoi.ss.usermodel.celltype.string);                 cell.setcellvalue(title);                 if (sheet.titles[t].style != null)                     cell.cellstyle = getcellstyle(sheet.titles[t].style);             }         }         int cols = 0;         int cc = 0;         irow row = null;         (int r = 0; r < sheet.rows.length; r++)         {             if (sheet.rows[r].row > -1)             {                 rr = sheet.rows[r].row;                 row = worksheet.getrow(sheet.rows[r].row);             }             else             {                 //rr = r;                 row = worksheet.getrow(rr);             }               if (row == null)             {                 row = worksheet.createrow(rr);             }                                 if(sheet.rows[r].cells.length > cols)                 cols = sheet.rows[r].cells.length;              (int c = 0; c < sheet.rows[r].cells.length;c++ )             {                   cc = c;                  if (sheet.rows[r].cells[c].column > -1)                 {                     cc = sheet.rows[r].cells[c].column;                 }                 if (!maxcolsize.keys.contains(cc))                     maxcolsize.add(cc, 0);                  switch (sheet.rows[r].cells[c].datatype)                 {                     case "datetime":                         cell = row.createcell(cc, npoi.ss.usermodel.celltype.numeric);                         cell.setcellvalue((datetime)sheet.rows[r].cells[c].value);                         cell.cellstyle.dataformat = dateformat;                         if (maxcolsize[cc] < cell.numericcellvalue.tostring().length)                             maxcolsize[cc] = cell.numericcellvalue.tostring().length;                         break;                     case "numeric":                     case "decimal":                         cell = row.createcell(cc, npoi.ss.usermodel.celltype.numeric);                         cell.setcellvalue(convert.todouble(sheet.rows[r].cells[c].value)); //sheet.rows[r].cells[c].value                         cell.cellstyle.dataformat = decimalformat;                         if (maxcolsize[cc] < cell.numericcellvalue.tostring().length)                             maxcolsize[cc] = cell.numericcellvalue.tostring().length;                         break;                     case "integer":                         cell = row.createcell(cc, npoi.ss.usermodel.celltype.numeric);                         cell.setcellvalue(convert.toint32(sheet.rows[r].cells[c].value));                         cell.cellstyle.dataformat = dataformat.getformat("0");                         if (maxcolsize[cc] < cell.numericcellvalue.tostring().length)                             maxcolsize[cc] = cell.numericcellvalue.tostring().length;                         break;                     case "date":                         cell = row.createcell(cc, npoi.ss.usermodel.celltype.numeric);                         cell.setcellvalue((datetime)sheet.rows[r].cells[c].value);                         cell.cellstyle.dataformat = dataformat.getformat("dd.mm.yyyy");                         if (maxcolsize[cc] < cell.numericcellvalue.tostring().length)                             maxcolsize[cc] = cell.numericcellvalue.tostring().length;                         break;                     case "boolean":                         cell = row.createcell(cc, npoi.ss.usermodel.celltype.boolean);                         cell.setcellvalue(convert.toboolean(sheet.rows[r].cells[c].value));                         if (maxcolsize[cc] < cell.booleancellvalue.tostring().length)                             maxcolsize[cc] = cell.stringcellvalue.tostring().length;                         break;                     case "formula":                         cell = row.createcell(cc, npoi.ss.usermodel.celltype.formula);                         cell.setcellformula((string)(sheet.rows[r].cells[c].value));                         if (maxcolsize[cc] < cell.booleancellvalue.tostring().length)                             maxcolsize[cc] = cell.stringcellvalue.tostring().length;                         break;                     default:                         cell = row.createcell(cc, npoi.ss.usermodel.celltype.string);                         cell.setcellvalue((string)sheet.rows[r].cells[c].value);                         if (maxcolsize[cc] < cell.stringcellvalue.length)                             maxcolsize[cc] = cell.stringcellvalue.length;                         break;                 }                 if (sheet.rows[r].cells[c].style != null)                 {                     var st = getcellstyle(sheet.rows[r].cells[c].style);                                                    cell.cellstyle = st;                 }              }               if (sheet.rows[r].row == -1)                 rr++;              if(sheet.merge != null)                 foreach (var merge in sheet.merge)                 {                     worksheet.addmergedregion( new npoi.ss.util.cellrangeaddress(merge.firstrow,merge.lastrow, merge.firstcolumn, merge.lastcolumn));                 }                             }          foreach (var col in maxcolsize)         {             worksheet.setcolumnwidth(col.key, ((int)(col.value * 1.14388)) * 256);         }         xssfformulaevaluator.evaluateallformulacells(workbook);     }     using (var fs = new filestream(xlsfile, filemode.create, fileaccess.write))     {         workbook.write(fs);     }      return xlsfile; }


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5);
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:
Read more

Python Pig Latin Translator -

-
so have been working on @ codecademy.com while trying learn python. don't know if blind , can't see why won't work or what. here's code won't let me use. function print variable if had typed in , print "empty" if didn't type anything. print "welcome english pig latin translator!" original = raw_input("what's name?") if len(original) > 0 print original else print "empty" if len(original) > 0 else the above lines contains syntax errors. try: if len(original) > 0: else: note added colons, ( : ).
Read more