Hello,
I'm trying to update the information of a listObject hosted in a Workbook created by an Excel Addin (example Code1) with an commandline application.
I've tried creating an instance of Excel and accessing at all the listObjects but GetVstoObject always returns null objects. I think is a security problem, but I don't know how to resolve it. (example Code2).
I've tried with ServerDocuments but I don't have CachedData, and it's not possible to use at Application Add-in level. Any suggestion?
Thanks in advance.
Code1
using System;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Excel.Extensions;
namespace ExcelAddIn2{
public partial class ThisAddIn{
private void ThisAddIn_Startup(object sender, System.EventArgs e){
DataTable theDataTable = GetDataTable();
Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook.GetVstoObject();
Worksheet worksheet = ((Excel.Worksheet)workbook.Worksheets[1]).GetVstoObject();
Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 1]];
Excel.ListObject interopList = worksheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo,range);
ListObject list = interopList.GetVstoObject();
list.Name = "myListObject";
list.DataSource = theDataTable;
workbook.SaveCopyAs(@"C:\theExcel.xlsx");
}
private DataTable GetDataTable() {
/*Datatable example, I'm using my implementation of a OleDBCommand to get it*/
DataTable table = new DataTable("myTable");
DataColumn columnId = new DataColumn();
columnId.DataType = System.Type.GetType("System.String");
columnId.ColumnName = "id";
DataColumn columnName = new DataColumn();
columnName.DataType = System.Type.GetType("System.String");
columnName.ColumnName = "Name";
table.Columns.Add(columnId);
table.Columns.Add(columnName);
DataRow row;
for (int i = 0; i <= 2; i++){
row = table.NewRow();
row["id"] = i;
row["Name"] = "Name " + i;
table.Rows.Add(row);
}
return table;
}
..etc..
Code2 References: Microsoft.Office.Interop.Excel, Microsoft.Office.Tools.Common.v9.0, Microsoft.Office.Tools.Excel.v9.0, Microsoft.Office.Tools.v9.0, Microsoft.VisualStudio.Tools.Applications.Runtime.v9.0, System.Windows.Forms (It needs it ¿?)
using System;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Excel.Extensions;
using System.Reflection;
namespace ConsoleApplication1{
class Program {
static void Main(string[] args)
{
Excel.Application app = new Excel.ApplicationClass();
Excel.Workbook excelWorkbook = app.Workbooks.Open(@"C:\theExcel.xlsx",
Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Microsoft.Office.Tools.Excel.Worksheet worksheet = ((Excel.Worksheet)excelWorkbook.Worksheets[1]).GetVstoObject();
foreach (ListObject list in worksheet.ListObjects) {
list.DataSource = GetNewDataTable();
}
app.Quit();
}
private static DataTable GetNewDataTable()
{
DataTable table = new DataTable("myTable");
DataColumn columnId = new DataColumn();
columnId.DataType = System.Type.GetType("System.String");
columnId.ColumnName = "id";
DataColumn columnName = new DataColumn();
columnName.DataType = System.Type.GetType("System.String");
columnName.ColumnName = "Name";
table.Columns.Add(columnId);
table.Columns.Add(columnName);
DataRow row;
for (int i = 0; i <= 2; i++)
{
row = table.NewRow();
row["id"] = i;
row["Name"] = "New Name " + i;
table.Rows.Add(row);
}
return table;
}
}
}