public IReadOnlyCollection <AssetState> ReadAssetStates(IXLWorkbook report)
{
var result = new List <AssetState>();
var header = report.Search("3.1 Движение по ценным бумагам инвестора").Single();
var nextHeader = report.Search("3.2 Движение по производным финансовым инструментам").Single();
var startRow = header.Address.RowNumber + 2;
var endRow = nextHeader.Address.RowNumber - 1;
var rows = report
.FindRows(r =>
(r.RowNumber() >= startRow) &&
(r.RowNumber() <= endRow))
.ToArray();
var labelRow = report.FindRows(r => r.RowNumber() == header.Address.RowNumber + 1).First();
var nameColumn = TableHelper.LookupColumnLetter(labelRow, "Сокращенное наименование актива");
var inCountColumn = TableHelper.LookupColumnLetter(labelRow, "Входящий остаток");
var incColumn = TableHelper.LookupColumnLetter(labelRow, "Зачисление");
var decColumn = TableHelper.LookupColumnLetter(labelRow, "Списание");
var outCountColumn = TableHelper.LookupColumnLetter(labelRow, "Исходящий остаток");
foreach (var row in rows)
{
var name = row.Cell(nameColumn).GetString().Trim();
var inCount = TryGetValue(row.Cell(inCountColumn));
var inc = TryGetValue(row.Cell(incColumn));
var dec = TryGetValue(row.Cell(decColumn));
var outCount = TryGetValue(row.Cell(outCountColumn));
result.Add(new(name, inCount, inc, dec, outCount));
}
return(result);
}
// ワークブックをCSVとして保存
public static List <string> SaveAsCsv(IXLWorkbook workbook, string path)
{
var list = new List <string>();
var taskList = new List <Task>();
foreach (var worksheet in workbook.Worksheets)
{
//worksheet.RangeUsed().DataType = XLDataType.Text;
string name = Path.Combine(path, worksheet.Name);
name += ".csv";
list.Add(worksheet.Name);
taskList.Add(
Task.Factory.StartNew(
() => { SaveAsCsv(worksheet, name); }
));
//SaveAsCsv(worksheet, name);
}
Task.WhenAll(taskList).Wait();
return(list);
}
private IXLWorksheet CreateStatsSheet <T>(IXLWorkbook wb, IEnumerable <T> results, double durationSla, double sizeSla)
{
IXLWorksheet wsStats = wb.Worksheets.Add("Perfx_Stats");
var stats = results.GetStats();
var statsDataTable = this.ToDataTable(stats.AsEnumerable());
var statsTable = wsStats.Cell(1, 1).InsertTable(statsDataTable, "Stats");
statsTable.Theme = XLTableTheme.TableStyleLight8;
var statsRowCount = (statsDataTable.Rows.Count + 1).ToString();
var durations = wsStats.Range("B2:I" + statsRowCount);
SetFormat(durations, durationSla);
var sizes = wsStats.Range("J2:K" + statsRowCount);
SetFormat(sizes, sizeSla);
wsStats.Range("L2:L" + statsRowCount).Style.Font.SetFontColor(XLColor.SeaGreen);
wsStats.Range("M2:M" + statsRowCount).Style.Font.SetFontColor(XLColor.OrangeRed);
try
{
wsStats.SheetView.Freeze(1, 1);
wsStats.Columns().AdjustToContents();
}
catch (Exception)
{
// Expected on Windows Server
}
return(wsStats);
}
private async Task Execute(IXLWorkbook xlsDocument, string ecfTableName, Func <XlsReader, EcfTableWriter, string[], Task <int> > action)
{
if (ShouldExportTable(ecfTableName, out var ecfFile))
{
// Report status
Console.WriteLine($"[Extracting] [{ecfTableName}] Start...");
// Create Excel Reader
var xlsReader = new XlsReader(xlsDocument, _config?.EcfExport?.XlsSheetName, _config?.EcfExport?.XlsFirstRowNumber, _config?.EcfExport?.XlsLastRowNumber);
// Generate ECF file name
var ecfFileName = Path.ChangeExtension(Path.Combine(_config.EcfExport.TargetFolderName, ecfTableName), "csv");
// Create ECF file for export
using var ecfWriterStream = new FileStream(ecfFileName, FileMode.Create, FileAccess.ReadWrite, FileShare.None);
// Create ECF Writer
using var ecfWriter = new CsvWriter(ecfWriterStream, Encoding.UTF8);
// Call table specific action
var ecfRecordCounter = await action(xlsReader, new EcfTableWriter(ecfWriter), ecfFile?.Headers);
// Inc counters
_recordCounter += ecfRecordCounter;
_tableCounter++;
// Report status
Console.WriteLine($"[Extracting] [{ecfTableName}] {ecfRecordCounter} record(s) extracted");
}
}
public IReadOnlyCollection <Asset> ReadAssets(IXLWorkbook report)
{
var result = new List <Asset>();
var tradesHeader = report.Search("4.1 Информация о ценных бумагах").Single();
var nextHeader = report.Search("4.2 Информация об инструментах, не квалифицированных в качестве ценной бумаги").Single();
var potentialLabelCell = report
.FindCells(c =>
(c.Address.ColumnLetter == "A") &&
(c.Address.RowNumber == tradesHeader.Address.RowNumber + 1))
.First();
var offset = !string.IsNullOrWhiteSpace(potentialLabelCell.GetString()) ? 1 : 2;
var startRow = tradesHeader.Address.RowNumber + offset + 1;
var endRow = nextHeader.Address.RowNumber - 1;
var rows = report.FindRows(r =>
(r.RowNumber() >= startRow) &&
(r.RowNumber() <= endRow));
var labelRow = report.FindRows(r => r.RowNumber() == tradesHeader.Address.RowNumber + offset).First();
var nameColumn = "A";
var isinColumn = TableHelper.LookupColumnLetter(labelRow, "ISIN");
var typeColumn = TableHelper.LookupColumnLetter(labelRow, "Тип");
foreach (var row in rows)
{
var name = row.Cell(nameColumn).GetString().Trim();
var isin = row.Cell(isinColumn).GetString().Trim();
var type = row.Cell(typeColumn).GetString().Trim();
var category = type.Contains("обл") ? "Bond" : "Share";
result.Add(new(isin, name, category));
}
return(result);
}
/// <summary>
/// Reads a stream and detects (if specified) the encoding, but falls back to the specified enconding if not detection failed.
/// </summary>
/// <param name="stream"> The stream to read. </param>
public XlsxStreamReader(Stream stream)
{
this.workbook = new XLWorkbook(stream);
this.sheet = workbook.Worksheet(1);
this.rows = sheet.RowsUsed().GetEnumerator();
this.EndOfStream = !this.rows.MoveNext();
}
internal void CreateWorksheet(IXLWorkbook workbook, string sheetName, IElement tableNode)
{
IXLWorksheet worksheet = workbook.Worksheets.Add(sheetName);
int row = 1;
int col = 1;
foreach (IElement rowNode in tableNode.QuerySelectorAll("tr"))
{
List <IElement> cells = rowNode.QuerySelectorAll("th").ToList();
cells.AddRange(rowNode.QuerySelectorAll("td"));
foreach (IElement cellNode in cells)
{
RenderCell(worksheet, cellNode, row, ref col);
}
col = 1;
row++;
}
if (!hasMergedCells)
{
var table = worksheet.RangeUsed().CreateTable("mainTable" + worksheet.Name);
table.Theme = XLTableTheme.TableStyleLight1;
table.ShowRowStripes = Settings.ShowRowStripes;
table.ShowAutoFilter = Settings.ShowFilter;
}
if (Settings.AutofitColumns)
{
worksheet.ColumnsUsed().AdjustToContents();
}
}
public void ApplyFormatToCell(IXLWorkbook wb, IXLCell cell, IFormat format)
{
if (format is ClosedXmlFormat closedXmlFormat)
{
closedXmlFormat.Stylize.Invoke(cell.Style);
}
}
private void PopulateDelegatesSheet(IXLWorkbook workbook, int centreId)
{
var delegateRecords = userDataService.GetDelegateUserCardsByCentreId(centreId);
var delegates = delegateRecords.OrderBy(x => x.LastName).Select(
x => new
{
x.LastName,
x.FirstName,
DelegateID = x.CandidateNumber,
AliasID = x.AliasId,
JobGroupID = x.JobGroupId,
x.Answer1,
x.Answer2,
x.Answer3,
x.Answer4,
x.Answer5,
x.Answer6,
x.Active,
x.EmailAddress,
HasPRN = GetHasPrnForDelegate(x.HasBeenPromptedForPrn, x.ProfessionalRegistrationNumber),
PRN = x.HasBeenPromptedForPrn ? x.ProfessionalRegistrationNumber : null,
}
);
ClosedXmlHelper.AddSheetToWorkbook(workbook, DelegatesSheetName, delegates, TableTheme);
}
private void AddData(IReadOnlyDayWorkLog Logs, IXLWorkbook workbook)
{
var worksheet = GetWorkSheet(Logs, workbook);
var row = GetRow(Logs, worksheet);
WritingRow(Logs, row);
}
private IXLWorksheet CreateWorkSheet(IXLWorkbook workbook)
{
var worksheet = workbook.AddWorksheet($"{DateTime.Today.ToString(Format.Month)}");
CreateTable(worksheet);
return(worksheet);
}
private static void AddSheetToWorkbook(IXLWorkbook workbook, string sheetName, IEnumerable <object>?dataObjects)
{
var sheet = workbook.Worksheets.Add(sheetName);
var table = sheet.Cell(1, 1).InsertTable(dataObjects);
table.Theme = XLTableTheme.TableStyleLight9;
sheet.Columns().AdjustToContents();
}
public IDictionary <ExcelDataType, object> ReadExcelNamedRange(IXLWorkbook workbook, string namedRange)
{
IXLNamedRange range = workbook.NamedRange(namedRange);
if (range == null)
{
throw new ClosedXMLReadException($"There is no named range called {namedRange} in this workbook");
}
int count = range.Ranges.First().Cells().Count();
//if solo then don't return collection.
if (count.Equals(1))
{
IXLCell cell = range.Ranges.First().Cells().First();
switch (cell.DataType)
{
case XLDataType.DateTime:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDateTime().ToOADate() }
});
case XLDataType.Number:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDouble() }
});
case XLDataType.Text:
string textValue = GetSingleCellTextValue(cell);
KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue);
switch (parsed.Key)
{
case ExcelDataType.Numeric:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, (double)parsed.Value }
});
case ExcelDataType.Text:
string[,] array = new string[1, 1];
array[0, 0] = textValue;
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Text, array }
});
default:
throw new NotImplementedException("I haven't implemented formulas yet");
}
}
}
IXLTable table = range.Ranges.First().AsTable();
return(ReadTable(table));
}
public IReadOnlyCollection <Trade> ReadTrades(IXLWorkbook report, IReadOnlyCollection <Asset> assets)
{
var result = new List <Trade>();
var tradesHeader = report.Search("1.1 Информация о совершенных и исполненных сделках на конец отчетного периода").Single();
var nextHeader = report.Search("1.2 Информация о неисполненных сделках на конец отчетного периода").Single();
var startRow = tradesHeader.Address.RowNumber + 2;
var endRow = nextHeader.Address.RowNumber - 1;
var rows = report.FindRows(r =>
(r.RowNumber() >= startRow) &&
(r.RowNumber() <= endRow));
var labelRow = report.FindRows(r => r.RowNumber() == tradesHeader.Address.RowNumber + 1).First();
var dateColumn = TableHelper.LookupColumnLetter(labelRow, "Дата заключения");
var timeColumn = TableHelper.LookupColumnLetter(labelRow, "Время");
var typeColumn = TableHelper.LookupColumnLetter(labelRow, "Вид сделки");
var nameColumn = TableHelper.LookupColumnLetter(labelRow, "Сокращенное наименование актива");
var currencyColumn = TableHelper.LookupColumnLetter(labelRow, "Валюта цены");
var countColumn = TableHelper.LookupColumnLetter(labelRow, "Количество");
var sumColumn = TableHelper.LookupColumnLetter(labelRow, "Сумма сделки");
var feeColumn = TableHelper.LookupColumnLetter(labelRow, "Комиссия брокера");
foreach (var row in rows)
{
var dateCell = row.Cell(dateColumn);
if (IsPageSeparator(dateCell.GetString()))
{
continue;
}
// We expect that it's Moscow time, but no timezone provided
// and for backward-compatibility we should use fixed value
var dateDt = dateCell.GetDateTimeExact("dd.MM.yyyy", "MM/dd/yyyy hh:mm:ss");
var date = new DateTimeOffset(dateDt, TimeSpan.FromHours(3));
var timeDt = row.Cell(timeColumn).GetDateTime();
var time = new DateTimeOffset(timeDt, TimeSpan.FromHours(3));
var fullDate = new DateTimeOffset(date.Year, date.Month, date.Day, time.Hour, time.Minute, time.Second, time.Offset);
var type = row.Cell(typeColumn).GetString().Trim();
if (IsTemporaryType(type))
{
continue;
}
var buy = (type == "Покупка");
var name = row.Cell(nameColumn).GetString().Trim();
// Skip money transfer deals
if (name.EndsWith("_TOM"))
{
continue;
}
var isin = assets.Single(a => a.Name == name).Isin;
var count = (int)row.Cell(countColumn).GetDouble();
count = buy ? count : -count;
var currency = row.Cell(currencyColumn).GetString().Trim();
var sum = row.Cell(sumColumn).GetDecimal();
var brokerFee = row.Cell(feeColumn).GetDecimal();
var fee = brokerFee;
result.Add(new(fullDate, isin, name, count, currency, sum, fee));
}
return(result);
}
/// <summary>
/// Creates a new <see cref="SpreadsheetReader"/>.
/// </summary>
/// <param name="stream">The underlying stream to read from.</param>
public SpreadsheetReader(Stream stream)
{
_stream = stream ?? throw new ArgumentNullException(nameof(stream));
// Open document
_workbook = new XLWorkbook(stream);
_worksheet = _workbook.Worksheets.First();
_currentRow = _worksheet.FirstRowUsed();
}
private void PopulateJobGroupsSheet(IXLWorkbook workbook)
{
var jobGroups = jobGroupsDataService.GetJobGroupsAlphabetical()
.OrderBy(x => x.id)
.Select(
item => new { JobGroupID = item.id, JobGroupName = item.name }
);
ClosedXmlHelper.AddSheetToWorkbook(workbook, JobGroupsSheetName, jobGroups, TableTheme);
}
private IXLWorksheet GetWorkSheet(IReadOnlyDayWorkLog logs, IXLWorkbook workbook)
{
var ThisMonthworksheet = workbook.Worksheets.FirstOrDefault(w => w.Name == logs.GetStartTime()?.ToString(Format.Month));
if (ThisMonthworksheet != null)
{
return(ThisMonthworksheet);
}
return(CreateWorkSheet(workbook));
}
public ImportManagerBuilder <T> OpenFile(MemoryStream stream, string worksheetName)
{
Workbook = new XLWorkbook(stream);
Workbook.TryGetWorksheet(worksheetName, out var worksheet);
ActiveWorksheet = worksheet ?? throw new InvalidWorksheetNameException();
return(this);
}
/// <summary>
/// Read Table from Excel Sheet into a memory DataTable
/// </summary>
/// <param name="wb">ClosedXml WorkBook instance</param>
/// <param name="sheetNumber">Workbook sheet number to read</param>
/// <param name="options">Read options <see cref="ReadOptions"/></param>
/// <returns></returns>
public static DataTable ReadTable(this IXLWorkbook wb, int sheetNumber, ReadOptions options = null)
{
if (sheetNumber <= 0 || sheetNumber > wb.Worksheets.Count)
{
throw new IndexOutOfRangeException($"{nameof(sheetNumber)} is Out of Range");
}
var ws = wb.Worksheet(sheetNumber);
return(ReadExcelSheet(ws, options));
}
private static IXLWorksheet DetectWorkSheet(string fileName, IXLWorkbook wb)
{
foreach (var ws in wb.Worksheets)
{
if (ws.Name.Equals(fileName))
{
return(ws);
}
}
return(null);
}
private static void FormatAllDelegateWorksheetColumns(IXLWorkbook workbook, DataTable dataTable)
{
ClosedXmlHelper.FormatWorksheetColumn(workbook, dataTable, RegisteredDate, XLDataType.DateTime);
var boolColumns = new[] { PasswordSet, Active, Approved, IsAdmin };
foreach (var columnName in boolColumns)
{
ClosedXmlHelper.FormatWorksheetColumn(workbook, dataTable, columnName, XLDataType.Boolean);
}
}
public static void WritePlayerSheet <T>(IXLWorkbook workbook, List <T> players) where T : Player
{
playerMapper.TryGetValue(typeof(T), out string sheetName);
var worksheet = workbook.AddWorksheet(sheetName);
WriteHeaders <T>(worksheet);
for (int i = 0; i < players.Count; i++)
{
WritePlayer(worksheet, i + 4, players[i]);
}
Console.WriteLine($"Wrote Excel sheet {typeof(T)}.");
}
private FileContentResult CreateFileWithContent(IXLWorkbook workbook)
{
using(var stream = new MemoryStream())
{
workbook.SaveAs(stream);
stream.Flush();
return new FileContentResult(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = $"agencies_{DateTime.UtcNow.ToShortDateString()}.xlsx"
};
}
}
public IDictionary <ExcelDataType, object> ReadNamedRangeOrTable(IXLWorkbook workbook,
string namedRangeOrTableName)
{
try
{
return(ReadExcelNamedRange(workbook, namedRangeOrTableName));
}
catch (ClosedXMLReadException e)
{
return(ReadTable(workbook, namedRangeOrTableName));
}
}
public static void FormatWorksheetColumn(
IXLWorkbook workbook,
DataTable dataTable,
string columnName,
XLDataType dataType
)
{
var columnNumber = dataTable.Columns.IndexOf(columnName) + 1;
workbook.Worksheet(1).Column(columnNumber).CellsUsed(c => c.Address.RowNumber != 1)
.SetDataType(dataType);
}
public void ApplyFormatToCell(IXLWorkbook wb, RowDefinition defaultRowDef, RowDefinition rowDef, int columnIndex, IXLCell cell)
{
var formats = FormatMerger.GetFormatsOrderedByLessPrioritary <ClosedXmlFormat>(defaultRowDef, rowDef, columnIndex);
formats.Reverse();
Func <IXLStyle, IXLStyle> f = (s) => null;
var composedStylize = formats.Aggregate(f, (mergedFormat, nextFormat) =>
{
return(mergedFormat.Compose(nextFormat.Stylize));
});
ApplyFormatToCell(wb, cell, new ClosedXmlFormat(composedStylize));
}
/// <summary>
/// Read All Data from Excel insto DataSet. Will be create a DataTable by WorkbookSheet
/// </summary>
/// <param name="wb">ClosedXml WorkBook instance</param>
/// <param name="options">Read options <see cref="ReadOptions"/></param>
/// <returns></returns>
public static DataSet ReadTables(this IXLWorkbook wb, ReadOptions options = null)
{
DataSet ds = new DataSet();
foreach (var workBookWorksheet in wb.Worksheets)
{
var dt = ReadExcelSheet(workBookWorksheet, options);
ds.Tables.Add(dt);
}
return(ds);
}
/// <summary>
/// Read Table from Excel Sheet into a typed IEnumerable Collection
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="wb">ClosedXml WorkBook instance</param>
/// <param name="sheetNumber">Workbook sheet number to read</param>
/// <param name="options">Read options <see cref="ReadOptions"/></param>
/// <returns></returns>
public static IEnumerable <T> ReadTable <T>(this IXLWorkbook wb, int sheetNumber, ReadOptions options = null) where T : class, new()
{
if (sheetNumber <= 0 || sheetNumber > wb.Worksheets.Count)
{
throw new IndexOutOfRangeException($"{nameof(sheetNumber)} is Out of Range");
}
var ws = wb.Worksheet(sheetNumber);
var dt = ReadExcelSheet(ws, options);
return(dt.AsEnumerableTyped <T>(options));
}
private void TryCreateHeaderRow(IXLWorkbook wb, Matrix mat, IXLWorksheet sheet)
{
if (mat.HasHeaders)
{
var headers = sheet.Row(1);
mat.ColumnsDefinitions.Select((colDef, colNum) =>
{
var cell = headers.Cell(1 + colDef.Index);
_formatApplier.ApplyFormatToCell(wb, cell, colDef.HeaderCellFormat);
cell.Value = colDef.Label;
return(cell);
}).ToArray();
}
}
public void Dispose()
{
if (IsDisposed)
{
return;
}
if (_disposeWorkbookWithTemplate)
{
Workbook.Dispose();
}
Workbook = null;
IsDisposed = true;
}