AutoFitColumns and AutoFitRows not working

The calls to AutoFitColumns and AutoFitRows don't seem to working. It doesn't matter if I move the calls before or after any data loading or cell formatting.

This is in Excel 2003/2007. Here is the code I am using:

	public static Cells.Workbook GetReportAsWorkbook(DataTable dt, Layer.Report report)
	{
	// Setup the license for the aspose dll
	Cells.License license = new Cells.License();
	license.SetLicense("Aspose.Cells.lic");

	// Create the workbook, and begin making the main worksheet
	Cells.Workbook wb = new Cells.Workbook();
	// Set the name of the worksheet (1 sheet is 
	// automatically created when you create the workbook)
	Cells.Worksheet ws = wb.Worksheets[0];
	ws.Name = "IPAR Report Data";

	// Import the data
	ws.Cells.ImportDataTable(dt, true, "A4");

	// Format the columns if necessary
	for (int i = 0; i < dt.Columns.Count; i++)
	{
		// Delete the bogus PK column
		if (dt.Columns<img src="/Community/emoticons/emotion-55.gif" alt="Idea [I]" />.ColumnName == "PK")
		{
			ws.Cells.DeleteColumn(i);
			continue;
		}

		if (Layer.IsColumnCurrency(dt.Columns<img src="/Community/emoticons/emotion-55.gif" alt="Idea [I]" />))
		{
			// Set to currency. 7 = $#,##0.00;$-#,##0.00, see aspose help for other formats
			ws.Cells.Columns[Convert.ToByte(i)].Style.Number = 7;
		}
		else if (Layer.IsColumnPercentage(dt.Columns<img src="/Community/emoticons/emotion-55.gif" alt="Idea [I]" />))
		{
			// Set to percentage. 10 = 0.00%, see aspose help for other formats
			ws.Cells.Columns[Convert.ToByte(i)].Style.Number = 10;
		}

	}


	// Freeze the header rows
	ws.FreezePanes(4, 0, 4, 0);

	// Autofilter the main row
	ws.AutoFilter.SetRange(3, 0, Convert.ToInt32(ws.Cells.MaxDataColumn));

	// Style the header row cells
	// (a bug does not allow us to set the style on the entire Row 
	// object itself, so we need to loop through each cell 
	// individually)
	for (int i = 0; i < Convert.ToInt32(ws.Cells.MaxDataColumn) + 1; i++)
	{
		ws.Cells[3, i].Style.Font.IsBold = true;
		ws.Cells[3, i].Style.Font.IsItalic = true;
		ws.Cells[3, i].Style.Font.Color = Drawing.Color.White;
		ws.Cells[3, i].Style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
		// Add a custom color to the color palette 
		// so we can use it as a cell background
		wb.ChangePalette(Drawing.Color.FromArgb(128, 158, 114), 55);
		ws.Cells[3, i].Style.Pattern = Aspose.Cells.BackgroundType.Solid;
		ws.Cells[3, i].Style.ForegroundColor = Drawing.Color.FromArgb(128, 158, 114);
	}

	// Add the title to the worksheet
	ws.Cells.Merge(0, 0, 1, 10);
	ws.Cells["A1"].Style.Font.Size = 14;
	ws.Cells["A1"].Style.Font.IsBold = true;
	ws.Cells["A1"].PutValue("IPAR Report Data");

	// Add some basic report information
	ws.Cells.Merge(1, 0, 1, 10);
	string basicReportInfo = string.Empty;
	basicReportInfo = "Date Run: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "  ";
	if (report.Status.Value == Layer.Status.Saved)
	{
		basicReportInfo += "Name: " + report.Name + "  ";
	}
	basicReportInfo += "Type: " + Layer.StringEnum.GetStringValue(report.ReportType) + " ";
	ws.Cells["A2"].PutValue(basicReportInfo);

	// Add some basic threshold option data
	ws.Cells.Merge(2, 0, 1, 10);
	if (!report.ThresholdType.HasValue || report.ThresholdType.Value == Layer.ThresholdType.NoCriteria)
	{
		// Do nothing, we are not displaying any threshold info
	}
	else
	{
		string basicThresholdInfo = string.Empty;
		basicThresholdInfo = "Threshold Criteria: Items where " + Layer.StringEnum.GetStringValue(report.ThresholdCriteria) + " ";
		basicThresholdInfo += "are " + Layer.StringEnum.GetStringValue(report.ThresholdMeasure) + " ";
		basicThresholdInfo += report.ThresholdAmount.Value.ToString("$#,##0.00;$-#,##0.00") + " ";
		basicThresholdInfo += report.ThresholdShowZeros.Value ? "including" : "not including" + " ";
		basicThresholdInfo += "items with no sales in last 4 weeks";
		ws.Cells["A3"].PutValue(basicThresholdInfo);
	}

	// Autosize the columns and rows
	ws.AutoFitColumns();
	ws.AutoFitRows();
	// Force the first row to grow because autofit doesn't work
	ws.Cells.SetRowHeightPixel(0, 20);
	// Return the workbook for saving
	return wb;


}

