开放XML SDK Excel公式重新计算缓存问题 [英] open xml sdk excel formula recalculate cache issue
问题描述
我看了所有的previous回答了类似的问题在左右，但这些问题的答案不工作。我通过在Excel中的价值和希望公式单元格重新计算，但其仍然显示旧值。
我已经使用了2技术1）从小区2）打开删除的计算值并以编程方式关闭该文件。他们都没有为我工作。这里是我的code。请帮助。
字符串文件名=使用Server.Mappath（/）+ExcelData.xlsx //获取在Excel工作表，保存2个数字，并关闭它。 使用（S preadsheetDocument文件= S preadsheetDocument.Open（文件名，真实））
{
苫布苫布= document.WorkbookPart.Workbook.Descendants＆LT;表方式＆gt;（）的SingleOrDefault（S = GT; s.Name ==myRange1）;
如果（表== NULL）
{
抛出新的ArgumentException（
的String.Format（没有命名表{0}以s preadsheet发现{1}，myRange1，文件名），SHEETNAME）;
}
WorksheetPart worksheetPart =（WorksheetPart）document.WorkbookPart.GetPartById（sheet.Id）; INT的rowIndex = int.Parse（C3.Substring（1））; 鳞次栉比= worksheetPart.Worksheet.GetFirstChild＆LT; SheetData＆GT;（）。
元素＆lt;行方式＆gt;（）FirstOrDefault（R =＆GT; r.RowIndex == rowIndex位置）; 细胞CELL3 = row.Elements＆下;电池＆gt;（）FirstOrDefault（C =＆gt;中C3.Equals（c.CellReference.Value））;
如果（CELL3！= NULL）
{
cell3.CellValue =新CellValue（13）;
cell3.DataType =新DocumentFormat.OpenXml.EnumValue＆LT; CellValues＆GT;（CellValues.Number）;
} document.WorkbookPart.WorksheetParts
.SelectMany（部分=＆GT; part.Worksheet.Elements＆LT; SheetData＆GT;（））
.SelectMany（数据=＆GT; data.Elements＆LT;行＆GT;（））
.SelectMany（ROW1 = GT; row1.Elements＆下;电池＆gt;（））
。凡（电池=＆GT; cell.CellFormula = NULL＆放大器;！＆安培;！cell.CellValue = NULL）
.ToList（）
.ForEach（小区= GT; cell.CellValue.Remove（））; worksheetPart.Worksheet.Save（）; } //得到结果了Excel中。
使用（S preadsheetDocument文件= S preadsheetDocument.Open（文件名，FALSE））
{
document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = TRUE;
document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = TRUE; 苫布苫布= document.WorkbookPart.Workbook.Descendants＆LT;表方式＆gt;（）的SingleOrDefault（S = GT; s.Name ==myRange1）;
如果（表== NULL）
{
抛出新的ArgumentException（
的String.Format（没有命名表{0}以s preadsheet发现{1}，myRange1，文件名），SHEETNAME）;
} WorksheetPart worksheetPart =（WorksheetPart）document.WorkbookPart.GetPartById（sheet.Id）; INT的rowIndex = int.Parse（C4.Substring（1））; 鳞次栉比= worksheetPart.Worksheet.GetFirstChild＆LT; SheetData＆GT;（）。
元素＆lt;行方式＆gt;（）FirstOrDefault（R =＆GT; r.RowIndex == rowIndex位置）; 细胞细胞= row.Elements＆下;电池＆gt;（）FirstOrDefault（C =＆gt;中的C4.Equals（c.CellReference.Value））; calculatedvalue = Convert.ToDouble（cell.CellValue.InnerText）;
}
从你提供的code片段我错过你提到博客的重要组成部分。
如该博客OpenXML的规定只能用于创建/读取/更新和删除用于处理OpenXML兼容的应用程序的存储格式。你没有得到实际的应用引擎。为了达到你想要什么，你要么需要在服务器上安装Excel，以便您可以利用的命名空间的Microsoft.Office.Interop.Excel 和它的应用 COM接口或依赖于最终用户Excel应用程序做重新计算openening更新Excel工作表时。
VAR excelApp =新Microsoft.Office.Interop.Excel.Application（）;
工作簿簿= excelApp.Workbooks.Open（Path.GetFullPath（_filePath））;
workbook.Close（真）;
excelApp.Quit（）;
请一定要明白，如果你需要给服务器上（因为你需要在一个网页的计算结果为例）运行，你可能会遇到严重的性能故障，内存，I / O和CPU明智的。在这种情况下，我宁愿C＃实现的计算规则/公式。
I have looked at all the previous answers to the similar questions in SO but those answers do not work. I pass in the value in excel and want the formula cell to recalculate but its still showing up the old value.
I have used the 2 techniques 1) remove the calculated value from the cell 2)Open and close the file programmatically. Both of them don't work for me. Here is my code. Please help.
string filename = Server.MapPath("/") + "ExcelData.xlsx";
// getting 2 numbers in excel sheet, saving, and closing it.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, true))
{
Sheet sheet = document.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == "myRange1");
if (sheet == null)
{
throw new ArgumentException(
String.Format("No sheet named {0} found in spreadsheet {1}", "myRange1", filename), "sheetName");
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
int rowIndex = int.Parse("C3".Substring(1));
Row row = worksheetPart.Worksheet.GetFirstChild<SheetData>().
Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
Cell cell3 = row.Elements<Cell>().FirstOrDefault(c => "C3".Equals(c.CellReference.Value));
if (cell3 != null)
{
cell3.CellValue = new CellValue("13");
cell3.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
}
document.WorkbookPart.WorksheetParts
.SelectMany(part => part.Worksheet.Elements<SheetData>())
.SelectMany(data => data.Elements<Row>())
.SelectMany(row1 => row1.Elements<Cell>())
.Where(cell => cell.CellFormula != null && cell.CellValue != null)
.ToList()
.ForEach(cell => cell.CellValue.Remove());
worksheetPart.Worksheet.Save();
}
// getting the result out of excel.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false))
{
document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
Sheet sheet = document.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == "myRange1");
if (sheet == null)
{
throw new ArgumentException(
String.Format("No sheet named {0} found in spreadsheet {1}", "myRange1", filename), "sheetName");
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
int rowIndex = int.Parse("C4".Substring(1));
Row row = worksheetPart.Worksheet.GetFirstChild<SheetData>().
Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
Cell cell = row.Elements<Cell>().FirstOrDefault(c => "C4".Equals(c.CellReference.Value));
calculatedvalue = Convert.ToDouble(cell.CellValue.InnerText);
}
From the by you provided code snippet I miss the essential part from the blog you referred to.
As stated in that blog OpenXML can only be used for creating/reading/updating and deleting the storage format for OpenXML compatible applications. You don't get the actual application engine. To achieve what you want you either need to install Excel on your server so you can leverage Microsoft.Office.Interop.Excel namespace and it's Application COM interface or rely on the end-users Excel application to do the recalculation when openening the updated excel sheet.
code copied and text adapted from this blog
var excelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook = excelApp.Workbooks.Open(Path.GetFullPath(_filePath));
workbook.Close(true);
excelApp.Quit();
Please do realize that if you need this to run on a server (for example because you need the calculated results in a webpage) you might run into serious performance trouble, memory, I/O and CPU wise. In that case I would rather implement the calculation rules/formulas in C#.
这篇关于开放XML SDK Excel公式重新计算缓存问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
