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.
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;
}
}
}
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?).
