Hi,
Can you run the below code and see why the aspose not able to copy the data when i use the insert range method. My requirement is to cut paste certain rows/columns with data.
I can copy the row using CopyRow() method, but that doesn’t have options to shift the rows down like InsertRange(). Please review and give us a solution as soon as you can.
public void TestAsposeCode(string filePathToSave)
{
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
ExcelRange.ExcelWorksheet = worksheet;
for (int i = 1; i <= 16; i++)
{
ExcelRange.SetCellRange(i, 1).SetValue(string.Format("{0}{1}", NonHoldingConstants.TemplateType, "OneRowColRange- '" + i + "' and 1 "));
}
ShiftTotalRows(false, worksheet);
workbook.Save(filePathToSave);
}
private void ShiftTotalRows(bool isTotalRequired, Worksheet excelWorksheet)
{
var totalRowIndexes = new List<int>() { 11, 12 };// { 12, 13 };// In PARis, initial startingRow/Column will be decremented by 1.
var startingRow = 1;//2;
if (!isTotalRequired)
{
int count = 0;
foreach (int totalRowIndex in totalRowIndexes)
{
Range copyRange = AsposeCellsHelper.GetAsposeCellRange(totalRowIndex, 1, 1, 1, excelWorksheet).EntireRow;
Range insertRange = AsposeCellsHelper.GetAsposeCellRange(startingRow, 1, 1, 1, excelWorksheet).EntireRow;
CellArea areaToMoveRows = new CellArea() { StartRow = startingRow, EndRow = startingRow };
excelWorksheet.Cells.InsertRange(areaToMoveRows, 1, ShiftType.Down, false);
excelWorksheet.Cells.DeleteRow(totalRowIndex + 1);// +1 is required since index will be changed after adding the above row
count++;
}
}
}
public static class AsposeCellsHelper
{
public static Range GetAsposeCellRange(int startingRow, int startingColumn, int totalRows, int totalColumns, Worksheet sheet)
{
if (sheet != null)
{
Tuple<int, int> asposeRowColumn = ReAdjustRowColumnIndexForAspose(startingRow, startingColumn);
if (asposeRowColumn != null)
{
sheet.SelectRange(asposeRowColumn.Item1, asposeRowColumn.Item2, totalRows, totalColumns, true);
Range selectedRange = ExcelRange.GetSelectedRange();
if (selectedRange != null)
return selectedRange;
}
}
return null;
}
private static Tuple<int, int> ReAdjustRowColumnIndexForAspose(int startingRow, int startingColumn)
{
startingRow = startingRow - 1;
startingColumn = startingColumn - 1;
if (startingRow >= 0 && startingColumn >= 0)
{
return Tuple.Create<int, int>(startingRow, startingColumn);
}
return null;
}
}