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); } } }