We are getting the excel file and our requirement is like we need to read the value of the particular cell value. This cell is fixed from where we need to read this value.
In this deme
we will see how to read this value using Script task.
Below is the example file.
In this file, we need to read the value of Cell H4 . In this the cell we are getting the sum of Sales Amount column.
Taking script task
We need to write the below code in the script task.
|
public void Main() { // TODO: Add your code here try { string FolderPath =
Dts.Variables["User::Source_Dir"].Value.ToString(); string File_Name = Dts.Variables["User::File_Name"].Value.ToString(); //Declare and initilize
variables string filePath = ""; filePath = FolderPath + "\\" + File_Name; string tabName = "Sheet1$"; String strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + filePath + ";Extended
Properties=\"Excel 12.0;HDR=NO;IMEX=1\";"; OleDbConnection cn = new OleDbConnection(strCn); string strSQL = "Select * From
[" + tabName + "H4:H4]"; OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, cn); DataSet ds = new DataSet(); objAdapter.Fill(ds, "dSheet1"); DataTable dt = ds.Tables["dSheet1"]; foreach (DataRow row in dt.Rows) { MessageBox.Show(row[0].ToString()); } cn.Close(); Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception ex) { using (StreamWriter sw = File.CreateText(Dts.Variables["User:: Source_Dir"].Value.ToString() + "\\" + "ErrorLog.log")) {
sw.WriteLine(ex.Message.ToString()); Dts.TaskResult = (int)ScriptResults.Failure; } } } |
Now we are
ready to run this script.
Get the
expected result.
