openxml sdk excel如何解析和计算公式 [英] openxml sdk excel how to parse and calculate formula
问题描述
从云中的远程Web服务器上托管的Web应用程序（没有安装Excel），我将传入C2和C3的值。
我还可以在excel中确定确切的公式。如何在c＃中以编程方式解析该公式，如果C2和C3的输入值分别为2和4，我可以得到6的结果？
如果公式非常复杂，如何在asp.net mvc应用程序中在服务器端的C＃中解析公式并计算它的最佳方法是什么？
如果你提供一个打开excel文件的工具，并将其内容翻译成html，您必须处理计算。
如果文件创建良好，例如手动使用Excel，您可以确保你不需要管理公式的计算，因为excel做的技巧和存储在CellFormula的子元素中的公式，并导致CellValue的子元素（参见方法GetValue_D11（））。所以基本上你只需要显示结果..它总是一个String。
不幸的是，你必须处理style和dataTypes，如果你想要行为。
其实你有构建一个复杂的基于Web的电子表格查看器/编辑器。
下面是一个用于检索String值和公式值的固定的（完全不是动态的）的示例。如果您想运行测试，请确保下载该文件（ http://www.devnmore.com/ share / Test.xlsx ）否则它不能工作。
ShowValuesSample svs = new ShowValuesSample（yourPath\\ \\\Test.xlsx）;
String [] test = svs.GetDescriptions_A2A10（）;
Double grandTotal = svs.GetValue_D11（）;
ShowValuesSample类：
使用系统;
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Threading.Tasks;
使用DocumentFormat.OpenXml.Packaging;
使用Ap = DocumentFormat.OpenXml.ExtendedProperties;
使用Vt = DocumentFormat.OpenXml.VariantTypes;
使用DocumentFormat.OpenXml;
使用DocumentFormat.OpenXml.Spreadsheet;
使用A = DocumentFormat.OpenXml.Drawing;
使用System.Globalization;
命名空间TesterApp
{
public class ShowValuesSample
{
public String FileName {get;私人集}
private SpreadsheetDocument _ExcelDocument = null;
public SpreadsheetDocument ExcelDocument
{
get
{
if（_ExcelDocument == null）
{
_ExcelDocument = SpreadsheetDocument.Open（FileName，真正）;
}
return _ExcelDocument;
}
}
私人SheetData _SheetDataOfTheFirstSheet = null;
public SheetData SheetDataOfTheFirstSheet
{
get
{
if（_SheetDataOfTheFirstSheet == null）
{
WorksheetPart shPart = ExcelDocument.WorkbookPart.WorksheetParts .ElementAt（0）;
工作表wsh = shPart.Worksheet;
_SheetDataOfTheFirstSheet = wsh.Elements< SheetData>（）。ElementAt（0）;
}
返回_SheetDataOfTheFirstSheet;
}
}
private SharedStringTable _SharedStrings = null;
public SharedStringTable SharedStrings
{
get
{
if（_SharedStrings == null）
{
SharedStringTablePart shsPart = ExcelDocument.WorkbookPart.SharedStringTablePart ;
_SharedStrings = shsPart.SharedStringTable;
}
return _SharedStrings;
}
}
public ShowValuesSample（String fileName）
{
FileName = fileName;
}
//在文件描述中存储为sharedString
//所以cellValue它是在我的例子中保存的sharedStringTable
//的零基索引9不同的值
// sharedstring它是减少文件大小的一个技巧obiouvsly写
//重复字符串一次
public String [] GetDescriptions_A2A10（）
{
String [] retVal = new String [9]; （int i = 0; i< retVal.Length; i ++）
行r = SheetDataOfTheFirstSheet.Elements< Row>（）ElementAt（i + 1 ）;
Cell c = r.Elements< Cell>（）。ElementAt（0）;
Int32 shsIndex = Convert.ToInt32（c.CellValue.Text）;
SharedStringItem shsItem = SharedStrings.Elements< SharedStringItem>（）。ElementAt（shsIndex）;
retVal [i] = shsItem.Text.Text;
}
return retVal;
}
//存储的值isacause excel
//要确保它是正确的，你应该执行所有的计算
//在这种情况下，我是确定Excel没有存储错误的值所以..
public Double GetValue_D11（）
{
Double retVal = 0.0d;
Int32 cellIndex = 0;
// cellIndex它是0而不是3，因为A11，B11，C11是空单元格
//另一个问题要处理;-)
单元格c = SheetDataOfTheFirstSheet.Elements< Row> ）.ElementAt（10）.Elements<电池>（）的ElementAt（cellIndex）。
//作为例子来看一下storeFormula的值
String storedFormula = c.CellFormula.Text;
String storedValue = c.CellValue.Text;
NumberFormatInfo provider = new NumberFormatInfo（）;
provider.NumberDecimalSeparator =。;
provider.NumberGroupSeparator =，;
provider.NumberGroupSizes = new Int32 [] {3};
retVal = Convert.ToDouble（storedValue，provider）;
return retVal;
}
}
}
I have formula cell in excel file that has the formula =SUM(C2:C3).
From the web application hosted on remote webserver in the cloud, that does not have Excel installed, I would pass in values for C2 and C3.
I can also determine the exact formula in excel. How do I parse this formula programmatically in c# so that I could get the result of 6 if the input values of C2 and C3 were 2 and 4 respectively?
What if the formula is very complex, what is the best way to parse the formula and calculate it in C# on the server side in asp.net mvc application?
Code sample would really benefit me in this case.
If you provide a tool to open excel file and translate it's content to html you must deal with calculation.
If the file is "well created", for example manually with Excel you can be sure you don't need to manage computation of the formulas cause excel does the trick and stores both the formula in CellFormula's child element and result in CellValue's child element (See the method GetValue_D11()). So basically you just need to show the result.. which always will be a String.
Unfortunately you have to deal with styles and dataTypes, if you want to mantain behaviour.
Actually you have to build a complex web based spreadsheet viewer/editor.
Here is a sample "fixed" (totally not dynamic for all) for retrieving String values and formula values. if you wanna run the test be sure to download that file (http://www.devnmore.com/share/Test.xlsx) otherwise it can't works.
ShowValuesSample svs = new ShowValuesSample("yourPath\\Test.xlsx");
String[] test = svs.GetDescriptions_A2A10();
Double grandTotal = svs.GetValue_D11();
ShowValuesSample class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml.Packaging;
using Ap = DocumentFormat.OpenXml.ExtendedProperties;
using Vt = DocumentFormat.OpenXml.VariantTypes;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using System.Globalization;
namespace TesterApp
{
public class ShowValuesSample
{
public String FileName { get; private set; }
private SpreadsheetDocument _ExcelDocument = null;
public SpreadsheetDocument ExcelDocument
{
get
{
if (_ExcelDocument == null)
{
_ExcelDocument = SpreadsheetDocument.Open(FileName, true);
}
return _ExcelDocument;
}
}
private SheetData _SheetDataOfTheFirstSheet = null;
public SheetData SheetDataOfTheFirstSheet
{
get
{
if (_SheetDataOfTheFirstSheet == null)
{
WorksheetPart shPart = ExcelDocument.WorkbookPart.WorksheetParts.ElementAt(0);
Worksheet wsh = shPart.Worksheet;
_SheetDataOfTheFirstSheet = wsh.Elements<SheetData>().ElementAt(0);
}
return _SheetDataOfTheFirstSheet;
}
}
private SharedStringTable _SharedStrings = null;
public SharedStringTable SharedStrings
{
get
{
if (_SharedStrings == null)
{
SharedStringTablePart shsPart = ExcelDocument.WorkbookPart.SharedStringTablePart;
_SharedStrings = shsPart.SharedStringTable;
}
return _SharedStrings;
}
}
public ShowValuesSample(String fileName)
{
FileName = fileName;
}
//In the file descriptions are stored as sharedString
//so cellValue it's the zeroBased index of the sharedStringTable
//in my example i saved 9 different values
//sharedstring it's a trick to reduce size of a file obiouvsly writing
//repetitive string just once
public String[] GetDescriptions_A2A10()
{
String[] retVal = new String[9];
for (int i = 0; i < retVal.Length; i++)
{
Row r = SheetDataOfTheFirstSheet.Elements<Row>().ElementAt(i + 1);
Cell c = r.Elements<Cell>().ElementAt(0);
Int32 shsIndex = Convert.ToInt32(c.CellValue.Text);
SharedStringItem shsItem = SharedStrings.Elements<SharedStringItem>().ElementAt(shsIndex);
retVal[i] = shsItem.Text.Text;
}
return retVal;
}
//The value it's stored beacause excel does
//To be sure it's correct you should perform all calculations
//In this case i'm sure Excel didn't stored the wrong value so..
public Double GetValue_D11()
{
Double retVal = 0.0d;
Int32 cellIndex = 0;
//cellIndex it's 0 and not 3, cause A11, B11, C11 are empty cells
//Another issue to deal with ;-)
Cell c = SheetDataOfTheFirstSheet.Elements<Row>().ElementAt(10).Elements<Cell>().ElementAt(cellIndex);
//as example take a look at the value of storedFormula
String storedFormula = c.CellFormula.Text;
String storedValue = c.CellValue.Text;
NumberFormatInfo provider = new NumberFormatInfo();
provider.NumberDecimalSeparator = ".";
provider.NumberGroupSeparator = ",";
provider.NumberGroupSizes = new Int32[] { 3 };
retVal = Convert.ToDouble(storedValue, provider);
return retVal;
}
}
}
这篇关于openxml sdk excel如何解析和计算公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
