Operacje na plikach Excel przy wykorzystaniu biblioteki NPOI

Bibliotek NPOI - .NET software development

Operacje na plikach Excel przy wykorzystaniu biblioteki NPOI

W pracy nad tworzeniem aplikacji .NET wielokrotnie możemy spotykać się z sytuacją, w której potrzebujemy pobrać dane z pliku Excel lub stworzyć arkusz od podstaw. Dostępnych jest wiele bibliotek, które można w tym celu z powodzeniem wykorzystać, ale jest jedna, która przyciąga swoją funkcjonalnością: NPOI. Charakteryzuje ją kilka aspektów: jest bezpłatna, pokrywa dużą część oryginalnych funkcjonalności arkusza, takich jak: formatowanie komórek, formatowanie danych czy definicja formuł, wspiera rozszerzenia .xls, .xlsx, a także posiada sporą ilość gotowych implementacji dostępnych na dedykowanej dla tej biblioteki stronie GitHub, co ułatwia i przyspiesza proces implementacji własnych rozwiązań. 

W poniższym artykule zapoznamy się z operacjami na plikach Excel przy wykorzystaniu tej właśnie biblioteki.

W tym celu użyjemy danych z przygotowanego wcześniej arkusza, a następnie sami utworzymy taki arkusz. Przygotowany plik zawiera kilka rekordów z imieniem, nazwiskiem oraz adresem e-mail osoby.

Na początek dla naszej aplikacji zainstalujemy pakiet:

Install-Package DotNetCore.NPOI

Następnie utworzymy klasę reprezentującą wiersz w arkuszu:

public class Person

{

    public int No { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string EmailAddress { get; set; }

}

Teraz możemy przystąpić do operacji odczytu i zapisu.

 

Odczyt danych z pliku

 

Możemy teraz zacząć odczytywać dane z arkusza. W tym celu utworzymy metodę:

private IEnumerable GetPersonsFromFile(string filePath)

Biblioteka NPOI dostarcza dwie klasy reprezentujące plik Excel:

  • HSSFWorkbook dla plików .xls
  • XSSFWorkbook dla plików .xlsx

Aby poprawnie odczytać plik Excel, bez względu na jego rozszerzenie, możemy zrobić to ręcznie w następujący sposób:

using FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

 

IWorkbook workbook;

if (filePath.IndexOf(„.xlsx”) > 0)

{

    workbook = new XSSFWorkbook(fs);

}

else if (filePath.IndexOf(„.xls”) > 0)

{

    workbook = new HSSFWorkbook(fs);

}

else

{

    return null;

}

Możemy jednak tego uniknąć, stosując klasę WorkbookFactory, która zajmie się identyfikacją rozszerzenia i zwróci potrzebny nam obiekt:

using FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

IWorkbook workbook = WorkbookFactory.Create(fs);

 

Potrzebujemy jeszcze odczytać pierwszy arkusz z pliku:

ISheet sheet = workbook.GetSheetAt(0);

Odczytywanie danych odbywa się poprzez odniesienie się do konkretnego wiersza oraz konkretnej komórki arkusza. Indeksy komórek oraz wierszy rozpoczynają się od zera.
Mając na uwadze nagłówek w naszym arkuszu rozpoczniemy odczytywanie od wiersza nr 1. Następnie, po weryfikacji poprawności wierszy, utworzymy nowe obiekty je reprezentujące.

Przy odczytywaniu wartości z komórek arkusza należy zwrócić uwagę na typ danych, jaki dana komórka przechowuje.

for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)

{

    IRow currentRow = sheet.GetRow(rowIndex);

 

    if (currentRow != null && currentRow.Cells.Count.Equals(4))

    {

        yield return new Person()

        {

            No = Convert.ToInt32(currentRow.GetCell(0).NumericCellValue.ToString()),

            FirstName = currentRow.GetCell(1).StringCellValue,

            LastName = currentRow.GetCell(2).StringCellValue,

            EmailAddress = currentRow.GetCell(3).StringCellValue

        };

    }

}

Poprawność metody możemy łatwo zweryfikować wypisując pobrane z arkusza osoby w konsoli:

GetPersonsFromFile(@”C:\npoiDemoFile.xlsx”)

    .ToList()

    .ForEach(

        person =>

        Console.WriteLine(

            $”{person.No}. {person.FirstName} {person.LastName}, ” +

            $”{person.EmailAddress}”

        )

);

 

Zapis danych do pliku

 

W celu zapisu danych do pliku utworzymy metodę przyjmującą listę osób, które zapiszemy oraz ścieżkę do pliku:

private void SavePersonsToFile(IEnumerable persons, string filePath)

Zapis zaczniemy od dodania arkusza do pliku:

IWorkbook workbook = new XSSFWorkbook();

ISheet sheet = workbook.CreateSheet(„Sheet_1”);

Następnie utworzymy wiersz nagłówkowy.
Aby odwzorować nasz przykładowy arkusz, użyjemy stylów oraz czcionek, które następnie przypiszemy do odpowiednich komórek.

int rowIndex = 0;

var row = sheet.CreateRow(rowIndex++);

var headerStyle = workbook.CreateCellStyle();

headerStyle.Alignment = HorizontalAlignment.Center;

headerStyle.BorderBottom = BorderStyle.Thin;

headerStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;

headerStyle.FillPattern = FillPattern.SolidForeground;

 

var font = workbook.CreateFont();

font.FontName = „Verdana”;

font.FontHeightInPoints = 11;

font.IsBold = true;

headerStyle.SetFont(font);

 

var noHeaderCell = row.CreateCell(0);

noHeaderCell.CellStyle = headerStyle;

noHeaderCell.SetCellValue(„No.”);

 

var fNameHeaderCell = row.CreateCell(1);

fNameHeaderCell.CellStyle = headerStyle;

fNameHeaderCell.SetCellValue(„First name”);

 

var lNameHeaderCell = row.CreateCell(2);

lNameHeaderCell.CellStyle = headerStyle;

lNameHeaderCell.SetCellValue(„Last name”);

 

var emailHeaderCell = row.CreateCell(3);

emailHeaderCell.CellStyle = headerStyle;

emailHeaderCell.SetCellValue(„Email address”);

Ostatnim elementem edycji arkusza jest dodanie wierszy z poszczególnymi osobami:

foreach(var person in persons.OrderBy(person => person.No))

{

    row = sheet.CreateRow(rowIndex++);

    row.CreateCell(0).SetCellValue(person.No);

    row.CreateCell(1).SetCellValue(person.FirstName);

    row.CreateCell(2).SetCellValue(person.LastName);

    row.CreateCell(3).SetCellValue(person.EmailAddress);

}

Na koniec pozostaje tylko zapis pliku:

using var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);

workbook.Write(fs);

W celu weryfikacji wywołamy utworzoną metodę:

SavePersonsToFile(GetPersonsFromFile(@”C:\npoiDemoFile.xlsx”), @”C:\newNpoiDemoFile.xlsx”);

Wynikiem jest następujący arkusz z poprawnie dodanym stylem do komórek nagłówka oraz poprawnie wypisanymi osobami:

To tylko prosta prezentacja podstawowych możliwości biblioteki NPOI, pokazująca jednak, że w nieskomplikowany sposób możemy – za pomocą kodu – operować na arkuszach danych. Możliwości biblioteki są bardzo rozległe, więc z dużą dozą prawdopodobieństwa będziemy mogli wykorzystać ją do innych rozwiązań, których moglibyśmy użyć korzystając ze standardowego arkusza Excel.

 

Autor:

Wiktor Baran

Senior Quant .NET Developer

ALTEN Polska