public void ThenStartCellIsOneCommaOne()
{
ExcelCellAddress start = _excelParser.Worksheet.Dimension.Start;
Assert.Equal(1, start.Row);
Assert.Equal(1, start.Column);
}
/// <summary>
/// Adds a 'number' to the number cell at 'address' and saves the document
/// </summary>
public void AddNumberTo(ExcelCellAddress address, int number)
{
if (_currentSheet == null)
{
throw new CustomException("No sheet open!");
}
int numberInCell = 0;
//If the cell is empty set its value
if (_currentSheet.Cells[address.Row, address.Column].Value == null)
{
_currentSheet.SetValue(address.Address, number);
}
//if the cell already has a number add the value
else if (int.TryParse(_currentSheet.Cells[address.Row, address.Column].Value.ToString(), out numberInCell))
{
_currentSheet.SetValue(address.Address, number + numberInCell);
}
else
{
Console.WriteLine("Unable to change cell at:" + address.Address + " containing " + _currentSheet.GetValue(address.Row, address.Column));
return;
}
currModificationsToXlsx++;
Console.WriteLine("Cell[" + address.Address + "] = " + (number + numberInCell));
Save();
}
/// <summary>
/// Merge all cells into one in the range.
/// </summary>
/// <param name="range">Cells to merge</param>
public static void Merge(this ExcelRangeBase range)
{
ExcelCellAddress start = range.Start;
ExcelCellAddress end = range.End;
range.Worksheet.Cells[start.Row, start.Column, end.Row, end.Column].Merge = true;
}
/// <summary>
/// Dynamically append new entries to the current sheet
/// </summary>
public void AddItemEntryToCurrentSheet(DefinitionParserData.Item entry)
{
ExcelCellAddress current = new ExcelCellAddress(2, 1 + Program.gameRecognizer.enemyHandling.mobDrops.GetGroupNumberForEnemy(_currentSheet.Name, entry.group) * 4);
int maxDetph = 10;
//Add the group if it does't exist
if (!_currentSheet.Cells[current.Row, current.Column, current.Row, current.Column + 2].Merge)
{
_currentSheet.Cells[current.Row, current.Column, current.Row, current.Column + 2].Merge = true;
}
_currentSheet.Cells[current.Row, current.Column, current.Row, current.Column + 2].Value = entry.group;
_currentSheet.Cells[current.Row, current.Column, current.Row, current.Column + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//Find free spot in the group
while (_currentSheet.Cells[current.Address].Value != null)
{
current = new ExcelCellAddress(current.Row + 1, current.Column);
if (current.Row >= maxDetph)
{
current = new ExcelCellAddress(2, current.Column + 4);
}
}
//Insert
InsertValue(current, entry.mainPronounciation);
InsertValue(new ExcelCellAddress(current.Row, current.Column + 1), entry.yangValue);
InsertValue(new ExcelCellAddress(current.Row, current.Column + 2), 0);
sheetToAdresses[_currentSheet.Name].Add(entry.mainPronounciation, new ExcelCellAddress(current.Row, current.Column + 2));
Save();
}
public ExcelThreadedCommentThread(ExcelCellAddress cellAddress, XmlDocument commentsXml, ExcelWorksheet worksheet)
{
CellAddress = cellAddress;
ThreadedCommentsXml = commentsXml;
Worksheet = worksheet;
Comments = new ExcelThreadedCommentCollection(worksheet, commentsXml.SelectSingleNode("tc:ThreadedComments", worksheet.NameSpaceManager));
}
public static void ApplyVertical <T>(this ExcelWorksheet sheet, ExcelWorksheet templateSheet, IEnumerable <T> models, int startRowIndex = 0)
{
var type = typeof(T);
var properties = type.GetProperties();
foreach (var property in properties)
{
var address = GetExcelCellAddress(property);
if (string.IsNullOrEmpty(address))
{
continue;
}
var cellAddress = new ExcelCellAddress(address);
var rowIndex = startRowIndex == 0 ? cellAddress.Row : startRowIndex;
foreach (var model in models)
{
var value = property.GetValue(model);
var destinationCell = sheet.Cells[rowIndex, cellAddress.Column];
templateSheet.Cells[address].Copy(destinationCell);
destinationCell.Value = value;
rowIndex++;
}
}
}
public void ThenEndCellIsNotOneCommaOne()
{
ExcelCellAddress end = _excelParser.Worksheet.Dimension.End;
Assert.NotEqual(1, end.Row);
Assert.NotEqual(1, end.Column);
}
public Dicts InitializeMobSheet(string mobName, MobAsociatedDrops data)
{
Dicts d = new Dicts(true);
interaction.InsertValue(new ExcelCellAddress(1, 1), "Spreadsheet for enemy: " + interaction.currentSheet.Name);
interaction.InsertValue(new ExcelCellAddress(1, 4), "Num killed:");
interaction.InsertValue(new ExcelCellAddress(1, 5), 0);
Dictionary <string, string[]> itemEntries = Program.gameRecognizer.enemyHandling.mobDrops.GetDropsForMob(mobName);
ExcelCellAddress startAddr = new ExcelCellAddress("A2");
foreach (string key in itemEntries.Keys)
{
interaction.currentSheet.Cells[startAddr.Address].Value = key;
for (int i = 0; i < itemEntries[key].Length; i++)
{
int _offset = i + 1;
ExcelCellAddress itemName = new ExcelCellAddress(startAddr.Row + _offset, startAddr.Column);
ExcelCellAddress yangVal = new ExcelCellAddress(startAddr.Row + _offset, startAddr.Column + 1);
ExcelCellAddress totalDroped = new ExcelCellAddress(startAddr.Row + _offset, startAddr.Column + 2);
interaction.InsertValue(itemName, itemEntries[key][i]);
d.addresses.Add(itemEntries[key][i], totalDroped);
interaction.InsertValue(yangVal, DefinitionParser.instance.currentGrammarFile.GetYangValue(itemEntries[key][i]));
interaction.InsertValue(totalDroped, 0);
}
startAddr = new ExcelCellAddress(2, startAddr.Column + 4);
}
interaction.Save();
return(d);
}
public void Get_Excel_Address_From_String(string address, string expectedColumn, long expectedRow)
{
var excellAddress = new ExcelCellAddress(address);
Assert.Equal(ExcelCellAddress.GetColumnLetter(excellAddress.Column), expectedColumn);
Assert.Equal(excellAddress.Row, expectedRow);
}
public async Task <Dictionary <int, ExcelTerm> > GetTermsFromExcel(ExcelWorksheet worksheet)
{
var result = new Dictionary <int, ExcelTerm>();
try {
if (worksheet == null)
{
return(result);
}
await Task.Run(() =>
{
foreach (var cell in worksheet.Cells[worksheet.Dimension.Address])
{
var excellCellAddress = new ExcelCellAddress(cell.Address);
if (_providerSettings.HasHeader && excellCellAddress.Row == 1)
{
continue;
}
var id = excellCellAddress.Row;
if (!result.ContainsKey(id))
{
result[id] = new ExcelTerm();
}
SetCellValue(result[id], cell, excellCellAddress.Column);
}
});
}catch (Exception ex)
{
TelemetryService.Instance.AddException(ex);
}
return(result);
}
public async Task <Dictionary <int, ExcelTerm> > GetTermsFromExcel(ExcelWorksheet worksheet)
{
var result = new Dictionary <int, ExcelTerm>();
try {
if (worksheet == null)
{
return(result);
}
await Task.Run(() =>
{
foreach (var cell in worksheet.Cells[worksheet.Dimension.Address])
{
var excellCellAddress = new ExcelCellAddress(cell.Address);
if (_providerSettings.HasHeader && excellCellAddress.Row == 1)
{
continue;
}
var id = excellCellAddress.Row;
if (!result.ContainsKey(id))
{
result[id] = new ExcelTerm();
}
SetCellValue(result[id], cell, excellCellAddress.Column);
}
});
}
catch (Exception ex)
{
Log.Logger.Error($"GetTermsFromExcel method: {ex.Message}\n {ex.StackTrace}");
throw ex;
}
return(result);
}
public static void ReadExcelManual()
{
var excelFile = new FileInfo(Path.Combine(Directory.GetCurrentDirectory(), "xlsx\\sample.xlsx"));
using (var package = new ExcelPackage(excelFile))
{
var list = new List <SimpleModel>();
var sheet = package.Workbook.Worksheets[1];
// Fetch the WorkSheet size
ExcelCellAddress startCell = sheet.Dimension.Start;
ExcelCellAddress endCell = sheet.Dimension.End;
// cells
for (int row = startCell.Row + 1; row <= endCell.Row; row++)
{
var item = new SimpleModel();
item.col1 = Convert.ToInt32(sheet.Cells[row, 1].Value);
item.col2 = sheet.Cells[row, 2].Text;
item.col3 = sheet.Cells[row, 3].Text;
list.Add(item);
}
var options = new JsonSerializerOptions {
WriteIndented = true
};
Console.WriteLine(JsonSerializer.Serialize(new { cells = list }, options));
}
}
private void GenerateNumberColumn(ExcelWorksheet sheet, ExcelCellAddress cellAddress, int count)
{
for (int i = 0; i < count; i++)
{
sheet.Cells[cellAddress.Row + i, cellAddress.Column].Value = i + 1;
}
}
/// <summary>
/// Gets continuous range between two addressess
/// </summary>
public ExcelRange GetRangeContinuous(ExcelCellAddress addr1, ExcelCellAddress addr2)
{
main.currentSheet.Select(addr1.Address + ":" + addr2.Address);
ExcelRange range = main.currentSheet.SelectedRange;
main.currentSheet.Select("A1");
return(range);
}
IDictionary <string, IList <LocalizedStringValue> > ReadPlainExcelFileForJson(string filePath)
{
try
{
var dictionary = new Dictionary <string, IList <LocalizedStringValue> >();
using (var fileStream = new FileStream(filePath, FileMode.Open))
{
var excel = new ExcelPackage(fileStream);
var ws = excel.Workbook.Worksheets.FirstOrDefault(u => u.Name == "JSON");
// select the range that will be included in the table
if (ws == null)
{
throw new Exception("Worksheet with name JSON not found");
}
var dimension = ws.Dimension;
int realEndRow = 0;
while (!string.IsNullOrEmpty(ws.Cells[realEndRow + 1, 1].Text))
{
realEndRow++;
}
int realEndColumn = 0;
while (!string.IsNullOrEmpty(ws.Cells[1, realEndColumn + 1].Text))
{
realEndColumn++;
}
var start = dimension.Start;
var end = new ExcelCellAddress(realEndRow, realEndColumn);
for (int row = start.Row + 1; row <= end.Row; row++)
{
var key = ws.Cells[row, 1].Text;
if (dictionary.ContainsKey(key))
{
throw new Exception($"Table contains duplicated key with value={key}");
}
dictionary[key] = new List <LocalizedStringValue>();
for (int col = start.Column; col < end.Column; col++)
{
var cell = ws.Cells[row, col + 1];
dictionary[key].Add(new LocalizedStringValue
{
Key = key,
Culture = ws.Cells[1, col + 1].Text, //table.Columns[col].Name,
Value = cell.Text,
});
}
}
}
return(dictionary);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
private DataSet ParseExcel(ExcelPackage package)
{
if (package == null)
{
return(null);
}
try
{
var result = new DataSet();
foreach (var sheet in package.Workbook.Worksheets)
{
if ((sheet?.Dimension?.End?.Row ?? 0) <= 0)
{
continue;
}
if ((sheet?.Dimension?.End?.Column ?? 0) <= 0)
{
continue;
}
var table = new DataTable {
TableName = sheet.Name
};
var totalRows = Math.Min(sheet.Dimension.End.Row, MAX_PREVIEW_ROWS);
var totalColumns = Math.Min(sheet.Dimension.End.Column, Constants.MAX_COLUMNS);
ColumnsNames.Clear();
for (var columnIndex = sheet.Dimension.Start.Column; columnIndex <= totalColumns; columnIndex++)
{
var columnName = ExcelCellAddress.GetColumnLetter(columnIndex);
table.Columns.Add(columnName);
ColumnsNames.Add(columnName);
}
for (var rowIndex = sheet.Dimension.Start.Row; rowIndex <= totalRows; rowIndex++)
{
var row = table.Rows.Add();
for (var columnIndex = sheet.Dimension.Start.Column; columnIndex <= totalColumns; columnIndex++)
{
var columnName = ExcelCellAddress.GetColumnLetter(columnIndex);
row[columnName] = sheet.Cells[columnName + rowIndex].Value;
}
}
result.Tables.Add(table);
}
return(result);
}
catch (Exception ex)
{
ColumnsNames.Clear();
_eventAggregator.PublishOnUIThread(ex);
return(null);
}
}
public virtual bool BeforeRead(ExcelRange range, ExcelCellAddress end)
{
this.range = range;
endRow = end.Row;
iRow = StartRow - 1;
Msg = new MsgHtml();
Entities = new List <TEntity>(endRow - iRow);
return(true);
}
public Group(ExcelCellAddress groupName, ExcelCellAddress elementNameFirstIndex,
ExcelCellAddress yangValueFirstIndex, ExcelCellAddress totalCollectedFirstIndex)
{
this.groupName = groupName;
this.elementNameFirstIndex = elementNameFirstIndex;
this.yangValueFirstIndex = yangValueFirstIndex;
this.totalCollectedFirstIndex = totalCollectedFirstIndex;
this.totalEntries = 0;
}
private void GenerateDriversRow(ExcelWorksheet sheet, ExcelCellAddress cellAddress, IEnumerable <string> driverNames)
{
driverNames.ForEach(x =>
{
sheet.Cells[cellAddress.Address].Value = x;
sheet.Cells[cellAddress.Address].AutoFitColumns();
cellAddress = new ExcelCellAddress(cellAddress.Row, cellAddress.Column + 1);
});
}
public ExcelCellAddressInstance(ObjectInstance prototype, ExcelCellAddress excelCellAddress)
: this(prototype)
{
if (excelCellAddress == null)
{
throw new ArgumentNullException("excelCellAddress");
}
m_excelCellAddress = excelCellAddress;
}
public ValueElement(string token)
{
var separateIndex = token.IndexOf(',');
if (separateIndex > 0)
{
_templateAddress = new ExcelCellAddress(token.Substring(0, separateIndex));
_value = token.Substring(separateIndex + 1);
}
}
private static ExcelRange GenerateTableGrid <T>(ExcelWorksheet worksheet,
TgOptions <T> gridOptions, ExcelCellAddress topLeftCellAddress) where T : class
{
SetTableLocation(gridOptions, topLeftCellAddress);
var loaderFactory = new TableLoaderFactory();
var tableLoader = loaderFactory.Create(gridOptions, worksheet);
return(tableLoader.Load());
}
private void UpdateTable(ref ExcelTable table, ExcelCellAddress startAddress, ExcelCellAddress endAddress)
{
var newRange =
string.Format("{0}:{1}", startAddress.Address, endAddress.Address);
var tableElement = table.TableXml.DocumentElement;
tableElement.Attributes["ref"].Value = newRange;
tableElement["autoFilter"].Attributes["ref"].Value = newRange;
}
/// <summary>
/// Insert 'value' into cell at 'address' in current worksheet
/// </summary>
public void InsertValue <T>(ExcelCellAddress address, T value)
{
if (_currentSheet == null)
{
throw new CustomException("No sheet open!");
}
_currentSheet.SetValue(address.Address, value);
Console.WriteLine("Cell[" + address.Address + "] = " + value);
Save();
}
public FAJsonWriter(ExcelWorksheet ws)
{
this.ws = ws;
start = ws.Dimension.Start;
end = ws.Dimension.End;
fieldNames = new Dictionary <int, string>();
for (int x = start.Column; x <= end.Column; x++)
{
fieldNames.Add(x, Regex.Replace(GetCellStringValue(start.Row, x), @"\s+", ""));
}
}
/// <summary>
/// Increases number count to 'item' in current speadsheet
/// </summary>
public void ItemDropped(DefinitionParserData.Item item, int amount = 1)
{
if (!string.IsNullOrWhiteSpace(currentEnemy))
{
mobDrops.UpdateDrops(currentEnemy, item);
}
ExcelCellAddress address = Program.interaction.GetAddress(item.mainPronounciation);
Program.interaction.AddNumberTo(address, amount);
stack.Push(new ItemInsertion(address, amount));
}
private static void SetTableLocation <T>(TgOptions <T> gridOptions, ExcelCellAddress topLeftCellAddress)
where T : class
{
if (topLeftCellAddress == null)
{
topLeftCellAddress = new ExcelCellAddress(1, 1);
}
gridOptions.TableTopPosition = topLeftCellAddress.Row;
gridOptions.TableLeftPosition = topLeftCellAddress.Column;
}
/// <summary>
/// Averages cells in scattered range 'addresses' and puts result into 'result'
/// </summary>
public void Average(ExcelCellAddress result, ExcelCellAddress[] addresses)
{
string s = "";
foreach (ExcelCellAddress addr in addresses)
{
s = string.Join(",", s, addr.Address);
}
s = s.TrimStart(',');
main.currentSheet.Cells[result.Address].Formula = "AVERAGE(" + s + ")";
}
public override ExcelCellAddress GetDimensionEnd(string worksheet)
{
ExcelCellAddress address = null;
try
{
address = _package.Workbook.Worksheets[worksheet].Dimension.End;
}
catch { }
return(address);
}
private ExcelAddress GetMergedAddress(ExcelWorksheet templateSheet, ExcelCellAddress cellAddress)
{
foreach (string mergedCellAdress in templateSheet.MergedCells)
{
if (mergedCellAdress.Split(':')[0] == cellAddress.Address)
{
return(new ExcelAddress(mergedCellAdress));
}
}
return(null);
}
