Needs clarity about Cell Styles

[suriz4u <su...@gmail.com>]

Hi all
       Iam using cellstyles in POI to generate excel.For formatting
different datatypes iam using different styles.

For example for formatting date iam using  
 HSSFCellStyle csDate = wb.createCellStyle();
Iam creating different cellstyles for different datatypes.I tried to use
only one cellStyle but it doesnt worked.

Is it recommendable to create different cell styles for different
datatypes???

Please  any one can give me solution for this...

Thanks
Suresh G
-- 
View this message in context: http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17726681.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: Needs clarity about Cell Styles

[Anthony Andrews <py...@yahoo.com>]

Yes, it is quite straightforward really.

You need to create a style for each different type of cell you will encounter. Imagine for example that you need to style date cells, text cells and numeric cells differently and that you still have the alternate line colouring constraint, then you will need to create six different cell styles;

* Basic date cell stlye
* Alternate colouring date cell style
* Basic text cell style
* Alternate colouring text cell style
* Basic numeric cell style
* Alternate colouring numeric cell style

Then, you need to determine how you will apply these styles. The row number question can be easilly addressed using the modulus operator as you have already done. The cell type issue could surely be addressed when you create the cell - at that time you know what is goint to go into the cell and should be able to determine the cell style appropriately.

--- On Mon, 6/9/08, suriz4u <suriz4u@gmail.com> wrote:
From: suriz4u <suriz4u@gmail.com>
Subject: Re: Needs clarity about Cell Styles
To: user@poi.apache.org
Date: Monday, June 9, 2008, 9:30 PM

Thanks Anthony.But iam using different styles for different objects.For
example for date iam using
HSSFCellStyle csDate = wb.createCellStyle();
HSSFCellStyle csDouble = wb.createCellStyle();
I have different styles for different datatypes such as double etc..
So based on the row count i can set the color,but if iam using following
code.
Iam looping through row and for each cell iam checking the datatype.

 if(value instanceof Double)
                    {
                        if(!(rownum%2==0))
                          {  
                           
csDouble.setFillForegroundColor(HSSFColor.AQUA.index);
                           
csDouble.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                          }
}
So iam using many styles in a row,i mean for different cells i have
different styles.So if iam using above code and setting color.That color is
being applied through out the sheet.

So any solution please??


Anthony Andrews wrote:
> 
> As far as I know, there is no way to set the style for a row; so, you are
> going to have to set the style for each cell as you create it.
> 
> If it were me. I would create the style objects for each 'row'
firstly,
> outside of any loop. So;
> 
> HSSFCellStyle evenRowCellStyle = wb.createCellStyle();
> evenRowCellStyle.setBackgroundFillColor(HSSFColor.AQUA.index);
> HSSFCellStyle oddRowCellStyle = wb.createCellStyle();
> oddRowCellStyle.setBackgroundFillColor(HSSFColor.RED.index);
> 
> Where wb is the reference to a workbook I have created.
> 
> Now, I do not know how you are creating your cells, but the example I will
> give is of a couple of loops; the outer loop creates rows, the inner
> cells. So;
> 
> HSSFSheet sheet = wb.createSheet("Colouring Test");
> HSSFRow row = null;
> HSSFCell cell = null;
> 
> for (int rowNum = 0; rowNum < 10; rowNum++) {
> 
>    row = sheet.createRow((short)rowNum);
> 
>    for(int cellNum = 0; cellNum < 10; cellNum++)
{
>       cell =
row.createCell((short)colNum);
> 
>       if((rowNum % 2) == 0) {
>
        
> cell.setCellStyle(evenRowCellStyle);
>       }
>       else {
>
        
> cell.setCellStyle(oddRowCellStyle);
>       }
>    }
> 
> }
> 
> You may even find that you only need to create a single cell style object,
> try it on a small test sheet and see.
> 
> PS - Hope this works, I did not test it simple typed the code into the
> browser. If not, just let me know.
> 
> 
> --- On Mon, 6/9/08, suriz4u <suriz4u@gmail.com> wrote:
> From: suriz4u <suriz4u@gmail.com>
> Subject: Re: Needs clarity about Cell Styles
> To: user@poi.apache.org
> Date: Monday, June 9, 2008, 2:40 AM
> 
> Thanks for such a good explanation.But in my application there is
> requirement that alternative rows should be colored.As iam using different
> styles for different objects.I tried to use in following way for different
> objects
> 
>  HSSFCellStyle csDouble= wb.createCellStyle();
>   if(!(rowNumber%2==0)) 
>        csDouble.setFillBackgroundColor(HSSFColor.AQUA.index);
> 
> But it didnt worked in this way..
> Is there any  solution for this???
> 
> 
> 
> 
> 
> Anthony Andrews wrote:
> > 
> > Yes, you need to create a different style object for each cell
style
> that
> > your sheet requires. Typically, people tend to accomplish this
task
> at the
> > start of the application - assuming of course they know at that
stage
> all
> > of the cell styles they require. This is not to say, of course,
that
> there
> > is anything wrong with creating styles as you go, just try to
avoid
> > duplicates if memory could be an issue for you.
> > 
> > If you do try to reuse cell styles by modifying the style object
once
> it
> > has been created, you will (should) find that all of your cells
will
> have
> > the same appearance. This occurs because the cell style objects
are
> > interpreted (interrogated) when you call the write method if I
> understand
> > correctly.
> > 
> > --- On Sun, 6/8/08, suriz4u
<suriz4u@gmail.com> wrote:
> > From: suriz4u <suriz4u@gmail.com>
> > Subject: Needs clarity about Cell Styles
> > To: user@poi.apache.org
> > Date: Sunday, June 8, 2008, 10:49 PM
> > 
> > Hi all
> >        Iam using cellstyles in POI to generate excel.For
formatting
> > different datatypes iam using different styles.
> > 
> > For example for formatting date iam using  
> >  HSSFCellStyle csDate = wb.createCellStyle();
> > Iam creating different cellstyles for different datatypes.I tried
to
> use
> > only one cellStyle but it doesnt worked.
> > 
> > Is it recommendable to create different cell styles for different
> > datatypes???
> > 
> > Please  any one can give me solution for this...
> > 
> > Thanks
> > Suresh G
> > -- 
> > View this message in context:
> >
>
http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17726681.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
> > 
> > 
> >       
> > 
> 
> -- 
> View this message in context:
>
http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17729434.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
> 
> 
>       
> 

