Problem in copying only value for cells having formula

Hi,

I'm using Aspose.Cells evaluation version for .NET (V4.3).

I am having a template which is having formulae in its cells, it calculates values using these formulae.

I want to copy only the values(not formula) from these cells and paste them into another workbook.

I tried many times, but its returning a value of "0.0" everytime for the cells having formula, and not the exact value.

Please help !!

Hi,

Thanks for considering Aspose.

Well, by default the formulas are not calculated to give resultant values. So, you have to call Workbook.CalculateFormula() method before retrieving a formulated cell's value This method explicitly calculate all the formulas in the workbook

Could you try this and response us if it fine now. If you still find the problems, kindly do send the template file with your sample code.

Thank you.

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

Hi,

And following is my testing code in a console application with the template file (attached) and th output file (also attached) is fine.

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\test_rangecopy.xls");
Worksheet oSheetSource = workbook.Worksheets[0];
Range oRange = oSheetSource.Cells.CreateRange("A1", "D2");
workbook.CalculateFormula(false);
//Retrieve the data from the range.
object[,] oExcelRangeValues;
oExcelRangeValues = (System.Object[,])oRange.Worksheet.Cells.ExportArray(0, 0, 2, 4);
//Copy the values to the required worksheet in the Result Workbook
Workbook wkb = new Workbook();
Worksheet oWorksheet = wkb.Worksheets[0];
oWorksheet.Cells.ImportTwoDimensionArray(oExcelRangeValues, 5, 2);
wkb.Save("d:\\test\\test_copyvalues.xls");
I think without your template excel file we are unable to figure out your issue. Could you please create a sample console application using your template file that shows the problem. and do post your complete project here. We will check and figure out your issue soon.
Please zip your project, follow the steps below to post it here:
Click["Options"]tab -> click ["Add/Update"] button -> then click ["Browse"] button to select the zipped file -> and ["Save"] it.
And always use the latest version 4.3.
Thank you.

Please find herewith the attached source and destination sample. This isn't working fine.

Hi,

Thanks for considering Aspose

I have reviewed a bit and tested your code with your template files.

I have the following factors:

1. Since you are using ArrayList class as it may insert values as strings into cells of the columns A and B in the Source file. So, consequently the forumulas in column C do not calculated properly and the formula results remain as "0".

2. According to your code, the oWorkbookResult workbook should be saved first and then oWorkbookSource workbook should be saved to disk. Doing this, it will work fine.

3. Try to optimize your code and in both Boolean custom methods (fnUpdateSource, fnCopyToDestination ) you can give both workbook objects as parameters.

I have tried to optimize and minimize your code and it gives perfect results for your requirements, the output files are also attached, kindly check them.

Following is your code with a few adjustment. I added a webform "WebForm4" in the web project. I put both your template files into "Designer" folder which is in my Application folder.

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 CellsApp
{
///


/// Summary description for WebForm4.
///

public class WebForm4 : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{

string strSourceFilePath = null;
string strDestinationFilePath = null;

try

{

//Instantiate
Workbook oWorkbookSource = new Workbook();
Workbook oWorkbookResult = new Workbook();
strSourceFilePath = MapPath("Designer") + "\\SampleTemplateSource.xls";
strDestinationFilePath = MapPath("Designer") + "\\SampleTemplateDestination.xls";
fnUpdateSource(strSourceFilePath, strDestinationFilePath,oWorkbookSource,oWorkbookResult);

fnCopyToDestination(strSourceFilePath, strDestinationFilePath, oWorkbookSource,oWorkbookResult);

//Save the Result Workbook destination

oWorkbookResult.Save("d:\\test\\sampledestinationf.xls");

//Save the Result Workbook Source

oWorkbookSource.Save("d:\\test\\samplesourcef.xls");

}

catch (Exception oException)

{
Response.Write(oException.Message);
}

finally

{



}



// Put user code to initialize the page here
}

public bool fnUpdateSource(string SampleTemplateSource, string SampleTemplateDestination, Workbook oWorkbookSource,Workbook oWorkbookResult)

{

Range oRange = null;


try

{

//Open the Result Workbook

oWorkbookResult.Open(SampleTemplateDestination);

//Open the Source Workbook

oWorkbookSource.Open(SampleTemplateSource);

object[,] oExcelRangeValues;

Worksheet 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);

Worksheet oWorksheetSource = oWorkbookSource.Worksheets[0];

for (int k = 0, j = 1; k < 10; k++, j += 2)

{

oWorksheetSource.Cells[j, 0].PutValue(oExcelRangeValues[k,0]);
oWorksheetSource.Cells[j, 1].PutValue(oExcelRangeValues[k,0]);
}

return true;

}

catch (Exception oException)

{

Response.Write(oException.Message);
return false;

}

finally

{


}

}

public bool fnCopyToDestination(string SampleTemplateSource, string SampleTemplateDestination, Workbook oWorkbookSource,Workbook oWorkbookResult)

{

try

{

oWorkbookSource.CalculateFormula(true);

Worksheet oWorksheetSource = oWorkbookSource.Worksheets[0];

//Set the worksheet object to the worksheet in the report template

Worksheet 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);

}

return true;

}

catch (Exception oException)

{

Response.Write(oException.Message);
return false;

}

finally

{


}

}


#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InitializeComponent()
{



this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}

Hope, it will help you.

Thank you.