Free Support Forum - aspose.com

Landray RDM 56727

src.zip (1.6 KB)

it throws error !
唐河2场2区院墙砌筑零星工程结算书.xls.zip (12.5 KB)

@hucq_landray_com_cn,

Thanks for the sample code and template file.

I tested your scenario/case using your sample code with your template file with v18.6 (https://artifact.aspose.com/webapp/#/artifacts/browse/tree/General/repo/com/aspose/aspose-cells/18.6 - please try it), it works fine and I do not get any exception. I used the following sample code:
e.g
Sample code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.CountryCode;
import com.aspose.cells.HtmlHiddenColDisplayType;
import com.aspose.cells.HtmlHiddenRowDisplayType;
import com.aspose.cells.HtmlLinkTargetType;
import com.aspose.cells.HtmlSaveOptions;
import com.aspose.cells.ImageFormat;
import com.aspose.cells.ImageOrPrintOptions;
import com.aspose.cells.License;
import com.aspose.cells.LoadOptions;
import com.aspose.cells.MemorySetting;
import com.aspose.cells.SheetRender;
import com.aspose.cells.Style;
import com.aspose.cells.StyleFlag;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class ExcelDemo {

	private static License excelLic;
	private static String sourceFileDir;
	private static String fileName;

	public static void main(String[] args) throws Throwable {
		sourceFileDir = "f:\\files\\";
		fileName = "Source12.xls";
		if (args != null && args.length > 0) {
			sourceFileDir = args[0];
			if (args.length > 1) {
				fileName = args[1];
			}
		}
		try {
			excelLic = new License();
			//excelLic.setLicense(sourceFileDir + File.separator + "Aspose.Total.Java.lic");
		} catch (Exception e) {
			e.printStackTrace();
		}
		File sourceFileDirFile = new File(sourceFileDir);
		File sourceFile = new File(sourceFileDirFile, fileName);
		LoadOptions loadOptions = new LoadOptions();
		loadOptions.setStandardFont("SimSun");
		loadOptions.setRegion(CountryCode.USA);
		loadOptions.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
		Workbook excel = new Workbook(new FileInputStream(sourceFile), loadOptions);
		excel.acceptAllRevisions();
		HtmlSaveOptions saveOptions = new HtmlSaveOptions();
		saveOptions.setExportActiveWorksheetOnly(true);
		saveOptions.setExportHiddenWorksheet(false);
		saveOptions.setExportImagesAsBase64(true);
		saveOptions.setHiddenColDisplayType(HtmlHiddenColDisplayType.HIDDEN);
		saveOptions.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.HIDDEN);
		saveOptions.setLinkTargetType(HtmlLinkTargetType.BLANK);
		int sheetCount = excel.getWorksheets().getCount();
		// int visibleCount = 0;
		int originActiveIndex = excel.getWorksheets().getActiveSheetIndex();
		Worksheet worksheet = null;
		Workbook checkExcel = getCheckExcel(new FileInputStream(sourceFile));
		boolean canApplyStyle = canApplyStyle(checkExcel, saveOptions);
		for (int i = 1; i <= sheetCount; i++) {
			worksheet = checkExcel.getWorksheets().get(i - 1);
			Cells cells1 = worksheet.getCells();
			Cell cell1 = cells1.get("A6");
			Style style1 = cell1.getStyle();
			System.out.println(style1);
			if (worksheet != null && worksheet.isVisible()) {
				// visibleCount++;
				excel.getWorksheets().setActiveSheetIndex(i - 1);
				if (canApplyStyle) {
					Style newStyle = excel.createStyle();
					newStyle.setTextWrapped(true);
					StyleFlag flag = new StyleFlag();
					flag.setWrapText(true);
					newStyle.setTextWrapped(true);
					Cells cells = worksheet.getCells();
					cells.applyStyle(newStyle, flag);
				}
				Cells cells2 = worksheet.getCells();
				Cell cell2 = cells2.get("A6");
				Style style2 = cell2.getStyle();
				System.out.println(style2);
				if (originActiveIndex == i - 1) {
					worksheet.getPageSetup().setPrintArea("A1:I4");
					ImageOrPrintOptions imgOption = new ImageOrPrintOptions();
					imgOption.setHorizontalResolution(96);
					imgOption.setVerticalResolution(96);
					imgOption.setDefaultFont("SimSun");
					imgOption.setQuality(100);
					imgOption.setOutputBlankPageWhenNothingToPrint(true);
					imgOption.setImageFormat(ImageFormat.getPng());
					SheetRender sheetRender = new SheetRender(worksheet, imgOption);
					sheetRender.toImage(0, new FileOutputStream(new File(sourceFileDirFile, "thumbnail.png")));
				}
				excel.save(new FileOutputStream(new File(sourceFileDirFile, "pageResult" + i + ".html")), saveOptions);
			}
			System.gc();
		}
	}

	private static boolean canApplyStyle(Workbook checkExcel, HtmlSaveOptions saveOptions) {
		boolean result = true;
		int sheetCount = checkExcel.getWorksheets().getCount();
		Worksheet worksheet = null;
		try {
			for (int i = 1; i <= sheetCount; i++) {
				worksheet = checkExcel.getWorksheets().get(i - 1);
				if (worksheet != null && worksheet.isVisible()) {
					Style newStyle = checkExcel.createStyle();
					newStyle.setTextWrapped(true);
					StyleFlag flag = new StyleFlag();
					flag.setWrapText(true);
					newStyle.setTextWrapped(true);
					Cells cells = worksheet.getCells();
					cells.applyStyle(newStyle, flag);
					checkExcel.getWorksheets().setActiveSheetIndex(i - 1);
					checkExcel.save(new FileOutputStream(new File(sourceFileDir, "canapplystle.html")), saveOptions);
				}
			}
		} catch (Throwable e) {
			result = false;
		}
		return result;
	}

	private static Workbook getCheckExcel(FileInputStream fileInputStream) {
		LoadOptions loadOptions = new LoadOptions();
		loadOptions.setStandardFont("SimSun");
		loadOptions.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
		Workbook result = null;
		try {
			result = new Workbook(fileInputStream, loadOptions);
		} catch (Exception e) {

		}
		return result;
	}

}

