There is some problems with the solution.
The Excel sheet render is with order;
when sheet1 finished rendering the sheet2 rendering is just started;
so
if sheet1 has a formula with relevance to sheet2. the formula will be #REF!
We need some extra code to refresh the formula when the whole excel is finished rendering
Here, I give a solution
We need a POJO to store the formula information,
like: sheet name , row index, cell index and the formula
and then
when we copy a cell to another sheet cell
if the cell type is HSSFCell.CELL_TYPE_FORMULA
we need to add the cell information to a collection as you like (of course you need to create a collection in the class)
OK~ excuse my poor English
I give the solution in the following
1. we need a POJO to save the formula infomation
I create a private inner static class named FormulaInfo
2. create a formulaInfo Collection in the class
3. in copyCell(), when you detected the cell is a FormulaType m you need to add informatiton to the formulaInfoList
4. after create the whole WorkBook, we need to refresh the formula