protected virtual decimal?GetValueFromCell(XlsCell cell)
{
if (double.TryParse(ChangeDecSep(cell), out var valDub))
{
return((decimal?)valDub);
}
return(null);
}
//меняет разделитель целой и дробной части на установленный в системе
protected virtual string ChangeDecSep(XlsCell cell)
{
var replace = cell.Value.ToString().Replace("%", string.Empty);
return(DecSeparator == "."
? replace.Replace(",", DecSeparator)
: DecSeparator == ","
? replace.Replace(".", DecSeparator)
: string.Empty);
}
/// <summary>
/// Собирает данные по листу в список строка/ячейка
/// </summary>
private static List <XlsRow> GetRows(OpenXmlElement data, OpenXmlElementList cellFrm, SharedStringItem[] sharedItems, int startRow = 0, int endRow = 0)
{
var rows = data.Elements <Row>().ToList();
var result = new List <XlsRow>(rows.Count);
endRow = endRow > 0 ? endRow : rows.Count;
for (var i = startRow; i < endRow; i++)
{
var cells = rows[i].Descendants <Cell>().ToList();
result.Add(new XlsRow {
Cells = new List <XlsCell>(cells.Count)
}); //! Cells может оказаться пустым
uint indexRow = 0;
for (var j = 0; j < cells.Count; j++)
{
var cell = cells[j];
if (cell == null)
{
continue;
}
if (j == 0)
{
indexRow = (uint)GetIndexRow(cell);
}
if (indexRow == 0)
{
indexRow = (uint)(i + startRow);
}
var item = new XlsCell
{
AddressName = cell.CellReference,
Value = GetCellValue(cellFrm, sharedItems, cell),
Index = indexRow,
ColumnName = GetColumnName(cell, (int)indexRow)
};
result.LastOrDefault()?.Cells.Add(item);
}
}
return(result);
}
public static DataTable RemoveColumnsByRow(this DataTable dataTable, int rowIndex, Func <XlsCell, bool> filter)
{
if (rowIndex >= dataTable.Rows.Count)
{
throw new ArgumentOutOfRangeException(string.Format("row index overflow: {0}", dataTable.Rows.Count));
}
DataRow row = dataTable.Rows[rowIndex];
int index = 0;
var removeIndexs = new List <int>();
foreach (object cell in row.ItemArray)
{
XlsCell value = null;
var xlsCell = cell as XlsCell;
if (xlsCell != null)
{
value = xlsCell;
}
else
{
value = new XlsCell(cell);
}
if (filter(value))
{
removeIndexs.Add(index);
}
index++;
}
for (int i = removeIndexs.Count - 1; i >= 0; i--)
{
dataTable.Columns.RemoveAt(removeIndexs[i]);
}
return(dataTable);
}
/// <summary>
/// Add a row to the input DataTable for each row match in the input MatchCollection
/// </summary>
/// <param name="rowMatches">A collection of all the rows to add to the DataTable</param>
/// <param name="dataTable">The DataTable to which we add rows</param>
private static void ParseRows(IEnumerable rowMatches, DataTable dataTable)
{
foreach (Match rowMatch in rowMatches)
{
// if the row contains header tags don't use it - it is a header not a row
if (!rowMatch.Value.Contains("<th"))
{
DataRow dataRow = dataTable.NewRow();
var rowArray = new List <XlsCell>();
MatchCollection cellMatches = Regex.Matches(
rowMatch.Value,
CellPattern,
ExpressionOptions);
for (int columnIndex = 0; columnIndex < cellMatches.Count; columnIndex++)
{
var cellValue = cellMatches[columnIndex].Groups[1].ToString();
cellValue = cellValue.RemoveSpan();
var hyperLinkMach = Regex.Match(cellValue, HyperLinkPattern);
if (hyperLinkMach.Captures.Count > 0)
{
var xlsCell = new XlsCell(hyperLinkMach.Groups[2].Value);
xlsCell.SetHyperLink(hyperLinkMach.Groups[1].Value);
rowArray.Add(xlsCell);
Console.WriteLine("have hyperlink");
}
else
{
rowArray.Add(new XlsCell(cellValue));
}
}
XlsCell[] cells = rowArray.ToArray();
dataRow.ItemArray = cells;
dataTable.Rows.Add(dataRow);
}
}
}
public abstract XlsNode AddNode(XlsCell cell);