-- 
View this message in context:
http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17747584.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: Needs clarity about Cell Styles

[suriz4u <su...@gmail.com>]

Thanks Anthony.But iam using different styles for different objects.For
example for date iam using
HSSFCellStyle csDate = wb.createCellStyle();
HSSFCellStyle csDouble = wb.createCellStyle();
I have different styles for different datatypes such as double etc..
So based on the row count i can set the color,but if iam using following
code.
Iam looping through row and for each cell iam checking the datatype.

 if(value instanceof Double)
                    {
                        if(!(rownum%2==0))
                          {  
                           
csDouble.setFillForegroundColor(HSSFColor.AQUA.index);
                           
csDouble.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                          }
}
So iam using many styles in a row,i mean for different cells i have
different styles.So if iam using above code and setting color.That color is
being applied through out the sheet.

So any solution please??


Anthony Andrews wrote:
> 
> As far as I know, there is no way to set the style for a row; so, you are
> going to have to set the style for each cell as you create it.
> 
> If it were me. I would create the style objects for each 'row' firstly,
> outside of any loop. So;
> 
> HSSFCellStyle evenRowCellStyle = wb.createCellStyle();
> evenRowCellStyle.setBackgroundFillColor(HSSFColor.AQUA.index);
> HSSFCellStyle oddRowCellStyle = wb.createCellStyle();
> oddRowCellStyle.setBackgroundFillColor(HSSFColor.RED.index);
> 
> Where wb is the reference to a workbook I have created.
> 
> Now, I do not know how you are creating your cells, but the example I will
> give is of a couple of loops; the outer loop creates rows, the inner
> cells. So;
> 
> HSSFSheet sheet = wb.createSheet("Colouring Test");
> HSSFRow row = null;
> HSSFCell cell = null;
> 
> for (int rowNum = 0; rowNum < 10; rowNum++) {
> 
>    row = sheet.createRow((short)rowNum);
> 
>    for(int cellNum = 0; cellNum < 10; cellNum++) {
>       cell = row.createCell((short)colNum);
> 
>       if((rowNum % 2) == 0) {
>         
> cell.setCellStyle(evenRowCellStyle);
>       }
>       else {
>         
> cell.setCellStyle(oddRowCellStyle);
>       }
>    }
> 
> }
> 
> You may even find that you only need to create a single cell style object,
> try it on a small test sheet and see.
> 
> PS - Hope this works, I did not test it simple typed the code into the
> browser. If not, just let me know.
> 
> 
> --- On Mon, 6/9/08, suriz4u <suriz4u@gmail.com> wrote:
> From: suriz4u <suriz4u@gmail.com>
> Subject: Re: Needs clarity about Cell Styles
> To: user@poi.apache.org
> Date: Monday, June 9, 2008, 2:40 AM
> 
> Thanks for such a good explanation.But in my application there is
> requirement that alternative rows should be colored.As iam using different
> styles for different objects.I tried to use in following way for different
> objects
> 
>  HSSFCellStyle csDouble= wb.createCellStyle();
>   if(!(rowNumber%2==0)) 
>        csDouble.setFillBackgroundColor(HSSFColor.AQUA.index);
> 
> But it didnt worked in this way..
> Is there any  solution for this???
> 
> 
> 
> 
> 
> Anthony Andrews wrote:
> > 
> > Yes, you need to create a different style object for each cell style
> that
> > your sheet requires. Typically, people tend to accomplish this task
> at the
> > start of the application - assuming of course they know at that stage
> all
> > of the cell styles they require. This is not to say, of course, that
> there
> > is anything wrong with creating styles as you go, just try to avoid
> > duplicates if memory could be an issue for you.
> > 
> > If you do try to reuse cell styles by modifying the style object once
> it
> > has been created, you will (should) find that all of your cells will
> have
> > the same appearance. This occurs because the cell style objects are
> > interpreted (interrogated) when you call the write method if I
> understand
> > correctly.
> > 
> > --- On Sun, 6/8/08, suriz4u <suriz4u@gmail.com> wrote:
> > From: suriz4u <suriz4u@gmail.com>
> > Subject: Needs clarity about Cell Styles
> > To: user@poi.apache.org
> > Date: Sunday, June 8, 2008, 10:49 PM
> > 
> > Hi all
> >        Iam using cellstyles in POI to generate excel.For formatting
> > different datatypes iam using different styles.
> > 
> > For example for formatting date iam using  
> >  HSSFCellStyle csDate = wb.createCellStyle();
> > Iam creating different cellstyles for different datatypes.I tried to
> use
> > only one cellStyle but it doesnt worked.
> > 
> > Is it recommendable to create different cell styles for different
> > datatypes???
> > 
> > Please  any one can give me solution for this...
> > 
> > Thanks
> > Suresh G
> > -- 
> > View this message in context:
> >
> http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17726681.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
> > 
> > 
> >       
> > 
> 
> -- 
> View this message in context:
> http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17729434.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
> 
> 
>       
> 

