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?