We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Insert a row into a created range

I want to insert a row into an existing range. How do I do that?

I have an existing range with some formatting (merged two columns and two rows etc.)
Range groupRange = ws.Cells.CreateRange(2, 0, 2, 8);

I want to create a new range and copy existing style.
Range newSubGroupRange = ws.Cells.CreateRange(5, 0, 5, 8);
newSubGroupRange.CopyStyle(subGroupRange);

Besides, the size of new range needs to be dynamic in the sense I should be able to add additional rows but at the same time preserve formatting. Same way as if I insert a new row between two merged rows and it merges all three rows and 2 columns.

How do I do that?

I tried this using attached template file. When I do copy style it copies formatting to only first 2 rows of the new range as opposed to the whole range. I have tried multiple things and its nor working.

My function is below:

private static void generateCreditReportFromDS(DateTime date, string traderName, RGrpGuideline rGrpGuideline, Workbook wb, string worksheetName)
{
Worksheet ws = wb.Worksheets[worksheetName];
ws.Name = ws.Name.Replace(“Template”, traderName.ToUpper());
ws.Cells[“A2”].Value = traderName + " Guidelines Report";
ws.Cells[“H2”].Value = date.ToString(“MMM dd, yyyy”);
ws.Cells[“A6”].Value = “US/EUR High Grade (HG) and High Yield (HY) Corporate Bonds and CDS”;
ws.Cells[“H9”].Value = “350”;

int templateRow = 18;
int startingRow = 20;
int currentColumn = 0;
int currentRow = startingRow;
int totalColumns = 8;
//Range groupRange = ws.Cells[templateRow, startingColumn].GetMergedRange();
//Range subGroupRange = ws.Cells[templateRow + groupRange.RowCount, startingColumn].GetMergedRange();
Range groupRange = ws.Cells.CreateRange(templateRow, currentColumn, 2, totalColumns);
Range subGroupRange = ws.Cells.CreateRange(templateRow + groupRange.RowCount, currentColumn,2,totalColumns);


int nextGroupRangeRow = subGroupRange.FirstRow + subGroupRange.RowCount;
foreach (string gGrpName in rGrpGuideline.GGroupMap.Keys)
{
//int currentColumn = startingColumn;
//int groupStart = currentRow;
GuidelineGrp gGrp = null;
rGrpGuideline.GGroupMap.TryGetValue(gGrpName, out gGrp);
Range newGroupRange = ws.Cells.CreateRange(nextGroupRangeRow, currentColumn, groupRange.RowCount, totalColumns);
newGroupRange.CopyStyle(groupRange);

ws.Cells[newGroupRange.FirstRow,newGroupRange.FirstColumn].Value = gGrpName;
currentRow += groupRange.RowCount;

int nextSubGroupRangeRow = nextGroupRangeRow + groupRange.RowCount;
foreach (string gSubGrpName in gGrp.SubGroupMap.Keys)
{
GuidelineSubGrp gSubGrp = null;
gGrp.SubGroupMap.TryGetValue(gSubGrpName, out gSubGrp);

Range newSubGroupRange = ws.Cells.CreateRange(nextSubGroupRangeRow, currentColumn, gSubGrp.InstanceMap.Keys.Count, totalColumns);
newSubGroupRange.CopyStyle(subGroupRange);

int subGroupStart = newSubGroupRange.FirstRow;
ws.Cells[newSubGroupRange.FirstRow, newSubGroupRange.FirstColumn].Value = gSubGrpName;
//ws.Cells.CopyRow(ws.Cells, templateRow, currentRow + 1);
currentRow = newSubGroupRange.FirstRow + 1;
foreach (int instanceSort in gSubGrp.InstanceMap.Keys)
{
//ws.Cells.InsertRow(currentRow);

GuidelineInstance instance = null;
gSubGrp.InstanceMap.TryGetValue(instanceSort, out instance);
ws.Cells[currentRow, currentColumn+2].Value = instance._InstanceName;
//ws.Cells[currentRow, currentColumn + 2].SetStyle(instanceStyle);
double guidelineValue = instance._GuidelineValue;

if (!Double.NaN.Equals(guidelineValue))
{
ws.Cells[currentRow, currentColumn + 3].PutValue(guidelineValue);
}
else
{
ws.Cells[currentRow, currentColumn + 3].PutValue("");
}

ws.Cells[currentRow, currentColumn + 4].PutValue(instance._ActualValue);
ws.Cells[currentRow, currentColumn + 5].Value = instance._Units;
double percentage = instance._Percentage;
Cell percentCell = ws.Cells[currentRow, currentColumn + 6];
Range instanceRange = ws.Cells.CreateRange(subGroupStart, currentColumn, gSubGrp.InstanceMap.Keys.Count, 8);
Style percentStyle = percentCell.GetStyle();

if (Double.NaN.Equals(percentage))
{
percentCell.PutValue("");
}
else if (Double.PositiveInfinity.Equals(percentage))
{
percentCell.PutValue(“Infinity”);
}
else
{
percentCell.PutValue(percentage);
}
percentCell.SetStyle(percentStyle);

ws.Cells[currentRow, currentColumn + 7].Value = instance._Notes;


//ws.Cells.CopyRow(ws.Cells, instanceTemplateRow, currentRow + 1);
currentRow++;

}
nextSubGroupRangeRow = currentRow;

//ws.Cells.CopyRow(ws.Cells, instanceTemplateRow, currentRow + 1);
currentRow++;
}
nextGroupRangeRow = currentRow;
//ws.Cells.Merge(groupStart, startingColumn, 2, 3);


}

ws.Cells.DeleteRows(subGroupRange.FirstRow, subGroupRange.RowCount);
ws.Cells.DeleteRows(groupRange.FirstRow,groupRange.RowCount);

}

Hi,


Thanks for the template files and code segment.

I have checked your issue a bit. I could not evaluate your issue properly, I could not execute your code properly as I am not sure about certain objects, types, variables etc. and their values. We appreciate if you could create a simple sample console application (runnable), zip it and post it here with all the source and other files, we will check your issue soon.

Also, we recommend you to kindly try our latest version/fix: Aspose.Cells for .NET v7.5.1.2 and see if it makes any difference.

Thank you.