Stimulsoft: Make Excel column auto width

Question

I'm exporting a (Stimulsoft) report to excel but some cells text becomes long and causes horizontal alignment between cells to fail. It's clear word wraping enlarges cell height but don't spreads it to all others cells in same row.

I managed to make it more reliable setting CanGrow to false in all cells but that way user needs to manualy auto-fit columns width after excel opening.

There's any way to tell Excel to automatic fit all columns width?

Answer 1

There is, and it is rather simple.

Private Sub AutoFitCols()

        Worksheets("Sheet1").Columns.AutoFit

End Sub

Just replace Sheet1 with the name of the worksheet you want the columns to autofit on.

If you want this to happen automatically when the workbook is opened, you can place the code in the Workbook_Open event like so:

Private Sub Workbook_Open()

        Worksheets("Sheet1").Columns.AutoFit

End Sub

Just be sure that code is within the ThisWorkbook code module.

Answer 2

In excel select all sheet (on the upper left) and double click the line between column b and c. in VB:

Cells.Select
    Cells.EntireColumn.AutoFit

Answer 3

Keyboard shortcut to auto fit all columns in any spreadsheet

Ctrl+A (Select All)

Alt+H+O+I (Autofit Column Width)

Answer 4

Worked it out and below is the source code for future reference. Thanks @Soulfire for pointing the right direction.

For start I was not even working with Interopsince requirements just stated I need to save it to Excel.

I contacted Stimulsoft and they pointed me it cannot be done

Since it cannot be donne before the file is created I added the feature to ask user to open it.

using Excel = Microsoft.Office.Interop.Excel;
// Do lots of Stuff

                SaveFileDialog saveFD = new SaveFileDialog();

                saveFD.Filter = "Excel Files|*.xlsx;*.csv;*.xls|All files|*.*";
                saveFD.FilterIndex = 1;
                saveFD.RestoreDirectory = true;
                saveFD.FileName = String.Format("MySavedFile_{0:yyyyMMddHHmmss}.xls", DateTime.Now);

                if (saveFD.ShowDialog() == DialogResult.OK)
                {
                    // In this example report is derived from the DevXpress XtraReport class
                    report.ExportToXls(saveFD.FileName);

                    // sanity note:Mensagem is a derived from devXpress XtraMessageBox
                    if (Mensagem.Confirm("File saved! Open it?"))
                    {
                        try
                        {
                            var excelApp = new Excel.Application();
                            excelApp.Visible = true;

                            var customEvent = new Excel.AppEvents_WorkbookOpenEventHandler(CustomWorkbookOpenEvent);

                            excelApp.WorkbookOpen += customEvent;

                            excelApp.Workbooks.Open(saveFD.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);}
                        catch (Exception)
                        {
                            Mensagem.Erro("Excel Failed to Open");
                        }
                    }
                }

The tricky part was to workout that COM Objects.

private void CustomWorkbookOpenEvent(Excel.Workbook wb)
{
    Excel._Worksheet sheet = (Excel.Worksheet)wb.ActiveSheet;
    sheet.Columns.AutoFit();
}

Edit

After a while we found interop is really bad for us (deploy on many users with many office versions, dll hell, etc).

We founs NetOffice is a great alternative. Just replaced the 3 lines below:

using Excel = NetOffice.ExcelApi;
/* ... */

                                var customEvent = new Excel.Application_WorkbookOpenEventHandler(CustomWorkbookOpenEvent);
                                excelApp.WorkbookOpenEvent += customEvent;