dustin

   1:  /* Microsoft SQL Server Integration Services Script Component

   2:  *  Write scripts using Microsoft Visual C# 2008.

   3:  *  ScriptMain is the entry point class of the script.*/

   4:   

   5:  using System;

   6:  using System.Data;

   7:  using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

   8:  using Microsoft.SqlServer.Dts.Runtime.Wrapper;

   9:  using SC_5b598c57224f45b2b97f422c8fa7d64c.csproj.ChilinkExcelService;

  10:   

  11:   

  12:  [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

  13:  public class ScriptMain : UserComponent

  14:  {

  15:      private SC_5b598c57224f45b2b97f422c8fa7d64c.csproj.ChilinkExcelService.ExcelService Es = null;

  16:      public DataTable dt;

  17:      RangeCoordinates rangeCoordinates = new RangeCoordinates();

  18:      string sheetName = "Sheet1";

  19:   

  20:      public override void PreExecute()

  21:      {

  22:          base.PreExecute();

  23:          /*

  24:            Add your code here for preprocessing or remove if not needed

  25:          */

  26:          Es = new ExcelService();

  27:          //Excel excel = new ChilinkExcelService.ExcelService();

  28:          Es.Credentials = new System.Net.NetworkCredential("帳號", "密碼", "網域");

  29:   

  30:          Status[] stati;

  31:          String sessionID = Es.OpenWorkbook("檔案路徑", "en-US", "en-US", out stati);

  32:   

  33:          rangeCoordinates.Column = 0;        //從0開始算，從第一欄開始抓

  34:          rangeCoordinates.Row = 1;           //從0開始算，指從第二列開始算(第一列是標題)

  35:          rangeCoordinates.Height = 2;

  36:          rangeCoordinates.Width = 8;

  37:   

  38:          dt = new DataTable();

  39:          dt.Columns.Add("Sorting_Date", System.Type.GetType("System.DateTime"));

  40:          dt.Columns.Add("Model_name");

  41:          dt.Columns.Add("Item_No");

  42:          dt.Columns.Add("People_No");

  43:          dt.Columns.Add("Hours");

  44:          dt.Columns.Add("Human_Cost");

  45:          dt.Columns.Add("Meal_Cost");

  46:          dt.Columns.Add("Traffic_Fee");

  47:   

  48:          object[] rangeResult = Es.GetRange(sessionID, sheetName, rangeCoordinates, true, out stati);

  49:   

  50:          for (int i = 0; i < rangeResult.Length; i++)

  51:          {

  52:              DataRow row = dt.NewRow();

  53:              row["Sorting_Date"] = Convert.ToDateTime(((object[])rangeResult[i])[0]);

  54:              row["Model_name"] = ((object[])rangeResult[i])[1];

  55:              row["Item_No"] = ((object[])rangeResult[i])[2];

  56:              row["People_No"] = ((object[])rangeResult[i])[3];

  57:              row["Hours"] = ((object[])rangeResult[i])[4];

  58:              row["Human_Cost"] = ((object[])rangeResult[i])[5];

  59:              row["Meal_Cost"] = ((object[])rangeResult[i])[6];

  60:              row["Traffic_Fee"] = ((object[])rangeResult[i])[7];

  61:              dt.Rows.Add(row);

  62:          }

  63:   

  64:   

  65:         

  66:      }

  67:   

  68:      public override void PostExecute()

  69:      {

  70:          base.PostExecute();

  71:          /*

  72:            Add your code here for postprocessing or remove if not needed

  73:            You can set read/write variables here, for example:

  74:            Variables.MyIntVar = 100

  75:          */

  76:      }

  77:   

  78:      public override void CreateNewOutputRows()

  79:      {

  80:          /*

  81:            Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

  82:            For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".

  83:          */

  84:          foreach (DataRow row in dt.Rows)

  85:          {

  86:              OutBuffer.AddRow();

  87:              OutBuffer.SortingDate =Convert.ToDateTime( row["Sorting_Date"]);

  88:              OutBuffer.Modelname = row["Model_name"].ToString();

  89:              OutBuffer.ItemNo = row["Item_No"].ToString();

  90:              OutBuffer.PeopleNo =Convert.ToInt32( row["People_No"]);

  91:              OutBuffer.Hours = Convert.ToInt32(row["Hours"]);

  92:              OutBuffer.HumanCost = Convert.ToInt32(row["Human_Cost"]);

  93:              OutBuffer.MealCost = Convert.ToInt32(row["Meal_Cost"]);

  94:              OutBuffer.TrafficFee = Convert.ToInt32(row["Traffic_Fee"]);            

  95:          }

  96:   

  97:          OutBuffer.EndOfRowset();

  98:   

  99:      }

 100:   

 101:  }