Adding existing spreadsheet replces functions with Print_Area

[Mark Oudesluys]

Hi,

I am creating a book of spreadsheets based on a predefined set of sheets to add. When I add one of my sheets with functions in the cells, specifically EOMONTH, it gets replaced by the named range Print_Area from my Cover Sheet..

I have attached the result file, so you can see the problem. The sheet named "Page 1" shows the incorrect result (there are other formatting issues that appear as well). I also attached the source workbook so you can see the original formula.

Here is the code I use to add this sheet:
*******************************************
sheetLocation = templateLocation + "Cover_e.xls";
if (File.Exists(sheetLocation))
{
destWorkbook_e = excelEngine.Excel.Workbooks.Open(sheetLocation);
destWorkbook_e.Worksheets[destWorkbook_e.Worksheets.Count - 1].Activate();
wsPage = destWorkbook_e.ActiveSheet;
wsPage.Range[wsPage.Names["ReportTitle"].Value].Text = prodName;
wsPage.Range[wsPage.Names["TheDate"].Value].Value = theDate;

sheetLocation = templateLocation + "ToC_e.xls";
if (File.Exists(sheetLocation))
{
sourceWorkbook = excelEngine.Excel.Workbooks.Open(sheetLocation);
destWorkbook_e.Worksheets.AddCopy(sourceWorkbook.Worksheets[0], ExcelWorksheetCopyFlags.CopyAll);
sourceWorkbook.Close();
destWorkbook_e.Worksheets[destWorkbook_e.Worksheets.Count - 1].Activate();
wsPage = destWorkbook_e.ActiveSheet;
wsPage.Range[wsPage.Names["TheDate"].Value].Value = theDate;
}

string prevCompCode = "0-0";
string compCode = "";
foreach (DataRow drReps in dtReps.Rows)
{
string pageNo = drReps["pageno"].ToString();
bool isAnnualized = Convert.ToBoolean(drReps["is_annualized"]);
string universeId = drReps["universeid"].ToString();
compCode = pageNo + "-" + drReps["chart_id"].ToString();
if (compCode != prevCompCode)
{
sheetLocation = templateLocation + drReps["chart_name"] + "_e.xls";
if (File.Exists(sheetLocation))
{
sourceWorkbook = excelEngine.Excel.Workbooks.Open(sheetLocation);
destWorkbook_e.Worksheets.AddCopy(sourceWorkbook.Worksheets[0], ExcelWorksheetCopyFlags.CopyAll);
sourceWorkbook.Close();
destWorkbook_e.Worksheets[destWorkbook_e.Worksheets.Count - 1].Activate();
wsPage = destWorkbook_e.ActiveSheet;
wsPage.Name = "Page " + pageNo;
wsPage.Range[wsPage.Names["Base_Date"].Value].Value = theDate;
wsPage.Range[wsPage.Names["Annualized"].Value].Value = ((isAnnualized) ? "y" : "n");
wsPage.Range[wsPage.Names["CalendarFlag"].Value].Value = ((isCalendar) ? "y" : "n");
wsPage.Range[wsPage.Names["UniverseID"].Value].Value = universeId;
}
prevCompCode = compCode;
}
}

//if (destWorkbook_e.Worksheets.Count > 1) destWorkbook_e.Worksheets[0].Remove();
destWorkbook_e.Worksheets[0].Activate();
destWorkbook_e.SaveAs(sSaveAs, ExcelSaveType.SaveAsXLS);
destWorkbook_e.Close();
}
*******************************************

When I debug the code and check the sourceWorkbook sheet for the range D4, it shows the correct value in the cell: sourceWorkbook.Worksheets[0].Range["D4"].Value="=EOMONTH(D5,IF(Annualized="Y",D2,D1))+1".
But when I add the sheet to the desination Workbook, the same range changes: destWorkbook_e.ActiveSheet.Range["D4"].Value="=CoverPage!Print_Area(D5,IF(Annualized="Y",D2,D1))+1".

Can you tell me what I am doing wrong or is this a glitch in your software?

Thanks,
Mark



ExcelTemplates_bde9932e.zip

[Mark Oudesluys]

Hi,

Further to this issue, I also notice that the sheet I added (Quartile_3_10.xls) has a few settings changed on the chart after it is added to the result workbook (Page 1). Namely, the font setting for both the y-axis values and the legend gets modified.

If you could answer that issue as well I would appreciate it.

Thanks,
Mark

[Sathish Babu R]

Hi Mark,

Thank you for your interest in Syncfusion products.

AddCopy method issue:

We are unable to reproduce the issue at our side. We have created a sample with the input template given by you in the last update and also we have tested the sample with the assembly version 8.2.0.18 (3.5 .Net Framework). Could you please modify the below attached sample and revert us back with the same. In addition, could you share the details given below?

- .Net Framework version
- Essential Studio version

Sample:
http://help.syncfusion.com/samples/xlsio.windows/supports/F95465-Add%20Copy%20Issue.zip

Chart Issue:

We are not able to see the issue in chart font setting, but we are able to see the chart axis not preserved properly in assembly version 8.2.0.18. We have attached the screen shot for your reference. This issue mentioned here is suspected to be a defect. Could you please report this issue through Direct Trac Developer Support System?
https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents so that we can provide you with the patch for this issue as well as you can take the advantage of the expertise of a dedicated support engineer and a guaranteed response time and we hope you will take advantage of this system as well.

Screen Shot:
http://help.syncfusion.com/samples/xlsio.windows/supports/Output.png

Please let us know if you have any queries.

Regards,
Sathish

[Mark Oudesluys]

Hi,

I have modified the project you sent me to add a few lines to demonstrate what happens when I add the 'Quartile 3 + 10' sheet to an existing workbook that contains the Cover_e.xls sheet. This mimics how my code currently works, minus all the dynamic database stuff. In the output file, check the date fields just above the chart, rows 4 & 5 columns D to P to see how the EOMONTH() gets changed to Cover!Print_Area().

Thanks,
Mark



AddCopyIssue_3a193b27.zip

[Sathish Babu R]

Hi Mark,

We were able to reproduce the issue at our side and this issue is suspected to be a defect. We have sent this to our development team for more analysis.

However, it would be great, if you could open an incident in the direct-trac to further follow-up on this issue.
https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents

Please let us know if you have any queries.

Regards,
Sathish
http://www.syncfusion.com/Content/images/directtrac/Post-Update.png