Latest revision as of 15:51, 26 November 2020

Introduzione

EP Plus è una libreria che ci consente di elaborare fogli Excel è un'alternativa ad altri strumenti anche Microsoft come le librerie OleDB.
Sito dove scaricare la libreria: CodePlex, GitHub, Sito ufficiale
Si può installare anche con NuGet
La libreria consta di 2 files, una DLL ed un XML con l'help dei metodi:

EPPlus.dll
EPPlus.XML

Guide, esempi, tutorial

• Formattazione dei dati itenium.be
• Formattazione dei dati riptutorial.com

Esempi

Presi dagli esempi ufficiali allegati alla libreria.

Scrittura di un file formattato

Dopo aver aggiunto la DLL "EPPlus.dll" alle references del progetto (sotto l'anteprima del foglio Excel):

Esmpio dalla guida

using System;
using System.Drawing;      // Per i colori
using System.IO;
using OfficeOpenXml;       // Riferimento principale della libreria
using OfficeOpenXml.Style; // Per lo stile delle celle

/// <summary>
/// Sample 1 - simply creates a new workbook from scratch.
/// The workbook contains one worksheet with a simple invertory list
/// </summary>
public static string RunSample1(DirectoryInfo outputDir)
{
	FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
	if (newFile.Exists)
	{
		newFile.Delete();  // ensures we create a new workbook
		newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
	}
	using (ExcelPackage package = new ExcelPackage(newFile))
	{
		// add a new worksheet to the empty workbook
		ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
		//Add the headers
		worksheet.Cells[1, 1].Value = "ID";
		worksheet.Cells[1, 2].Value = "Product";
		worksheet.Cells[1, 3].Value = "Quantity";
		worksheet.Cells[1, 4].Value = "Price";
		worksheet.Cells[1, 5].Value = "Value";

		//Add some items...
		worksheet.Cells["A2"].Value = 12001;
		worksheet.Cells["B2"].Value = "Nails";
		worksheet.Cells["C2"].Value = 37;
		worksheet.Cells["D2"].Value = 3.99;

		worksheet.Cells["A3"].Value = 12002;
		worksheet.Cells["B3"].Value = "Hammer";
		worksheet.Cells["C3"].Value = 5;
		worksheet.Cells["D3"].Value = 12.10;

		worksheet.Cells["A4"].Value = 12003;
		worksheet.Cells["B4"].Value = "Saw";
		worksheet.Cells["C4"].Value = 12;
		worksheet.Cells["D4"].Value = 15.37;

		//Add a formula for the value-column
		worksheet.Cells["E2:E4"].Formula = "C2*D2";

		//Ok now format the values;
		using (var range = worksheet.Cells[1, 1, 1, 5]) 
		{
			range.Style.Font.Bold = true;
			range.Style.Fill.PatternType = ExcelFillStyle.Solid;
			range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
			range.Style.Font.Color.SetColor(Color.White);
		}

		worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
		worksheet.Cells["A5:E5"].Style.Font.Bold = true;

		worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2,3,4,3).Address);
		worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
		worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
		
		//Create an autofilter for the range
		worksheet.Cells["A1:E4"].AutoFilter = true;

		worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";   //Format as text

		//There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful. 
		//For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or 
		//you want to use the result of a formula in your program.
		worksheet.Calculate(); 

		worksheet.Cells.AutoFitColumns(0);  //Autofit columns for all cells

		// lets set the header text 
		worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
		// add the page number to the footer plus the total number of pages
		worksheet.HeaderFooter.OddFooter.RightAlignedText =
			string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
		// add the sheet name to the footer
		worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
		// add the file path to the footer
		worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

		worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
		worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];

		// Change the sheet view to show it in page layout mode
		worksheet.View.PageLayoutView = true;

		// set some document properties
		package.Workbook.Properties.Title = "Invertory";
		package.Workbook.Properties.Author = "Jan Källman";
		package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 workbook using EPPlus";

		// set some extended property values
		package.Workbook.Properties.Company = "AdventureWorks Inc.";

		// set some custom property values
		package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
		package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
		// save our new workbook and we are done!
		package.Save();

	}

	return newFile.FullName;
}

Esempio caso reale 1

Nel seguente esempio di interessante c'è la scrittura formattata delle date e l'auto aggiustamento delle colonne in base alla lunghezza di quanto rappresentato:

using System;
using System.Drawing;      // Per i colori
using System.IO;
using OfficeOpenXml;       // Riferimento principale della libreria
using OfficeOpenXml.Style; // Per lo stile delle celle

