Excel Column Alignment

Mehmet Fatih

3y978C#

I merged the first and second rows and centered them. I want to justify the data in the columns I circled to the left. But the data in the first and second rows is left justified.

int sutun = 1;
int satir = 4;
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Workbooks.Add();
ExcelApp.Visible = true;
ExcelApp.Worksheets[1].Activate();
ExcelApp.Application.WindowState = Excel.XlWindowState.xlMinimized;
ExcelApp.ActiveWindow.WindowState = Excel.XlWindowState.xlMaximized;
ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, 9]].Merge();
ExcelApp.Range[ExcelApp.Cells[2, 1], ExcelApp.Cells[2, 9]].Merge();
ExcelApp.Cells[1, 1] = "ANADOLU IMAM HATIP LISESI";
ExcelApp.Cells[2, 1] = "GEZI LISTESI";
ExcelApp.Cells[1, 1].Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // merged line
ExcelApp.Cells[2, 1].Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // merged line
ExcelApp.Cells[4, 3].Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ExcelApp.Cells[4, 4].Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
// hücre genisligi
ExcelApp.Cells[4, 1].ColumnWidth = 12; // T.C. No
ExcelApp.Cells[4, 2].ColumnWidth = 8; // ö.No
ExcelApp.Cells[4, 3].ColumnWidth = 18; // isim
ExcelApp.Cells[4, 4].ColumnWidth = 18; // soyisim
ExcelApp.Cells[4, 5].ColumnWidth = 10;  //cinsiyeti
ExcelApp.Cells[4, 6].ColumnWidth = 18; // sinifi
ExcelApp.Cells[4, 7].ColumnWidth = 10; // ünvani
ExcelApp.Cells[4, 8].ColumnWidth = 10; // tel
ExcelApp.Cells[4, 9].ColumnWidth = 10; // v.tel
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
    ExcelApp.Cells[satir, sutun + j].Value = dataGridView1.Columns[j].HeaderText;
    ExcelApp.Cells[satir, sutun + j].Font.Color = System.Drawing.Color.Black;
    ExcelApp.Cells[satir, sutun + j].Font.Size = 12;
    ExcelApp.Cells[satir, sutun + j].Font.Bold = false;
    ExcelApp.Cells[satir, sutun + j].Font.Name = "Arial";
}
satir++;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        ExcelApp.Cells[satir + i, sutun + j].Value = dataGridView1[j, i].Value;
        if (dataGridView1[j, i].Value.ToString() == "sno")
        {
            for (int k = 1; k <= dataGridView1.Columns.Count; k++)
            {
                ExcelApp.Cells[satir + i, k].Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0);
            }
        }
    }
}
this.BringToFront();
this.SendToBack();
this.Activate();
this.Close();

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mehmet Fatih3y ago
I have solved this problem removiing the the word Style like that this ExcelApp.Cells[2, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
0

Amit Mohanty3y ago

Check this

// ... (existing code)

satir++;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        ExcelApp.Cells[satir + i, sutun + j].Value = dataGridView1[j, i].Value;

        // Apply left alignment to the columns you want to justify to the left
        if (j == 2 || j == 3) // Column indices 2 and 3 are circled columns
        {
            ExcelApp.Cells[satir + i, sutun + j].Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
        }
        else
        {
            ExcelApp.Cells[satir + i, sutun + j].Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        }
        
        if (dataGridView1[j, i].Value.ToString() == "sno")
        {
            for (int k = 1; k <= dataGridView1.Columns.Count; k++)
            {
                ExcelApp.Cells[satir + i, k].Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0);
            }
        }
    }
}

// ... (existing code)

Mehmet Fatih3y ago
Amit, when I change it with your code, It is aligning the title to the left. I want to align the title to the center.

0

Amit Mohanty3y ago

You need to apply the left alignment style to the corresponding cells in the loop where you are setting the cell values.

// ... (existing code)

satir++;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        ExcelApp.Cells[satir + i, sutun + j].Value = dataGridView1[j, i].Value;

        // Apply left alignment to the columns you want to justify to the left
        if (j == 2 || j == 3) // Column indices 2 and 3 are circled columns
        {
            ExcelApp.Cells[satir + i, sutun + j].Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
        }
        
        if (dataGridView1[j, i].Value.ToString() == "sno")
        {
            for (int k = 1; k <= dataGridView1.Columns.Count; k++)
            {
                ExcelApp.Cells[satir + i, k].Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0);
            }
        }
    }
}

// ... (existing code)