Login Login
MORE

CSharp:EPPlus X Excel

From Aino Wiki

Jump to: navigation, search

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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;
}
EPPlusSample01.png

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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
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

?
1
2
3
4
5
6
7
8
9
10
11
12
//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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
//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

?
1
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:

Retrieved from "http://wiki.aino.it/index.php?title=CSharp:EPPlus_X_Excel&oldid=6199"
Author
Giuseppe AINO