Hi Amjad,
I tested the code using workbook.CalculateValue() but even then its not updating the cell value and hence returning the value "0.0" for the cell having formula.
I could not attach the code file and templates, so i'm providing the code here.
(Please update the path of the templates in App.Config file)
(The code tries to copy the values of column A from destination worksheet and paste it in column A and B of source worksheet. Column C in the source worksheet is having formulae which will calculate the sum of the values of column A and B for that row. (here is the problem)Then the updated values of column C(which is having formulae) from the source worksheet are copied to column A of the destination worksheet(this returns "0.0").
Let me know the result.
Waiting for your response.
Below are the contents of cofig file and Class file
App.Config file:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="SourceFilePath" value="C:\Documents and Settings\146131\Desktop\SampleTemplateSource.xls"/>
<add key="DestinationFilePath" value="C:\Documents and Settings\146131\Desktop\SampleTemplateDestination.xls"/>
</appSettings>
</configuration>
Class File:
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Configuration;
using Aspose.Cells;
using System.Diagnostics;
namespace SampleCodeCopyValue
{
class Program
{
static void Main(string[] args)
{
#region Variable and Object Declaration
Program oProgram = null;
string strSourceFilePath = null;
string strDestinationFilePath = null;
#endregion
try
{
//Instantiate
oProgram = new Program();
strSourceFilePath = ConfigurationSettings.AppSettings["SourceFilePath"].ToString();
strDestinationFilePath = ConfigurationSettings.AppSettings["DestinationFilePath"].ToString();
oProgram.fnUpdateSource(strSourceFilePath, strDestinationFilePath);
oProgram.fnCopyToDestination(strSourceFilePath, strDestinationFilePath);
}
catch (Exception oException)
{
}
finally
{
GC.Collect();
}
}
public bool fnUpdateSource(string SampleTemplateSource, string SampleTemplateDestination)
{
#region Variable and Object Declaration
Workbook oWorkbookSource = null;
Workbook oWorkbookResult = null;
Range oRange = null;
Worksheet oWorksheetSource = null;
Worksheet oWorksheetResult = null;
ArrayList arrLstSummaryValues = null;
#endregion
try
{
#region Updating Values in the Source Template
//Instantiate
arrLstSummaryValues = new ArrayList();
//Type t1 = Type.GetTypeFromProgID("Excel.Application");
oWorkbookSource = new Workbook();
oWorkbookResult = new Workbook();
//Clear the ArrayList
arrLstSummaryValues.Clear();
//Open the Result Workbook
oWorkbookResult.Open(SampleTemplateDestination);
//Open the Source Workbook
oWorkbookSource.Open(SampleTemplateSource);
//oSheets = oWorkbookSource.Worksheets;
Object[,] oExcelRangeValues;
oWorksheetResult = oWorkbookResult.Worksheets[0];
//Get a range of data.
oRange = oWorksheetResult.Cells.CreateRange(1, 0, 10, 1);
//Retrieve the data from the range.
oExcelRangeValues = (System.Object[,])oRange.Worksheet.Cells.ExportArray(oRange.FirstRow, oRange.FirstColumn, oRange.RowCount, oRange.ColumnCount);
for (int j = 0; j < 10; j++)
{
arrLstSummaryValues.Add(oExcelRangeValues[j, 0].ToString());
}
oWorksheetSource = oWorkbookSource.Worksheets[0];
for (int k = 0, j = 1; k < 10; k++, j += 2)
{
oWorksheetSource.Cells[j, 0].PutValue(arrLstSummaryValues[k].ToString());
oWorksheetSource.Cells[j, 1].PutValue(arrLstSummaryValues[k].ToString());
}
//Clear the arraylist
arrLstSummaryValues.Clear();
oExcelRangeValues = null;
//Save the Result Workbook
oWorkbookSource.Save(SampleTemplateSource);
#endregion
return true;
}
catch (Exception oException)
{
//File.AppendAllText(this.strErrorLogFileName, oException.Message);
return false;
}
finally
{
//Set null for source and result workbook
oWorkbookSource = null;
oWorkbookResult = null;
GC.Collect();
}
}
public bool fnCopyToDestination(string SampleTemplateSource, string SampleTemplateDestination)
{
#region Variable and Object Declaration
Workbook oWorkbookSource = null;
Workbook oWorkbookResult = null;
Range oRange = null;
Worksheet oWorksheetSource = null;
Worksheet oWorksheetResult = null;
ArrayList arrLstSummaryValues = null;
#endregion
try
{
#region Copying values from Source Template to Destination Template
//Instantiate
arrLstSummaryValues = new ArrayList();
oWorkbookSource = new Workbook();
oWorkbookResult = new Workbook();
//Clear the ArrayList
arrLstSummaryValues.Clear();
//Open the Result Workbook
oWorkbookResult.Open(SampleTemplateDestination);
//Open the Source Workbook
oWorkbookSource.Open(SampleTemplateSource);
oWorkbookSource.CalculateFormula(true);
//oSheets = oWorkbookSource.Worksheets;
Object[,] oExcelRangeValues;
oWorksheetSource = oWorkbookSource.Worksheets[0];
////Get a range of data.
//oRange = oWorksheetSource.Cells.CreateRange(1, 2, 19, 1);
////Retrieve the data from the range.
//oExcelRangeValues = (System.Object[,])oRange.Worksheet.Cells.ExportArray(oRange.FirstRow, oRange.FirstColumn, oRange.RowCount, oRange.ColumnCount);
////Retrieving the values from the object and storing it in an arraylist
//for (int j = 0; j < 20; j += 2)
//{
// //add the values to the arraylist
// arrLstSummaryValues.Add(oExcelRangeValues[j, 0].ToString());
//}
//Set the worksheet object to the worksheet in the report template
oWorksheetResult = oWorkbookResult.Worksheets[0];
//Assigning the values to the respective cells
for (int k = 0, j=1; k < 10; k++, j+=2)
{
oWorksheetResult.Cells[k + 1, 0].PutValue(oWorksheetSource.Cells[j,2].Value);
}
//Clear the arraylist
arrLstSummaryValues.Clear();
oExcelRangeValues = null;
//Save the Result Workbook
oWorkbookResult.Save(SampleTemplateDestination);
#endregion
return true;
}
catch (Exception oException)
{
//File.AppendAllText(this.strErrorLogFileName, oException.Message);
return false;
}
finally
{
//Set null for source and result workbook
oWorkbookSource = null;
oWorkbookResult = null;
GC.Collect();
}
}
}
}
Thanks