com.aspose.cells使用ICustomFunction自定义函数

com.aspose.cells当前版本20.5中days函数有问题。自定义一个days2函数。

package test;

import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.aspose.cells.ICustomFunction;
import com.aspose.cells.ReferredArea;

/**
 * 自定义函数
 * 使用方法：wb.calculateFormula(true, new CustomFunction());
 * @author 
 * @date 2021年1月7日 下午12:00:26
 */
public class CustomFunction implements ICustomFunction {

	@Override
	public Object calculateCustomFunction(String functionName, ArrayList paramsList, ArrayList contextObjects) {
		/*for (Object o : paramsList) {
			ReferredArea ra = (ReferredArea) o;
			if (ra.isArea()) {
				o = ra.getValues();
			} else {
				o = ra.getValue(0, 0);
			}
		}*/
		try {
			if ("days2".equalsIgnoreCase(functionName)) {
				// days2(end_date, start_date)
				// 如果两个日期参数为数字，DAYS 使用 EndDate–StartDate 计算两个日期之间的天数。
				// 时间戳从1970.1.1开始的毫秒数，日期在EXCEL中是以序列号保存的，默认从1900年1月1日为序号1
				int endDate, startDate;
				ReferredArea ra = (ReferredArea) paramsList.get(0);
				Object o = ra.getValue(0, 0);
				if (o instanceof Double) {
					endDate = (int) (double) o;
				} else {
					String date = o.toString();
					String pattern = date.replaceFirst("\\d{4}", "yyyy")// 年
							.replaceFirst("\\d{1,2}", "MM")// 月
							.replaceFirst("\\d{1,2}", "dd")// 日
							.replaceFirst("\\d{1,2}", "HH")// 时
							.replaceFirst("\\d{1,2}", "mm")// 分
							.replaceFirst("\\d{1,2}", "ss");// 秒
					SimpleDateFormat sdf = new SimpleDateFormat(pattern);
					endDate = (int) org.apache.poi.ss.usermodel.DateUtil.getExcelDate(sdf.parse(date));
				}
				ra = (ReferredArea) paramsList.get(1);
				o = ra.getValue(0, 0);
				if (o instanceof Double) {
					startDate = (int) (double) o;
				} else {
					String date = o.toString();
					String pattern = date.replaceFirst("\\d{4}", "yyyy")// 年
							.replaceFirst("\\d{1,2}", "MM")// 月
							.replaceFirst("\\d{1,2}", "dd")// 日
							.replaceFirst("\\d{1,2}", "HH")// 时
							.replaceFirst("\\d{1,2}", "mm")// 分
							.replaceFirst("\\d{1,2}", "ss");// 秒
					SimpleDateFormat sdf = new SimpleDateFormat(pattern);
					startDate = (int) org.apache.poi.ss.usermodel.DateUtil.getExcelDate(sdf.parse(date));
				}
				int count = endDate - startDate;
				return count;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}

 

package test;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;
import test.CustomFunction;
import util.AsposeLicenseUtil;

public class Test3 {

	public static void main(String[] args) throws Exception {
		// 去水印验证
		AsposeLicenseUtil.getExcelLicense();
		Workbook wb = new Workbook();
		WorksheetCollection sheets = wb.getWorksheets();
		Worksheet sheet = sheets.get(0);
		Cells cells = sheet.getCells();
		Cell A1 = cells.get("A1");
		Cell B1 = cells.get("B1");
		Cell C1 = cells.get("C1");
		Cell D1 = cells.get("D1");
		Cell E1 = cells.get("E1");
		Cell F1 = cells.get("F1");

		A1.setValue("2021-01-01 00:00:00");
		B1.setValue("2021-01-01 23:59:58");
		C1.setValue("2021-01-01 23:59:59");
		D1.setValue("2021-01-02 00:00:00");
		E1.setValue("2021-01-02 23:59:58");
		F1.setValue("2021-01-02 23:59:59");
		
		
		//公式
		Cell A2 = cells.get("A2");
		Cell B2 = cells.get("B2");
		Cell C2 = cells.get("C2");
		Cell D2 = cells.get("D2");
		Cell E2 = cells.get("E2");
		Cell F2 = cells.get("F2");
		
		A2.setFormula("=Days2(D1,C1)");//Days(end_date,start_date)
		B2.setFormula("=Days2(F1,B1)");//Days(end_date,start_date)
		C2.setFormula("=Days360(C1,D1)");// Days360(start_date,end_date)
		D2.setFormula("=Days360(B1,F1)");// Days360(start_date,end_date)
		E2.setFormula("=DATEDIF(C1,D1,\"D\")");// DATEDIF(start_date,end_date,unit)
		F2.setFormula("=DATEDIF(B1,F1,\"D\")");// DATEDIF(start_date,end_date,unit)

		// 计算，然后删除公式
		// wb.calculateFormula(true);// 获取经过公式计算的数据,忽略计算错误
		 wb.calculateFormula(true, new CustomFunction());
		cells.removeFormulas();// 删除公式
		
		Cell A3 = cells.get("A3");
		Cell B3 = cells.get("B3");
		Cell C3 = cells.get("C3");
		Cell D3 = cells.get("D3");
		Cell E3 = cells.get("E3");
		Cell F3 = cells.get("F3");
		
		A3.setFormula("=Days(D1,C1)");//Days(end_date,start_date)
		B3.setFormula("=Days(F1,B1)");//Days(end_date,start_date)
		C3.setFormula("=Days360(C1,D1)");// Days360(start_date,end_date)
		D3.setFormula("=Days360(B1,F1)");// Days360(start_date,end_date)
		E3.setFormula("=DATEDIF(C1,D1,\"D\")");// DATEDIF(start_date,end_date,unit)
		F3.setFormula("=DATEDIF(B1,F1,\"D\")");// DATEDIF(start_date,end_date,unit)

		String nam = "c:/test/g/" + new SimpleDateFormat("HHmmss").format(new Date()) + ".xlsx";
		System.out.println(nam);
		wb.save(new FileOutputStream(nam), SaveFormat.XLSX);
		wb.dispose();
	}

}