string fileName = "file.xlsm";
Telerik.Windows.Documents.Spreadsheet.Model.Workbook workbook;
IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsm.XlsmFormatProvider();
using (Stream input = new FileStream(fileName, FileMode.Open))
{
workbook = formatProvider.Import(input);
}
Add support for images embedded in cells.
Similar to clicking "Place in Cell' in MS Excel which embeds the floating image into the cell:
Alternative: Floating Images
I am using .net 7. I originally hit it with Telerik.UI.for.Wpf.70.Xaml.2023.3.1114. I updated to Telerik.UI.for.Wpf.70.Xaml.2024.1.423 to see if it was fixed but it still appears to happen.
I have attached a sample program which shows the behavior. Original.xlsm was created in Excel and has an image embedded in cell A1. Call ImportWorkbook with the path to the original file. Then export that workbook (unchanged) to the destination file.
Workbook workbook = ImportWorkbook( originalFileName ); ExportWorkbook( workbook, destinationFileName );
private Workbook ImportWorkbook( string fileName )
{
Telerik.Windows.Documents.Spreadsheet.Model.Workbook workbook;
IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsm.XlsmFormatProvider();
using( Stream input = new FileStream( fileName, FileMode.Open ) )
{
workbook = formatProvider.Import( input );
}
return workbook;
}
private void ExportWorkbook( Workbook workbook, string fileName )
{
Telerik.Windows.Documents.Spreadsheet.FormatProviders.IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsm.XlsmFormatProvider();
using( Stream output = new FileStream( fileName, FileMode.Create ) )
{
formatProvider.Export( workbook, output );
}
}
Incorrectly resolved fill between local formatting and cell style.
A fill (fillId) of a cell style (cellStyleXfs) is respected instead of the fill (fillId) of the local formatting (cellXfs).
Use the below code snippet to generate XLSX document and export it. You will notice that the export operation is extremely slow:
Stopwatch sw = new Stopwatch();
sw.Start();
IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets.Add();
worksheet2.Name ="Days";
List<string> weekdays = new List<string>() { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"};
for (int i = 0; i < 7; i++)
{
worksheet2.Cells[0, i].SetValue(weekdays[i]);
}
for (int i = 0; i < 200; i++)
{
for (int j = 0; j < 10; j++)
{
CellIndex cellIndex = new CellIndex(i, j);
CellSelection selection = worksheet.Cells[cellIndex];
selection.SetValue("Wednesday");
var context = new ListDataValidationRuleContext(worksheet, cellIndex)
{
InputMessageTitle = "InputMessageTitle",
InputMessageContent = "InputMessageTitle"
};
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "ErrorAlertTitle";
context.ErrorAlertContent = "ErrorAlertContent";
context.InCellDropdown = true;
context.Argument1 = "=Days!A0:A6"; //"Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday";
ListDataValidationRule rule = new(context);
worksheet.Cells[cellIndex].SetDataValidationRule(rule);
}
}
string outputFile = @"..\..\..\output.xlsx";
File.Delete(outputFile);
using (Stream output = new FileStream(outputFile, FileMode.Create))
{
formatProvider.Export(workbook, output);
}
sw.Stop();
Debug.WriteLine("Export " + sw.ElapsedMilliseconds);
Import an XLSX document and auto-fit the columns. Then, export the document to XLSX format.
Even though a SpreadFixedTextMeasurer is applied and a FontProvider is implemented, the columns are not wide enough to fit the content:
XIRR function returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.
https://support.microsoft.com/en-gb/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d
The charts have a display blanks as property which is represented by the dispBlanksAs element. The absence of this element sometimes makes drastic difference in the way a chart might look. E.g. the following chart has a gap in its data and depending on what value dispBlanksAs has, it looks very different.
Incorrect calculation of UsedCellRange when conditional formatting is applied to a large cell range.
Workaround:
var usedCellRange = workbook.ActiveWorksheet.GetUsedCellRange(
CellPropertyDefinitions.AllPropertyDefinitions
.Except(
CellPropertyDefinitions.AllPropertyDefinitions.Where(p => p.Name == "DataValidationRule" || p.Name == "ConditionalFormatting")));