问题：设置Excel的公式，根据java更新Excel中变量单元格，输出的Excel公式未生效

解决：

  //打开Excel重新计算公式
        workBook.setForceFormulaRecalculation(true);

源码如下：

 /**
     * Whether the application shall perform a full recalculation when the workbook is opened.
     * <p>
     * Typically you want to force formula recalculation when you modify cell formulas or values
     * of a workbook previously created by Excel. When set to true, this flag will tell Excel
     * that it needs to recalculate all formulas in the workbook the next time the file is opened.
     * </p>
     * <p>
     * Note, that recalculation updates cached formula results and, thus, modifies the workbook.
     * Depending on the version, Excel may prompt you with "Do you want to save the changes in <em>filename</em>?"
     * on close.
     * </p>
     *
     * @param value true if the application will perform a full recalculation of
     * workbook values when the workbook is opened
     * @since 3.8
     */
   public void setForceFormulaRecalculation(boolean value){
        CTWorkbook ctWorkbook = getCTWorkbook();
        CTCalcPr calcPr = ctWorkbook.isSetCalcPr() ? ctWorkbook.getCalcPr() : ctWorkbook.addNewCalcPr();
        // when set to 0, will tell Excel that it needs to recalculate all formulas
        // in the workbook the next time the file is opened.
        calcPr.setCalcId(0);

        if(value && calcPr.getCalcMode() == STCalcMode.MANUAL) {
            calcPr.setCalcMode(STCalcMode.AUTO);
        }
    }

 

背景：在实现自动化测试时，执行完每一条excel测试用例后，需要更新该条用例的测试结果单元格中执行结果，于是在网上苦找方法，都是零碎案例，无法达到预期目的，又由于java功底一般，故自己琢磨写了一个简单的方法解决问题，相信网上大神众多，肯定有更好方式和方法，欢迎大神来拍砖指导。。




package common;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFCell;
import java.io.*;
/**

 * User: XZee

 * Date: 2011-10-10

 * Time: 16:10:29

 */
public class write_excel {

     /** 总行数 */

    private int totalRows = 0;

    /** 总列数 */

    private int totalCells = 0;

    /** 错误信息 */

    private static String errorInfo;

    private static XSSFWorkbook workbook;

    private static HSSFWorkbook workbook2;

    /** 构造方法 

     * @return */
    public int getTotalRows() {

        return totalRows;

    }

    public int getTotalCells() {

        return totalCells;

    }

    public String getErrorInfo() {

        return errorInfo;

    }

    

    public static boolean validateExcel(String filePath) {

        /** 检查文件名是否为空或者是否是Excel格式的文件 */

        if (filePath == null|| !(isExcel2003(filePath) || isExcel2007(filePath))) {

            errorInfo = "文件名不是excel格式";

            return false;

        }

        /** 检查文件是否存在 */

        File file = new File(filePath);

        if (file == null || !file.exists()) {

            errorInfo = "文件不存在";

            return false;

        }

        return true;

    }

    

    

   public static boolean isExcel2003(String filePath) {

  return filePath.matches("^.+\\.(?i)(xls)$");

 }
    public static boolean isExcel2007(String filePath) {

  return filePath.matches("^.+\\.(?i)(xlsx)$");

    }
    /**

     * 只是一个demo，这里假设修改的值是String类型

     * @param filePath

     * @param sheetIndex

     * @param col

     * @param row

     * @param value

     * @throws Exception

     */

