Add several features for xlsx-chart library #19

Tsenzuk · 2021-07-20T19:38:42Z

Features that are added in this pr:

custom chart position on the spreadsheet
custom color (fill and border) for each point/bar/sector resolves Charts colors #15
support "noFill" value as a color to hide some point/bar/sector
custom color (fill and border) for each series
custom legend position
an ability to hide the legend
custom plot size and position in the chart block
custom title position in the chart block
clear multi-layer support to be able to add any amount of same type charts (e.g. for bar + bar stacked + column + column staked) resolves Multiple charts only works for column chart #17 resolves Does this package still works? #11
support for stacked charts

config for check:

let fs = require ("fs");
let XLSXChart = require ("xlsx-chart");
let xlsxChart = new XLSXChart ();
let opts = {
    file: "mult_"+Date.now(),
    charts: [
        {
            position: {
                fromColumn: 10,
                toColumn: 20,
                fromRow: 10,
                toRow: 20,
            },
            customColors: {
                points: {
                    "Title 1": {
                        "Field 1": 'FF0000',
                        "Field 2": '00FF00',
                    },
                },
                series: {
                    "Title 2": {
                        fill: 'aaaaaa',
                        line: '00ffff',
                    }
                }
            },
            chart: "bar",
            titles: [
                "Title 1",
                "Title 2",
                "Title 3"
            ],
            fields: [
                "Field 1",
                "Field 2",
                "Field 3",
                "Field 4"
            ],
            data: {
                "Title 1": {
                    "Field 1": 5,
                    "Field 2": 10,
                    "Field 3": 15,
                    "Field 4": 20
                },
                "Title 2": {
                    "Field 1": 10,
                    "Field 2": 5,
                    "Field 3": 20,
                    "Field 4": 15
                },
                "Title 3": {
                    "Field 1": 20,
                    "Field 2": 15,
                    "Field 3": 10,
                    "Field 4": 5
                }
            },
            chartTitle: "Title 1"
        },
        {
            // position: {
            //     fromColumn: 10,
            //     toColumn: 20,
            //     // fromRow: 10,
            //     // toRow: 20,
            // },
            // customColors: {
            //     points: {
            //         "Title 1": {
            //             "Field 1": 'FF0000',
            //             "Field 2": '00FF00',
            //         },
            //     },
            //     series: {
            //         "Title 2": {
            //             fill: 'aaaaaa',
            //             line: '00ffff',
            //         }
            //     }
            // },
            // chart: "line",
            titles: [
                "Title 1",
                "Title 2",
                "Title 3",
                "Title 4",
                "Title 5",
                "Title 6",
                "Title 7",
            ],
            fields: [
                "Field 1",
                "Field 2",
                "Field 3",
            ],
            data: {
                "Title 1": {
                    "chart": "bar",
                    grouping: "stacked",
                    "Field 1": 5,
                    "Field 2": 10,
                    "Field 3": 15,
                },
                "Title 2": {
                    "chart": "bar",
                    grouping: "stacked",
                    "Field 1": 10,
                    "Field 2": 5,
                    "Field 3": 20,
                },
                "Title 3": {
                    "chart": "line",
                    grouping: "stacked",
                    "Field 1": 20,
                    "Field 2": 15,
                    "Field 3": 10,
                },
                "Title 4": {
                    "chart": "bar",
                    "Field 1": 5,
                    "Field 2": 10,
                    "Field 3": 15,
                },
                "Title 5": {
                    "chart": "bar",
                    "Field 1": 10,
                    "Field 2": 5,
                    "Field 3": 20,
                },
                "Title 6": {
                    "chart": "column",
                    "Field 1": 20,
                    "Field 2": 15,
                    "Field 3": 10,
                },
                "Title 7": {
                    "chart": "line",
                    grouping: "stacked",
                    "Field 1": 15,
                    "Field 2": 10,
                    "Field 3": 5,
                },
            },
            chartTitle: "Chart Title 2"
        },
        {
            position: {
                fromColumn: 10,
                toColumn: 15,
                fromRow: 0,
                toRow: 10,
            },
            chart: "doughnut",
            titles: [
                "Price"
            ],
            fields: [
                "value",
                "rest",
                "down"
            ],
            data: {
                "Price": {
                    "value": 10,
                    "rest": 100 - 10,
                    "down": 100
                }
            },
            customColors: {
                points: {
                    "Price": {
                        "down": 'noFill'
                    }
                }
            },
            manualLayout: {
                plotArea: {
                    w: 0.5,
                    h: 0.5,
                    x: 0.25,
                    y: 0.33,
                },
                title: {
                    x: 0.4,
                    y: 0.6
                }
            },
            firstSliceAng: 270,
            holeSize: 50,
            legendPos: null,
            chartTitle: "Gauge\nchart"
        },
    ]
};
xlsxChart.generate (opts, function (err, data) {
    fs.writeFileSync (`.\\results\\${opts.file}.xlsx`, data);
});

