* JExcelAPI (http://jexcelapi.sourceforge.net/) has a cell deep copy
feature using WritableCell.copyTo(int col, int row) method which copies
formulas relatively if the formula exists.
I couldn't find anything like this in Apache POI .
I need to copy the formula cell and the formula should change relative way in
the new cell using Apache POI.
For example if C1=A1+B1, when I copy C1 Cell to C2 the formula should be
C2=A2+B2.
I have written a custom method to do it. But I don't know whether this method
supports for all the excel supported formulas. It would be great if POI
developers writes a method to do this where I can use confidently. Attached
here the custom method that I had developed to copy formula relatively.
Your help would be appreciated greatly.
Thank You,
Siva Sankara Kakularam
Developer Sr. | FIRST Application Support | WellPoint Inc.,
Mailstop: CAAC03-003I | 21555 Oxnard Street | Woodland Hills | CA - 91367
* [email protected]<mailto:[email protected]> | *Office:
(818) 234-4511 | *Cell: (858) 382-0028
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
and privileged information or otherwise be protected by law. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please contact the sender by reply e-mail
and destroy all copies of the original message.
public String getCopyFormula(
Workbook workbook,
Sheet sheet,
Cell oldCell,
Cell newCell)
{
String oldFormula = oldCell.getCellFormula();
String newFormula = new String();
if(oldFormula != null)
{
FormulaParsingWorkbook parsingWorkbook = null;
FormulaRenderingWorkbook renderingWorkbook = null;
if(workbook instanceof HSSFWorkbook)
{
parsingWorkbook =
HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);
renderingWorkbook =
HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);
}
else if(workbook instanceof XSSFWorkbook)
{
parsingWorkbook =
XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
renderingWorkbook =
XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
}
// get PTG's in the formula
Ptg[] ptgs = FormulaParser.parse(oldFormula, parsingWorkbook,
FormulaType.CELL, workbook.getSheetIndex(sheet));
// iterating through all PTG's
for( Ptg ptg : ptgs )
{
if( ptg instanceof RefPtgBase ) // for references such as A1,
A2, B3
{
RefPtgBase refPtgBase = (RefPtgBase) ptg;
// if row is relative
if(refPtgBase.isRowRelative() )
{
refPtgBase.setRow((short)(newCell.getRowIndex()
- (oldCell.getRowIndex() - refPtgBase.getRow())));
}
// if col is relative
if(refPtgBase.isColRelative() )
{
refPtgBase.setColumn((short)(newCell.getColumnIndex() -
(oldCell.getColumnIndex() - refPtgBase.getColumn())));
}
}
if(ptg instanceof AreaPtgBase) // for area of cells A1:A4
{
AreaPtgBase areaPtgBase = (AreaPtgBase)ptg;
// if first row is relative
if(areaPtgBase.isFirstRowRelative() )
{
areaPtgBase.setFirstRow((short)(newCell.getRowIndex() -
(oldCell.getRowIndex() - areaPtgBase.getFirstRow())));
}
// if last row is relative
if(areaPtgBase.isLastRowRelative())
{
areaPtgBase.setLastRow((short)(newCell.getRowIndex() -
(oldCell.getRowIndex() - areaPtgBase.getLastRow())));
}
// if first column is relative
if(areaPtgBase.isFirstColRelative())
{
areaPtgBase.setFirstColumn((short)(newCell.getColumnIndex() -
(oldCell.getColumnIndex() - areaPtgBase.getFirstColumn())));
}
// if last column is relative
if(areaPtgBase.isLastColRelative())
{
areaPtgBase.setLastColumn((short)(newCell.getColumnIndex() -
(oldCell.getColumnIndex() - areaPtgBase.getLastColumn())));
}
}
}
newFormula = FormulaRenderer.toFormulaString(renderingWorkbook,
ptgs);
}
return newFormula;
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
