Thought of that as well Jonathan and was just about to post but you beat me to it - wood and trees!
Still cannot see why you need to create any formats though if you are merely populating the template file. Ideally, you should just be copying the formats from the cells you created on the template. Yours Mark B Szostak, Jonathan wrote: > > I don't want to insult your intelligence, but is the column wide enough to > display numbers over 10,000? > > -----Original Message----- > From: MSB [mailto:[email protected]] > Sent: Tuesday, May 11, 2010 1:54 AM > To: [email protected] > Subject: Re: Problems setting currency format > > > If you are copying the format for the cell from another cell that you have > already created in your template, is it not simply a matter of doing that, > getting the format applied to the template cell and then applying it to > the cell you are inserting? That code would look something like this; > > I am not sure what your processing cycle looks like but I am going to > assume that as you populate a row in the worksheet with data, you simply > look into the same column in the previous row to retrieve a cell whose > formatting you can copy. If this is the case, then the code could look > somthing like this; > > // assume I am inserting a new row number 10 // and a cell into column 5. > int rowNumIndex = 9; > int newCellIndex = 4; > > Row newRow = sheet.createRow(rowNumIndex); Cell newCell = > newRow.createCell(newCellIndex); newCell.setCellValue(100000.00); > > // Get a reference to the a cell in the same column but the previous row - > this assumes that // the previous row was populated but that is a detail > that depends upon how you are // processing the CSV file I suppose. You > could change this to alwasy look into the row // you created using Excel > when you built your template, if this row was the second row // on the > worksheet, you could even hard code that row index, a little like this; // > newCell.setCellStyle(sheet.getRow(1).getCell(newCellIndex).getCellStyle()); > newCell.setCellStyle(sheet.getRow(newRowIndex - > 1).getCell(newCellIndex).getCellStyle()); > > Not very neat but all it does is apply to all cells in a specific column, > the same cell style. I have not tried this on 'running' code but it should > be easy enough to test quite quickly and assuming the format applied to > the cell in your template works then it should be copied into those cells > you create. > > Yours > > Mark B > > > > KonstantinD wrote: >> >> Hi All, >> >> I'm creating xlsx workbook using XSSF and having issues assigning the >> currency format to a cell. >> My problem happens if the number is greater than 10,000.00. I get >> ######## instead of the number. >> If I click on that cell, I get the correct number, but I don't want to >> explain that to my clients :) If the number is less than 10k >> everything works perfectly. >> >> I have an Excel template which I'm populating with values from the CSV >> file. >> In that template all I have is a row with column headers. >> I'm setting a currency format to one of the columns in the template >> and then reading that format in my program. I then assign that format >> to each cell in that column. >> >> I've tried using Excel's default currency format : $#,##0.00 and I've >> tried changing it to allow greater numbers: $###,##0.00. But with >> both of them I get the same problem if the number is greater than 10k. >> >> Here's the code that I'm using to set the format: >> XSSFCellStyle style = wb.createCellStyle(); CreationHelper >> createHelper = wb.getCreationHelper(); String dFormat = >> sheet.getRow(1).getCell(col).getCellStyle().getDataFormatString(); >> >> style.setDataFormat(createHelper.createDataFormat().getFormat(dFormat) >> ); cell.setCellValue(Double.parseDouble(value)); >> cell.setCellStyle(style); >> >> I would greatly appreciate any help with this. >> >> Sincerely, >> >> Konstantin. >> > > -- > View this message in context: > http://old.nabble.com/Problems-setting-currency-format-tp28517432p28520194.html > Sent from the POI - User mailing list archive at Nabble.com. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > -- View this message in context: http://old.nabble.com/Problems-setting-currency-format-tp28517432p28523616.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
