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: [email protected]
For additional commands, e-mail: [email protected]
