CODEWITHSUNDEEP
c#excelclosedxml
UwUs
UwUs

Reputation: 157

ClosedXML how to set cell width for whole workbook?

i'm using closedXml to export data to Excel. But my columns need to be adjusted to the content therefore i've tried to specify the colums width by hand:

var ws = workbook.Worksheets.Add("Column Settings");

col.Width = 50;

Wasn't working for me.I've also tried:

Columns().AdjustToContents();

But also no results. So looking for an solution that will pre set the cell width in the whole sheet or workbook. This is my code:

    //variables 
    XLWorkbook workbook = new XLWorkbook();
    var wsDep = workbook.Worksheets.Add("MBR");
    wsDep.Columns().AdjustToContents();
    DataTable dt = new DataTable() { TableName = "MBR" };
    DataSet ds = new DataSet();
    TagRepo tr = new TagRepo();
    List<string> tags = new List<string>();
    tags.Add("TimeStamp");

    //list min,max,avg.
    foreach (var item in tr.getAllTagNames())
    { 
        tags.Add(item.Trim()+"_Min");
        tags.Add(item.Trim() + "_Max");
        tags.Add(item.Trim() + "_Avg");
    }
    //input data
    int l = tags.Count();
    //Colum names
    string[] columns = new string [l];

    for (int i = 0; i < l; i++)
    {
        columns[i] = tags[i];
    }
    var rows = new object[][]
    {
    new object[] {"1", 2, false },
    new object[] { "test", 10000, 19.9 },
    };
    //Add columns
    dt.Columns.AddRange(columns.Select(c => new DataColumn(c.ToString())).ToArray());
    //Add rows
    foreach (var row in rows)
    {
        dt.Rows.Add(row);
    }

    //Convert datatable to dataset and add it to the workbook as worksheet
    ds.Tables.Add(dt);
    workbook.Worksheets.Add(ds);
    
    //save
    //string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    string savePath = Path.Combine(@"\\nas\TreviMap\Pilootproeven en opvolging-IC\Export data", "toon.xlsx");
    workbook.SaveAs(savePath, false);

Upvotes: 7

Views: 7948

Answers (1)

Raidri
Raidri

Reputation: 17550

You need to call Columns().AdjustToContents(); after you have filled the worksheet(s).

Upvotes: 14

Related Questions