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.Rows;
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 = “$11”;
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.Worksheets;
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.Worksheets;
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!