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