3 Replies Latest reply on May 11, 2009 1:24 PM by MichaelvdV@Atos

    Using DataLink and Excel Automation in .NET

    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 :)