CODEWITHSUNDEEP
c#asp.netexcelexport
Cloud
Cloud

Reputation: 213

Code to expand columns in excel

When I export my data into excel, it loads up the columns but doesn't expand the columns. It shows the data without having to double click on the line to expand the columns. Can I write some code which will automatically auto-adjust the columns so that the cells can show the entire data? I'm using asp.net and c#.

The first picture is what it currently looks like & the second shows what I want it to look like.

What it looks like

What I want it to look like

protected void Button1_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;

    DataSet dataSet = new DataSet();

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISALog1ConnectionString"].ToString());
    SqlCommand cmd = new SqlCommand("exec ProxyReport", conn);
    cmd.CommandTimeout = 200;
    SqlDataAdapter ad = new SqlDataAdapter(cmd);
    ad.Fill(dataSet);

    GridView1.DataSource = dataSet.Tables[0];
    GridView1.DataBind();
    GridView2.DataSource = dataSet.Tables[1];
    GridView2.DataBind();
    GridView3.DataSource = dataSet.Tables[2];
    GridView3.DataBind();

    dataSet.Tables[0].TableName = "1";
    dataSet.Tables[1].TableName = "2";
    dataSet.Tables[2].TableName = "3";

    int count = 3;

    ExcelPackage pack = new ExcelPackage();


    for (int i = 1; i <= count; i++)
    {
        DataTable table = dataSet.Tables[i.ToString()];
        ExcelWorksheet ws = pack.Workbook.Worksheets.Add("Top" + i);
        ws.Cells["A1"].LoadFromDataTable(table, true);
    }  

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=ExcelExport.xlsx");
            Response.BinaryWrite(pack.GetAsByteArray());
            Response.Flush();
            Response.End();
  }

Upvotes: 2

Views: 2861

Answers (3)

J. Luckenbach
J. Luckenbach

Reputation: 71

I was looking for a solution on the same problem. The checked answer above didnt work for me directly.

It worked with a simple addition:

Excel.Range range;

range = ws.get_Range("A1", "1");
range.Columns. AutoFit();

Upvotes: 0

HatSoft
HatSoft

Reputation: 11201

Excel.Range range;

range = ws.get_Range("A1", "1");
range.AutoFit();

MSDN

Upvotes: 1

ssis_ssiSucks
ssis_ssiSucks

Reputation: 1506

Looks like the BestFit property is what you want, though it might be buggy:

http://excelpackage.codeplex.com/discussions/248406/

Upvotes: 0

Related Questions