in some cases it was array of chart definitions in some cases it was object: { "bar": true, "radar": true, ... }

"charts.xslx" template is absent in "templates" folder. Current implementation allow to use any template, that have axes, so bar is good enough

before fix zero values also set to empty string, same as values not passed at all

mpaustin1993 · 2022-01-07T17:17:53Z

@Tsenzuk Awesome contribution! I've been using some of the features you implemented for a personal project of mine.

Upon chart / table export, do you have any idea how to align the table columns, or change the styling of the table headers?

I've been sifting through the documentation of DocumentFormat.OpenXml.Spreadsheet but the styling aspects of it are pretty confusing.

Thank you!

Tsenzuk · 2022-01-09T15:29:04Z

@mpaustin1993 yes, it's possible. @vshovkuta investigated this functionality. Here some code examples:

				var c = [{
					$: {
						r: "A" + (y + 2),
						t: "s",
						s: f === "gaugeRestValue" || f === "gaugeDownValue" ? "2" : "0" // here based on f variable we're setting the style with index === 2
					},
					v: me.getStr (f)
				}];

		me.read ({file: "xl/styles.xml"}, function (err, o) {
			if (err) {
				return cb (new VError (err, "writeTableStyle"));
			}

			o.styleSheet.cellXfs = {
				"$": {
					"count": "3"
				},
				"xf": [
					{
						"$": {
							"numFmtId": "0", "fontId": "0", "fillId": "0", "borderId": "0", "xfId": "0"
						}
					},
					{
						"$": {
							"numFmtId": "0", "fontId": "0", "fillId": "0", "borderId": "0", "xfId": "0", "applyAlignment": "1"
						},
						"alignment": {
							"$": {
								"vertical": "bottom", "wrapText": "1"
							}
						}
					},
					{ // it's a style added to styles array. It will be applied if it's index is used in "s" field of cell
						"$": {
							"numFmtId": "0", "fontId": "1", "fillId": "0", "borderId": "0", "xfId": "0", "applyFont": "1"
						}
					}
				]
			}

			const defaultFont = Object.assign ({}, o.styleSheet.fonts.font);

			o.styleSheet.fonts.font = [];
			o.styleSheet.fonts.font.push(defaultFont);

			const setWhiteColorForFont = Object.assign ({}, defaultFont);
			setWhiteColorForFont.color = {
				$: {
					rgb: "FFFFFFFF",
				}
			};

			o.styleSheet.fonts.font.push(setWhiteColorForFont);
			o.styleSheet.fonts.$.count = o.styleSheet.fonts.font.length;


			me.write ({file: "xl/styles.xml", object: o});
			cb ();
		});
	},

mpaustin1993 · 2022-01-10T16:10:22Z

@Tsenzuk & @vshovkuta - thanks a bunch! I'll give this a try & let you know if I have any questions

HaiAlison · 2022-06-29T14:52:18Z

@mpaustin1993 yes, it's possible. @vshovkuta investigated this functionality. Here some code examples:

can you give me an advise to fix this error?
TypeError: Cannot set properties of undefined (setting 'c:ser')

Tsenzuk · 2022-06-29T14:59:49Z

@HaiAlison do you use the code from this branch or from the master?

if this branch, please also mention, which line it is?

Looks like, the object of the chart description inside the writeChart() is not initialized properly

HaiAlison · 2022-06-29T15:05:30Z

Oh, I have resolved this bug, thanks for your commit 00bf5ca

rohanke · 2022-12-03T22:20:14Z

