We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Error Data may have been lost

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;

}

Hi,

Thanks for providing us the template file with sample code.

We will check your issue soon.

By the way, which version you are using and did you try our latest version (4.6.0)?

Thank you.

I am using version 4.6.0 of aspose and vs2005 for dev. environment with .net 2.0 framework. Thanks.

Hi,

Could you post your created file here, we want to check it. And, we have tested using your template file with the following code, it works fine.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(@"f:\test\Copy+of+ExportItemSummary.xls");
Worksheets worksheets = workbook.Worksheets;
for (int i = 0; i < 10; i++)
{
string tabName = "Test" + i;
worksheets[worksheets.AddCopy("Sheet2")].Name = tabName;

worksheets[0].Hyperlinks.Add("B" + (i + 2), 1, 1, tabName + "!A1");
worksheets[0].Cells["B" + (i + 2)].PutValue(tabName);
}
worksheets[0].AutoFitColumns();
workbook.Save(@"f:\test\dest_output.xls");

My output file is also attached, you may check it.

Thank you.

I have fixed the problem with the hyperlink, it was missing single Quotes around the description

Sheet[0].Hyperlinks.Add("B" + (i + 2), 1, 1, "'" + tabName + "'"+"!A1" );

I will upload the latest code and the template along with output so we both have the latest versions. Code didn't change much other then the hyperlink above and the use of string builder function. And only the header was changed on the template. The template file is file named ExportItemSummary.xls and the results file is the com.medline.medpack.ExcelViewerPL.xls file. Thanks.

Here is the function

{

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;

SortableCollection<PackQuoteInfo> collExportItems = Session["ExportItemSummary.Report"] == null ? null : Session["ExportItemSummary.Report"] as SortableCollection<PackQuoteInfo>;

SortableCollection<ExportItemSummaryInfo> exportItemsCol= ExportItemSummary.GetExportItemSummary(Utility.GetDelimitedList(collExportItems.GetPropertyArray("ItemId")));

if (exportItemsCol != null)

{

customerOneID = exportItemsCol[0].CustomerID;

customerOneName = exportItemsCol[0].CustomerName;

foreach (ExportItemSummaryInfo item in exportItemsCol)

{

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[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. This is where things break starting with the top statement of AddCopy

Sheet[0].Hyperlinks.Add("B" + (i + 2), 1, 1, "'" + tabName + "'"+"!A1" );

SortableCollection<ComponentInfo> collPackDetails = null;

if (item.Type == "Pack")

{

collPackDetails = Component.GetComponentsAnalysisByPackList(item.Item, Component.SumDuplicatesCriteria.DoNotSum);

}

else

{

collPackDetails = Component.GetComponentsAnalysisByQuoteList(item.Item);

}

Sheet[tabName].Cells["N" + (3)].PutValue(DateTime.Today.Date.ToString(""));

Sheet[tabName].Cells["A" + (headerAmount + 1)].PutValue(customerOneName);

Sheet[tabName].Cells["A" + (headerAmount + 2)].PutValue(customerOneID);

Sheet[tabName].Cells["A" + (headerAmount + 2)].PutValue(item.Description);

Sheet[tabName].Cells["A" + (headerAmount + 2)].PutValue(item.Item);

foreach (ComponentInfo comp in collPackDetails)

{

Sheet[tabName].Cells["A" + (i + 2)].PutValue(comp.QtyPerKit);

Sheet[tabName].Cells["B" + (i + 2)].PutValue(comp.UnitOfMeasure);

Sheet[tabName].Cells["C" + (i + 2)].PutValue(comp.ComponentNumber);

Sheet[tabName].Cells["D" + (i + 2)].PutValue(comp.ComponentDescription);

Sheet[tabName].Cells["E" + (i + 2)].PutValue(comp.VendorItemNumber);

Sheet[tabName].Cells["F" + (i + 2)].PutValue(comp.VendorName );

Sheet[tabName].Cells["G" + (i + 2)].PutValue(comp.LatexFlag);

Sheet[tabName].Cells["H" + (i + 2)].PutValue(comp.MedlineNetCost);

Sheet[tabName].Cells["I" + (i + 2)].PutValue(comp.LineItemCost);

Sheet[tabName].Cells["J" + (i + 2)].PutValue(comp.ExtendedLineItemCost);

Sheet[tabName].Cells["K" + (i + 2)].PutValue(comp.Rebate);

}

// Sheet[0].Cells["M" + (headerAmount + 7)].PutValue(DateTime.Today.Date.ToString(""));

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

// Sheet[0].Cells["N" + (i + 2)].PutValue(System.Math.Round(customer.AnnualSales, 2, MidpointRounding.AwayFromZero)); remove mhb

//Sheet[0].Cells["O" + (i + 2)].PutValue(System.Math.Round((customer.AverageBpe / 100), 4, MidpointRounding.AwayFromZero)); remove mhb

i++;

}

Sheet[0].Cells["A" + (i+1)].PutValue("This information and all other information relating to the Med-Pack program is confidential and is provided for the sole purpose of providing analysis for Medline and our customers. Any other use or distribution to third parties is strictly prohibited") ;

Sheet[0].Cells["A" + (i+1)].Style.Font.IsBold = true ;

Sheet[0].Cells["A" + (i + 1)].Style.IsTextWrapped = true;

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;

}

Hi,

Thanks for the template files (input + output)

We will figure it out soon.

Thank you.

I forgot to mention that the error happens when you switch between the tabs going from tab to tab that’s when excel crashes. Thanks.

Hi,

After checking your created file, I find it was actually a bug in the old versions about Worksheet.Copy method and we have already fixed this bug in the latest version. We have a doubt, I think you are still using the old version. So, kindly make sure that you should use the latest version (4.6.0)

If you still could not evaluate, create a fresh sample console app with 4.6.0 and post it here to reproduce the issue, we will check it soon.

Thank you.