C# .NET - Auto Fit the cells of Excel

I want to auto fit the cells of excel

Example ##### is coming in cell, if we select all sheet and go to format > Cells > autofit it auto the selected range. I want this to do programmatically I am using this

                        ObjWorkSheet.Cells.AutoFit();
                        ObjWorkSheet.Rows.AutoFit();

This is giving range selection error, How to select range of whole sheet and make it autofit

To adjust the column width, you can do the following:

Excel.Range xlEntireColumn = null;
Excel.Range xlRange = null;

while (reader.Read()) {
for (int i=0;i<numcols;i++) { 
    xlsheet.ActiveSheet.Cells[row,i+1] = reader.GetValue(i).ToString();

    xlRange = xlSheet.ActiveSheet.Cells[row, i+1];
    xlEntireColumn = xlRange.EntireColumn;
    xlEntireColumn.AutoFit();
}

Hope this helps.

So u should make sure the column datatype is same as u describe in code

if u set String type column for that column which contain ### value then this will comes true value,

Some type it showing Date type when the contain the timespan value and if u set less with of that column

so make sure these things for fill execl sheet

hope this solve yr problem

Refer the Eggheadcafe's past post of it and Article

Or

Refer this code

public void ExportToExcel()
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new ApplicationClass();
            excel.Visible = true;
            Workbook wBook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet wSheet = (Worksheet)wBook.Worksheets[1];
            Range wRange;
 
            excel.Cells[2, 3] = "Schedule of Professional Tax";
            wRange = (Range)excel.Cells[2, 3];
            wRange.Font.Bold = true;
 
            excel.Cells[2,4] = "Month/Year:";
            wRange = (Range)excel.Cells[2, 4];
            wRange.Font.Bold = true;
            wRange.Columns.AutoFit();
 
            excel.Cells[2, 5] = dtpMonthYear.Text.ToString();
            if (cmbGrade.Text.Trim() != "")
            {
                excel.Cells[3, 4] = "Grade:";
                excel.Cells[3, 5] = cmbGrade.Text.Trim();
            }
 
            //Export Column Names
            excel.Cells[4, 1] = "S.No";
            wRange = (Range)excel.Cells[4, 1];
            wRange.Columns.Font.Bold = true;
            wRange.Columns.AutoFit();
 
            excel.Cells[4, 2] = "Emp.Code";
            wRange = (Range)excel.Cells[4, 2];
            wRange.Columns.Font.Bold = true;
            wRange.Columns.AutoFit();
 
            excel.Cells[4, 3] = "Name/Designation of person";
            wRange = (Range)excel.Cells[4, 3];
            wRange.Columns.Font.Bold = true;
            wRange.Columns.AutoFit();
 
            excel.Cells[4, 4] = "Amount";
            wRange = (Range)excel.Cells[4, 4];
            wRange.Columns.Font.Bold = true;           
 
            //Add Values
            double TotalAmt = 0;
            for (int i = 0; i < dgvPTDetails.Rows.Count; i++)
            {
                excel.Cells[6 + i, 1] = i + 1;
                excel.Cells[6 + i, 2] = dgvPTDetails.Rows[i].Cells[1].Value.ToString();
                excel.Cells[6 + i, 3] = dgvPTDetails.Rows[i].Cells[2].Value.ToString() + "/" + dgvPTDetails.Rows[i].Cells[3].Value.ToString();
                excel.Cells[6 + i, 4] = dgvPTDetails.Rows[i].Cells[4].Value.ToString();               
 
                //Calculate TotalAmount
                string Amt = "";
                Amt = dgvPTDetails.Rows[i].Cells[4].Value.ToString();
                if (Amt == "")
                { Amt = "0"; }
                TotalAmt = TotalAmt + Convert.ToDouble(Amt);                              
            }
 
            //Display Total
            int RowTot = dgvPTDetails.Rows.Count + 7;
            excel.Cells[RowTot, 2] = "Total : ";
            wRange = (Range)excel.Cells[RowTot, 2];
            wRange.Font.Bold = true;
 
            excel.Cells[RowTot, 4] = TotalAmt.ToString();
            wRange = (Range)excel.Cells[RowTot, 4];
            wRange.Font.Bold = true;
        }

Also See those links :

http://www.eggheadcafe.com/community/aspnet/2/42640/autofit-column.aspx
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_23505519.html
http://www.c-sharpcorner.com/UploadFile/mgold/Query2Excel12032005011029AM/Query2Excel.aspx

see that article also:
http://www.eggheadcafe.com/forumpost.aspx?topicid=2&forumpostid=3954

Code:

Range oRng = null;

oRng = Excel.get_Range("A1","A1");

oRng.AutoFit();

public void SetColumnWidth(Worksheet ws, int col, int width)
{
((Range)ws.Cells[1, col]).EntireColumn.ColumnWidth = width;
}
// autofit to contents
public void AutoFitColumn(Worksheet ws, int col)
{
((Range)ws.Cells[1, col]).EntireColumn.AutoFit();
}

Best Luck!!!!!!!!!!
Sujit.

Use this:

ObjWorkSheet.EntireColumn.AutoFit();

ObjWorkSheet.EntireColumn.AutoFit();

I want property that can do this

Dear you are missing small thing

Do it like this

oWorksheet.Cells.Select(); // Missing this…………

oWorksheet.Columns.Autofit();

oWorksheet.Rows.Autofit();

It is really great thing to me.......

one again thanks alot