You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by njr30 <nj...@yahoo.com> on 2009/10/22 06:01:31 UTC
Setting the row number to 3 while inserting values in different
cells !!
Hi,
I am writing some data to Excel using POI where I have to reset the row
number to 3 as I complete inserting values in different cells.
How do I do that ?
Thanks,
njr30
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26003733.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by MSB <ma...@tiscali.co.uk>.
Hello Jag,
Not good news - no body should be allowed a holiday.
Anyway, the best thing to do - at least in my opinion - is for us to work
together and create the code to accomplish this task for you. Hopefully, it
will not be too hard and we will need to unpick some of the work your
colleague has already done to ensure that the completed report conforms with
your companies standards re formatting etc. As this discussion is moving
away being a simple POI question, it may also be best if we continued the
discussion off list; if you agree, simply reply on the following address -
markbrdsly@tiscali.co.uk - when you provide the answers to these first few
questions.
If I remember correctly, the report consists of data arranged into a series
of columns and each column also has a heading. Can you tell me please;
Are you creating a brand new Excel workbook, are you opening and populating
a template or opening and modifying an existing workbook/spreadsheet?
Which version of Excel are you targetting? Are you creating a binary (.xls)
file or an OpenXML (.xlsx) file? If you are not sure, take a look through
your colleagues code and you will see they are using classes from either the
HSSF stream or the XSSF one. They may have written the code to be indepedent
of a specific type in which case you will see import statements like this -
'import org.apache.poi.ss.usermodel.Workbook' - somewhere in the code.
Are the column headings known in advance or are they a part of the data set
that the application will receive?
The values that must be written into the columns, are they always numeric
values or can we expect to deal with strings of text, numbers, dates,
formulae, etc?
What format do you receieve these values in? Are they, for example,
contained in an array of Strings?
Are the values that will be written into the cells formatted in any way? By
this, I mean should negative numbers appear in red with a leading minus
sign?
Do you have on hand a completed version of the resport that I could take a
look at? If you do, you will need to clear it with your bosses and make sure
that the report does not contain any sensitive information. All I am looking
for is an idea of the layout or appearance of the completed report so you do
not even need to include 'live' or real data. As long as each column
contains the sort of information you would expect to see, looking the way it
should then I will be able to work with that.
What I am aiming for at this initial stage is some code that you can call to
populate columns with data; my first thought is a method with arguments to
accept an array containing the data for the column, a number indicating
which column is being created and possibly formatting information as well
either for each cell or all of the cells in the column. To use the code, I
can see s process where you create the workbook, insert a new sheet and
possibly populate the row of column headers. Then, as your code becomes
aware of the need to insert a new column, you call the method described
above before finally saving the file away.
That should do for now, doubtless there will be other questions as the work
continues and I will be asking you also to look at your colleagues work to
see what they have done and how POI has been used. I reckon that we will be
able to come up with something that will work for you; quick and dirty it
may well be but it will also work properly.
njr30 wrote:
>
> Hi Mark,
>
> Thanks a lot for your advise.
>
> The person who wrote the class is with the company but he is away and I
> have to complete this report ASAP, hence this hurry.
>
> For a minute let us say we are going to add more methods to the helper
> class and they will be fresh methods which will insert rows column by
> column as opposed to row by row. If we say that then would you be able to
> assist to get this going and later on once i know the whole deal I will
> make some adjustments.
>
> Do you know what we need to do to make this work Column by Column Insert ?
> The idea is by hook or crook to make it work.
>
> If not please dont worry. Thanks a lot again for your assistance.
>
> Regards
> Jag
>
>
> MSB wrote:
>>
>> While I cannot be certain from the code you have posted, I do not think
>> that the helper class will suit your purposes here. From what I have
>> seen, it has been created to populate the worksheet one row at a time and
>> cannot be used to populate it one column at a time without making
>> modifications.
>>
>> Does the person who wrote the helper class still work for your company?
>> If so, you will be best talking to them to see what changes must be made
>> to convert from a row to a column based approach. If they do not still
>> work for the company, is there someone who has already made use of the
>> helper class and with whom you are able to discuss your requirement?
>>
>> Sorry that I cannot offer any more constructive assistance.
>>
>> Yours
>>
>> Mark B
>>
>>
>> njr30 wrote:
>>>
>>> Hi Mark,
>>>
>>> Thanks a lot.
>>>
>>> Here is the helper class I am using and the following is my class method
>>> trying to access the helper class to create worksheet, row, empty cells
>>> etc etc ..
>>>
>>> for (int k = 0; k < modifyEventList.size(); k++) {
>>>
>>> if (k == 0) {
>>>
>>> poi.createSheet("Modify_Event");
>>> poi.newRow();
>>> poi.createCenteredCell("Change Log Report");
>>> poi.newRow();
>>> poi.newRow();
>>> poi.createHeaderCell("Event Update Date");
>>> poi.createHeaderCell("Event Code");
>>> poi.createHeaderCell("Event Name");
>>> poi.createHeaderCell("Event Status");
>>> poi.createHeaderCell("Event Version");
>>> poi.createHeaderCell("Change Reason");
>>> poi.createHeaderCell("Planners Name");
>>> poi.createHeaderCell("User");
>>> poi.createHeaderCell("General Information Tab");
>>> poi.createHeaderCell("Forecasting Tab");
>>> poi.createHeaderCell("Stationery Tab");
>>> poi.createHeaderCell("Datamail Tab");
>>> poi.createHeaderCell("Output Quality Tab");
>>> poi.createHeaderCell("Support Tab");
>>> poi.createHeaderCell("Review Tab");
>>>
>>> }
>>>
>>> int eventId = modifyEventList.get(k).getEventId();
>>>
>>>
>>> if (eventId != previousEventId) {
>>>
>>> if (previousEventId > 0) {
>>> poi.addColumnBorders(eventFirstRow,
>>> poi.getRowIndex(), FIRST_COLUMN, LAST_COLUMN);
>>> }
>>> previousEventId = eventId;
>>> eventFirstRow = poi.getRowIndex() + 1;
>>> }
>>>
>>>
>>> poi.newRow();
>>>
>>> if (modifyEventList.get(k).getTabId() == 0) {
>>>
>>> tabOneCount = 0;
>>> tabTwoCount = 0;
>>> tabThreeCount = 0;
>>> tabFourCount = 0;
>>> tabFiveCount = 0;
>>> tabSixCount = 0;
>>> tabSevenCount = 0;
>>>
>>>
>>> poi.createCenteredCell(modifyEventList.get(k).getUpdateDate());
>>> poi.createCell(modifyEventList.get(k).getEventCode());
>>> poi.createCell(modifyEventList.get(k).getEventName());
>>> poi.createCell(modifyEventList.get(k).getEventStatus());
>>> poi.createCell(modifyEventList.get(k).getEventVersion());
>>> poi.createCell(modifyEventList.get(k).getChangeReason());
>>> poi.createCell(modifyEventList.get(k).getPlannerName());
>>> poi.createCell(modifyEventList.get(k).getUpdateUser());
>>>
>>> }else if (modifyEventList.get(k).getTabId() == 1) {
>>> poi.newRow();
>>> poi.createEmptyCells(8);
>>> poi.createCell(modifyEventList.get(k).getOldValue());
>>> poi.newRow();
>>> poi.createEmptyCells(8);
>>> poi.createCell(modifyEventList.get(k).getNewValue());
>>> tabOneCount++;
>>>
>>> }else if (modifyEventList.get(k).getTabId() == 2) {
>>> poi.newRow();
>>> poi.createEmptyCells(9);
>>> poi.createCell(modifyEventList.get(k).getOldValue());
>>> poi.newRow();
>>> poi.createEmptyCells(9);
>>> poi.createCell(modifyEventList.get(k).getNewValue());
>>> tabTwoCount++;
>>>
>>> }else if (modifyEventList.get(k).getTabId() == 3) {
>>>
>>> poi.newRow();
>>> poi.createEmptyCells(10);
>>> poi.createCell(modifyEventList.get(k).getOldValue());
>>> poi.newRow();
>>> poi.createEmptyCells(10);
>>> poi.createCell(modifyEventList.get(k).getNewValue());
>>> tabThreeCount++;
>>>
>>> }else if (modifyEventList.get(k).getTabId() == 4) {
>>> poi.newRow();
>>> poi.createEmptyCells(11);
>>> poi.createCell(modifyEventList.get(k).getOldValue());
>>> poi.newRow();
>>> poi.createEmptyCells(11);
>>> poi.createCell(modifyEventList.get(k).getNewValue());
>>> tabFourCount++;
>>>
>>> }else if (modifyEventList.get(k).getTabId() == 5) {
>>> poi.newRow();
>>> poi.createEmptyCells(12);
>>> poi.createCell(modifyEventList.get(k).getOldValue());
>>> poi.newRow();
>>> poi.createEmptyCells(12);
>>> poi.createCell(modifyEventList.get(k).getNewValue());
>>> tabFiveCount++;
>>>
>>> }else if (modifyEventList.get(k).getTabId() == 6) {
>>> poi.newRow();
>>> poi.createEmptyCells(13);
>>> poi.createCell(modifyEventList.get(k).getOldValue());
>>> poi.newRow();
>>> poi.createEmptyCells(13);
>>> poi.createCell(modifyEventList.get(k).getNewValue());
>>> tabSixCount++;
>>>
>>> }else if (modifyEventList.get(k).getTabId() == 7) {
>>> poi.newRow();
>>> poi.createEmptyCells(14);
>>> poi.createCell(modifyEventList.get(k).getOldValue());
>>> poi.newRow();
>>> poi.createEmptyCells(14);
>>> poi.createCell(modifyEventList.get(k).getNewValue());
>>> tabSevenCount++;
>>> }
>>> }
>>>
>>> poi.autoSizeColumn(0); // Event Update Date
>>> poi.setColumnWidth(1, 250); // Event Code
>>> poi.setColumnWidth(2, 500); // Event Name
>>> poi.setColumnWidth(3, 200); // Event Status
>>> poi.setColumnWidth(4, 200); // Event Version
>>> poi.setColumnWidth(5, 1000); // Change Reason
>>> poi.setColumnWidth(6, 300); // Planners Name
>>> poi.setColumnWidth(7, 200); // Event Update User
>>> poi.setColumnWidth(8, 500); // General Information Tab
>>> poi.setColumnWidth(9, 500); // Forecasting Tab
>>> poi.setColumnWidth(10, 500); // Stationery Tab
>>> poi.setColumnWidth(11, 500); // Datamail Tab
>>> poi.setColumnWidth(12, 500); // Output Quality Tab
>>> poi.setColumnWidth(13, 500); // Support Tab
>>> poi.setColumnWidth(14, 500); // Review Tab
>>>
>>>
>>>
>>>
>>> }
>>>
>>>
>>>
>>>
>>> Would you be able to assist ?
>>>
>>> Regards
>>> Jagannath
>>>
>>>
>>>
>>>
>>> MSB wrote:
>>>>
>>>> Sorry, I just assumed that you were using the API directly. Can you
>>>> post the code from this helper class here? I suspect there will be
>>>> commercial constraints and am certain that you will need to discuss
>>>> this with your manager but we may be able to ffer more help if you
>>>> could do this?
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> njr30 wrote:
>>>>>
>>>>> Hi Mark,
>>>>>
>>>>> Thanks for your help and sample code.
>>>>>
>>>>> The thing is I am using a helper class which is already existing in
>>>>> our code base to create sheets, rows etc. I am not able to figure out
>>>>> what is happening which is not able to set the row number to 3 at the
>>>>> start of populating each of the cell.
>>>>>
>>>>> Ya you are right there are constraints with the data set where in I am
>>>>> not getting the value of entire row as a record. I have to populate
>>>>> cell by cell due to that.
>>>>>
>>>>> I may have to debug more and see what is happening why the row number
>>>>> is not getting initialised to 3.
>>>>>
>>>>> Thanks&Regards
>>>>> Jag
>>>>>
>>>>>
>>>>> MSB wrote:
>>>>>>
>>>>>> Okay, I have had a look at the example and think I can see where the
>>>>>> confusion arises. What you need to do is either keep track of the
>>>>>> rows using a Collection - an ArrayList for example - or interrogate
>>>>>> the Sheet object for the row so that you can add onto it. Of the two
>>>>>> options, the latter is by far the better one IMO as the Sheet object
>>>>>> is already storing the Row references for you internally in a list
>>>>>> structure.
>>>>>>
>>>>>> Obviously, I do not know what triggers the requirement to add a new
>>>>>> cell onto a row (to jump from populating cell A6 on your example to
>>>>>> populating cell B2), only you can be fully aware of your algorithm,
>>>>>> but you need to basically do something like the following (and I am
>>>>>> assuming that you are targetting the HSSF stream here;
>>>>>>
>>>>>> HSSFWorkbook workbook = new HSSFWorkbook();
>>>>>> HSSFSheet sheet = workbook.createSheet();
>>>>>> // Create the first row.
>>>>>> HSSFRow row = sheet.createRow(0);
>>>>>> // Create the first cell on the row
>>>>>> HSSFCell cell = row.createCell(0);
>>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>>> cell.setCellValue(10);
>>>>>> // Now, repeat to create the first column full of cells
>>>>>> row = sheet.createRow(1);
>>>>>> cell = row.createCell(0);
>>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>>> cell.setCellValue(20);
>>>>>> row = sheet.createRow(2);
>>>>>> cell = row.createCell(0);
>>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>>> cell.setCellValue(30);
>>>>>> row = sheet.createRow(3);
>>>>>> cell = row.createCell(0);
>>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>>> cell.setCellValue(40);
>>>>>> // Assuming that you have filled the first column and want to create
>>>>>> the next cell
>>>>>> // as cell B1 - the second cell on the first row - the trick is to
>>>>>> recover the reference
>>>>>> // for the first row from the sheet like this;
>>>>>> row = sheet.getRow(0);
>>>>>> // and then you can add a cell to the row
>>>>>> cell = row.createCell(1);
>>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>>> cell.setCellValue(400);
>>>>>>
>>>>>> and so on.....
>>>>>>
>>>>>> One extra wrinkle is to test the value returned by the call to the
>>>>>> getRow() method. If that method call returns a null value, then you
>>>>>> will need to create the row, something a little like this;
>>>>>>
>>>>>> row = sheet.getRow(5);
>>>>>> if(row == null) {
>>>>>> row = sheet.createRow(5);
>>>>>> }
>>>>>>
>>>>>> The other option would be to get your data set and then populate each
>>>>>> row completely but this may not be possible; only you can know the
>>>>>> constraints imposed by the data set.
>>>>>>
>>>>>> Hope this helps.
>>>>>>
>>>>>> Yours
>>>>>>
>>>>>> Mark B
>>>>>>
>>>>>>
>>>>>> njr30 wrote:
>>>>>>>
>>>>>>> Hi Mark,
>>>>>>>
>>>>>>> Thanks for the reply.
>>>>>>>
>>>>>>> I am reading the data from db and writing the data on to spreadsheet
>>>>>>> using POI.
>>>>>>>
>>>>>>> I have attached a sample spreadsheet with this post.
>>>>>>>
>>>>>>> I have got 7 cells where the data has to be written. I first fill up
>>>>>>> cell 1 then cell2 and so on.
>>>>>>>
>>>>>>> Say for eg one cell one data was laid out for over 5 rows. When I
>>>>>>> start writing to cell 2 it starts writing from row 6 but i want it
>>>>>>> to start again from row 1 same with the rest.
>>>>>>>
>>>>>>> In the attached spreadsheet i have 2 worksheets wrong and right.
>>>>>>> wrong is what the pgm is doing at the moment and the right has the
>>>>>>> way data has to be laid out. Can you kindly
>>>>>>> http://www.nabble.com/file/p26012375/sample.xls sample.xls assist.
>>>>>>>
>>>>>>> Thanx a lot in advance.
>>>>>>>
>>>>>>>
>>>>>>> Thanks&Regards
>>>>>>> Jag
>>>>>>>
>>>>>>>
>>>>>>> MSB wrote:
>>>>>>>>
>>>>>>>> Sorry, but I am not competely clear what you are asking.
>>>>>>>>
>>>>>>>> Are you saying that you have a worksheet and use POI to open it,
>>>>>>>> access a number of cells on the sheet that could each be on a
>>>>>>>> different row, change the value in each of those cells and then
>>>>>>>> re-position each onto row 3 of the worksheet?
>>>>>>>>
>>>>>>>> Yours
>>>>>>>>
>>>>>>>> Mark B
>>>>>>>>
>>>>>>>>
>>>>>>>> njr30 wrote:
>>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> I am writing some data to Excel using POI where I have to reset
>>>>>>>>> the row number to 3 as I complete inserting values in different
>>>>>>>>> cells.
>>>>>>>>>
>>>>>>>>> How do I do that ?
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>> njr30
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>> http://www.nabble.com/file/p26034390/POIHelper.java POIHelper.java
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26037782.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by njr30 <nj...@yahoo.com>.
Hi Mark,
Thanks a lot for your advise.
The person who wrote the class is with the company but he is away and I have
to complete this report ASAP, hence this hurry.
For a minute let us say we are going to add more methods to the helper class
and they will be fresh methods which will insert rows column by column as
opposed to row by row. If we say that then would you be able to assist to
get this going and later on once i know the whole deal I will make some
adjustments.
Do you know what we need to do to make this work Column by Column Insert ?
The idea is by hook or crook to make it work.
If not please dont worry. Thanks a lot again for your assistance.
Regards
Jag
MSB wrote:
>
> While I cannot be certain from the code you have posted, I do not think
> that the helper class will suit your purposes here. From what I have seen,
> it has been created to populate the worksheet one row at a time and cannot
> be used to populate it one column at a time without making modifications.
>
> Does the person who wrote the helper class still work for your company? If
> so, you will be best talking to them to see what changes must be made to
> convert from a row to a column based approach. If they do not still work
> for the company, is there someone who has already made use of the helper
> class and with whom you are able to discuss your requirement?
>
> Sorry that I cannot offer any more constructive assistance.
>
> Yours
>
> Mark B
>
>
> njr30 wrote:
>>
>> Hi Mark,
>>
>> Thanks a lot.
>>
>> Here is the helper class I am using and the following is my class method
>> trying to access the helper class to create worksheet, row, empty cells
>> etc etc ..
>>
>> for (int k = 0; k < modifyEventList.size(); k++) {
>>
>> if (k == 0) {
>>
>> poi.createSheet("Modify_Event");
>> poi.newRow();
>> poi.createCenteredCell("Change Log Report");
>> poi.newRow();
>> poi.newRow();
>> poi.createHeaderCell("Event Update Date");
>> poi.createHeaderCell("Event Code");
>> poi.createHeaderCell("Event Name");
>> poi.createHeaderCell("Event Status");
>> poi.createHeaderCell("Event Version");
>> poi.createHeaderCell("Change Reason");
>> poi.createHeaderCell("Planners Name");
>> poi.createHeaderCell("User");
>> poi.createHeaderCell("General Information Tab");
>> poi.createHeaderCell("Forecasting Tab");
>> poi.createHeaderCell("Stationery Tab");
>> poi.createHeaderCell("Datamail Tab");
>> poi.createHeaderCell("Output Quality Tab");
>> poi.createHeaderCell("Support Tab");
>> poi.createHeaderCell("Review Tab");
>>
>> }
>>
>> int eventId = modifyEventList.get(k).getEventId();
>>
>>
>> if (eventId != previousEventId) {
>>
>> if (previousEventId > 0) {
>> poi.addColumnBorders(eventFirstRow,
>> poi.getRowIndex(), FIRST_COLUMN, LAST_COLUMN);
>> }
>> previousEventId = eventId;
>> eventFirstRow = poi.getRowIndex() + 1;
>> }
>>
>>
>> poi.newRow();
>>
>> if (modifyEventList.get(k).getTabId() == 0) {
>>
>> tabOneCount = 0;
>> tabTwoCount = 0;
>> tabThreeCount = 0;
>> tabFourCount = 0;
>> tabFiveCount = 0;
>> tabSixCount = 0;
>> tabSevenCount = 0;
>>
>>
>> poi.createCenteredCell(modifyEventList.get(k).getUpdateDate());
>> poi.createCell(modifyEventList.get(k).getEventCode());
>> poi.createCell(modifyEventList.get(k).getEventName());
>> poi.createCell(modifyEventList.get(k).getEventStatus());
>> poi.createCell(modifyEventList.get(k).getEventVersion());
>> poi.createCell(modifyEventList.get(k).getChangeReason());
>> poi.createCell(modifyEventList.get(k).getPlannerName());
>> poi.createCell(modifyEventList.get(k).getUpdateUser());
>>
>> }else if (modifyEventList.get(k).getTabId() == 1) {
>> poi.newRow();
>> poi.createEmptyCells(8);
>> poi.createCell(modifyEventList.get(k).getOldValue());
>> poi.newRow();
>> poi.createEmptyCells(8);
>> poi.createCell(modifyEventList.get(k).getNewValue());
>> tabOneCount++;
>>
>> }else if (modifyEventList.get(k).getTabId() == 2) {
>> poi.newRow();
>> poi.createEmptyCells(9);
>> poi.createCell(modifyEventList.get(k).getOldValue());
>> poi.newRow();
>> poi.createEmptyCells(9);
>> poi.createCell(modifyEventList.get(k).getNewValue());
>> tabTwoCount++;
>>
>> }else if (modifyEventList.get(k).getTabId() == 3) {
>>
>> poi.newRow();
>> poi.createEmptyCells(10);
>> poi.createCell(modifyEventList.get(k).getOldValue());
>> poi.newRow();
>> poi.createEmptyCells(10);
>> poi.createCell(modifyEventList.get(k).getNewValue());
>> tabThreeCount++;
>>
>> }else if (modifyEventList.get(k).getTabId() == 4) {
>> poi.newRow();
>> poi.createEmptyCells(11);
>> poi.createCell(modifyEventList.get(k).getOldValue());
>> poi.newRow();
>> poi.createEmptyCells(11);
>> poi.createCell(modifyEventList.get(k).getNewValue());
>> tabFourCount++;
>>
>> }else if (modifyEventList.get(k).getTabId() == 5) {
>> poi.newRow();
>> poi.createEmptyCells(12);
>> poi.createCell(modifyEventList.get(k).getOldValue());
>> poi.newRow();
>> poi.createEmptyCells(12);
>> poi.createCell(modifyEventList.get(k).getNewValue());
>> tabFiveCount++;
>>
>> }else if (modifyEventList.get(k).getTabId() == 6) {
>> poi.newRow();
>> poi.createEmptyCells(13);
>> poi.createCell(modifyEventList.get(k).getOldValue());
>> poi.newRow();
>> poi.createEmptyCells(13);
>> poi.createCell(modifyEventList.get(k).getNewValue());
>> tabSixCount++;
>>
>> }else if (modifyEventList.get(k).getTabId() == 7) {
>> poi.newRow();
>> poi.createEmptyCells(14);
>> poi.createCell(modifyEventList.get(k).getOldValue());
>> poi.newRow();
>> poi.createEmptyCells(14);
>> poi.createCell(modifyEventList.get(k).getNewValue());
>> tabSevenCount++;
>> }
>> }
>>
>> poi.autoSizeColumn(0); // Event Update Date
>> poi.setColumnWidth(1, 250); // Event Code
>> poi.setColumnWidth(2, 500); // Event Name
>> poi.setColumnWidth(3, 200); // Event Status
>> poi.setColumnWidth(4, 200); // Event Version
>> poi.setColumnWidth(5, 1000); // Change Reason
>> poi.setColumnWidth(6, 300); // Planners Name
>> poi.setColumnWidth(7, 200); // Event Update User
>> poi.setColumnWidth(8, 500); // General Information Tab
>> poi.setColumnWidth(9, 500); // Forecasting Tab
>> poi.setColumnWidth(10, 500); // Stationery Tab
>> poi.setColumnWidth(11, 500); // Datamail Tab
>> poi.setColumnWidth(12, 500); // Output Quality Tab
>> poi.setColumnWidth(13, 500); // Support Tab
>> poi.setColumnWidth(14, 500); // Review Tab
>>
>>
>>
>>
>> }
>>
>>
>>
>>
>> Would you be able to assist ?
>>
>> Regards
>> Jagannath
>>
>>
>>
>>
>> MSB wrote:
>>>
>>> Sorry, I just assumed that you were using the API directly. Can you post
>>> the code from this helper class here? I suspect there will be commercial
>>> constraints and am certain that you will need to discuss this with your
>>> manager but we may be able to ffer more help if you could do this?
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> njr30 wrote:
>>>>
>>>> Hi Mark,
>>>>
>>>> Thanks for your help and sample code.
>>>>
>>>> The thing is I am using a helper class which is already existing in
>>>> our code base to create sheets, rows etc. I am not able to figure out
>>>> what is happening which is not able to set the row number to 3 at the
>>>> start of populating each of the cell.
>>>>
>>>> Ya you are right there are constraints with the data set where in I am
>>>> not getting the value of entire row as a record. I have to populate
>>>> cell by cell due to that.
>>>>
>>>> I may have to debug more and see what is happening why the row number
>>>> is not getting initialised to 3.
>>>>
>>>> Thanks&Regards
>>>> Jag
>>>>
>>>>
>>>> MSB wrote:
>>>>>
>>>>> Okay, I have had a look at the example and think I can see where the
>>>>> confusion arises. What you need to do is either keep track of the rows
>>>>> using a Collection - an ArrayList for example - or interrogate the
>>>>> Sheet object for the row so that you can add onto it. Of the two
>>>>> options, the latter is by far the better one IMO as the Sheet object
>>>>> is already storing the Row references for you internally in a list
>>>>> structure.
>>>>>
>>>>> Obviously, I do not know what triggers the requirement to add a new
>>>>> cell onto a row (to jump from populating cell A6 on your example to
>>>>> populating cell B2), only you can be fully aware of your algorithm,
>>>>> but you need to basically do something like the following (and I am
>>>>> assuming that you are targetting the HSSF stream here;
>>>>>
>>>>> HSSFWorkbook workbook = new HSSFWorkbook();
>>>>> HSSFSheet sheet = workbook.createSheet();
>>>>> // Create the first row.
>>>>> HSSFRow row = sheet.createRow(0);
>>>>> // Create the first cell on the row
>>>>> HSSFCell cell = row.createCell(0);
>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>> cell.setCellValue(10);
>>>>> // Now, repeat to create the first column full of cells
>>>>> row = sheet.createRow(1);
>>>>> cell = row.createCell(0);
>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>> cell.setCellValue(20);
>>>>> row = sheet.createRow(2);
>>>>> cell = row.createCell(0);
>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>> cell.setCellValue(30);
>>>>> row = sheet.createRow(3);
>>>>> cell = row.createCell(0);
>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>> cell.setCellValue(40);
>>>>> // Assuming that you have filled the first column and want to create
>>>>> the next cell
>>>>> // as cell B1 - the second cell on the first row - the trick is to
>>>>> recover the reference
>>>>> // for the first row from the sheet like this;
>>>>> row = sheet.getRow(0);
>>>>> // and then you can add a cell to the row
>>>>> cell = row.createCell(1);
>>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>>> cell.setCellValue(400);
>>>>>
>>>>> and so on.....
>>>>>
>>>>> One extra wrinkle is to test the value returned by the call to the
>>>>> getRow() method. If that method call returns a null value, then you
>>>>> will need to create the row, something a little like this;
>>>>>
>>>>> row = sheet.getRow(5);
>>>>> if(row == null) {
>>>>> row = sheet.createRow(5);
>>>>> }
>>>>>
>>>>> The other option would be to get your data set and then populate each
>>>>> row completely but this may not be possible; only you can know the
>>>>> constraints imposed by the data set.
>>>>>
>>>>> Hope this helps.
>>>>>
>>>>> Yours
>>>>>
>>>>> Mark B
>>>>>
>>>>>
>>>>> njr30 wrote:
>>>>>>
>>>>>> Hi Mark,
>>>>>>
>>>>>> Thanks for the reply.
>>>>>>
>>>>>> I am reading the data from db and writing the data on to spreadsheet
>>>>>> using POI.
>>>>>>
>>>>>> I have attached a sample spreadsheet with this post.
>>>>>>
>>>>>> I have got 7 cells where the data has to be written. I first fill up
>>>>>> cell 1 then cell2 and so on.
>>>>>>
>>>>>> Say for eg one cell one data was laid out for over 5 rows. When I
>>>>>> start writing to cell 2 it starts writing from row 6 but i want it to
>>>>>> start again from row 1 same with the rest.
>>>>>>
>>>>>> In the attached spreadsheet i have 2 worksheets wrong and right.
>>>>>> wrong is what the pgm is doing at the moment and the right has the
>>>>>> way data has to be laid out. Can you kindly
>>>>>> http://www.nabble.com/file/p26012375/sample.xls sample.xls assist.
>>>>>>
>>>>>> Thanx a lot in advance.
>>>>>>
>>>>>>
>>>>>> Thanks&Regards
>>>>>> Jag
>>>>>>
>>>>>>
>>>>>> MSB wrote:
>>>>>>>
>>>>>>> Sorry, but I am not competely clear what you are asking.
>>>>>>>
>>>>>>> Are you saying that you have a worksheet and use POI to open it,
>>>>>>> access a number of cells on the sheet that could each be on a
>>>>>>> different row, change the value in each of those cells and then
>>>>>>> re-position each onto row 3 of the worksheet?
>>>>>>>
>>>>>>> Yours
>>>>>>>
>>>>>>> Mark B
>>>>>>>
>>>>>>>
>>>>>>> njr30 wrote:
>>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I am writing some data to Excel using POI where I have to reset the
>>>>>>>> row number to 3 as I complete inserting values in different cells.
>>>>>>>>
>>>>>>>> How do I do that ?
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> njr30
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>> http://www.nabble.com/file/p26034390/POIHelper.java POIHelper.java
>>
>
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26036734.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by MSB <ma...@tiscali.co.uk>.
While I cannot be certain from the code you have posted, I do not think that
the helper class will suit your purposes here. From what I have seen, it has
been created to populate the worksheet one row at a time and cannot be used
to populate it one column at a time without making modifications.
Does the person who wrote the helper class still work for your company? If
so, you will be best talking to them to see what changes must be made to
convert from a row to a column based approach. If they do not still work for
the company, is there someone who has already made use of the helper class
and with whom you are able to discuss your requirement?
Sorry that I cannot offer any more constructive assistance.
Yours
Mark B
njr30 wrote:
>
> Hi Mark,
>
> Thanks a lot.
>
> Here is the helper class I am using and the following is my class method
> trying to access the helper class to create worksheet, row, empty cells
> etc etc ..
>
> for (int k = 0; k < modifyEventList.size(); k++) {
>
> if (k == 0) {
>
> poi.createSheet("Modify_Event");
> poi.newRow();
> poi.createCenteredCell("Change Log Report");
> poi.newRow();
> poi.newRow();
> poi.createHeaderCell("Event Update Date");
> poi.createHeaderCell("Event Code");
> poi.createHeaderCell("Event Name");
> poi.createHeaderCell("Event Status");
> poi.createHeaderCell("Event Version");
> poi.createHeaderCell("Change Reason");
> poi.createHeaderCell("Planners Name");
> poi.createHeaderCell("User");
> poi.createHeaderCell("General Information Tab");
> poi.createHeaderCell("Forecasting Tab");
> poi.createHeaderCell("Stationery Tab");
> poi.createHeaderCell("Datamail Tab");
> poi.createHeaderCell("Output Quality Tab");
> poi.createHeaderCell("Support Tab");
> poi.createHeaderCell("Review Tab");
>
> }
>
> int eventId = modifyEventList.get(k).getEventId();
>
>
> if (eventId != previousEventId) {
>
> if (previousEventId > 0) {
> poi.addColumnBorders(eventFirstRow, poi.getRowIndex(),
> FIRST_COLUMN, LAST_COLUMN);
> }
> previousEventId = eventId;
> eventFirstRow = poi.getRowIndex() + 1;
> }
>
>
> poi.newRow();
>
> if (modifyEventList.get(k).getTabId() == 0) {
>
> tabOneCount = 0;
> tabTwoCount = 0;
> tabThreeCount = 0;
> tabFourCount = 0;
> tabFiveCount = 0;
> tabSixCount = 0;
> tabSevenCount = 0;
>
>
> poi.createCenteredCell(modifyEventList.get(k).getUpdateDate());
> poi.createCell(modifyEventList.get(k).getEventCode());
> poi.createCell(modifyEventList.get(k).getEventName());
> poi.createCell(modifyEventList.get(k).getEventStatus());
> poi.createCell(modifyEventList.get(k).getEventVersion());
> poi.createCell(modifyEventList.get(k).getChangeReason());
> poi.createCell(modifyEventList.get(k).getPlannerName());
> poi.createCell(modifyEventList.get(k).getUpdateUser());
>
> }else if (modifyEventList.get(k).getTabId() == 1) {
> poi.newRow();
> poi.createEmptyCells(8);
> poi.createCell(modifyEventList.get(k).getOldValue());
> poi.newRow();
> poi.createEmptyCells(8);
> poi.createCell(modifyEventList.get(k).getNewValue());
> tabOneCount++;
>
> }else if (modifyEventList.get(k).getTabId() == 2) {
> poi.newRow();
> poi.createEmptyCells(9);
> poi.createCell(modifyEventList.get(k).getOldValue());
> poi.newRow();
> poi.createEmptyCells(9);
> poi.createCell(modifyEventList.get(k).getNewValue());
> tabTwoCount++;
>
> }else if (modifyEventList.get(k).getTabId() == 3) {
>
> poi.newRow();
> poi.createEmptyCells(10);
> poi.createCell(modifyEventList.get(k).getOldValue());
> poi.newRow();
> poi.createEmptyCells(10);
> poi.createCell(modifyEventList.get(k).getNewValue());
> tabThreeCount++;
>
> }else if (modifyEventList.get(k).getTabId() == 4) {
> poi.newRow();
> poi.createEmptyCells(11);
> poi.createCell(modifyEventList.get(k).getOldValue());
> poi.newRow();
> poi.createEmptyCells(11);
> poi.createCell(modifyEventList.get(k).getNewValue());
> tabFourCount++;
>
> }else if (modifyEventList.get(k).getTabId() == 5) {
> poi.newRow();
> poi.createEmptyCells(12);
> poi.createCell(modifyEventList.get(k).getOldValue());
> poi.newRow();
> poi.createEmptyCells(12);
> poi.createCell(modifyEventList.get(k).getNewValue());
> tabFiveCount++;
>
> }else if (modifyEventList.get(k).getTabId() == 6) {
> poi.newRow();
> poi.createEmptyCells(13);
> poi.createCell(modifyEventList.get(k).getOldValue());
> poi.newRow();
> poi.createEmptyCells(13);
> poi.createCell(modifyEventList.get(k).getNewValue());
> tabSixCount++;
>
> }else if (modifyEventList.get(k).getTabId() == 7) {
> poi.newRow();
> poi.createEmptyCells(14);
> poi.createCell(modifyEventList.get(k).getOldValue());
> poi.newRow();
> poi.createEmptyCells(14);
> poi.createCell(modifyEventList.get(k).getNewValue());
> tabSevenCount++;
> }
> }
>
> poi.autoSizeColumn(0); // Event Update Date
> poi.setColumnWidth(1, 250); // Event Code
> poi.setColumnWidth(2, 500); // Event Name
> poi.setColumnWidth(3, 200); // Event Status
> poi.setColumnWidth(4, 200); // Event Version
> poi.setColumnWidth(5, 1000); // Change Reason
> poi.setColumnWidth(6, 300); // Planners Name
> poi.setColumnWidth(7, 200); // Event Update User
> poi.setColumnWidth(8, 500); // General Information Tab
> poi.setColumnWidth(9, 500); // Forecasting Tab
> poi.setColumnWidth(10, 500); // Stationery Tab
> poi.setColumnWidth(11, 500); // Datamail Tab
> poi.setColumnWidth(12, 500); // Output Quality Tab
> poi.setColumnWidth(13, 500); // Support Tab
> poi.setColumnWidth(14, 500); // Review Tab
>
>
>
>
> }
>
>
>
>
> Would you be able to assist ?
>
> Regards
> Jagannath
>
>
>
>
> MSB wrote:
>>
>> Sorry, I just assumed that you were using the API directly. Can you post
>> the code from this helper class here? I suspect there will be commercial
>> constraints and am certain that you will need to discuss this with your
>> manager but we may be able to ffer more help if you could do this?
>>
>> Yours
>>
>> Mark B
>>
>>
>> njr30 wrote:
>>>
>>> Hi Mark,
>>>
>>> Thanks for your help and sample code.
>>>
>>> The thing is I am using a helper class which is already existing in our
>>> code base to create sheets, rows etc. I am not able to figure out what
>>> is happening which is not able to set the row number to 3 at the start
>>> of populating each of the cell.
>>>
>>> Ya you are right there are constraints with the data set where in I am
>>> not getting the value of entire row as a record. I have to populate cell
>>> by cell due to that.
>>>
>>> I may have to debug more and see what is happening why the row number is
>>> not getting initialised to 3.
>>>
>>> Thanks&Regards
>>> Jag
>>>
>>>
>>> MSB wrote:
>>>>
>>>> Okay, I have had a look at the example and think I can see where the
>>>> confusion arises. What you need to do is either keep track of the rows
>>>> using a Collection - an ArrayList for example - or interrogate the
>>>> Sheet object for the row so that you can add onto it. Of the two
>>>> options, the latter is by far the better one IMO as the Sheet object is
>>>> already storing the Row references for you internally in a list
>>>> structure.
>>>>
>>>> Obviously, I do not know what triggers the requirement to add a new
>>>> cell onto a row (to jump from populating cell A6 on your example to
>>>> populating cell B2), only you can be fully aware of your algorithm, but
>>>> you need to basically do something like the following (and I am
>>>> assuming that you are targetting the HSSF stream here;
>>>>
>>>> HSSFWorkbook workbook = new HSSFWorkbook();
>>>> HSSFSheet sheet = workbook.createSheet();
>>>> // Create the first row.
>>>> HSSFRow row = sheet.createRow(0);
>>>> // Create the first cell on the row
>>>> HSSFCell cell = row.createCell(0);
>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>> cell.setCellValue(10);
>>>> // Now, repeat to create the first column full of cells
>>>> row = sheet.createRow(1);
>>>> cell = row.createCell(0);
>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>> cell.setCellValue(20);
>>>> row = sheet.createRow(2);
>>>> cell = row.createCell(0);
>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>> cell.setCellValue(30);
>>>> row = sheet.createRow(3);
>>>> cell = row.createCell(0);
>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>> cell.setCellValue(40);
>>>> // Assuming that you have filled the first column and want to create
>>>> the next cell
>>>> // as cell B1 - the second cell on the first row - the trick is to
>>>> recover the reference
>>>> // for the first row from the sheet like this;
>>>> row = sheet.getRow(0);
>>>> // and then you can add a cell to the row
>>>> cell = row.createCell(1);
>>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>> cell.setCellValue(400);
>>>>
>>>> and so on.....
>>>>
>>>> One extra wrinkle is to test the value returned by the call to the
>>>> getRow() method. If that method call returns a null value, then you
>>>> will need to create the row, something a little like this;
>>>>
>>>> row = sheet.getRow(5);
>>>> if(row == null) {
>>>> row = sheet.createRow(5);
>>>> }
>>>>
>>>> The other option would be to get your data set and then populate each
>>>> row completely but this may not be possible; only you can know the
>>>> constraints imposed by the data set.
>>>>
>>>> Hope this helps.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> njr30 wrote:
>>>>>
>>>>> Hi Mark,
>>>>>
>>>>> Thanks for the reply.
>>>>>
>>>>> I am reading the data from db and writing the data on to spreadsheet
>>>>> using POI.
>>>>>
>>>>> I have attached a sample spreadsheet with this post.
>>>>>
>>>>> I have got 7 cells where the data has to be written. I first fill up
>>>>> cell 1 then cell2 and so on.
>>>>>
>>>>> Say for eg one cell one data was laid out for over 5 rows. When I
>>>>> start writing to cell 2 it starts writing from row 6 but i want it to
>>>>> start again from row 1 same with the rest.
>>>>>
>>>>> In the attached spreadsheet i have 2 worksheets wrong and right. wrong
>>>>> is what the pgm is doing at the moment and the right has the way data
>>>>> has to be laid out. Can you kindly
>>>>> http://www.nabble.com/file/p26012375/sample.xls sample.xls assist.
>>>>>
>>>>> Thanx a lot in advance.
>>>>>
>>>>>
>>>>> Thanks&Regards
>>>>> Jag
>>>>>
>>>>>
>>>>> MSB wrote:
>>>>>>
>>>>>> Sorry, but I am not competely clear what you are asking.
>>>>>>
>>>>>> Are you saying that you have a worksheet and use POI to open it,
>>>>>> access a number of cells on the sheet that could each be on a
>>>>>> different row, change the value in each of those cells and then
>>>>>> re-position each onto row 3 of the worksheet?
>>>>>>
>>>>>> Yours
>>>>>>
>>>>>> Mark B
>>>>>>
>>>>>>
>>>>>> njr30 wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I am writing some data to Excel using POI where I have to reset the
>>>>>>> row number to 3 as I complete inserting values in different cells.
>>>>>>>
>>>>>>> How do I do that ?
>>>>>>>
>>>>>>> Thanks,
>>>>>>> njr30
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
> http://www.nabble.com/file/p26034390/POIHelper.java POIHelper.java
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26036598.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by njr30 <nj...@yahoo.com>.
Hi Mark,
Thanks a lot.
Here is the helper class I am using and the following is my class method
trying to access the helper class to create worksheet, row, empty cells etc
etc ..
for (int k = 0; k < modifyEventList.size(); k++) {
if (k == 0) {
poi.createSheet("Modify_Event");
poi.newRow();
poi.createCenteredCell("Change Log Report");
poi.newRow();
poi.newRow();
poi.createHeaderCell("Event Update Date");
poi.createHeaderCell("Event Code");
poi.createHeaderCell("Event Name");
poi.createHeaderCell("Event Status");
poi.createHeaderCell("Event Version");
poi.createHeaderCell("Change Reason");
poi.createHeaderCell("Planners Name");
poi.createHeaderCell("User");
poi.createHeaderCell("General Information Tab");
poi.createHeaderCell("Forecasting Tab");
poi.createHeaderCell("Stationery Tab");
poi.createHeaderCell("Datamail Tab");
poi.createHeaderCell("Output Quality Tab");
poi.createHeaderCell("Support Tab");
poi.createHeaderCell("Review Tab");
}
int eventId = modifyEventList.get(k).getEventId();
if (eventId != previousEventId) {
if (previousEventId > 0) {
poi.addColumnBorders(eventFirstRow, poi.getRowIndex(),
FIRST_COLUMN, LAST_COLUMN);
}
previousEventId = eventId;
eventFirstRow = poi.getRowIndex() + 1;
}
poi.newRow();
if (modifyEventList.get(k).getTabId() == 0) {
tabOneCount = 0;
tabTwoCount = 0;
tabThreeCount = 0;
tabFourCount = 0;
tabFiveCount = 0;
tabSixCount = 0;
tabSevenCount = 0;
poi.createCenteredCell(modifyEventList.get(k).getUpdateDate());
poi.createCell(modifyEventList.get(k).getEventCode());
poi.createCell(modifyEventList.get(k).getEventName());
poi.createCell(modifyEventList.get(k).getEventStatus());
poi.createCell(modifyEventList.get(k).getEventVersion());
poi.createCell(modifyEventList.get(k).getChangeReason());
poi.createCell(modifyEventList.get(k).getPlannerName());
poi.createCell(modifyEventList.get(k).getUpdateUser());
}else if (modifyEventList.get(k).getTabId() == 1) {
poi.newRow();
poi.createEmptyCells(8);
poi.createCell(modifyEventList.get(k).getOldValue());
poi.newRow();
poi.createEmptyCells(8);
poi.createCell(modifyEventList.get(k).getNewValue());
tabOneCount++;
}else if (modifyEventList.get(k).getTabId() == 2) {
poi.newRow();
poi.createEmptyCells(9);
poi.createCell(modifyEventList.get(k).getOldValue());
poi.newRow();
poi.createEmptyCells(9);
poi.createCell(modifyEventList.get(k).getNewValue());
tabTwoCount++;
}else if (modifyEventList.get(k).getTabId() == 3) {
poi.newRow();
poi.createEmptyCells(10);
poi.createCell(modifyEventList.get(k).getOldValue());
poi.newRow();
poi.createEmptyCells(10);
poi.createCell(modifyEventList.get(k).getNewValue());
tabThreeCount++;
}else if (modifyEventList.get(k).getTabId() == 4) {
poi.newRow();
poi.createEmptyCells(11);
poi.createCell(modifyEventList.get(k).getOldValue());
poi.newRow();
poi.createEmptyCells(11);
poi.createCell(modifyEventList.get(k).getNewValue());
tabFourCount++;
}else if (modifyEventList.get(k).getTabId() == 5) {
poi.newRow();
poi.createEmptyCells(12);
poi.createCell(modifyEventList.get(k).getOldValue());
poi.newRow();
poi.createEmptyCells(12);
poi.createCell(modifyEventList.get(k).getNewValue());
tabFiveCount++;
}else if (modifyEventList.get(k).getTabId() == 6) {
poi.newRow();
poi.createEmptyCells(13);
poi.createCell(modifyEventList.get(k).getOldValue());
poi.newRow();
poi.createEmptyCells(13);
poi.createCell(modifyEventList.get(k).getNewValue());
tabSixCount++;
}else if (modifyEventList.get(k).getTabId() == 7) {
poi.newRow();
poi.createEmptyCells(14);
poi.createCell(modifyEventList.get(k).getOldValue());
poi.newRow();
poi.createEmptyCells(14);
poi.createCell(modifyEventList.get(k).getNewValue());
tabSevenCount++;
}
}
poi.autoSizeColumn(0); // Event Update Date
poi.setColumnWidth(1, 250); // Event Code
poi.setColumnWidth(2, 500); // Event Name
poi.setColumnWidth(3, 200); // Event Status
poi.setColumnWidth(4, 200); // Event Version
poi.setColumnWidth(5, 1000); // Change Reason
poi.setColumnWidth(6, 300); // Planners Name
poi.setColumnWidth(7, 200); // Event Update User
poi.setColumnWidth(8, 500); // General Information Tab
poi.setColumnWidth(9, 500); // Forecasting Tab
poi.setColumnWidth(10, 500); // Stationery Tab
poi.setColumnWidth(11, 500); // Datamail Tab
poi.setColumnWidth(12, 500); // Output Quality Tab
poi.setColumnWidth(13, 500); // Support Tab
poi.setColumnWidth(14, 500); // Review Tab
}
Would you be able to assist ?
Regards
Jagannath
MSB wrote:
>
> Sorry, I just assumed that you were using the API directly. Can you post
> the code from this helper class here? I suspect there will be commercial
> constraints and am certain that you will need to discuss this with your
> manager but we may be able to ffer more help if you could do this?
>
> Yours
>
> Mark B
>
>
> njr30 wrote:
>>
>> Hi Mark,
>>
>> Thanks for your help and sample code.
>>
>> The thing is I am using a helper class which is already existing in our
>> code base to create sheets, rows etc. I am not able to figure out what
>> is happening which is not able to set the row number to 3 at the start of
>> populating each of the cell.
>>
>> Ya you are right there are constraints with the data set where in I am
>> not getting the value of entire row as a record. I have to populate cell
>> by cell due to that.
>>
>> I may have to debug more and see what is happening why the row number is
>> not getting initialised to 3.
>>
>> Thanks&Regards
>> Jag
>>
>>
>> MSB wrote:
>>>
>>> Okay, I have had a look at the example and think I can see where the
>>> confusion arises. What you need to do is either keep track of the rows
>>> using a Collection - an ArrayList for example - or interrogate the Sheet
>>> object for the row so that you can add onto it. Of the two options, the
>>> latter is by far the better one IMO as the Sheet object is already
>>> storing the Row references for you internally in a list structure.
>>>
>>> Obviously, I do not know what triggers the requirement to add a new cell
>>> onto a row (to jump from populating cell A6 on your example to
>>> populating cell B2), only you can be fully aware of your algorithm, but
>>> you need to basically do something like the following (and I am assuming
>>> that you are targetting the HSSF stream here;
>>>
>>> HSSFWorkbook workbook = new HSSFWorkbook();
>>> HSSFSheet sheet = workbook.createSheet();
>>> // Create the first row.
>>> HSSFRow row = sheet.createRow(0);
>>> // Create the first cell on the row
>>> HSSFCell cell = row.createCell(0);
>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>> cell.setCellValue(10);
>>> // Now, repeat to create the first column full of cells
>>> row = sheet.createRow(1);
>>> cell = row.createCell(0);
>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>> cell.setCellValue(20);
>>> row = sheet.createRow(2);
>>> cell = row.createCell(0);
>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>> cell.setCellValue(30);
>>> row = sheet.createRow(3);
>>> cell = row.createCell(0);
>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>> cell.setCellValue(40);
>>> // Assuming that you have filled the first column and want to create the
>>> next cell
>>> // as cell B1 - the second cell on the first row - the trick is to
>>> recover the reference
>>> // for the first row from the sheet like this;
>>> row = sheet.getRow(0);
>>> // and then you can add a cell to the row
>>> cell = row.createCell(1);
>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>> cell.setCellValue(400);
>>>
>>> and so on.....
>>>
>>> One extra wrinkle is to test the value returned by the call to the
>>> getRow() method. If that method call returns a null value, then you will
>>> need to create the row, something a little like this;
>>>
>>> row = sheet.getRow(5);
>>> if(row == null) {
>>> row = sheet.createRow(5);
>>> }
>>>
>>> The other option would be to get your data set and then populate each
>>> row completely but this may not be possible; only you can know the
>>> constraints imposed by the data set.
>>>
>>> Hope this helps.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> njr30 wrote:
>>>>
>>>> Hi Mark,
>>>>
>>>> Thanks for the reply.
>>>>
>>>> I am reading the data from db and writing the data on to spreadsheet
>>>> using POI.
>>>>
>>>> I have attached a sample spreadsheet with this post.
>>>>
>>>> I have got 7 cells where the data has to be written. I first fill up
>>>> cell 1 then cell2 and so on.
>>>>
>>>> Say for eg one cell one data was laid out for over 5 rows. When I start
>>>> writing to cell 2 it starts writing from row 6 but i want it to start
>>>> again from row 1 same with the rest.
>>>>
>>>> In the attached spreadsheet i have 2 worksheets wrong and right. wrong
>>>> is what the pgm is doing at the moment and the right has the way data
>>>> has to be laid out. Can you kindly
>>>> http://www.nabble.com/file/p26012375/sample.xls sample.xls assist.
>>>>
>>>> Thanx a lot in advance.
>>>>
>>>>
>>>> Thanks&Regards
>>>> Jag
>>>>
>>>>
>>>> MSB wrote:
>>>>>
>>>>> Sorry, but I am not competely clear what you are asking.
>>>>>
>>>>> Are you saying that you have a worksheet and use POI to open it,
>>>>> access a number of cells on the sheet that could each be on a
>>>>> different row, change the value in each of those cells and then
>>>>> re-position each onto row 3 of the worksheet?
>>>>>
>>>>> Yours
>>>>>
>>>>> Mark B
>>>>>
>>>>>
>>>>> njr30 wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I am writing some data to Excel using POI where I have to reset the
>>>>>> row number to 3 as I complete inserting values in different cells.
>>>>>>
>>>>>> How do I do that ?
>>>>>>
>>>>>> Thanks,
>>>>>> njr30
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
http://www.nabble.com/file/p26034390/POIHelper.java POIHelper.java
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26034390.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by MSB <ma...@tiscali.co.uk>.
Sorry, I just assumed that you were using the API directly. Can you post the
code from this helper class here? I suspect there will be commercial
constraints and am certain that you will need to discuss this with your
manager but we may be able to ffer more help if you could do this?
Yours
Mark B
njr30 wrote:
>
> Hi Mark,
>
> Thanks for your help and sample code.
>
> The thing is I am using a helper class which is already existing in our
> code base to create sheets, rows etc. I am not able to figure out what is
> happening which is not able to set the row number to 3 at the start of
> populating each of the cell.
>
> Ya you are right there are constraints with the data set where in I am not
> getting the value of entire row as a record. I have to populate cell by
> cell due to that.
>
> I may have to debug more and see what is happening why the row number is
> not getting initialised to 3.
>
> Thanks&Regards
> Jag
>
>
> MSB wrote:
>>
>> Okay, I have had a look at the example and think I can see where the
>> confusion arises. What you need to do is either keep track of the rows
>> using a Collection - an ArrayList for example - or interrogate the Sheet
>> object for the row so that you can add onto it. Of the two options, the
>> latter is by far the better one IMO as the Sheet object is already
>> storing the Row references for you internally in a list structure.
>>
>> Obviously, I do not know what triggers the requirement to add a new cell
>> onto a row (to jump from populating cell A6 on your example to populating
>> cell B2), only you can be fully aware of your algorithm, but you need to
>> basically do something like the following (and I am assuming that you are
>> targetting the HSSF stream here;
>>
>> HSSFWorkbook workbook = new HSSFWorkbook();
>> HSSFSheet sheet = workbook.createSheet();
>> // Create the first row.
>> HSSFRow row = sheet.createRow(0);
>> // Create the first cell on the row
>> HSSFCell cell = row.createCell(0);
>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>> cell.setCellValue(10);
>> // Now, repeat to create the first column full of cells
>> row = sheet.createRow(1);
>> cell = row.createCell(0);
>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>> cell.setCellValue(20);
>> row = sheet.createRow(2);
>> cell = row.createCell(0);
>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>> cell.setCellValue(30);
>> row = sheet.createRow(3);
>> cell = row.createCell(0);
>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>> cell.setCellValue(40);
>> // Assuming that you have filled the first column and want to create the
>> next cell
>> // as cell B1 - the second cell on the first row - the trick is to
>> recover the reference
>> // for the first row from the sheet like this;
>> row = sheet.getRow(0);
>> // and then you can add a cell to the row
>> cell = row.createCell(1);
>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>> cell.setCellValue(400);
>>
>> and so on.....
>>
>> One extra wrinkle is to test the value returned by the call to the
>> getRow() method. If that method call returns a null value, then you will
>> need to create the row, something a little like this;
>>
>> row = sheet.getRow(5);
>> if(row == null) {
>> row = sheet.createRow(5);
>> }
>>
>> The other option would be to get your data set and then populate each row
>> completely but this may not be possible; only you can know the
>> constraints imposed by the data set.
>>
>> Hope this helps.
>>
>> Yours
>>
>> Mark B
>>
>>
>> njr30 wrote:
>>>
>>> Hi Mark,
>>>
>>> Thanks for the reply.
>>>
>>> I am reading the data from db and writing the data on to spreadsheet
>>> using POI.
>>>
>>> I have attached a sample spreadsheet with this post.
>>>
>>> I have got 7 cells where the data has to be written. I first fill up
>>> cell 1 then cell2 and so on.
>>>
>>> Say for eg one cell one data was laid out for over 5 rows. When I start
>>> writing to cell 2 it starts writing from row 6 but i want it to start
>>> again from row 1 same with the rest.
>>>
>>> In the attached spreadsheet i have 2 worksheets wrong and right. wrong
>>> is what the pgm is doing at the moment and the right has the way data
>>> has to be laid out. Can you kindly
>>> http://www.nabble.com/file/p26012375/sample.xls sample.xls assist.
>>>
>>> Thanx a lot in advance.
>>>
>>>
>>> Thanks&Regards
>>> Jag
>>>
>>>
>>> MSB wrote:
>>>>
>>>> Sorry, but I am not competely clear what you are asking.
>>>>
>>>> Are you saying that you have a worksheet and use POI to open it, access
>>>> a number of cells on the sheet that could each be on a different row,
>>>> change the value in each of those cells and then re-position each onto
>>>> row 3 of the worksheet?
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> njr30 wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I am writing some data to Excel using POI where I have to reset the
>>>>> row number to 3 as I complete inserting values in different cells.
>>>>>
>>>>> How do I do that ?
>>>>>
>>>>> Thanks,
>>>>> njr30
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26021257.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by njr30 <nj...@yahoo.com>.
Hi Mark,
Thanks for your help and sample code.
The thing is I am using a helper class which is already existing in our
code base to create sheets, rows etc. I am not able to figure out what is
happening which is not able to set the row number to 3 at the start of
populating each of the cell.
Ya you are right there are constraints with the data set where in I am not
getting the value of entire row as a record. I have to populate cell by cell
due to that.
I may have to debug more and see what is happening why the row number is not
getting initialised to 3.
Thanks&Regards
Jag
MSB wrote:
>
> Okay, I have had a look at the example and think I can see where the
> confusion arises. What you need to do is either keep track of the rows
> using a Collection - an ArrayList for example - or interrogate the Sheet
> object for the row so that you can add onto it. Of the two options, the
> latter is by far the better one IMO as the Sheet object is already storing
> the Row references for you internally in a list structure.
>
> Obviously, I do not know what triggers the requirement to add a new cell
> onto a row (to jump from populating cell A6 on your example to populating
> cell B2), only you can be fully aware of your algorithm, but you need to
> basically do something like the following (and I am assuming that you are
> targetting the HSSF stream here;
>
> HSSFWorkbook workbook = new HSSFWorkbook();
> HSSFSheet sheet = workbook.createSheet();
> // Create the first row.
> HSSFRow row = sheet.createRow(0);
> // Create the first cell on the row
> HSSFCell cell = row.createCell(0);
> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
> cell.setCellValue(10);
> // Now, repeat to create the first column full of cells
> row = sheet.createRow(1);
> cell = row.createCell(0);
> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
> cell.setCellValue(20);
> row = sheet.createRow(2);
> cell = row.createCell(0);
> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
> cell.setCellValue(30);
> row = sheet.createRow(3);
> cell = row.createCell(0);
> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
> cell.setCellValue(40);
> // Assuming that you have filled the first column and want to create the
> next cell
> // as cell B1 - the second cell on the first row - the trick is to recover
> the reference
> // for the first row from the sheet like this;
> row = sheet.getRow(0);
> // and then you can add a cell to the row
> cell = row.createCell(1);
> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
> cell.setCellValue(400);
>
> and so on.....
>
> One extra wrinkle is to test the value returned by the call to the
> getRow() method. If that method call returns a null value, then you will
> need to create the row, something a little like this;
>
> row = sheet.getRow(5);
> if(row == null) {
> row = sheet.createRow(5);
> }
>
> The other option would be to get your data set and then populate each row
> completely but this may not be possible; only you can know the constraints
> imposed by the data set.
>
> Hope this helps.
>
> Yours
>
> Mark B
>
>
> njr30 wrote:
>>
>> Hi Mark,
>>
>> Thanks for the reply.
>>
>> I am reading the data from db and writing the data on to spreadsheet
>> using POI.
>>
>> I have attached a sample spreadsheet with this post.
>>
>> I have got 7 cells where the data has to be written. I first fill up cell
>> 1 then cell2 and so on.
>>
>> Say for eg one cell one data was laid out for over 5 rows. When I start
>> writing to cell 2 it starts writing from row 6 but i want it to start
>> again from row 1 same with the rest.
>>
>> In the attached spreadsheet i have 2 worksheets wrong and right. wrong is
>> what the pgm is doing at the moment and the right has the way data has to
>> be laid out. Can you kindly
>> http://www.nabble.com/file/p26012375/sample.xls sample.xls assist.
>>
>> Thanx a lot in advance.
>>
>>
>> Thanks&Regards
>> Jag
>>
>>
>> MSB wrote:
>>>
>>> Sorry, but I am not competely clear what you are asking.
>>>
>>> Are you saying that you have a worksheet and use POI to open it, access
>>> a number of cells on the sheet that could each be on a different row,
>>> change the value in each of those cells and then re-position each onto
>>> row 3 of the worksheet?
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> njr30 wrote:
>>>>
>>>> Hi,
>>>>
>>>> I am writing some data to Excel using POI where I have to reset the row
>>>> number to 3 as I complete inserting values in different cells.
>>>>
>>>> How do I do that ?
>>>>
>>>> Thanks,
>>>> njr30
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26017220.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by MSB <ma...@tiscali.co.uk>.
Okay, I have had a look at the example and think I can see where the
confusion arises. What you need to do is either keep track of the rows using
a Collection - an ArrayList for example - or interrogate the Sheet object
for the row so that you can add onto it. Of the two options, the latter is
by far the better one IMO as the Sheet object is already storing the Row
references for you internally in a list structure.
Obviously, I do not know what triggers the requirement to add a new cell
onto a row (to jump from populating cell A6 on your example to populating
cell B2), only you can be fully aware of your algorithm, but you need to
basically do something like the following (and I am assuming that you are
targetting the HSSF stream here;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
// Create the first row.
HSSFRow row = sheet.createRow(0);
// Create the first cell on the row
HSSFCell cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(10);
// Now, repeat to create the first column full of cells
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(20);
row = sheet.createRow(2);
cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(30);
row = sheet.createRow(3);
cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(40);
// Assuming that you have filled the first column and want to create the
next cell
// as cell B1 - the second cell on the first row - the trick is to recover
the reference
// for the first row from the sheet like this;
row = sheet.getRow(0);
// and then you can add a cell to the row
cell = row.createCell(1);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(400);
and so on.....
One extra wrinkle is to test the value returned by the call to the getRow()
method. If that method call returns a null value, then you will need to
create the row, something a little like this;
row = sheet.getRow(5);
if(row == null) {
row = sheet.createRow(5);
}
The other option would be to get your data set and then populate each row
completely but this may not be possible; only you can know the constraints
imposed by the data set.
Hope this helps.
Yours
Mark B
njr30 wrote:
>
> Hi Mark,
>
> Thanks for the reply.
>
> I am reading the data from db and writing the data on to spreadsheet using
> POI.
>
> I have attached a sample spreadsheet with this post.
>
> I have got 7 cells where the data has to be written. I first fill up cell
> 1 then cell2 and so on.
>
> Say for eg one cell one data was laid out for over 5 rows. When I start
> writing to cell 2 it starts writing from row 6 but i want it to start
> again from row 1 same with the rest.
>
> In the attached spreadsheet i have 2 worksheets wrong and right. wrong is
> what the pgm is doing at the moment and the right has the way data has to
> be laid out. Can you kindly
> http://www.nabble.com/file/p26012375/sample.xls sample.xls assist.
>
> Thanx a lot in advance.
>
>
> Thanks&Regards
> Jag
>
>
> MSB wrote:
>>
>> Sorry, but I am not competely clear what you are asking.
>>
>> Are you saying that you have a worksheet and use POI to open it, access a
>> number of cells on the sheet that could each be on a different row,
>> change the value in each of those cells and then re-position each onto
>> row 3 of the worksheet?
>>
>> Yours
>>
>> Mark B
>>
>>
>> njr30 wrote:
>>>
>>> Hi,
>>>
>>> I am writing some data to Excel using POI where I have to reset the row
>>> number to 3 as I complete inserting values in different cells.
>>>
>>> How do I do that ?
>>>
>>> Thanks,
>>> njr30
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26013360.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by njr30 <nj...@yahoo.com>.
Hi Mark,
Thanks for the reply.
I am reading the data from db and writing the data on to spreadsheet using
POI.
I have attached a sample spreadsheet with this post.
I have got 7 cells where the data has to be written. I first fill up cell 1
then cell2 and so on.
Say for eg one cell one data was laid out for over 5 rows. When I start
writing to cell 2 it starts writing from row 6 but i want it to start again
from row 1 same with the rest.
In the attached spreadsheet i have 2 worksheets wrong and right. wrong is
what the pgm is doing at the moment and the right has the way data has to be
laid out. Can you kindly http://www.nabble.com/file/p26012375/sample.xls
sample.xls assist.
Thanx a lot in advance.
Thanks&Regards
Jag
MSB wrote:
>
> Sorry, but I am not competely clear what you are asking.
>
> Are you saying that you have a worksheet and use POI to open it, access a
> number of cells on the sheet that could each be on a different row, change
> the value in each of those cells and then re-position each onto row 3 of
> the worksheet?
>
> Yours
>
> Mark B
>
>
> njr30 wrote:
>>
>> Hi,
>>
>> I am writing some data to Excel using POI where I have to reset the row
>> number to 3 as I complete inserting values in different cells.
>>
>> How do I do that ?
>>
>> Thanks,
>> njr30
>>
>
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26012375.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: Setting the row number to 3 while inserting values in different
cells !!
Posted by MSB <ma...@tiscali.co.uk>.
Sorry, but I am not competely clear what you are asking.
Are you saying that you have a worksheet and use POI to open it, access a
number of cells on the sheet that could each be on a different row, change
the value in each of those cells and then re-position each onto row 3 of the
worksheet?
Yours
Mark B
njr30 wrote:
>
> Hi,
>
> I am writing some data to Excel using POI where I have to reset the row
> number to 3 as I complete inserting values in different cells.
>
> How do I do that ?
>
> Thanks,
> njr30
>
--
View this message in context: http://www.nabble.com/Setting-the-row-number-to-3-while-inserting-values-in-different-cells-%21%21-tp26003733p26004580.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