AutoFitRow..can't get to work


#1

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

}

}


#2

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.


#3

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


#4

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.