using NLog;
using OfficeOpenXml;    //EPPlus library
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Text.RegularExpressions;
 
//...
private static Logger m_logger = LogManager.GetCurrentClassLogger();
//...
public static string CleanExcelSheets(string strExcelFilePath, string strFileName, object logObject)
{
	string strFileFullPath = string.Format("{0}\\{1}", strExcelFilePath, strFileName);
	string strLogTxtOut = string.Empty;
	string strLogTxt = string.Empty;
	int nrSheet = 0;
	int i = 0;
	string strNewSheetName = string.Empty;
	string strNewFileName = string.Empty;
 
	try
	{
		FileInfo excelFile = new FileInfo(strFileFullPath);
		if (!excelFile.Exists)
		{
			throw new Exception(string.Format("File '{0}' not found!", strFileFullPath));
		}
		strLogTxt = WriteLog(logObject, string.Format("{0} - {1}\r\n", GetCurrentMethod(),
							string.Format("FOUND file '{0}'", strFileName)));
 
		excelFile = new FileInfo(strFileFullPath);
		using (ExcelPackage package = new ExcelPackage(excelFile))
		{
			List<ExcelWorksheet> worksheets = package.Workbook.Worksheets.ToList();
			nrSheet = worksheets.Count;
			strLogTxt += WriteLog(logObject, string.Format("{0} - FOUND {1} sheets\r\n", GetCurrentMethod(), nrSheet));
 
			foreach (ExcelWorksheet worksheet in worksheets)
			{
				i++;
				strNewSheetName = CleanSheetName(worksheet.Name, logObject);
				strLogTxt += WriteLog(logObject, string.Format("Sheet {0}/{1}: '{2}' --> '{3}'\r\n"
						, i, nrSheet, worksheet.Name, strNewSheetName));
				worksheet.Name = strNewSheetName;
			}
			package.Save();
		}
		strLogTxt += WriteLog(logObject, string.Format("{0} - {1}\r\n", GetCurrentMethod(), "UPDATE File sheets"));
 
		strNewFileName = CleanExcelFileName(strFileName, logObject);
		strLogTxt += WriteLog(logObject, string.Format("{0} - NEW fileName '{1}'\r\n", GetCurrentMethod(), strNewFileName));
	}
	catch (Exception ex)
	{
		strLogTxt += WriteLog(logObject, string.Format("{0} - ERROR\r\n\t{1}\r\n", GetCurrentMethod(), ex.Message));
		throw;
	}
	return strLogTxtOut;
}
 
public static string CleanExcelFileName(string strFileName, object logObject)
{
	string strNewFileName = string.Empty;
	string strLogTxt = string.Empty;
	try
	{
		//Rimuove le indicazioni delle settimane di riferimento
		strNewFileName = Regex.Replace(strFileName, @"[wW]\d+", string.Empty);
		//Rimuove tutti i numeri
		strNewFileName = Regex.Replace(strNewFileName, @"\d", string.Empty);
		strNewFileName = strNewFileName.Replace("-", string.Empty).Trim();
		if (strNewFileName.EndsWith("_.xlsx"))
			strNewFileName = strNewFileName.Substring(0, strNewFileName.Length - 6)
								+ ".xlsx";
	}
	catch (Exception ex)
	{
		strLogTxt += WriteLog(logObject, string.Format("{0} - ERROR\r\n\t{1}\r\nOn file '{2}'\r\n"
							, GetCurrentMethod(), ex.Message, strFileName));
		throw;
	}
	return strNewFileName;
}
 
//... il seguente metodo rinomina il file Excel stesso
public static string RenameExcelFile(string strFileFullPath, object logObject)
{
	string strNewFileName = string.Empty;
	string strFilePath = string.Empty;
	string strFileName = string.Empty;
	string strLogTxtOut = string.Empty;
 
	try
	{
		strFilePath = Path.GetDirectoryName(strFileFullPath);
		strFileName = Path.GetFileName(strFileFullPath);
		strNewFileName = ExcelHelper.CleanExcelFileName(strFileName);
		File.Move(strFilePath + "\\" + strFileName, strFilePath + "\\" + strNewFileName);
		strLogTxtOut = WriteLog(logObject, string.Format("{0} - RENAMED file '{1}' to '{2}'\r\n"
						, GetCurrentMethod(), strFileName, strNewFileName));
	}
	catch (Exception ex)
	{
		strLogTxtOut += WriteLog(logObject, string.Format("{0} - ERROR\r\n\t{1}\r\nOn file '{2}'\r\n"
						, GetCurrentMethod(), ex.Message, strFileName));
		throw;
	}
	return strLogTxtOut;
}
 
//... extra...
public static string WriteLog(object logObject, string strLogTxt)
{
	string strOut = string.Empty;
	try
	{
		if (logObject != null)
		{
			((System.Windows.Forms.TextBox)logObject).Text += strLogTxt;
		}
		else
		{
			if (strLogTxt.IndexOf("ERROR") >= 0)
			{
				m_logger.Error(strLogTxt);
			}
			else if(strLogTxt.IndexOf("WARNING") >= 0)
			{
				m_logger.Warn(strLogTxt);
			}
			else
			{
				m_logger.Info(strLogTxt);
			}
			Console.WriteLine(strLogTxt);
		}
		strOut = strLogTxt;
	}
	catch (Exception ex)
	{
		strOut = string.Format("WriteLog - ERROR: {0}", ex.Message);
		m_logger.Info(strOut);
	}
	return strOut;
}