private static void WriteToExcelPlanFile(string strFileFullPath, List<MSPlannerVCVI> lstDestPlanner
										, object logOpObject, object logDataObject)
{
	string strLogTxtOut = string.Empty;
	string strFileName = string.Empty;
	int row = 0;
	try
	{
		strFileName = Path.GetFileName(strFileFullPath);

		FileInfo newFile = new FileInfo(strFileFullPath);
		if (newFile.Exists)
		{
			newFile.Delete();  // ensures we create a new workbook
			newFile = new FileInfo(strFileFullPath);
			strLogTxtOut += WriteLog(logOpObject, string.Format("{0} - Deleted existing File\"{1}\"\r\n"
															, GetCurrentMethod(), strFileName));
		}
		using (ExcelPackage package = new ExcelPackage(newFile))
		{
			// add a new worksheet to the empty workbook
			ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Planner Tasks");

			//Add the headers
			worksheet.Cells[1, 1].Value = "Title";
			worksheet.Cells[1, 2].Value = "Description";
			worksheet.Cells[1, 3].Value = "Start Date";
			worksheet.Cells[1, 4].Value = "End Date";
			worksheet.Cells[1, 5].Value = "Progress";
			worksheet.Cells[1, 6].Value = "AssignedTo";
			worksheet.Cells[1, 7].Value = "projectID";

			// Ok now format the values;
			// Scritte bold bianche su sfondo blu
			using (var range = worksheet.Cells[1, 1, 1, 7])
			{
				range.Style.Font.Bold = true;
				range.Style.Fill.PatternType = ExcelFillStyle.Solid;
				range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
				range.Style.Font.Color.SetColor(Color.White);
			}

			row = 2;
			foreach (MSPlannerVCVI plannerRow in lstDestPlanner) 
			{
				//Add items...
				worksheet.Cells[row, 1].Value = plannerRow.Title;
				worksheet.Cells[row, 2].Value = plannerRow.Description;
				if (plannerRow.StartDate == DateTime.MinValue)
				{
					worksheet.Cells[row, 3].Value = "NA";
				}
				else
				{
					worksheet.Cells[row, 3].Value = plannerRow.StartDate;
					worksheet.Cells[row, 3].Style.Numberformat.Format = "dd/mm/yyyy";
				}
				if (plannerRow.EndDate == DateTime.MinValue)
				{
					worksheet.Cells[row, 4].Value = "NA";
				}
				else
				{
					worksheet.Cells[row, 4].Value = plannerRow.EndDate;
					worksheet.Cells[row, 4].Style.Numberformat.Format = "dd/mm/yyyy";
				}
				worksheet.Cells[row, 5].Value = plannerRow.Progress;
				worksheet.Cells[row, 6].Value = plannerRow.AssignedTo;
				worksheet.Cells[row, 7].Value = plannerRow.ProjectID;
				row++;
			}
			worksheet.Cells.AutoFitColumns(); // Dimensiona automaticamente ogni colonna per adattarsi al valore rappresentato

			// set some document properties
			package.Workbook.Properties.Title = "Planner Tasks";
			package.Workbook.Properties.Author = "Giuseppe AINO";

			// set some extended property values
			package.Workbook.Properties.Company = "Vodafone";

			// set some custom property values
			package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Giuseppe AINO");
			package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
			// save our new workbook and we are done!
			package.Save();

		}
	}
	catch (Exception ex)
	{
		strLogTxtOut += WriteLog(logOpObject, string.Format("{0} - ERROR\r\n\t{1}\r\nOn file '{2}'\r\n"
													, GetCurrentMethod(), ex.Message, strFileName));
		throw;
	}
}

Lettura di un file

Come percorso in input al metodo va bene quello del file creato al paragrafo precedente

using System;
using System.IO;
using OfficeOpenXml;

