C# – Tworzenie pliku Excel.

C# – Tworzenie pliku Excel.

Pokaże teraz jak skonstruować plik Excel w C#. W tym samouczku użyjemy biblioteki Open XML, C# i .NET Core. Zaczynajmy!

  1. Przechodzimy do Visual Studio 2019 i utworzymy nową aplikację konsolową C#.
  2. Musimy dodać odniesienie do biblioteki: DocumentFormat.OpenXml
  3. Dodajemy nową klasę i nazwjemy ja np. Test
  4. I tworzymy publiczną metodę o nazwie CreateExcelDoc. W tej metodzie utworzymy i zapiszemy nasz plik Excel.
  5. Importujemy odpowiednie przestrzenie nazw.
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Pdf
{
    public class Test
    {
        public void CreateExcelDoc(string fileName)
        {
        }
    }
}

6. Teraz tworzymy nowy dokument arkusza kalkulacyjnego i przekazujemy nazwę pliku i dokument jako parametry.

using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{  }

7. SpreadsheetDocument musi mieć WorkbookPart i WorkSheetPart. Dodamy następujący kod w bloku using.

using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))

{
      WorkbookPart workbookPart = document.AddWorkbookPart();
      workbookPart.Workbook = new Workbook();

      WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
      worksheetPart.Worksheet = new Worksheet(new SheetData());         
}

8. Dołączamy Arkusze do skoroszytu. Arkusze będą zawierać jeden lub wiele arkuszy.

Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

9. Następnie możemy dodać jeden lub wiele Arkuszy.

Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Arkusz 1" };
sheets.Append(sheet);

10. Na koniec zapis skoroszytu.

workbookPart.Workbook.Save();

11. I teraz w metodzie Main klasy Program utwórzmy obiekt naszej klasy test i wywołajmy metodę CreateExcelDocument, przekazując ścieżkę do pliku.

static void Main(string[] args)
{
     Test test = new Test();
     test.CreateExcelDoc(@"C:\Excel\test.xlsx");
}

12. Uruchommy projekt i sprawdź wygenerowany plik Excel.

13. Jeśli chcemy coś zapisać dodajemy SheetData do arkusza. SheetData działa jak kontener, do którego będą trafiać wszystkie wiersze i kolumny.

SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

14. Następnie dodajemy wiersz Row. Klasa Row reprezentuje wiersz w arkuszu kalkulacyjnym programu Excel. Każdy wiersz może zawierać jedną lub więcej komórek Cell. Każda komórka będzie miała CellValue, która zawiera rzeczywistą wartość w komórce.

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

15. Dodaj wiersz do arkusza kalkulacyjnego.

sheetData.AppendChild(row);

I na końcu zapisujemy arkusz.

worksheetPart.Worksheet.Save();

16. Uruchommy projekt i sprawdź wygenerowany plik Excel.

Cały kod klasy Test.

public class Test
{
        public void CreateExcelDoc(string fileName)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Arkusz 1" };
                sheets.Append(sheet);

                workbookPart.Workbook.Save();

                SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
                Row row = new Row();
                Cell cell = new Cell() { CellValue = new CellValue("napis test "), DataType = CellValues.String };
                row.Append(cell);

                sheetData.AppendChild(row);
                worksheetPart.Worksheet.Save();
            }
       }
}

https://github.com/mariuszjurczenko/ExcelOpenXml

36 comments

  1. Cześć. Całkiem ciekawy artykuł. Mam jednak problem z decimalami. Jak można je dodać? DataType = CellValues.Number nie wystarczy i działa tylko na inty.

  2. This paragraph is in fact a nice one it helps new net viewers, who are wishing for blogging. Elayne Shepard Derriey

  3. Thanks to my father who shared with me about this web site, this website is actually awesome. Babbette Nicolas Wyon Nanine Carey Caldwell

  4. Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest. Belicia Gerard Mallin

  5. I in addition to my buddies were studying the great helpful tips found on the blog while then came up with an awful feeling I never thanked the web site owner for those tips. My guys became for this reason excited to see all of them and now have pretty much been tapping into these things. Many thanks for genuinely indeed thoughtful as well as for making a decision on this kind of helpful resources millions of individuals are really needing to be aware of. My personal honest regret for not expressing appreciation to earlier. Malynda Timothee Ximenez

  6. Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a little bit, but other than that, this is magnificent blog. A great read. I will definitely be back. Tami Lyle Farrica

  7. Having read this I believed it was rather informative. I appreciate you finding the time and energy to put this information together. I once again find myself spending a lot of time both reading and posting comments. But so what, it was still worth it! Mari Derron Chadburn

  8. Wow, this post is pleasant, my younger sister is analyzing these kinds of things, therefore I am going to tell her.| Gwenore Morgun Penrose

  9. This site is my inhalation, real fantastic layout and Perfect subject matter. Joellen Gonzales Cordula Elmira Rubin Lehmann

  10. Hiya, I am really glad I have found this information. Today bloggers publish only about gossip and web stuff and this is really annoying. A good website with interesting content, this is what I need. Thanks for making this site, and I will be visiting again. Do you do newsletters by email? Allina Ralph Charline

  11. I have read a few good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to create such a magnificent informative site. Rosabel Augustus Clapper

  12. I enjoy you because of all of your effort on this web site. My mother enjoys making time for investigation and it is simple to grasp why. We know all of the powerful medium you provide effective tips on the blog and as well foster contribution from other people on this concern then our favorite child has always been becoming educated a great deal. Take advantage of the rest of the year. Your carrying out a stunning job. Ashli Kermy Simdars

  13. I have learn some good stuff here. Definitely worth bookmarking for revisiting. I surprise how so much attempt you set to make this type of fantastic informative website. Nathalia Roderic Naldo

  14. I feel genuinely thrilled to have seen your current webpage and appearance forward to be able to so many more interesting times reading here. Cheers yet again for all typically the details. Beilul Hersh Jollenta

  15. Thanks-a-mundo for the blog article. Really looking forward to read more. Engracia Elden Edouard Ivette Hillier Intosh

  16. Great site. Plenty of helpful information here. I am sending it to several buddies ans additionally sharing in delicious. And naturally, thank you for your sweat! Cari Ryan Claudell

  17. Hello, I enjoy readinhg all off you post. I wanted to write a little comment to support you. Morganica Timothy Ruperto

  18. I blog quite often and I truly appreciate your information. Your article has truly peaked my interest. I will book mark your site and keep checking for new information about once per week. I opted in for your Feed too. Renate Byram Braynard

  19. Twoja metoda wyjaśniania wszystkiego w tym artykule jest naprawdę przyjemna, wszyscy mogą to łatwo zrozumieć. Wielkie dzięki.

  20. Po przejrzeniu kilku postów na blogu w Twojej witrynie, szczerze doceniam Twój sposób pisania bloga. Dokonałem rezerwacji i oznaczyłem to na mojej liście zakładek i wrócę w najbliższej przyszłości.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *