You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by KonstantinD <kd...@gmail.com> on 2010/05/10 23:48:54 UTC
Problems setting currency format
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-tp28517432p28517432.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Creating a general Word Document only from Java code. HWPDocument help.
Posted by David Fisher <df...@jmlafferty.com>.
Please do not hijack threads.
Please start by asking a single question in a new thread.
For example you ask about fonts, please tell us what you see and where those fonts are installed on your system.
Thanks,
Regards,
Dave
On May 19, 2010, at 12:46 AM, Zachary Mitchell wrote:
> If I am to go:
>
>
> //*********************************************************************
> import ...;
>
> int colorOne = 7;
> int ftcfe = 9;
>
> HWPFDocument document = new HWPFDocument();
> FontTable fontTable = document.getFontTable();
> List fontList = Arrays.asList(fontTable.getFontNames()); //This is how to wrap a primitive array.
> ListIterator fonts = fontList.iterator();
>
> while(fonts.hasNext())
> {
> System.out.println(next.toString()); //A
> }
>
> Range range = document.getOverallRange();
> CharacterRun runOne = range.getCharacterRun(0); //B
> CharacterRun runTwo = runOne.insertAfter("This is some initial Text");
> runTwo.setBold(true);
> runTwo.setColor(colorOne); //C
> runTwo.setFontSize(16);
> runTwo.setFtcFE(ftcfe); //D
> ...
>
> document.write(new FileOutputStream(new File("WordFile1.doc")));
> //*********************************************************************
>
>
> -A: if done with an initially empty constructor HWPFDocument, will this print
> all of the TrueType Font names visible through the JVM from the particular OS?
> Will the index of a particular font be the int value for use in //D ?
>
> -B: Is this how one creates consecutive new CharacterRuns? Can you create your own
> CharacterRuns within a Range stipulating a start index?
> What happens with how CharacterRuns end?
>
> -C: Should be a simple one; where are the names/indicies for color values here?
>
> -D: If there are TTF names and indicies at //A, is this the index data needed here at //D ?
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Creating a general Word Document only from Java code. HWPDocument help.
Posted by Zachary Mitchell <za...@internode.on.net>.
If I am to go:
//*********************************************************************
import ...;
int colorOne = 7;
int ftcfe = 9;
HWPFDocument document = new HWPFDocument();
FontTable fontTable = document.getFontTable();
List fontList = Arrays.asList(fontTable.getFontNames()); //This is how to
wrap a primitive array.
ListIterator fonts = fontList.iterator();
while(fonts.hasNext())
{
System.out.println(next.toString()); //A
}
Range range = document.getOverallRange();
CharacterRun runOne = range.getCharacterRun(0); //B
CharacterRun runTwo = runOne.insertAfter("This is some initial Text");
runTwo.setBold(true);
runTwo.setColor(colorOne); //C
runTwo.setFontSize(16);
runTwo.setFtcFE(ftcfe); //D
...
document.write(new FileOutputStream(new File("WordFile1.doc")));
//*********************************************************************
-A: if done with an initially empty constructor HWPFDocument, will this
print
all of the TrueType Font names visible through the JVM from the
particular OS?
Will the index of a particular font be the int value for use in //D ?
-B: Is this how one creates consecutive new CharacterRuns? Can you create
your own
CharacterRuns within a Range stipulating a start index?
What happens with how CharacterRuns end?
-C: Should be a simple one; where are the names/indicies for color values
here?
-D: If there are TTF names and indicies at //A, is this the index data
needed here at //D ?
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Problems setting currency format
Posted by MSB <ma...@tiscali.co.uk>.
There is one potential problem that you could face. If you take a style from
an existing cell and modify it before applying it to another cell, then the
changes you make will be retrospective. That is tpo say, the changes will
apply to every cell the style is applied to.
If you want to take an existing style, modify it slightly - for example
change the background or foreground colour - and then apply it to another
cell, tale a look at the cloneStyleFrom() method that is defined on the
org.apache.poi.ss.usermodel.CellStyle interface. It allows you to take an
existing cell style, perform a deep copy of it, modify the copy and then
apply that to another cell.
Hope this helps.
Yours
Mark B
KonstantinD wrote:
>
> Thank you for your fast responses!
>
> I wish the problem was as simple as the column not being wide enough :)
> But thank you guys for pointing this out, it made me realize that I should
> be automatically resizing those columns.
>
> The reason I wasn't copying the whole format of the first cell was because
> that first cell has some additional formatting (such as background color
> and text rotation).
>
> But after I spent too much time on trying to get just the format itself,
> Mark, I think I'm going to follow your advice, get the complete style of
> the first cell in the column but set the correct rotation and color.
>
> Thank you very much.
>
> Sincerely,
>
> Konstantin.
>
>
>
> MSB wrote:
>>
>> 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:markbrdsly@tiscali.co.uk]
>>> Sent: Tuesday, May 11, 2010 1:54 AM
>>> To: user@poi.apache.org
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Problems-setting-currency-format-tp28517432p28525943.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Problems setting currency format
Posted by KonstantinD <kd...@gmail.com>.
Thank you for your fast responses!
I wish the problem was as simple as the column not being wide enough :)
But thank you guys for pointing this out, it made me realize that I should
be automatically resizing those columns.
The reason I wasn't copying the whole format of the first cell was because
that first cell has some additional formatting (such as background color and
text rotation).
But after I spent too much time on trying to get just the format itself,
Mark, I think I'm going to follow your advice, get the complete style of the
first cell in the column but set the correct rotation and color.
Thank you very much.
Sincerely,
Konstantin.
MSB wrote:
>
> 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:markbrdsly@tiscali.co.uk]
>> Sent: Tuesday, May 11, 2010 1:54 AM
>> To: user@poi.apache.org
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Problems-setting-currency-format-tp28517432p28524767.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Problems setting currency format
Posted by MSB <ma...@tiscali.co.uk>.
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:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, May 11, 2010 1:54 AM
> To: user@poi.apache.org
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Problems setting currency format
Posted by "Szostak, Jonathan" <jo...@jackson.com>.
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:markbrdsly@tiscali.co.uk]
Sent: Tuesday, May 11, 2010 1:54 AM
To: user@poi.apache.org
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problems setting currency format
Posted by MSB <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org