Need help !!!

[vedgunjan <ve...@yagnaiq.com>]

Hi Experts,

 

I'm new the POI library. In the application that I have been working I need
following help.

 

1.	How to make entire column in the excel sheet as non-editable or
rather how to lock an entire column.

 

2.	Also how to make a row or cell in the excel sheet as non-editable or
lock it.

 

3.	How can I write a formula for the entire column of rows? For example
: 

      I want that each row in column H should have formula which is
something like (corresponding "I" value *   corresponding "J" value)

 

Thanks & Regards

Ved Gunjan.

 

Incubation Center, C -201/ Unit No: 5, Pune IT Park,

Aundh Rd, Pune, India.

 

Meet Me Details:  Tel: 91.20.30223.100 | Fax: 91.20.30223.100 | Cell:
9890015916 



www.yagnaiq.com <http://www.yagnaiq.com/> 

  _____  

"Lack of will power has caused more failure than lack of intelligence or
ability"

 

Re: Need help !!!

[Tim Wilkins <ti...@mediatel.co.uk>]

I don't know how to set columns and rows to be non-editable but I have 
just implemented a formula copy utility which I think is what your 3rd 
point is referring to.

To get this feature to work you will need to checkout the latest POI 
code from the svn repository (http://svn.apache.org/repos/asf) as there 
is a change that Nick Burch made for me to source code to allow access 
to the FormulaParser class.

Here is the main method of my code:

public static String copyFormula(HSSFCell srcCell, HSSFWorkbook 
workbook, short rowOffset, short columnOffset)
throws FormulaUtilsException
{
if (srcCell.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
return null;

FormulaParser parser = 
HSSFFormulaEvaluator.getUnderlyingParser(workbook,srcCell.getCellFormula());
parser.parse();
Ptg[] ptgs = parser.getRPNPtg();

for (int i = 0, iSize = ptgs.length; i < iSize; i++)
{
//we are only interested in cell references
if (ptgs[i] instanceof ReferencePtg)
{
ReferencePtg ptg = (ReferencePtg) ptgs[i];

if ( ptg.isColRelative() )
ptg.setColumn( (short) (ptg.getColumn() + columnOffset));
if ( ptg.isRowRelative() )
ptg.setRow( (short) (ptg.getRow() + rowOffset));
}
else if (ptgs[i] instanceof AreaPtg)
{
AreaPtg ap = (AreaPtg) ptgs[i];

if ( ap.isFirstRowRelative() )
ap.setFirstRow( (short) (ap.getFirstRow() + rowOffset) );
if ( ap.isLastRowRelative() )
ap.setLastRow( (short) (ap.getLastRow() + rowOffset) );
if ( ap.isFirstColRelative() )
ap.setFirstColumn( (short) (ap.getFirstColumn() + columnOffset) );
if ( ap.isLastColRelative() )
ap.setLastColumn( (short) (ap.getLastColumn() + columnOffset) );
}
}

return parser.toFormulaString( ptgs );
}


Basically the method takes a parameter srcCell that is the cell that 
contains the formula that you want to copy. It also take two offset 
parameters that indicate the number of rows and columns to 'move' the 
formula references by. Finally it returns a string representation of the 
new formula.

Please be aware that I have not fully tested this code but it should 
give you an idea of how to implement this functionality.

Hope this helps,

Tim

vedgunjan wrote:
>
> Hi Experts,
>
> I’m new the POI library. In the application that I have been working I 
> need following help.
>
>    1. How to make entire column in the excel sheet as non-editable or
>       rather how to lock an entire column.
>
>    2. Also how to make a row or cell in the excel sheet as
>       non-editable or lock it.
>
>    3. How can I write a formula for the entire column of rows? For
>       example :
>
> I want that each row in column H should have formula which is 
> something like (corresponding “*I*” value * corresponding “*J*” value)
>
> *Thanks & Regards*
>
> **Ved Gunjan****.******
>
> ** **
>
> **Incubation**** Center****, C -201/ Unit No: 5, Pune IT Park,**
>
> **Aundh Rd****, Pune, India****.**
>
> ** **
>
> **Meet Me Details:**** ****Tel: 91.20.30223.100 ****|**** Fax: 
> 91.20.30223.100 ****|**** Cell: 9890015916 **
>
> www.yagnaiq.com <http://www.yagnaiq.com/>__
>
> ------------------------------------------------------------------------
>
> "Lack of will power has caused more failure than lack of intelligence 
> or ability"
>


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org