MichaelvdV@Atos

Using DataLink and Excel Automation in .NET

Discussion created by MichaelvdV@Atos on May 11, 2009
Latest reply on May 11, 2009 by MichaelvdV@Atos

admins: Not sure wheter this post is in the right forum, if it isn't: please move it.

 

I'm trying to open an Excel Workbook in .NET, change a field containing a datestamp, calculate the entire workbook, and then save the values in a copy. We are using this to generate reports from 'templates' using PI DataLink.

 

Please consider the following code:


Workbook wb;
            try
            {                  
                wb = ExcelApplication.Workbooks.Open(templateInfo.FullName,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value,
                                                                Missing.Value);
                ExcelApplication.AddIns.Add(@"C:\Program Files\PIPC\Excel\pipc32.xll", false);
            }
            catch (Exception ex)
            {
                throw;
            }
            //Worksheet sheet = default(Worksheet);

            //foreach (Worksheet s in wb.Sheets)
            //{
            //    sheet = s;
            //    break;
            //}
           
            ((Microsoft.Office.Interop.Excel._Workbook)wb).Activate();
            wb.Windows[1].Visible = true;
            ExcelApplication.EnableEvents = true;
            ExcelApplication.Names.Item("ReportDate", Missing.Value, Missing.Value).RefersToRange.Value2 = context.OperationDate.ToOADate();
          //  Range reportDateRange = ExcelApplication.get_Range("ReportDate", Missing.Value);
            //Range reportDateRange = sheet.get_Range("ReportDate", Missing.Value);
          //  reportDateRange.Formula = context.OperationDate.ToString();
            string outpath = context.IntervalID == 1
                            ? context.DayReportOutputPath
                            : context.MonthReportOutputPath;

            ExcelApplication.CalculateFull();

            foreach (Worksheet s in wb.Sheets)
            {
                ((Microsoft.Office.Interop.Excel._Worksheet)s).Activate();
                ExcelApplication.Cells.Select();
                ExcelApplication.Cells.Copy(Missing.Value);
                ((Range)ExcelApplication.Selection).PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                ((Range)ExcelApplication.Cells[1, 1]).Select();
              
            }

           // wb.SaveAs(Path.Combine(outpath, context.OperationDate.ToString("yyyy-MM-dd_hh-mm-ss") + templateInfo.Name));
          
            wb.SaveAs(Path.Combine(outpath, context.OperationDate.ToString("yyyy-MM-dd_hh-mm-ss") + templateInfo.Name), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            wb.Close(false, Missing.Value, Missing.Value);

 

The generated Excel report only contains #NAME? fields, instead of the values that should have come from DataLink. (please note: i'm only trying to save the values, not the DataLink calls. If I comment out the lin containing the 'PasteSpecial' it works, but only with the datalink calls.

 

Has anyone ever attempted something like this ? I'm not exactly an Excel Automation specalist :)

 

 

Outcomes