public void SimpleFunction()
{
ExcelFormula excelFormula = new ExcelFormula("=SimpleFunction()");
Assert.AreEqual(excelFormula.Count, 2);
ExcelFormulaToken token = excelFormula[0];
Assert.IsTrue(string.Compare(token.Value, "SimpleFunction") == 0);
}
public void Test1()
{
ExcelFormula excelFormula =
new ExcelFormula(
@"=if(percentage>75,distinction,if(percentage>60,first,if(percentage>50,second,if(percentage>35,pass,fail))))");
Assert.IsTrue(excelFormula.Count > 0);
}
private static IEnumerable <IAddress> GetReferences(ExcelFormula <A1Style> formula, IAddress referencedFrom)
{
switch (formula.NodeType & FormulaNodeType.CategoryMask)
{
case FormulaNodeType.BinaryOperator:
switch (formula.NodeType)
{
case FormulaNodeType.RangeOperator:
case FormulaNodeType.UnionOperator:
case FormulaNodeType.IntersectOperator:
return(new[] { formula.ToAddress(referencedFrom) });
default:
return
(new[] { formula.BinaryOperator.FirstArgument, formula.BinaryOperator.SecondArgument }.SelectMany
(x => GetReferences(x, referencedFrom)));
}
case FormulaNodeType.UnaryOperator:
return(GetReferences(formula.UnaryOperator.Argument, referencedFrom));
case FormulaNodeType.FunctionCall:
return(formula.Function.Arguments.SelectMany(x => GetReferences(x, referencedFrom)));
case FormulaNodeType.Literal:
return(new IAddress[0]);
case FormulaNodeType.Reference:
return(new[] { formula.ToAddress(referencedFrom) });
default:
throw new ApplicationException("Unexpected node type");
}
}
public ExcelPrecedent(ExcelReference reference, ExcelFormula parentFormula, string parentFormulaSrc, ExcelReference parentReference)
{
reference_ = reference;
parentFormula_ = parentFormula;
parentFormulaSrc_ = parentFormulaSrc;
parentReference_ = parentReference;
}
public void Test1()
{
ExcelFormula excelFormula =
new ExcelFormula(
@"=if(percentage>75,distinction,if(percentage>60,first,if(percentage>50,second,if(percentage>35,pass,fail))))");
Assert.IsTrue(excelFormula.Count > 0);
}
public void SimpleFunction()
{
ExcelFormula excelFormula = new ExcelFormula("=SimpleFunction()");
Assert.AreEqual(excelFormula.Count, 2);
ExcelFormulaToken token = excelFormula[0];
Assert.IsTrue(string.Compare(token.Value, "SimpleFunction") == 0);
}
protected EvaluationResult Evaluate(string name, string cell, string expression, ExpressionScope scope, ExpressionFormat format)
{
if (!expression.StartsWith("="))
{
expression = "=" + expression;
}
var excelFormula = new ExcelFormula(expression);
var tokens = excelFormula.OfType <ExcelFormulaToken>();
return(Evaluate(name, cell, tokens, scope, format));
}
FormulaReturnValue EvaluateFormula(XSSFWorkbook workbook, ISheet sheet, ExcelFormula excelFormula)
{
m_WorkBook = workbook;
m_Sheet = sheet;
List <ExcelFormulaToken> tokens = new List <ExcelFormulaToken>();
foreach (ExcelFormulaToken token in excelFormula)
{
tokens.Add(token);
}
return(EvaluateFormulaFromTokens(sheet, tokens, string.Empty));
}
protected ExcelFormula GetTopParentFirstEntryFormulas(Guid uid)
{
ExcelFormula innerFormulasUnited = new ExcelFormula();
if (Parent != null)
{
return(Parent.GetTopParentFirstEntryFormulas(uid));
}
else
{
innerFormulasUnited.AddElementsRange(GetInnerFirstEntryFormulas(uid).Elements);
return(innerFormulasUnited);
}
}
public void RecalcCellReferences(SheetChange sheetChange)
{
foreach (var i in this.Cells())
{
var c = i.Value;
if (c.Formula != null)
{
c.Formula.Text = ExcelFormula.TranslateForSheetChange(c.Formula.Text, sheetChange, _wsheet.Name);
c.Formula.R1 = ExcelRange.TranslateForSheetChange(c.Formula.R1, sheetChange, _wsheet.Name);
c.Formula.R2 = ExcelRange.TranslateForSheetChange(c.Formula.R2, sheetChange, _wsheet.Name);
c.Formula.Reference = ExcelRange.TranslateForSheetChange(c.Formula.Reference, sheetChange, _wsheet.Name);
}
}
// Adjust conditional formatting
List <ConditionalFormatting> cfToRemoveList = new List <ConditionalFormatting>();
foreach (var cf in this.GetElements <ConditionalFormatting>())
{
bool removeCf = false;
List <StringValue> lst = new List <StringValue>();
foreach (var sqrefItem in cf.SequenceOfReferences.Items)
{
string newRef = ExcelRange.TranslateForSheetChange(sqrefItem.Value, sheetChange, _wsheet.Name);
if (!newRef.StartsWith("#")) // no error
{
lst.Add(new StringValue(newRef));
}
else
{
cfToRemoveList.Add(cf);
removeCf = true;
break;
}
}
if (removeCf)
{
break;
}
cf.SequenceOfReferences = new ListValue <StringValue>(lst);
foreach (var f in cf.Descendants <Formula>())
{
f.Text = ExcelFormula.TranslateForSheetChange(f.Text, sheetChange, _wsheet.Name);
}
}
foreach (ConditionalFormatting cf in cfToRemoveList)
{
this.RemoveElement(cf);
}
}
public void SetFormula(int row, int col, ExcelFormula formula, int floatDecimals)
{
if (formula != null && formula.Elements.Count > 0)
{
if (CurrentExportType == TExportExcelAdapterType.toXLS && formula.StringRepresentation().Length < 1024)
{
SetCellFloatValue(row, col, new TFormula(formula.StringRepresentation().Insert(0, "=")), floatDecimals);
}
else
{
SetCellFloatValue(row, col, formula.DoubleRepresentation(), floatDecimals);
}
}
}
//TODO: Похоже на проблему, дублирование функционала
protected ExcelFormula GetFirstLevelFormulas(Guid uid)
{
ExcelFormula innerFormulasUnited = new ExcelFormula();
if (InnerBlocks != null && InnerBlocks.Count > 0)
{
foreach (var innerBlock in InnerBlocks)
{
ExcelFormula innerFormula = innerBlock.GetFormula(uid);
innerFormulasUnited.AddElementsRange(innerFormula.Elements);
}
}
return(innerFormulasUnited);
}
public void RecalcCellReferences(SheetChange sheetChange)
{
foreach (KeyValuePair <ulong, CellProxy> pair in this.Cells())
{
CellProxy proxy = pair.Value;
if (proxy.Formula != null)
{
proxy.Formula.Text = ExcelFormula.TranslateForSheetChange(proxy.Formula.Text, sheetChange, this._wsheet.Name);
proxy.Formula.R1 = ExcelRange.TranslateForSheetChange(proxy.Formula.R1, sheetChange, this._wsheet.Name);
proxy.Formula.R2 = ExcelRange.TranslateForSheetChange(proxy.Formula.R2, sheetChange, this._wsheet.Name);
proxy.Formula.Reference = ExcelRange.TranslateForSheetChange(proxy.Formula.Reference, sheetChange, this._wsheet.Name);
}
}
List <ConditionalFormatting> list = new List <ConditionalFormatting>();
foreach (ConditionalFormatting formatting in this.GetElements <ConditionalFormatting>())
{
bool flag = false;
List <StringValue> list2 = new List <StringValue>();
foreach (StringValue value2 in formatting.SequenceOfReferences.Items)
{
string str = ExcelRange.TranslateForSheetChange(value2.Value, sheetChange, this._wsheet.Name);
if (!str.StartsWith("#"))
{
list2.Add(new StringValue(str));
}
else
{
list.Add(formatting);
flag = true;
break;
}
}
if (flag)
{
break;
}
formatting.SequenceOfReferences = new ListValue <StringValue>(list2);
foreach (Formula formula in formatting.Descendants <Formula>())
{
formula.Text = ExcelFormula.TranslateForSheetChange(formula.Text, sheetChange, this._wsheet.Name);
}
}
foreach (ConditionalFormatting formatting2 in list)
{
this.RemoveElement(formatting2);
}
}
public void AddCellToFormula(Guid formulaUid, int col, int row, double value, EnumExcelFormulaOperators oper)
{
ExcelFormula formula;
if (!formulaUidDict.ContainsKey(formulaUid))
{
formula = new ExcelFormula();
formulaUidDict.Add(formulaUid, formula);
}
else
{
formula = formulaUidDict[formulaUid];
}
formula.AddElement(new FormulaElement(col, row, value, oper));
}
public void FunctionWithParameters()
{
ExcelFormula excelFormula = new ExcelFormula("=Function(parameter,parameter1,10.0)");
Assert.AreEqual(excelFormula.Count, 7);
ExcelFormulaToken token = excelFormula[0];
Assert.IsTrue(string.Compare(token.Value, "Function") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Function));
token = excelFormula[1];
Assert.IsTrue(string.Compare(token.Value, "parameter") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Operand));
token = excelFormula[2];
Assert.IsTrue(string.Compare(token.Value, ",") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Argument));
token = excelFormula[3];
Assert.IsTrue(string.Compare(token.Value, "parameter1") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Operand));
token = excelFormula[5];
Assert.IsTrue(string.Compare(token.Value, "10.0") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Operand));
}
public void FunctionWithParameters()
{
ExcelFormula excelFormula = new ExcelFormula("=Function(parameter,parameter1,10.0)");
Assert.AreEqual(excelFormula.Count, 7);
ExcelFormulaToken token = excelFormula[0];
Assert.IsTrue(string.Compare(token.Value, "Function") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Function));
token = excelFormula[1];
Assert.IsTrue(string.Compare(token.Value, "parameter") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Operand));
token = excelFormula[2];
Assert.IsTrue(string.Compare(token.Value, ",") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Argument));
token = excelFormula[3];
Assert.IsTrue(string.Compare(token.Value, "parameter1") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Operand));
token = excelFormula[5];
Assert.IsTrue(string.Compare(token.Value, "10.0") == 0);
Assert.IsTrue(token.Type.Equals(ExcelFormulaTokenType.Operand));
}
public static FormulaReturnValue EvaluateCellFormula(XSSFWorkbook workbook, ICell cell)
{
if (cell.CellType != CellType.Formula)
{
return(null);
}
ISheet sheet = cell.Sheet;
string formula = "=" + cell.CellFormula;
ExcelFormula excelFormula = new ExcelFormula(formula);
List <ExcelFormulaToken> tokens = new List <ExcelFormulaToken>();
foreach (ExcelFormulaToken token in excelFormula)
{
//Console.WriteLine("Token type \"" + token.Type.ToString() + "\" value \"" + token.Value + "\"");
tokens.Add(token);
}
ExcelFormulaEvaluator formulaEvaluator = new ExcelFormulaEvaluator(workbook);
FormulaReturnValue retValue = formulaEvaluator.EvaluateFormulaFromTokens(sheet, tokens, string.Empty);
return(retValue);
}
// Сумируем все строковые виражения разделенные знаком & в ExcelFormula
private static void CompressFormulaToken(ExcelFormula excelFormula)
{
while (excelFormula.Where <ExcelFormulaToken>(t => t.Subtype == ExcelFormulaTokenSubtype.Concatenation && t.Value == "&").Count() > 0)
{
for (int i = 0; i < excelFormula.Count; i++)
{
ExcelFormulaToken token = excelFormula[i];
if (token.Value == "&")
{
string newTokenValue, firsOperand = "", lastOperand;
if (i > 1)
{
if (excelFormula[i - 2].Type == ExcelFormulaTokenType.OperatorPrefix)
{
firsOperand = excelFormula[i - 2].Value + excelFormula[i - 1].Value;
}
else
{
firsOperand = excelFormula[i - 1].Value;
}
}
else
{
firsOperand = excelFormula[i - 1].Value;
}
if (excelFormula[i + 1].Type == ExcelFormulaTokenType.OperatorPrefix)
{
lastOperand = excelFormula[i + 1].Value + excelFormula[i + 2].Value;
}
else
{
lastOperand = excelFormula[i + 1].Value;
}
newTokenValue = firsOperand + lastOperand;
if (excelFormula[i + 1].Type == ExcelFormulaTokenType.OperatorPrefix)
{
excelFormula.RemoveAt(i + 1);
excelFormula.RemoveAt(i + 1);
}
else
{
excelFormula.RemoveAt(i + 1);
}
if (i > 1)
{
if (excelFormula[i - 2].Type == ExcelFormulaTokenType.OperatorPrefix)
{
excelFormula.RemoveAt(i - 1);
i--;
}
}
excelFormula.RemoveAt(i - 1);
i--;
excelFormula[i].Value = newTokenValue;
excelFormula[i].Subtype = ExcelFormulaTokenSubtype.Text;
excelFormula[i].Type = ExcelFormulaTokenType.Operand;
break;
}
}
}
}
public void GenerateSheets()
{
int YEAR = 2017;
object[] headers =
{
@"Project/Activity", "Feature", "Employee", "Comment", "Business Line (BL)",
"Time(date)", "Location", "Work Units"
};
string PATH = @"c:\Maciek\Timesheets\2017";
int COL_FIRST = 1;
int COL_LAST = 8;
int ROW_FIRST = 1;
int ROW_DATA_FIRST = 2;
ExcelApp excel = new ExcelApp();
excel.CreateNewInstance();
for (int m = 12; m >= 1; m--)
{
IExcelWorkbook workbook = excel.CreateAndActivateNewWorkbook();
string workBookName = $"Protokol_MSzczudlo_{YEAR}_{m:D2}.xlsx";
string workBookPath = Path.Combine(PATH, workBookName);
int daysInMonth = DateTime.DaysInMonth(YEAR, m);
int ROW_LAST = daysInMonth + 1;
int ROW_SUM = ROW_LAST + 1;
ExcelSheetData sheetData = new ExcelSheetData();
ExcelRowData headerRowData = new ExcelRowData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowData.Values = headers.ToList();
sheetData.Rows.Add(headerRowData);
ExcelFormatData headerRowFormat = new ExcelFormatData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowFormat.Background = Color.LightGray;
headerRowFormat.IsFontBold = true;
headerRowFormat.HorizontalAlignment = ExcelHorizontalAlignmentType.Center;
sheetData.Formats.Add(headerRowFormat);
for (int d = 1; d <= daysInMonth; d++)
{
object[] values = new object[8];
int row = d + 1;
DateTime iDay = new DateTime(YEAR, m, d);
values[5] = iDay.ToShortDateString();
bool isFreeDay = iDay.DayOfWeek == DayOfWeek.Saturday || iDay.DayOfWeek == DayOfWeek.Sunday;
if (isFreeDay)
{
ExcelFormatData iFormatData = new ExcelFormatData(COL_FIRST, row, COL_LAST, row);
iFormatData.Background = Color.Salmon;
sheetData.Formats.Add(iFormatData);
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
else
{
values[0] = "IS Treasury";
values[2] = "Maciej Szczudło";
values[7] = 8;
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
ExcelBorderData borderData = new ExcelBorderData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_LAST);
borderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
borderData.Borders.AddBorder(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderData);
ExcelFormatData fontFormatData = new ExcelFormatData(borderData.Range);
fontFormatData.FontSize = 10;
sheetData.Formats.Add(fontFormatData);
}
string monthName = DateTimeTools.GetMonthName(m);
string sheetName = $"{monthName} {YEAR}";
IExcelSheet newSheet = workbook.CreateSheet(sheetName);
ExcelRangeInfo sumRange = new ExcelRangeInfo(ROW_DATA_FIRST, COL_LAST, ROW_LAST, COL_LAST);
ExcelFormula formula = ExcelFormulaHelper.GetSum(ROW_SUM, COL_LAST, sumRange);
sheetData.Formulas.Add(formula);
ExcelBorderData borderSumData = new ExcelBorderData(formula.Range);
borderSumData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderSumData);
newSheet.InsertSheetData(sheetData);
newSheet.SetColumnsAutoFit(COL_FIRST, COL_LAST);
newSheet.SetCellName(ROW_SUM, COL_LAST, "godziny");
excel.SetDisplayGridlines(false);
workbook.Save(workBookPath);
}
}
private void insertFormula(ExcelFormula formula)
{
Range range = GetRange(formula.Range);
range.FormulaLocal = formula.Expression;
}
public void CopyTo(uint targetRow, uint targetCol)
{
string newFormulaText = ExcelFormula.Translate(this.Text, (int)targetRow - (int)_row, (int)targetCol - (int)_col);
_wsheet.Cells[targetRow, targetCol].Formula.Text = newFormulaText;
}
public void FormulaNameWithSpace()
{
ExcelFormula excelFormula = new ExcelFormula("=FormulaWithSpace(parameter 1,parameter 2)");
Assert.IsNotNull(excelFormula);
}
public void SetFormulaStyled(int row, int col, ExcelFormula formula, TFlxFontStyles fs, int floatDecimals = 3)
{
SetFormula(row, col, formula, floatDecimals);
SetCellFontStyle(row, col, fs);
}
public void FormulaNameWithSpace()
{
ExcelFormula excelFormula = new ExcelFormula("=FormulaWithSpace(parameter 1,parameter 2)");
Assert.IsNotNull(excelFormula);
}
private static void ReplaceRefInFormulaToValue(ExcelFormula excelFormula, WorksheetPart workSheetPart, SpreadsheetDocument RecultDoc)
{
SharedStringTablePart shareStringTablePart = RecultDoc.WorkbookPart.SharedStringTablePart;
for (int i = 0; i < excelFormula.Count; i++)
{
ExcelFormulaToken token = excelFormula[i];
if (token.Subtype == ExcelFormulaTokenSubtype.Reference)
{
try
{
Cell fCell = FindCell(token.Value, workSheetPart);
if (fCell.CellValue != null)
{
if (fCell.DataType.Value == CellValues.SharedString)
{
int itemIndex = int.Parse(fCell.CellValue.Text);
if (shareStringTablePart != null)
{
SharedStringItem item = shareStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(itemIndex);
if (item != null)
{
token.Value = item.Text.Text;
}
}
}
else
{
token.Value = fCell.CellValue.Text;
}
}
else if (fCell.CellFormula != null)
{
token.Value = calcFormule(fCell, workSheetPart, RecultDoc, false);
}
else
{
token.Value = "";
}
double d;
if (double.TryParse(token.Value, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), out d))
{
token.Subtype = ExcelFormulaTokenSubtype.Number;
}
else
{
token.Subtype = ExcelFormulaTokenSubtype.Text;
}
}
catch (System.Exception ex)
{
;
}
finally
{
;
}
}
if (token.Subtype == ExcelFormulaTokenSubtype.Range)
{
try
{
string str = "";
foreach (Cell c in FindCells(token.Value, workSheetPart))
{
if (c.CellValue != null)
{
if (c.DataType.Value == CellValues.SharedString)
{
int itemIndex = int.Parse(c.CellValue.Text);
if (shareStringTablePart != null)
{
SharedStringItem item = shareStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(itemIndex);
if (item != null)
{
str += item.Text.Text + ",";
}
}
}
else
{
str += c.CellValue.Text + ",";
}
}
else if (c.CellFormula != null)
{
str += calcFormule(c, workSheetPart, RecultDoc, false) + ",";
}
}
token.Value = str.TrimEnd(',');
}
catch (System.Exception ex)
{
;
}
finally
{
;
}
}
}
}
private void InsertSingleReport(IExcelWorkbook workBook, TransactionReport report, IComparer <Transaction> transactionComparer)
{
ExcelSheetData sheetData = new ExcelSheetData();
TransactionManager transactionManager = new TransactionManager();
ColorConverter converter = new ColorConverter();
var firstTransaction = report.Transactions.First();
List <string> headers = Transaction.GetTransactionHeaders().ToList();
sheetData.Rows.Add(new ExcelRowData(COL_FIRST, ROW_HEADERS, headers.ToArray()));
int col_LAST = COL_FIRST + headers.Count - 1;
int col_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Category));
int col_AMOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Amount));
int col_ACCOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.AccountNumber));
int col_TRANSACTION_NUMBER = COL_FIRST +
headers.IndexOf(
ReflectionTools.GetName(() => firstTransaction.TransactionNumber));
int col_TRANSACTION_COMMENT = COL_FIRST + headers.IndexOf(
ReflectionTools.GetName(() => firstTransaction.Comment));
int iTransactionRow = 1;
foreach (var transaction in report.Transactions)
{
iTransactionRow++;
object[] values = ReflectionTools
.GetPropertyValues(transaction, headers).ToArray();
sheetData.Rows.Add(new ExcelRowData(COL_FIRST, iTransactionRow, values));
if (transaction.Color.IsNotNull())
{
ExcelRangeInfo iRange = new ExcelRangeInfo(iTransactionRow, COL_FIRST, iTransactionRow, col_LAST);
ExcelFormatData colorFormat = new ExcelFormatData(iRange);
colorFormat.Background = (Color)converter.ConvertFromString(transaction.Color);
sheetData.Formats.Add(colorFormat);
}
}
int row_LAST_TRANSACTION = ROW_FIRST_TRANSACTION + report.Transactions.Count - 1;
int row_SUBTOTAL = row_LAST_TRANSACTION + 1;
int row_SummaryTable = row_SUBTOTAL + 2;
iTransactionRow = row_SummaryTable;
Tree <string> categoryTree = GetCategoryTree(report.Transactions);
foreach (var categoryItem in categoryTree.Children)
{
string category = categoryItem.Value;
var subCategories = categoryItem.Children.Select(i => i.Value);
foreach (string subCategory in subCategories)
{
ExcelCellData categoryCell = new ExcelCellData(iTransactionRow, col_CATEGORY);
categoryCell.Value = category;
ExcelCellData subCategoryCell = new ExcelCellData(iTransactionRow, COLUMN_SUB_CATEGORY);
subCategoryCell.Value = subCategory;
}
}
ExcelRangeInfo transactionsRange =
new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_CATEGORY, row_LAST_TRANSACTION, col_AMOUNT);
ExcelRangeInfo subTotalRange = new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_AMOUNT, row_LAST_TRANSACTION, col_AMOUNT);
ExcelFormula subTotalFormula = ExcelFormulaHelper.GetSubTotalSum(row_SUBTOTAL, col_AMOUNT, subTotalRange);
sheetData.Formulas.Add(subTotalFormula);
ExcelBorderData subTotalBorderData = new ExcelBorderData(subTotalFormula.Range);
subTotalBorderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(subTotalBorderData);
ExcelBorderData borderTable = new ExcelBorderData(COL_FIRST, ROW_HEADERS, col_LAST, row_LAST_TRANSACTION);
borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlHairline, ExcelLineStyle.xlContinuous));
borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous));
sheetData.Borders.Add(borderTable);
ExcelRangeInfo columnAccountRange = ExcelRangeInfo.CreateColumnRange(col_ACCOUNT);
ExcelFormatData columnAccountFormat = new ExcelFormatData(columnAccountRange);
columnAccountFormat.NumberFormat = "@";
sheetData.Formats.Add(columnAccountFormat);
ExcelRangeInfo columnTransactionNumber = ExcelRangeInfo.CreateColumnRange(col_TRANSACTION_NUMBER);
ExcelFormatData columnTransactionFormat = new ExcelFormatData(columnTransactionNumber);
columnTransactionFormat.NumberFormat = "0";
sheetData.Formats.Add(columnTransactionFormat);
IExcelSheet sheet = workBook.CreateSheet(report.Name);
sheet.InsertSheetData(sheetData);
int columnCount = headers.Count;
sheet.SetColumnsAutoFit(1, columnCount);
sheet.SetAutoFilter(ROW_HEADERS, COL_FIRST, ROW_HEADERS, col_LAST);
}
public void CopyTo(uint targetRow, uint targetCol)
{
string str = ExcelFormula.Translate(this.Text, (int)(targetRow - this._row), (int)(targetCol - this._col));
this._wsheet.Cells[targetRow, targetCol].Formula.Text = str;
}
public static string calcFormule(Cell cell, WorksheetPart workSheetPart, SpreadsheetDocument RecultDoc, bool DelCellFormula)
{
if (cell.CellValue == null)
{
cell.Append(new CellValue {
Text = ""
});
}
ExcelFormula excelFormula = new ExcelFormula("=" + cell.CellFormula.Text); //Разбираем формулу на узлы
ReplaceRefInFormulaToValue(excelFormula, workSheetPart, RecultDoc); //Замена ссилок в формулах на их значения
CompressFormulaToken(excelFormula); // Сумируем все строковые виражения разделенные знаком & в ExcelFormula
//Собираем формулу в строковое выражение для расчета в NCalc
string fStr = "";
for (int i = 0; i < excelFormula.Count; i++)
{
ExcelFormulaToken token = excelFormula[i];
string tokenValue = token.Value;
if (token.Subtype == ExcelFormulaTokenSubtype.Start)
{
tokenValue += "(";
}
if (token.Subtype == ExcelFormulaTokenSubtype.Text)
{
tokenValue = "'" + tokenValue.Replace(@"\", "/") + "'";
}
if (token.Subtype == ExcelFormulaTokenSubtype.Concatenation && token.Value == "&")
{
tokenValue = "+";
}
if (token.Subtype == ExcelFormulaTokenSubtype.Number)
{
tokenValue = tokenValue.Replace(',', '.');
}
if (token.Subtype == ExcelFormulaTokenSubtype.Stop)
{
tokenValue = ")";
}
fStr += tokenValue;
}
if (excelFormula.Count == 1) // Если в формуле только один узел заменяем значение ячейки значением формулы
{
if (excelFormula[0].Subtype == ExcelFormulaTokenSubtype.Text)
{
cell.CellValue.Text = excelFormula[0].Value.Trim('\'');
if (cell.DataType != null)
{
cell.DataType.Value = CellValues.String;
}
if (DelCellFormula)
{
cell.CellFormula.Remove();
}
else
{
cell.CellFormula.CalculateCell = false;
}
}
if (excelFormula[0].Subtype == ExcelFormulaTokenSubtype.Number)
{
cell.CellValue.Text = excelFormula[0].Value;
if (cell.DataType != null)
{
cell.DataType.Value = CellValues.Number;
}
if (DelCellFormula)
{
cell.CellFormula.Remove();
}
else
{
cell.CellFormula.CalculateCell = false;
}
}
return(cell.CellValue.Text);
}
Expression Ev = new Expression(fStr, EvaluateOptions.IgnoreCase | EvaluateOptions.NoCache);
Ev.EvaluateFunction += EvaluateFunction1;
try
{
cell.CellValue.Text = Convert.ToString(Ev.Evaluate());
// if (cell.DataType != null)
// {
decimal r;
if (decimal.TryParse(cell.CellValue.Text, out r))
{
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue.Text = r.ToString("G", CultureInfo.InvariantCulture);
}
else
{
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
// }
if (DelCellFormula)
{
cell.CellFormula.Remove();
}
else
{
cell.CellFormula.CalculateCell = true;
}
}
catch (System.Exception ex)
{
// cell.DataType.Value = CellValues.String;
// cell.CellValue.Text = ex.Message + "[" + fStr + "]";
// AddComments(workSheetPart, cell.CellReference.Value, "Qw" /*ex.Message + "[" + fStr + "]"*/);
}
finally
{
;
}
return(cell.CellValue.Text);
}
// TODO Handle INDIRECT, etc.
// TODO Static cache (string-ExcelFormula).
// TODO Fix for R1C1 mode.
// TODO Fix external references.
private IEnumerable <ExcelPrecedent> GetPrecedents(ExcelReference reference)
{
List <ExcelPrecedent> items;
if (cache_.TryGetValue(reference, out items))
{
foreach (var item in items)
{
yield return(item);
foreach (var p in GetPrecedents(item.Reference))
{
yield return(p);
}
}
}
else
{
bool isFormula = (bool)XlCall.Excel(XlCall.xlfGetCell, 48, reference);
if (isFormula)
{
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 6, reference);
ExcelFormula excelFormula = new ExcelFormula(formula);
foreach (var token in excelFormula)
{
if (token.Type == ExcelFormulaTokenType.Operand &&
token.Subtype == ExcelFormulaTokenSubtype.Range)
{
//if (isRCmode)
//{
// var regex = new Regex(
// @"^=(?:(?<Sheet>[^!]+)!)?(?:R((?<RAbs>\d+)|(?<RRel>\[-?\d+\]))C((?<CAbs>\d+)|(?<CRel>\[-?\d+\]))){1,2}$",
// RegexOptions.Compiled | RegexOptions.IgnoreCase);
// if (regex.IsMatch(formula))
// {
// throw new NotSupportedException();
// }
//}
var range = token.Value;
Match rangeMatch = rangeRegex_.Match(range);
var col1 = ExcelAColumnToInt(rangeMatch.Groups["Col1"].Value) - 1;
var row1 = Int32.Parse(rangeMatch.Groups["Row1"].Value) - 1;
Group sheetGroup = rangeMatch.Groups["Sheet"];
var sheetName = sheetGroup.Success ? sheetGroup.Value : null;
int col2 = col1;
int row2 = row1;
if (rangeMatch.Groups["Col2"].Success)
{
col2 = ExcelAColumnToInt(rangeMatch.Groups["Col2"].Value) - 1;
row2 = Int32.Parse(rangeMatch.Groups["Row2"].Value) - 1;
}
for (int col = col1; col <= col2; col++)
{
for (int row = row1; row <= row2; row++)
{
ExcelReference precedantRef;
if (sheetName == null)
{
precedantRef = new ExcelReference(row, row, col, col, reference.SheetId);
}
else
{
precedantRef = new ExcelReference(row, row, col, col, sheetName);
}
ExcelPrecedent newPrecedent = new ExcelPrecedent(
precedantRef, excelFormula, formula, reference);
AddToCache(reference, newPrecedent);
yield return(newPrecedent);
foreach (var nestedPrecedant in GetPrecedents(precedantRef))
{
yield return(nestedPrecedant);
}
}
}
}
}
}
}
}
public void SetFormula(int row, int col, ExcelFormula formula)
{
SetFormula(row, col, formula, 3);
}
