C# Script to get Excel Range value
Happy new year, everyone!
below is the code in Visual studio
Rng = (Excel.Range)sht.Cells[2, Column];
Excel.Range Rng2 = Rng.get_Resize(maxrow - 1, 1);
//content=Rng2.Value2;
for (int i = 0; i < maxrow - 1; i++)
{
content[i,0] = Rng2[i + 1].Value();
}
It runs well in Microsoft Visual Studio,
But once i copy it to Pega robotics studio C# script component,
It shows as attached screen shot, kindly help.
The code is same with Microsoft visual studio.
Accepted Solution
Flextronics Technology Co Ltd
CN
Has been resolved.
content=(string[,])Rng2.Value2
there must be a conversion in Pega, it is not necessary in VS.
Flextronics Technology Co Ltd
CN
Rng = (Excel.Range)sht.Cells[2, Column];
Excel.Range Rng2 = Rng.get_Resize(maxrow - 1, 1);
//content=Rng2.Value2;
for (int i = 0; i < maxrow - 1; i++)
{
content[i,0] = Rng2[i + 1].Value;//update here
}
Sorry, update the code as above,
Did anyone encounter this issue? The code works in Microsoft Visual Studio, but can't work in Pega,
The reference is the same.
Flextronics Technology Co Ltd
CN
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel=Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
namespace Dynamic.Script_8D54FC075EDC454
{
// Script generated by Pega Robotics Studio 8.0.1063.0
// Please use caution when modifying class name, namespace or attributes
[OpenSpan.TypeManagement.DynamicTypeAttribute()]
[OpenSpan.Design.ComponentIdentityAttribute("Script-8D54FC075EDC454")]
public sealed class Script
{
//Use API to get process id
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
Excel.Application ExcelApp;
object running=null;
public string[,] ColCopy(string Excelfile,int SheetIndex, int Column)
{
try
{
running = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
ExcelApp = (Excel.Application)running;
}
catch
{
ExcelApp = new Excel.Application();
ExcelApp.Visible = true;
}
ExcelApp.ScreenUpdating = false;
int count=0;
Excel.Workbook
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel=Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
namespace Dynamic.Script_8D54FC075EDC454
{
// Script generated by Pega Robotics Studio 8.0.1063.0
// Please use caution when modifying class name, namespace or attributes
[OpenSpan.TypeManagement.DynamicTypeAttribute()]
[OpenSpan.Design.ComponentIdentityAttribute("Script-8D54FC075EDC454")]
public sealed class Script
{
//Use API to get process id
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
Excel.Application ExcelApp;
object running=null;
public string[,] ColCopy(string Excelfile,int SheetIndex, int Column)
{
try
{
running = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
ExcelApp = (Excel.Application)running;
}
catch
{
ExcelApp = new Excel.Application();
ExcelApp.Visible = true;
}
ExcelApp.ScreenUpdating = false;
int count=0;
Excel.Workbook wbk = null;
foreach (Excel.Workbook wb in ExcelApp.Workbooks)
{
count++;
if(wb.FullName==@Excelfile)
{
wbk = ExcelApp.Workbooks[count];
}
}
if (wbk==null)
{
wbk = ExcelApp.Workbooks.Open(@Excelfile);
}
Excel.Worksheet sht = (Excel.Worksheet)wbk.Sheets[SheetIndex];
Excel.Range Rng = sht.UsedRange;
int maxrow = Rng.Rows.Count;
string[,] content = new string[maxrow - 1,1];
if (maxrow > 1)
{
//Deliver the value to an array
Rng = (Excel.Range)sht.Cells[2, Column];
Excel.Range Rng2 = Rng.get_Resize(maxrow - 1, 1);
//content=Rng2.Value2;
for (int i = 0; i < maxrow - 1; i++)
{
content[i,0] = Rng2[i + 1].Value;
}
}
ExcelApp.ScreenUpdating = true;
if (running==null)
{
//if there's no running excel before this step, then kill the process.
ExcelApp.Quit();
IntPtr ExHandle = new IntPtr(ExcelApp.Hwnd);
int pid = 0;
GetWindowThreadProcessId(ExHandle, out pid);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(pid);
p.Kill();//kill excel process
}
else
{
wbk.Close();
}
return content;
}
public string fortest(string[,] data)
{
string ss=data[0,0];
return ss;
}
}
}
Flextronics Technology Co Ltd
CN
Hi Jeff, need your help on this.
the code works well in microsoft visual studio.
Hi Jeff, need your help on this.
the code works well in microsoft visual studio.
content[i,0] = Rng2[i + 1].Value;
Pegasystems Inc.
US
The only thing I can think of that may explain this is that you may have the wrong Office dlls loaded. In the Studio install folder there a series of folders that contain the Office interop dlls, one for each version. Copy the contents of the Office version installed on your machine to the root install folder. The entry points for each version of Office are different and require the correct interop dlls to function. My assumption is that in Visual Studio you supplied the correct version, but since you can't directly choose this in Pega Robotics the dlls may be different.
Flextronics Technology Co Ltd
CN
In Pega, I add the office2016 dlls under Pega installment folder. In Microsoft Visual Studio, I use the PIA, that's the difference. but seems i can't add the Microsoft PIA to Pega reference. It shows the reference was not under the namespace of pega. How to replace the Pega dlls with Microsoft PIA?
Accepted Solution
Flextronics Technology Co Ltd
CN
Has been resolved.
content=(string[,])Rng2.Value2
there must be a conversion in Pega, it is not necessary in VS.