using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using Excel;
using System.Windows.Forms;
using System.Drawing;

namespace TestExcel
{
    public partial class _ReadExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            lblResult.Text = string.Empty;
            LoadExcel();
        }

        private void LoadExcel()
        {
            string folderPath = Server.MapPath("~/App_data/");
            string fileName = folderPath + "MenuTemplate.xls";

            Excel.Application eApp = null;
            Excel.Workbook eBook = null;
            Excel.Worksheet eSheet = null;
            int startRow = 2;
            object Nothing = Type.Missing;

            eApp = new Excel.Application();
            eBook = eApp.Workbooks.Open(fileName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);

            for (int sheetIndex = 1; sheetIndex <= eBook.Worksheets.Count; sheetIndex++)
            {
                eSheet = (Excel.Worksheet)eBook.Worksheets[sheetIndex];
                if (eSheet.UsedRange.Rows.Count <= 1)
                    continue;

                string sheetName = eSheet.Name;
                lblResult.Text += System.Environment.NewLine + System.Environment.NewLine + "Sheet Name:" + sheetName;
                for (int rowIndex = startRow; rowIndex <= eSheet.UsedRange.Rows.Count; rowIndex++)
                {
                    string menuName = ((Excel.Range)eSheet.Cells[rowIndex, 'A' - 'A' + rowIndex]).Text.ToString();
                    if (menuName == "")
                        continue;
                    string description = ((Excel.Range)eSheet.Cells[rowIndex, 'B' - 'A' + rowIndex]).Text.ToString();

                    lblResult.Text += System.Environment.NewLine + "MenuName Description";
                    lblResult.Text += System.Environment.NewLine + menuName + " " + description;

                    ////Can only do it in windows application. Need more research
                    ////Read Image and save to disk
                    //if (eSheet.Shapes.Count > rowIndex - startRow)
                    //{
                    //    Excel.Shape s = eSheet.Shapes.Item(rowIndex - startRow + 1) as Excel.Shape;
                    //    s.CopyPicture(Appearance.Button, Excel.XlCopyPictureFormat.xlBitmap);　//Coty to memory
                    //    IDataObject iData = Clipboard.GetDataObject();
                    //    if (iData.GetDataPresent(DataFormats.Bitmap))
                    //    {
                    //        System.Drawing.Bitmap bit = (Bitmap)iData.GetData(DataFormats.Bitmap);
                    //        bit.Save(folderPath + rowIndex.ToString() + ".jpg");
                    //        bit.Dispose();
                    //    }
                    //}
                }
            }

            eApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(eSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(eBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(eApp);
            GC.Collect();
        }
    }
}