OLEDB Excel file format

Question

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
                 fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMES=1;\"";

using (OleDbConnection conn = new OleDbConnection(strConn))
{
    conn.Open();
    DataTable dt = dataset.Tables[0];
    OleDbCommand cmd = new OleDbCommand(getCreateTableCommand(dt), conn);
    cmd.ExecuteNonQuery();

    conn.Close();
}

/*method - getCreateTableCommand returns something like this - 
{CREATE TABLE [Results] ([name1] STRING,[name2] STRING,[name3] STRING,[name4] STRING}*/

Output is GOOD. Issue is- I want the excel columns to have proper width. In other words, I want to get wider columns sometimes even wider then the space required to write columns' name. How to resize the column?

score 0 · Answer 1 · answered Jul 15 '13 at 18:53

The ACE Driver has no control over how Excel displays the value. If you want to control the cell formatting, you will need to use the OpenXML SDK, a Third Party Add-In like jetCell, or Excel Automation using the Primary Interop Assembly, but chances are you don't want to go with the last option.

score 0 · Answer 2 · edited May 23 '17 at 12:21

After populating Excel, write a method to autofit the column widths, or set them explicitly in the code to whatever you want. You'd need to use the Excel Interop, and from what I hear, it's a pain in C#. However, this is a pretty simple task and I suspect would not be a big deal in C#. My experience with Excel automation lies solely with vb. This is what I'd do (I don't like using 3rd party anything in my apps though)

How do I auto size columns through the Excel interop objects?

OLEDB Excel file format

2 Answers2