How to set background color for a entire worksheet ?
This article explains how to set background color for entire worksheet in XlsIO using C#/VB.NET.
How to set background color for entire worksheet?
To set background color for entire worksheet, we need to create a style in workbook and apply it to the entire worksheet.
The following methods in XlsIO is used to set default styles for row or column in a worksheet.
S.No | Method | Usage |
1 | void SetDefaultColumnStyle(int iColumnIndex, IStyle defaultStyle); | Sets the default style for the specified column. |
2 | void SetDefaultColumnStyle(int iStartColumnIndex, int iEndColumnIndex, IStyle defaultStyle); | Sets the default style for the columns between the start and end index specified. |
3 | void SetDefaultRowStyle(int iRowIndex, IStyle defaultStyle); | Sets the default style for the specified row. |
4 | void SetDefaultRowStyle(int iStartRowIndex, int iEndRowIndex, IStyle defaultStyle); | Sets the default style for the rows between the start and end index specified. |
Note: When applying the styles for the entire worksheet, it is preferable to use SetDefaultColumnStyle(int iStartColumnIndex, int iEndColumnIndex, IStyle defaultStyle). Because the SetDefaultRowStyle(int iStartRowIndex, int iEndRowIndex, IStyle defaultStyle) will cause performance issue as the number of rows in higher than the number columns.
Steps to set color for the entire worksheet
- Set workbook version for the workbook. Because, here we use maximum number columns which will be different based on the workbook version.
workbook.Version = ExcelVersion.Excel2016;
- Create a new style and add it to the styles in the workbook.
//Add a new style to the workbook named "FillColor"
IStyle style = workbook.Styles.Add("FillColor");
- Set background color for the new style.
//Background color to be applied is stored in the style object
style.ColorIndex = ExcelKnownColors.Yellow;
- Apply the style to the worksheet using IWorksheet.SetDefaultColumnStyle(Int iStartColumnIndex,iEndColumnIndex,IStyle defaultStyle) method. Here, startIndex is 1 and the endIndex is the maximum number column in the worksheet.
//For each column in the sheet, default column style is set
worksheet.SetDefaultColumnStyle(startIndex, endIndex, style);
To know more about sheet or range formatting in XlsIO, please refer the documentation.
The following C#/VB.NET complete code snippet shows how to set color for entire worksheet in XlsIO.
using Syncfusion.XlsIO;
using System.IO;
namespace XlsIO_Sample
{
class Program
{
public static void Main(string[] args)
{
//Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel2016;
IWorksheet worksheet = workbook.Worksheets[0];
int startIndex = 1;
int endIndex = workbook.MaxColumnCount;
//Add a new style to the workbook named "FillColor"
IStyle style = workbook.Styles.Add("FillColor");
//Background color to be applied is stored in the style object
style.ColorIndex = ExcelKnownColors.Yellow;
//For each column in the sheet, default column style is set
worksheet.SetDefaultColumnStyle(startIndex, endIndex, style);
//Save and close the workbook
Stream stream = File.Create("Output.xlsx");
workbook.SaveAs(stream);
}
}
}
}
Imports Syncfusion.XlsIO
Imports System.IO
Namespace XlsIO_Sample
Class Program
Public Shared Sub Main(ByVal args As String())
'Instantiate the spreadsheet creation engine
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
Dim workbook As IWorkbook = application.Workbooks.Create(1)
'Set workbook version
workbook.Version = ExcelVersion.Excel2016
Dim worksheet As IWorksheet = workbook.Worksheets(0)
Dim startIndex As Integer = 1
Dim endIndex As Integer = workbook.MaxColumnCount
'Add a new style to the workbook named "FillColor"
Dim style As IStyle = workbook.Styles.Add("FillColor")
'Background color to be applied is stored in the style object
style.ColorIndex = ExcelKnownColors.Yellow
'For each column in the sheet, default column style is set
worksheet.SetDefaultColumnStyle(startIndex, endIndex, style)
'Save and close the workbook
Dim stream As Stream = File.Create("Output.xlsx")
workbook.SaveAs(stream)
End Using
End Sub
End Class
End Namespace
The following screenshot shows the output generated by XlsIO after applying color for entire worksheet.
