Can aspose convert mht to excel including formula?

Aspose.Cells Product Family
1

can aspose convert mht to excel including formula? In a cell, we want to do some simple forumla like:

=IF(1.1=0,“zero”,“non-zero”)

2

@jtsui,

Aspose.Cells can convert MHT (MHTML) files to Excel formats (XLSX, XLS, etc.). How do you set formula in MHTML file? Do you set the formula as string? Could you please share sample MHTML file. We will check it soon.

3

Basically, I created a simple spreadsheet in excel. I entered the formular =IF(1.1=0,“zero”,“non-zero”) in one cell. Then, I save the spreadsheet to the mht format file in excel.

Then, when I called the aspose api to convert the mht file to xls format. The result output file just simply displays the format as string in the cell rather than displaying the output of the formula which I expect it to be “non-zero” (1.1 is not zero).

Here is the sample java code:
HtmlLoadOptions loadOptions = new HtmlLoadOptions(LoadFormat.M_HTML);
loadOptions.setLoadStyleStrategy(TxtLoadStyleStrategy.BUILT_IN);
saveFormat = SaveFormat.EXCEL_97_TO_2003;
Workbook workbook = new Workbook(srcFile, loadOptions);
workbook.save(dstFile, saveFormat);

4

@jtsui
If you directly save the file as mht in Excel, the mht file does not contain formulas. So when you use cells to import the saved MHT file, there are no formulas in the resulting file. Please refer to the attachment. sample.zip (10.3 KB)

By creating a sample file and testing it on the latest version v25.4 using the following sample code, we can obtain the correct results. Please refer to the attachment. result.zip (11.7 KB)

// Load the workbook
Workbook wb = new Workbook(filePath + "sample.xlsx");

wb.save(filePath + "out_java.mht");

HtmlLoadOptions loadOptions = new HtmlLoadOptions(LoadFormat.M_HTML);
loadOptions.setLoadStyleStrategy(TxtLoadStyleStrategy.BUILT_IN);
Workbook workbook = new Workbook(filePath + "out_java.mht", loadOptions);

System.out.println(workbook.getWorksheets().get(0).getCells().get("B2").getFormula());
workbook.save(filePath + "out_java.xls", SaveFormat.EXCEL_97_TO_2003);

The output:

=IF(1.1=0,"zero","non-zero")

We recommend you to kindly try using our latest version: Aspose.Cells for Java v25.4.

If you still find the issue, kindly do share your complete sample (runnable) code and template Excel file (if any) to reproduce the issue on our end, we will check it soon.

5

John,

actually, in order to display the formula correctly, I had to enter the formula to the mht file with a text editor as follow, basically, the equal sign is entered as =3D

=3DIF(1.1=3D0,“zero”,“nozero”)

I will email you my sample mht file separately.

Thanks,
James

6

@jtsui
Thank you for your feedback. Please send the mht file to the email address: john.he@aspose.com.

8

@jtsui
If you need to use Excel to save the file as MHT and manually insert formula data. You can refer to the following string.

<td x:fmla=3d'=3dIF(1.1=3d0,&quot;zero&quot;,&quot;non-zero&quot;)'>nozero</td>

By modifying the MHT file saved in Excel and testing it using the following sample code, we can obtain the correct results. Please refer to the attachment. result.zip (15.6 KB)

HtmlLoadOptions loadOptions = new HtmlLoadOptions(LoadFormat.M_HTML);
loadOptions.setLoadStyleStrategy(TxtLoadStyleStrategy.BUILT_IN);
Workbook workbook = new Workbook(filePath + "sample.mht", loadOptions);

System.out.println(workbook.getWorksheets().get(0).getCells().get("B2").getFormula());
workbook.save(filePath + "out_java.xls", SaveFormat.EXCEL_97_TO_2003);

The output:

=IF(1.1=0,"zero","non-zero")
9

Thanks for the solution. It is working now.

10

@jtsui
Thank you for your feedback. I’m glad your issue has been resolved. If you have any questions, please feel free to contact us at any time.

Additionally, we do not recommend that you directly modify the mht file as this may result in unpredictable errors.

11

@jtsui
The embedded html file in the mht file is encoded with “quoted-printable” Encoding.
If you manually insert text into Mht file, please encode it strictly in “quoted-printable” , otherwise the Mht file will be damaged.