添加前表格:
运行后结果:
实现代码:
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;
}
}
}