Now and Today functions not rendered right?

Hello,

We are trying to move off of office automation to Aspose. We have an xlsx that has cells with the NOW and TODAY function in it.

We use Aspose.CELLS .NET 18.11.4 to recalculate the cells and then render to tiff. We notice that when we do this with office automation, the NOW and TODAY cells render the time/date of the time the tiff file is generated (approximately).

But when we do the same with Aspose, NOW and TODAY reflect the time and date the xlsx was last saved. Is this proper behavior for Aspose or a bug?

Attached is a zip file with
The input xlsx
The resulting tif
A screen shot of the issue I’m talking about.

dates.zip (139.9 KB)

We used the following code to render the tif in C#. Note the part where we call CalculateFormula to make sure we recalc the cell.

private void button1_Click(object sender, EventArgs e)
{
Aspose.Cells.License cellsLicense = new Aspose.Cells.License();
cellsLicense.SetLicense(“C:\interfax\Interfax20\OfficeRenderer\Service\License\Aspose.Total.lic”);

		Aspose.Imaging.License imagingLicense = new Aspose.Imaging.License();
		imagingLicense.SetLicense("C:\\interfax\\Interfax20\\OfficeRenderer\\Service\\License\\Aspose.Total.lic");

		int numPages = 0;
		var asposeWorkbook = new Aspose.Cells.Workbook("c:\\dtemp\\Issue19-Original.xlsx");

		numPages = asposeWorkbook.Worksheets.Count;

		Aspose.Cells.Worksheet selectedWS = null;
		selectedWS = asposeWorkbook.Worksheets[0];



		foreach (Aspose.Cells.Worksheet w in asposeWorkbook.Worksheets)
		{
			if (w.IsSelected)
			{
				bool anySelected = true;
				selectedWS = w;
				// rcalc cells size
				w.AutoFitRows(true);

				// recalc formulas
				foreach (Aspose.Cells.Cell cell in w.Cells)
				{
					try
					{
						if (cell.IsFormula)
							w.CalculateFormula(cell.Formula);
					}
					catch { }
				}

				// recalc pivot tables
				foreach (Aspose.Cells.Pivot.PivotTable pt in w.PivotTables)
				{
					try { pt.RefreshData(); }
					catch { }
					try { pt.CalculateData(); }
					catch { }
					try { pt.CalculateRange(); }
					catch { }
				}

			}
			else
				//mark this worksheet as NOT saveable
				w.IsVisible = false;
		}


		bool flipresizeWS = false;
		Aspose.Cells.Rendering.ImageOrPrintOptions CellsSaveOptions = null;
		Aspose.Cells.Rendering.ImageOrPrintOptions cellRenderoptions = CellsSaveOptions;

		if (cellRenderoptions == null)
		{
			Aspose.Cells.PageSetup setup = selectedWS.PageSetup;

			int horizDpi = 170;
			int vertDpi = 170;
			flipresizeWS = setup.Orientation == Aspose.Cells.PageOrientationType.Landscape;
			cellRenderoptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();
			// Set Horizontal Resolution
			cellRenderoptions.SaveFormat = Aspose.Cells.SaveFormat.TIFF;
			cellRenderoptions.TiffCompression = Aspose.Cells.Rendering.TiffCompression.CompressionCCITT3;

			// Set Vertical Resolution
			cellRenderoptions.VerticalResolution = vertDpi;
			cellRenderoptions.HorizontalResolution = horizDpi;
			cellRenderoptions.Quality = 100;
			cellRenderoptions.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
			cellRenderoptions.PixelFormat = System.Drawing.Imaging.PixelFormat.Format32bppArgb;

			// If you want entire sheet as a single image
			cellRenderoptions.OnePagePerSheet = false;


			// Render to image
			Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(selectedWS, cellRenderoptions);
			sr.ToTiff("c:\\dtemp\\out.tiff");
		}
	}

@danwise,

Thanks for your query.

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46505 - Now and Today functions not rendered right in TIFF file

@danwise,

We did evaluate your sample code further and the following code part is not right. For your information, Worksheet.CalculateFormula(string formula) directly calculates/re-calculates formulas separately (it does not actually perform calculations or embed the results in the worksheet/workbook rather separately calculates a formula and gives you calculation results against the given formula), see the topic for your reference: Calculate Formulas|Documentation
e.g
Sample code:


// recalc formulas
foreach (Aspose.Cells.Cell cell in w.Cells)
{
try
{
if (cell.IsFormula)
w.CalculateFormula(cell.Formula);
}
catch { }
}

Please change the code segment as following:
e.g
Sample code:


// recalc formulas
foreach (Aspose.Cells.Cell cell in w.Cells)
{
try
{
if (cell.IsFormula)
cell.Calculate(false, null);

  			}
  			catch { }
  		}

it will work fine and as expected.

Hope, this helps a bit.

@danwise,

Furthermore, for performance considerations, it would be much better for you to use Workbook.CalculateFormula(CalculationOptions options) or Worksheet.CalculateFormula(CalculationOptions options, bool recursive) to calculate all the formulas of the Workbook or Worksheet.

That code works for me. Thanks for pointing out I was using the wrong function.

@danwise,

Good to know that your issue is sorted out by the suggested code segment. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.