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