dotNETportal.cz

Pokud chceme v .NET vytvořit Excel soubor máme asi čtyři možnosti (pokud nepočítám drahé řešení nejvyšší verze SharePoint Serveru 2010).

1. Excel application COM object model
   Jedná se o volání přímo Excelu přes jeho COM rozhraní (ActiveX). Výhodou je, že lze v podstatě provést cokoli co umí samotný excel. Nevýhodou je nutnost volání z .NET přes interop knihovny, to sice za pomoci značných vylepšení v .NET 4.0, nebo pomoci Visual Studio Tools for Office rozšíření již není tak náročné na používání, ale na pozadí se pořád jedná o volání COM objektů a tím se ztrácí výkonnost (prostě to není managed code). Další nevýhodou je nutnost instalace Microsoft Office na počítači a proto se tato varianta hodí výhradně pro tlusté klientské aplikace.
2. OleDb Provider
   Pomoci datového ovladače OleDb pro Microsoft Office lze přistoupit k Excel souboru tak jako by to byla databázová tabulka a načítat nebo vkládat data. Toto řešení je jednoduché, ale umožňuje pouze základní operace s Excel souborem a rozhodně se na některé scénáře nehodí.
3. EPPlus nebo ExcelPackage library
   Jedná se o plně managed .NET knihovnu EPPlus.dll. Knihovna využívá System.IO.Packaging API v .NETu a umožňuje proto generovat Excel dokumenty formátu Open XML tj. soubory formátu Excel 2007 (.docx). Je ke stažení na http://epplus.codeplex.com. Knihovna navazuje a značně rozšiřuje předchozí projekt ExcelPackage library (ExcelPackage.dll) na http://excelpackage.codeplex.com.
4. Knihovna NPOI
   Jedná se o open source knihovnu, sloužící pro tvorbu binárních .xls souborů (formát Excel 97-2003). Knihovna je ke stažení na http://npoi.codeplex.com.

Pokud potřebujeme generovat Excel na serveru např. jako výstup ASP.NET aplikace, první způsob je v tomto případě naprosto nevhodný (nutnost instalace Excel na server, slabší výkonost). Všude kde to lze doporučuji použít knihovnu EPPlus / ExcelPackage pro generování .docx souborů. Knihovna má jednoduchý public interface, snažící se co nejvíce přiblížit Excel COM objekt modelu. Příklady a popisy použití jsou např. zde nebo zde.

Ačkoli už je to nějaký ten pátek od vzniku Open XML formátu Office dokumentů, pořád narážím na zákazníky, kteří mají s těmi čtyřpísmennými formáty s x na konci nějaký problém, proto když požadují generovat .xls soubor, nezbývá nic jiného, než tedy šáhnout po NPOI knihovně. A té se dále budu v tomto článku věnovat.

NPOI knihovna je vytvořena jako portace z jazyku Java (projekt POI Java) do .NETu a nutno říct, že je to na knihovně vidět. Osobně mi public inteface knihovny připadá nepřehledný a místy zbytečně složitý na používání. Navíc nové verze mají nekompatibilní interface a tak strávíme dost času přechodem na novější verzi. A další značnou nevýhodou je v podstatě nulová dokumentace. Nicméně jelikož se jedná o jednou knihovnu pro tvorbu .xls souborů, nezbývá nám nic jiného než děkovat autorům za tento projekt a knihovnu používat.

(Pozn. Pokud by jsme potřebovali v aplikaci generovat jak xls tak i xlsx soubory, bude to dost náročné, protože musíme použít knihovny obě a ještě kód pro generování dokumentů bude v podstatě úplně odlišný, na rozdíl od použití COM object modelu, kde by jsme jen na konci při ukládání generovaného dokumentu jedním parametrem specifikovali výstupní formát.)

Základní použití NPOI knihovny je popsáno v článku zde. Také je možné využít pomocnou wrapper třídu, která je k dispozici zde (článek autora zde). Já zde uvedu jednoduchý příklad jak načíst již vytvořený excel soubor (z pole bajtů), provést vněm úpravu a pak znovu soubor vrátit:

public static byte[] ModifyExcel(byte[] excelData)
{
    HSSFWorkbook workbook;
    //Načtení excel souboru
    using (var stream = new MemoryStream(excelData))
    {
        //Getting the complete workbook
        workbook = new HSSFWorkbook(stream, true);
    }

    //Getting the worksheet
    Sheet sheet = workbook.GetSheetAt(0);

    //Úprava excelu
    Row row = sheet.GetRow(1);
    Cell cell = row.GetCell(0); //Cell A1
    cell.SetCellType(CellType.STRING);
    cell.SetCellValue("Modified");

    using (var ms = new MemoryStream())
    {
        workbook.Write(ms);
        ms.Close();
        return ms.ToArray();
    }
}

(Kód je vytvořen pro NPOI v. 1.2.3.0)

Když vytváříme trochu složitější excel můžeme narazit na některé problémy. Následující příklady metod Vám mohou pomoci při řešení některých z nich.

Získání adresy oblasti excelu (string, kterým se určí oblast např. ve výrazech):

public static string GetRangeString(int rowFrom, int colFrom, int rowTo, int colTo)
{
    var region = new NPOI.SS.Util.Region(rowFrom, colFrom, rowTo, colTo);
    NPOI.SS.Util.CellRangeAddress address = NPOI.SS.Util.Region.ConvertToCellRangeAddress(region);
    return address.FormatAsString();
}

