public void Fill(ExcelWorksheet worksheet, ExcelRangeBase departmentCell, ExcelRangeBase summaryCell)
{
Year = new Dictionary <string, YearQuarter>();
int rowYear = departmentCell.Start.Row;
int columnStart = departmentCell.Start.Column;
int columnEnd = summaryCell.Start.Column;
YearQuarter yearQuarter = null;
for (int i = columnStart + 1; i < columnEnd; i++)
{
var cell = worksheet.Cells[ExcelAddress.GetAddress(rowYear, i)];
if (cell.Value != null)
{
if (yearQuarter != null && yearQuarter.Quarter.Count > 0)
{
Year.Add(yearQuarter.Cell.Value.ToString(), yearQuarter);
}
yearQuarter = new YearQuarter();
yearQuarter.Cell = cell;
}
cell = worksheet.Cells[ExcelAddress.GetAddress(rowYear + 1, i)];
if (cell.Value != null)
{
yearQuarter.Quarter.Add(cell.Value.ToString(), cell);
}
}
if (yearQuarter != null && yearQuarter.Quarter.Count > 0)
{
Year.Add(yearQuarter.Cell.Value.ToString(), yearQuarter);
}
//debug();
}
public static void CheckExistedCellLocation(ExcelWorksheet worksheet, string location)
{
if (location != ExcelAddress.GetAddress(worksheet.Cells[location].Start.Row, worksheet.Cells[location].Start.Column))
{
throw new Exception("Некорректный адрес ячейки: " + location);
}
}
public static List <LinhaPlanilhaModel> LerArquivo(this ImportacaoController controller, DadosImportacaoVM model, ExcelWorksheet worksheet)
{
List <LinhaPlanilhaModel> linhasArquivoImportado = new List <LinhaPlanilhaModel>();
var colunas = System.Enum.GetValues(typeof(EnumColunasPlanilha)).Cast <int>().ToList();
for (var linha = LINHA_INICIO; linha <= worksheet.Dimension.Rows; linha++)
{
var celulas = new List <CelulaPlanilhaModel>();
colunas.ForEach(x =>
{
var celula = worksheet.Cells[linha, x].Value;
celulas.Add(new CelulaPlanilhaModel
{
Linha = linha,
Coluna = x,
ValorOriginal = celula,
Endereco = ExcelAddress.GetAddress(linha, x),
ValorManipulado = celula != null ? celula.ToString() : null
});
});
controller.ValidarCelulas(celulas);
var tipoErroLinhaPlanilha = controller.ValidarRegraCNPJouCPF(celulas);
linhasArquivoImportado.Add(new LinhaPlanilhaModel()
{
ExcelRow = worksheet.Row(linha),
Celulas = celulas,
Erro = tipoErroLinhaPlanilha
});
}
return(linhasArquivoImportado);
}
/// <summary>
/// This method follows the calculation chain to get the order of the calculation
/// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas).
/// </summary>
/// <param name="depChain">The dependency chain object</param>
/// <param name="lexer">The formula tokenizer</param>
/// <param name="wb">The workbook where the formula comes from</param>
/// <param name="ws">The worksheet where the formula comes from</param>
/// <param name="f">The cell function object</param>
/// <param name="options">Calcultaiton options</param>
private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
{
Stack <FormulaCell> stack = new Stack <FormulaCell>();
iterateToken:
while (f.tokenIx < f.Tokens.Count)
{
var t = f.Tokens[f.tokenIx];
if (t.TokenType == TokenType.ExcelAddress)
{
var adr = new ExcelFormulaAddress(t.Value);
if (adr.Table != null)
{
adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column));
}
if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column)) != ExcelAddressBase.eAddressCollition.No && !options.AllowCirculareReferences)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
}
if (adr._fromRow > 0 && adr._fromCol > 0)
{
if (string.IsNullOrEmpty(adr.WorkSheet))
{
if (f.ws == null)
{
f.ws = ws;
}
else if (f.ws.SheetID != f.SheetID)
{
f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
}
}
else
{
f.ws = wb.Worksheets[adr.WorkSheet];
}
if (f.ws != null)
{
f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
else if (t.TokenType == TokenType.NameValue)
{
string adrWb, adrWs, adrName;
ExcelNamedRange name;
ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws == null ? "" : f.ws.Name);
if (!string.IsNullOrEmpty(adrWs))
{
if (f.ws == null)
{
f.ws = wb.Worksheets[adrWs];
}
if (f.ws.Names.ContainsKey(t.Value))
{
name = f.ws.Names[adrName];
}
else if (wb.Names.ContainsKey(adrName))
{
name = wb.Names[adrName];
}
else
{
name = null;
}
if (name != null)
{
f.ws = name.Worksheet;
}
}
else if (wb.Names.ContainsKey(adrName))
{
name = wb.Names[t.Value];
f.ws = name.Worksheet;
}
else
{
name = null;
}
if (name != null)
{
if (string.IsNullOrEmpty(name.NameFormula))
{
if (name.NameValue == null)
{
f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, name.Start.Row,
name.Start.Column, name.End.Row, name.End.Column);
goto iterateCells;
}
}
else
{
var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var rf = new FormulaCell()
{
SheetID = name.LocalSheetId, Row = name.Index, Column = 0
};
rf.Formula = name.NameFormula;
rf.Tokens = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList();
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id && !options.AllowCirculareReferences)
{
throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name)));
}
}
}
}
}
}
}
f.tokenIx++;
}
depChain.CalcOrder.Add(f.Index);
if (stack.Count > 0)
{
f = stack.Pop();
goto iterateCells;
}
return;
iterateCells:
while (f.iterator != null && f.iterator.Next())
{
var v = f.iterator.Value;
if (v == null || v.ToString().Trim() == "")
{
continue;
}
var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
if (!depChain.index.ContainsKey(id))
{
var rf = new FormulaCell()
{
SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
};
if (f.iterator.Value is int)
{
rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
}
else
{
rf.Formula = v.ToString();
}
rf.ws = f.ws;
rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id)
{
if (options.AllowCirculareReferences == false)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
}
else
{
f = stack.Pop();
goto iterateCells;
}
}
}
}
}
}
f.tokenIx++;
goto iterateToken;
}
public void FillRange(ExcelWorksheet worksheet, int startRow, int startColumn, int endRow, int endColumn)
{
FillRange(worksheet, ExcelAddress.GetAddress(startRow, startColumn, endRow, endColumn));
}
public static void CopyDataFromAnotherWorkSheet(
int startRowFrom
, int startColumnFrom
, int endRowFrom
, int endColumnFrom
, int startRowTo
, int startColumnTo
, ExcelWorksheet worksheetFrom
, ExcelWorksheet worksheetTo
, bool byLinks
)
{
int differenceRowTo = startRowTo - startRowFrom;
int differenceColumnTo = startColumnTo - startColumnFrom;
for (int i = startRowFrom; i <= endRowFrom; i++)
{
for (int j = startColumnFrom; j <= endColumnFrom; j++)
{
if (byLinks)
{
worksheetTo.Cells[i + differenceRowTo, j + differenceColumnTo].Formula = "=" + worksheetFrom.Name + "!" + ExcelAddress.GetAddress(i, j);
}
else
{
worksheetTo.Cells[i + differenceRowTo, j + differenceColumnTo].Value = worksheetFrom.Cells[i, j].Value;
}
}
}
}
public static string GetLocationNewMoveCell(ExcelWorksheet worksheet, string cell, int rows, int columns)
{
return(ExcelAddress.GetAddress(worksheet.Cells[cell].Start.Row + rows, worksheet.Cells[cell].Start.Column + columns));
}
static void fillDepartmentBlock(
ExcelWorksheet worksheet
, string headerCellValue
, DataRow[] dataTableRows
, ExcelRangeBase summaryRow_Cell
, int summary_Cell_Start_Column
, int role_Cell_Start_Column
, int department_Cell_Start_Column
, ref int lastNonEmptyRowIndex
, ref ExcelRangeBase headerBlockCell
)
{
if (dataTableRows.Length > 0)
{
worksheet.InsertRow(lastNonEmptyRowIndex + 1, 1);
Helpers.CellStyleClass cellStyle = new Helpers.CellStyleClass();
cellStyle.SetPropertiesFromCell(summaryRow_Cell);
cellStyle.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
cellStyle.BorderLeftStyle = OfficeOpenXml.Style.ExcelBorderStyle.None;
cellStyle.BorderRightStyle = OfficeOpenXml.Style.ExcelBorderStyle.None;
cellStyle.FillRange(worksheet, ExcelAddress.GetAddress(lastNonEmptyRowIndex + 1, summaryRow_Cell.Start.Column, lastNonEmptyRowIndex + 1, summary_Cell_Start_Column));
worksheet.Row(lastNonEmptyRowIndex + 1).Height = worksheet.DefaultRowHeight;
worksheet.Cells[lastNonEmptyRowIndex + 1, summaryRow_Cell.Start.Column].Value = headerCellValue;
headerBlockCell = worksheet.Cells[lastNonEmptyRowIndex + 1, summaryRow_Cell.Start.Column];
lastNonEmptyRowIndex = headerBlockCell.Start.Row;
worksheet.InsertRow(lastNonEmptyRowIndex + 1, dataTableRows.Length);
foreach (DataRow row in dataTableRows) //заполняем заголовки обычных строк
{
lastNonEmptyRowIndex++;
worksheet.Cells[lastNonEmptyRowIndex, role_Cell_Start_Column].Value = row["Role_System_Specialization"].ToString();
worksheet.Cells[lastNonEmptyRowIndex, department_Cell_Start_Column].Value = row["Department_FullName"].ToString();
}
try
{
worksheet.Cells[ExcelAddress.GetAddress(headerBlockCell.Start.Row, summaryRow_Cell.Start.Column, headerBlockCell.Start.Row, summaryRow_Cell.Start.Column + 1)].Merge = true;
}
catch { }
cellStyle.FillBackgroundColor = System.Drawing.Color.Transparent;
cellStyle.FontColor = System.Drawing.Color.Black;
cellStyle.FontBold = false;
cellStyle.BorderLeftStyle = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
cellStyle.BorderRightStyle = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
cellStyle.BorderTopColor = System.Drawing.Color.LightGray;
cellStyle.BorderBottomColor = System.Drawing.Color.LightGray;
cellStyle.BorderLeftColor = System.Drawing.Color.LightGray;
cellStyle.BorderRightColor = System.Drawing.Color.LightGray;
cellStyle.FillRange(worksheet, ExcelAddress.GetAddress(headerBlockCell.Start.Row + 1, summaryRow_Cell.Start.Column, lastNonEmptyRowIndex, summaryRow_Cell.Start.Column + 1)); //роль и департамент
cellStyle.FillRange(worksheet, ExcelAddress.GetAddress(headerBlockCell.Start.Row + 1, summaryRow_Cell.Start.Column + 2, lastNonEmptyRowIndex, summary_Cell_Start_Column)); //аллокации
for (int i = headerBlockCell.Start.Column + 2; i < summary_Cell_Start_Column; i++) //обновляем формулы в блоке
{
worksheet.Cells[headerBlockCell.Start.Row, i].Formula = "=SUM(" + ExcelAddress.GetAddress(headerBlockCell.Start.Row + 1, i) + ":" + ExcelAddress.GetAddress(headerBlockCell.Start.Row + dataTableRows.Length, i) + ")";
}
}
}
static void writeWorksheet(ExcelWorksheet worksheet, int projectNumber, DataTable dataTable)
{
try
{
var department_Cell = Helpers.Excel.GetCellByValue(worksheet, Settings.SQLVariables.Department_Excel); //ячейка Подразделение
var summary_Cell = Helpers.Excel.GetCellByValue(worksheet, Settings.SQLVariables.LastColumnSummary_Excel); //ячейка ИТОГО Объем работ(ч/д)
var summaryRow_Cell = Helpers.Excel.GetCellByValue(worksheet, Settings.SQLVariables.SummaryRow_Excel); //ячейка "ИТОГО по всем внутренним ресурсам Банка: "
var role_Cell = Helpers.Excel.GetCellByValue(worksheet, Settings.SQLVariables.Role_Excel); //ячейка Роль\система\специализация
if ((worksheet.Dimension.Start.Row - 1 + worksheet.Dimension.Rows) > 0)
{
worksheet.DeleteRow(summaryRow_Cell.Start.Row + 1, (worksheet.Dimension.Start.Row - 1 + worksheet.Dimension.Rows) - summaryRow_Cell.Start.Row); //удаляем лишние строки
}
var distinctView = new DataView(dataTable);
distinctView.Sort = "Department_FullName ASC, Role_System_Specialization ASC";
var distinctRows = distinctView.ToTable(true, "TextIdentificator", "DepartmentBlockTypeName", "Role_System_Specialization", "Department_FullName"); //уникальные строки
var it_development_Table = distinctRows.Select(String.Format("DepartmentBlockTypeName = '{0}'", Settings.SQLVariables.It_development_SQL)); //разбиение на группы
var it_other_Table = distinctRows.Select(String.Format("DepartmentBlockTypeName = '{0}'", Settings.SQLVariables.It_other_SQL)); //разбиение на группы
var business_functionality_Table = distinctRows.Select(String.Format("DepartmentBlockTypeName = '{0}'", Settings.SQLVariables.Business_functionality_SQL)); //разбиение на группы
distinctRows = null;
distinctView = null;
ExcelRangeBase it_development_Cell = null; //ячейка Ресурсы ИТ-развития
ExcelRangeBase it_other_Cell = null; //ячейка Прочие ИТ-ресурсы
ExcelRangeBase business_functionality_Cell = null; //ячейка Бизнес- и функциональные подразделения
int lastNonEmptyRowIndex = summaryRow_Cell.Start.Row;
fillDepartmentBlock(worksheet ////Ресурсы ИТ-развития
, Settings.SQLVariables.It_development_Excel
, it_development_Table
, summaryRow_Cell
, summary_Cell.Start.Column
, role_Cell.Start.Column
, department_Cell.Start.Column
, ref lastNonEmptyRowIndex
, ref it_development_Cell);
fillDepartmentBlock(worksheet //Прочие ИТ-ресурсы
, Settings.SQLVariables.It_other_Excel
, it_other_Table
, summaryRow_Cell
, summary_Cell.Start.Column
, role_Cell.Start.Column
, department_Cell.Start.Column
, ref lastNonEmptyRowIndex
, ref it_other_Cell);
fillDepartmentBlock(worksheet //Бизнес- и функциональные подразделения
, Settings.SQLVariables.Business_functionality_Excel
, business_functionality_Table
, summaryRow_Cell
, summary_Cell.Start.Column
, role_Cell.Start.Column
, department_Cell.Start.Column
, ref lastNonEmptyRowIndex
, ref business_functionality_Cell);
for (int i = summaryRow_Cell.Start.Column + 2; i < summary_Cell.Start.Column; i++) //вставляем формулу в итоговой верхней строке
{
string formula = "=" + ((it_development_Cell == null) ? "" : ExcelAddress.GetAddress(it_development_Cell.Start.Row, i));
formula += ((formula == "" || it_other_Cell == null) ? "" : "+") + ((it_other_Cell == null) ? "" : ExcelAddress.GetAddress(it_other_Cell.Start.Row, i));
formula += ((formula == "" || business_functionality_Cell == null) ? "" : "+") + ((business_functionality_Cell == null) ? "" : ExcelAddress.GetAddress(business_functionality_Cell.Start.Row, i));
worksheet.Cells[summaryRow_Cell.Start.Row, i].Formula = formula;
}
for (int i = summary_Cell.Start.Row + 1; i < lastNonEmptyRowIndex + 1; i++) //вставляем формулу в итоговом столбце
{
worksheet.Cells[i, summary_Cell.Start.Column].Formula =
"=SUM(" + ExcelAddress.GetAddress(i, department_Cell.Start.Column + 1) + ":" + ExcelAddress.GetAddress(i, summary_Cell.Start.Column - 1) + ")";
}
var date = new Date(); //ячейки с данными годов и кварталов
date.Fill(worksheet, department_Cell, summary_Cell);
foreach (DataRow row in dataTable.Rows) //заполняем данные
{
int rowStart = 0;
int rowEnd = 0;
string departmentBlockTypeNameData = row["DepartmentBlockTypeName"].ToString();
if (departmentBlockTypeNameData == Settings.SQLVariables.It_development_SQL)
{
rowStart = it_development_Cell.Start.Row + 1;
rowEnd = it_development_Cell.Start.Row + it_development_Table.Length;
}
else if (departmentBlockTypeNameData == Settings.SQLVariables.It_other_SQL)
{
rowStart = it_other_Cell.Start.Row + 1;
rowEnd = it_other_Cell.Start.Row + it_other_Table.Length;
}
else if (departmentBlockTypeNameData == Settings.SQLVariables.Business_functionality_SQL)
{
rowStart = business_functionality_Cell.Start.Row + 1;
rowEnd = business_functionality_Cell.Start.Row + business_functionality_Table.Length;
}
else
{
throw new Exception("\nНеизвестный тип блока подразделения (DepartmentBlockTypeName): " + departmentBlockTypeNameData + ", Ресурс: " + row["Role_System_Specialization"].ToString() + "; " + row["Department_FullName"].ToString());
}
int rowRole = getRowResourse(worksheet, row["Role_System_Specialization"].ToString(), row["Department_FullName"].ToString(), rowStart, rowEnd, role_Cell.Start.Column, department_Cell.Start.Column);
if (rowRole == -1)
{
throw new Exception("\nНе найден ресурс " + row["Role_System_Specialization"].ToString() + " " + row["Department_FullName"].ToString());
}
try
{
int column = date.Year[row["ResourceAllocation_Year"].ToString()]
.Quarter[Settings.SQLVariables.QuarterPreText + row["ResourceAllocation_Quarter"].ToString()].Start.Column;
try //сможем ли преобразовать значение в double
{
worksheet.Cells[rowRole, column].Value = Convert.ToDouble(row["ResourceAllocation_Allocated"].ToString());
}
catch
{
worksheet.Cells[rowRole, column].Value = null;
}
}
catch
{
string quarter = " " + row["ResourceAllocation_Year"].ToString() + " кв. " + row["ResourceAllocation_Quarter"].ToString() + ";";
if (Settings.Variables.UserMessage == "")
{
Settings.Variables.UserMessage = "В файл не записаны кварталы:";
}
if (Settings.Variables.UserMessage.IndexOf(quarter) == -1)
{
Settings.Variables.UserMessage += quarter;
}
}
}
if (it_development_Cell != null) //заполням тире в пустые ячейки
{
Helpers.Excel.FillWorksheetEmptyValues(worksheet, it_development_Cell.Start.Row + 1, it_development_Cell.Start.Column + 2, it_development_Cell.Start.Row + it_development_Table.Length, summary_Cell.Start.Column - 1);
}
if (it_other_Cell != null)
{
Helpers.Excel.FillWorksheetEmptyValues(worksheet, it_other_Cell.Start.Row + 1, it_other_Cell.Start.Column + 2, it_other_Cell.Start.Row + it_other_Table.Length, summary_Cell.Start.Column - 1);
}
if (business_functionality_Cell != null)
{
Helpers.Excel.FillWorksheetEmptyValues(worksheet, business_functionality_Cell.Start.Row + 1, business_functionality_Cell.Start.Column + 2, business_functionality_Cell.Start.Row + business_functionality_Table.Length, summary_Cell.Start.Column - 1);
}
if (Helpers.Sugar.ConvertStringToBool(Settings.SQLVariables.ExceptionNoDateForFileQuarter))
{
foreach (var yearQuarter in date.Year)
{
foreach (var quarter in yearQuarter.Value.Quarter)
{
if (dataTable.Select(
String.Format("ResourceAllocation_Year = {0} AND ResourceAllocation_Quarter = {1}"
, yearQuarter.Value.Cell.Value.ToString()
, quarter.Key.ToString().Replace(Settings.SQLVariables.QuarterPreText, "")))
.Length == 0)
{
throw new Exception("\nВ базе нет данных по кварталу в файле: " + yearQuarter.Value.Cell.Value.ToString() + " кв. " + quarter.Key.ToString());
}
}
}
}
if (Helpers.Sugar.ConvertStringToBool(Settings.SQLVariables.IsAutoFitColumns))
{
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
}
}
catch (Exception exception)
{
exception.Message =
"\nПроект "
+ projectNumber.ToString()
+ ": ошибка в выходе excelPackage: "
+ exception.Message
;
}
}
/// <summary>
/// This method follows the calculation chain to get the order of the calculation
/// Goto (!) is used internally to prevent stackoverflow on extremly large dependency trees (that is, many recursive formulas).
/// </summary>
/// <param name="depChain">The dependency chain object</param>
/// <param name="lexer">The formula tokenizer</param>
/// <param name="wb">The workbook where the formula comes from</param>
/// <param name="ws">The worksheet where the formula comes from</param>
/// <param name="f">The cell function object</param>
/// <param name="options">Calcultaiton options</param>
private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
{
Stack <FormulaCell> stack = new Stack <FormulaCell>();
iterateToken:
while (f.tokenIx < f.Tokens.Count)
{
var t = f.Tokens[f.tokenIx];
if (t.TokenType == TokenType.ExcelAddress)
{
var adr = new ExcelFormulaAddress(t.Value);
if (adr.IsTableAddress)
{
adr.SetRCFromTable(ws.Package, new ExcelAddress(f.Row, f.Column, f.Row, f.Column));
}
if (adr.WorkSheet == null && adr.Collide(new ExcelAddress(f.Row, f.Column, f.Row, f.Column)) != ExcelAddress.eAddressCollition.No && !options.AllowCircularReferences)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddress.GetAddress(f.Row, f.Column))));
}
if (adr._fromRow > 0 && adr._fromCol > 0)
{
if (string.IsNullOrEmpty(adr.WorkSheet))
{
if (f.ws == null)
{
f.ws = ws;
}
else if (f.ws.SheetID != f.SheetID)
{
f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
}
}
else
{
f.ws = wb.Worksheets[adr.WorkSheet];
}
if (f.ws != null)
{
f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
else if (t.TokenType == TokenType.NameValue)
{
ExcelNamedRange name = null;
var worksheet = f.ws ?? ws;
// Worksheet-scoped named ranges take precedence over workbook-scoped named ranges.
if (worksheet?.Names?.ContainsKey(t.Value) == true)
{
name = worksheet.Names[t.Value];
}
else if (wb.Names.ContainsKey(t.Value))
{
name = wb.Names[t.Value];
}
if (name != null)
{
var nameFormulaTokens = name.GetRelativeNameFormula(f.Row, f.Column)?.ToList();
if (nameFormulaTokens.Count == 0 && !string.IsNullOrEmpty(name.NameFormula))
{
nameFormulaTokens = name.Workbook.FormulaParser.Lexer.Tokenize(name.NameFormula)?.ToList();
}
// Remove the current named range token and replace it with the named range's formula.
f.Tokens.RemoveAt(f.tokenIx);
f.Tokens.InsertRange(f.tokenIx, nameFormulaTokens);
goto iterateToken;
}
}
else if (t.TokenType == TokenType.Function && t.Value.IsEquivalentTo(Offset.Name))
{
var stringBuilder = new StringBuilder($"{OffsetAddress.Name}(");
int offsetStartIndex = f.tokenIx;
int parenCount = 1;
for (f.tokenIx += 2; parenCount > 0 && f.tokenIx < f.Tokens.Count; f.tokenIx++)
{
var token = f.Tokens[f.tokenIx];
stringBuilder.Append(token.Value);
if (token.TokenType == TokenType.OpeningParenthesis)
{
parenCount++;
}
else if (token.TokenType == TokenType.ClosingParenthesis)
{
parenCount--;
}
}
ExcelRange cell = ws.Cells[f.Row, f.Column];
string originalFormula = cell.Formula;
string addressOffsetFormula = stringBuilder.ToString();
stringBuilder.Clear();
for (int i = 0; i < f.Tokens.Count; i++)
{
if (i == offsetStartIndex)
{
stringBuilder.Append(0);
}
else if (i < offsetStartIndex || i >= f.tokenIx)
{
stringBuilder.Append(f.Tokens[i].Value);
}
}
cell.Formula = stringBuilder.ToString();
var offsetResult = ws.Calculate(addressOffsetFormula, f.Row, f.Column);
cell.Formula = originalFormula;
if (offsetResult is string resultString)
{
ExcelAddress adr = new ExcelAddress(resultString);
var worksheet = string.IsNullOrEmpty(adr.WorkSheet) ? ws : wb.Worksheets[adr.WorkSheet];
// Only complete the OFFSET's dependency chain if a valid existing address was successfully parsed.
if (worksheet != null)
{
f.Tokens.RemoveRange(offsetStartIndex, f.tokenIx - offsetStartIndex);
var offsetResultTokens = wb.FormulaParser.Lexer.Tokenize(adr.FullAddress);
f.Tokens.InsertRange(offsetStartIndex, offsetResultTokens);
f.ws = worksheet;
f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
f.tokenIx++;
}
depChain.CalcOrder.Add(f.Index);
if (stack.Count > 0)
{
f = stack.Pop();
goto iterateCells;
}
return;
iterateCells:
while (f.iterator != null && f.iterator.MoveNext())
{
var v = f.iterator.Value;
if (v == null || v.ToString().Trim() == "")
{
continue;
}
var id = ExcelAddress.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
if (!depChain.Index.ContainsKey(id))
{
var rf = new FormulaCell()
{
SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
};
if (f.iterator.Value is int)
{
rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
}
else
{
rf.Formula = v.ToString();
}
rf.ws = f.ws;
rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddress.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id ||
ExcelAddress.GetCellID(par.ws.SheetID, par.Row, par.Column) == id)
{
if (options.AllowCircularReferences == false)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
}
else
{
f = stack.Pop();
goto iterateCells;
}
}
}
}
}
}
f.tokenIx++;
goto iterateToken;
}
static string getXMLDataFromFile(string filePath, int projectNumber)
{
string resultXML = "";
ExcelPackage excelPackage = null;
var cellsDictionary = new System.Collections.Generic.Dictionary <string, Helpers.CellsDictionaryElement>(); //словарь элементов ячеек
int id = 0; //заполняем словарь ячеек - переводим таблицу в плоскую структуру
try
{
var dataSet = Helpers.Sugar.GetDataSetFromXML(Settings.SQLVariables.cells);
foreach (DataRow row in dataSet.Tables[0].Rows)
{
foreach (DataColumn column in dataSet.Tables[0].Columns)
{
cellsDictionary.Add(id.ToString(), new Helpers.CellsDictionaryElement()
{
Id = id, Location = row[column].ToString(), Value = ""
});
id++;
}
}
excelPackage = new ExcelPackage(new FileInfo(filePath));
var worksheet = Helpers.Excel.GetExcelWorksheetByName(excelPackage, Settings.SQLVariables.WorksheetName);
foreach (var cell in cellsDictionary)
{
try
{
if (cell.Value.Location != ExcelAddress.GetAddress(worksheet.Cells[cell.Value.Location].Start.Row, worksheet.Cells[cell.Value.Location].Start.Column))
{
throw new Exception();
}
else
{
cell.Value.Value = (worksheet.Cells[cell.Value.Location].Value ?? "").ToString();
}
}
catch
{
cell.Value.Value = cell.Value.Location; //если адрес ячейки найти невозможно, то её значение будет равно её адресу
}
}
id = 0; //заполняем датасет из плоского словаря ячеек
foreach (DataRow row in dataSet.Tables[0].Rows)
{
foreach (DataColumn column in dataSet.Tables[0].Columns)
{
row[column] = cellsDictionary[id.ToString()].Value;
id++;
}
}
resultXML = dataSet.GetXml();
if (resultXML == "")
{
throw new Exception("Не удалось собрать XML!");
}
}
catch (Exception exception)
{
exception.Message = "\nОшибка в общей информации бюджета проекта " + projectNumber.ToString() + ": " + exception.Message + Environment.NewLine;
}
finally
{
if (excelPackage != null)
{
excelPackage.Dispose();
}
}
return(resultXML);
}
internal void ConvertRichtext()
{
if (_cells == null)
{
return;
}
var isRt = _cells.Worksheet._flags.GetFlagValue(_cells._fromRow, _cells._fromCol, CellFlags.RichText);
if (Count == 1 && isRt == false)
{
_cells.Worksheet._flags.SetFlagValue(_cells._fromRow, _cells._fromCol, true, CellFlags.RichText);
var s = _cells.Worksheet.GetStyleInner(_cells._fromRow, _cells._fromCol);
//var fnt = cell.Style.Font;
var fnt = _cells.Worksheet.Workbook.Styles.GetStyleObject(s, _cells.Worksheet.PositionID, ExcelAddress.GetAddress(_cells._fromRow, _cells._fromCol)).Font;
this[0].PreserveSpace = true;
this[0].Bold = fnt.Bold;
this[0].FontName = fnt.Name;
this[0].Italic = fnt.Italic;
this[0].Size = fnt.Size;
this[0].UnderLine = fnt.UnderLine;
int hex;
if (fnt.Color.Rgb != "" && int.TryParse(fnt.Color.Rgb, NumberStyles.HexNumber, null, out hex))
{
this[0].Color = Color.FromArgb(hex);
}
}
}
