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

比眉伴天荒 2023-10-06 15:57 35阅读 0赞

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();
    	}
    
    }