Volání  GetRangeString(0, 0, 1, 2) vrací “A1:C2”.

Nastavení výrazu do buňky excelu (Formula):

public static void SetCellFormula(Cell cell, string formula)
{
    var ci = System.Threading.Thread.CurrentThread.CurrentCulture;
    try
    {
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
        cell.CellFormula = formula.Replace(",", ".").Replace(";", ",");
    }
    finally
    {
        System.Threading.Thread.CurrentThread.CurrentCulture = ci;
    }
}

Příklady volání:
SetCellFormula(cell, "21 * 7,5");
SetCellFormula(cell, "SUBTOTAL(9;A1:A4)");
Při volání se do výrazu nepíše počáteční znak “=”.

Aby se nastavení výrazu provedlo správně pro český Excel zápis výrazu, musíme trochu obejít “chybu” knihovny a nastavení provést v EN kultuře. Aby ale výraz byl pro EN kulturu správný, musíme provést zaměnění desetinné čárky na tečku a také zaměnit středník na čárku – oddělovač parametrů funkcí.

Pokud také měníme některé pole, které mají vliv na již exitující políčky s  formula výrazy, musíme vynutit jejich přepočet následujícím kódem:

sheet.ForceFormulaRecalculation = true;

Zkopírování stylu buňky:

public static CellStyle CopyCellStyle(HSSFWorkbook workbook, CellStyle sourceCellStyle)
{
    var style = workbook.CreateCellStyle();

    style.Alignment = sourceCellStyle.Alignment;
    style.VerticalAlignment = sourceCellStyle.VerticalAlignment;
    style.SetFont(sourceCellStyle.GetFont(workbook));
    style.DataFormat = sourceCellStyle.DataFormat;
    style.WrapText = sourceCellStyle.WrapText;
    style.FillForegroundColor = sourceCellStyle.FillForegroundColor;
    style.FillPattern = sourceCellStyle.FillPattern;
    style.BorderTop = sourceCellStyle.BorderTop;
    style.BorderLeft = sourceCellStyle.BorderLeft;
    style.BorderRight = sourceCellStyle.BorderRight;
    style.BorderBottom = sourceCellStyle.BorderBottom;
    style.TopBorderColor = sourceCellStyle.TopBorderColor;
    style.LeftBorderColor = sourceCellStyle.LeftBorderColor;
    style.RightBorderColor = sourceCellStyle.RightBorderColor;
    style.BottomBorderColor = sourceCellStyle.BottomBorderColor;

    return style;
}

Příklad použití je následující:

//Copy cell style
CellStyle style = CopyCellStyle(workbook, sourceCell.CellStyle);

//Modify style
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;

cell.CellStyle = style;

Kdybychom pouze nastavili CellStyle z buňky sourceCell buňce cell a poté ho změnili, změna by se provedla v obou buňkách (protože obě buňky mají stejný styl). Pokud tedy potřebujeme buňce nastavit poupravený styl jiné buňky, musíme vytvořit nový styl a podle stylu sourceCell  pouze nastavit stejně vlastnosti, to je vytažené do metody CopyCellStyle. Poté již můžeme styl změnit, např. změnit barvu podkladu jako je v příkladu, a poté tento nový styl nastavit buňce.

Nastavení vlastní RGB barvy:

Pokud nám nestačí předdefinované barvy excelu v NPOI.HSSF.Util.HSSFColor  a potřebujeme nastavit vlastní barvu pomoci RGB složek, musíme změnit paletu barvy pod některým z existujících identifikátorů (indexu). Proto si musíme vybrat index některé barvy co v excel souboru ještě nepoužíváme (to je samozřejmě nevýhodné z důvodu, že identifikátor pak neoznačuje naší barvu což je v kódu zavádějící).

//Změna barvy na RGB 222, 231, 247
var palette = workbook.GetCustomPalette();
short colorIndex = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;
palette.SetColorAtIndex(colorIndex, (byte)0xDE, (byte)0xE7, (byte)0xF7);

Tento kód změní barvu pod indexem GREY_40_PERCENT na barvu RGB 222, 231, 247. Tato změna barvy se projeví ve všech buňkách Excelu, které mají ve svém stylu nastavenou barvu s indexem GREY_40_PERCENT.

Vložení řádku:

public static void InsertRow(Sheet sheet, int row)
{
    sheet.ShiftRows(row, sheet.LastRowNum, 1, true, false);

    var rowSource = sheet.GetRow(row + 1);
    var rowInsert = sheet.CreateRow(row);

    for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
    {
        var cellSource = rowSource.GetCell(colIndex);
        var cellInsert = rowInsert.CreateCell(colIndex);
        if (cellSource != null)
        {
            cellInsert.CellStyle = cellSource.CellStyle;
        }
    }

    rowInsert.Height = rowSource.Height;
}

Metoda InsertRow vloží jeden nový řádek nad řádek s indexem row. Provádí to tak, že nejprve volání ShiftRows posune řádky pod řádkem row a pak na vzniklém prázdném řádku vytvoří buňky (metodou CreateCell) a nastaví stejný styl jako mají buňky na řádku row.

V tomto článku jsem ukázal několik pomocných příkladů řešící problémy, na které jsem narazil při tvorbě Excel souborů pomoci knihovny NPOI. Myslím si, že mohou posloužit i někomu dalšímu.