HI,
I'm exporting an Ultragid's data to Excel via the document exporter. This works well but if an existing worksheet in the excel workbook has data set up as a pivot table (based on a previous exports data), this gets 'broken' and no longer functions correctly.
I'm assuming this is because I'm deleting any existing worksheet that I'm finding and re-creating with the new data?
Here's the code:
METHOD PUBLIC VOID ProcessCTRL-E():
DEFINE VARIABLE lvExtensions AS CHARACTER NO-UNDO.
/* Order of extensions checked */
/* xlsm - Macro enabled Workbook */
/* xlsx - Workbook */
/* xls - Workbook 97-2003 */
lvExtensions = "xlsm,xlsx,xls".
/*
* N.B we are using version 9.2 of Infragistics so we can't support
* an export to excel templates "xltm,xltx,xlt" until version 10x
*/
/* xltm - Macro enabled Template */
/* xltx - Template */
/* xlt - Template 97-2003*/
DEFINE VARIABLE lvcFileName AS CHARACTER NO-UNDO.
DEFINE VARIABLE lvcProgramName AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE wb AS Infragistics.Excel.Workbook NO-UNDO.
DEFINE VARIABLE ws AS Infragistics.Excel.Worksheet NO-UNDO.
DEFINE VARIABLE wsExporter AS Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter NO-UNDO.
DEFINE VARIABLE lvcWorksheetName AS CHARACTER NO-UNDO.
DEFINE VARIABLE lvlWorkSheetFound AS LOGICAL NO-UNDO.
DEFINE VARIABLE lvcDefaultFileExtension AS CHARACTER INIT ".xlsx" NO-UNDO.
DEFINE VARIABLE lvcFileExtension AS CHARACTER NO-UNDO.
DEFINE VARIABLE lvfExcelFormat AS Infragistics.Excel.WorkbookFormat NO-UNDO.
DEFINE VARIABLE lvlReturn AS LOGICAL NO-UNDO.
ASSIGN
lvcProgramName = SUBSTRING(lvcABLWindowName,1, LENGTH(lvcABLWindowName) - 2)
lvcWorksheetName = lvcProgramName.
SESSION:SET-WAIT-STATE ("GENERAL").
DO i = 1 TO NUM-ENTRIES(lvExtensions):
ASSIGN
FILE-INFO:FILE-NAME = lvcProgramName + "." + ENTRY(i,lvExtensions).
IF FILE-INFO:FULL-PATHNAME NE ? THEN
DO:
ASSIGN
lvcFileName = FILE-INFO:FULL-PATHNAME
lvcFileExtension = ENTRY(i,lvExtensions).
LEAVE.
END.
END.
/* set the format of the workbook */
CASE lvcFileExtension:
WHEN "xlsx" THEN
lvfExcelFormat = Infragistics.Excel.WorkbookFormat:Excel2007.
WHEN "xls" THEN
lvfExcelFormat = Infragistics.Excel.WorkbookFormat:Excel97To2003.
WHEN "xlsm" THEN
lvfExcelFormat = Infragistics.Excel.WorkbookFormat:Excel2007MacroEnabled.
OTHERWISE
lvfExcelFormat = Infragistics.Excel.WorkbookFormat:Excel2007.
END CASE.
/* create a new workbook object with a format */
wb = NEW Infragistics.Excel.Workbook(lvfExcelFormat).
IF NOT VALID-OBJECT(wb) THEN
DO:
SESSION:SET-WAIT-STATE ("").
MESSAGE "Error: Unable to create a workbook object for export."
VIEW-AS ALERT-BOX.
RETURN.
END.
/* So far so good so create an exporter */
wsExporter = NEW Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter().
/* open an existing file */
IF lvcFileName <> "" THEN
DO:
/* load the file into the workbook object */
wb = Infragistics.Excel.Workbook:LOAD(lvcFileName) NO-ERROR.
IF GetErrorStatus("Export load error") THEN
RETURN.
_loop:
DO i = 0 TO wb:Worksheets:Count - 1:
/* locate an existing worksheet */
IF wb:Worksheets[i]:Name = lvcWorksheetName THEN
DO:
lvlWorkSheetFound = TRUE.
/* delete the existing worksheet */
wb:Worksheets:RemoveAt(i).
LEAVE _loop.
END.
END.
END.
ELSE /* create a new workbook */
ASSIGN
lvcFileName = lvcProgramName + lvcDefaultFileExtension.
/* add a worksheet to the work book */
wb:Worksheets:Add(lvcWorksheetName).
/* create a worksheet object from the workbook's new worksheet */
ws = wb:Worksheets[lvcWorksheetName].
IF NOT VALID-OBJECT(GetReferenceToUltragrid()) THEN
DO:
SESSION:SET-WAIT-STATE ("").
MESSAGE "Cannot export the data as the reference to the grid object is not valid"
VIEW-AS ALERT-BOX.
RETURN.
END.
/* export the grid contents to the worksheet */
wsExporter:Export(GetReferenceToUltragrid(), ws).
/* save the file */
wb:SAVE(lvcFileName) NO-ERROR.
IF GetErrorStatus("Export save error") THEN
RETURN.
SESSION:SET-WAIT-STATE ("").
/* open the default application for this file type */
System.Diagnostics.Process:Start(QUOTER(lvcFileName)).
END METHOD.
Thanks in advance for any help,
Mark.