true の場合、UltraGridExcelExport はグリッド数式はエクスポート先のワークシートで相当する数式に変換されます。
false に設定された場合、エクスポート時のグリッドのセル値はワークシートのセル値に直接エクスポートされます。
グリッドのすべてのデータがエクスポートされた後に数式がエクスポートされます。このプロパティが true に設定された場合、グリッド セルの値はワークシートのセルへエクスポートされます。数式のエクスポートは生データのエクスポートが完了したあとに実行されます。
Excel の制限および Excel とUltraCalcManager の動作の違いにより、メインのワークシートのデータへポイントするための隠しワークシートを作成する必要のある数式があります。
何らか理由でエクスポーターが数式の変換に失敗した場合、FormulaExportError イベントが発生します。デフォルトでは、イベントは処理されません。エラーメッセージがワークシート セルに書き込まれます。
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 ); } } }