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