AutoFitRow..can't get to work

Here’s my code:

private void CreateAddedItemsReportWithoutWkgp(Excel excel)

{

try

{

string designerFile = System.Configuration.ConfigurationSettings.AppSettings.Get(“AsposeFile”);

excel.Open(designerFile);

this.dataTbl.Reset();

this.oleDbComm = this.oleDbConn.CreateCommand();

this.oleDbComm.CommandType = CommandType.StoredProcedure;

this.oleDbComm.CommandText = “p_sel_CASH_LOG_RPT_ADDED”;

this.oleDbComm.Parameters.Add(new OleDbParameter("@BEGIN_DATE",OleDbType.VarChar,30));

this.oleDbComm.Parameters.Add(new OleDbParameter("@END_DATE", OleDbType.VarChar,30));

this.oleDbComm.Parameters.Add(new OleDbParameter("@LOG_TYPE",OleDbType.Integer));

Worksheet sheet = excel.Worksheets[“Added Items Report”];

sheet.Name = “Added Items Report1”;

Cells cells = sheet.Cells;

sheet.PageSetup.SetHeader(1, “&“Tahoma,Bold” &12 " + “Added Items Report\rAdded Items from " + txtBeginDt.Text + " to " + txtEndDt.Text + “\r” + slogTypeName);

///If the begin date equals the end date, add the hour and minutes

///so we can get the whole day.

this.txtBeginDt.Text = this.txtBeginDt.Text + " 00:00”;

this.txtEndDt.Text = this.txtEndDt.Text + " 23:59”;

oleDbComm.Parameters["@BEGIN_DATE"].Value = txtBeginDt.Text;

oleDbComm.Parameters["@END_Date"].Value = txtEndDt.Text;

oleDbComm.Parameters["@LOG_TYPE"].Value = GetLogType();

this.oleDbAdapter = new OleDbDataAdapter(this.oleDbComm);

this.oleDbAdapter.Fill(this.dataTbl);

sheet.Cells.ImportDataTable(this.dataTbl, false, 1,0);

//Remove lines for comments

for(int c = dataTbl.Rows.Count; c >= 1; --c)

{

string val = cells[c,0].StringValue.ToString();

string prevVal = cells[c -1,0].StringValue.ToString();

int z = val.CompareTo(prevVal);

switch (z)

{

case 0:

//They’re equal so remove the data

cells.ClearContents(c,0,c,14);

break;

default:

Range r = cells.CreateRange(c,0,c,20);

r.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);

break;

}

}

//Create style

int styleIndex;

Aspose.Excel.Style style;

styleIndex = excel.Styles.Add();

style = excel.Styles[styleIndex];

Border borderStyle = style.Borders[BorderType.TopBorder];

borderStyle.LineStyle = CellBorderType.Thin;

borderStyle.Color = Color.Black;

Border borderStyle2 = style.Borders[BorderType.BottomBorder];

borderStyle2.LineStyle = CellBorderType.Thin;

borderStyle2.Color = Color.Black;

Border borderStyle3 = style.Borders[BorderType.LeftBorder];

borderStyle3.LineStyle = CellBorderType.Thin;

borderStyle3.Color = Color.Black;

Border borderStyle4 = style.Borders[BorderType.RightBorder];

borderStyle4.LineStyle = CellBorderType.Thin;

borderStyle4.Color = Color.Black;

style.IsTextWrapped = true;

int f = 2;

for(int i = 1; i <= this.dataTbl.Rows.Count; i ++)

{

cells[i,19].Formula = “=Q” + f + “&” " & P" + f + “& " " & R” + f + " & S" + f;

cells[i,19].Style = style;

f ++;

}

//Add line for bottom row

Range range = cells.CreateRange(dataTbl.Rows.Count + 1,0,dataTbl.Rows.Count + 1,19);

range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);

//Auto fit rows

for(int i = 0; i < dataTbl.Rows.Count+1; i++)

{

sheet.AutoFitRow(i);

}

sheet.PageSetup.PrintTitleRows = “$1Embarrassed1”;

///Remove the rest of the sheets

for(int i = 0; i < excel.Worksheets.Count ; i ++)

{

sheet = excel.WorksheetsIdea;

if(sheet.Name != “Added Items Report1”)

{

excel.Worksheets.RemoveAt(i);

i --;

}

}

excel.Save(“AddedItems1.xls”, SaveType.OpenInExcel, FileFormatType.Excel2000, Response);

}

catch(Exception ex)

{

throw new Exception(“Create Added Items Report”,ex);

}

}

AutoFitRow doesn’t work on cells with formula assigned at run time. Formula calculation engine had not been developed when we provided AutoFit method. I will investigate this issue and add this feature in future release.

Laurence:
Can you suggest a work around? I wrote a routine to calculate the row height and tried to set it using SetRowHeight before I wrote the formula to the spreadsheet and that won’t work either.
Terri
Tongue Tied

Hi Terri,

I will enhance it the make the AutoFitRow method working on cells set with formulas. However, it will not be availabe right now. I think mid of October will be feasible. And you may have to upgrade to Enterprise edition for Formula Calculation Engine is needed.

@trwatkins,
Aspose.Cells has replaced Aspose.Excel which is discontinued and no more development is done for it. The new product Aspose.Cells has rich set of features and better performance as compared to Aspose.Excel. All the latest features available in different versions of MS Excel are available in it. We can autofit rows and columns in a variety of ways like autofitting merged cells etc. Following is a sample code to autofit rows using Aspose.Cells.

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

// Auto-fitting the 2nd row of the worksheet
worksheet.autoFitRow(1);

// Auto-fitting the 1st column of the worksheet
worksheet.autoFitColumn(0);

// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "AutoFitRowsandColumns_out.xls");

// Print message
System.out.println("Row and Column auto fit successfully.");

Following is the link to a document which contains description and working examples to autofit rows and columns:
Autofit Rows and Columns

Download a free trial version of this new product here:
Aspose.Cells for .NET(Latest version)

We have prepared a detailed runnable solution which can be used to test different features of this this product and is available here.