使用OpenXml为Excel表格添加合计行

添加前表格:

运行后结果:

实现代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ConsoleApplication7
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Multiselect = false;
            ofd.Filter = "Excel Document|*.xlsx";
            ofd.ShowDialog();
            string opath = ofd.FileName;
            string newpath = AppDomain.CurrentDomain.BaseDirectory 
                + "test.xlsx";
            File.Copy(opath, newpath, true);
            using (SpreadsheetDocument sd = SpreadsheetDocument
                .Open(newpath, true))
            {
                WorkbookPart wp = sd.WorkbookPart;
                Workbook wb = wp.Workbook;
                Sheet s = wb.Descendants<Sheet>().Where(w => w.Name == "Sheet1")
                    .FirstOrDefault();
                WorksheetPart sp = wp.GetPartById(s.Id) as WorksheetPart;
                if (sp != null)
                {
                    SheetData sda = sp.Worksheet.Descendants<SheetData>()
                        .FirstOrDefault();
                    Row cr = new Row() { RowIndex = (UInt32Value)8U };
                    Cell c1 = CreateCell("A8", "SUBTOTAL(109,A2:A7)", "51");
                    Cell c2 = CreateCell("B8", "SUBTOTAL(109,B2:B7)", "57");
                    Cell c3 = CreateCell("C8", "SUBTOTAL(109,C2:C7)", "63");
                    cr.Append(c1);
                    cr.Append(c2);
                    cr.Append(c3);
                    sda.Append(cr);
                    CalculationChainPart ccp = wp
                        .AddNewPart<CalculationChainPart>();
                    CalculationChain cc = new CalculationChain();
                    CalculationCell cc1 = new CalculationCell()
                    {
                        CellReference = "A8",
                        SheetId = 1,
                        NewLevel = true
                    };
                    CalculationCell cc2 = new CalculationCell()
                    {
                        CellReference = "B8",
                        SheetId = 1
                    };
                    CalculationCell cc3 = new CalculationCell()
                    {
                        CellReference = "C8",
                        SheetId = 1
                    };
                    cc.Append(cc1);
                    cc.Append(cc2);
                    cc.Append(cc3);
                    ccp.CalculationChain = cc;
                }
                wb.Save();
            }
        }

        private static Cell CreateCell(string p, string p_2, string p_3)
        {
            Cell result = new Cell() { CellReference = p };
            CellFormula cf = new CellFormula() { Text = p_2 };
            CellValue cv = new CellValue() { Text = p_3 };
            result.Append(cf);
            result.Append(cv);
            return result;
        }
    }
}