    public static void updateExcel(String filePath,int sheetIndex,int row,int col,String value)throws Exception{

        /** 验证文件是否合法 */

        if (!validateExcel(filePath)) {

            System.out.println(errorInfo);

        }

        

        FileInputStream fis=new FileInputStream(filePath);

        

        /** 判断文件的类型，是2003还是2007 */

        

        if (isExcel2007(filePath)) {
            workbook = new XSSFWorkbook(fis);

            XSSFSheet sheet=workbook.getSheetAt(0);

            XSSFRow r=sheet.getRow(row);

            XSSFCell cell=r.getCell(col);

            

            String str=cell.getStringCellValue();

            //这里假设对应单元格原来的类型也是String类型

            cell.setCellValue(value);

            System.out.println("单元格原来值为"+str+"\n");

            System.out.println("单元格值被更新为"+value+"\n");
            fis.close();//关闭文件输入流
            FileOutputStream fos=new FileOutputStream(filePath);

            workbook.write(fos);

            fos.close();//关闭文件输出流

        }else if(isExcel2003(filePath)){            

         workbook2 = new HSSFWorkbook(fis);

         HSSFSheet sheet = workbook2.getSheetAt(sheetIndex);

         HSSFRow r = sheet.getRow(row);

         HSSFCell cell = r.getCell(col);    

         String str=cell.getStringCellValue();

         //这里假设对应单元格原来的类型也是String类型

         cell.setCellValue(value);

         System.out.println("单元格原来值为"+str);

         System.out.println("单元格值被更新为"+value);
         fis.close();//关闭文件输入流
         FileOutputStream fos=new FileOutputStream(filePath);

         workbook2.write(fos);

         fos.close();//关闭文件输出流

        }else{

         System.out.println("当前文件，可能不是excel203或excel2007文件，请检查文件");

        }
    }


 public static void main(String[] args) throws Exception{

       // TODO Auto-generated method stub

      File file=new File("E:\\workspace\\FE_performance_test\\Datasource\\TestCase.xlsx");

      String file="E:\\workspace\\FE_performance_test\\Datasource\\TestCase.xlsx";
         //下面尝试更改第一行第一列的单元格的值

      write_excel.updateExcel(file,0,6,1,"2哥好腻害");

 }



原文地址：http://blog.csdn.net/zouxiongqqq/article/details/78478298

背景：在实现自动化测试时，执行完每一条excel测试用例后，需要更新该条用例的测试结果单元格中执行结果，于是在网上苦找方法，都是零碎案例，无法达到预期目的，又由于java功底一般，故自己琢磨写了一个简单的方法解决问题，相信网上大神众多，肯定有更好方式和方法，欢迎大神来拍砖指导。。

 



package common;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.*;

/**

 * User: XZee

 * Date: 2011-10-10

 * Time: 16:10:29

 */

public class write_excel {

     /** 总行数 */

    private int totalRows = 0;

    /** 总列数 */

    private int totalCells = 0;

    /** 错误信息 */

    private static String errorInfo;

    private static XSSFWorkbook workbook;

    private static HSSFWorkbook workbook2;

    /** 构造方法

     * @return */

    public int getTotalRows() {

        return totalRows;

    }

    public int getTotalCells() {

        return totalCells;

    }

    public String getErrorInfo() {

        return errorInfo;

    }

   

    public static boolean validateExcel(String filePath) {

        /** 检查文件名是否为空或者是否是Excel格式的文件 */

        if (filePath == null|| !(isExcel2003(filePath) || isExcel2007(filePath))) {

            errorInfo = "文件名不是excel格式";

            return false;

        }

        /** 检查文件是否存在 */

        File file = new File(filePath);

        if (file == null || !file.exists()) {

            errorInfo = "文件不存在";

            return false;

        }

        return true;

    }

    

    

   public static boolean isExcel2003(String filePath) {

  return filePath.matches("^.+\\.(?i)(xls)$");

 }

    public static boolean isExcel2007(String filePath) {

  return filePath.matches("^.+\\.(?i)(xlsx)$");

    }

    /**

     * 只是一个demo，这里假设修改的值是String类型

     * @param filePath

     * @param sheetIndex

     * @param col

     * @param row

     * @param value

     * @throws Exception

     */

