向SpreadSheetDocument中的LineChart添加新的数据

原始文件如下：

代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using log4net;
using System.Windows.Forms;
using DocumentFormat.OpenXml.Packaging;
using System.IO;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using C = DocumentFormat.OpenXml.Drawing.Charts;

namespace ExcelAppendChartData
{
    class Program
    {
        private static ILog log = log4net.LogManager.GetLogger(typeof(Program));
        
        [STAThread]
        static void Main(string[] args)
        {
            string source = string.Empty;
            string target = string.Empty;
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Open Source Spreadsheet Document";
            ofd.Multiselect = false;
            ofd.Filter = "Spreadsheet Document (*.xlsx)|*.xlsx";
            ofd.InitialDirectory = @"C:\Users\v-yangxu\Desktop\Work";
            ofd.ShowDialog();
            source = ofd.FileName;
            target = AppDomain.CurrentDomain.BaseDirectory + "Result.xlsx";
            File.Copy(source, target, true);
            using (SpreadsheetDocument sd = SpreadsheetDocument
                .Open(target, true))
            {
                WorkbookPart wp = sd.WorkbookPart;
                log.Info("Get WorkbookPart");
                Workbook wb = wp.Workbook;
                log.Info("Get Workbook");
                Sheet s = wb.Descendants<Sheet>().Where(S => S.Name == "Sheet1")
                    .FirstOrDefault();
                log.Info("Sheet rId : " + s.Id);
                WorksheetPart wsp = wp.GetPartById(s.Id) as WorksheetPart;
                if (wsp != null)
                {
                    log.Info("Get WorksheetPart");
                    Worksheet w = wsp.Worksheet;
                    SheetData sda = w.Descendants<SheetData>().FirstOrDefault();
                    Row r = new Row() { RowIndex = (UInt32Value)5U };
                    log.Info("=========Begin to Append Data==========");
                    Cell c1 = AddCell("A5", "2");
                    log.Info("A5 : 2");
                    Cell c2 = AddCell("B5", "4");
                    log.Info("B5 : 4");
                    Cell c3 = AddCell("C5", "3");
                    log.Info("C5 : 3");
                    Cell c4 = AddCell("D5", "2");
                    log.Info("D5 : 2");
                    r.Append(c1);
                    r.Append(c2);
                    r.Append(c3);
                    r.Append(c4);
                    sda.Append(r);
                    log.Info(">>>>>>>>End Append<<<<<<<<<<<<<");
                    SheetDimension sdms = w.SheetDimension;
                    sdms.Reference = "A1:D5";
                    DrawingsPart dp = wsp.DrawingsPart;
                    if (dp != null)
                    {
                        log.Info("Get DrawingsPart");
                        ChartPart cp = dp.ChartParts.FirstOrDefault();
                        if (cp != null)
                        {
                            log.Info("Get ChartPart");
                            C.ChartSpace cs = cp.ChartSpace;
                            C.Chart c = cs.Descendants<C.Chart>().FirstOrDefault();
                            C.PlotArea pa = c.PlotArea;
                            C.LineChart lc = pa.Descendants<C.LineChart>()
                                .FirstOrDefault();
                            C.LineChartSeries lcs = new C.LineChartSeries();
                            C.Index i = new C.Index() { Val = (UInt32Value)3U };
                            C.Order o = new C.Order() { Val = (UInt32Value)3U };
                            C.Marker m = new C.Marker();
                            C.Symbol sy = new C.Symbol()
                            {
                                Val = C.MarkerStyleValues.None
                            };
                            m.Append(sy);
                            C.CategoryAxisData cad = new C.CategoryAxisData();
                            C.StringReference stringReference1 = 
                                new C.StringReference();
                            C.Formula formula1 = new C.Formula();
                            formula1.Text = "Sheet1!$A$1:$D$1";

                            C.StringCache stringCache1 = new C.StringCache();
                            C.PointCount pointCount1 = new C.PointCount() 
                            { 
                                Val = (UInt32Value)4U 
                            };

                            C.StringPoint stringPoint1 = new C.StringPoint() 
                            { 
                                Index = (UInt32Value)0U 
                            };
                            C.PointCount p = new C.PointCount() 
                            { 
                                Val = (UInt32Value)4U 
                            };
                            stringCache1.Append(p);
                            C.NumericValue numericValue1 = new C.NumericValue();
                            numericValue1.Text = "A";

                            stringPoint1.Append(numericValue1);

                            C.StringPoint stringPoint2 = new C.StringPoint() 
                            { 
                                Index = (UInt32Value)1U 
                            };
                            C.NumericValue numericValue2 = new C.NumericValue();
                            numericValue2.Text = "B";

                            stringPoint2.Append(numericValue2);

                            C.StringPoint stringPoint3 = new C.StringPoint() 
                            { 
                                Index = (UInt32Value)2U 
                            };
                            C.NumericValue numericValue3 = new C.NumericValue();
                            numericValue3.Text = "C";

                            stringPoint3.Append(numericValue3);

                            C.StringPoint stringPoint4 = new C.StringPoint() 
                            { 
                                Index = (UInt32Value)3U 
                            };
                            C.NumericValue numericValue4 = new C.NumericValue();
                            numericValue4.Text = "D";
                            stringPoint4.Append(numericValue4);

                            stringReference1.Append(formula1);
                            stringCache1.Append(stringPoint1);
                            stringCache1.Append(stringPoint2);
                            stringCache1.Append(stringPoint3);
                            stringCache1.Append(stringPoint4);
                            stringReference1.Append(stringCache1);
                            cad.Append(stringReference1);

                            C.Values values1 = new Values();

                            C.NumberReference numberReference1 = 
                                new C.NumberReference();
                            C.Formula formula2 = new C.Formula();
                            formula2.Text = "Sheet1!$A$5:$D$5";

                            C.NumberingCache numberingCache1 = 
                                new C.NumberingCache();
                            C.FormatCode formatCode1 = new C.FormatCode();
                            formatCode1.Text = "General";
                            C.PointCount pointCount2 = new C.PointCount() 
                            { 
                                Val = (UInt32Value)4U 
                            };

                            C.NumericPoint numericPoint1 = new C.NumericPoint() 
                            { 
                                Index = (UInt32Value)0U 
                            };
                            C.NumericValue numericValue5 = new C.NumericValue();
                            numericValue5.Text = "2";

                            numericPoint1.Append(numericValue5);

                            C.NumericPoint numericPoint2 = new C.NumericPoint() 
                            { 
                                Index = (UInt32Value)1U 
                            };
                            C.NumericValue numericValue6 = new C.NumericValue();
                            numericValue6.Text = "4";

                            numericPoint2.Append(numericValue6);

                            C.NumericPoint numericPoint3 = new C.NumericPoint() 
                            { 
                                Index = (UInt32Value)2U 
                            };
                            C.NumericValue numericValue7 = new C.NumericValue();
                            numericValue7.Text = "3";

                            numericPoint3.Append(numericValue7);

                            C.NumericPoint numericPoint4 = new C.NumericPoint() 
                            { 
                                Index = (UInt32Value)3U 
                            };
                            C.NumericValue numericValue8 = new C.NumericValue();
                            numericValue8.Text = "2";

                            numericPoint4.Append(numericValue8);

                            numberingCache1.Append(formatCode1);
                            numberingCache1.Append(pointCount1);
                            numberingCache1.Append(numericPoint1);
                            numberingCache1.Append(numericPoint2);
                            numberingCache1.Append(numericPoint3);
                            numberingCache1.Append(numericPoint4);

                            numberReference1.Append(formula2);
                            numberReference1.Append(numberingCache1);
                            values1.Append(numberReference1);

                            C.Smooth smth = new C.Smooth() { Val = false };

                            lcs.Append(i);
                            lcs.Append(o);
                            lcs.Append(m);
                            lcs.Append(cad);
                            lcs.Append(values1);
                            lcs.Append(smth);
                            C.LineChartSeries lst = lc
                                .Descendants<C.LineChartSeries>().Last();

                            lc.InsertAfter<C.LineChartSeries>(lcs, lst);
                            w.Save();
                        }
                        else
                        {
                            log.Fatal("Can't get ChartPart");
                            Console.ReadKey();
                            return;
                        }
                    }
                    else
                    {
                        log.Fatal("Can't get DrawingsPart");
                        Console.ReadKey();
                        return;
                    }
                }
                else
                {
                    log.Fatal("Can't get WorksheetPart");
                }
                Console.ReadKey();
            }
        }

        static Cell AddCell(string reference, string value)
        {
            Cell result = new Cell() { CellReference = reference };
            CellValue cv = new CellValue() { Text = value };
            result.Append(cv);
            return result;
        }
    }
}

效果如下：

---

欢迎访问《 许阳的红泥屋》