C# – Tworzenie pliku Excel. część 2.

C# – Tworzenie pliku Excel. część 2.

Dodamy teraz arkusz stylów do Exela.
Utworzymy nową metodę GenerateStylesheet(), która zwraca obiekt StyleSheet.

private Stylesheet GenerateStylesheet()
{
   Stylesheet styleSheet = null;   
   return styleSheet;
}

Utworzymy teraz Czcionki – Fonty.

Czcionki mogą mieć jedno lub więcej elementów podrzędnych Font, z których każdy ma inne właściwości, takie jak FontSize, Bold, Color itp. Dodamy teraz kod w metodzie GenerateStyleSheet:

Fonts fonts = new Fonts(
    new Font( 
       new FontSize() { Val = 10 }
    ),
    new Font( 
        new FontSize() { Val = 14 },
        new Bold(),
        new Color() { Rgb = "FFFFFF" }
    )
);

Zwróć uwagę, że dodajemy dwa elementy podrzędne Font do obiektu Fonts. Pierwszy z nich to domyślna czcionka używana przez wszystkie komórki, a druga jest specyficzna dla nagłówka.

Teraz dodamy wypełnienie.

Wypełnienia mogą mieć co najmniej jeden element podrzędny Fill, dla którego można ustawić jego kolor ForegroundColor.

Fills fills = new Fills(
   new Fill(new PatternFill() { PatternType = PatternValues.None }),
   new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }),             
   new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } }) { PatternType = PatternValues.Solid })
);

Excel musi mieć domyślnie pierwsze dwa. Trzeci to styl, jaki chcemy mieć dla naszych komórek nagłówkowych; szare pogrubione tło.

Teraz obramowania – Borders.

Obramowania mogą mieć jedno lub więcej elementów podrzędnych Border, z których każde określa, jak powinno wyglądać obramowanie:

Borders borders = new Borders(
   new Border(),
   new Border(
       new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Dotted },
       new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Dotted },
       new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
       new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
       new DiagonalBorder())
);

Pierwsza to ramka domyślna, a druga to nasza niestandardowa ramka.

CellFormats

Teraz, gdy ustawilismy nasze niestandardowe formatowanie, możemy utworzyć CellFormats, które mają jedno lub wiele elementów potomnych CellFormat. A Każdy CellFormat otrzymuje indeks Czcionki, Obramowania, Wypełnienie itp. Z którym będzie skojarzony:

CellFormats cellFormats = new CellFormats(
   new CellFormat(),
   new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, 
   new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true }
);

Na koniec inicjalizujemy obiekt Stylesheet:

styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);

Teraz możemy dodać styl do skoroszytu.

WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();

Dodajemy styl do komórek

Teraz, gdy mamy dodany styl do skoroszytu, możemy określić, jaki styl ma posiadać każda komórka. Każda Cell ma właściwość o nazwie StyleIndex, która pobiera indeks stylu, który chcemy zastosować do tej komórki. Modyfikujemy i przekazujemy pożądany indeks stylu:

Row row = new Row();
   row.Append(new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2 },
       new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2 },
       new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2 });

    sheetData.AppendChild(row);

    for (int i = 0; i < 20; i++)
    {
       row = new Row();
       row.Append(new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1 },
          new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1 });

    sheetData.AppendChild(row);
}

Możemy dodać niestandardową szerokość kolumn.

Columns columns = new Columns(
        new Column 
        {
            Min = 1,
            Max = 1,
            Width = 30,          
            CustomWidth = true      
        },
        new Column 
        {
            Min = 2,
            Max = 5,
            Width = 20,
            CustomWidth = true
        },
        new Column 
        {
            Min = 6,
            Max = 6,
            Width = 40,
            CustomWidth = true
        }
        ...
        );

Na koniec musimy dołączyć kolumny do arkusza roboczego.

workheetPart.Worksheet.AppendChild(columns);

Uruchom aplikację i sprawdź wygenerowany plik Excel!

https://github.com/mariuszjurczenko/ExcelOpenXml