Can you look and see if there is something I am doing wrong?

Please try this attached fix.

And AutoFit methods don't work for merged cells. That's same as MS Excel.

Ok, I will try this, thanks. I guess I'm not sure what you mean by autofit for merged cells. I am able to select a row with merged columns in Excel, and choose AutoFit Row Height and it works.

Ok, this is still not working. I've attached a screen shot of the generated sheet so you can see what I am experiencing. The screen is in Excel 2007, but looks the same in Excel 2003.

The AutoFit does not take in to account the width of the dropdown arrow for the AutoFilter, so some of the characters in the header cells on row 4 appear behind the arrow.

Additionally, I am still losing formatting on my header row after I apply AutoFit columns. The code has a greeninsh background color with white bold text, but that isn't coming through.

Thanks,

Adam

Hi Adam,

I tried your code with minimal subtraction using Employees table in Northwind database:

Additionally, I am still losing formatting on my header row after I apply AutoFit columns. The code has a greeninsh background color with white bold text, but that isn't coming through

Well, I don't lose the formatting you mentioned. I got the header row (auto-filtered) with greenish background with white bold text (Please see the attachment screenshot).

The AutoFit does not take in to account the width of the dropdown arrow for the AutoFilter, so some of the characters in the header cells on row 4 appear behind the arrow

Well, I think AutoFit partially works, not completely thought. (Please see the attachment). there might be a few columns which are not perfectly auto fitted.

We will figure out the issue and enhance auto fit rows/columns.

Thank you.

Hi Amjad,

Thanks for responding. Your screenshot looks like it is using Excel 2000. Have you tried this on Excel 2003/2007? Can you verify that the header row formatting works on those versions?

The only way I can get header row formatting to work is if I place the formatting code after the AutoFitColumns call.

Hi Adam,

Well, I tested using Excel 2000, Excel2002 and Excel 2003 as my FileFormatType and got the same results with header row formatted (greenish background with white bold text color): I have attached my output excel file using Excel 2003 here (Kindly check it). And below, I paste your source code with a few ajustments and subtraction:

private void button56_Click(object sender, System.EventArgs e)

{

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\Northwind.mdb");

con.Open();

OleDbCommand cmd = new OleDbCommand("Select * from Employees",con);

OleDbDataAdapter da = new OleDbDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds,"Employees");

DataTable dt = ds.Tables["Employees"];

Workbook workbook = GetReportAsWorkbook(dt);

workbook.Save("d:\\autofitissue.xls",FileFormatType.Excel2003);

}

public static Workbook GetReportAsWorkbook(DataTable dt)