-- 
View this message in context: http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17747584.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: Needs clarity about Cell Styles

[Anthony Andrews <py...@yahoo.com>]

As far as I know, there is no way to set the style for a row; so, you are going to have to set the style for each cell as you create it.

If it were me. I would create the style objects for each 'row' firstly, outside of any loop. So;

HSSFCellStyle evenRowCellStyle = wb.createCellStyle();
evenRowCellStyle.setBackgroundFillColor(HSSFColor.AQUA.index);
HSSFCellStyle oddRowCellStyle = wb.createCellStyle();
oddRowCellStyle.setBackgroundFillColor(HSSFColor.RED.index);

Where wb is the reference to a workbook I have created.

Now, I do not know how you are creating your cells, but the example I will give is of a couple of loops; the outer loop creates rows, the inner cells. So;

HSSFSheet sheet = wb.createSheet("Colouring Test");
HSSFRow row = null;
HSSFCell cell = null;

for (int rowNum = 0; rowNum < 10; rowNum++) {

   row = sheet.createRow((short)rowNum);

   for(int cellNum = 0; cellNum < 10; cellNum++) {
      cell = row.createCell((short)colNum);

      if((rowNum % 2) == 0) {
         cell.setCellStyle(evenRowCellStyle);
      }
      else {
         cell.setCellStyle(oddRowCellStyle);
      }
   }

}

You may even find that you only need to create a single cell style object, try it on a small test sheet and see.

PS - Hope this works, I did not test it simple typed the code into the browser. If not, just let me know.


--- On Mon, 6/9/08, suriz4u <suriz4u@gmail.com> wrote:
From: suriz4u <suriz4u@gmail.com>
Subject: Re: Needs clarity about Cell Styles
To: user@poi.apache.org
Date: Monday, June 9, 2008, 2:40 AM

Thanks for such a good explanation.But in my application there is
requirement that alternative rows should be colored.As iam using different
styles for different objects.I tried to use in following way for different
objects

 HSSFCellStyle csDouble= wb.createCellStyle();
  if(!(rowNumber%2==0)) 
       csDouble.setFillBackgroundColor(HSSFColor.AQUA.index);

But it didnt worked in this way..
Is there any  solution for this???





