This module uses the DevExpress Spreadsheet API to change cell formatting in Excel documents. You can process the predefined sample file or supply your own document. To do the latter, select Upload a File in the file selection drop-down menu.
Configure cell formatting options in the Formatting Settings section: select font name, size, and style; choose text and background colors; and set horizontal and vertical alignment. Select Create Custom Style to apply these formatting settings as a custom cell style.
Use the Apply Formatting and Save as ... dropdown button to select the output format, apply formatting, and download the result.
Select a Document
DocumentForProtection.xlsx
Formatting Settings
using DevExpress.Spreadsheet;
using System.Drawing;
Stream FormatWorkbook(Stream inputStream, bool createNewStyle, string fontName, Color fontColor, double fontSize,
SpreadsheetFontStyle fontStyle, Color backgroundColor, SpreadsheetHorizontalAlignment horizontalAlignment,
SpreadsheetVerticalAlignment verticalAlignment, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
//Get the data range of the active worksheet
CellRange dataRange = workbook.Worksheets.ActiveWorksheet.GetDataRange();
if(createNewStyle) {
Style customStyle = CreateCustomStyle(workbook, fontName, fontColor, fontSize, fontStyle, backgroundColor, horizontalAlignment, verticalAlignment);
dataRange.Style = customStyle;
}
else {
ApplyDirectFormatting(dataRange, fontName, fontColor, fontSize, fontStyle, backgroundColor, horizontalAlignment, verticalAlignment);
}
dataRange.AutoFitColumns();
// Create output stream
Stream outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Style CreateCustomStyle(Workbook workbook, string fontName, Color fontColor, double fontSize, SpreadsheetFontStyle fontStyle,
Color backgroundColor, SpreadsheetHorizontalAlignment horizontalAlignment, SpreadsheetVerticalAlignment verticalAlignment) {
// Add a new style under the "Custom Style" name to the style collection
Style customStyle = workbook.Styles.Add("Custom Style");
// Specify the style's format characteristics.
customStyle.BeginUpdate();
try {
// Specify font settings (font name, color, size and style)
customStyle.Font.Name = fontName;
customStyle.Font.Color = fontColor;
customStyle.Font.Size = fontSize;
customStyle.Font.FontStyle = fontStyle;
// Set the background fill
customStyle.Fill.BackgroundColor = backgroundColor;
// Specify text alignment in cells
customStyle.Alignment.Horizontal = horizontalAlignment;
customStyle.Alignment.Vertical = verticalAlignment;
}
finally {
customStyle.EndUpdate();
}
return customStyle;
}
void ApplyDirectFormatting(CellRange cellRange, string fontName, Color fontColor, double fontSize, SpreadsheetFontStyle fontStyle,
Color backgroundColor, SpreadsheetHorizontalAlignment horizontalAlignment, SpreadsheetVerticalAlignment verticalAlignment) {
// Begin updating of the range formatting
DevExpress.Spreadsheet.Formatting formatting = cellRange.BeginUpdateFormatting();
try {
// Specify font settings (font name, color, size and style)
formatting.Font.Name = fontName;
formatting.Font.Color = fontColor;
formatting.Font.Size = fontSize;
formatting.Font.FontStyle = fontStyle;
// Specify cell background color
formatting.Fill.BackgroundColor = backgroundColor;
// Specify text alignment in cells
formatting.Alignment.Horizontal = horizontalAlignment;
formatting.Alignment.Vertical = verticalAlignment;
}
finally {
cellRange.EndUpdateFormatting(formatting);
}
}