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 10:25:50 GMT

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


Mime
View raw message