When true (the default), the UltraGridExcelExport will attempt to convert grid formulas into equivalent formulas in the exported worksheet.
When false, the value of the cell in the grid at the time of the export will be exported directly to the value of the cells in the worksheet.
The exporting of formulas is done after all of the data in the grid has been exported. So when this property is set to true, the normal export process of exporting the values of the grid cells to the worksheet cells will still occur as normal, and the exporting of formulas will occur after all of the raw data exporting has completed.
Due to limitations of Excel and differences between Excel and UltraCalcManager behavior, some formulas may require the creation of a hidden worksheet which will point back to the main worksheet for it's data.
If the exporter fails to convert a formula for any reason, the FormulaExportError event will fire. By default, if the event is not handled, the error message will be written to the worksheet cell.
Imports System Imports System.Text Imports System.IO Imports Infragistics.Excel Imports Infragistics.Win Imports Infragistics.Win.UltraWinGrid Imports Infragistics.Win.UltraWinGrid.ExcelExport Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim exporter As New ExcelExporter(Me.ultraGrid1, "temp.xls") exporter.Export(Infragistics.Excel.WorkbookFormat.Excel97To2003, "ErrorLog.htm") End Sub Public Class ExcelExporter Private grid As UltraGrid = Nothing Private fileName As String = String.Empty Private logStream As FileStream = Nothing Private hasErrors As Boolean = False Public Sub New(ByVal grid As UltraGrid, ByVal fileName As String) MyBase.New() Me.grid = grid Me.fileName = fileName End Sub Public Function Export(ByVal workbookFormat As WorkbookFormat, ByVal log As String) As Boolean ' Clear the 'hasErrors' flag Me.hasErrors = False ' Create the log file stream if the caller wants to log the results If String.IsNullOrEmpty(log) = False Then Me.logStream = New FileStream(log, FileMode.Create, FileAccess.ReadWrite) ' Create a new instance of the UltraGridExcelExporter class. Dim excelExporter As New UltraGridExcelExporter() ' Set ExportFormulas to true ExcelExporter.ExportFormulas = True Try ' Handle the events that are fired when a formula is exported. AddHandler excelExporter.FormulaExporting, AddressOf Me.excelExporter_FormulaExporting AddHandler excelExporter.FormulaExported, AddressOf Me.excelExporter_FormulaExported AddHandler excelExporter.FormulaExportError, AddressOf Me.excelExporter_FormulaExportError AddHandler excelExporter.InitializeSummary, AddressOf Me.excelExporter_InitializeSummary If Not Me.logStream Is Nothing Then Me.WriteToLog("<HTML><BODY>") ' Export excelExporter.Export(Me.grid, Me.fileName, workbookFormat) If Not Me.logStream Is Nothing Then Me.WriteToLog("</BODY></HTML>") Catch Me.hasErrors = True Finally ' Detach the event handlers RemoveHandler excelExporter.FormulaExporting, AddressOf Me.excelExporter_FormulaExporting RemoveHandler excelExporter.FormulaExported, AddressOf Me.excelExporter_FormulaExported RemoveHandler excelExporter.FormulaExportError, AddressOf Me.excelExporter_FormulaExportError RemoveHandler excelExporter.InitializeSummary, AddressOf Me.excelExporter_InitializeSummary If Not Me.logStream Is Nothing Then Me.logStream.Close() Me.logStream.Dispose() End If end try Return Me.hasErrors = False End Function ' Handles the InitializeSummary event. Private Sub excelExporter_InitializeSummary(ByVal sender As Object, ByVal e As InitializeSummaryEventArgs) ' Format the log entry Dim logEntry As String = String.Format("Initializing summary '{0}' (Excel format string = '{1}', .NET format string = '{2}')...", e.Summary.Key, e.ExcelFormatStr, e.FrameworkFormatStr) ' Write the log entry Me.WriteToLog(logEntry, False) End Sub ' Handles the FormulaExporting event. Private Sub excelExporter_FormulaExporting(ByVal sender As Object, ByVal e As FormulaExportingEventArgs) ' Export the formula. e.Action = FormulaExportAction.ExportFormula ' Format the log entry Dim logEntry As String = String.Format("Exporting formula '{0}' for cell '{1}' (Context = {2})", e.GridFormula, e.WorksheetCell, e.Context) ' Write the log entry Me.WriteToLog(logEntry, False) End Sub ' Handles the FormulaExported event. Private Sub excelExporter_FormulaExported(ByVal sender As Object, ByVal e As FormulaExportedEventArgs) ' Format the log entry Dim logEntry As String = String.Format("Exported formula '{0}' for cell '{1}' (Context = {2})", e.GridFormula, e.WorksheetCell, e.Context) ' Write the log entry Me.WriteToLog(logEntry, False) End Sub ' Handles the FormulaExportError event. Private Sub excelExporter_FormulaExportError(ByVal sender As Object, ByVal e As FormulaExportErrorEventArgs) ' Record the error Me.hasErrors = True ' Suppress the error for subsequent instances of this formula e.Action = FormulaExportErrorAction.CancelAll ' Don't try to write out potentially invalid formulas e.ApplyInvalidFormulaIfPossible = False ' Write the error to the log Dim logEntry As String = String.Format("Error exporting grid formula '{0}' to excel formula '{1}' for cell '{2}' (Error = {3}, Description = '{4}')", e.GridFormula, e.ExcelFormula, e.WorksheetCell, e.Error, e.ErrorText) Me.WriteToLog(logEntry, True) End Sub Private Sub WriteToLog(ByVal s As String) Me.WriteToLog(s, False) End Sub Private Sub WriteToLog(ByVal s As String, ByVal isError As Boolean) Dim quote As String = Chr(34).ToString() If (isError) Then s = String.Format("{0}{1}{2}", quote + "<font color=" + quote + "#FF0000" + quote + ">", s, "</font>") s = String.Format("{0}{1}{2}", s, "<br>", Environment.NewLine) If Not Me.logStream Is Nothing Then Dim encoding As ASCIIEncoding = New ASCIIEncoding() Dim bytes As Byte() = encoding.GetBytes(s) Me.logStream.Write(bytes, 0, bytes.Length) End If End Sub End Class
using System; using System.Text; using System.IO; using Infragistics.Excel; using Infragistics.Win; using Infragistics.Win.UltraWinGrid; using Infragistics.Win.UltraWinGrid.ExcelExport; private void cmdExport_Click(object sender, EventArgs e) { ExcelExporter exporter = new ExcelExporter( this.ultraGrid1, "temp.xls" ); exporter.Export(Infragistics.Excel.WorkbookFormat.Excel97To2003, "ErrorLog.htm"); } public class ExcelExporter { private UltraGrid grid = null; private string fileName = string.Empty; private FileStream logStream = null; private bool hasErrors = false; public ExcelExporter(UltraGrid grid, string fileName) { this.grid = grid; this.fileName = fileName; } public bool Export(WorkbookFormat workbookFormat, string log ) { // Clear the 'hasErrors' flag this.hasErrors = false; // Create the log file stream if the caller wants to log the results if ( string.IsNullOrEmpty(log) == false ) this.logStream = new FileStream( log, FileMode.Create, FileAccess.ReadWrite ); // Create a new instance of the UltraGridExcelExporter class. UltraGridExcelExporter excelExporter = new UltraGridExcelExporter(); // Set ExportFormulas to true excelExporter.ExportFormulas = true; try { // Handle the events that are fired when a formula is exported. excelExporter.FormulaExporting += new EventHandler<FormulaExportingEventArgs>(this.excelExporter_FormulaExporting); excelExporter.FormulaExported += new EventHandler<FormulaExportedEventArgs>(this.excelExporter_FormulaExported); excelExporter.FormulaExportError += new EventHandler<FormulaExportErrorEventArgs>(this.excelExporter_FormulaExportError); excelExporter.InitializeSummary += new EventHandler<InitializeSummaryEventArgs>(this.excelExporter_InitializeSummary); if ( this.logStream != null ) this.WriteToLog( "<HTML><BODY>" ); // Export excelExporter.Export( this.grid, this.fileName, workbookFormat ); if ( this.logStream != null ) this.WriteToLog( "</BODY></HTML>" ); } catch { this.hasErrors = true; } finally { // Detach the event handlers excelExporter.FormulaExporting -= new EventHandler<FormulaExportingEventArgs>(this.excelExporter_FormulaExporting); excelExporter.FormulaExported -= new EventHandler<FormulaExportedEventArgs>(this.excelExporter_FormulaExported); excelExporter.FormulaExportError -= new EventHandler<FormulaExportErrorEventArgs>(this.excelExporter_FormulaExportError); excelExporter.InitializeSummary -= new EventHandler<InitializeSummaryEventArgs>(this.excelExporter_InitializeSummary); if ( this.logStream != null ) { this.logStream.Close(); this.logStream.Dispose(); } } return this.hasErrors == false; } // Handles the InitializeSummary event. private void excelExporter_InitializeSummary(object sender, InitializeSummaryEventArgs e) { // Format the log entry string logEntry = string.Format( "Initializing summary '{0}' (Excel format string = '{1}', .NET format string = '{2}')...", e.Summary.Key, e.ExcelFormatStr, e.FrameworkFormatStr ); // Write the log entry this.WriteToLog( logEntry, false ); } // Handles the FormulaExporting event. private void excelExporter_FormulaExporting(object sender, FormulaExportingEventArgs e) { // Export the formula. e.Action = FormulaExportAction.ExportFormula; // Format the log entry string logEntry = string.Format( "Exporting formula '{0}' for cell '{1}' (Context = {2})", e.GridFormula, e.WorksheetCell, e.Context ); // Write the log entry this.WriteToLog( logEntry, false ); } // Handles the FormulaExported event. private void excelExporter_FormulaExported(object sender, FormulaExportedEventArgs e) { // Format the log entry string logEntry = string.Format( "Exported formula '{0}' for cell '{1}' (Context = {2})", e.GridFormula, e.WorksheetCell, e.Context ); // Write the log entry this.WriteToLog( logEntry, false ); } // Handles the FormulaExportError event. private void excelExporter_FormulaExportError(object sender, FormulaExportErrorEventArgs e) { // Record the error this.hasErrors = true; // Suppress the error for subsequent instances of this formula e.Action = FormulaExportErrorAction.CancelAll; // Don't try to write out potentially invalid formulas e.ApplyInvalidFormulaIfPossible = false; // Write the error to the log string logEntry = string.Format( "Error exporting grid formula '{0}' to excel formula '{1}' for cell '{2}' (Error = {3}, Description = '{4}')", e.GridFormula, e.ExcelFormula, e.WorksheetCell, e.Error, e.ErrorText ); this.WriteToLog( logEntry, true ); } private void WriteToLog( string s ) { this.WriteToLog( s, false ); } private void WriteToLog( string s, bool isError ) { if ( isError ) s = string.Format("{0}{1}{2}", "<font color=\"#FF0000\">", s, "</font>"); s = string.Format("{0}{1}{2}", s, "<br>", Environment.NewLine); if ( this.logStream != null ) { ASCIIEncoding encoding = new ASCIIEncoding(); byte[] bytes = encoding.GetBytes( s ); this.logStream.Write( bytes, 0, bytes.Length ); } } }
Target Platforms: Windows 10, Windows 8.1, Windows 8, Windows 7, Windows Server 2012, Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2