{

// Setup the license for the aspose dll

//Cells.License license = new Cells.License();

//license.SetLicense("Aspose.Cells.lic");

// Create the workbook, and begin making the main worksheet

Workbook wb = new Workbook();

// Set the name of the worksheet (1 sheet is

// automatically created when you create the workbook)

Worksheet ws = wb.Worksheets[0];

ws.Name = "IPAR Report Data";

// Import the data

ws.Cells.ImportDataTable(dt, true, "A4");

/*

// Format the columns if necessary

for (int i = 0; i < dt.Columns.Count; i++)

{

// Delete the bogus PK column

if (dt.Columns.ColumnName == "PK")

{

ws.Cells.DeleteColumn(i);

continue;

}

if (Layer.IsColumnCurrency(dt.Columns))

{

// Set to currency. 7 = $#,##0.00;$-#,##0.00, see aspose help for other formats

ws.Cells.Columns[Convert.ToByte(i)].Style.Number = 7;

}

else if (Layer.IsColumnPercentage(dt.Columns))

{

// Set to percentage. 10 = 0.00%, see aspose help for other formats

ws.Cells.Columns[Convert.ToByte(i)].Style.Number = 10;

}

}

*/

// Freeze the header rows

ws.FreezePanes(4, 0, 4, 0);

// Autofilter the main row

ws.AutoFilter.SetRange(3, 0, Convert.ToInt32(ws.Cells.MaxDataColumn));

// Style the header row cells

// (a bug does not allow us to set the style on the entire Row

// object itself, so we need to loop through each cell

// individually)

for (int i = 0; i < Convert.ToInt32(ws.Cells.MaxDataColumn) + 1; i++)

{

ws.Cells[3, i].Style.Font.IsBold = true;

ws.Cells[3, i].Style.Font.IsItalic = true;

ws.Cells[3, i].Style.Font.Color = Color.White;

ws.Cells[3, i].Style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

// Add a custom color to the color palette

// so we can use it as a cell background

wb.ChangePalette(Color.FromArgb(128, 158, 114), 55);

ws.Cells[3, i].Style.Pattern = Aspose.Cells.BackgroundType.Solid;

ws.Cells[3, i].Style.ForegroundColor = Color.FromArgb(128, 158, 114);

}

// Add the title to the worksheet

ws.Cells.Merge(0, 0, 1, 10);

ws.Cells["A1"].Style.Font.Size = 14;

ws.Cells["A1"].Style.Font.IsBold = true;

ws.Cells["A1"].PutValue("IPAR Report Data");

// Add some basic report information

ws.Cells.Merge(1, 0, 1, 10);

string basicReportInfo = string.Empty;

basicReportInfo = "Date Run: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + " ";

/*

if (report.Status.Value == Layer.Status.Saved)

{

basicReportInfo += "Name: " + report.Name + " ";

}

basicReportInfo += "Type: " + Layer.StringEnum.GetStringValue(report.ReportType) + " ";

*/

ws.Cells["A2"].PutValue(basicReportInfo);

// Add some basic threshold option data

ws.Cells.Merge(2, 0, 1, 10);

/*

if (!report.ThresholdType.HasValue || report.ThresholdType.Value == Layer.ThresholdType.NoCriteria)

{

// Do nothing, we are not displaying any threshold info

}

else

{

*/

string basicThresholdInfo = string.Empty;

basicThresholdInfo = "Threshold Criteria: Items where " /* + Layer.StringEnum.GetStringValue(report.ThresholdCriteria) + " ";

basicThresholdInfo += "are " + Layer.StringEnum.GetStringValue(report.ThresholdMeasure) + " ";

basicThresholdInfo += report.ThresholdAmount.Value.ToString("$#,##0.00;$-#,##0.00") + " ";

basicThresholdInfo += report.ThresholdShowZeros.Value ? "including" : "not including" + " "*/;

basicThresholdInfo += "items with no sales in last 4 weeks";

ws.Cells["A3"].PutValue(basicThresholdInfo);

/*

}

*/

// Autosize the columns and rows

ws.AutoFitColumns();

ws.AutoFitRows();

// Force the first row to grow because autofit doesn't work

ws.Cells.SetRowHeightPixel(0, 20);

// Return the workbook for saving

return wb;

}

Thank you.