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: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Hard to Explain...

Thread ID:

Created:

Updated:

Platform:

Replies:

130274 May 3,2017 04:37 PM UTC May 5,2017 03:34 PM UTC WinForms 3
loading
Tags: XlsIO
Seth
Asked On May 3, 2017 04:37 PM UTC

I have a fairly complex Excel spreadsheet that contains macros and multiple tabs (it's an .xlsm file).  I'm programmatically inserting data into two existing tables on two separate tabs which is working perfectly fine.  The problem I'm having is that, when I open the updated file (i.e. after the data is inserted), I'm seeing data on two OTHER tabs that I absolutely, positively am not modifying in code.  These two tabs also have tables on them, but they're intentionally empty (they have one blank row).  If you look at the attached screenshot, I highlighted the cells so you could see their contents (the text is set to white on purpose).  Both tables have the exact same values so it seems that the re-saving of the spreadsheet in XLSIO is doing something strange, however unlikely that seems.

The macros I mentioned above are run from the click event of a button on the spreadsheet that reads the data that's inserted into the "original" two tables, does some fancy calculations in code and then populates the latter two empty tables.  I'm 100% certain that there's no code running before the button is clicked so the "phantom" data is not originating from that.  As a matter of fact, I left macros disabled to ensure they weren't running.  Any thoughts on what could be going on?


Attachment: 20170503_105926_72182cdd.zip

Sridhar Sukumar [Syncfusion]
Replied On May 4, 2017 01:00 PM UTC

Hi Seth, 

 

Thank you for contacting Syncfusion support. 

 

We are unable to reproduce the issue from our side. Kindly share us the issue reproducing input files along with the code snippet which will be helpful for us to give you a prompt solution at the earliest. 

 

Regards, 

Sridhar S. 

Seth
Replied On May 4, 2017 01:44 PM UTC

Thanks for your reply.  Attached are the "template" file (i.e. the one that's filled in with data using XLSIO) and the resulting file after the data is filled in.  Here's my C# code for populating the data in case I'm doing something unorthodox:

        public string Export(Report report, int? AsLoginID)
        {
            ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType.FullName);
            string path = Path.Combine(ConfigurationManager.AppSettings["SpreadsheetTemplatePath"], report.SourceTemplate);
            string connString = report.GetConnectionString();

            ExcelEngine excelEngine = new ExcelEngine();
            IApplication xl = excelEngine.Excel;

            IWorkbook wb = xl.Workbooks.Open(path, ExcelOpenType.Automatic);

            var worksheets = Data.GetReportWorksheets(report.ReportID);
            foreach (var worksheet in worksheets)
            {
                var worksheetParams = Data.GetWorksheetParameters(worksheet.WorksheetID);

                // always add a @CurrentUserLoginID parameter for reports that need to know who the report was run as
                worksheetParams.Add(new WorksheetParameter { ParameterName = "@CurrentUserLoginID", ParameterType = "int", ParameterValue = AsLoginID.HasValue ? AsLoginID.ToString() : string.Empty });

                DataTable dt = Data.ReturnDataTable(connString, string.Format("{0} {1}", report.CommonSQL, worksheet.SQL), worksheetParams.ToList());

                Syncfusion.XlsIO.IWorksheet ws;

                ws = wb.Worksheets[worksheet.WorksheetName];
                if (ws == null)
                {
                    ws = wb.Worksheets.Create(worksheet.WorksheetName);
                    ws.ImportDataTable(dt, true, 1, 1);
                }
                else
                {
                    var includeHeader = (worksheet.IncludeHeader.HasValue ? worksheet.IncludeHeader.Value : false);
                    ws.ImportDataTable(dt, includeHeader, includeHeader ? 1 : 2, 1);
                }

                ws.Rows[0].FreezePanes();

                if (worksheet.FormatForPrinting == true)
                {
                    IListObject table = ws.ListObjects.Create(string.Format("Table{0}", wb.Worksheets[worksheet.WorksheetName].Index + 1), ws.UsedRange);
                    table.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9;
               
                    if (ws.UsedRange.LastColumn < 15 || ws.UsedRange.LastRow < 2500)
                        ws.UsedRange.AutofitColumns();
                }
            }

            foreach (IWorksheet ws in wb.Worksheets)
            {
                IPivotTables pTables = ws.PivotTables;
                for (int i = 0; i < pTables.Count; i++)
                {
                    ws.PivotTables[i].Options.ShowDrillIndicators = false;
                }                   
            }
           
            string savedReport = Path.Combine(ConfigurationManager.AppSettings["SavedSpreadsheetPath"], report.GetFileName());
            try
            {
                wb.Worksheets[0].Activate();
                wb.SaveAs(savedReport);
                wb.Close();

                log.Info(string.Format("Report exported successfully - {0} using {1}", savedReport, report.GetServerAndDatabase()));
            }

            catch (Exception ex)
            {
                log.Error(string.Format("Report export failed - {0} using {1}" + Environment.NewLine + "Error:{2}", savedReport, report.GetServerAndDatabase(), ex.Message));
                savedReport = string.Empty;
            }

            finally
            {           
                excelEngine.Dispose();
            }
           
            return savedReport;
        }

Attachment: Phantom_Data_fd81cf06.7z

Sridhar Sukumar [Syncfusion]
Replied On May 5, 2017 03:34 PM UTC

Hi Seth, 

  

We were able to reproduce the issue. A support incident to track the status of this defect has been created under your account. Please log on to our support website to check for further updates 

  

https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents  

  

Please let me know if you have any questions. 

  

Regards, 

Sridhar S. 

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

;