操作excel表格用公式来处理数据时,可通过创建公式来运算数据,或通过读取公式来获取数据信息来源。本文以通过java代码来演示在excel中创建及读取公式的方法。这里使用了excel java类库(free spire.xls for java 免费版),在官网下载获取文件包后,解压,将lib文件夹下的jar文件导入java程序;或者通过并导入。导入结果如下:
import com.spire.xls.*; public class addformula { public static void main(string[] args) { //创建workbook对象 workbook wb = new workbook(); //获取第一个工作表 worksheet sheet = wb.getworksheets().get(0); //声明两个变量 int currentrow = 1; string currentformula = null; //设置列宽 sheet.setcolumnwidth(1, 32); sheet.setcolumnwidth(2, 16); //写入用于测试的数据到单元格 sheet.getcellrange(currentrow,1).setvalue("测试数据:"); sheet.getcellrange(currentrow,2).setnumbervalue(1); sheet.getcellrange(currentrow,3).setnumbervalue(2); sheet.getcellrange(currentrow,4).setnumbervalue(3); sheet.getcellrange(currentrow,5).setnumbervalue(4); sheet.getcellrange(currentrow,6).setnumbervalue(5); //写入文本 currentrow += 2; sheet.getcellrange(currentrow,1).setvalue("公式:") ; ; sheet.getcellrange(currentrow,2).setvalue("结果:"); //设置单元格格式 cellrange range = sheet.getcellrange(currentrow,1,currentrow,2); range.getstyle().getfont().isbold(true); range.getstyle().setknowncolor(excelcolors.lightgreen1); range.getstyle().setfillpattern(excelpatterntype.solid); range.getstyle().getborders().getbyborderslinetype(borderslinetype.edgebottom).setlinestyle(linestyletype.medium); //算数运算 currentformula = "=1/2+3*4"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //日期函数 currentformula = "=today()"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); sheet.getcellrange(currentrow,2).getstyle().setnumberformat("yyyy/mm/dd"); //时间函数 currentformula = "=now()"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); sheet.getcellrange(currentrow,2).getstyle().setnumberformat("h:mm am/pm"); //if函数 currentformula = "=if(b1=5,\"yes\",\"no\")"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //pi函数 currentformula = "=pi()"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //三角函数 currentformula = "=sin(pi()/6)"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //计数函数 currentformula = "=count(b1:f1)"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //最大值函数 currentformula = "=max(b1:f1)"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //平均值函数 currentformula = "=average(b1:f1)"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //求和函数 currentformula = "=sum(b1:f1)"; sheet.getcellrange(++currentrow,1).settext(currentformula); sheet.getcellrange(currentrow,2).setformula(currentformula); //保存文档 wb.savetofile("addformulas.xlsx",fileformat.version2013); wb.dispose(); } }
公式创建结果:
import com.spire.xls.*; public class readformula { public static void main(string[] args) { //加载excel文档 workbook wb = new workbook(); wb.loadfromfile("addformulas.xlsx"); //获取第一个工作表 worksheet sheet = wb.getworksheets().get(0); //遍历b1到b13的单元格 for (object cell: sheet.getcellrange("b1:b13")) { cellrange cellrange = (cellrange)cell; //判断单元格是否含有公式 if (cellrange.hasformula()) { //打印单元格及公式 string certaincell = string.format("单元格[%d, %d]含有公式:", cellrange.getrow(), cellrange.getcolumn()); system.out.println(certaincell + cellrange.getformula()); } } } }
公式读取结果:
(本文完)
如对本文有疑问, 点击进行留言回复!!
(已解决)宝塔面板SSL失败|无法启动,提示 nginx: configuration file /www/server/nginx/conf/nginx.conf test failed
JAVAWEB第六天——JQuery速成(2)【表单校验优化,美观可视化升级,Validation插件,省市二级联动升级】
Flutter Navigator路由,返回,替换路由及返回指定页面
springboot插入时间出现 at [Source: (PushbackInputStream); line: 9, column: 12]的问题。
使用Spring Cloud Bus自动动态刷新配置文件的流程总结
网友评论