You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by luke devon <lu...@yahoo.com> on 2010/04/13 07:45:32 UTC
Modify EXCEL work sheets from POI
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/
Re: Modify EXCEL work sheets from POI
Posted by MSB <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Modify EXCEL work sheets from POI
Posted by MSB <ma...@tiscali.co.uk>.
That is quite a long list there and some of them relate more to programming
technique than use of POI but they are all possible. It may be best to
tackle them one at a time to avoid your getting overwhelmed with the details
so here is one possible way to tackle removing the Summary sheet from a
workbook. This time, I have not written a completed class for you but simply
some code that you should be able to assemble into a class.
int sheetToDelete = -1;
java.io.File file = new File(".......");
java.io.FileInutStream fis = new FileInputStream(file);
org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);
fis.close();
int numSheets = workbook.getNumberOfSheets();
for(int i = 0; i < numSheets; i++) {
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt();
if(sheet.getSheetName().equals("Summary")) {
sheetToDelete = i;
break;
}
}
// The sheet may not have been found and in that case sheetToDelete
// will still hold minus one. Only if it holds a value greater than that
will
// the sheet have been found and we can delete it.
if(sheetToDelete > -1) {
workbook.removeSheetAt(sheetToDelete);
}
java.io.FileOutputStream fos = new FileOutputStream(file);
workbook.write(fos);
and that sort of thing should do the trick. Remember to test the code on a
copy of the workbook first just to make sure it behaves as you want.
Once you have this one sorted, let me know and we can tackle removing the
unwanted commas from those values. Before doing so however, I would ask you
use Excel and take a look at the cells containing the values in question to
see whether they do actually contain embedded commas or if someone has
simply applied a format to them so that the value appears like that.
Yours
Mark B
PS Make sure to spell the sheet's name correctly otherwise it will not be
found.
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-tp28226105p28231045.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