如何用C#在Excel数据表中添加空行并实现表头拆分合并

Got it, let's tweak your existing C# code to implement the exact Excel layout you need—adding a top row for the merged year value extracted from your week-based headers. Here's the modified code with clear explanations of the key changes:

private void button1_Click(object sender, EventArgs e)
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    // 1. Insert the top empty row first (will hold our merged year later)
    xlWorkSheet.Rows[1].Insert(Excel.XlInsertShiftDirection.xlShiftDown);

    // 2. Write header data to row 2 (now shifted down from original position)
    xlWorkSheet.Cells[2, 1] = "Retailer";
    xlWorkSheet.Cells[2, 2] = "Brand";
    xlWorkSheet.Cells[2, 3] = "2019_WK1";
    xlWorkSheet.Cells[2, 4] = "2019_WK2";
    xlWorkSheet.Cells[2, 5] = "2019_WK3";

    // 3. Write your data rows starting from row 3
    xlWorkSheet.Cells[3, 1] = "LuLu";
    xlWorkSheet.Cells[3, 2] = "Perisil";
    xlWorkSheet.Cells[3, 3] = "25";
    xlWorkSheet.Cells[3, 4] = "26";
    xlWorkSheet.Cells[3, 5] = "23";

    xlWorkSheet.Cells[4, 1] = "Lulu";
    xlWorkSheet.Cells[4, 2] = "Ariel";
    xlWorkSheet.Cells[4, 3] = "26";
    xlWorkSheet.Cells[4, 4] = "28";
    xlWorkSheet.Cells[4, 5] = "29";

    xlWorkSheet.Cells[5, 1] = "Danube";
    xlWorkSheet.Cells[5, 2] = "Omo";
    xlWorkSheet.Cells[5, 3] = "27";
    xlWorkSheet.Cells[5, 4] = "28";
    xlWorkSheet.Cells[5, 5] = "30";

    xlWorkSheet.Cells[6, 1] = "Danube";
    xlWorkSheet.Cells[6, 2] = "Tide";
    xlWorkSheet.Cells[6, 3] = "24";
    xlWorkSheet.Cells[6, 4] = "23";
    xlWorkSheet.Cells[6, 5] = "29";

    xlWorkSheet.Cells[7, 1] = "Bin Dawood";
    xlWorkSheet.Cells[7, 2] = "Persil";
    xlWorkSheet.Cells[7, 3] = "26";
    xlWorkSheet.Cells[7, 4] = "27";
    xlWorkSheet.Cells[7, 5] = "28";

    // 4. Process headers to extract year and merge the top row
    // Extract year from the first week header (C2)
    string headerText = xlWorkSheet.Cells[2, 3].Value.ToString();
    string year = headerText.Split('_')[0];

    // Merge cells C1 to E1 for the year
    Excel.Range yearRange = xlWorkSheet.Range["C1", "E1"];
    yearRange.Merge();
    yearRange.Value = year;
    // Center-align the year text for better readability
    yearRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
    yearRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

    // Optional: Adjust column widths to fit content automatically
    xlWorkSheet.UsedRange.Columns.AutoFit();

    // Save and clean up Excel COM objects
    xlWorkBook.SaveAs("F:\\CTR_Data", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlApp);
    releaseObject(xlWorkBook);
    releaseObject(xlWorkSheet);

    MessageBox.Show("File created !");
}

// Helper method to clean up Excel COM objects (prevents lingering processes)
private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Unable to release the Object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

Key Changes Explained: #

• Inserted Top Row: We added xlWorkSheet.Rows[1].Insert(...) right after creating the worksheet to shift all content down and make space for the merged year row.
• Year Extraction: Using Split('_')[0] on the header text (like 2019_WK1) pulls out the year value cleanly with minimal code.
• Cell Merging: We merged cells C1 to E1 (the columns with week-based headers) and centered the year text to match professional Excel report layouts.
• Auto-Adjust Columns: Added UsedRange.Columns.AutoFit() to ensure all content is visible without manual resizing.

If you later have headers with mixed years (e.g., some 2019, some 2020), you could extend this code to group columns by year and merge corresponding sections of the top row—just let me know if you need that!

内容的提问来源于stack exchange，提问作者Aamir Ahmad