using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Collections;
//TestEnviroment:VS2013Update4 Excel2007
//Read by COM Object
namespace SmartStore.LocalModel
{
  public class ExcelTable
  {
    private string _path;
    public ExcelTable()
    {
      _path = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
      _path += \"条码对照表.xls\";
    }
    public void ReadEPC2BarCode(out ArrayList arrayPI)
    {
      DataTable dt = ReadSheet(2);
      arrayPI = new ArrayList();
      foreach (DataRow dr in dt.Rows)
      {
        EPC2BarCode eb = new EPC2BarCode();
        eb.EPC = (string)dr[\"epcID\"];
        eb.Barcode = (string)dr[\"条形码\"];
        eb.EPC = eb.EPC.Trim();
        eb.Barcode = eb.Barcode.Trim();
        if (eb.EPC == null || eb.EPC.Length <= 0)
          break;
        arrayPI.Add(eb);
      }
    }
    public void ReadProductInfo(out ArrayList arrayPI)
    {
      DataTable dt = ReadSheet(1);
      arrayPI = new ArrayList();
      foreach (DataRow dr in dt.Rows)
      {
        ProductInfo pi = new ProductInfo();
        pi.Name = (string)dr[\"商品名称\"];
        pi.SN = (string)dr[\"商品编号\"];
        pi.BarCode = (string)dr[\"商品条码\"];
        pi.Brand = (string)dr[\"品牌\"];
        pi.Color = (string)dr[\"颜色\"];
        pi.Size = (string)dr[\"尺码\"];
        pi.Name = pi.Name.Trim();
        pi.SN = pi.SN.Trim();
        pi.BarCode = pi.BarCode.Trim();
        pi.Brand = pi.Brand.Trim();
        pi.Color = pi.Color.Trim();
        pi.Size = pi.Size.Trim();
        if (pi.Name == null || pi.Name.Length <= 0)
          break;
        arrayPI.Add(pi);
      }
    }
    private DataTable ReadSheet(int indexSheet)
    {
      Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
      Microsoft.Office.Interop.Excel.Sheets sheets;
      Microsoft.Office.Interop.Excel.Workbook workbook = null;
      object oMissiong = System.Reflection.Missing.Value;
      System.Data.DataTable dt = new System.Data.DataTable();
      try
      {
        workbook = app.Workbooks.Open(_path, oMissiong, oMissiong, oMissiong, oMissiong,
          oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
        //将数据读入到DataTable中——Start
        sheets = workbook.Worksheets;
        //输入1, 读取第一张表
        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(indexSheet);
        if (worksheet == null)
          return null;
        string cellContent;
        int iRowCount = worksheet.UsedRange.Rows.Count;
        int iColCount = worksheet.UsedRange.Columns.Count;
        Microsoft.Office.Interop.Excel.Range range;
        //负责列头Start
        DataColumn dc;
        int ColumnID = 1;
        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
        while (range.Text.ToString().Trim() != \"\")
        {
          dc = new DataColumn();
          dc.DataType = System.Type.GetType(\"System.String\");
          dc.ColumnName = range.Text.ToString().Trim();
          dt.Columns.Add(dc);
          range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ++ColumnID];
        }
        //End
        for (int iRow = 2; iRow <= iRowCount; iRow++)
        {
          DataRow dr = dt.NewRow();
          for (int iCol = 1; iCol <= iColCount; iCol++)
          {
            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
            cellContent = (range.Value2 == null) ? \"\" : range.Text.ToString();
            //if (iRow == 1)
            //{
            //  dt.Columns.Add(cellContent);
            //}
            //else
            //{
            dr[iCol - 1] = cellContent;
            //}
          }
          //if (iRow != 1)
          dt.Rows.Add(dr);
        }
        //将数据读入到DataTable中——End
        return dt;
      }
      catch
      {
        return null;
      }
      finally
      {
        workbook.Close(false, oMissiong, oMissiong);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        workbook = null;
        app.Workbooks.Close();
        app.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
        app = null;
        GC.Collect();
        GC.WaitForPendingFinalizers();
      }
    }
  }
}