/// <summary>
/// Simply opens an existing file and reads some values and properties
/// </summary>
class Sample2
{
	public static void RunSample2(string FilePath)
	{
		Console.WriteLine("Reading column 2 of {0}", FilePath);
		Console.WriteLine();

		FileInfo existingFile = new FileInfo(FilePath);
		using (ExcelPackage package = new ExcelPackage(existingFile))
		{
			// get the first worksheet in the workbook
			ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
			int col = 2; //The item description
			// output the data in column 2
			for (int row = 2; row < 5; row++)
				Console.WriteLine("\tCell({0},{1}).Value={2}", row, col, worksheet.Cells[row, col].Value);

			// output the formula in row 5
			Console.WriteLine("\tCell({0},{1}).Formula={2}", 3, 5, worksheet.Cells[3, 5].Formula);                
			Console.WriteLine("\tCell({0},{1}).FormulaR1C1={2}", 3, 5, worksheet.Cells[3, 5].FormulaR1C1);

			// output the formula in row 5
			Console.WriteLine("\tCell({0},{1}).Formula={2}", 5, 3, worksheet.Cells[5, 3].Formula);
			Console.WriteLine("\tCell({0},{1}).FormulaR1C1={2}", 5, 3, worksheet.Cells[5, 3].FormulaR1C1);

		} // the using statement automatically calls Dispose() which closes the package.

		Console.WriteLine();
		Console.WriteLine("Sample 2 complete");
		Console.WriteLine();
	}
}

Altro esempio:

using NLog;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;    //EPPlus library
using System.IO;
using System.Diagnostics;
using System.Runtime.CompilerServices;
using System.Text.RegularExpressions;

//..
private static Logger m_logger = LogManager.GetCurrentClassLogger();

//..
public static string ExportExcelFile4Planner(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);

		FileInfo excelFile = new FileInfo(strFileFullPath); //!!
		if (!excelFile.Exists)
		{
			throw new Exception(string.Format("File '{0}' not found!", strFileFullPath));
		}
		WriteLog(logObject, string.Format("{0} - {1}\r\n", GetCurrentMethod(),
										string.Format("FOUND file '{0}'", strFileName)));

		excelFile = new FileInfo(strFileFullPath);
		using (ExcelPackage pkgSrcExcel = new ExcelPackage(excelFile))
		{
			// get the first worksheet in the workbook
			ExcelWorksheet wrkSrcSheet = pkgSrcExcel.Workbook.Worksheets.First();

			ExcelCellAddress startSrcCell = wrkSrcSheet.Dimension.Start;
			ExcelCellAddress endSrcCell = wrkSrcSheet.Dimension.End;

			WriteLog(logObject, string.Format("Current sheet: \"{0}\"\t{1} rows \t{2} column"
												, wrkSrcSheet.Name, endSrcCell.Row, endSrcCell.Column));

			//Have to use for loops insteadof worksheet.Cells to protect against empties
			for (int row = startSrcCell.Row; row <= endSrcCell.Row; row++)
			{
				for (int col = startSrcCell.Column; col <= endSrcCell.Column; col++)
				{
					strLogTxtOut += string.Format("\t({0},{1})\t\t{2}\r\n", row, col, wrkSrcSheet.Cells[row, col].Value);
					
				}
			}
			WriteLog(logObject, strLogTxtOut);
		}
	}
	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;
}

Formattazione

Da riptutorial.com
NOTA La singola cella che nel primo esempio è riferita da worksheet.Cells["A1:A25"] ha come tipo: ExcelRange.

Date

//default DateTime patterns
worksheet.Cells["A1:A25"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

//custom DateTime patters
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";

//or overwrite the patterns in the CurrentThread with your own
Thread.CurrentThread.CurrentCulture = new CultureInfo("nl-NL")
{
    DateTimeFormat = { YearMonthPattern = "MMM yy" }
};
worksheet.Cells["A1:A25"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.YearMonthPattern;

Numeri

//integer (not really needed unless you need to round numbers, Excel with use default cell properties)
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "0";

//integer without displaying the number 0 in the cell
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "#";

//number with 1 decimal place
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "0.0";

//number with 2 decimal places
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "0.00";

//number with 2 decimal places and thousand separator
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "#,##0.00";

// Numbers
var moneyCell = sheet.Cells["A3"];
moneyCell.Style.Numberformat.Format = "$#,##0.00";
moneyCell.Value = 15.25M;

//number with 2 decimal places and thousand separator and money symbol
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "€#,##0.00";

// Ancora: Money
sheet.Cells["A2"].Value = "Moneyz";
sheet.Cells["B2,D2"].Value = 15000.23D;
sheet.Cells["C2,E2"].Value = -2000.50D;
sheet.Cells["B2:C2"].Style.Numberformat.Format = "#,##0.00 [$€-813];[RED]-#,##0.00 [$€-813]";
sheet.Cells["D2:E2"].Style.Numberformat.Format = "[$$-409]#,##0";


//percentage (1 = 100%, 0.01 = 1%)
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "0%";

Testo

worksheet.Cells["A1:A25"].Style.Numberformat.Format = "@";

Mappa e Link

C# | Librerie di terze parti PlugIn | Accesso ai files, Excel

Visual Studio | MS SQL

Parole chiave: