This post includes a series of interesting support tickets answered throughout October and November along with a handful of enhancements made to our Office-inspired product line. We hope you find the contents of this post interesting and of business value. Should you have any questions about this post, feel free to comment below.
Breaking Change in .NET 6
As you may already know, Microsoft changed DeflateStream, GZipStream, and CryptoStream behaviors in .NET 6. See the following breaking change for more information: DeflateStream, GZipStream, and CryptoStream handling of partial and zero-byte reads.
This breaking change affects our Office File API and Office-inspired UI controls (for WinForms and WPF) as we use DeflateStream to import OpenXML documents and load data from ZIP archives. If your application targets .NET 6, you may encounter the following issues:
- Your Word (DOCX, DOTX, DOCM, or DOTM) and Excel (XLSX, XLSM, XLTX, or XLTM) documents may be loaded incorrectly.
- The
ZipItem.Extract method can throw CrcErrorArchiveItemException.
Please upgrade to v21.1.7+ or v21.2.4+ to avoid/address these issues within your .NET 6 project (see Bug Report T1047593).
Tips & Tricks
WinForms and WPF Rich Text Editors
WinForms and WPF Spreadsheet Controls
T1034923 - How to display a message that prompts a user to save changes before the current spreadsheet document is closed
https://supportcenter.devexpress.com/ticket/details/t1034923
Handle the SpreadsheetControl.DocumentClosing event and use the SpreadsheetControl.Modified property within the event handler to check whether the current document has changes. We use this method in our demos for the WinForms and WPF Spreadsheet controls. The following code snippet from our demos demonstrates how to display a prompt message before the document is closed:
void spreadsheetControl1_DocumentClosing(object sender, CancelEventArgs e)
{
if (spreadsheetControl1.Modified)
{
string currentFileName = spreadsheetControl1.Options.Save.CurrentFileName;
string message = !string.IsNullOrEmpty(currentFileName) ?
$"Do you want to save the changes you made for '{currentFileName}'?" :
"Do you want to save the changes?";
DialogResult result = XtraMessageBox.Show(message, "Warning",
MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
e.Cancel = !spreadsheetControl1.SaveDocument();
}
else e.Cancel = result == DialogResult.Cancel;
}
}
T1036481 - How to create total bars for a Waterfall chart via the Spreadsheet control’s UI
https://supportcenter.devexpress.com/ticket/details/t1036481
T1038552 - How to exclude chart types from the Insert Chart ribbon menu in the WPF Spreadsheet control
https://supportcenter.devexpress.com/ticket/details/t1038552
T1039579 - How to exclude chart types from the Change Chart Type dialog in the WPF Spreadsheet control
https://supportcenter.devexpress.com/ticket/details/t1039579
T1045409 - How to keep hidden columns invisible when the AutoFitColumns method is executed
https://supportcenter.devexpress.com/ticket/details/t1045409
Create a union range with only visible columns and call the AutoFitColumns extension method for this range.
private static void AutoFitVisibleColumns(Worksheet worksheet,
int startColumn, int endColumn)
{
var range = worksheet.Range.FromLTRB(startColumn, 0, endColumn, 0);
for (int i = startColumn; i <= endColumn; i++)
{
if (!worksheet.Columns[i].Visible)
range = range.Exclude(worksheet.Columns[i]);
}
range.AutoFitColumns();
}
T1045447 - How to prevent export of filtered rows when a document is saved in XLSX or XLS format
https://supportcenter.devexpress.com/ticket/details/t1045447
Iterate through worksheet rows and check the Row.Visible and Row.IsFiltered properties. If a row is hidden and belongs to a filtered range, call the Worksheet.Rows.Remove method to delete this row from the worksheet.
spreadsheetControl.BeginUpdate();
CellRange dataRange = worksheet.GetDataRange();
int rowCount = 0;
int lastRowIndex = dataRange.BottomRowIndex;
for (int rowIndex = lastRowIndex; rowIndex >= 0; rowIndex--)
{
Row row = worksheet.Rows[rowIndex];
if (!row.Visible && row.IsFiltered)
rowCount++;
else
{
if (rowCount > 0)
worksheet.Rows.Remove(rowIndex + 1, rowCount);
rowCount = 0;
}
}
if (rowCount > 0)
worksheet.Rows.Remove(0, rowCount);
spreadsheetControl.EndUpdate();
spreadsheetControl.SaveDocument("Result.xlsx", DocumentFormat.Xlsx);
WinForms PDF Viewer
WinForms Spell Checker
Enhancements
Spreadsheet Document API and Spreadsheet Controls (for WinForms and WPF)
T1042763 - We implemented new API properties that allow you to link shape text to a cell
https://supportcenter.devexpress.com/ticket/details/t1042763
You can now use the Shape.ShapeText.Formula property to specify a reference to the cell that contains text you want to display in your shape. Use the A1 or R1C1 cell reference type depending on reference style used in your workbook (see DocumentSettings.R1C1ReferenceStyle). External references are not supported.
// Specify cell value.
worksheet["B2"].Value = "Shape Text";
// Create rectangle.
var rectangle = worksheet.Shapes.AddShape(ShapeGeometryPreset.Rectangle,
worksheet["B4:D7"]);
// Link shape text to the "B2" cell.
rectangle.ShapeText.Formula = "=Sheet1!B2";
The following image demonstrates the result:
You can also use a defined name that refers to the required cell.
// Specify cell value.
worksheet["B2"].Value = "Shape Text";
// Create defined name for the "B2" cell.
worksheet.DefinedNames.Add("cellB2", "Sheet1!$B$2");
// Create rectangle.
var rectangle = worksheet.Shapes.AddShape(ShapeGeometryPreset.Rectangle,
worksheet["B4:D7"]);
// Use defined name to link shape text to the "B2" cell.
rectangle.ShapeText.Formula = "=cellB2";
The Shape.ShapeText.IsLinked property returns true if shape text is linked to a cell. Shape text is updated automatically when a value in the referenced cell changes or document formulas are recalculated.
T1041398 - We added a new WorksheetView.GridlineColor property to specify the color of worksheet gridlines
https://supportcenter.devexpress.com/ticket/details/t1041398
The following code snippet changes the gridline color to red:
workbook.Worksheets[0].ActiveView.GridlineColor = Color.Red;
Assign Color.Empty to the WorksheetView.GridlineColor property to restore default gridline color. Transparent or semitransparent colors are not supported.
PDF Document API
T1037054 - We implemented a new PdfDocumentProcessor.DataRecognitionCacheSize property that allows you to set cache size (in pages) for data recognition
https://supportcenter.devexpress.com/ticket/details/t1037054
Use the PdfDocumentProcessor.DataRecognitionCacheSize property to increase the number of pages with cached data. This may be useful if you need to accelerate FindText method execution on documents larger than 65 pages (the default cache size is 65 pages).
If the DataRecognitionCacheSize property is set to 0, cache size is unlimited.
pdfDocumentProcessor.DataRecognitionCacheSize = 100;
T1047842 – We added new APIs to create a form field with multiple widgets
https://supportcenter.devexpress.com/ticket/details/t1047842
You can now add widgets to a text box (PdfAcroFormTextBoxField), check box (PdfAcroFormCheckBoxField), and list box/combo box (the PdfAcroFormChoiceField class descendants).
Use the following methods to manage a form field’s widgets:
-
AddWidget(PdfRectangle rectangle)
Adds a widget to the page where the form field is located.
-
AddWidget(int pageNumber, PdfRectangle rectangle)
Adds a widget to a specific page.
-
ClearWidgets()
Removes existing widgets from the form field.
We also implemented a new AddButton(string name, int pageNumber, PdfRectangle rect) method for the PdfAcroFormRadioGroupField class to append an additional radio button to a radio group field.
WinForms and WPF PDF Viewers
Note: Add the DevExpress.Docs.v21.2.dll assembly to your project to use the PDF Facade API. Please note that you need an active DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.
New Examples: WinForms and WPF Spreadsheet Controls
We created two examples that demonstrate the use of the DevExpress Spell Checker component (to check spelling in our WinForms and WPF Spreadsheet controls). When a user enters text in a cell, the spell checker indicates misspelled words with a red wavy line. A user can right-click an underlined word and select the correct spelling from the list of suggestions or invoke the Spelling dialog.
Your Feedback Matters
As always, if you’ve come across an interesting support ticket you’d like to share with the rest of the DevExpress developer community, please comment below and include the appropriate link.
Free DevExpress Products - Get Your Copy Today
The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the
DevExpress Support Center at your convenience. We'll be happy to follow-up.