vendredi 17 avril 2015

ETL script - 'object' does not contain a definition of 'Value' in Microsoft.Office.Interop

I have a .net 4.5 project getting data from an Excel file and saving some of it to files. It runs perfectly.


However, I decided to make an ETL process of running this script and then uploading it to a database. In the script task of the ETL process I simply paste the same code to the window, add the same references etc and get a bug: 'object' does not contain a definition of 'Value'.


Here's the code:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;

namespace etl.csproj
{
public void Main()
{
process();
Dts.TaskResult = (int)ScriptResults.Success;
}

static void process()
{
string mySheet = @"C:\\pathToMyExcelFile";
System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\my file.txt");
Excel.Application excelApp = new Excel.Application();
Excel.Workbook xlWorkBook = excelApp.Workbooks.Open(mySheet, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Sheets sheets = xlWorkBook.Worksheets;
Excel.Worksheet worksheet;

for (int i = 1; i < sheets.Count; ++i)
{
worksheet = (Excel.Worksheet)sheets.get_Item(i);
for (int j = 5; j <= 9; ++j)
{
for (int k = 68; k <= 72; k++)
{

if (worksheet.Cells[k, j].Value2 != null) //HERE IS THE ERROR
{
file.WriteLine(worksheet.Name + "|" + (string)(worksheet.Cells[k, 2] as Excel.Range).Value /*ERROR HERE*/ + "|" + (j - 4).ToString() + "|" + worksheet.Cells[k, j].Text/*ERROR HERE*/);
}
}


}

}
file.Close();
}

}
}


The only change in those two projects' metadata is that the latter is built in .NET 3.5, not 4.5 (I cannot change the version). I would be grateful for any help!


Aucun commentaire:

Enregistrer un commentaire