private bool ResizeRange(Range range)
{
var rangeWorker = new RangeWorker(excelPackage, range);
var newLastRowIndex = rangeWorker.LastRowIndex;
while (rangeWorker.IsEmpty(newLastRowIndex) && newLastRowIndex > rangeWorker.FirstRowIndex)
{
newLastRowIndex--;
}
while (!rangeWorker.IsEmpty(newLastRowIndex + 1))
{
newLastRowIndex++;
}
if (newLastRowIndex == rangeWorker.LastRowIndex)
{
return(rangeWorker.IsNewRange);
}
excelPackage.Workbook.Names.Remove(range.Name);
var newRange = rangeWorker[rangeWorker.FirstRowIndex, rangeWorker.FirstColumnIndex, newLastRowIndex, rangeWorker.LastColumnIndex];
excelPackage.Workbook.Names.Add(range.Name, newRange);
return(true);
}
private void CopyRangeToTable(Range range, DataTable dataTable)
{
var rangeWorker = new RangeWorker(excelPackage, range);
for (var rowIndex = rangeWorker.FirstRowIndex + 1; rowIndex <= rangeWorker.LastRowIndex; rowIndex++)
{
DataRow newRow = dataTable.NewRow();
for (int colIndex = rangeWorker.FirstColumnIndex; colIndex <= rangeWorker.LastColumnIndex; colIndex++)
{
var columnName = rangeWorker.GetColumnName(colIndex);
var expectedType = dataTable.Columns[columnName].DataType;
var value = rangeWorker[rowIndex, colIndex, expectedType];
newRow[columnName] = value;
}
dataTable.Rows.Add(newRow);
}
}
private void CopyTableToRange(Range range, DataTable dataTable, bool keepFormula = false)
{
var rangeWorker = new RangeWorker(excelPackage, range);
for (int colIndex = rangeWorker.FirstColumnIndex; colIndex <= rangeWorker.LastColumnIndex; colIndex++)
{
var columnName = rangeWorker.GetColumnName(colIndex);
var column = range.Columns?[columnName];
rangeWorker[rangeWorker.FirstRowIndex, colIndex, column : column] = column.DisplayTitle;
}
var rowIndex = rangeWorker.FirstRowIndex + 1;
foreach (DataRow row in dataTable.Rows)
{
for (int colIndex = rangeWorker.FirstColumnIndex; colIndex <= rangeWorker.LastColumnIndex; colIndex++)
{
var columnName = rangeWorker.GetColumnName(colIndex);
var column = range.Columns?[columnName];
rangeWorker[rowIndex, colIndex, column : column, keepFormula : keepFormula] = row[columnName];
}
rowIndex++;
}
var newLastRowIndex = rowIndex - 1;
for (var emptyRowIndex = newLastRowIndex + 1; emptyRowIndex <= rangeWorker.LastRowIndex; emptyRowIndex++)
{
rangeWorker.Clear(emptyRowIndex);
}
excelPackage.Workbook.Names.Remove(range.Name);
var newRange = rangeWorker[rangeWorker.FirstRowIndex, rangeWorker.FirstColumnIndex, newLastRowIndex, rangeWorker.LastColumnIndex];
excelPackage.Workbook.Names.Add(range.Name, newRange);
}