Using C# and ADO OLEDB connection string to create an excel file. How do i make the header row in excel bold?

Question

I have set my connection string as follows:-

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + saveFilenameAndLocation + ";Extended Properties='Excel 12.0 xml; HDR=Yes' "

I have specified that the first row is a header and my excel spreadsheet is created with the header row, followed by all the data rows. However, I want to make my header row bold, how do i do this? Any help appreciated.

score 3 · Answer 1 · answered Aug 25 '11 at 21:44

You need to use Office Interop; it can't be done with ADO.

Add a reference to your project to Microsoft.Office.Interop.Excel by right-clicking References in the Solution Explorer, and clicking 'Add Reference'. Then choose "Microsoft.Office.Interop.Excel".

Here is a very simple example that opens up an Excel document and makes the first row bold when you click a button on the user form.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{


    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String filename = "C:\\Path\\To\\Excel\\File\\file.xls";

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = true;
            Workbook xlWkb = xlApp.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet xlSh = xlWkb.Sheets[1] as Worksheet;
            Range xlRng = xlSh.UsedRange.get_Range("A1", Type.Missing).EntireRow;
            xlRng.Font.Bold = true;
            xlWkb.Save();
            xlApp.Quit();

            xlRng = null;
            xlSh = null;
            xlWkb = null;
            xlApp = null;
        }
    }
}

Thank you for all your assistance, that has helped me loads. — carrie, Aug 26 '11 at 08:12

score 1 · Answer 2 · edited May 23 '17 at 11:55

OLEDB provides access to the data only, not the formatting.

To access cell attributes and so on, you need to either use Interop (http://msdn.microsoft.com/en-us/library/ms173186%28v=vs.80%29.aspx) or a thrid party component like Spire.xls (http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html, commercial) or one of the other similar options (check Import and Export Excel - What is the best library?).

Using C# and ADO OLEDB connection string to create an excel file. How do i make the header row in excel bold?

2 Answers2