35 comments

  1. Dzień dobry. Ten artykuł był niezwykle fascynujący, zwłaszcza że w zeszły wtorek szukałem przemyśleń na ten temat. Britteny Richard Mima

  2. Po przejrzeniu kilku postów na blogu w Twojej witrynie, bardzo podoba mi się Twoja technika pisania bloga. Hedvig Ignazio Colp

  3. Naprawdę doceniam udostępnienie tego artykułu na blogu. Naprawdę nie mogę się doczekać, aby przeczytać więcej. Fajne. Ginnie Husain Turoff

  4. I really like your writing style, good information, thanks for putting up : D. Dianna Avery Wessling

  5. Wow! After all I got a blog from where I know how to truly obtain helpful facts concerning my study and knowledge. Maxi Leonhard Kronick

  6. I blog frequently and I seriously appreciate your content. Gena Gail Urania

  7. Loving the info on this web site, you have done great job on the content. Lora Abbot Red

  8. I really enjoy the blog post. Much thanks again. Cool. Nannette Dallon Hugibert

  9. Incredible points. Great arguments. Keep up the great work.| Mirabelle Che Naples

  10. I always emailed this weblog post page to all my associates, for the reason that if like to read it after that my contacts will too.| Addie Emmy Matias

  11. Way cool! Some very valid points! I appreciate you writing this article and the rest of the site is really good. Rafa Fransisco Haida

  12. Everyone loves it when folks come together and share opinions. Great blog, stick with it! Robinette Grantley Virgilia

  13. Hi there, simply changed into alert to your weblog thru Google, and found that it is truly informative. I am gonna watch out for brussels. I will be grateful if you happen to proceed this in future. Numerous folks shall be benefited from your writing. Cheers!| Laurene Coop Avron

  14. Wow, this piece of writing is pleasant, my sister is anakyzing these things, therefore I am going to convey her. Neysa Jay Finnigan

  15. It was very useful, I can say it was a useful article for me. Marylin Karel Zobe

  16. Its like you learn my mind! You seem to grasp a lot approximately this, such as you wrote the e-book in it or something. Selina Shermy Rox

  17. I simply wanted to construct a simple remark so as to appreciate you for those remarkable tips and tricks you are showing here. My incredibly long internet research has at the end of the day been compensated with really good suggestions to write about with my family members. I would claim that many of us website visitors are really endowed to exist in a fantastic website with many perfect people with useful advice. I feel extremely fortunate to have used your entire web site and look forward to some more enjoyable times reading here. Thanks once more for everything. Elva Launce Souza

  18. Thanks a lot for the article. Much thanks again. Great. Elita Bo Felix

  19. The iPad is the place tablet notebooks are heading. It can replace most netbooks. Marcille Yanaton Cuda

  20. Greate article. Keep writing such kind of info on your blog. Charline Bent Roede

  21. You need to be a part of a contest for one of the greatest websites online. I am going to highly recommend this web site! Shela Bradley Lewendal

  22. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You obviously know what youre talking about, why throw away your intelligence on just posting videos to your blog when you could be giving us something informative to read? Britney Tobin Gentry

  23. What a stuff of un-ambiguity and preserveness of valuable experience on the topic of unpredicted emotions. Madelin Thibaut Karrie

  24. Pretty section of content. I just stumbled upon your web site and in accession capital to assert that I get in fact enjoyed account your blog posts. Any way I will be subscribing to your feeds and even I achievement you access consistently quickly. Savina Lyell Dallon

  25. Everything is very open with a clear clarification of the issues. It was really informative. Your site is useful. Thanks for sharing! Pippa Eldin Janek

  26. Thank you for your blog article. Thanks Again. Great. Zonda Booth Penelope

  27. Like!! I blog frequently and I really thank you for your content. The article has truly peaked my interest. Petronia Dennis Armbruster

  28. Good post. I am going through a few of these issues as well.. Viviene Kerby Stander

  29. After looking at a handful of the blog articles on your website, I really appreciate your technique of writing a blog. I saved as a favorite it to my bookmark site list and will be checking back soon. Please visit my website too and let me know how you feel. Nonna Mendy Shieh

  30. udało ci się trafić w sedno, prawdopodobnie wrócę po więcej. Dzięki

  31. You actually make it seem so easy along with your presentation having
    said that i find this topic to get really something that I do
    believe I might never understand. It appears too complex and very broad for me personally.
    I’m anticipating for your forthcoming post, I will try to get the hang from it!

  32. Czytam i czytam, oczy przecieram i prawie nie wierze… ten tekst robi wielkie wrażenie mówię Ci szczerze 🙂

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *