how to style whole worksheet

[patrick551]

how do you style a whole worksheet before enter data.

For example, I would like to use the font 'Arial Narrow' for the whole worksheet, or paint every cell background with grey. There doesn't seem to be a way to do it.

Thanks for your help.

[Lisa.Li]

Hello,

Thanks for your inquiry.
Sorry that our Spire.XLS doesn't support styling a whole worksheet. However, our product supports styling a special cell range of a worksheet like the following code snippet.

Code: Select all

......
Worksheet worksheet = workbook.Worksheets[0];
//set background color for cells from A1 to H10
worksheet.Range["A1:H10"].Style.Color = Color.Gray;
//set font for cells from A1 to H10
worksheet.Range["A1:H10"].Style.Font.FontName = "Arial Narrow";


Besides, you also can try the following sample code to style the used cell range of a worksheet. If there is any question, welcome to write back.

Code: Select all

......
Worksheet worksheet = workbook.Worksheets[0];
//set background color for used cell range
worksheet.AllocatedRange.Style.Color = Color.Gray;
//set font for used cell range
worksheet.AllocatedRange.Style.Font.FontName = "Arial Narrow";


Sincerely,
Lisa
E-iceblue support team

[patrick551]

I was hoping there are ways to set up the worksheet before I know what cells are going to be used. The methold you suggested I think is what most people eventually used given the way the program works. Thanks for the reply.

[Lisa.Li]

Hello,

Thanks for your feedback.
As for styling a whole worksheet, we will consider adding it as a new feature into our upgrade list, if it can be achieved in the future, we will let you know.

Sincerely,
Lisa
E-iceblue support team

[Lisa.Li]

Hello,

Thanks for waiting.
We just released the Spire.XLS Pack(Hotfix) Version:9.9.5 which adds a method to apply the style for a whole worksheet. Welcome to download it and refer to the following code snippet to have a test.
Our website link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
NuGet link: https://www.nuget.org/packages/Spire.XLS/9.9.5

Code: Select all

......
CellStyle style = book.Styles.add("newStyle");
style.Color = Color.Pink;
style.Font.Size = 15;
book.Worksheets[0].ApplyStyle(style);
......


Sincerely,
Lisa
E-iceblue support team

[prism]

Hi,
I am working on spire.xls with the data table. I have done export my database values to Excel sheet, but I need to styles to values occupied rows and columns only. The same time a different color to the first row. Some times the column will increment and decrement, So I can't every time change the column value like this A[1] - H[1].
Thanks

[Amber.Gu]

Hi,

Thanks for your inquiry.
Please refer to the code below to style the used cell range and add color to the first row of the used range in Excel file.

Code: Select all

            //style the used cell range
            sheet.AllocatedRange.Style.Color = Color.Red;
            sheet.AllocatedRange.Style.Font.FontName = "Arial";

            //add color to the first row of the used range
            sheet.AllocatedRange.Rows[0].Style.Color = Color.Blue;

If the code couldn't solve your issue, to help us better investigate your issue, please offer us your input DataTable and your desired result file. You could upload them here or send us(support@e-iceblue.com) via email.

Best wishes,
Amber
E-iceblue support team

[prism]

Hi,
It is solved.
Thanks

[Amber.Gu]

Hi,

Thanks for your feedback.
Any question, welcome to contact us.

Best wishes,
Amber
E-iceblue support team