private IXLRangeRow GetFirstDataRow(IXLWorksheet worksheet)
{
var firstStopRowUsed = worksheet.FirstRowUsed();
var headerStopsRow = firstStopRowUsed.RowUsed();
return(headerStopsRow.RowBelow());
}
public DataTable ImportExceltoDatatable(string filePath, string sheetName)
{
// Open the Excel file using ClosedXML.
// Keep in mind the Excel file cannot be open when trying to read it
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(sheetName);
//Create a new DataTable.
DataTable dt = new DataTable();
var headerRow = workSheet.FirstRowUsed();
foreach (IXLCell cell in headerRow.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
foreach (IXLRow row in workSheet.Rows())
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
return(dt);
}
}
/// <summary>
/// Returns a DataTable from an Excel Worksheet
/// Rules:
/// #1 the first row used contains all column names
/// #2 all table items are in the same range as the first row
/// #3 an table ends when it has an empty row
/// </summary>
/// <param name="sheetPosition">the position/ index of the worksheet (starts at 1)</param>
/// <returns></returns>
public static DataTable ExtractDataTable(IXLWorksheet worksheet)
{
var dataTable = new DataTable();
// Gets the range of the used Cells
var firstRow = worksheet.FirstRowUsed().RowUsed();
var columnNames = firstRow.Cells().Select(cell => cell.Value.ToString());
// Add columns to the DataTable
foreach (var columnName in columnNames)
{
dataTable.Columns.Add(columnName);
}
// Add row to the DataTable
var currentRow = firstRow.RowBelow();
while (!currentRow.IsEmpty())
{
dataTable.Rows.Add();
var cells = currentRow.Cells();
foreach (var item in cells.Select((cell, indexer) => new { Cell = cell, Index = indexer }))
{
dataTable.Rows[dataTable.Rows.Count - 1][item.Index] = item.Cell.Value;
}
currentRow = currentRow.RowBelow();
}
return(dataTable);
}
public string[,] ConvertWorksheetTo2dArray(IXLWorksheet worksheet)
{
// Look for the first row used
var firstRowUsed = worksheet.FirstRowUsed();
// Narrow down the row so that it only includes the used part
var residentinfoHeaders = firstRowUsed.RowUsed();
var residentinfo = residentinfoHeaders;
Console.WriteLine($"Show Resident Information: ", residentinfoHeaders.Cell(1).Value);
var i = 1;
while (!residentinfo.Cell(i).IsEmpty())
{
//Console.WriteLine();
Console.WriteLine(residentinfo.Cell(i).Value + "|");
i++;
}
// for(InitializeSomething; BooleanCheckSomething; MutateSomething(s))
//
//
for (var row = firstRowUsed.RowUsed(); !row.IsEmpty(); row = row.RowBelow())
{
foreach (var cell in row.Cells())
{
Console.Write(cell.Value);
Console.Write(" ");
}
Console.WriteLine();
}
//Index's for The Cells in Table
var activeRowCount = worksheet.RowsUsed().Count();
var activeColumnCount = worksheet.ColumnsUsed().Count();
string[,] my2dArrayWithConvertedCells = new string[, ]
{
{ "", "" },
};
//using foreach to create 2d Array
//convert to 2d Array and return the values
var rangeOfFirstNames = worksheet.Range("A1:A5");
foreach (var cell in rangeOfFirstNames.Cells())
{
cell.SetDataType(XLDataType.Text);
var valueofcell = cell.Value.ToString();
}
return(my2dArrayWithConvertedCells);
}
public void getexceList() //заполняет модель сигнала и создает лист сигналов.
{
int KKScolumn = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("KKS")).First().WorksheetColumn().ColumnNumber();
int typecolumn1 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Inp_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn;
int typecolumn2 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Inp2_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn;
int typecolumn3 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Out_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn;
int typecolumn4 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Out2_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn;
int cabpos = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("KKS кабеля")).First().WorksheetColumn().ColumnNumber() - KKScolumn;
int conpos = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("Connection")).First().WorksheetColumn().ColumnNumber() - KKScolumn;
signals = new List <Signalinexcel>();
var rngs = _sheet.Range(_sheet.Column(KKScolumn).FirstCellUsed().CellBelow(), _sheet.Column(KKScolumn).LastCellUsed());
rngs.Cells().ForEach(a =>
{
if (a.GetString() != "")
{
var r1 = _sheet.Range(a.CellRight(cabpos).Address, a.CellRight(cabpos + 3).Address);//берет только первый кабель, исправить.
var r2 = _sheet.Range(a.CellRight(cabpos + 5).Address, a.CellRight(cabpos + 8).Address);
string zlk =
(a.CellRight(cabpos + 4).GetString().Trim(',') + "," + a.CellRight(cabpos + 9).GetString()).Trim(',');
string cab = r1.Cells().Aggregate("", (str, c) => str.Trim('#') + "#" + c.GetString())
+ r2.Cells().Aggregate("", (str, c) => str.Trim('#') + "#" + c.GetString());
signals.Add(new Signalinexcel(a.GetString().Trim(),
a.CellRight(1).GetString().Trim(),
a.CellRight(typecolumn1).GetString().Trim(),
a.CellRight(typecolumn2).GetString().Trim(),
a.CellRight(typecolumn3).GetString().Trim(),
a.CellRight(typecolumn4).GetString().Trim(), cab.Trim(),
a.CellRight(conpos).GetString().Trim(), zlk));
}
});
}
private IEnumerable <ValuesBunch> ReadWorkSheet(IXLWorksheet worksheet)
{
var items = new List <ValuesBunch>();
var firstRow = worksheet.FirstRowUsed();
var lastRow = worksheet.LastRowUsed();
IXLRow currentRow = firstRow;
ValuesBunch valueItems = null;
while (currentRow.RowNumber() <= lastRow.RowNumber())
{
var currentRowFirstCell = currentRow.FirstCellUsed();
var correntRowLastCell = currentRow.LastCellUsed();
if (DateTime.TryParse(currentRowFirstCell?.CachedValue.ToString(), out var initialDate) && initialDate < DateTime.Now)
{
InitializeFinalDate(currentRowFirstCell, correntRowLastCell, initialDate, out var finalDate);
if (valueItems != null && valueItems.Count > 0)
{
items.Add(valueItems);
}
if (finalDate.HasValue)
{
valueItems = new ValuesBunch(initialDate, finalDate.Value);
}
else
{
valueItems = new ValuesBunch(initialDate);
}
}
else if (valueItems != null && currentRow != null && currentRow.CellsUsed().Count() > 0)
{
var currentTypeCell = currentRow.FirstCellUsed();
var rowDelta = ParseValueItems(valueItems, currentTypeCell);
if (valueItems != null && valueItems.Count > 0)
{
items.Add(valueItems);
valueItems = null;
}
currentRow = currentRow.RowBelow(rowDelta - currentRow.RowNumber());
}
currentRow = currentRow.RowBelow();
}
if (valueItems != null && valueItems.Count > 0)
{
items.Add(valueItems);
}
return(items);
}
/// <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();
}
public DataTable ReadExcel()
{
// Open the Excel file using ClosedXML.
// Keep in mind the Excel file cannot be open when trying to read it
using (XLWorkbook workBook = new XLWorkbook(Path))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Create a new DataTable.
DataTable table = new DataTable();
//Loop through the Worksheet rows.
bool firstRow = true;
var startIndex = workSheet.FirstRowUsed().FirstCellUsed().Address.ColumnNumber;
var endIndex = workSheet.FirstRowUsed().LastCellUsed().Address.ColumnNumber;
foreach (IXLRow row in workSheet.RowsUsed())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
table.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
table.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(startIndex, endIndex))
{
table.Rows[table.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
return(table);
}
}
public void Highlight(IXLWorksheet worksheet, HighlightOptions options)
{
if (worksheet == null)
{
throw new ArgumentNullException("worksheet");
}
var headerRow = worksheet.FirstRowUsed();
var compareRelatedColumns =
FindColumnsBySuffix(headerRow, options.NameOfLeftDataSource, options.NameOfRightDataSource)
.ToList();
var compareRelatedColumnNames = compareRelatedColumns.Select(l => l.Key);
// hide gap & compare result column
foreach (var column in compareRelatedColumns)
{
if (columnNameBuilder.IsGapColumn(column.Key) || columnNameBuilder.IsCompareResultColumn(column.Key))
{
worksheet.Column(column.Value.ColumnNumber).Hide();
}
}
// create formula
var cellValueMissingFormulaFormats = new Dictionary <string, string>();
var cellValueNotEqualFormulaFormats = new Dictionary <string, string>();
var cellValueSimilarFormulaFormats = new Dictionary <string, string>();
var cellValueEqualFormulaFormats = new Dictionary <string, string>();
var underlyingColumns = columnNameBuilder.GetUnderlyingColumnNames(compareRelatedColumnNames, new[] { options.NameOfLeftDataSource, options.NameOfRightDataSource });
foreach (var underlyingColumn in underlyingColumns)
{
var leftColumnName = columnNameBuilder.BuildColumName(options.NameOfLeftDataSource, underlyingColumn);
var rightColumnName = columnNameBuilder.BuildColumName(options.NameOfRightDataSource, underlyingColumn);
var gapColumnName = columnNameBuilder.BuildGapColumnName(underlyingColumn);
var compareColumnName = columnNameBuilder.BuildCompareResultColumnName(underlyingColumn);
var leftColumn =
compareRelatedColumns.FirstOrDefault(l => leftColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
var rightColumn =
compareRelatedColumns.FirstOrDefault(l => rightColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
var gapColumn =
compareRelatedColumns.FirstOrDefault(l => gapColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
var compareColumn =
compareRelatedColumns.FirstOrDefault(l => compareColumnName.Equals(l.Key, StringComparison.OrdinalIgnoreCase));
CreateFormula(leftColumn, rightColumn, gapColumn, compareColumn, cellValueMissingFormulaFormats, cellValueSimilarFormulaFormats, cellValueEqualFormulaFormats, cellValueNotEqualFormulaFormats);
}
var contentRows = worksheet.RowsUsed().Skip(1).ToList();
ApplyFormula(contentRows,
cellValueEqualFormulaFormats, cellValueMissingFormulaFormats, cellValueSimilarFormulaFormats, cellValueNotEqualFormulaFormats);
}
public virtual SheetParseResult <T> ParseSheet <T>(IXLWorksheet sheet, Func <T, List <string> > validateT = null) where T : class, new()
{
if (sheet == null)
{
throw new ArgumentNullException(nameof(sheet));
}
var result = new SheetParseResult <T>();
var headerRow = sheet.FirstRowUsed();
var lastRow = sheet.LastRowUsed();
var mappings = GetDefaultPropertyMapParsers <T>(headerRow);
//foreach over the rows and parse to T
foreach (var _row in sheet.Rows(firstRow: headerRow.RowBelow().RowNumber(), lastRow: lastRow.RowNumber()))
{
result.TotalRecordCount++;
var row = _row; //modified closure
var runningValidation = new List <string>(); //use to give feedback on parse and validation
var t = new T();
foreach (var m in mappings)
{
object val;
var cell = row.Cell(m.ExcelColumnIndex);
var didParse = m.TryGetProperty(propertyInfo: m.ObjectPropertyInfo, input: cell.GetString(), outVal: out val);
if (didParse)
{
m.ObjectPropertyInfo.SetValue(t, val);
}
else
{
runningValidation.Add($"{m.ObjectPropertyInfo.Name} did not parse.");
}
this.FillCellBackground(cell: ref cell, isValid: didParse);
}
if (runningValidation.Count == 0 && validateT != null)
{
runningValidation.AddRange(validateT(t));
}
if (runningValidation.Count == 0)
{
result.ValidList.Add(t);
}
this.FillRowBackgroundWithValidationMessage(row: ref row, isValid: runningValidation.Count == 0, validationMessages: runningValidation);
}
return(result);
}
private void ObtenerListaDeClasesDesdeXlsx(IXLWorksheet hoja, out List <Clase> clases)
{
clases = new List <Clase>();
try
{
// Los indices siempre son 1, 2, 3 y 4 porque son las columnas del XLSX generado en la descarga de plantilla, la cual es la aceptada para la carga.
const int indiceNombres = 1;
const int indiceDescripciones = 2;
const int indiceTipos = 3;
const int indicePrioridad = 4;
// Obtiene la fila de los encabezados
var primeraFilaUsada = hoja.FirstRowUsed();
var fila = primeraFilaUsada.RowUsed();
// Baja una fila para leer la data
fila = fila.RowBelow();
// Recorre las filas hasta que encuentra una que este vacio el nombre.
while (!fila.Cell(indiceNombres).IsEmpty())
{
// Valida que las columnas tengan data
if (fila.Cell(indiceDescripciones).IsEmpty() || fila.Cell(indiceTipos).IsEmpty() || fila.Cell(indiceTipos).IsEmpty())
{
continue;
}
// Agrega las clases encontradas en el XLSX a la lista de clases
clases.Add(new Clase()
{
CLASS_NAME = fila.Cell(indiceNombres).GetString()
,
CLASS_DESCRIPTION = fila.Cell(indiceDescripciones).GetString()
,
CLASS_TYPE = fila.Cell(indiceTipos).GetString()
,
PRIORITY = int.Parse(fila.Cell(indicePrioridad).GetString())
});
// Baja una fila para continuar leyendo el XLSX
fila = fila.RowBelow();
}
}
catch (Exception e)
{
InteraccionConUsuarioServicio.Alerta($"Error al leer la hoja de Clases: {e.Message}");
}
}
private List <string> GetWorksheetHeaders(IXLWorksheet ws)
{
var categories = new List <string>();
// Look for the first row used
var firstRowUsed = ws.FirstRowUsed();
// Narrow down the row so that it only includes the used part
var categoryRow = firstRowUsed.RowUsed();
//Get all categoreis
foreach (var cell in categoryRow.Cells())
{
categories.Add(cell.GetString());
}
return(categories);
}
private static void ApplyConditionalFormatting(IXLWorksheet worksheet, IXLRange formattingRange, string equalFormulaFormat, string missingFormulaFormat, string similarFormulaFormat, string notEqualFormulaFormat)
{
var top = worksheet.FirstRowUsed().RowNumber();
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, equalFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.Green);
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, missingFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.Red);
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, similarFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.GreenRyb);
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, notEqualFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.Yellow);
}
public XlsReader(IXLWorkbook xlsDocument, string xlsSheetName, int?xlsFirstRowNumber, int?xlsLastRowNumber)
{
if (xlsDocument.Worksheets.Count > 0)
{
// Try to get worksheet
if (!string.IsNullOrEmpty(xlsSheetName) && xlsDocument.TryGetWorksheet(xlsSheetName, out var _foundWorksheet))
{
_worksheet = _foundWorksheet;
}
else
{
_worksheet = xlsDocument.Worksheet(1);
}
// Try to get first an last row number of data list
_firstRowNumber = xlsFirstRowNumber ?? _worksheet.FirstRowUsed().RowNumber();
_lastRowNumber = xlsLastRowNumber ?? _worksheet.LastRowUsed().RowNumber();
}
else
{
throw new XlsReaderException("The Excel file seems to have no worksheet");
}
}
public IDataSheetRow FirstRowUsed()
{
return(new DataSheetRow(_worksheet.FirstRowUsed()));
}
public void ExtractData()
{
var firstRowUsed = _startRow != null?_sheet.Row(_startRow.Value) : _sheet.FirstRowUsed();
var lastRowUsed = _endRow != null?_sheet.Row(_endRow.Value) : _sheet.LastRowUsed();
var dataReadingRow = firstRowUsed.Row(1, lastRowUsed.RowNumber()); // считываемая строка
int fullDataRowsCount = 0; // количество строк с полной информацией
int halfDataOrEmptyRowsCount = 0; // количество пустых или частично заполненных строк
// если первая строка - заголовок таблицы, то пропустить ее
//dataRow = dataRow.RowBelow();
// чтение строк
while (dataReadingRow.RowNumber() <= lastRowUsed.RowNumber())
{
// проверка наличия данных в целевых ячейках
var readThisRow = true;
foreach (var cellNum in _propertyColumns.Values)
{
readThisRow = readThisRow & !dataReadingRow.Cell(cellNum).IsEmpty();
}
// читать строку, если заполнены все целевые ячейки
if (readThisRow)
{
fullDataRowsCount++;
var dataItem = new Data
{
Company = dataReadingRow.Cell(_propertyColumns["Data:Company"]).GetString(),
Address = dataReadingRow.Cell(_propertyColumns["Data:City"]).GetString() + ", " +
dataReadingRow.Cell(_propertyColumns["Data:Address"]).GetString(),
Counts = new List <Count>
{
new Count
{
Name = dataReadingRow.Cell(_propertyColumns["Data:Counts:0:Name"]).GetString(),
Value = dataReadingRow.Cell(_propertyColumns["Data:Counts:0:Value"]).GetString(),
},
new Count
{
Name = dataReadingRow.Cell(_propertyColumns["Data:Counts:1:Name"]).GetString(),
Value = dataReadingRow.Cell(_propertyColumns["Data:Counts:1:Value"]).GetString(),
},
},
};
// вывод на консоль
Console.WriteLine($"Row num: {dataReadingRow.RowNumber()}");
Console.WriteLine($"Company: {dataItem.Company}");
Console.WriteLine($"Address: {dataItem.Address}");
Console.Write($"Counts: [ ");
foreach (var count in dataItem.Counts)
{
Console.Write($"{{{count.Name}, {count.Value}}}");
if (dataItem.Counts.IndexOf(count) < dataItem.Counts.Count - 1)
{
Console.Write(", ");
}
}
Console.WriteLine(" ]\n");
}
else
{
halfDataOrEmptyRowsCount++;
}
// чтение следующей строки
dataReadingRow = dataReadingRow.RowBelow();
}
Console.WriteLine("\n=== SUMMARY ===\n");
Console.WriteLine($"Индекс первой строки: {firstRowUsed.RowNumber()}");
Console.WriteLine($"Индекс последней строки: {lastRowUsed.RowNumber()}");
Console.WriteLine($"Количество полных строк: {fullDataRowsCount}");
Console.WriteLine($"Количество пустых строк: {halfDataOrEmptyRowsCount}");
}
