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

Dodajemy Wykres.

Dzisiaj pokażę jak możemy narysować wykres w arkuszu Excel przy użyciu OpenXML.

Krok – 1

Utworzymy teraz arkusz kalkulacyjny z danymi i nie użyjemy tutaj kodu z poprzednich części. Rozpoczynamy nowy projekt a kod to kompletny kod do tworzenia arkusza Excela z danymi co już umiemy robić. Powinno to być łatwe do zrozumienia. Jeśli nie, zapoznaj się z poprzednimi częściami tej serii.

Kod ten inicjuje niektóre przykładowe dane przy użyciu klasy Person i używa tych danych do utworzenia arkusza kalkulacyjnego. Do arkusza kalkulacyjnego nie zastosowano żadnych stylów żeby niepotrzebnie nie komplikować!

class ExcelChartTest
{
   public void CreateExcelDoc(string fileName)
   {
       List<Person> people = new List<Person>();
       Initizalize(people);

       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 = "People" };
          SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
          sheets.Append(sheet);
          workbookPart.Workbook.Save();

              
          // Constructing header
          Row row = new Row();
          // first empty
          row.AppendChild(ConstructCell(string.Empty, CellValues.String));
          foreach (var month in Months.Short)
          {
             row.AppendChild(ConstructCell(month, CellValues.String));
          }
          // Insert the header row to the Sheet Data
          sheetData.AppendChild(row);

          // Inserting people
          foreach (var person in people)
          {
             row = new Row();
             row.AppendChild(ConstructCell(person.Name, CellValues.String));

             foreach (var value in person.Values)
             {
                row.AppendChild(ConstructCell(value.ToString(), CellValues.Number));
             }
             sheetData.AppendChild(row);
          }
         worksheetPart.Worksheet.Save();
      }
   }

   private Cell ConstructCell(string value, CellValues dataType)
   {
      return new Cell()
      {
         CellValue = new CellValue(value),
         DataType = new EnumValue<CellValues>(dataType),
      };
   }

   private void Initizalize(List<Person> people)
   {
      people.AddRange(new Person[] {
         new Person
         {
            Name = "Marcin",
            Values = new byte[] { 14, 25, 29, 18, 21, 17, 26, 24, 19, 21, 28, 24 }
         },
         new Person
         {
            Name = "Mariusz",
            Values = new byte[] { 20, 15, 26, 18, 21, 17, 26, 24, 19, 30, 10, 15 }
         },
         new Person
         {
           Name = "Tomek",
           Values = new byte[] {  18, 22, 24, 18, 30, 10, 19, 22, 15, 27, 18, 23 }
         }
      });
   }
}
    public class Person
    {
        public string Name { get; set; }
        public byte[] Values { get; set; }
    }
    public struct Months
    {
        public static string[] Short = {
            "Jan",
            "Feb",
            "Mar",
            "Apr",
            "May",
            "Jun",
            "Jul",
            "Aug",
            "Sep",
            "Oct",
            "Nov",
            "Dec"
        };
    }

Krok – 2

I teraz W metodzie Main klasy Program utworzymy obiekt naszej klasy ExcelChartTest i wywołamy metodę CreateExcelDocument, przekazując ścieżkę do pliku.

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

      Console.WriteLine("Zrobione");
   }
}

Możemy uruchomić program i zobaczyć plik excel z tabelką danych dla której stworzymy wykres!

Krok – 3

Narysujemy wykres w arkuszu kalkulacyjnym. Po utworzeniu arkusza dodajmy DrawingsPart do arkusza i inicjujemy rysunek w arkuszu.

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

// step 3
DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) });
worksheetPart.Worksheet.Save();
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
// end step 3

sheets.Append(sheet);
workbookPart.Workbook.Save();

Krok 4

Dodajemy wykres DravingPart.

// step 4
ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.AppendChild(new EditingLanguage() { Val = "en-US" });

Chart chart = chartPart.ChartSpace.AppendChild(new Chart());
chart.AppendChild(new AutoTitleDeleted() { Val = true }); // We don't want to show the chart title

Krok 5

Dodajemy PlotArea do wykresu i dołącz Layout oraz BarChart jako jego elementy podrzędne.

// step 5
PlotArea plotArea = chart.AppendChild(new PlotArea());
Layout layout = plotArea.AppendChild(new Layout());

BarChart barChart = plotArea.AppendChild(new BarChart(
        new BarDirection() { Val = new EnumValue<BarDirectionValues>(BarDirectionValues.Column) },
        new BarGrouping() { Val = new EnumValue<BarGroupingValues>(BarGroupingValues.Clustered) },
        new VaryColors() { Val = false }
 ));


