private MoveData[] ScanRange(int groupBy) { Sheet.SuspendEvents(); IXLRangeRow lastRow = null; string prevVal = null; int groupStart = 0; List <MoveData> groups = new List <MoveData>(); using (var rows = _range.Rows()) { foreach (var row in rows) { lastRow = row; var val = row.Cell(groupBy).GetString(); var isSummaryRow = row.IsSummary(); if (string.IsNullOrEmpty(val) && !isSummaryRow) { if (groupStart > 0) { groups.Add(CreateMoveTask(groupBy, prevVal, _range.Cell(groupStart, 1), row.RowAbove().Unsubscribed().LastCell(), RangeType.DataRange)); } groups.Add(CreateMoveTask(groupBy, "", row.FirstCell(), row.LastCell(), RangeType.HeaderRow)); prevVal = null; groupStart = 0; continue; } if (val != prevVal) { if (groupStart > 0) { groups.Add(CreateMoveTask(groupBy, prevVal, _range.Cell(groupStart, 1), row.RowAbove().Unsubscribed().LastCell(), RangeType.DataRange)); } prevVal = val; groupStart = !isSummaryRow?row.RangeAddress.Relative(_range.RangeAddress).FirstAddress.RowNumber : 0; } if (isSummaryRow) { var moveData = new MoveData(row.RangeAddress, RangeType.SummaryRow, "", Sheet.Row(row.RowNumber()).Unsubscribed().OutlineLevel); moveData.PageBreak = Sheet.PageSetup.RowBreaks.Any(x => row.RowNumber() - (_summaryAbove ? 1 : 0) == x); groups.Add(moveData); } } if (lastRow != null && groupStart > 0) { using (var groupRng = _range.Range(_range.Cell(groupStart, 1), lastRow.LastCell())) groups.Add(new MoveData(groupRng.RangeAddress, RangeType.DataRange, prevVal, Sheet.Row(groupStart).Unsubscribed().OutlineLevel) { GroupColumn = groupBy }); } } Sheet.ResumeEvents(); return(groups.ToArray()); }
/// <summary> /// Row for table description /// </summary> /// <param name="worksheet">The worksheet</param> /// <param name="table">The table being printed</param> /// <param name="currentRow">The current row</param> /// <param name="lastColumn">The last column</param> private void CreateTableDescriptionRow(ref IXLWorksheet worksheet, ref TableInfo table, ref int currentRow, int lastColumn) { IXLRange row = CreateRow(ref worksheet, ref currentRow, lastColumn); row.Cell(1, 1).Value = "Table Description:"; row.Cell(1, 1).Style.Font.Bold = true; row.Cell(1, 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Cell(1, 1).Style.Fill.SetBackgroundColor(_headingColor); row.Cell(1, 2).Value = table.TableDescription; }
/// <summary> /// Create the table header /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="currentRow">The current row</param> private void CreateTableHeader(ref IXLWorksheet sheet, ref int currentRow) { IXLRange row = CreateRow(ref sheet, ref currentRow); int currentColumn = 1; row.Cell(1, currentColumn++).Value = "Table Name"; row.Cell(1, currentColumn++).Value = "Column Name"; row.Cell(1, currentColumn++).Value = "Description"; row.Cell(1, currentColumn++).Value = string.Empty; row.Cell(1, currentColumn++).Value = "SQL note find/replace double quote, added automatically"; }
private IAppActionResult <ReadModelForExcel> ParseLoadedData(IAppActionResult <ReadModelForExcel> result, IXLRange range, int rowCount, int colCount) { Regex rSepForFIO = new Regex(SeparatorFIO); StringBuilder value = new StringBuilder(); var position = "Position"; var employee = "Employee"; for (int i = 1; i < rowCount; ++i) { result.Data.Employees.Add(new Employee()); result.Data.Employees[result.Data.Employees.Count - 1].Id = Guid.NewGuid(); for (int j = 0; j < colCount; ++j) { var rowData = range.Cell(i + 1, j + 1).Value.ToString(); var columnName = range.Cell(1, j + 1).Value.ToString(); if (columnName == position) { if (!result.Data.Positions.Exists(d => d.Name == rowData)) { result.Data.Positions.Add(new Position { Id = Guid.NewGuid(), Name = rowData }); result.Data.Employees[result.Data.Employees.Count - 1].PositionId = result.Data.Positions[result.Data.Positions.Count - 1].Id; result.Data.Employees[result.Data.Employees.Count - 1].Position = new Position { Id = result.Data.Positions[result.Data.Positions.Count - 1].Id, Name = rowData }; } else { var pos = result.Data.Positions.Find(d => d.Name == rowData); result.Data.Employees[result.Data.Employees.Count - 1].PositionId = pos.Id; result.Data.Employees[result.Data.Employees.Count - 1].Position = new Position { Id = pos.Id, Name = pos.Name }; } } if (columnName == employee) { string[] dataName = rSepForFIO.Split(rowData); result.Data.Employees[result.Data.Employees.Count - 1].Surname = dataName[0]; result.Data.Employees[result.Data.Employees.Count - 1].FirstName = dataName[1]; result.Data.Employees[result.Data.Employees.Count - 1].Patronymic = dataName[2]; } } } return(result); }
private MoveData[] ScanRange(int groupBy) { IXLRangeRow lastRow = null; string prevVal = null; int groupStart = 0; List <MoveData> groups = new List <MoveData>(); var rows = _range.Rows(); foreach (var row in rows) { lastRow = row; var val = row.Cell(groupBy).GetString(); var isSummaryRow = row.IsSummary(); if (string.IsNullOrEmpty(val) && !isSummaryRow) { if (groupStart > 0) { groups.Add(CreateMoveTask(groupBy, prevVal, _range.Cell(groupStart, 1), row.RowAbove().LastCell(), RangeType.DataRange)); } groups.Add(CreateMoveTask(groupBy, "", row.FirstCell(), row.LastCell(), RangeType.HeaderRow)); prevVal = null; groupStart = 0; continue; } if (val != prevVal) { if (groupStart > 0) { groups.Add(CreateMoveTask(groupBy, prevVal, _range.Cell(groupStart, 1), row.RowAbove().LastCell(), RangeType.DataRange)); } prevVal = val; groupStart = !isSummaryRow?row.RangeAddress.Relative(_range.RangeAddress).FirstAddress.RowNumber : 0; } if (isSummaryRow) { var moveData = CreateMoveTask(groupBy, "", row.FirstCell(), row.LastCell(), RangeType.SummaryRow); moveData.PageBreak = Sheet.PageSetup.RowBreaks.Any(x => row.RowNumber() - (_summaryAbove ? 1 : 0) == x); groups.Add(moveData); } } if (lastRow != null && groupStart > 0) { groups.Add(CreateMoveTask(groupBy, prevVal, _range.Cell(groupStart, 1), lastRow.LastCell(), RangeType.DataRange)); } return(groups.ToArray()); }
/// <summary> /// Возвращает адресс нижней правой ячейки пары /// </summary> /// <param name="worksheet"></param> /// <param name="range"></param> /// <param name="column"></param> /// <returns></returns> private IXLRange SearchForLessonRange(IXLWorksheet worksheet, IXLRange range, int startRow, int startColumn) { var column = startColumn; var startCell = range.Cell(startRow, column).Address; var row = startRow; var lessonPrimaryBorderStyle = XLBorderStyleValues.Medium; var lessonSecondaryBorderStyle = XLBorderStyleValues.Thin; while ((range.Cell(row, column).Style.Border.RightBorder != lessonPrimaryBorderStyle) && (range.Cell(row, column + 1).Style.Border.RightBorder != lessonPrimaryBorderStyle) && (column < range.ColumnCount())) { column++; } while ((range.Cell(row, column).Style.Border.BottomBorder != lessonPrimaryBorderStyle) && (range.Cell(row, column).Style.Border.BottomBorder != lessonSecondaryBorderStyle) && (range.Cell(row + 1, column).Style.Border.TopBorder != lessonPrimaryBorderStyle) && (range.Cell(row + 1, column).Style.Border.TopBorder != lessonSecondaryBorderStyle) && (row < range.RowCount())) { row++; } return(worksheet.Range(startCell, range.Cell(row, column).Address)); //возвращает на 1 столбец меньше военки }
private float ResCalc(char dx, int order) { try { EnemyRES[order] = float.Parse(EnemyRange.Cell(CellLoc(dx, Enemy_Select.SelectedIndex + 2)).Value.ToString()); } catch (FormatException) { EnemyRES[order] = float.PositiveInfinity; DialogResult err; err = MessageBox.Show("表格中的某处数据出了问题。\n出问题的数据一般是无穷大,已自动设定。\n记得改一下数据表。", "提示"); } return(EnemyRES[order]); }
/// <summary> /// 武器相关查询 /// </summary> public string WeaponNameSelect() { init = true; var col = 1; try { WeaponRange = book.Worksheet("武器索引").RangeUsed(); switch (Weapon_Type.SelectedIndex) { case 0: col = 1; break; case 1: col = 5; break; case 2: col = 9; break; case 3: col = 13; break; case 4: col = 17; break; default: col = 1; break; } for (int i = 1; i <= WeaponRange.Column(col).CellsUsed().Count(); i++) { if (WeaponRange.Cell(i, col).Value.ToString() == Weapon_Type.SelectedItem.ToString()) { Weapon[i - 1] = WeaponRange.Cell(i, col + 1).Value.ToString(); } } if (Weapon_Choose.Items.Count == 0) { Weapon_Choose.Items.AddRange(Weapon); } return(WeaponRange.Cell(Weapon_Choose.SelectedIndex + 1, col + 2).Value.ToString());// Weapon_Choose.SelectedItem.ToString(); } catch (NullReferenceException) { DialogResult err; err = MessageBox.Show("未找到表格文件,或表格文件错误。\n请尝试打开“帮助”项中的部署工具进行修复。", "提示"); Close(); return(""); } catch (Exception) { return(Weapon[0].ToString()); } }
private void RenderSubrange(object item, FormulaEvaluator evaluator, TemplateCell cell, TagsList tags, ref int iCell, ref int row) { var start = _buff.NextAddress; // дочерний шаблон, к которому принадлежит ячейка var xlCell = _rowRange.Cell(cell.Row, cell.Column); var ownRng = _subranges.First(r => r._cells.Any(c => c.CellType != TemplateCellType.None && c.XLCell != null && Equals(c.XLCell.Address, xlCell.Address))); var formula = "{{" + ownRng.Source.ReplaceLast("_", ".") + "}}"; if (evaluator.Evaluate(formula, new Parameter(Name, item)) is IEnumerable value) { var valArr = value.Cast <object>().ToArray(); ownRng.Generate(valArr); if (ownRng.IsHorizontal) { iCell += ownRng._colCnt - 1; int shiftLen = ownRng._colCnt * (valArr.Length - 1); tags.Where(tag => tag.Cell.Row == cell.Row && tag.Cell.Column > cell.Column) .ForEach(t => { t.Cell.Column += shiftLen; t.Cell.XLCell = _rowRange.Cell(t.Cell.Row, t.Cell.Column); }); } else { row += ownRng._rowCnt - 1; while (_cells[iCell].Row <= row + 1) { iCell++; } int shiftLen = ownRng._rowCnt * (valArr.Length - 1); tags.Where(tag => tag.Cell.Row > cell.Row) .ForEach(t => { t.Cell.Row += shiftLen; t.Cell.XLCell = _rowRange.Cell(t.Cell.Row, t.Cell.Column); }); } } var rng = _buff.GetRange(start, _buff.PrevAddress); var rangeName = ownRng.Name; var dnr = rng.Worksheet.Workbook.NamedRange(rangeName); dnr.SetRefersTo(rng); }
/// <summary> /// Selects random answers from the given answer range, skipping the excludedIndex. /// </summary> /// <param name="answers">The range to select an answer from.</param> /// <param name="excludedIndex">The cell index to skip (the correct answer that should not be included in the random ones).</param> /// <param name="count">The count of answers to select</param> /// <returns>A list of answer strings.</returns> public static List <String> SelectRandomAnswers(IXLRange answers, int excludedIndex, int count) { var output = new List <string>(); var exclusions = new List <int>() { excludedIndex }; //Get range for random int min = answers.FirstCellUsed().Address.RowNumber; int max = answers.LastCellUsed().Address.RowNumber; //iterate until we have the desired amount of results while (output.Count < count) { int selectedIndex = Random.Next(min, max); //Skip the correct answer, and avoid duplicate random answers if (exclusions.Contains(selectedIndex)) { continue; } output.Add(answers.Cell(selectedIndex, 1).GetValue <string>()); exclusions.Add(selectedIndex); } return(output); }
private static string Cell(IXLRange range, int row, int column) { var cell = range.Cell(row, column); var value = cell.Value; if (value is bool) { return(XmlConvert.ToString((bool)cell.Value)); } if (cell.Value is DateTime) { var dateTime = XmlConvert.ToString((DateTime)cell.Value, XmlDateTimeSerializationMode.Utc); if (dateTime.EndsWith("T00:00:00Z", StringComparison.Ordinal)) { dateTime = dateTime.RemoveFromEnd("T00:00:00Z", StringComparison.Ordinal); } return(dateTime); } return(cell.GetFormattedString()); }
public void EnemyCheck() { EnemyRange = book.Worksheet("敌人属性").RangeUsed(); for (int i = 1; i < EnemyRange.RowCount(); i++) { Enemyname[i - 1] = EnemyRange.Cell(CellLoc('A', i + 1)).Value; } try { Enemy_Select.Items.AddRange(Enemyname); Confirm_Button.Enabled = true; } catch (ArgumentNullException) { Confirm_Button.Enabled = !(Enemy_Select.SelectedIndex < 0); } RESSelectorBox.Items.AddRange(new object[] { "风\t" + 0 + "%", "岩\t" + 0 + "%", "雷\t" + 0 + "%", "冰\t" + 0 + "%", "水\t" + 0 + "%", "火\t" + 0 + "%", "草\t" + 0 + "%", "物理\t" + 0 + "%", "对应属性\t" + 0 + "%" }); RESSelectorBox.SelectedIndex = 0; }
public void InsideBorderTest() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); IXLRange range = ws.Range("B2:D4"); SetupBorders(range); range.Style.Border.InsideBorder = XLBorderStyleValues.Thin; range.Style.Border.InsideBorderColor = XLColor.Red; IXLCell center = range.Cell(2, 2); Assert.AreEqual(XLColor.Red, center.Style.Border.TopBorderColor); Assert.AreEqual(XLColor.Red, center.Style.Border.BottomBorderColor); Assert.AreEqual(XLColor.Red, center.Style.Border.LeftBorderColor); Assert.AreEqual(XLColor.Red, center.Style.Border.RightBorderColor); Assert.AreEqual(XLBorderStyleValues.None, range.FirstRow().Cell(1).Style.Border.TopBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.FirstRow().Cell(2).Style.Border.TopBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.FirstRow().Cell(3).Style.Border.TopBorder); Assert.AreEqual(XLBorderStyleValues.None, range.LastRow().Cell(1).Style.Border.BottomBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.LastRow().Cell(2).Style.Border.BottomBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.LastRow().Cell(3).Style.Border.BottomBorder); Assert.AreEqual(XLBorderStyleValues.None, range.FirstColumn().Cell(1).Style.Border.LeftBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.FirstColumn().Cell(2).Style.Border.LeftBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.FirstColumn().Cell(3).Style.Border.LeftBorder); Assert.AreEqual(XLBorderStyleValues.None, range.LastColumn().Cell(1).Style.Border.RightBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.LastColumn().Cell(2).Style.Border.RightBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.LastColumn().Cell(3).Style.Border.RightBorder); }
public static object[] GetSheetIntoObjectUsingClosedXML(string fileDetails, string sheetName) { object[] main = null; using (XLWorkbook book = new XLWorkbook(fileDetails)) { IXLWorksheet xlSheet = book.Worksheet(sheetName); IXLRange xlRange = xlSheet.RangeUsed(); int rowCount = xlRange.RowCount(); int ColCount = xlRange.ColumnCount(); Console.WriteLine(rowCount); Console.WriteLine(ColCount); main = new object[rowCount - 1]; //number of rows (Test case) for (int i = 2; i <= rowCount; i++) { object[] temp = new object[ColCount]; //number of parameter for (int j = 1; j <= ColCount; j++) { string value = xlRange.Cell(i, j).GetString(); Console.WriteLine(value); temp[j - 1] = value; } main[i - 2] = temp; } book.Dispose(); } return(main); }
/// <summary> /// Create rows for missing column descriptions /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="currentRow">The current row</param> /// <param name="column">The column to print</param> private void CreateTableRowsForMissingColumnDescriptions(ref IXLWorksheet sheet, ref int currentRow, TableColumnInfo column) { if (!string.IsNullOrEmpty(column.ColumnDescription)) { return; } IXLRange row = CreateRow(ref sheet, ref currentRow); int currentColumn = 1; row.Cell(1, currentColumn++).Value = column.Table.TableName; row.Cell(1, currentColumn++).Value = column.ColumnName; row.Cell(1, currentColumn++).Value = column.ColumnDescription; row.Cell(1, currentColumn++).Value = string.Empty; string replacementFormula = "RC[1]"; replacementFormula = @"SUBSTITUTE(" + replacementFormula + @", ""{0}"", RC[-4])"; // Replaces {0} with table name replacementFormula = @"SUBSTITUTE(" + replacementFormula + @", ""{1}"", RC[-3])"; // Replaces {1} with columns name replacementFormula = "SUBSTITUTE(" + replacementFormula + @", ""{2}"", SUBSTITUTE(RC[-2], ""'"", ""''""))"; // Replaces {2} with description, also replaces "'" in description with "''" for SQL single quote escape row.Cell(1, currentColumn++).FormulaR1C1 = @"=IF(RC[-2] = """", ""Enter a Column description (Column C)"", " + replacementFormula + ")"; int sqlScriptColumn = currentColumn++; row.Cell(1, sqlScriptColumn).Value = Common.SqlQuery._SCRIPT_TEMPLATE_FOR_TABLE_COLUMN; row.Cell(1, sqlScriptColumn).Style.Font.SetFontColor(XLColor.White); }
/// <summary> /// Create rows for db objects /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="currentRow">The current row</param> /// <param name="obj">The object to print</param> private void CreateTableRowsForObject(ref IXLWorksheet sheet, ref int currentRow, StoredProcFuncInfo obj) { int startSectionRow = currentRow; IXLRange row = CreateRow(ref sheet, ref currentRow); int currentColumn = 1; row.Cell(1, currentColumn++).Value = obj.ObjectName; row.Cell(1, currentColumn++).Value = obj.ObjectType; foreach (StoredProcFuncInfoParams param in obj.Parameters) { CreateTableRowsForParameter(ref sheet, ref currentRow, param); } int endSectionRow = currentRow - 1; ApplyFormattingToSection(ref sheet, startSectionRow, endSectionRow); currentRow++; }
/// <summary> /// Create the table header /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="currentRow">The current row</param> private void CreateTableHeader(ref IXLWorksheet sheet, ref int currentRow) { IXLRange row = CreateRow(ref sheet, ref currentRow); int currentColumn = 1; row.Cell(1, currentColumn++).Value = "Object Name"; row.Cell(1, currentColumn++).Value = "Object Type"; row.Cell(1, currentColumn++).Value = "Parameter Name"; row.Cell(1, currentColumn++).Value = "Parameter sequence"; row.Cell(1, currentColumn++).Value = "Parameter Type"; row.Cell(1, currentColumn++).Value = "Parameter Max Length"; row.Cell(1, currentColumn++).Value = "Out parameter?"; }
/// <summary> /// Create row for parameter /// </summary> /// <param name="sheet">The sheet</param> /// <param name="currentRow">The current row</param> /// <param name="param">The parameter to print</param> private void CreateTableRowsForParameter(ref IXLWorksheet sheet, ref int currentRow, StoredProcFuncInfoParams param) { IXLRange row = CreateRow(ref sheet, ref currentRow); int currentColumn = 1; row.Cell(1, currentColumn++).Value = param.ParentObject.ObjectName; row.Cell(1, currentColumn++).Value = param.ParentObject.ObjectType; row.Cell(1, currentColumn++).Value = param.ParameterName; row.Cell(1, currentColumn++).Value = param.ParameterId; row.Cell(1, currentColumn++).Value = param.ParameterDataType; row.Cell(1, currentColumn++).Value = param.ParameterMaxLength; row.Cell(1, currentColumn++).Value = param.IsOutParameter; }
/// <summary> /// 角色相关查询 /// </summary> public void CharacterRangeSelect() { CharacterRange = book.Worksheet("角色索引").RangeUsed(); for (int i = 1; i < CharacterRange.ColumnCount(); i++) { Elements[i - 1] = CharacterRange.Cell(1, i).Value; } try { Character_Elem.Items.AddRange(Elements); } catch (ArgumentNullException) { } }
private void WeaponSelect() { var template = WeaponNameSelect(); WeaponLevel = book.Worksheet("武器模板").RangeUsed(); try { for (int i = 1; i <= WeaponLevel.Row(1).CellsUsed().Count(); i++) { if (template == WeaponLevel.Cell(1, i).Value.ToString()) { for (int k = 1; k <= 26; k++) { if (Weapon_Level.SelectedItem.ToString() == WeaponLevel.Cell(k, 1).Value.ToString()) { Weapon_Result.Text = WeaponLevel.Cell(k, i).Value.ToString(); } } } } } catch (NullReferenceException) { } }
private void SetColumnsWidth(IXLRange range, IList <ExcelDynamicColumn> columns) { for (int i = 0; i < columns.Count; i++) { ExcelDynamicColumn column = columns[i]; if (column.Width == null && !column.AdjustToContent) { continue; } if (Type == PanelType.Vertical) { IXLColumn excelColumn = range.Cell(1, i + 1).WorksheetColumn(); if (column.Width != null) { excelColumn.Width = column.Width.Value; } if (column.AdjustToContent) { excelColumn.AdjustToContents(); } } else { IXLRow excelRow = range.Cell(i + 1, 1).WorksheetRow(); if (column.Width != null) { excelRow.Height = column.Width.Value; } if (column.AdjustToContent) { excelRow.AdjustToContents(); } } } }
private void CharacterLevelSelect() { try { var nme = Character_Choose.SelectedItem.ToString(); NameOfCharacter = nme; CharacterLevel = book.Worksheet("角色等级").RangeUsed(); var lvl = Character_Level.Text; int r = 2, c = 2; if (Character_Check.Checked && (lvl == 20.ToString() || lvl == 40.ToString() || lvl == 50.ToString() || lvl == 60.ToString() || lvl == 70.ToString() || lvl == 80.ToString())) { lvl = Character_Level.Text + "+"; } for (int p = 1; p < CharacterLevel.ColumnsUsed().Count(); p++) { if (CharacterLevel.Cell(1, p).Value.ToString() == nme) { c = p; break; } } for (int i = 1; i < 100; i++) { if (CharacterLevel.Cell(i, 1).Value.ToString() == lvl) { r = i; Character_Result.Text = Convert.ToInt32(CharacterLevel.Cell(r, c).Value).ToString(); break; } } Character_Elem.Items.AddRange(Level); } catch (Exception) { } }
private void CharacterSelect() { var ElemRange = book.Worksheet("角色索引").Column(Character_Elem.SelectedIndex + 1).CellsUsed().Count(); try { for (int k = 1; k < ElemRange; k++) { Character[k - 1] = CharacterRange.Cell(k + 1, Character_Elem.SelectedIndex + 1).Value; } Character_Choose.Items.AddRange(Character); } catch (ArgumentNullException) { } }
/// <summary> /// Row for table last modified row /// </summary> /// <param name="worksheet">The worksheet</param> /// <param name="table">The table being printed</param> /// <param name="currentRow">The current row</param> /// <param name="lastColumn">The last column</param> private void CreateTableLastModifiedRow(ref IXLWorksheet worksheet, ref TableInfo table, ref int currentRow, int lastColumn) { IXLRange row = CreateRow(ref worksheet, ref currentRow, lastColumn); row.Cell(1, 1).Value = "Table Last Modified:"; row.Cell(1, 1).Style.Font.Bold = true; row.Cell(1, 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Cell(1, 1).Style.Fill.SetBackgroundColor(_headingColor); row.Cell(1, 2).Value = table.LastModified; row.Cell(1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; }
/// <summary> /// Create column row /// </summary> /// <param name="worksheet">The worksheet</param> /// <param name="column">The current DB table column</param> /// <param name="currentRow">The current row</param> /// <param name="lastColumn">The last column</param> private void CreateTableColumnRow(ref IXLWorksheet worksheet, TableColumnInfo column, ref int currentRow, int lastColumn) { IXLRange row = CreateRow(ref worksheet, ref currentRow, lastColumn); row.Style.Border.InsideBorder = XLBorderStyleValues.Thin; row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; int currentColumn = 1; row.Cell(1, currentColumn++).Value = column.ColumnName; row.Cell(1, currentColumn++).Value = column.ColumnDescription; row.Cell(1, currentColumn++).Value = column.ColumnDataType; row.Cell(1, currentColumn++).Value = column.AdditionalInfoFormatted; row.Cell(1, currentColumn++).Value = column.DefaultValue; row.Cell(1, currentColumn++).Value = column.AllowsNulls; row.Cell(1, currentColumn++).Value = column.PartOfKeyFormatted; }
public void TestCellsWithoutFormulas() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Test"); IXLRange range = ws.Range(1, 1, 10, 10); range.Cell(1, 1).Value = "Value"; range.Cell(2, 2).Value = "Value2"; range.Cell(2, 2).Active = true; range.Cell(3, 3).FormulaA1 = "=ROW()"; range.Cell(4, 4).FormulaR1C1 = "=COLUMN()"; range.Cell(5, 5).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; range.Cell(6, 6).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; range.Cell(6, 6).FormulaA1 = "=A1+B2"; Assert.AreEqual(100, range.Cells().Count()); Assert.AreEqual(97, range.CellsWithoutFormulas().Count()); Assert.AreEqual(3, range.Cells(c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count()); Assert.AreEqual(2, range.CellsWithoutFormulas(c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count()); }
/// <summary> /// Create heading row for columns table /// </summary> /// <param name="worksheet">The worksheet</param> /// <param name="currentRow">The current row</param> /// <param name="lastColumn">The last column</param> private void CreateTableColumnsHeadingRow(ref IXLWorksheet worksheet, ref int currentRow, int lastColumn) { IXLRange row = CreateRow(ref worksheet, ref currentRow, lastColumn); row.Style.Font.Bold = true; row.Style.Alignment.WrapText = true; row.Style.Border.InsideBorder = XLBorderStyleValues.Medium; row.Style.Border.OutsideBorder = XLBorderStyleValues.Medium; row.Style.Fill.SetBackgroundColor(_headingColor); int column = 1; row.Cell(1, column++).Value = "Column Name"; row.Cell(1, column++).Value = "Description"; row.Cell(1, column++).Value = "Type"; row.Cell(1, column++).Value = "Additional Type Information"; row.Cell(1, column++).Value = "Default Value"; row.Cell(1, column++).Value = "Allows Nulls?"; row.Cell(1, column++).Value = "Part of Key?"; }
/// <summary> /// Create the Row describing for a table missing its description /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="currentRow">The current row</param> /// <param name="table">The table to print</param> private void CreateTableRowForMissingDescription(ref IXLWorksheet sheet, ref int currentRow, TableInfo table) { IXLRange row = CreateRow(ref sheet, ref currentRow); int currentColumn = 1; row.Cell(1, currentColumn++).Value = table.TableName; row.Cell(1, currentColumn++).Value = string.Empty; row.Cell(1, currentColumn++).Value = table.TableDescription; row.Cell(1, currentColumn++).Value = string.Empty; string replacementFormula = "RC[1]"; replacementFormula = @"SUBSTITUTE(" + replacementFormula + @", ""{0}"", RC[-4])"; // Replaces {0} with table name replacementFormula = "SUBSTITUTE(" + replacementFormula + @", ""{1}"", SUBSTITUTE(RC[-2], ""'"", ""''""))"; // Replaces {1} with description, also replaces "'" in description with "''" for SQL single quote escape row.Cell(1, currentColumn++).FormulaR1C1 = @"=IF(RC[-2] = """", ""Enter a Table description (Column C)"", " + replacementFormula + ")"; int sqlScriptColumn = currentColumn++; row.Cell(1, sqlScriptColumn).Value = Common.SqlQuery._SCRIPT_TEMPLATE_FOR_TABLE; row.Cell(1, sqlScriptColumn).Style.Font.SetFontColor(XLColor.White); }
private void CreateExcel() { bool merge = false; var workbook = new XLWorkbook(); //Tworzenie arkuszy IXLWorksheet worksheetSummary = workbook.Worksheets.Add("Podsumowanie"); IXLRange summaryRange = worksheetSummary.Range(1, 1, Int16.MaxValue, 3); IXLRange summaryTitlesRange = summaryRange.Range(1, 1, 1, 3); summaryTitlesRange.Style.Font.Bold = true; summaryTitlesRange.Cell(1, 1).Value = "Tytuł"; summaryTitlesRange.Cell(1, 2).Value = "Wydawnictwo"; summaryTitlesRange.Cell(1, 3).Value = "Ilość sprzedanych sztuk"; summaryTitlesRange.RangeUsed().Style .Border.SetInsideBorder(XLBorderStyleValues.Thin) .Border.SetOutsideBorder(XLBorderStyleValues.Thin); IXLRange summaryDataRange = summaryRange.Range(2, 1, Int16.MaxValue, 3); for (int i = 0; i < raportElement.Count - 1; i++) { if (summaryDataRange.RowCount() > 0) { for (int j = 0; j < summaryDataRange.RowCount() - 1; j++) { if (summaryDataRange.Cell(j + 1, 1).Value.Equals(raportElement[i].Nazwa) && summaryDataRange.Cell(j + 1, 2).Value.Equals(raportElement[i].Wydawnictwo)) { int tmp = Convert.ToInt32(summaryDataRange.Cell(j + 1, 3).Value); tmp += raportElement[j].Ilosc; summaryDataRange.Cell(j + 1, 3).Value = tmp; merge = true; raportElement.RemoveAt(i); j = 1000; } } } if (!merge) { summaryDataRange.Cell(i + 1, 1).Value = raportElement[i].Nazwa; summaryDataRange.Cell(i + 1, 2).Value = raportElement[i].Wydawnictwo; summaryDataRange.Cell(i + 1, 3).Value = raportElement[i].Ilosc; } else { merge = !merge; } } summaryDataRange.Cell(raportElement.Count, 1).Value = raportElement[raportElement.Count - 1].Nazwa; summaryDataRange.Cell(raportElement.Count, 2).Value = raportElement[raportElement.Count - 1].Wydawnictwo; summaryDataRange.Cell(raportElement.Count, 3).Value = raportElement[raportElement.Count - 1].Ilosc; worksheetSummary.Columns().AdjustToContents(); using (var dlg = new SaveFileDialog()) { dlg.Filter = "(*.xlsx) Excel | *.xlsx"; dlg.AddExtension = true; dlg.ShowDialog(); try { if (!String.IsNullOrWhiteSpace(dlg.FileName)) { workbook.SaveAs(dlg.FileName); MessageBox.Show("Raport wygenerowany", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (IOException ex) { MessageBox.Show(ex.ToString()); } catch (Exception ex) { throw ex; } } }
private Lesson GetLessonData(IXLWorksheet worksheet, IXLRangeRow groupRow, IXLRange currentRange, int currentColumn, int day, int time, int checkedWeek) { var lessonRange = SearchForLessonRange(worksheet, currentRange, checkedWeek, currentColumn); var name = lessonRange.Cell(1, 1).Value.ToString(); string type; bool isLecture; try { isLecture = lessonRange.Cell(1, 1).Style.Fill.BackgroundColor.Color != XLColor.FromArgb(0, 255, 255, 255).Color; } catch { isLecture = true; } if (isLecture) { type = "Lecture"; } else { Regex regex = new Regex(@"лаб[.]*", RegexOptions.IgnoreCase); if (regex.IsMatch(name)) { name = regex.Replace(name, ""); type = "Laba"; } else { regex = new Regex(@"пр[.]*", RegexOptions.IgnoreCase); name = regex.Replace(name, ""); type = "Practice"; } } name = name.Trim(); var weekType = (lessonRange.RowCount() == 4) ? "Both" : (checkedWeek == 1) ? "Odd" : "Even"; //надо унифицировать для четных недель все var group = worksheet.Cell(groupRow.FirstCell().Address.RowNumber, currentRange.Cell(1, currentColumn).Address.ColumnNumber).Value.ToString(); var lessonInfo = ""; for (var i = 2; i <= lessonRange.RowCount(); i++) { for (var j = 1; j <= lessonRange.ColumnCount(); j++) { if (!lessonRange.Cell(i, j).IsEmpty()) { lessonInfo += lessonRange.Cell(i, j).Value.ToString() + " "; } } } var rangeOfLessonInfo = lessonInfo.Split(' '); var room = ""; var teacher = ""; var teacherIO = ""; foreach (string info in rangeOfLessonInfo) { if (info == "") { continue; } if ((info.ToLower().Contains("уит")) || (new Regex("[0-9]+").IsMatch(info))) //Доразобраться с регулярками { room = info; } else { if ((info.Contains('.')) && (info != "пр.") && (info != "лаб.")) { var ioRange = info.Split('.'); foreach (string io in ioRange) { if (io != "") { teacherIO += io + " "; } } continue; } teacher = info; } } return(new Lesson(((Time)time).ToString(), type, group, room, weekType, (DayOfWeek)(day + 1), teacher, name)); }