C# Open XML SDK write data at specific cell reference

Question

I have an excel sheet with a cell with a changed cell reference.

With my code, i can load the excel sheet and load my data into it. But i want that my data will begin insert at the specific Cell "int_startdok".

My Code:

    private byte[] LoadDataToFile([NotNull] DataTable dataTable)
    {
        if (dataTable == null) throw new ArgumentNullException(nameof(dataTable));

        var memoryStream = new MemoryStream();
        var byteArray = File.ReadAllBytes(TemplateFilePath);
        memoryStream.Write(byteArray, 0, byteArray.Length);

        using (var spreadsheetDocument = SpreadsheetDocument.Open(memoryStream, true))
        {
            var workbookPart = spreadsheetDocument.WorkbookPart;
            var worksheetPart = workbookPart.WorksheetParts.First();
            var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

            var headerRow = new Row();

            var columns = new List<string>();
            foreach (DataColumn column in dataTable.Columns)
            {
                columns.Add(column.ColumnName);

                var cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(column.ColumnName);
                headerRow.AppendChild(cell);
            }

            foreach (DataRow dsrow in dataTable.Rows)
            {
                var newRow = new Row();
                foreach (var col in columns)
                {
                    var cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(dsrow[col].ToString());
                    newRow.AppendChild(cell);
                }

                sheetData.AppendChild(newRow);
            }
        }

        return memoryStream.ToArray();
    }

How can i do this?

Answer 1

I got a solution for my problem. With ClosedXML i could do the trick very easy and reduce my code. For ClosedXML you need Open XML SDK. You can get it from the ClosedXML Wiki page.

Here is my solution:

    private byte[] InsertDataToTemplate([NotNull] DataTable dataTable)
    {
        if (dataTable == null) throw new ArgumentNullException(nameof(dataTable));

        // Create a memorystream from the template
        var memoryStream = new MemoryStream();
        var byteArray = File.ReadAllBytes(TemplateFilePath);
        memoryStream.Write(byteArray, 0, byteArray.Length);

        using (var workbook = new XLWorkbook(memoryStream))
        {
            var sheetData = workbook.Worksheets.First();
            sheetData.Cell("int_startdok").InsertData(dataTable.Rows);
            workbook.Save();
        }

        return memoryStream.ToArray();
    }