CSharp:Uso libreria NPOI
From Aino Wiki
Revision as of 10:11, 17 March 2020 by Giuseppe AINO (Talk | contribs)
Libreria
Libreria OpenSource, sito Ufficiale npoi.codeplex
Esempi:
- programering.com Semplici esempio Excel to DataTable e viceversa
- c-sharpcorner per inserire immagini, Link etc
NOTE: è gratuita sino alla versione 4.*. E' installabile direttamente in una sokution usando NuGet.
Rinominare i fogli
Segue una parte di codice di una soluzione reale.
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; }
Trasformazione da Excel a DataTable
L'NPOI è utile per evitare problemi di compatibilità tra le versioni a 32 bit/ 64 bit delle librerie Microsoft per la lettura di files Excel.
sito ufficiale
//... 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; }
Mappa e Link
C# | Accesso ai files | Accesso ai files, Excel