Error when saving file


#1

Here’s my code:

private void CreateAcquisitionLogReport(Excel excel)

{

try

{

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

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

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

excel.Open(designerFile);

ReadDeals();

//Insert workgroups selected into Ref_corp_cage_log_rpt

//GetWorkgroupList();

Worksheet sheet = excel.Worksheets[“Acq Log by Deal ID”];

sheet.Name = “Acq Log by Deal ID1”;

Cells cells = sheet.Cells;

//Set the Grand Total formula to nothing

sGrandTotal = “”;

int currentRow = -1;

//Style

int styleIndex;

Aspose.Excel.Style style;

styleIndex = excel.Styles.Add();

style = excel.Styles[styleIndex];

style.Font.IsBold = true;

style.Number = 7;

//For each deal get the list of rows

if(dataTbl.Rows.Count > 0)

{

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

{

DataRow dealRow = this.dataTbl.RowsIdea;

string sFilter = dealRow[“DEAL_ID”].ToString();

//Need to write the deal ID to the screen to show the

//user that the app has not stopped responding.

GetDealData(sFilter);

currentRow += 3;

string startCell = excel.GetCellName(currentRow,11);

cells.ImportDataTable(dataTbl2, false, currentRow, 0);

Range range = cells.CreateRange(currentRow, 1,1,4);

range = cells.CreateRange(currentRow + 1, 1, dataTbl2.Rows.Count, 1);

range = cells.CreateRange(currentRow + 1, 4, dataTbl2.Rows.Count, 1);

currentRow -=1;

cells[currentRow,10].Formula = “”;

currentRow += dataTbl2.Rows.Count;

string endCell = excel.GetCellName(currentRow,11);

//Add styles to make the sub-totals bold

cells[currentRow + 1,10].PutValue((string)“SUM”);

if(dataTbl2.Rows.Count > 0)

{

cells[currentRow + 1,11].Formula = “=sum(” + startCell + “:” + endCell + “)”;

}

//Add the style to the sub-totals

cells[currentRow + 1, 10].Style = style;

cells[currentRow + 1, 11].Style = style;

sGrandTotal = sGrandTotal + cells[currentRow + 1,11].Name + “+”;

}

//Need to add a grand total

cells[currentRow + 3, 10].PutValue((string)“GRAND TOTAL”);

cells[currentRow + 3, 11].Formula = “=sum(” + sGrandTotal + “)”;

cells[currentRow + 3, 10].Style = style;

cells[currentRow + 3, 11].Style = style;

//Auto fit rows

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

{

sheet.AutoFitRow(i);

}

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

sheet.PageSetup.SetHeader(1, "&“Tahoma,Bold” &12 " + "Acquisition Report By Deal ID\rFrom " + txtBeginDt.Text + " to " + txtEndDt.Text);

///Remove the rest of the sheets

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

{

sheet = excel.WorksheetsIdea;

if(sheet.Name != “Acq Log by Deal ID1”)

{

excel.Worksheets.RemoveAt(i);

i --;

}

}

excel.Save(“AcqLogbyDealID1.xls”, SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

}

else

//Just open the report so the user can see there is no data

{

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

{

sheet = excel.WorksheetsIdea;

if(sheet.Name != “Acq Log by Deal ID1”)

{

excel.Worksheets.RemoveAt(i);

i --;

}

}

excel.Save(“AcqLogbyDealID1.xls”, SaveType.OpenInExcel, FileFormatType.Default, this.Response);

///Remove the rest of the sheets

}

}

catch(Exception ex)

{

throw new Exception(“Create Acquisition Log Report”,ex);

}

}

Here is my error:

An Error Occurred On /315C01/wfrmCriteria.aspx

Exception of type System.Web.HttpUnhandledException was thrown.

Create Acquisition Log Report

Errors in Excel Save method

Index was outside the bounds of the array

Thank you!


#2

Could you try to remove the following code to see what will happen?
if(sheet.Name != "Acq Log by Deal ID1")

{

excel.Worksheets.RemoveAt(i);

i --;

}

And could you send your designer file to me? Thanks.


#3

It deleted all the worksheets from the excel workbook. Then nothing was saved or was visible when excel was loaded.

Have you had a chance to look at the designer file? I have looked at every thing I could think of to get this to work. This is the last outstanding issue for the project.

Thank you for your time and assistance.


#4

Please send your designer file tom me, I will investigate it.

I know those code is used to remove extra worksheets. I meant to see what would happen if you didn’t delete those worksheets. Could you have a try? Thanks.