    public static void updateExcel(String filePath,int sheetIndex,int row,int col,String value)throws Exception{

        /** 验证文件是否合法 */

        if (!validateExcel(filePath)) {

            System.out.println(errorInfo);

        }

       

        FileInputStream fis=new FileInputStream(filePath);

       

        /** 判断文件的类型，是2003还是2007 */

       

        if (isExcel2007(filePath)) {

            workbook = new XSSFWorkbook(fis);

            XSSFSheet sheet=workbook.getSheetAt(sheetIndex);

            XSSFRow r=sheet.getRow(row);

            XSSFCell cell=r.getCell(col);

           

            String str=cell.getStringCellValue();

            //这里假设对应单元格原来的类型也是String类型

            cell.setCellValue(value);

            System.out.println("单元格原来值为"+str+"\n");

            System.out.println("单元格值被更新为"+value+"\n");

            fis.close();//关闭文件输入流

            FileOutputStream fos=new FileOutputStream(filePath);

            workbook.write(fos);

            fos.close();//关闭文件输出流

        }else if(isExcel2003(filePath)){            

         workbook2 = new HSSFWorkbook(fis);

         HSSFSheet sheet = workbook2.getSheetAt(sheetIndex);

         HSSFRow r = sheet.getRow(row);

         HSSFCell cell = r.getCell(col);   

         String str=cell.getStringCellValue();

         //这里假设对应单元格原来的类型也是String类型

         cell.setCellValue(value);

         System.out.println("单元格原来值为"+str);

         System.out.println("单元格值被更新为"+value);

         fis.close();//关闭文件输入流

         FileOutputStream fos=new FileOutputStream(filePath);

         workbook2.write(fos);

         fos.close();//关闭文件输出流

        }else{

         System.out.println("当前文件，可能不是excel203或excel2007文件，请检查文件");

        }

    }

 

 public static void main(String[] args) throws Exception{

       // TODO Auto-generated method stub

      File file=new File("E:\\workspace\\FE_performance_test\\Datasource\\TestCase.xlsx");

      String file="E:\\workspace\\FE_performance_test\\Datasource\\TestCase.xlsx";

         //下面尝试更改第一行第一列的单元格的值

      write_excel.updateExcel(file,0,6,1,"2哥好腻害");

 }



现在正在做的项目中涉及大量的Excel文件导出导入操作,都是使用Java Excel来操作。 

Java Excel是一开放源码项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。下面我写了一个简单的例子，展示基本的读取，新建，更新(包括常见格式的设置:字体，颜色，背景,合并单元格)，拷贝操作，有这些其实已经基本足够应付大部分问题了。下面是例的源代码:
import java.io.*; 
import java.util.Date; 
import jxl.*; 
import jxl.format.Colour; 
import jxl.format.UnderlineStyle; 
import jxl.read.biff.BiffException; 
import jxl.write.*; 
import jxl.format.UnderlineStyle; 
import jxl.format.CellFormat;; 

public class OperateExcel { 

/** 
  * Read data from a excel file 
  */ 
public static void  readExcel(String excelFileName){ 
  Workbook  rwb = null;  
  try{ 
   InputStream stream = new FileInputStream(excelFileName); 
   rwb = Workbook.getWorkbook(stream); 
   Sheet  sheet = rwb.getSheet(0); 
   Cell   cell  = null; 
   int columns = sheet.getColumns(); 
   int rows    = sheet.getRows(); 
   for( int i=0 ; i< rows ; i++ ) 
    for( int j=0 ; j< columns ; j++){ 
     //attention: The first parameter is column,the second parameter is row.  
     cell = sheet.getCell(j,i);    
     String str00 = cell.getContents(); 
     if( cell.getType() == CellType.LABEL ) 
       str00 += " LAEBL"; 
     else if( cell.getType() == CellType.NUMBER) 
       str00 += " number"; 
     else if( cell.getType() == CellType.DATE) 
       str00 += " date"; 
     System.out.println("00==>"+str00); 
    } 
   stream.close(); 
  } 
  catch(IOException e){  
   e.printStackTrace(); 
  } 
  catch(BiffException e){ 
   e.printStackTrace(); 
  } 
  finally{  
   rwb.close(); 
  } 
} 
/** 
  * create a new excelFile 
  * @param excelFileName create name 
  */ 
public static void createExcelFile(String excelFileName){ 
  try{ 
   WritableWorkbook wwb = Workbook.createWorkbook(new File(excelFileName)); 
   WritableSheet     ws  = wwb.createSheet("sheet1",0); 
   //also,The first parameter is  column,the second parameter is row. 
   // add normal label data 
   Label label00 = new Label(0,0,"Label00"); 
   ws.addCell(label00); 
   //add font formating data   
   WritableFont  wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD , true); 
   WritableCellFormat wff = new WritableCellFormat(wf); 
   Label label10 = new Label(1,0,"Label10",wff); 
   ws.addCell(label10); 
   //add color font formating data 
   WritableFont wf_color = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.DOUBLE_ACCOUNTING,Colour.RED); 
   WritableCellFormat wff_color = new WritableCellFormat(wf_color); 
   wff_color.setBackground(Colour.GRAY_25); //set background coloe to gray  
   Label label20 = new Label(2,0,"Label20",wff_color);   
   ws.addCell(label20); 
   
