Accessing the unique styles in a workbook

Aspose.Cells Product Family
1

Hello:

In the internal representation of an Excel spreadsheet the styles in the workbook are grouped together in styles.xml.

I attached a spreadsheet and two xml files to see what I mean.

The first 7 values are all bold. If you look at sheet1.xml which is open xml data of sheet 1, all 7 cells reference style “1” which is stored in styles.xml:

styles.xml:


<cellXfs count="2">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1" />
</cellXfs>
sheet1.xml:

- <sheetData>
- <row r="1" spans="1:1">
- <c r="A1" s="1">
<v>1</v>
</c>
</row>
- <row r="2" spans="1:1">
- <c r="A2" s="1">
<v>2</v>
</c>
</row>
- <row r="3" spans="1:1">
- <c r="A3" s="1">
<v>3</v>
</c>
</row>

....

As you can see all the cells reference style "1" through the s attribute.



My question to you guys is this. In Apose, are there any methods that allow me to identify the unique styles of a workbook, and then for any given cell, can I identify which unique style that cell uses, in the same way as it is in the internal representation of the spreadsheet? I know about the getStyle or getDisplay style Cell methods but they return objects. I want to be able to identify the unique styles then I want to know for each cell which style that cell uses.

This link gives you more information about the open xml format and the significance of the different tags & attributes.

http://officeopenxml.com/SSstyles.php

Thanks!

2

Hi,

Thanks for your posting and using Aspose.Cells.

You can get the style of the cell using the Cell.GetStyle() method. Then if your cell is using named style, then you can access it from Style object using the Style.ParentStyle, it will return the named style which is stored in xml. Please try the Style.ParentStyle property for your needs.

You can also find all the named styles by iterating the Workbook.Styles collection.

3

Hello:

The internal definition of the spreadsheet keeps a list of a unique styles even though they are not named. Is there in the Aspose.Cells api a function that can return me those styles? Also is there a way to link a cell to the unique style associated with that cell.

Can you please pass my question to a developer? He/she should know about the internal representation of a spreadsheet and should understand my question.

Thanks

4

Hi,

Thanks for using Aspose.Cells.

Please use the following method. Cell.SharedStyleIndex

5

Ok, based on my observations, I can use something like this:

Cell cell = worksheet.Cells[0, 0];
Style style = workbook.GetStyleInPool(cell.SharedStyleIndex);

But how do I get all the styles that are in the “pool”? I assume the pool contains all the styles that are in the spreadsheet.

Can you please, please get to the bottom of this by consulting one of your developers?


Thank you

6

Hello:


To better understand what I want, I wrote a little program that uses the Microsoft Open XML SDK 2.5 (http://msdn.microsoft.com/en-us/library/office/bb448854.aspx). You can add it to the c# project via the NuGet (search for DocumentFormat.OpenXml).

I don’t want to use the Microsoft SDK, if Aspose.Cells provides the api to do the same thing.


Here is the source code:

using System;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Aspose.Cells;
using Aspose.Cells.Charts;
using Aspose.Cells.Drawing;
using Aspose.Cells.Pivot;
using Aspose.Cells.Rendering;
using Aspose.Cells.Tables;
using DocumentFormat.OpenXml.Packaging;
using TestAspose.NorthwindDataSetTableAdapters;
using pk =DocumentFormat.OpenXml.Packaging;
using ox=DocumentFormat.OpenXml;
using oxs = DocumentFormat.OpenXml.Spreadsheet;


namespace TestAspose
{
class Program
{



static void Main(string[] args)
{
TestSharedStyles2();
Console.WriteLine(“Done!”);
Console.ReadKey();
}

public static void TestSharedStyles2()
{
using (pk.SpreadsheetDocument document = pk.SpreadsheetDocument.Open(@“C:\temp\spreadsheets\test_spreadsheet.xlsx”, false))
{
pk.WorkbookPart workbookPart = document.WorkbookPart;

oxs.Workbook workbook = workbookPart.Workbook;

// pk.WorkbookStylesPart workbookStylesPart = workbookPart.WorkbookStylesPart;
oxs.Sheets sheets = workbook.Sheets;

pk.WorkbookStylesPart workbookStylesPart = workbookPart.WorkbookStylesPart;

oxs.Stylesheet stylesheet = workbookStylesPart.Stylesheet;

// Thesea are all the unique cell formats
foreach (oxs.CellFormat cellFormat in stylesheet.CellFormats)
{
Console.WriteLine(cellFormat.OuterXml);
}

foreach (oxs.Sheet sheet in sheets)
{
Console.WriteLine(sheet.Name);

pk.WorksheetPart worksheetPart = (pk.WorksheetPart)workbookPart.GetPartById(sheet.Id);

oxs.Worksheet worksheet = worksheetPart.Worksheet;
foreach (oxs.Cell cell in worksheet.Descendants<oxs.Cell>())
{
Console.WriteLine(" {0}, StyleIndex: {1}, cellFormat: {2}", cell.CellReference, cell.StyleIndex, stylesheet.CellFormats.ChildElements[(int) cell.StyleIndex.Value].OuterXml);

}
}
}
}
}
}

Thanks

7

Hi,

Thanks for your posting and using Aspose.Cells.

Well, you want to access all the styles that are in pool. We will look into this requirement and have logged your issue for investigation. We will see if we could provide you some API for this. This issue has been logged as CELLSNET-41992.

Please spare us some time. Once, we will have some update for you, we will let you know asap.

8

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have added Workbook.CountOfStylesInPool property.

Please download and try this fix: Aspose.Cells for .NET v7.5.3.1.


9

Hi there:

If I run the function below against the attached spreadsheet, it returns 21 styles. Why so many?

In the spreadsheet there are only a few.

public static void TestConditionalFormatting()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\test_conditional_formatting2.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Console.WriteLine(workbook.CountOfStylesInPool);
for (int i = 0; i < workbook.CountOfStylesInPool; i++)
{
Style style = workbook.GetStyleInPool(i);
Console.WriteLine(StyleToString(style));
}
}

public static string StyleToString(Style style)
{

return String.Format(“Name:{0}, Font: {1}, ForegroundColor:{2}, BackgroundColor:{3}, Borders:{4}”, style.Name, style.Font, style.ForegroundColor, style.BackgroundColor, BordersToString(style.Borders));
}

Here is the styles.xml file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>



















































<x14:slicerStyles defaultSlicerStyle=“SlicerStyleLight1”/>





Thanks

10

Hi Costa,

Thank you for trying out our new build.

I have logged your concerns to the ticket already associated with this thread, and we will shortly respond back with more information on this.

11

Hi Costa,

Thanks for using Aspose.Cells.

The conditional style is different with normal style, so it’s not in this style.
We are looking into how to provide it to you. Once, we will have some other update for you, we will let you know asap.

12

Hi Costas,

Thanks for using Aspose.Cells.

Please try Workbook.Worksheets.Dxfs with the latest fix i.e Aspose.Cells for .NET v7.6.0.2.