poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: Setting the row number to 3 while inserting values in different cells !!
Date Sat, 24 Oct 2009 06:51:13 GMT

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


Mime
View raw message