Anthony Andrews wrote:
> 
> Yes, you need to create a different style object for each cell style that
> your sheet requires. Typically, people tend to accomplish this task at the
> start of the application - assuming of course they know at that stage all
> of the cell styles they require. This is not to say, of course, that there
> is anything wrong with creating styles as you go, just try to avoid
> duplicates if memory could be an issue for you.
> 
> If you do try to reuse cell styles by modifying the style object once it
> has been created, you will (should) find that all of your cells will have
> the same appearance. This occurs because the cell style objects are
> interpreted (interrogated) when you call the write method if I understand
> correctly.
> 
> --- On Sun, 6/8/08, suriz4u <suriz4u@gmail.com> wrote:
> From: suriz4u <suriz4u@gmail.com>
> Subject: Needs clarity about Cell Styles
> To: user@poi.apache.org
> Date: Sunday, June 8, 2008, 10:49 PM
> 
> Hi all
>        Iam using cellstyles in POI to generate excel.For formatting
> different datatypes iam using different styles.
> 
> For example for formatting date iam using  
>  HSSFCellStyle csDate = wb.createCellStyle();
> Iam creating different cellstyles for different datatypes.I tried to use
> only one cellStyle but it doesnt worked.
> 
> Is it recommendable to create different cell styles for different
> datatypes???
> 
> Please  any one can give me solution for this...
> 
> Thanks
> Suresh G
> -- 
> View this message in context:
>
http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17726681.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
> 
> 
>       
> 

-- 
View this message in context:
http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17729434.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: Needs clarity about Cell Styles

[suriz4u <su...@gmail.com>]

Thanks for such a good explanation.But in my application there is
requirement that alternative rows should be colored.As iam using different
styles for different objects.I tried to use in following way for different
objects

 HSSFCellStyle csDouble= wb.createCellStyle();
  if(!(rowNumber%2==0)) 
       csDouble.setFillBackgroundColor(HSSFColor.AQUA.index);

But it didnt worked in this way..
Is there any  solution for this???





Anthony Andrews wrote:
> 
> Yes, you need to create a different style object for each cell style that
> your sheet requires. Typically, people tend to accomplish this task at the
> start of the application - assuming of course they know at that stage all
> of the cell styles they require. This is not to say, of course, that there
> is anything wrong with creating styles as you go, just try to avoid
> duplicates if memory could be an issue for you.
> 
> If you do try to reuse cell styles by modifying the style object once it
> has been created, you will (should) find that all of your cells will have
> the same appearance. This occurs because the cell style objects are
> interpreted (interrogated) when you call the write method if I understand
> correctly.
> 
> --- On Sun, 6/8/08, suriz4u <suriz4u@gmail.com> wrote:
> From: suriz4u <suriz4u@gmail.com>
> Subject: Needs clarity about Cell Styles
> To: user@poi.apache.org
> Date: Sunday, June 8, 2008, 10:49 PM
> 
> Hi all
>        Iam using cellstyles in POI to generate excel.For formatting
> different datatypes iam using different styles.
> 
> For example for formatting date iam using  
>  HSSFCellStyle csDate = wb.createCellStyle();
> Iam creating different cellstyles for different datatypes.I tried to use
> only one cellStyle but it doesnt worked.
> 
> Is it recommendable to create different cell styles for different
> datatypes???
> 
> Please  any one can give me solution for this...
> 
> Thanks
> Suresh G
> -- 
> View this message in context:
> http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17726681.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
> 
> 
>       
> 

-- 
View this message in context: http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17729434.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: Needs clarity about Cell Styles

[Anthony Andrews <py...@yahoo.com>]

Yes, you need to create a different style object for each cell style that your sheet requires. Typically, people tend to accomplish this task at the start of the application - assuming of course they know at that stage all of the cell styles they require. This is not to say, of course, that there is anything wrong with creating styles as you go, just try to avoid duplicates if memory could be an issue for you.

If you do try to reuse cell styles by modifying the style object once it has been created, you will (should) find that all of your cells will have the same appearance. This occurs because the cell style objects are interpreted (interrogated) when you call the write method if I understand correctly.

--- On Sun, 6/8/08, suriz4u <suriz4u@gmail.com> wrote:
From: suriz4u <suriz4u@gmail.com>
Subject: Needs clarity about Cell Styles
To: user@poi.apache.org
Date: Sunday, June 8, 2008, 10:49 PM

Hi all
       Iam using cellstyles in POI to generate excel.For formatting
different datatypes iam using different styles.

For example for formatting date iam using  
 HSSFCellStyle csDate = wb.createCellStyle();
Iam creating different cellstyles for different datatypes.I tried to use
only one cellStyle but it doesnt worked.

Is it recommendable to create different cell styles for different
datatypes???

Please  any one can give me solution for this...

Thanks
Suresh G
-- 
View this message in context:
http://www.nabble.com/Needs-clarity-about-Cell-Styles-tp17726681p17726681.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