JAVA 操作EXCEL的问题,能否取得EXCEL公式中的值 我想用JAVA来读取EXCEL文件,只要是想通过excel的公式取计算结果。比如我的一个EXCEL文件A1=10A2=20A3=SUM(A1+A2)我如果打开excel以后,给A1赋值=10 给A2赋值=20然后我读取A3的值是否得到30。如果可以的话用哪个lib可以完成 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我用的jxl的版本为2.6我建一个excel文档,存放为 d:\Book1.xls并在Book1.xls中设置值:A1=100B1=200C1=SUM(A1:A2)根据公式进行计算的话,C1的值应为300但是我根据d:\Book1.xls生成了一个相同的Book2.xls以后,值也正常写入Book2.xls但是当取得Book2.xls中C1的值的时候,取出来的为0,没有得到公式的结果。当我手工打开Book2.xls以后,不做任何修改Excel会提示:是否保存你对“Book2.xls”所做的更改?Microsoft Excel打开由早期版本 Excel最后保存的文件时会自动重算所有公式。进行保存,再次运行程序,C1计算后的值就以正常取得了。运行结果如下://第一次生成excel情况Sheet1Formula in C1 value: 0.00 formula: SUM(A1:B1)//第二次保存excel情况Sheet1Formula in C1 value: 300.00 formula: SUM(A1:B1)如何让其第一次就可以取得值,不用人工进行干预就可以进行完成.我主要是想利用excel的公式来完成一些表格的计算.然后保存计算结果.import jxl.read.biff.BiffException;import jxl.biff.formula.FormulaException;import jxl.write.*;import jxl.write.Number;import jxl.*;import java.util.ArrayList;import java.util.Iterator;import java.io.*;public class TestExcel { public static void createNewExcel() { try { Workbook rw = Workbook.getWorkbook(new File("D:\\Book1.xls")); WritableWorkbook wwb = Workbook.createWorkbook(new File("D:\\Book2.xls"), rw); WritableSheet ws = wwb.getSheet(0); NumberFormat nf = new NumberFormat("#.##"); WritableCellFormat wcfN = new WritableCellFormat(nf); Number number1 = new Number(0, 0, 100, wcfN); Number number2 = new Number(1, 0, 200, wcfN); ws.addCell(number1); ws.addCell(number2); wwb.write(); wwb.close(); } catch (FileNotFoundException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } catch (IOException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } catch (WriteException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } catch (BiffException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } } public static void formulas() { String file = "d:\\Book2.xls"; String encoding = "UTF8"; try { Workbook w = Workbook.getWorkbook(new File(file)); formulas(w, System.out, encoding); w.close(); } catch (Exception e) { e.printStackTrace(); } } public static void formulas(Workbook w, PrintStream out, String encoding) throws IOException { if (encoding == null || !encoding.equals("UnicodeBig")) { encoding = "UTF8"; } try { OutputStreamWriter osw = new OutputStreamWriter(out, encoding); BufferedWriter bw = new BufferedWriter(osw); ArrayList parseErrors = new ArrayList(); for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) { Sheet s = w.getSheet(sheet); bw.write(s.getName()); bw.newLine(); Cell[] row = null; Cell c = null; for (int i = 0; i < s.getRows(); i++) { row = s.getRow(i); for (int j = 0; j < row.length; j++) { c = row[j]; if (c.getType() == CellType.NUMBER_FORMULA || c.getType() == CellType.STRING_FORMULA || c.getType() == CellType.BOOLEAN_FORMULA || c.getType() == CellType.DATE_FORMULA || c.getType() == CellType.FORMULA_ERROR) { FormulaCell nfc = (FormulaCell) c; StringBuffer sb = new StringBuffer(); jxl.biff.CellReferenceHelper.getCellReference(c.getColumn(), c.getRow(), sb); try { bw.write("Formula in " + sb.toString() + " value: " + c.getContents()); bw.flush(); bw.write(" formula: " + nfc.getFormula()); bw.flush(); bw.newLine(); } catch (FormulaException e) { bw.newLine(); parseErrors .add(s.getName() + '!' + sb.toString() + ": " + e.getMessage()); } } } } } bw.flush(); bw.close(); if (parseErrors.size() > 0) { System.err.println(); System.err.println("There were " + parseErrors.size() + " errors"); Iterator i = parseErrors.iterator(); while (i.hasNext()) { System.err.println(i.next()); } } } catch (UnsupportedEncodingException e) { System.err.println(e.toString()); } } public static void main(String[] li) { createNewExcel(); formulas(); }} 你为什么需要操作EXCEL公式呢?一般情况下,你读取或者设置基本单元格的数据,公式用excel的,这样不就OK了,比如你想操作excel,写工资单,合计部分不需要你写的,你只需要把事先设计好的exel模版弄好,一个萝卜一个坑,你打开exel的时候,软件会自动生成公式计算额结果。 我关键是还得把结果给取回来做一下保存,所以写完excel以后,我主要是想通过excel的公式进行计算,并把计算的结果拿回来 interface 接口 问题求解 Eclipse+CVSNT,链接正常,但只有空的文件夹,没有任何文件! 我想查看javax.servlet.*和java.servlet.http.*的函数的说明,有没有他api文档 各位大侠对各个版本的可以生成Excel文档的软件有什么看法,例如POI 有关树的算法问题 招聘要求 工作经验问题 java时间问题 100 分求《O'Reilly java swing, 2nd》 如何将字节流转换成中文字符串 总是提示验证码不正确,请问如何 我想将自己定义的时间+到数据库中怎么+?
我建一个excel文档,存放为 d:\Book1.xls
并在Book1.xls中设置值:
A1=100
B1=200
C1=SUM(A1:A2)根据公式进行计算的话,C1的值应为300但是我根据d:\Book1.xls生成了一个相同的Book2.xls以后,值也正常写入Book2.xls但是当取得Book2.xls中C1的值的时候,取出来的为0,没有得到公式的结果。当我手工打开Book2.xls以后,不做任何修改Excel会提示:是否保存你对“Book2.xls”所做的更改?
Microsoft Excel打开由早期版本 Excel最后保存的文件时会自动重算所有公式。进行保存,再次运行程序,C1计算后的值就以正常取得了。运行结果如下:
//第一次生成excel情况
Sheet1
Formula in C1 value: 0.00 formula: SUM(A1:B1)
//第二次保存excel情况
Sheet1
Formula in C1 value: 300.00 formula: SUM(A1:B1)如何让其第一次就可以取得值,不用人工进行干预就可以进行完成.我主要是想利用excel的公式来完成一些表格的计算.然后保存计算结果.import jxl.read.biff.BiffException;import jxl.biff.formula.FormulaException;import jxl.write.*;
import jxl.write.Number;
import jxl.*;import java.util.ArrayList;
import java.util.Iterator;
import java.io.*;
public class TestExcel {
public static void createNewExcel() {
try {
Workbook rw = Workbook.getWorkbook(new File("D:\\Book1.xls"));
WritableWorkbook wwb = Workbook.createWorkbook(new File("D:\\Book2.xls"), rw);
WritableSheet ws = wwb.getSheet(0);
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfN = new WritableCellFormat(nf);
Number number1 = new Number(0, 0, 100, wcfN);
Number number2 = new Number(1, 0, 200, wcfN);
ws.addCell(number1);
ws.addCell(number2);
wwb.write();
wwb.close();
} catch (FileNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (IOException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (WriteException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (BiffException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
}
public static void formulas() {
String file = "d:\\Book2.xls";
String encoding = "UTF8";
try {
Workbook w = Workbook.getWorkbook(new File(file));
formulas(w, System.out, encoding); w.close();
} catch (Exception e) {
e.printStackTrace();
}
} public static void formulas(Workbook w, PrintStream out, String encoding)
throws IOException {
if (encoding == null || !encoding.equals("UnicodeBig")) {
encoding = "UTF8";
} try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw); ArrayList parseErrors = new ArrayList(); for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) {
Sheet s = w.getSheet(sheet); bw.write(s.getName());
bw.newLine(); Cell[] row = null;
Cell c = null; for (int i = 0; i < s.getRows(); i++) {
row = s.getRow(i); for (int j = 0; j < row.length; j++) {
c = row[j];
if (c.getType() == CellType.NUMBER_FORMULA
|| c.getType() == CellType.STRING_FORMULA
|| c.getType() == CellType.BOOLEAN_FORMULA
|| c.getType() == CellType.DATE_FORMULA
|| c.getType() == CellType.FORMULA_ERROR) {
FormulaCell nfc = (FormulaCell) c;
StringBuffer sb = new StringBuffer();
jxl.biff.CellReferenceHelper.getCellReference(c.getColumn(),
c.getRow(), sb); try {
bw.write("Formula in " + sb.toString()
+ " value: " + c.getContents());
bw.flush();
bw.write(" formula: " + nfc.getFormula());
bw.flush();
bw.newLine();
} catch (FormulaException e) {
bw.newLine();
parseErrors
.add(s.getName() + '!' + sb.toString()
+ ": " + e.getMessage());
}
}
}
}
}
bw.flush();
bw.close(); if (parseErrors.size() > 0) {
System.err.println();
System.err.println("There were " + parseErrors.size()
+ " errors"); Iterator i = parseErrors.iterator();
while (i.hasNext()) {
System.err.println(i.next());
}
}
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
} public static void main(String[] li) {
createNewExcel();
formulas();
}
}