Set specific Width to Generated excel via code

Question

I have below code to generate an excel file. Excel file generate successfully. But columns are in default width. I want to adjust the column to match the width of the content.

 private void CreateExcelFile()
        {
            DataTable dt = BindFoodOrderToGrid(txtDate.Text);
            if (dt.Rows.Count > 0)
            {
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
                Response.ContentType = "application/ms-excel";

                string[] visibleColumns = { "food_type", "qty", "call_name", "Mobile" };

                // Set visibility for each column
                RemoveUnwantedColumns(dt, visibleColumns);
                dt.Columns["food_type"].ColumnName = "Food Type";
                dt.Columns["qty"].ColumnName = "Qty";
                dt.Columns["call_name"].ColumnName = "Employee";
                // Specify the columns you want to make visible


                dt.AcceptChanges();
                dt.Columns["Employee"].SetOrdinal(0);
                dt.Columns["Food Type"].SetOrdinal(1);
                dt.Columns["Qty"].SetOrdinal(2);
                dt.Columns["Mobile"].SetOrdinal(3);


                string str = string.Empty;
                foreach (DataColumn dtcol in dt.Columns)
                {
                    Response.Write(str + dtcol.ColumnName);
                    str = "\t";
                }
                Response.Write("\n");
                foreach (DataRow dr in dt.Rows)
                {
                    str = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Response.Write(str + Convert.ToString(dr[j]));
                        str = "\t";
                    }
                    Response.Write("\n");
                }

                Response.End();
            }
        }

I tried

    dt.Columns["food_type"].ColumnName = "     Food Type";

dt.Columns["food_type"].ColumnName = "@nbsp;@nbsp;Food Type";

But nothings working.

Answer 1

I tried to create a simple demo by using the nuget package : ClosedXML but I am not able to run it. The way of adjusting the width of columns is that :

// Set specific column widths
worksheet.Column(1).Width = 20; // Width for "Employee" column
worksheet.Column(2).Width = 25; // Width for "Food Type" column
worksheet.Column(3).Width = 10; // Width for "Qty" column
worksheet.Column(4).Width = 15; // Width for "Mobile" column

private void CreateExcelFile()
{
    DataTable dt = BindFoodOrderToGrid(txtDate.Text);
    if (dt.Rows.Count > 0)
    {
        using (var workbook = new ClosedXML.Excel.XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("Customers");
            var currentRow = 1;

            // Adjust the column names as needed
            dt.Columns["food_type"].ColumnName = "Food Type";
            dt.Columns["qty"].ColumnName = "Qty";
            dt.Columns["call_name"].ColumnName = "Employee";
            dt.Columns["Mobile"].ColumnName = "Mobile";

            // Set specific column widths
            worksheet.Column(1).Width = 20; // Width for "Employee" column
            worksheet.Column(2).Width = 25; // Width for "Food Type" column
            worksheet.Column(3).Width = 10; // Width for "Qty" column
            worksheet.Column(4).Width = 15; // Width for "Mobile" column

            // Add column headers
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cell(currentRow, i + 1).Value = dt.Columns[i].ColumnName;
            }

            // Add rows
            foreach (DataRow dr in dt.Rows)
            {
                currentRow++;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    worksheet.Cell(currentRow, j + 1).Value = dr[j];
                }
            }

            using (var stream = new MemoryStream())
            {
                workbook.SaveAs(stream);
                stream.Flush();
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", $"attachment; filename=Customers.xlsx");

                stream.Position = 0;
                stream.CopyTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }
}