See how to use the new import feature of the RadSpreadStreamProcessing library.
For a long time, we have supported creating files with the RadSpreadStreamProcessing library. This library is used particularly for large files. The key part is that it does not require all the data to be loaded in the application memory to create the document. This significantly reduces the memory usage of the application and allows for handling large amounts of data. This is achieved by directly writing the data in the file.
In the R3 2022 Release, we have added the ability for reading XLSX or CSV files with this library as well. This feature uses the same approach and reads the cells and properties consecutively (as they appear in the document structure). This way, only one cell is stored at a time in the application, which reduces the memory footprint.
Besides cell values, you can read the cell format and styles. You can read the Row/Column properties as well. Check out the Import section of the RadSpreadStreamProcessing online documentation.
In the example below, we will use both features (reading and writing). In this scenario, we will have a very large XLSX file, and we want to use only some of the data inside. We will get the data depending on some cell properties. As we are reading the data, we will simultaneously write it in a CSV file. The resulting file will contain only the filtered data.
using Telerik.Documents.SpreadsheetStreaming;
string source = @"..\..\..\source.xlsx";
string result = @"..\..\..\result.csv";
using (FileStream sourceFile = File.OpenRead(source))
{
using (FileStream resultFile = File.OpenWrite(result))
{
using (IWorkbookImporter workBookImporter = SpreadImporter.CreateWorkbookImporter(SpreadDocumentFormat.Xlsx, sourceFile))
{
using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Csv, resultFile))
{
using (IWorksheetImporter worksheetImporter = workBookImporter.WorksheetImporters.First())
{
using (IWorksheetExporter worksheetExporter = workbookExporter.CreateWorksheetExporter("My sheet"))
{
foreach (IRowImporter rowImporter in worksheetImporter.Rows)
{
// Skip the header rows
List<string> values = new List<string>();
foreach (ICellImporter cellImporter in rowImporter.Cells)
{
if (cellImporter.RowIndex < 3)
{
continue;
}
string value = cellImporter.Value;
if (cellImporter.ColumnIndex <= 6 && !string.IsNullOrEmpty(value))
{
values.Add(value);
}
// Status column which is a color
else if (cellImporter.ColumnIndex == 7)
{
var fill = cellImporter.Format.Fill as SpreadPatternFill;
SpreadColor color = fill.BackgroundColor.LocalValue;
// Export the rows with Green status only
if (color.R == 142 && color.G == 188 && color.B == 0)
{
using (IRowExporter rowExporter = worksheetExporter.CreateRowExporter())
{
foreach (var item in values)
{
using (ICellExporter cellExporter = rowExporter.CreateCellExporter())
{
cellExporter.SetValue(item);
}
}
}
}
}
}
}
}
}
}
}
}
}
I hope this example is useful. Converting the opposite scenario (CSV to XLSX) is supported as well and can be easily achieved.
No matter if you are already familiar with Telerik Document Processing or will meet the libraries for the first time, hurry up and get the latest bits so you can take advantage of the different document management possibilities they provide:
And hopefully you know by now that your input is valuable—we do listen. So, do not be shy and drop us a line to share your feedback in the comments section below or directly in our Document Processing Libraries Feedback Portal.
Dimitar Karamfilov is a Support Officer in the UI for WinForms team. He joined Telerik after graduating from the Telerik Academy in 2013. Apart from work he likes outdoor activities and reading philosophy literature.