using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Excel.Extensions;

namespace ExcelLib
{
    public class ExcelHelper
    {
        public ExcelHelper(Microsoft.Office.Interop.Excel.Application app)
        {
            application = app;
        }

        Microsoft.Office.Interop.Excel.Application application = null;

        public void AddSheet(string sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = 
                (Microsoft.Office.Interop.Excel.Worksheet)application.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            ws.Name =  GetSheetNameWithNumber(sheetName);
        }

        public string GetSheetNameWithNumber(string sheetName)
        {
            int highestNameNumber = 0;
            Microsoft.Office.Interop.Excel.Worksheet sheet = null;
            Microsoft.Office.Tools.Excel.Worksheet vstoWorksheet = null;

            Microsoft.Office.Tools.Excel.Workbook workbook = 
                application.ActiveWorkbook.GetVstoObject();

            // 1 indexed 
            for (int i = 1; i <= workbook.Worksheets.Count; i++)
            {
                sheet = workbook.Worksheets[i] as Microsoft.Office.Interop.Excel.Worksheet;

                vstoWorksheet = sheet.GetVstoObject();

                if (vstoWorksheet.Name.Contains(sheetName))
                {
                    string[] nameParts = sheet.Name.Split('_');
                    if (nameParts.Length < 2)
                    {
                        // Since there are not 2 parts, there is no value - we do nothing
                    }
                    else
                    {
                        int nameNumber = Convert.ToInt32(nameParts[1]);
                        if (nameNumber > highestNameNumber)
                        {
                            // set the highestNameNumber to the new highest
                            highestNameNumber = nameNumber;
                        }
                    }

                }

                System.Diagnostics.Debug.WriteLine("Sheet : " + vstoWorksheet.Name);
            }

            string nameWithNumber = sheetName + "_" + Convert.ToString((highestNameNumber + 1));
            return nameWithNumber;
        }

        public void SetCell(string column, string row, string value)
        {
            string columnRow = column + row;

            Range r = application.get_Range(columnRow, Type.Missing);
            r.Value2 = value;
        }
    }
}