// Constructing header
Row row = new Row();
int rowIndex = 1;
// first empty
row.AppendChild(ConstructCell(string.Empty, CellValues.String));
foreach (var month in Months.Short)
{
    row.AppendChild(ConstructCell(month, CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
rowIndex++;

Krok 6

Dodajemy serię i kategorie dla wykresu. Po skonstruowaniu wiersza nagłówka, dla każdej osoby dodamy ChartSeries do BarChart. Dla każdego BarSeries dodajemy komórki odniesienia w arkuszu kalkulacyjnym, tworząc formułę People!$B$0:$G$0. Po dodaniu referencji utworzymy StringCache dla rzeczywistych danych.

// step 6 
for (int i = 0; i < people.Count; i++)
{
   BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(
      new Index() { Val = (uint)i },
      new Order() { Val = (uint)i },
      new SeriesText(new NumericValue() { Text = people[i].Name })
   ));

   // Adding category axis to the chart
   CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData());

   // Category
   // Constructing the chart category
   string formulaCat = "People!$B$1:$M$1";

   StringReference stringReference = categoryAxisData.AppendChild(new StringReference()
   {
      Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaCat }
   });

   StringCache stringCache = stringReference.AppendChild(new StringCache());
   stringCache.Append(new PointCount() { Val = (uint)Months.Short.Length });

   for (int j = 0; j < Months.Short.Length; j++)
   {
      stringCache.AppendChild(new NumericPoint() { Index = (uint)j }).Append(new NumericValue(Months.Short[j]));
   }
}

var chartSeries = barChart.Elements<BarChartSeries>().GetEnumerator();

Krok 7

Dodajemy wartości dla wykresu dla każdej osoby dodajemy rzeczywiste wartości do każdej serii. Zwróć uwagę, że tak samo jak w przypadku kategorii dodajemy odniesienie do danych w arkuszu kalkulacyjnym za pomocą formuły, a także dodajemy rzeczywiste dane do pamięci podręcznej.
!!!! usuwamy // Inserting people

// step 7
for (int i = 0; i < people.Count; i++)
{
  row = new Row();

  row.AppendChild(ConstructCell(people[i].Name, CellValues.String));

  chartSeries.MoveNext();

  string formulaVal = string.Format("People!$B${0}:$M${0}", rowIndex);
  DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());

  NumberReference numberReference = values.AppendChild(new NumberReference()
  {
    Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaVal }
  });

  NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache());
  numberingCache.Append(new PointCount() { Val = (uint)Months.Short.Length });

  for (uint j = 0; j < people[i].Values.Length; j++)
  {
    var value = people[i].Values[j];

    row.AppendChild(ConstructCell(value.ToString(), CellValues.Number));

    numberingCache.AppendChild(new NumericPoint() { Index = j }).Append(new NumericValue(value.ToString()));
  }

  sheetData.AppendChild(row);
  rowIndex++;
}

barChart.AppendChild(new DataLabels(
          new ShowLegendKey() { Val = false },
          new ShowValue() { Val = false },
          new ShowCategoryName() { Val = false },
          new ShowSeriesName() { Val = false },
          new ShowPercent() { Val = false },
          new ShowBubbleSize() { Val = false }
));

barChart.Append(new AxisId() { Val = 48650112u });
barChart.Append(new AxisId() { Val = 48672768u });

Krok 8

Konfigurowanie CategoyAxis i ValueAxis. Do tej pory dodaliśmy rysunek do arkusza i dołączaliśmy do niego wykres. Następnie dodaliśmy ChartSeries do Chart. Rzeczywista kategoria i wartości zostały dodane do każdej ChartSeries. Teraz musimy ustawić osie kategorii i wartości!!!

// step 8
// Adding Category Axis
plotArea.AppendChild(
   new CategoryAxis(
      new AxisId() { Val = 48650112u },
      new Scaling(new Orientation() { Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }),
      new Delete() { Val = false },
      new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) },
      new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) },
      new CrossingAxis() { Val = 48672768u },
      new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
      new AutoLabeled() { Val = true },
      new LabelAlignment() { Val = new EnumValue<LabelAlignmentValues>(LabelAlignmentValues.Center) }
   )
);

// Adding Value Axis
plotArea.AppendChild(
   new ValueAxis(
      new AxisId() { Val = 48672768u },
      new Scaling(new Orientation() { Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }),
      new Delete() { Val = false },
      new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Left) },
      new MajorGridlines(),
      new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
      {
         FormatCode = "General",
         SourceLinked = true
      },
      new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) },
      new CrossingAxis() { Val = 48650112u },
      new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
      new CrossBetween() { Val = new EnumValue<CrossBetweenValues>(CrossBetweenValues.Between) }
   )
);

chart.Append(
   new PlotVisibleOnly() { Val = true },
   new DisplayBlanksAs() { Val = new EnumValue<DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap) },
   new ShowDataLabelsOverMaximum() { Val = false }
);

chartPart.ChartSpace.Save(); 

Krok 9

TwoCellAnchor. Na koniec musimy zdefiniować, gdzie fizycznie ma się pojawić wykres. Na tym polega odpowiedzialność TwoCellAnchor.

// step 9
// Positioning the chart on the spreadsheet
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());

twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(
      new ColumnId("0"),
      new ColumnOffset("0"),
      new RowId((rowIndex + 2).ToString()),
      new RowOffset("0")
));

twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(
      new ColumnId("8"),
      new ColumnOffset("0"),
      new RowId((rowIndex + 12).ToString()),
      new RowOffset("0")
));

// Append GraphicFrame to TwoCellAnchor
GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame());
graphicFrame.Macro = string.Empty;

graphicFrame.Append(new NonVisualGraphicFrameProperties(
     new NonVisualDrawingProperties()
     {
         Id = 2u,
         Name = "Sample Chart"
     },
     new NonVisualGraphicFrameDrawingProperties()
));

graphicFrame.Append(new Transform( new DocumentFormat.OpenXml.Drawing.Offset() { X = 0L, Y = 0L },
   new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0L, Cy = 0L }
));

graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic( new DocumentFormat.OpenXml.Drawing.GraphicData(
   new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) }
             )
         { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }
));

twoCellAnchor.Append(new ClientData());

I na koniec zapisujem rysunek.

drawingsPart.WorksheetDrawing.Save();
worksheetPart.Worksheet.Save();
Cały kod na githubie:

https://github.com/mariuszjurczenko/ExcelOpenXml

50 comments

  1. Kiedy piszesz historie swojego zycia, nie pozwll, aby ktos inny trzymal pilro

  2. Kazdy potrafi wspllczuc cierpieniom przyjaciela. Ale cieszenie sie jego sukcesem wymaga charakteru wysokiej klasy. Oscar Wilde.

  3. Hello colleagues, its enormous piece of writing regarding cultureand entirely explained, keep it up all the time. Ajay Kevin Anitra

  4. Enjoyed every bit of your article. Really looking forward to read more. Really Great. Calli Jerome Eudo

  5. I view something genuinely interesting about your site so I saved to favorites. Larisa Wallie Plato

  6. Dziękuję za ten wspaniały post. Cieszę się, że znalazłem tę stronę internetową na Yahoo. Valaria Ruprecht Selma

  7. Jestem wdzięczny właścicielowi tej witryny, który udostępnił tutaj ten imponujący artykuł. Judy Pyotr Gati

  8. Hi, yeah this paragraph is really good and I have learned lot of things from it concerning blogging.
    thanks.

  9. I absolutely love your blog and find nearly all of your post’s to be just what I’m looking for.
    Do you offer guest writers to write content for yourself?
    I wouldn’t mind producing a post or elaborating on a lot of the subjects you
    write in relation to here. Again, awesome web log!

  10. Its not my first time to pay a visit this web page, i am visiting this web page dailly and obtain pleasant facts from here daily. Bernadine Trefor Borreri

  11. Simply wish to say your article is as surprising.
    The clearness in your post is simply nice and i can assume
    you’re an expert on this subject. Well with your permission allow me to grab your feed to keep updated with forthcoming post.
    Thanks a million and please continue the rewarding work.

  12. It as very straightforward to find out any matter on net as compared to books, as I found this article at this web page. Roze Royce Landmeier Shalna Ringo Quenby

  13. very good post, i actually love this website, keep on it Dania Bruce Gerlac

  14. Tech Addiction Media is a blogging platform providing tutorials, ideas and information on entertainment using streaming technology for home and business, and also provides information relating to social media, marketing, entrepreneurship, and technology. Learn how to stream everything for free and subscribe to get the best tips and tricks on streaming technology. Glynnis Lew Sato

  15. Kazdy potrafi wspolczuc cierpieniom przyjaciela. Ale cieszenie sie jego sukcesem wymaga charakteru wysokiej klasy. Oscar Wilde.

  16. I was looking everywhere and this popped up like nothing! Frannie Peder Routh

  17. Well I truly enjoyed reading it. This tip provided by you is very helpful for proper planning. Flora Asher Dowling Ernesta Bran Rind

  18. I think this is among the most significant information for me. And i am glad reading your article. But wanna remark on some general things, The web site style is perfect, the articles is really nice : D. Good job, cheers Cthrine Durante Sup

  19. Wow! Thank you! I continually needed to write on my site something like that. Can I implement a fragment of your post to my blog? Agnola Andie Sension

  20. Way cool! Some very valid points! I appreciate you penning this post and also the rest of the site is very good. Page Claudio Hayden

  21. Thank you for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your further post thank you once again. Leese Alistair Elboa

  22. Your means of describing the whole thing in this paragraph is actually pleasant, every one can easily understand it, Thanks a lot.| Annie Hendrick Babbette

  23. I just could not go away your website before suggesting that I extremely loved the usual info an individual provide in your guests? Is going to be back ceaselessly to check up on new posts Odele Kenn Bevvy

  24. Thank you Debbie for these informative articles & tips. I enjoyed reading them & have already been adhering to many of your suggestions already. I pray that you, your family & supporters stay well. Blessings! Gretchen Cesar Maro

  25. Dobry artykuł. Z pewnością doceniam tę stronę. Trzymaj tak dalej

  26. Zwykle, zanim zycie wreczy nam swoje najwspanialsze prezenty, owija je starannie w najwieksze przeciwnosci losu… – Richard Paul Evans

Dodaj komentarz

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