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