Exporting sheet properties corrupts the file and it cannot be opened in Excel.
In the latest version we export:
<sheetPr>
<pageSetUpPr fitToPage="true" />
<tabColor rgb="FF92D050" />
</sheetPr>
Excel cannot read this and tries to recover the file unsuccessfully. This works in R3 2020.
When a csv is exported the entire range which has values is exported. When export is done in Excel, the cells of this range which are empty are marked by an additional field, separated by a comma. This comma is present in SpreadProcessing only in places where it is obligatory for example in the middle of the row. Obligatory comma in this context means that without it the file will not be read correctly by Excel or SpreadProcessing. In places where it is not obligatory, like in an empty row or in the end of the row, it is skipped. These non-obligatory commas do not influence how the file will be interpreted by Excel, however, according to the most-popular csv spec, they should be present, which means that there could be parsers which are unable to parse the resulting file. (2.4 Each line should contain the same number of fields throughout the file.) Example: Exported by Excel: --------------------------------------- Product,Unit Price,Units in Stock,Discontinued Chai,$18.00 ,39, Chang,,17, Chef Anton's Cajun Seasoning,$22.00 ,53,No ,,, Chef Anton's Gumbo Mix,$21.35 ,0,No --------------------------------------- Exported by SpreadProcessing: --------------------------------------- Product,Unit Price,Units in Stock,Discontinued Chai,$18.00 ,39 Chang,,17 Chef Anton's Cajun Seasoning,$22.00 ,53,No Chef Anton's Gumbo Mix,$21.35 ,0,No --------------------------------------- Note the extra commas in the empty row and in the end of the first two rows in the SpreadProcessing sample.
The files are not properly recognized when the base64 string representing the exported XLS file is converted using https://base64.guru/converter/decode/file.
If an XLS file produced by MS Excel is converted with the same tool, it is generated with .bin extension.
When the column has cells that span several rows, the auto fit of column width is not working.
Workaround: If the document is being generated with the API, autofit the contents before merging the data.
One of the options grouped under alignment is Shrink to Fit. Shrink to fit will automatically reduce the font size until text fits in a cell.
Importing XLSX file in Blazor Web Assembly for .NET 5.0 is very slow.
<InputFile OnChange="@HandleFileSelected" />
async Task HandleFileSelected(InputFileChangeEventArgs e)
{
var ms = new MemoryStream((int)e.File.Size);
Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
using (Stream ey = e.File.OpenReadStream(e.File.Size))
{
await ey.CopyToAsync(ms,(int)e.File.Size);
ey.Close();
}
oWorkbook = formatProvider.Import(ms);
}
Hello,
I am writing a piece of code in VB that converts Excel files that a user uploads into PDF files. It merges all of them into one fixed document and exports that fixed document to a PDF. Whenever I upload specifically the "Schedule" default layout from Google Sheets, exported to xlsx format, all my times are changed from 9:30 AM or 1:28 PM to 9:12 AM and 1:12 PM respectively.
Reproduction Steps:
- Make a new sheet on Google Sheets using the "Schedule" layout.
- Export it to .xlsx
- Read the file into a spreadsheet, convert that spreadsheet to a PDF, save it, and open the PDF.
- All of the times should be incorrectly written.
A few key notes that may help with resolving the issue :
- The conversion from .xlsx to a Workbook datatype does not have any issues. When importing to Workbook then exporting to XLSX, the sheets remained unchanged.
- MS Excel does not have any issues viewing the exported XLSX file with the correct times, which means it is most likely a problem with the ExportToFixedDocument process.
- Writing times in the same format manually and exporting it from Google Sheets does not create this bug. So far, I have only found that the "Schedule" layout will cause this issue.
Below I have written a snippet of my code, as well as included the xlsx file and the pdf file that results from it. I am using one of the latest versions of Telerik Document Processing, having been downloaded on 6/25/2021.
Case".xlsx"
Using infileStream As Stream = File.OpenRead(inputFilePath)
Dim inputFormatter AsNew Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider
Dim imported As Spreadsheet.Model.Workbook = inputFormatter.Import(infileStream)
imported.ActiveWorksheet.WorksheetPageSetup.PaperType = Model.PaperTypes.Letter
imported.ActiveWorksheet.WorksheetPageSetup.CenterHorizontally = True
imported.ActiveWorksheet.WorksheetPageSetup.FitToPages = TrueDim format AsNew Spreadsheet.FormatProviders.Pdf.PdfFormatProvider
Dim fixedDoc As Fixed.Model.RadFixedDocument = format.ExportToFixedDocument(imported)
primaryDoc.Merge(fixedDoc)
EndUsing
Add support for preserving and exporting the macros in a macro-enabled workbook. Macros are pieces of code written in Visual Basic for Applications (VBA) which are used to automate repetitive tasks in excel. The macros are part of the excel file. When a file contains a macro its extension becomes xlsm. SpreadProcessing should be able to import and export xlsm files and edit the macros in them.