We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Adding existing spreadsheet replces functions with Print_Area

Thread ID:

Created:

Updated:

Platform:

Replies:

95465 Jul 9,2010 06:32 PM UTC Jul 16,2010 08:29 AM UTC ASP.NET Web Forms (Classic) 4
loading
Tags: XlsIO
Mark Oudesluys
Asked On July 9, 2010 06:32 PM UTC

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
Replied On July 9, 2010 06:43 PM UTC

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 [Syncfusion]
Replied On July 12, 2010 11:53 AM UTC

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
Replied On July 12, 2010 03:40 PM UTC

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 [Syncfusion]
Replied On July 16, 2010 08:29 AM UTC

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

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;