C# .NET - Auto Fit the cells of Excel - Asked By Abhi Rana on 27-Aug-08 03:07 AM

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


reply - Binny ch replied to Abhi Rana on 27-Aug-08 03:12 AM

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.

#### showig in case of datatype of column are mismatch - Web Star replied to Abhi Rana on 27-Aug-08 03:14 AM

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

Setting a Column Width in Excel - Binny ch replied to Abhi Rana on 27-Aug-08 03:14 AM

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();
}
Go through this link hope u find some help:
http://www.c-sharpcorner.com/UploadFile/mgold/Query2Excel12032005011029AM/Query2Excel.aspx
Refer the Eggheadcafe's past post of it and Article - Deepak Ghule replied to Abhi Rana on 27-Aug-08 03:15 AM

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

Use this code... - Atul Shinde replied to Abhi Rana on 27-Aug-08 03:19 AM

Code:

Range oRng = null;

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

oRng.AutoFit();
Not Working - Abhi Rana replied to Binny ch on 27-Aug-08 03:22 AM
I just want the property that can select the selection mode of whole sheet and auto fit the columns
Try this - Sagar P replied to Abhi Rana on 27-Aug-08 03:31 AM
 

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();
}

May be this will solve your problem.

Just go thr this link;

http://www.c-sharpcorner.com/UploadFile/mgold/Query2Excel12032005011029AM/Query2Excel.aspx

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

Use this - Atul Shinde replied to Abhi Rana on 27-Aug-08 03:37 AM

Use this:

ObjWorkSheet.EntireColumn.AutoFit();

No property like this............ - Abhi Rana replied to Atul Shinde on 27-Aug-08 04:22 AM

ObjWorkSheet.EntireColumn.AutoFit();

I want property that can do this

AutoFit - Kalit Sikka replied to Abhi Rana on 27-Aug-08 06:17 AM

Dear you are missing small thing

 

Do it like this

 

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

oWorksheet.Columns.Autofit();

oWorksheet.Rows.Autofit();

THANKS ALOT - Abhi Rana replied to Kalit Sikka on 27-Aug-08 06:42 AM

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

one again thanks alot