Hi,

This is a somewhat unusual problem... We have developed an application that presents data in grids to the user and allows them to slice and dice and send the results to an excel workbook. In particular one thing the users can do is to hide columns and also add formula columns.

So the problem arises when a column involved in a formula is hidden, in this case the excel exporter creates an additional hidden sheet in the workbook called 'Calc_Helper' that contains the values of the hidden column, and the excel formula references that sheet. That is fine when we export a single worksheet but we run into problems when we export a second grid to the same workbook as a sheet called 'Calc_Helper' already exists and an error message is inserted into the formula cell.

I understand that we are using a v old version of infragistics, so my real question is whether this has been fixed in the current version?

As an aside it seems unnecessary to create a hidden sheet with the values of the hidden columns in, an alternative would have been to embed the value directly in the formula instead of the column reference and use a specific formula for each formula cell in the sheet. Alternatively reusing (an unused section of) the existing Calc_Helper sheet would work, as would creating a seperate helper sheet for it.

This illustrates the problem:

// Create a datatable with two double columns a and b
var dt = new DataTable();
dt.Columns.Add("a", typeof(double));
dt.Columns.Add("b", typeof(double));
dt.Rows.Add(new object[] { 1.0, 2.0 });
 
// bind to grid, create formula column c = a + b
var grid = new UltraGrid() { Name = "grid" };
grid.CalcManager = new UltraCalcManager();
grid.DataSource = dt;
var col = grid.Rows.Band.Columns.Add("c", "c");
col.Formula = "[a] + [b]";
            
// Export to a workbook creates a single sheet as expected
var exporter = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter();
var workbook = new Workbook();
exporter.Export(grid, workbook);
Debug.Assert(workbook.Worksheets.Count == 1);
 
// Hide a source column, exporting the grid creates a second hidden worksheet called 'Calc_Helper'
grid.Rows.Band.Columns[0].Hidden = true;
exporter.Export(grid, workbook);
Debug.Assert(workbook.Worksheets.Count == 2);
Debug.Assert(workbook.Worksheets[1].Name == "Calc_Helper");
 
// Add a worksheet and export to it, the calculated cell will have an error
var ws = workbook.Worksheets.Add("Grid2");
exporter.Export(grid, ws);
Debug.Assert(ws.GetCell("B2").Value == "A worksheet with the name \"Calc_Helper\" already exists in the workbook.\r\nParameter name: name");

Cheers

Martin