We have a requirement to extract all excel formatting data into a Json format as below. Basically the entire formatting is stored into a global “namedStyle” table and the individual Sheets has its own “sparklineGroups” , spans, table details and within the “data” group for each cell the “formatting” details are being referred by the “name” from the namedStyle table.
How do I extract or create this “namedStyle” table and refer them into the data section.
{
“spread”: {
“version”: “3.0”,
“sheetCount”: 21,
“namedStyles”: [
{
“name”: “20% - Accent1”,
“backColor”: “Accent 1 79”,
“foreColor”: “Text 1 0”,
“font”: “normal normal 14.7px Arial”,
“themeFont”: “Body”,
“textDecoration”: 0
},
{
“name”: “20% - Accent2”,
“backColor”: “Accent 2 79”,
“foreColor”: “Text 1 0”,
“font”: “normal normal 14.7px Arial”,
“themeFont”: “Body”,
“textDecoration”: 0
},
{
“name”: “__builtInStyle111”,
“parentName”: “Normal 2”,
“backColor”: “#333399”,
“foreColor”: “#FFFFFF”,
“hAlign”: 3,
“vAlign”: 1,
“font”: “normal normal 14.7px Verdana”,
“locked”: true,
“wordWrap”: false,
“borderLeft”: {
“color”: “#333399”,
“style”: 1
},
“borderTop”: {
“color”: “#333399”,
“style”: 2
},
“textDecoration”: 0
},
{
“name”: “_builtInStyle110",
“parentName”: “Normal 2”,
“foreColor”: “#545454”,
“hAlign”: 3,
“vAlign”: 2,
“font”: “normal normal 12px Verdana”,
“locked”: true,
“wordWrap”: false,
“borderRight”: {
“color”: “#FF0000”,
“style”: 1
},
“borderBottom”: {
“color”: “#FF0000”,
“style”: 1
},
“formatter”: “”$"#,##0.00);[Red]\(”$"#,##0.00\)",
“textDecoration”: 0
}
],
“sheets”: {
“18 Period Budget”: {
“name”: “18 Period Budget”,
“defaults”: {
“rowHeight”: 21.0,
“colWidth”: 64.0,
“colHeaderRowHeight”: 20.0,
“rowHeaderColWidth”: 40.0
},
“rowCount”: 49,
“columnCount”: 218,
“frozenRowCount”: 3,
“frozenColCount”: 2,
“data”: {
“rowCount”: 49,
“colCount”: 218,
“dataTable”: {
“0”: {
“1”: {
“value”: “company budget”,
“style”: “__builtInStyle196”
},
“2”: {
“style”: “__builtInStyle196”
},
“3”: {
“style”: “__builtInStyle196”
},
“4”: {
“style”: “__builtInStyle196”
},
“5”: {
“style”: “__builtInStyle13”
},
“6”: {
“style”: “__builtInStyle13”
}
},
“1”: {
“6”: {
“value”: “START DATE”,
“style”: “__builtInStyle31”
},
“7”: {
“value”: “/OADate(40544)/”,
“style”: “__builtInStyle29”
},
“sparklineGroups”: [
{
“sparklineType”: 0,
“setting”: {
“firstMarkerColor”: “rgba(255,192,0,1)”,
“highMarkerColor”: “rgba(0,176,80,1)”,
“lastMarkerColor”: “rgba(255,192,0,1)”,
“lowMarkerColor”: “rgba(255,0,0,1)”,
“markersColor”: “Accent 1 -49”,
“negativeColor”: “rgba(255,0,0,1)”,
“seriesColor”: “Accent 1 59”,
“showHigh”: true,
“showLow”: true,
“showMarkers”: true,
“groupMaxValue”: -1.7976931348623157E+308,
“groupMinValue”: 1.7976931348623157E+308
},
“axisOrientation”: 0,
“sparklines”: [
{
“row”: 8,
“col”: 21,
“orientation”: 1,
“data”: {
“row”: 8,
“col”: 2,
“rowCount”: 1,
“colCount”: 18
},
“type”: 0,
“setting”: {
“firstMarkerColor”: “rgba(255,192,0,1)”,
“highMarkerColor”: “rgba(0,176,80,1)”,
“lastMarkerColor”: “rgba(255,192,0,1)”,
“lowMarkerColor”: “rgba(255,0,0,1)”,
“markersColor”: “Accent 1 -49”,
“negativeColor”: “rgba(255,0,0,1)”,
“seriesColor”: “Accent 1 59”,
“showHigh”: true,
“showLow”: true,
“showMarkers”: true,
“groupMaxValue”: -1.7976931348623157E+308,
“groupMinValue”: 1.7976931348623157E+308
},
“axisOrientation”: 0
},
{
“row”: 9,
“col”: 21,
“orientation”: 1,
“data”: {
“row”: 9,
“col”: 2,
“rowCount”: 1,
“colCount”: 18
},
“type”: 0,
“setting”: {
“firstMarkerColor”: “rgba(255,192,0,1)”,
“highMarkerColor”: “rgba(0,176,80,1)”,
“lastMarkerColor”: “rgba(255,192,0,1)”,
“lowMarkerColor”: “rgba(255,0,0,1)”,
“markersColor”: “Accent 1 -49”,
“negativeColor”: “rgba(255,0,0,1)”,
“seriesColor”: “Accent 1 59”,
“showHigh”: true,
“showLow”: true,
“showMarkers”: true,
“groupMaxValue”: -1.7976931348623157E+308,
“groupMinValue”: 1.7976931348623157E+308
},
“axisOrientation”: 0
},
],
}
}
Any updates ?
Hi,
Thanks for your posting and using Aspose.Cells.
All formatting information of any cell is stored in the Style object. Please see this sample code, it retrieves the custom style and apply to cell A1, then it creates a built-in style and apply to cell A2. It should give you idea how aspose.cells deals with style.
I have attached the source and output Excel files for your reference.
Please also explain your requirements in more detail so that we could understand it better and advise you.
C#
Workbook workbook = new Workbook(“source.xlsx”);
//Get named style
Style style = workbook.GetNamedStyle(“MyStyle”);
//Appy this style to cell A1
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[“A1”].SetStyle(style);
//Create built-in style good and apply to cell A2
style = workbook.Styles.CreateBuiltinStyle(BuiltinStyleType.Good);
worksheet.Cells[“A2”].SetStyle(style);
workbook.Save(“output.xlsx”);
Hi Shakeel,
Thanks for the update. This helps. So my requirement is to extract data from an existing excel file and create a json object out of it.
While extracting I need to create a table of existing build-in-style table in json and then refer them in each cells style attribute. so if three cells are using the exact same formatting then they can refer to a single entry in the table
example extracted Json file namedStyles array table will look something like
“namedStyles”: [
{
“name”: “20% - Accent1”,
“backColor”: “Accent 1 79”,
“foreColor”: “Text 1 0”,
“font”: “normal normal 14.7px Arial”,
“themeFont”: “Body”,
“textDecoration”: 0
},
{
“name”: “20% - Accent2”,
“backColor”: “Accent 2 79”,
“foreColor”: “Text 1 0”,
“font”: “normal normal 14.7px Arial”,
“themeFont”: “Body”,
“textDecoration”: 0
},
{
“name”: “Normal 2”,
“hAlign”: 3,
“vAlign”: 2,
“font”: “normal normal 13.3px Arial”,
“locked”: true,
“wordWrap”: false,
“textDecoration”: 0
},
{
“name”: “Currency”,
“formatter”: “("$"* #,##0.00);("$"* \(#,##0.00\);(”$"* “-”??);(@_)"
},
{
“name”: “_builtInStyle121",
“parentName”: “Normal 2”,
“foreColor”: “#545454”,
“hAlign”: 3,
“vAlign”: 2,
“font”: “normal normal 12px Verdana”,
“locked”: true,
“wordWrap”: false,
“borderRight”: {
“color”: “#FF0000”,
“style”: 1
},
“borderBottom”: {
“color”: “#FF0000”,
“style”: 1
},
“formatter”: “”$"#,##0.00);[Red]\(”$"#,##0.00\)",
“textDecoration”: 0
},
{
“name”: “__builtInStyle120”,
“parentName”: “Normal 2”,
“foreColor”: “#545454”,
“hAlign”: 3,
“vAlign”: 2,
“font”: “normal normal 12px Verdana”,
“locked”: true,
“wordWrap”: false,
“borderRight”: {
“color”: “#3366FF”,
“style”: 1
},
“borderBottom”: {
“color”: “#3366FF”,
“style”: 1
},
“textDecoration”: 0
}]
and they can be referred into the json cells as
“dataTable”: {
/// Row 0 Column 1 to 6
“0”: {
“1”: {
“value”: “company budget”,
“style”: “__builtInStyle39”
},
“2”: {“style”: “__builtInStyle39”},
“3”: {“style”: “__builtInStyle39”},
“4”: {“style”: “__builtInStyle39”},
“5”: {“style”: “__builtInStyle13”},
“6”: {“style”: “__builtInStyle13”}
},
// Row 1 Column 6 - 10
“1”: {
“6”: {
“value”: “START DATE”,
“style”: “__builtInStyle31”
},
“7”: {
“value”: “/OADate(40544)/”,
“style”: “__builtInStyle29”
},
“8”: {
“value”: “PERIOD LENGTH (IN DAYS)”,
“style”: “__builtInStyle37”
},
“10”: {
“value”: 14.0,
“style”: “__builtInStyle30”
},
// Row 4 Column 2 and 3
“4”: {
“2”: {
“value”: “01-JAN”,
“style”: “__builtInStyle25”,
“formula”: “UPPER(TEXT(StartDate,“dd-mmm”))”
},
“3”: {
“value”: {"_calcError": “#VALUE!”},
“style”: “__builtInStyle26”,
“formula”: “UPPER(TEXT(C5+DayInterval,“dd-mmm”))”
},
And so on.
My question is how do I create the namedStyles table while extracting the data using AsposeCell and then refer them in the cell as above.
A Quick response would really be appreciated.
Hi,
Thanks for your posting and using Aspose.Cells.
I have attached a simple sample Excel file. Please download it and provide us your required Json that should be generated with this Excel file. It will help us move further in right direction.
Thanks for your help.
I have added the converted Json. As you can see there is a huge section of “namedStyle” with __builtInStyle1 and __builtInStyle2 that is being used in the data/dataTable/row/column cell
Hi,
Thanks for your explanation, providing the useful resource and considering Aspose.Cells.
We will analyze this feature and get back to you asap.
I think most of the namedStyles are the “default” Style from Excel. How do I extract them ?
http://support.microsoft.com/KB/291321
I will open a separate thread on the same.
thanks,
Rohit
We have some answer from another post. Please use that as a reference
Would you have an update on this ?
Thanks,
Rohit
Hi,
Thanks for considering Aspose.Cells.
You can extract all the styles using Workbook.getStyleInPool() method. Please check the source Excel file attached by me and the output of the following code for your reference. Let us know if it fulfills your needs.
As you can see from the console output, there are total 41 styles, out of which few are named styles.
Java
String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
System.out.println(“Total Number of Styles: " + workbook.getCountOfStylesInPool());
System.out.println(”----------------------------------------\r\n");
//Print the name of the styles
for (int i = 0; i < workbook.getCountOfStylesInPool(); i++)
{
Style style = workbook.getStyleInPool(i);
if(style.getName() !=null)
System.out.println(i + ": " + style.getName());
}
Total Number of Styles: 41
----------------------------------------
0: Normal
16: Percent
17: Currency
18: Currency [0]
19: Comma
20: Comma [0]
21: Good
22: Bad
23: Heading 1
24: Input
25: Linked Cell
26: Check Cell
27: Explanatory Text
28: Accent1
29: 40% - Accent2
30: 20% - Accent4