ClosedXmlExample

Test.cs

public class UniversalTemplateV3SpreadsheetGenerator
	{
		public void GenerateSpreadsheet(string outputFilename, Stream templateSource)
		{
			var currentRow = 1;
			using (XLWorkbook template = new XLWorkbook(templateSource), resultXlsx = CreateWorkbook())
			{
				var sheet = resultXlsx.Worksheet(1);
				var templateSheet = template.Worksheet(1);
				var templateRange = template.NamedRange("body").Ranges.First();
				var mergedRanges = templateSheet.MergedRanges.Where(mr => templateRange.Contains(mr)).ToList();

				for (int k = 0; k < 50000; k++)
				{
					var firstRangeColumn = templateRange.FirstColumn().WorksheetColumn().ColumnNumber();
					foreach (var mergedRange in mergedRanges)
					{
						var address = mergedRange.RangeAddress.FirstAddress;
						var firstRow = currentRow + address.RowNumber - templateRange.RangeAddress.FirstAddress.RowNumber;
						var lastRow = firstRow + mergedRange.RowCount() - 1;
						sheet.Range(firstRow, address.ColumnNumber, lastRow, address.ColumnNumber + mergedRange.ColumnCount() - 1).Merge(false);
					}

					for (var i = 1; i <= templateRange.RowCount(); i++)
					{
						for (var j = 1; j <= templateRange.ColumnCount(); j++)
						{
							var templateCell = templateRange.Cell(i, j);
							var targetCell = sheet.Cell(currentRow + i - 1, firstRangeColumn + j - 1);
							// Uncomment next line for check optimization
							// targetCell.IsInferiorMergedCellUserDefined = false; // I know that this is incorrect. In the real code, I will set the value correctly
							targetCell.Style = templateCell.Style;
							targetCell.Value = templateCell.Value;
						}
					}

					currentRow += templateRange.RowCount();
				}

				resultXlsx.SaveAs(outputFilename);
			}
		}

		private XLWorkbook CreateWorkbook()
		{
			var result = new XLWorkbook();
			result.AddWorksheet("test");
			return result;
		}
}