   //合并单元格 
   WritableFont wf_merge = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.DOUBLE_ACCOUNTING,Colour.GREEN); 
   WritableCellFormat wff_merge = new WritableCellFormat(wf_merge); 
   wff_merge.setBackground(Colour.BLACK); 
   Label label30 = new Label(3,0,"Label30",wff_merge);   
   ws.addCell(label30); 
   Label label40 = new Label(4,0,"Label40"); 
   ws.addCell(label40); 
   Label label50 = new Label(5,0,"Label50"); 
   ws.addCell(label50); 
     //合并 (0,3) (4,0) 
     //attention : 如果合并后面的列不为空,那么就把后面格的内容清空,格式也是按前一个单元格的格式 
   ws.mergeCells(3,0,4,0); 
   
   //添加Number格式数据 
   jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926); 
   ws.addCell(labelN); 
   
   //添加带有formatting的Number对象 
   jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##"); 
   jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); 
   jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN); 
   ws.addCell(labelNF); 
   
   //添加Boolean对象 
   jxl.write.Boolean labelBoolean = new jxl.write.Boolean(2,1,false); 
   ws.addCell(labelBoolean); 
   
   //添加DateTime对象 
   DateTime labelDT = new DateTime(3,1,new Date()); 
   ws.addCell(labelDT); 
   
   //添加带有格式的DataTime数据 
   DateFormat dtf = new DateFormat("yyyy-MM-dd hh:mm:ss"); 
   WritableCellFormat wcfDt = new WritableCellFormat(dtf);   
   wcfDt.setBackground(Colour.YELLOW); 
   DateTime labelDT_format =  new DateTime(4,1,new java.util.Date(),wcfDt); 
   ws.addCell(labelDT_format); 
   ws.mergeCells(4,1,5,1); //比较长,用两列来显示     
   
   wwb.write(); 
   wwb.close(); 
  } 
  catch(IOException e){ 
   e.printStackTrace(); 
  } 
  catch(WriteException e){ 
   e.printStackTrace(); 
  }  
} 
/** 
  * 如何更新Excel文件 
  * @param fileName 
  */ 
public static void updateExcel(String fileName){  
  try{ 
   jxl.Workbook rw = jxl.Workbook.getWorkbook(new File(fileName)); 
   WritableWorkbook wwb = Workbook.createWorkbook(new File(fileName),rw); 
   //这里其实执行的是一次copy操作,把文件先读到内存中,修改后再保存覆盖原来的文件来实现update操作 
   WritableSheet ws  = wwb.getSheet(0); 
   WritableCell wc = ws.getWritableCell(0,0); 
   if( wc.getType() == CellType.LABEL){ 
    Label l = (Label)wc; 
    l.setString(wc.getContents()+"_new"); 
   } 
   wwb.write(); 
   wwb.close(); 
  } 
  catch(IOException e){ 
   e.printStackTrace(); 
  } 
  catch(WriteException e){ 
   e.printStackTrace(); 
  } 
  catch(BiffException e){ 
   e.printStackTrace(); 
  } 
} 
/** 
  * 如何copy Excel文件 
  * @param fileName 
  */ 
public static void copyExcel(String sourFileName,String destFileName){  
  try{ 
   jxl.Workbook rw = jxl.Workbook.getWorkbook(new File(sourFileName)); 
   WritableWorkbook wwb = Workbook.createWorkbook(new File(destFileName),rw); 
   wwb.write(); 
   wwb.close(); 
  } 
  catch(IOException e){ 
   e.printStackTrace(); 
  } 
  catch(WriteException e){ 
   e.printStackTrace(); 
  } 
  catch(BiffException e){ 
   e.printStackTrace(); 
  } 
} 

public static void main(String [] argv){ 
  //OperateExcel.readExcel("E:\\test.xls"); 
  //OperateExcel.createExcelFile("E:\\test1.xls"); 
  //OperateExcel.updateExcel("E:\\test.xls"); 
  OperateExcel.copyExcel("E:\\test.xls","E:\\moon.xls"); 
} 

}