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