I simply renamed your template file to “Source12.xls” and updated the sourceFileDir and fileName variables values in your code to specify it.

I have attached the output files for your reference.
files1.zip (21.3 KB)

unknowfunction.png (89.6 KB)
but we still throws this error!

unknowfunction with id XXXXXXXXX

@hucq_landray_com_cn,

Well, that is strange. Did you extract and zipped archive which I attached in my previous post? You can see the output files are fine tuned. Please make sure that you are using our latest version/fix (e.g v18.7 or v18.6). Could you print the version number in code for confirmation. For example, you may add a line of code (at the start) to your code segment:
e.g
Sample code:

System.out.println("Aspose cells version " + CellsHelper.getVersion());

I am sure we use 18.7

Caused by:com.aspose.cells.CellsException: Unknown function with id[257]("Based on cell "概算!D6) com.aspose.cells.zaax.c(Unknown Source) com.aspose.cells.zaaz.a(Unknown Source) com.aspose.cells.zaaz.c(Unknown Source) com.aspose.cells.zaaz.a(Unknown Source) com.aspose.cells.zabz.b(Unknown Source) com.aspose.cells.zabu.a(Unknown Source) com.aspose.cells.zabu.e(Unknown Source) com.aspose.cells.zabq$zf.k(Unknown Source) com.aspose.cells.zabq.d(Unknown Source) com.aspose.cells.zabn.e(Unknown Source) com.aspose.cells.zbzb.a(Unknown Source) com.aspose.cells.zaby.a(Unknown Source) com.aspose.cells.zaby.b(Unknown Source) com.aspose.cells.zabz.a(Unknown Source) com.aspose.cells.zabz.b(Unknown Source) com.aspose.cells.zacr.a(Unknown Source) com.aspose.cells.zbaw.C(Unknown Source) com.aspose.cells.zbaw.h(Unknown Source) com.aspose.cells.zbco.a(Unknown Source) com.aspose.cells.zgc.a(Unknown Source) com.aspose.cells.zabk.k(Unknown Source) com.aspose.cells.zabk.d(Unknown Source) com.aspose.cells.zaaz.a(Unknown Source) com.aspose.cells.zaaz.c(Unknown Source) com.aspose.cells.zaaz.a(Unknown Source) com.aspose.cells.zbza.a(Unknown Source) com.aspose.cells.zaby.a(Unknown Source) com.aspose.cells.zaby.a(Unknown Source) com.aspose.cells.zabz.a(Unknown Source) com.aspose.cells.zabz.b(Unknown Source) com.aspose.cells.zabu.a(Unknown Source) com.aspose.cells.zabu.e(Unknown Source) com.aspose.cells.zbza.a(Unknown Source) com.aspose.cells.zaby.a(Unknown Source) com.aspose.cells.zaby.a(Unknown Source) com.aspose.cells.zabz.a(Unknown Source) com.aspose.cells.zabz.b(Unknown Source) com.aspose.cells.zaby.c(Unknown Source) com.aspose.cells.Workbook.calculateFormula(Unknown Source) com.aspose.cells.Workbook.calculateFormula(Unknown Source) com.aspose.cells.Workbook.calculateFormula(Unknown Source)

@landray.com.cn,

I have tried again using latest version Aspose.Cells for Java 18.7, JRE JavaSE-1.8, Windows 7 and Eclipse Java Photon. Please download the following sample project and test the scenario again. If issue is still not resolved, provide us your sample code (runnable), your environment details, operating system, JDK and any other relevant information.

SampleProject.zip (49.6 KB)

@landray.com.cn,

We found you might have given us the wrong code because your provided sample program cannot reproduce the issue. By further investigation, we found if we instantiate a Workbook from the used template file “source12.xls” and calculate it, we can get the exception you mentioned:
e.g
Sample code:

Workbook wb = new Workbook(&quot;source12.xls&quot;);

wb.calculateFormula(false);

We found there are some formulas in this template that Aspose.Cells does not support. You may set the “IgnoreError” parameter for calculate option as true to avoid the exception:

wb.calculateFormula(false);

In future versions we may consider to set the calculated value as “#NAME?” just like MS Excel does for those special functions, such as “EVALUATE()” instead of throwing exception.