I will be away from the PC all day today as I am working well up in the North
of the neighbouring county to create a series of ponds. So, firstly, do not
be surprised if I cannot respond to emails and, secondlym you will need to
do some digging around in the worksheets using Excel to try and
isolate/identify certain features about those numeric values the contain the
thousand separator, the comma.
Those value could be either a string of text such as 1,400 that a yser has
entered into a cell or they could be the result of a cell format applied to
a cell that holds a numeric value. If you can identify what you are dealing
with them this will determine the code you write to correct the issue.
Further, if there is a pattern to the columns these values are in, that will
help us a great deal - for example see if they are only occurring in columns
D, G and H. It is easier to confine the testing we will need to apply to
certain columns if this is possible.
Also, is it really necessary to actually modify the workbook in this case?
It would be quite easy to catch this sort of 'problem' and correct it before
the values were written into the CSV file.
You should be able to write the code to remove the Sum row from the
worksheet. All you will need to do here is;
Open the workbook.
Recover the number of sheets in the workbook.
Create a for loop to iterate through the sheets one at a time and within
that loop.
Get a sheet.
Recover the number of rows on the sheet.
Create another for loop to iterate through the rows on the sheet and for
each row.
Get the cell in column H - remember that POI uses indexes that start
from zero and
that the index for cell H will therefore be 7.
If the cell contains the String 'Sum'
Make a note of the row number.
Break out of the for loop - and there are a number of ways to do
this. You can
either use the keyword 'break' or simply set the set the loop
terminating value
so that it ends.
Remove the row from the worksheet.
Save the modified workbook.
In many ways, the techniques is very similar to that used to remove the
Summary sheet from the workbook, all we have done is stepped two 'levels'
further down, from sheet to row and from row to cell. If there are many Sum
rows on a single sheet, it should be possible to search for and remove all
of them before re-saving the workbook.
Yours
Mark B
PS to compare the value in cell H, you will need to get it's contents as a
String (have a look at the documentation for the Cell class and specifically
the methods available that begin with the word get). Once you have the cells
contents as a String, you cannot use the == operator to test them but must
use the equals() method. Again, there should be an example in the code that
removes the sheet.
Luke_Devon wrote:
>
> Hi ,
>
>
> As per the POI API, I
> think it is possible to modify EXCEL work sheets as well. My new
> requirement I
> would like to describe you.
>
> 1. Before convert into CSV, there are few
> modifications to be done to the EXCEL sheets.
>
> 2.
>
> Cell C
>
> Country Unit
> ------- ----
> aaa a
> b
> c
> d
> |
> |
> n
>
> Country field always in column “C”. It is constant.
> As I mentioned in the sketch, there is "n" number of values in the
> UNITS field. But in the country field, "aaa" won’t repeat until “n”
> number of units terminates. Country will display only once. I need to fill
> other cells in country columns until end of n number of units. Like this,
> there
> are more countries to be filled in the worksheet. All are align on CELLC.
>
> --------------------
> Cell H
>
> Based on Cell H there is a value called
> "Sum”. Once this value found entire ROWneeded to be removed
> from the work sheet.
>
> -------------------------------------
> In the work sheet, there is few values with
> comma. Eg: 1,300. These values are randomly located in the work sheet. I
> wanted
> to remove ","(comma) from the values. Eg: 1300
>
> ------------------------------------------------------
> And also in the worksheet there is 2 sheets
> are available. One is "summery" other one is "Detail". I
> wanted to remove "Summery" sheet.
> I would like to make sure , can we do such modifications via POI ? If it
> is possible can I see some examples ?
>
> As I introduced my self , I am not a software engineer. but kind of a
> developer with PERL , PHP and shell scripting. I used those scripting
> languages to automate some process in UNIX boxes. Since I am a Telecom
> systems engineer i would be able to do such little developments.but very
> frankly I am not a hardcore java developer. But my requirement is based on
> java right now.
>
>
> Your help very much appreciate.
>
> Thanks in advance
> Luke
>
>
>
> Get your new Email address!
> Grab the Email name you've always wanted before someone else does!
> http://mail.promotions.yahoo.com/newdomains/aa/
>
--
View this message in context:
http://old.nabble.com/Modify-EXCEL-work-sheets-from-POI-tp28226105p28238690.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]