//...
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
//...
 
        /// <summary>
        /// USA libreria NPOI. Crea un DataTable del contenuto del file Excel indicato. Lavora sul primo foglio di lavoro!
        /// </summary>
        /// <param name="fileExcelFullPath">Percorso sul FileSystem del file Excel</param>
        /// <param name="skipFirstLine">Se saltare la prima riga del foglio di lavoro</param>
        /// <param name="text4AllCells">Se considerare testo tutte le colonne del file</param>
        /// <returns></returns>
        public static DataTable GetDataTableFromExcel_NPOI(string fileExcelFullPath, bool text4AllCells)
        {
            return GetDataTableFromExcel_NPOI(null, fileExcelFullPath, 0, text4AllCells, 0, 0);
        }
 
        public static DataTable GetDataTableFromExcel_NPOI(string fileExcelFullPath, bool text4AllCells, int skipFirstNRow)
        {
            return GetDataTableFromExcel_NPOI(null, fileExcelFullPath, skipFirstNRow, text4AllCells, 0, 0);
        }
 
        public static DataTable GetDataTableFromExcel_NPOI(string fileExcelFullPath, bool text4AllCells, int skipFirstNRow, int maxRecord)
        {
            return GetDataTableFromExcel_NPOI(null, fileExcelFullPath, skipFirstNRow, text4AllCells, maxRecord, 0);
        }
 
        /// <summary>
        /// USA libreria NPOI. 
        /// </summary>
        /// <param name="workSheetName"></param>
        /// <param name="fileExcelFullPath"></param>
        /// <param name="skipFirstNRow"></param>
        /// <param name="text4AllCells"></param>
        /// <param name="maxRecord"></param>
        /// <param name="headerAtRow"></param>
        /// <returns></returns>
        public static DataTable GetDataTableFromExcel_NPOI(string workSheetName, string fileExcelFullPath, int skipFirstNRow,
                                                            bool text4AllCells, int maxRecord, int headerAtRow)
        {
            if (!File.Exists(fileExcelFullPath))
            {
                throw new Exception(String.Format("Non trovato il file Excel da elaborare '{0}'.", fileExcelFullPath));
            }
 
            HSSFWorkbook hssfwb;
            DataTable dt = new DataTable();
            int currRowDataSheet = 0;
            int j = 0;
 
            var prevCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            try
            {
                //Apertura del File Excel
                using (FileStream fileStream = new FileStream(fileExcelFullPath, FileMode.Open, FileAccess.Read))
                {
                    hssfwb = new HSSFWorkbook(fileStream);
                }
                #region Si carica la scheda indicata in input
                ISheet sheet = null;
                if (string.IsNullOrEmpty(workSheetName))
                {
                    //Se null dovrebbe prendere la prima scheda
                    sheet = hssfwb.GetSheetAt(0); 
                }
                else
                {
                    sheet = hssfwb.GetSheet(workSheetName); 
                }                
                if (sheet == null)
                {
                    throw new Exception(string.Format("Non trofato il foglio di nome o posizione: {0}.", workSheetName ?? ""));
                }
                #endregion
 
                #region DEFINIZIONE nome colonne DataTable IN BASE alla indicazione o meno della INTESTAZIONE
                try
                {
                    //Costruzione dell'intestazione del DataTable ovvero dei nomi dei Campi
                    IRow row = (HSSFRow)sheet.GetRow((headerAtRow - 1) + skipFirstNRow); // In base Zero
                    if (headerAtRow >= 0)    //Caso in cui si è esplicitamente indicata la riga contenente l'intestazione del DataTable
                    {
                        for (int i = 0; i < row.LastCellNum; i++)
                        {
                            dt.Columns.Add(row.GetCell(i).StringCellValue);
                        }
                    }
                    else                    //RIDENOMINAZINE standard delle colonne DataTable
                    {
                        for (j = 0; j < row.LastCellNum; j++)
                        {
                            dt.Columns.Add("C" + j);
                        }
                    }
                }
                catch (Exception ex)
                {       
                    throw new Exception(string.Format("Errore definendo l'intestazione dal file Excel, verificare la riga {0} scelta allo scopo.\r\n'\r\n{1}",
                        headerAtRow, ex.Message));
                }
                #endregion
 
                IRow iRow;
                string cellContent = string.Empty;
 
                //Si inizia dal rigo: skipFirstNRow + Eventuale intestazione
                for (currRowDataSheet = (skipFirstNRow + headerAtRow); currRowDataSheet <= sheet.LastRowNum; currRowDataSheet++)
                {
                    iRow = (HSSFRow)sheet.GetRow(currRowDataSheet);
                    DataRow dr = dt.NewRow();
                    j = 0;
                    foreach (ICell ic in iRow)
                    {
                        cellContent = ic.ToString();
 
                        switch (ic.CellType)
                        {
                            case CellType.NUMERIC:
                                cellContent = ic.ToString();
                                if (cellContent.IndexOf(",") > 0
                                    && cellContent.IndexOf(".") >= 0)
                                {
                                    cellContent = cellContent.Replace(",", "");
                                }
                                break;
                            default:
                                break;
                        }
 
                        dr[j] = cellContent;
                        j++;
                    }
 
                    dt.Rows.Add(dr);
                }
                hssfwb = null;
                sheet = null;
            }
            catch (Exception ex)
            {
                string strError = string.Format("MwC.Libraries.IO.FileManager._GetDataTableFromExcel()\r\nErrore dopo aver caricato {0} righe:\r\n{1}",
                                                currRowDataSheet, ex.Message);
                throw new Exception(strError);
            }
            finally
            {
                Thread.CurrentThread.CurrentCulture = prevCulture;
            }
            return dt;
        }