let fs = require ("fs");
let XLSXChart = require ("xlsx-chart");
let xlsxChart = new XLSXChart ();

let opts = {
charts: [
{
position: {
fromColumn: 10,
toColumn: 20,
fromRow: 10,
toRow: 20,
},
customColors: {
points: {
"Title 1": {
"Field 1": 'FF0000',
"Field 2": '00FF00',
},
},
series: {
"Title 2": {
fill: 'ff0000',
line: '00ffff',
}
}
},
chart: "column",
titles: [
"Title 1",
"Title 2",
"Title 3"
],
fields: [
"Field 1",
"Field 2",
"Field 3",
"Field 4"
],
data: {
"Title 1": {
"Field 1": 5,
"Field 2": 10,
"Field 3": 15,
"Field 4": 20
},
"Title 2": {
"Field 1": 10,
"Field 2": 5,
"Field 3": 20,
"Field 4": 15
},
"Title 3": {
"Field 1": 20,
"Field 2": 15,
"Field 3": 10,
"Field 4": 5
}
},
chartTitle: "Title 1"
}
, {
chart: "column",
titles: [
"Title 1",
"Title 2",
"Title 3"
],
fields: [
"Set 1",
"Set 2",
"Set 3",
"Set 4"
],
data: {
"Title 1": {
"Set 1": 5,
"Set 2": 10,
"Set 3": 15,
"Set 4": 20
},
"Title 2": {
"Set 1": 10,
"Set 2": 5,
"Set 3": 20,
"Set 4": 15
},
"Title 3": {
"Set 1": 20,
"Set 2": 15,
"Set 3": 10,
"Set 4": 5
}
},
chartTitle: "Bisevo 2018 #2"
}, {
chart: "column",
titles: [
"Title 1",
"Title 2"
],
fields: [
"Set 1",
"Set 2",
"Set 3"
],
data: {
"Title 1": {
"Set 1": 15,
"Set 2": 30,
"Set 3": 45
},
"Title 2": {
"Set 1": 5,
"Set 2": 2,
"Set 3": 10
}
},
chartTitle: "Affärs-Utveckling 2020"
}]
};

xlsxChart.generate (opts, function (err, data) {
fs.writeFileSync ("zzzz.xlsx", data);
});

One I changed the column colors its always getting from defult colors Library - rohan

Tsenzuk · 2022-12-03T23:49:41Z

@rohanke

colors set only for first chart and looks like set correctly
It's expected that each chart has separate colors description

rohanke · 2022-12-04T00:00:34Z

you can see the below source I have set colours correctly but its now working as I expected . I only need to change colour of the column. I am almost spent more than 10 hours . Pls help me to figure it out

version : "xlsx-chart": "^0.4.3"

let fs = require ("fs");
let XLSXChart = require ("xlsx-chart");
let xlsxChart = new XLSXChart ();

let opts = {
charts: [
{
position: {
fromColumn: 10,
toColumn: 20,
fromRow: 10,
toRow: 20,
},
customColors: {
points: {
"Title 1": {
"Field 1": 'FF0000',
"Field 2": '00FF00',
},
},
series: {
"Title 2": {
fill: 'ff0000',
line: '00ffff',
}
}
},
chart: "column",
titles: [
"Title 1",
"Title 2",
"Title 3"
],
fields: [
"Field 1",
"Field 2",
"Field 3",
"Field 4"
],
data: {
"Title 1": {
"Field 1": 0,
"Field 2": 10,
"Field 3": 15,
"Field 4": 20
},
"Title 2": {
"Field 1": 10,
"Field 2": 5,
"Field 3": 20,
"Field 4": 15
},
"Title 3": {
"Field 1": 20,
"Field 2": 15,
"Field 3": 10,
"Field 4": 5
}
},
chartTitle: "Title 1"
}
]
};

xlsxChart.generate (opts, function (err, data) {
fs.writeFileSync ("settimeChart.xlsx", data);
});

Tsenzuk · 2022-12-04T00:05:02Z

@rohanke you're in the discussion about PR, that still wasn't merged.
If you need custom colors from this PR, you should use the code from the branch instead of release in npm

rohanke · 2022-12-04T00:06:38Z

Okey Thank you

Tsenzuk added 25 commits July 6, 2021 18:34

