Hi,
The below code works fine and I am able to click on the different excel sheets that are tabbed. But when I comment out the line---> Sheet.Add(tabName); and uncomment out
-->// Sheet[Sheet.AddCopy("Sheet2")].Name = tabName;
I get an error that causes excel in 2002 to crash and excel 2007 to throw an error saying "File Error: Data may have been lost" error. Sheet2 is part of the same workbook, it's a template. First sheet is supposed to be a product summary with product description as a hyperlink to the product detail sheet which uses sheet two as a template. I did notice the hyperlink on the summary page(Sheet[0] doesn't work) I get an invalid reference error and I am not sure if it's tied to the first error. I have included my template that I am using. Thanks.
MemoryStream ms = null;
MemoryStream excelStream = new MemoryStream(ExcelResources.ExportItemSummary);
Aspose.Cells.Workbook Excel = new Workbook();
Excel.Open(excelStream);
Worksheets Sheet = Excel.Worksheets;
Sheet[0].Name = "Customer - Item Summary";
int headerAmount = 0;
int i = 7;
bool mutlipleDifferentCustomers= false ;
string customerOneID;
string customerOneName;
StringBuilder sb = new StringBuilder();
SortableCollection<PackQuoteInfo> collExportItems = Session["ExportItemSummary.Report"] == null ? null : Session["ExportItemSummary.Report"] as SortableCollection<PackQuoteInfo>;
SortableCollection<ExportItemSummaryInfo> exportItemsCol= ExportItemSummary.GetExportItemSummary(Utility.GetDelimitedList(collExportItems.GetPropertyArray("ItemId"))); //collection of products
if (exportItemsCol != null)
{
customerOneID = exportItemsCol[0].CustomerID;
customerOneName = exportItemsCol[0].CustomerName;
foreach (ExportItemSummaryInfo item in exportItemsCol)
{
sb = new StringBuilder();
if (customerOneID != item.CustomerID)
{
mutlipleDifferentCustomers = true;
}
String tabName = createTabName(item.Description, 0);
int tabIncrement = 1;
while (ExcelViewer.WorksheetExistsByName(Excel, tabName))
{
tabName = createTabName(item.Description, tabIncrement);
tabIncrement++;
}
Sheet.Add(tabName);
// Sheet[Sheet.AddCopy("Sheet2")].Name = tabName;
//I want to add a worksheet to the book using Sheet2 as a template and then use the returned value of AddCopy which is the index to help assign that worksheet a name.
sb.Append(tabName) ;
sb.Append("!A1") ;
Sheet[0].Hyperlinks.Add("B" + (i + 2), 1, 1, sb.ToString());
SortableCollection<ComponentInfo> collPackDetails = null;
if (item.Type == "Pack")
{
collPackDetails = Component.GetComponentsAnalysisByPackList(item.Item, Component.SumDuplicatesCriteria.DoNotSum);
}
else
{
collPackDetails = Component.GetComponentsAnalysisByQuoteList(item.Item);
}
Sheet[0].Cells["A" + (i + 2)].PutValue(item.Item);
Sheet[0].Cells["B" + (i + 2)].PutValue(item.Description);
Sheet[0].Cells["C" + (i + 2)].PutValue(item.CalendarMonthForecast);
Sheet[0].Cells["D" + (i + 2)].PutValue(item.GpCost);
Sheet[0].Cells["E" + (i + 2)].PutValue(item.BaseCost);
Sheet[0].Cells["F" + (i + 2)].PutValue(item.AnnualGpCost);
Sheet[0].Cells["G" + (i + 2)].PutValue(item.AnnualBaseCost);
Sheet[0].Cells["H" + (i + 2)].PutValue(item.GpPercent);
Sheet[0].Cells["I" + (i + 2)].PutValue(item.BpPercent);
Sheet[0].Cells["J" + (i + 2)].PutValue(item.SellPrice);
Sheet[0].Cells["K" + (i + 2)].PutValue(item.AnnualSpend);
Sheet[0].Cells["L" + (i + 2)].PutValue(item.InternalComments);
Sheet[0].Cells["M" + (i + 2)].PutValue(item.ExternalComments);
i++;
}
Sheet[0].Cells["M" + (headerAmount + 7)].PutValue(DateTime.Today.Date.ToString(""));
if (mutlipleDifferentCustomers == true)
{
Sheet[0].Cells["A" + (headerAmount + 5)].PutValue("Multiple Customers");
}
else
{
Sheet[0].Cells["A" + (headerAmount + 5)].PutValue(customerOneName);
Sheet[0].Cells["A" + (headerAmount + 6)].PutValue(customerOneID);
}
}
Sheet[0].AutoFitColumns();
ms = Excel.SaveToStream();
excelStream.Dispose();
return ms;
}