Formatting Excel Sheet using C#. The Style apply to all cells not to one cell

Question

I'm using a extension method to export DataTable to Excel. When I format cell style, Alignment and Font Size it applies to all cells in the sheet.

Here is my code

public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null, string address = "", string reportName = "")
    {
        try
        {
            int ColumnsCount;

            if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbooks.Add();

            // single worksheet
            Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;

            object[] Header = new object[ColumnsCount];

            // column headings               
            for (int i = 0; i < ColumnsCount; i++)
                Header[i] = DataTable.Columns[i].ColumnName;

            Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[10, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[10, ColumnsCount]));
            HeaderRange.Value = Header;
            HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gold);
            HeaderRange.Font.Bold = true;

            // DataCells
            int RowsCount = DataTable.Rows.Count;
            object[,] Cells = new object[RowsCount, ColumnsCount];

            for (int j = 0; j < RowsCount; j++)
                for (int i = 0; i < ColumnsCount; i++)
                    Cells[j, i] = DataTable.Rows[j][i];

            //Custom Header
            Worksheet.Range[Worksheet.Cells[1, 1], Worksheet.Cells[1, ColumnsCount]].Merge();
            Worksheet.Range[Worksheet.Cells[1, 1], Worksheet.Cells[2, ColumnsCount]].Merge();
            Worksheet.Cells[1, 1].Value = "DISTRIBUTOR SYSTEM";
            Worksheet.Cells[1, 1].Style.Font.Size = 18;
            Worksheet.Cells[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Aqua);
            Worksheet.Cells[1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            Worksheet.Cells[1].Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            Worksheet.Range[Worksheet.Cells[3, 1], Worksheet.Cells[3, 2]].Merge();
            Worksheet.Cells[3, 1].Value = "Invoice Date";

            Worksheet.Cells[3, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Aqua);
            Worksheet.Range[Worksheet.Cells[3, 3], Worksheet.Cells[3, 4]].Merge();
            Worksheet.Cells[3, 3].Value = System.DateTime.Today.ToShortDateString();
            Worksheet.Cells[3, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Aqua);

            Worksheet.Range[Worksheet.Cells[4, 1], Worksheet.Cells[4, ColumnsCount]].Merge();
            Worksheet.Cells[4, 1].Value = reportName;
            Worksheet.Cells[4, 1].Style.Font.Size = 18;

            Worksheet.Range[Worksheet.Cells[6, 1], Worksheet.Cells[6, ColumnsCount]].Merge();
            Worksheet.Cells[6, 1].Value = address;

            //Worksheet.Range[Worksheet.Cells[1, 1], Worksheet.Cells[1, 7]].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //Worksheet.Range[Worksheet.Cells[1, 1], Worksheet.Cells[1, 7]].Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            //Worksheet.Range[Worksheet.Cells[11, 7], Worksheet.Cells[RowsCount + 1, 7]].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

            Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[11, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
            Worksheet.Range[Worksheet.Cells[10, 4], Worksheet.Cells[RowsCount + 1, 4]].EntireColumn.NumberFormat = "yyyy/MM/dd hh:mm";

            Worksheet.Range[Worksheet.Cells[10, 11], Worksheet.Cells[RowsCount + 1, 7]].EntireColumn.NumberFormat = "####0.00";
            Worksheet.Cells[RowsCount + 2, 1].Value = "TOTAL";
            Worksheet.Cells[RowsCount + 2, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gold);
            Worksheet.Cells[RowsCount + 2, 7].Formula = "=Sum(" + Worksheet.Cells[11, 7].Address + ":" + Worksheet.Cells[RowsCount + 1, 7].Address + ")";

            Worksheet.Columns[7].ColumnWidth = 18.00;
            Worksheet.Columns[6].ColumnWidth = 18.00;
            Worksheet.Columns[5].ColumnWidth = 18.00;

            // check fielpath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    Worksheet.SaveAs(ExcelFilePath);
                    Excel.Quit();
                    MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            else    // no filepath is given
            {
                Excel.Visible = true;
            }
        }
        catch (Exception ex)
        {
            //MessageBox.Show(ex.Message);
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }

I only need to set font size and alignment center horizontally and vertically in cell 1, cell 4 and cell 6 Help plz

Answer 1

to set font size and alignment center horizontally and vertically in cell 1, cell 4 and cell 6 ... try

{
    Worksheet.Cells[1, i].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
    Worksheet.Cells[1, i].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
    Worksheet.Cells[1, i].Style.Font.Size = 20;
}

Answer 2

Using .Style in Excel Interop seems to change the style in the whole sheet, as highlighted here: Changing font size of one cell in excel using C# Try applying font and alignment changes directly to cells/ranges w/o accessing their style.