IWorkbookExporter?

[Joel Palmer]

You've officially made things confusing by having the different libraries for the spreadsheet.  I need a good (current) example on how to do an easy import and export of a Spreadsheet that I am using/showing in a Spreadsheet Control.  Also, I'll be saving the spreadsheet to a SQL table so do you have an example where I don't have to write out to a FileStream first?  I'd like to go directly from a Stream to a byte array without writing to the file system.

[Joel Palmer]

I found the information on a blog.  Your documentation needs to be more thorough and should require an additional link to a blog.  I am now getting an error I need your insight to resolve.  I cannot attach an .xlsx file to this forum so you need to give me some tools to get you the file that errors.

 

I use this code to import from a file:

Microsoft.Win32.SaveFileDialog dlg =
    new Microsoft.Win32.SaveFileDialog();

dlg.DefaultExt = ".xlsx";
dlg.Filter = "Excel | *.xlsx";
dlg.Title = "Identify Location";
dlg.InitialDirectory =
    Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

dlg.CheckFileExists = false;
dlg.CheckPathExists = true;
dlg.FilterIndex = 2;
dlg.RestoreDirectory = true;

bool? dlgRst = dlg.ShowDialog();

if (dlgRst == true)
{
    FileInfo file =
        new FileInfo(dlg.FileName);

    XlsxFormatProvider provider =
        new XlsxFormatProvider();

    using (Stream stream = new FileStream(file.FullName, FileMode.CreateNew, FileAccess.Write))
    {
        provider.Export(reportExcelControl.Workbook, stream);
    }
}

 

However, the import action corrupts the file... it can no longer be opened in Excel and its size changes to 0:

Telerik.Windows.Zip.InvalidDataException: Archive corrupted ---> System.IO.IOException: An attempt was made to move the file pointer before the beginning of the file.

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.SeekCore(Int64 offset, SeekOrigin origin)
   at System.IO.FileStream.Seek(Int64 offset, SeekOrigin origin)
   at Telerik.Windows.Zip.ZipArchive.ReadEndOfCentralDirectory()
   --- End of inner exception stack trace ---
   at Telerik.Windows.Zip.ZipArchive.ReadEndOfCentralDirectory()
   at Telerik.Windows.Zip.ZipArchive.Init(Stream stream, ZipArchiveMode mode, Boolean leaveOpen)
   at Telerik.Windows.Zip.ZipArchive..ctor(Stream stream, ZipArchiveMode mode, Boolean leaveOpen, Encoding entryNameEncoding, CompressionSettings compressionSettings, EncryptionSettings encryptionSettings)
   at Telerik.Windows.Documents.FormatProviders.OpenXml.OpenXmlImporter`1.Import(Stream input, IOpenXmlImportContext context)
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider.ImportOverride(Stream input)
   at Telerik.Windows.Documents.Spreadsheet.FormatProviders.WorkbookFormatProviderBase.Import(Stream input)
   at Hpt.Product.Measurement.UC.MeasurementReportUC.btnImportFromFile_Click(Object sender, RoutedEventArgs e) in C:\HPT\Measurements\HPT.Product.Measurement.UC\MeasurementReportUC.xaml.cs:line 219

 

System.IO.IOException: An attempt was made to move the file pointer before the beginning of the file.

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.SeekCore(Int64 offset, SeekOrigin origin)
   at System.IO.FileStream.Seek(Int64 offset, SeekOrigin origin)
   at Telerik.Windows.Zip.ZipArchive.ReadEndOfCentralDirectory()

 

Thanks in advance for your help,

Joel

[Joel Palmer]

Sorry, the code I posted was for my export method.  Here is the import code:

FileInfo file =
    FileSystem.GetFile(
        "Select File",
        Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
        "*.xlsx",
        "Excel | *.xlsx");

if (file != null)
{
    XlsxFormatProvider provider =
        new XlsxFormatProvider();

    reportExcelControl.Workbook =
        provider.Import(file.Create());
}

[Nikolay Demirev]

Hi Joel,

The RadSpreadsheet is the UI component which is used for visualizing spreadsheet documents. Those documents are stored in the memory of the control using the Workbook class. This class is the root of the RadSpreadProcessing library. That means RadSpreadsheet is the UI component and RadSpreadProcessing is its model.

As for the RadSpreadStreamProcessing, this is a library that is used only for exporting spreadsheet documents. It is very fast and it has very low memory footprint. This is achieved by removing the need of in-memory representation of the document, by exporting all values at the moment they are set.

As you need to import and export documents, the only solution available is RadSpreadProcessing, because the RadSpreadStreamProcessing is used only for export.

If I understand you correctly, in order to save the document in the SQL database, you need it to be exported to a byte array and after that imported from a byte array. Here is a code snippet which you could use to export the Workbook to a byte array:

XlsxFormatProvider provider = new XlsxFormatProvider();
byte[] array = provider.Export(workbook);

Here a code snippet you could use to import the document from a byte array:

XlsxFormatProvider provider = new XlsxFormatProvider();
Workbook workbook = provider.Import(array);

Looking at your code, there is nothing wrong with the export you have created and it should work. As for the code where you are trying to import the workbook, it is crashing because you are passing to the Import method of the format provider the result of file.Create(), which method creates a new empty file and returns the stream to it. The library can not import empty files or files which are not valid XLSX files.

Here is a code snippet showing how to import file from the file system:

Workbook workbook = null;
using (Stream stream = File.OpenRead("path to the file"))
{
    XlsxFormatProvider provider = new XlsxFormatProvider();
    workbook = provider.Import(stream);
}

We have an article that explains how to import and export a workbook.

If you have issues importing a specific document, you could submit a support ticket where you can attach files as a zip archive.

Regards,
Nikolay Demirev
Telerik by Progress