replace Cyrillic diagram name with Latin

1c5fc6d

allow to pass the chart position as a fields of chart

bc3a4b9

use chart.position object to set chart position on page

8a59b39

set coordinates for the first chart

4220d14

update README

01cdcdc

Merge branch 'feature/alow-set-chart-coordinates'

b3534ea

add custom point color for bar chart

00d930e

add custom series color; add custom fill and line colors for point

a3a82dc

fix me.charts mixing (used as list of charts and list of types)

b10392b

in some cases it was array of chart definitions in some cases it was object: { "bar": true, "radar": true, ... }

add custom markers color

ce6a01c

Add custom charts in multi-chart config

d67ba95

add custom legend position ('b', 'r', etc); null to hide legend

d2e49bc

Update README.md

654ee4d

Merge branch 'feature/allow-set-custom-point-color'

aa39545

add ability to move and resize chart and title over the chart canvas

94ad338

update README.md

e96f071

Merge branch 'feature/custom-layouts-position'

32bfa24

fix adding Line chart for multi-chart config

00bf5ca

Merge branch 'bugfix/fix-line-chart-add-to-multi-chart-config'

465d347

add eslint for easier code formatting

0cbda62

fix "new Buffer" warning

5340a96

Merge branch 'feature/add-eslint-dependency'

b22effa

Add muli-layer chart support

e88917c

add support multi-layer chart with several layers of the same type

d80ba18

Merge branch 'feature/add-mix-chart-support-for-muli-chart'

85a0906

Tsenzuk force-pushed the master branch from 4265c68 to 75d5b00 Compare July 21, 2021 09:16

Tsenzuk added 4 commits July 21, 2021 18:38

add grouping option to be able to create staked charts

2b24ec7

update README.md

2044996

Merge branch 'feature/add-grouping-option-for-chart'

341b61d

Use "bar.xlsx" as default template

0eb2281

"charts.xslx" template is absent in "templates" folder. Current implementation allow to use any template, that have axes, so bar is good enough

Merge branch 'bugfix/fix-mix-and-chartLines-examples'

a6b6a85

Tsenzuk force-pushed the master branch from 6b8f94f to a6b6a85 Compare July 21, 2021 15:39

Tsenzuk added 2 commits July 29, 2021 17:42

fix deleteEmptyCells for zero values case

3b92705

before fix zero values also set to empty string, same as values not passed at all

Merge branch 'bugfix/fix-deleteEmptyCells-option'

8c2e456

kimkimhun3 mentioned this pull request Nov 7, 2024

Resize issue and unable to move the chart #25

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add several features for xlsx-chart library #19

Add several features for xlsx-chart library #19

Tsenzuk commented Jul 20, 2021 •

edited

Loading

mpaustin1993 commented Jan 7, 2022

Tsenzuk commented Jan 9, 2022

mpaustin1993 commented Jan 10, 2022

HaiAlison commented Jun 29, 2022

Tsenzuk commented Jun 29, 2022 •

edited

Loading

HaiAlison commented Jun 29, 2022

rohanke commented Dec 3, 2022 •

edited

Loading

Tsenzuk commented Dec 3, 2022

rohanke commented Dec 4, 2022

Tsenzuk commented Dec 4, 2022

rohanke commented Dec 4, 2022

Add several features for xlsx-chart library #19

Are you sure you want to change the base?

Add several features for xlsx-chart library #19

Conversation

Tsenzuk commented Jul 20, 2021 • edited Loading

mpaustin1993 commented Jan 7, 2022

Tsenzuk commented Jan 9, 2022

mpaustin1993 commented Jan 10, 2022

HaiAlison commented Jun 29, 2022

Tsenzuk commented Jun 29, 2022 • edited Loading

HaiAlison commented Jun 29, 2022

rohanke commented Dec 3, 2022 • edited Loading

Tsenzuk commented Dec 3, 2022

rohanke commented Dec 4, 2022

Tsenzuk commented Dec 4, 2022

rohanke commented Dec 4, 2022

Tsenzuk commented Jul 20, 2021 •

edited

Loading

Tsenzuk commented Jun 29, 2022 •

edited

Loading

rohanke commented Dec 3, 2022 •

edited

Loading