private void ExtendNamedRangeDown(uint RowIndex, uint ColIndex1, uint ColIndex2, uint rowStep) { var defNames = workbook.Document.WorkbookPart.Workbook.DefinedNames; if (defNames != null) { foreach (DefinedName dn in defNames) { if (dn.Text == null || string.IsNullOrEmpty(dn.Text.Trim())) { continue; } var maddr = new XLRefAddress(workbook, dn.Text); if (maddr.SheetName != WorksheetName) { continue; } if (RowIndex >= maddr.RowIndex1 && RowIndex <= maddr.RowIndex2 && ColIndex1 == maddr.ColumnIndex1 && ColIndex2 == maddr.ColumnIndex2) { maddr.RowIndex2 += rowStep; dn.Text = maddr.RegerenceAddrFixedRowCols; } } } }
private static void SortRowByCellReference(Row row) { var sorted = row.Elements <Cell>().OrderBy(c => XLRefAddress.GetColumnIndex(c.CellReference)).ToArray(); row.RemoveAllChildren <Cell>(); row.Append(sorted); }
private void ShiftNamedRangesRight(uint newColIndex, uint colStep, uint RowIndex) { var defNames = workbook.Document.WorkbookPart.Workbook.DefinedNames; if (defNames != null) { foreach (DefinedName dn in defNames) { if (dn.Text == null || string.IsNullOrEmpty(dn.Text.Trim())) { continue; } var maddr = new XLRefAddress(workbook, dn.Text); if (maddr.SheetName != WorksheetName) { continue; } if (maddr.ColumnIndex1 >= newColIndex && maddr.RowIndex1 == RowIndex) { maddr.ColumnIndex1 += colStep; maddr.ColumnIndex2 += colStep; dn.Text = maddr.RegerenceAddrFixedRowCols; } } } }
private void ShiftMergedCellsRight(uint newColIndex, uint colStep, uint RowIndex) { var worksheetPart = workbook.GetWorksheetPartByName(WorksheetName); if (worksheetPart == null) { return; } var mergeCells = worksheetPart.Worksheet.Elements <MergeCells>().FirstOrDefault(); if (mergeCells != null) { var mergeCellsList = mergeCells.Elements <MergeCell>().Where(r => r.Reference.HasValue); foreach (var mc in mergeCellsList) { var maddr = new XLRefAddress(workbook, mc.Reference); if (maddr.ColumnIndex1 >= newColIndex && maddr.RowIndex1 == RowIndex) { maddr.ColumnIndex1 += colStep; maddr.ColumnIndex2 += colStep; mc.Reference = maddr.RegerenceAddrNoSheet; } } } }
private void CloneRowMergedCellsDown(uint RowIndex, uint ColumnIndex, uint newRowIndex) { var worksheetPart = workbook.GetWorksheetPartByName(WorksheetName); if (worksheetPart == null) { return; } var rowStep = (newRowIndex - RowIndex); var mergeCells = worksheetPart.Worksheet.Elements <MergeCells>().FirstOrDefault(); if (mergeCells != null) { var mergeCellsList = mergeCells.Elements <MergeCell>().Where(r => r.Reference.HasValue); foreach (var mc in mergeCellsList) { var maddr = new XLRefAddress(workbook, mc.Reference); if (maddr.RowIndex1 == RowIndex && maddr.ColumnIndex1 == ColumnIndex) { maddr.RowIndex1 += rowStep; maddr.RowIndex2 += rowStep; var newMerge = (MergeCell)mc.Clone(); newMerge.Reference = maddr.RegerenceAddrNoSheet; mergeCells.Append(newMerge); } } } }
public uint GetNextRowIndex(uint RowIndex, uint ColumnIndex) { uint res = RowIndex + 1; foreach (var mc in MergedCells) { var maddr = new XLRefAddress(workbook, mc.Reference); if (RowIndex == maddr.RowIndex1 && ColumnIndex == maddr.ColumnIndex1) { res = maddr.RowIndex2 + 1; break; } } return(res); }
public T GetCellValue <T>(string SheetName, uint ColumnIdx, uint RowIdx) { T res = default; var cell = GetCell(SheetName, ColumnIdx, RowIdx); if (cell != null) { var cellValue = cell.CellValue != null ? cell.CellValue.Text : ""; if (cell.DataType != null && cell.DataType == CellValues.SharedString) { var idx = int.Parse(cellValue, CultureInfo.InvariantCulture); var tbl = Document.WorkbookPart.SharedStringTablePart.SharedStringTable; var item = (SharedStringItem)tbl.ChildElements.Skip(idx).First(); res = (T)Convert.ChangeType(item.Text.InnerText, typeof(T), CultureInfo.InvariantCulture); } else if ((cell.DataType != null && cell.DataType == CellValues.Date) || typeof(T) == typeof(DateTime)) { var d = (double)Convert.ChangeType(cellValue, typeof(double), CultureInfo.InvariantCulture); res = (T)Convert.ChangeType(DateTime.FromOADate(d), typeof(T), CultureInfo.InvariantCulture); } else if ((cell.DataType != null && cell.DataType == CellValues.Boolean) || typeof(T) == typeof(bool)) { var d = (cellValue == "1"); res = (T)Convert.ChangeType(d, typeof(T), CultureInfo.InvariantCulture); } else { res = (T)Convert.ChangeType(cellValue, typeof(T), CultureInfo.InvariantCulture); } } if (res == null) { throw new Exception($"Cell value not found in {SheetName}!{XLRefAddress.GetColumnName(ColumnIdx)}{RowIdx}"); } return(res); }
private void ReplaceCell(uint columnIndex, uint rowIndex, Cell newCell) { ReplaceCell(XLRefAddress.GetColumnName(columnIndex), rowIndex, newCell); }
public Cell?GetCell(uint columnIndex, uint rowIndex) { return(GetCell(XLRefAddress.GetColumnName(columnIndex), rowIndex)); }
public void SetCellValue <T>(string RefAddr, T value) { var raddr = new XLRefAddress(this, RefAddr); SetCellValue(raddr.SheetName, raddr.ColumnIndex1, raddr.RowIndex1, value); }