diff options
| -rw-r--r-- | .github/ISSUE_TEMPLATE/bug_report.md (renamed from .github/ISSUE_TEMPLATE.md) | 6 | ||||
| -rw-r--r-- | .github/ISSUE_TEMPLATE/feature_request.md | 44 | ||||
| -rw-r--r-- | .travis.yml | 11 | ||||
| -rw-r--r-- | PULL_REQUEST_TEMPLATE.md | 45 | ||||
| -rw-r--r-- | README.md | 2 | ||||
| -rw-r--r-- | README_zh.md | 2 | ||||
| -rw-r--r-- | cell.go | 73 | ||||
| -rw-r--r-- | chart.go | 93 | ||||
| -rw-r--r-- | col.go | 37 | ||||
| -rw-r--r-- | comment.go | 79 | ||||
| -rw-r--r-- | datavalidation.go | 233 | ||||
| -rw-r--r-- | datavalidation_test.go | 57 | ||||
| -rw-r--r-- | date.go | 59 | ||||
| -rw-r--r-- | date_test.go | 42 | ||||
| -rw-r--r-- | excelize.go | 46 | ||||
| -rw-r--r-- | excelize_test.go | 61 | ||||
| -rw-r--r-- | file.go | 40 | ||||
| -rw-r--r-- | hsl.go | 139 | ||||
| -rw-r--r-- | lib.go | 51 | ||||
| -rw-r--r-- | picture.go | 132 | ||||
| -rw-r--r-- | rows.go | 48 | ||||
| -rw-r--r-- | shape.go | 19 | ||||
| -rw-r--r-- | sheet.go | 102 | ||||
| -rw-r--r-- | sheetpr.go | 13 | ||||
| -rw-r--r-- | sheetview.go | 9 | ||||
| -rw-r--r-- | styles.go | 265 | ||||
| -rw-r--r-- | styles_test.go | 134 | ||||
| -rw-r--r-- | table.go | 48 | ||||
| -rw-r--r-- | templates.go | 9 | ||||
| -rw-r--r-- | vmlDrawing.go | 9 | ||||
| -rw-r--r-- | xmlChart.go | 9 | ||||
| -rw-r--r-- | xmlComments.go | 17 | ||||
| -rw-r--r-- | xmlContentTypes.go | 9 | ||||
| -rw-r--r-- | xmlDecodeDrawing.go | 9 | ||||
| -rw-r--r-- | xmlDrawing.go | 9 | ||||
| -rw-r--r-- | xmlSharedStrings.go | 9 | ||||
| -rw-r--r-- | xmlStyles.go | 9 | ||||
| -rw-r--r-- | xmlTable.go | 9 | ||||
| -rw-r--r-- | xmlTheme.go | 149 | ||||
| -rw-r--r-- | xmlWorkbook.go | 9 | ||||
| -rw-r--r-- | xmlWorksheet.go | 42 |
41 files changed, 1772 insertions, 416 deletions
diff --git a/.github/ISSUE_TEMPLATE.md b/.github/ISSUE_TEMPLATE/bug_report.md index 5d7e931..a8c31a0 100644 --- a/.github/ISSUE_TEMPLATE.md +++ b/.github/ISSUE_TEMPLATE/bug_report.md @@ -1,3 +1,9 @@ +--- +name: Bug report +about: Create a report to help us improve + +--- + <!-- If you are reporting a new issue, make sure that we do not have any duplicates already open. You can ensure this by searching the issue list for this diff --git a/.github/ISSUE_TEMPLATE/feature_request.md b/.github/ISSUE_TEMPLATE/feature_request.md new file mode 100644 index 0000000..1737cd4 --- /dev/null +++ b/.github/ISSUE_TEMPLATE/feature_request.md @@ -0,0 +1,44 @@ +--- +name: Feature request +about: Suggest an idea for this project + +--- + +<!-- +If you are reporting a new issue, make sure that we do not have any duplicates +already open. You can ensure this by searching the issue list for this +repository. If there is a duplicate, please close your issue and add a comment +to the existing issue instead. + +Use the commands below to provide key information from your environment: +You do NOT have to include this information if this is a FEATURE REQUEST +--> + +**Description** + +<!-- +Briefly describe the problem you are having in a few paragraphs. +--> + +**Steps to reproduce the issue:** +1. +2. +3. + +**Describe the results you received:** + +**Describe the results you expected:** + +**Output of `go version`:** + +```text +(paste your output here) +``` + +**Excelize version or commit ID:** + +```text +(paste here) +``` + +**Environment details (OS, Microsoft Excel™ version, physical, etc.):** diff --git a/.travis.yml b/.travis.yml index f4e5c0c..c2f0f90 100644 --- a/.travis.yml +++ b/.travis.yml @@ -6,6 +6,17 @@ install: go: - 1.8.x - 1.9.x + - 1.10.x + - 1.11.x + +os: + - linux + - osx + +env: + matrix: + - GOARCH=amd64 + - GOARCH=386 script: - go vet ./... diff --git a/PULL_REQUEST_TEMPLATE.md b/PULL_REQUEST_TEMPLATE.md new file mode 100644 index 0000000..d2ac755 --- /dev/null +++ b/PULL_REQUEST_TEMPLATE.md @@ -0,0 +1,45 @@ +# PR Details + +<!--- Provide a general summary of your changes in the Title above --> + +## Description + +<!--- Describe your changes in detail --> + +## Related Issue + +<!--- This project only accepts pull requests related to open issues --> +<!--- If suggesting a new feature or change, please discuss it in an issue first --> +<!--- If fixing a bug, there should be an issue describing it with steps to reproduce --> +<!--- Please link to the issue here: --> + +## Motivation and Context + +<!--- Why is this change required? What problem does it solve? --> + +## How Has This Been Tested + +<!--- Please describe in detail how you tested your changes. --> +<!--- Include details of your testing environment, and the tests you ran to --> +<!--- see how your change affects other areas of the code, etc. --> + +## Types of changes + +<!--- What types of changes does your code introduce? Put an `x` in all the boxes that apply: --> + +- [ ] Docs change / refactoring / dependency upgrade +- [ ] Bug fix (non-breaking change which fixes an issue) +- [ ] New feature (non-breaking change which adds functionality) +- [ ] Breaking change (fix or feature that would cause existing functionality to change) + +## Checklist + +<!--- Go over all the following points, and put an `x` in all the boxes that apply. --> +<!--- If you're unsure about any of these, don't hesitate to ask. We're here to help! --> + +- [ ] My code follows the code style of this project. +- [ ] My change requires a change to the documentation. +- [ ] I have updated the documentation accordingly. +- [ ] I have read the **CONTRIBUTING** document. +- [ ] I have added tests to cover my changes. +- [ ] All new and existing tests passed. @@ -11,7 +11,7 @@ ## Introduction -Excelize is a library written in pure Go and providing a set of functions that allow you to write to and read from XLSX files. Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later. Support save file without losing original charts of XLSX. This library needs Go version 1.8 or later. The full API docs can be seen using go's built-in documentation tool, or online at [godoc.org](https://godoc.org/github.com/360EntSecGroup-Skylar/excelize) and [Chinese translation](https://xuri.me/excelize/zh_cn). +Excelize is a library written in pure Go and providing a set of functions that allow you to write to and read from XLSX files. Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later. Support save file without losing original charts of XLSX. This library needs Go version 1.8 or later. The full API docs can be seen using go's built-in documentation tool, or online at [godoc.org](https://godoc.org/github.com/360EntSecGroup-Skylar/excelize) and [docs reference](https://xuri.me/excelize/). ## Basic Usage diff --git a/README_zh.md b/README_zh.md index ef149b0..49680d1 100644 --- a/README_zh.md +++ b/README_zh.md @@ -11,7 +11,7 @@ ## 简介 -Excelize 是 Go 语言编写的用于操作 Office Excel 文档类库，基于 ECMA-376 Office OpenXML 标准。可以使用它来读取、写入由 Microsoft Excel™ 2007 及以上版本创建的 XLSX 文档。相比较其他的开源类库，Excelize 支持写入原本带有图片(表)、透视表和切片器等复杂样式的文档，还支持向 Excel 文档中插入图片与图表，并且在保存后不会丢失文档原有样式，可以应用于各类报表系统中。使用本类库要求使用的 Go 语言为 1.8 或更高版本，完整的 API 使用文档请访问 [godoc.org](https://godoc.org/github.com/360EntSecGroup-Skylar/excelize) 或查看 [中文翻译](https://xuri.me/excelize/zh_cn)。 +Excelize 是 Go 语言编写的用于操作 Office Excel 文档类库，基于 ECMA-376 Office OpenXML 标准。可以使用它来读取、写入由 Microsoft Excel™ 2007 及以上版本创建的 XLSX 文档。相比较其他的开源类库，Excelize 支持写入原本带有图片(表)、透视表和切片器等复杂样式的文档，还支持向 Excel 文档中插入图片与图表，并且在保存后不会丢失文档原有样式，可以应用于各类报表系统中。使用本类库要求使用的 Go 语言为 1.8 或更高版本，完整的 API 使用文档请访问 [godoc.org](https://godoc.org/github.com/360EntSecGroup-Skylar/excelize) 或查看 [参考文档](https://xuri.me/excelize/)。 ## 快速上手 @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -20,7 +29,7 @@ const ( STCellFormulaTypeShared = "shared" ) -// mergeCellsParser provides function to check merged cells in worksheet by +// mergeCellsParser provides a function to check merged cells in worksheet by // given axis. func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) string { axis = strings.ToUpper(axis) @@ -34,8 +43,8 @@ func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) string { return axis } -// SetCellValue provides function to set value of a cell. The following shows -// the supported data types: +// SetCellValue provides a function to set value of a cell. The following +// shows the supported data types: // // int // int8 @@ -83,7 +92,7 @@ func (f *File) SetCellValue(sheet, axis string, value interface{}) { } } -// setCellIntValue provides function to set int value of a cell. +// setCellIntValue provides a function to set int value of a cell. func (f *File) setCellIntValue(sheet, axis string, value interface{}) { switch value.(type) { case int: @@ -111,7 +120,7 @@ func (f *File) setCellIntValue(sheet, axis string, value interface{}) { } } -// SetCellBool provides function to set bool type value of a cell by given +// SetCellBool provides a function to set bool type value of a cell by given // worksheet name, cell coordinates and cell value. func (f *File) SetCellBool(sheet, axis string, value bool) { xlsx := f.workSheetReader(sheet) @@ -139,10 +148,10 @@ func (f *File) SetCellBool(sheet, axis string, value bool) { } } -// GetCellValue provides function to get formatted value from cell by given -// worksheet name and axis in XLSX file. If it is possible to apply a format to -// the cell value, it will do so, if not then an error will be returned, along -// with the raw value of the cell. +// GetCellValue provides a function to get formatted value from cell by given +// worksheet name and axis in XLSX file. If it is possible to apply a format +// to the cell value, it will do so, if not then an error will be returned, +// along with the raw value of the cell. func (f *File) GetCellValue(sheet, axis string) string { xlsx := f.workSheetReader(sheet) axis = f.mergeCellsParser(xlsx, axis) @@ -174,9 +183,9 @@ func (f *File) GetCellValue(sheet, axis string) string { return "" } -// formattedValue provides function to returns a value after formatted. If it is -// possible to apply a format to the cell value, it will do so, if not then an -// error will be returned, along with the raw value of the cell. +// formattedValue provides a function to returns a value after formatted. If +// it is possible to apply a format to the cell value, it will do so, if not +// then an error will be returned, along with the raw value of the cell. func (f *File) formattedValue(s int, v string) string { if s == 0 { return v @@ -189,7 +198,7 @@ func (f *File) formattedValue(s int, v string) string { return v } -// GetCellStyle provides function to get cell style index by given worksheet +// GetCellStyle provides a function to get cell style index by given worksheet // name and cell coordinates. func (f *File) GetCellStyle(sheet, axis string) int { xlsx := f.workSheetReader(sheet) @@ -211,8 +220,8 @@ func (f *File) GetCellStyle(sheet, axis string) int { return f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S) } -// GetCellFormula provides function to get formula from cell by given worksheet -// name and axis in XLSX file. +// GetCellFormula provides a function to get formula from cell by given +// worksheet name and axis in XLSX file. func (f *File) GetCellFormula(sheet, axis string) string { xlsx := f.workSheetReader(sheet) axis = f.mergeCellsParser(xlsx, axis) @@ -276,7 +285,7 @@ func getSharedForumula(xlsx *xlsxWorksheet, si string) string { return "" } -// SetCellFormula provides function to set cell formula by given string and +// SetCellFormula provides a function to set cell formula by given string and // worksheet name. func (f *File) SetCellFormula(sheet, axis, formula string) { xlsx := f.workSheetReader(sheet) @@ -305,10 +314,10 @@ func (f *File) SetCellFormula(sheet, axis, formula string) { } } -// SetCellHyperLink provides function to set cell hyperlink by given worksheet -// name and link URL address. LinkType defines two types of hyperlink "External" -// for web site or "Location" for moving to one of cell in this workbook. The -// below is example for external link. +// SetCellHyperLink provides a function to set cell hyperlink by given +// worksheet name and link URL address. LinkType defines two types of +// hyperlink "External" for web site or "Location" for moving to one of cell +// in this workbook. The below is example for external link. // // xlsx.SetCellHyperLink("Sheet1", "A3", "https://github.com/360EntSecGroup-Skylar/excelize", "External") // // Set underline and font color style for the cell. @@ -341,10 +350,10 @@ func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) { xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink, hyperlink) } -// GetCellHyperLink provides function to get cell hyperlink by given worksheet -// name and axis. Boolean type value link will be ture if the cell has a -// hyperlink and the target is the address of the hyperlink. Otherwise, the -// value of link will be false and the value of the target will be a blank +// GetCellHyperLink provides a function to get cell hyperlink by given +// worksheet name and axis. Boolean type value link will be ture if the cell +// has a hyperlink and the target is the address of the hyperlink. Otherwise, +// the value of link will be false and the value of the target will be a blank // string. For example get hyperlink of Sheet1!H6: // // link, target := xlsx.GetCellHyperLink("Sheet1", "H6") @@ -369,8 +378,8 @@ func (f *File) GetCellHyperLink(sheet, axis string) (bool, string) { return link, target } -// MergeCell provides function to merge cells by given coordinate area and sheet -// name. For example create a merged cell of D3:E9 on Sheet1: +// MergeCell provides a function to merge cells by given coordinate area and +// sheet name. For example create a merged cell of D3:E9 on Sheet1: // // xlsx.MergeCell("Sheet1", "D3", "E9") // @@ -429,7 +438,7 @@ func (f *File) MergeCell(sheet, hcell, vcell string) { } } -// SetCellInt provides function to set int type value of a cell by given +// SetCellInt provides a function to set int type value of a cell by given // worksheet name, cell coordinates and cell value. func (f *File) SetCellInt(sheet, axis string, value int) { xlsx := f.workSheetReader(sheet) @@ -453,7 +462,7 @@ func (f *File) SetCellInt(sheet, axis string, value int) { xlsx.SheetData.Row[xAxis].C[yAxis].V = strconv.Itoa(value) } -// prepareCellStyle provides function to prepare style index of cell in +// prepareCellStyle provides a function to prepare style index of cell in // worksheet by given column index and style index. func (f *File) prepareCellStyle(xlsx *xlsxWorksheet, col, style int) int { if xlsx.Cols != nil && style == 0 { @@ -466,8 +475,8 @@ func (f *File) prepareCellStyle(xlsx *xlsxWorksheet, col, style int) int { return style } -// SetCellStr provides function to set string type value of a cell. Total number -// of characters that a cell can contain 32767 characters. +// SetCellStr provides a function to set string type value of a cell. Total +// number of characters that a cell can contain 32767 characters. func (f *File) SetCellStr(sheet, axis, value string) { xlsx := f.workSheetReader(sheet) axis = f.mergeCellsParser(xlsx, axis) @@ -502,7 +511,7 @@ func (f *File) SetCellStr(sheet, axis, value string) { xlsx.SheetData.Row[xAxis].C[yAxis].V = value } -// SetCellDefault provides function to set string type value of a cell as +// SetCellDefault provides a function to set string type value of a cell as // default format without escaping the cell. func (f *File) SetCellDefault(sheet, axis, value string) { xlsx := f.workSheetReader(sheet) @@ -567,7 +576,7 @@ func (f *File) SetSheetRow(sheet, axis string, slice interface{}) { } } -// checkCellInArea provides function to determine if a given coordinate is +// checkCellInArea provides a function to determine if a given coordinate is // within an area. func checkCellInArea(cell, area string) bool { cell = strings.ToUpper(cell) @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -190,7 +199,7 @@ var ( } ) -// parseFormatChartSet provides function to parse the format settings of the +// parseFormatChartSet provides a function to parse the format settings of the // chart with default value. func parseFormatChartSet(formatSet string) (*formatChart, error) { format := formatChart{ @@ -352,7 +361,9 @@ func parseFormatChartSet(formatSet string) (*formatChart, error) { // minimum // // reverse_order: Specifies that the categories or values on reverse order (orientation of the chart). The reverse_order property is optional. The default value is false. +// // maximum: Specifies that the fixed maximum, 0 is auto. The maximum property is optional. The default value is auto. +// // minimum: Specifies that the fixed minimum, 0 is auto. The minimum property is optional. The default value is auto. // // Set chart size by dimension property. The dimension property is optional. The default width is 480, and height is 290. @@ -377,7 +388,7 @@ func (f *File) AddChart(sheet, cell, format string) error { return err } -// countCharts provides function to get chart files count storage in the +// countCharts provides a function to get chart files count storage in the // folder xl/charts. func (f *File) countCharts() int { count := 0 @@ -389,7 +400,7 @@ func (f *File) countCharts() int { return count } -// prepareDrawing provides function to prepare drawing ID and XML by given +// prepareDrawing provides a function to prepare drawing ID and XML by given // drawingID, worksheet name and default drawingXML. func (f *File) prepareDrawing(xlsx *xlsxWorksheet, drawingID int, sheet, drawingXML string) (int, string) { sheetRelationshipsDrawingXML := "../drawings/drawing" + strconv.Itoa(drawingID) + ".xml" @@ -406,8 +417,8 @@ func (f *File) prepareDrawing(xlsx *xlsxWorksheet, drawingID int, sheet, drawing return drawingID, drawingXML } -// addChart provides function to create chart as xl/charts/chart%d.xml by given -// format sets. +// addChart provides a function to create chart as xl/charts/chart%d.xml by +// given format sets. func (f *File) addChart(formatSet *formatChart) { count := f.countCharts() xlsxChartSpace := xlsxChartSpace{ @@ -562,7 +573,7 @@ func (f *File) addChart(formatSet *formatChart) { f.saveFileList(media, chart) } -// drawBaseChart provides function to draw the c:plotArea element for bar, +// drawBaseChart provides a function to draw the c:plotArea element for bar, // and column series charts by given format sets. func (f *File) drawBaseChart(formatSet *formatChart) *cPlotArea { c := cCharts{ @@ -659,7 +670,7 @@ func (f *File) drawBaseChart(formatSet *formatChart) *cPlotArea { return charts[formatSet.Type] } -// drawDoughnutChart provides function to draw the c:plotArea element for +// drawDoughnutChart provides a function to draw the c:plotArea element for // doughnut chart by given format sets. func (f *File) drawDoughnutChart(formatSet *formatChart) *cPlotArea { return &cPlotArea{ @@ -673,8 +684,8 @@ func (f *File) drawDoughnutChart(formatSet *formatChart) *cPlotArea { } } -// drawLineChart provides function to draw the c:plotArea element for line chart -// by given format sets. +// drawLineChart provides a function to draw the c:plotArea element for line +// chart by given format sets. func (f *File) drawLineChart(formatSet *formatChart) *cPlotArea { return &cPlotArea{ LineChart: &cCharts{ @@ -699,8 +710,8 @@ func (f *File) drawLineChart(formatSet *formatChart) *cPlotArea { } } -// drawPieChart provides function to draw the c:plotArea element for pie chart -// by given format sets. +// drawPieChart provides a function to draw the c:plotArea element for pie +// chart by given format sets. func (f *File) drawPieChart(formatSet *formatChart) *cPlotArea { return &cPlotArea{ PieChart: &cCharts{ @@ -712,8 +723,8 @@ func (f *File) drawPieChart(formatSet *formatChart) *cPlotArea { } } -// drawPie3DChart provides function to draw the c:plotArea element for 3D pie -// chart by given format sets. +// drawPie3DChart provides a function to draw the c:plotArea element for 3D +// pie chart by given format sets. func (f *File) drawPie3DChart(formatSet *formatChart) *cPlotArea { return &cPlotArea{ Pie3DChart: &cCharts{ @@ -725,7 +736,7 @@ func (f *File) drawPie3DChart(formatSet *formatChart) *cPlotArea { } } -// drawRadarChart provides function to draw the c:plotArea element for radar +// drawRadarChart provides a function to draw the c:plotArea element for radar // chart by given format sets. func (f *File) drawRadarChart(formatSet *formatChart) *cPlotArea { return &cPlotArea{ @@ -748,8 +759,8 @@ func (f *File) drawRadarChart(formatSet *formatChart) *cPlotArea { } } -// drawScatterChart provides function to draw the c:plotArea element for scatter -// chart by given format sets. +// drawScatterChart provides a function to draw the c:plotArea element for +// scatter chart by given format sets. func (f *File) drawScatterChart(formatSet *formatChart) *cPlotArea { return &cPlotArea{ ScatterChart: &cCharts{ @@ -771,8 +782,8 @@ func (f *File) drawScatterChart(formatSet *formatChart) *cPlotArea { } } -// drawChartSeries provides function to draw the c:ser element by given format -// sets. +// drawChartSeries provides a function to draw the c:ser element by given +// format sets. func (f *File) drawChartSeries(formatSet *formatChart) *[]cSer { ser := []cSer{} for k := range formatSet.Series { @@ -797,7 +808,7 @@ func (f *File) drawChartSeries(formatSet *formatChart) *[]cSer { return &ser } -// drawChartSeriesSpPr provides function to draw the c:spPr element by given +// drawChartSeriesSpPr provides a function to draw the c:spPr element by given // format sets. func (f *File) drawChartSeriesSpPr(i int, formatSet *formatChart) *cSpPr { spPrScatter := &cSpPr{ @@ -819,8 +830,8 @@ func (f *File) drawChartSeriesSpPr(i int, formatSet *formatChart) *cSpPr { return chartSeriesSpPr[formatSet.Type] } -// drawChartSeriesDPt provides function to draw the c:dPt element by given data -// index and format sets. +// drawChartSeriesDPt provides a function to draw the c:dPt element by given +// data index and format sets. func (f *File) drawChartSeriesDPt(i int, formatSet *formatChart) []*cDPt { dpt := []*cDPt{{ IDx: &attrValInt{Val: i}, @@ -848,8 +859,8 @@ func (f *File) drawChartSeriesDPt(i int, formatSet *formatChart) []*cDPt { return chartSeriesDPt[formatSet.Type] } -// drawChartSeriesCat provides function to draw the c:cat element by given chart -// series and format sets. +// drawChartSeriesCat provides a function to draw the c:cat element by given +// chart series and format sets. func (f *File) drawChartSeriesCat(v formatChartSeries, formatSet *formatChart) *cCat { cat := &cCat{ StrRef: &cStrRef{ @@ -860,8 +871,8 @@ func (f *File) drawChartSeriesCat(v formatChartSeries, formatSet *formatChart) * return chartSeriesCat[formatSet.Type] } -// drawChartSeriesVal provides function to draw the c:val element by given chart -// series and format sets. +// drawChartSeriesVal provides a function to draw the c:val element by given +// chart series and format sets. func (f *File) drawChartSeriesVal(v formatChartSeries, formatSet *formatChart) *cVal { val := &cVal{ NumRef: &cNumRef{ @@ -872,8 +883,8 @@ func (f *File) drawChartSeriesVal(v formatChartSeries, formatSet *formatChart) * return chartSeriesVal[formatSet.Type] } -// drawChartSeriesMarker provides function to draw the c:marker element by given -// data index and format sets. +// drawChartSeriesMarker provides a function to draw the c:marker element by +// given data index and format sets. func (f *File) drawChartSeriesMarker(i int, formatSet *formatChart) *cMarker { marker := &cMarker{ Symbol: &attrValString{Val: "circle"}, @@ -898,7 +909,7 @@ func (f *File) drawChartSeriesMarker(i int, formatSet *formatChart) *cMarker { return chartSeriesMarker[formatSet.Type] } -// drawChartSeriesXVal provides function to draw the c:xVal element by given +// drawChartSeriesXVal provides a function to draw the c:xVal element by given // chart series and format sets. func (f *File) drawChartSeriesXVal(v formatChartSeries, formatSet *formatChart) *cCat { cat := &cCat{ @@ -910,7 +921,7 @@ func (f *File) drawChartSeriesXVal(v formatChartSeries, formatSet *formatChart) return chartSeriesXVal[formatSet.Type] } -// drawChartSeriesYVal provides function to draw the c:yVal element by given +// drawChartSeriesYVal provides a function to draw the c:yVal element by given // chart series and format sets. func (f *File) drawChartSeriesYVal(v formatChartSeries, formatSet *formatChart) *cVal { val := &cVal{ @@ -922,8 +933,8 @@ func (f *File) drawChartSeriesYVal(v formatChartSeries, formatSet *formatChart) return chartSeriesYVal[formatSet.Type] } -// drawChartDLbls provides function to draw the c:dLbls element by given format -// sets. +// drawChartDLbls provides a function to draw the c:dLbls element by given +// format sets. func (f *File) drawChartDLbls(formatSet *formatChart) *cDLbls { return &cDLbls{ ShowLegendKey: &attrValBool{Val: formatSet.Legend.ShowLegendKey}, @@ -936,15 +947,15 @@ func (f *File) drawChartDLbls(formatSet *formatChart) *cDLbls { } } -// drawChartSeriesDLbls provides function to draw the c:dLbls element by given -// format sets. +// drawChartSeriesDLbls provides a function to draw the c:dLbls element by +// given format sets. func (f *File) drawChartSeriesDLbls(formatSet *formatChart) *cDLbls { dLbls := f.drawChartDLbls(formatSet) chartSeriesDLbls := map[string]*cDLbls{Bar: dLbls, BarStacked: dLbls, BarPercentStacked: dLbls, Bar3DClustered: dLbls, Bar3DStacked: dLbls, Bar3DPercentStacked: dLbls, Col: dLbls, ColStacked: dLbls, ColPercentStacked: dLbls, Col3DClustered: dLbls, Col3D: dLbls, Col3DStacked: dLbls, Col3DPercentStacked: dLbls, Doughnut: dLbls, Line: dLbls, Pie: dLbls, Pie3D: dLbls, Radar: dLbls, Scatter: nil} return chartSeriesDLbls[formatSet.Type] } -// drawPlotAreaCatAx provides function to draw the c:catAx element. +// drawPlotAreaCatAx provides a function to draw the c:catAx element. func (f *File) drawPlotAreaCatAx(formatSet *formatChart) []*cAxs { min := &attrValFloat{Val: formatSet.XAxis.Minimum} max := &attrValFloat{Val: formatSet.XAxis.Maximum} @@ -983,7 +994,7 @@ func (f *File) drawPlotAreaCatAx(formatSet *formatChart) []*cAxs { } } -// drawPlotAreaValAx provides function to draw the c:valAx element. +// drawPlotAreaValAx provides a function to draw the c:valAx element. func (f *File) drawPlotAreaValAx(formatSet *formatChart) []*cAxs { min := &attrValFloat{Val: formatSet.YAxis.Minimum} max := &attrValFloat{Val: formatSet.YAxis.Maximum} @@ -1019,7 +1030,7 @@ func (f *File) drawPlotAreaValAx(formatSet *formatChart) []*cAxs { } } -// drawPlotAreaSpPr provides function to draw the c:spPr element. +// drawPlotAreaSpPr provides a function to draw the c:spPr element. func (f *File) drawPlotAreaSpPr() *cSpPr { return &cSpPr{ Ln: &aLn{ @@ -1038,7 +1049,7 @@ func (f *File) drawPlotAreaSpPr() *cSpPr { } } -// drawPlotAreaTxPr provides function to draw the c:txPr element. +// drawPlotAreaTxPr provides a function to draw the c:txPr element. func (f *File) drawPlotAreaTxPr() *cTxPr { return &cTxPr{ BodyPr: aBodyPr{ @@ -1077,8 +1088,8 @@ func (f *File) drawPlotAreaTxPr() *cTxPr { } } -// drawingParser provides function to parse drawingXML. In order to solve the -// problem that the label structure is changed after serialization and +// drawingParser provides a function to parse drawingXML. In order to solve +// the problem that the label structure is changed after serialization and // deserialization, two different structures: decodeWsDr and encodeWsDr are // defined. func (f *File) drawingParser(drawingXML string, content *xlsxWsDr) int { @@ -1105,8 +1116,8 @@ func (f *File) drawingParser(drawingXML string, content *xlsxWsDr) int { return cNvPrID } -// addDrawingChart provides function to add chart graphic frame by given sheet, -// drawingXML, cell, width, height, relationship index and format sets. +// addDrawingChart provides a function to add chart graphic frame by given +// sheet, drawingXML, cell, width, height, relationship index and format sets. func (f *File) addDrawingChart(sheet, drawingXML, cell string, width, height, rID int, formatSet *formatPicture) { cell = strings.ToUpper(cell) fromCol := string(strings.Map(letterOnlyMapF, cell)) @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -121,8 +130,8 @@ func (f *File) SetColOutlineLevel(sheet, column string, level uint8) { xlsx.Cols.Col = append(xlsx.Cols.Col, col) } -// SetColWidth provides function to set the width of a single column or multiple -// columns. For example: +// SetColWidth provides a function to set the width of a single column or +// multiple columns. For example: // // xlsx := excelize.NewFile() // xlsx.SetColWidth("Sheet1", "A", "H", 20) @@ -259,8 +268,8 @@ func (f *File) positionObjectPixels(sheet string, colStart, rowStart, x1, y1, wi return colStart, rowStart, xAbs, yAbs, colEnd, rowEnd, x2, y2 } -// getColWidth provides function to get column width in pixels by given sheet -// name and column index. +// getColWidth provides a function to get column width in pixels by given +// sheet name and column index. func (f *File) getColWidth(sheet string, col int) int { xlsx := f.workSheetReader(sheet) if xlsx.Cols != nil { @@ -278,8 +287,8 @@ func (f *File) getColWidth(sheet string, col int) int { return int(defaultColWidthPixels) } -// GetColWidth provides function to get column width by given worksheet name and -// column index. +// GetColWidth provides a function to get column width by given worksheet name +// and column index. func (f *File) GetColWidth(sheet, column string) float64 { col := TitleToNumber(strings.ToUpper(column)) + 1 xlsx := f.workSheetReader(sheet) @@ -298,8 +307,8 @@ func (f *File) GetColWidth(sheet, column string) float64 { return defaultColWidthPixels } -// InsertCol provides function to insert a new column before given column index. -// For example, create a new column before column C in Sheet1: +// InsertCol provides a function to insert a new column before given column +// index. For example, create a new column before column C in Sheet1: // // xlsx.InsertCol("Sheet1", "C") // @@ -308,8 +317,8 @@ func (f *File) InsertCol(sheet, column string) { f.adjustHelper(sheet, col, -1, 1) } -// RemoveCol provides function to remove single column by given worksheet name -// and column index. For example, remove column C in Sheet1: +// RemoveCol provides a function to remove single column by given worksheet +// name and column index. For example, remove column C in Sheet1: // // xlsx.RemoveCol("Sheet1", "C") // @@ -346,10 +355,10 @@ func completeCol(xlsx *xlsxWorksheet, row, cell int) { } } -// convertColWidthToPixels provieds function to convert the width of a cell from -// user's units to pixels. Excel rounds the column width to the nearest pixel. -// If the width hasn't been set by the user we use the default value. If the -// column is hidden it has a value of zero. +// convertColWidthToPixels provieds function to convert the width of a cell +// from user's units to pixels. Excel rounds the column width to the nearest +// pixel. If the width hasn't been set by the user we use the default value. +// If the column is hidden it has a value of zero. func convertColWidthToPixels(width float64) float64 { var padding float64 = 5 var pixels float64 @@ -1,14 +1,24 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( "encoding/json" "encoding/xml" + "fmt" "strconv" "strings" ) -// parseFormatCommentsSet provides function to parse the format settings of the -// comment with default value. +// parseFormatCommentsSet provides a function to parse the format settings of +// the comment with default value. func parseFormatCommentsSet(formatSet string) (*formatComment, error) { format := formatComment{ Author: "Author:", @@ -18,6 +28,36 @@ func parseFormatCommentsSet(formatSet string) (*formatComment, error) { return &format, err } +// GetComments retrieves all comments and returns a map of worksheet name to +// the worksheet comments. +func (f *File) GetComments() (comments map[string][]Comment) { + comments = map[string][]Comment{} + for n := range f.sheetMap { + commentID := f.GetSheetIndex(n) + commentsXML := "xl/comments" + strconv.Itoa(commentID) + ".xml" + c, ok := f.XLSX[commentsXML] + if ok { + d := xlsxComments{} + xml.Unmarshal([]byte(c), &d) + sheetComments := []Comment{} + for _, comment := range d.CommentList.Comment { + sheetComment := Comment{} + if comment.AuthorID < len(d.Authors) { + sheetComment.Author = d.Authors[comment.AuthorID].Author + } + sheetComment.Ref = comment.Ref + sheetComment.AuthorID = comment.AuthorID + for _, text := range comment.Text.R { + sheetComment.Text += text.T + } + sheetComments = append(sheetComments, sheetComment) + } + comments[n] = sheetComments + } + } + return +} + // AddComment provides the method to add comment in a sheet by given worksheet // index, cell and format set (such as author and text). Note that the max // author length is 255 and the max text length is 32512. For example, add a @@ -49,14 +89,23 @@ func (f *File) AddComment(sheet, cell, format string) error { } commentsXML := "xl/comments" + strconv.Itoa(commentID) + ".xml" f.addComment(commentsXML, cell, formatSet) - f.addDrawingVML(commentID, drawingVML, cell) + var colCount int + for i, l := range strings.Split(formatSet.Text, "\n") { + if ll := len(l); ll > colCount { + if i == 0 { + ll += len(formatSet.Author) + } + colCount = ll + } + } + f.addDrawingVML(commentID, drawingVML, cell, strings.Count(formatSet.Text, "\n")+1, colCount) f.addContentTypePart(commentID, "comments") return err } -// addDrawingVML provides function to create comment as +// addDrawingVML provides a function to create comment as // xl/drawings/vmlDrawing%d.vml by given commit ID and cell. -func (f *File) addDrawingVML(commentID int, drawingVML, cell string) { +func (f *File) addDrawingVML(commentID int, drawingVML, cell string, lineCount, colCount int) { col := string(strings.Map(letterOnlyMapF, cell)) row, _ := strconv.Atoi(strings.Map(intOnlyMapF, cell)) xAxis := row - 1 @@ -83,7 +132,7 @@ func (f *File) addDrawingVML(commentID int, drawingVML, cell string) { }, VPath: &vPath{ Gradientshapeok: "t", - Connecttype: "rect", + Connecttype: "miter", }, }, } @@ -113,10 +162,12 @@ func (f *File) addDrawingVML(commentID int, drawingVML, cell string) { }, ClientData: &xClientData{ ObjectType: "Note", - Anchor: "3, 15, 8, 6, 4, 54, 13, 2", - AutoFill: "False", - Row: xAxis, - Column: yAxis, + Anchor: fmt.Sprintf( + "%d, 23, %d, 0, %d, %d, %d, 5", + 1+yAxis, 1+xAxis, 2+yAxis+lineCount, colCount+yAxis, 2+xAxis+lineCount), + AutoFill: "True", + Row: xAxis, + Column: yAxis, }, } s, _ := xml.Marshal(sp) @@ -149,8 +200,8 @@ func (f *File) addDrawingVML(commentID int, drawingVML, cell string) { f.XLSX[drawingVML] = v } -// addComment provides function to create chart as xl/comments%d.xml by given -// cell and format sets. +// addComment provides a function to create chart as xl/comments%d.xml by +// given cell and format sets. func (f *File) addComment(commentsXML, cell string, formatSet *formatComment) { a := formatSet.Author t := formatSet.Text @@ -209,8 +260,8 @@ func (f *File) addComment(commentsXML, cell string, formatSet *formatComment) { f.saveFileList(commentsXML, v) } -// countComments provides function to get comments files count storage in the -// folder xl. +// countComments provides a function to get comments files count storage in +// the folder xl. func (f *File) countComments() int { count := 0 for k := range f.XLSX { diff --git a/datavalidation.go b/datavalidation.go new file mode 100644 index 0000000..5ebd61f --- /dev/null +++ b/datavalidation.go @@ -0,0 +1,233 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + +package excelize + +import ( + "fmt" + "strings" +) + +// DataValidationType defined the type of data validation. +type DataValidationType int + +// Data validation types. +const ( + _DataValidationType = iota + typeNone // inline use + DataValidationTypeCustom + DataValidationTypeDate + DataValidationTypeDecimal + typeList // inline use + DataValidationTypeTextLeng + DataValidationTypeTime + // DataValidationTypeWhole Integer + DataValidationTypeWhole +) + +const ( + // dataValidationFormulaStrLen 255 characters+ 2 quotes + dataValidationFormulaStrLen = 257 + // dataValidationFormulaStrLenErr + dataValidationFormulaStrLenErr = "data validation must be 0-255 characters" +) + +// DataValidationErrorStyle defined the style of data validation error alert. +type DataValidationErrorStyle int + +// Data validation error styles. +const ( + _ DataValidationErrorStyle = iota + DataValidationErrorStyleStop + DataValidationErrorStyleWarning + DataValidationErrorStyleInformation +) + +// Data validation error styles. +const ( + styleStop = "stop" + styleWarning = "warning" + styleInformation = "information" +) + +// DataValidationOperator operator enum. +type DataValidationOperator int + +// Data validation operators. +const ( + _DataValidationOperator = iota + DataValidationOperatorBetween + DataValidationOperatorEqual + DataValidationOperatorGreaterThan + DataValidationOperatorGreaterThanOrEqual + DataValidationOperatorLessThan + DataValidationOperatorLessThanOrEqual + DataValidationOperatorNotBetween + DataValidationOperatorNotEqual +) + +// NewDataValidation return data validation struct. +func NewDataValidation(allowBlank bool) *DataValidation { + return &DataValidation{ + AllowBlank: allowBlank, + ShowErrorMessage: false, + ShowInputMessage: false, + } +} + +// SetError set error notice. +func (dd *DataValidation) SetError(style DataValidationErrorStyle, title, msg string) { + dd.Error = &msg + dd.ErrorTitle = &title + strStyle := styleStop + switch style { + case DataValidationErrorStyleStop: + strStyle = styleStop + case DataValidationErrorStyleWarning: + strStyle = styleWarning + case DataValidationErrorStyleInformation: + strStyle = styleInformation + + } + dd.ShowErrorMessage = true + dd.ErrorStyle = &strStyle +} + +// SetInput set prompt notice. +func (dd *DataValidation) SetInput(title, msg string) { + dd.ShowInputMessage = true + dd.PromptTitle = &title + dd.Prompt = &msg +} + +// SetDropList data validation list. +func (dd *DataValidation) SetDropList(keys []string) error { + dd.Formula1 = "\"" + strings.Join(keys, ",") + "\"" + dd.Type = convDataValidationType(typeList) + return nil +} + +// SetRange provides function to set data validation range in drop list. +func (dd *DataValidation) SetRange(f1, f2 int, t DataValidationType, o DataValidationOperator) error { + formula1 := fmt.Sprintf("%d", f1) + formula2 := fmt.Sprintf("%d", f2) + if dataValidationFormulaStrLen < len(dd.Formula1) || dataValidationFormulaStrLen < len(dd.Formula2) { + return fmt.Errorf(dataValidationFormulaStrLenErr) + } + + dd.Formula1 = formula1 + dd.Formula2 = formula2 + dd.Type = convDataValidationType(t) + dd.Operator = convDataValidationOperatior(o) + return nil +} + +// SetSqrefDropList provides set data validation on a range with source +// reference range of the worksheet by given data validation object and +// worksheet name. The data validation object can be created by +// NewDataValidation function. For example, set data validation on +// Sheet1!A7:B8 with validation criteria source Sheet1!E1:E3 settings, create +// in-cell dropdown by allowing list source: +// +// dvRange := excelize.NewDataValidation(true) +// dvRange.Sqref = "A7:B8" +// dvRange.SetSqrefDropList("E1:E3", true) +// xlsx.AddDataValidation("Sheet1", dvRange) +// +func (dd *DataValidation) SetSqrefDropList(sqref string, isCurrentSheet bool) error { + if isCurrentSheet { + dd.Formula1 = sqref + dd.Type = convDataValidationType(typeList) + return nil + } + return fmt.Errorf("cross-sheet sqref cell are not supported") +} + +// SetSqref provides function to set data validation range in drop list. +func (dd *DataValidation) SetSqref(sqref string) { + if dd.Sqref == "" { + dd.Sqref = sqref + } else { + dd.Sqref = fmt.Sprintf("%s %s", dd.Sqref, sqref) + } +} + +// convDataValidationType get excel data validation type. +func convDataValidationType(t DataValidationType) string { + typeMap := map[DataValidationType]string{ + typeNone: "none", + DataValidationTypeCustom: "custom", + DataValidationTypeDate: "date", + DataValidationTypeDecimal: "decimal", + typeList: "list", + DataValidationTypeTextLeng: "textLength", + DataValidationTypeTime: "time", + DataValidationTypeWhole: "whole", + } + + return typeMap[t] + +} + +// convDataValidationOperatior get excel data validation operator. +func convDataValidationOperatior(o DataValidationOperator) string { + typeMap := map[DataValidationOperator]string{ + DataValidationOperatorBetween: "between", + DataValidationOperatorEqual: "equal", + DataValidationOperatorGreaterThan: "greaterThan", + DataValidationOperatorGreaterThanOrEqual: "greaterThanOrEqual", + DataValidationOperatorLessThan: "lessThan", + DataValidationOperatorLessThanOrEqual: "lessThanOrEqual", + DataValidationOperatorNotBetween: "notBetween", + DataValidationOperatorNotEqual: "notEqual", + } + + return typeMap[o] + +} + +// AddDataValidation provides set data validation on a range of the worksheet +// by given data validation object and worksheet name. The data validation +// object can be created by NewDataValidation function. +// +// Example 1, set data validation on Sheet1!A1:B2 with validation criteria +// settings, show error alert after invalid data is entered with "Stop" style +// and custom title "error body": +// +// dvRange := excelize.NewDataValidation(true) +// dvRange.Sqref = "A1:B2" +// dvRange.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorBetween) +// dvRange.SetError(excelize.DataValidationErrorStyleStop, "error title", "error body") +// xlsx.AddDataValidation("Sheet1", dvRange) +// +// Example 2, set data validation on Sheet1!A3:B4 with validation criteria +// settings, and show input message when cell is selected: +// +// dvRange = excelize.NewDataValidation(true) +// dvRange.Sqref = "A3:B4" +// dvRange.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorGreaterThan) +// dvRange.SetInput("input title", "input body") +// xlsx.AddDataValidation("Sheet1", dvRange) +// +// Example 3, set data validation on Sheet1!A5:B6 with validation criteria +// settings, create in-cell dropdown by allowing list source: +// +// dvRange = excelize.NewDataValidation(true) +// dvRange.Sqref = "A5:B6" +// dvRange.SetDropList([]string{"1", "2", "3"}) +// xlsx.AddDataValidation("Sheet1", dvRange) +// +func (f *File) AddDataValidation(sheet string, dv *DataValidation) { + xlsx := f.workSheetReader(sheet) + if nil == xlsx.DataValidations { + xlsx.DataValidations = new(xlsxDataValidations) + } + xlsx.DataValidations.DataValidation = append(xlsx.DataValidations.DataValidation, dv) + xlsx.DataValidations.Count = len(xlsx.DataValidations.DataValidation) +} diff --git a/datavalidation_test.go b/datavalidation_test.go new file mode 100644 index 0000000..39dd229 --- /dev/null +++ b/datavalidation_test.go @@ -0,0 +1,57 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + +package excelize + +import "testing" + +func TestDataValidation(t *testing.T) { + xlsx := NewFile() + + dvRange := NewDataValidation(true) + dvRange.Sqref = "A1:B2" + dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorBetween) + dvRange.SetError(DataValidationErrorStyleStop, "error title", "error body") + dvRange.SetError(DataValidationErrorStyleWarning, "error title", "error body") + dvRange.SetError(DataValidationErrorStyleInformation, "error title", "error body") + xlsx.AddDataValidation("Sheet1", dvRange) + + dvRange = NewDataValidation(true) + dvRange.Sqref = "A3:B4" + dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorGreaterThan) + dvRange.SetInput("input title", "input body") + xlsx.AddDataValidation("Sheet1", dvRange) + + dvRange = NewDataValidation(true) + dvRange.Sqref = "A5:B6" + dvRange.SetDropList([]string{"1", "2", "3"}) + xlsx.AddDataValidation("Sheet1", dvRange) + + xlsx.SetCellStr("Sheet1", "E1", "E1") + xlsx.SetCellStr("Sheet1", "E2", "E2") + xlsx.SetCellStr("Sheet1", "E3", "E3") + dvRange = NewDataValidation(true) + dvRange.SetSqref("A7:B8") + dvRange.SetSqref("A7:B8") + dvRange.SetSqrefDropList("$E$1:$E$3", true) + err := dvRange.SetSqrefDropList("$E$1:$E$3", false) + t.Log(err) + xlsx.AddDataValidation("Sheet1", dvRange) + + dvRange = NewDataValidation(true) + dvRange.SetDropList(make([]string, 258)) + err = dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorGreaterThan) + t.Log(err) + + // Test write file to given path. + err = xlsx.SaveAs("./test/Book_data_validation.xlsx") + if err != nil { + t.Error(err) + } +} @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -8,17 +17,31 @@ import ( // timeLocationUTC defined the UTC time location. var timeLocationUTC, _ = time.LoadLocation("UTC") -// timeToUTCTime provides function to convert time to UTC time. +// timeToUTCTime provides a function to convert time to UTC time. func timeToUTCTime(t time.Time) time.Time { return time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), timeLocationUTC) } -// timeToExcelTime provides function to convert time to Excel time. +// timeToExcelTime provides a function to convert time to Excel time. func timeToExcelTime(t time.Time) float64 { - return float64(t.UnixNano())/8.64e13 + 25569.0 + // TODO in future this should probably also handle date1904 and like TimeFromExcelTime + var excelTime float64 + var deltaDays int64 + excelTime = 0 + deltaDays = 290 * 364 + // check if UnixNano would be out of int64 range + for t.Unix() > deltaDays*24*60*60 { + // reduce by aprox. 290 years, which is max for int64 nanoseconds + delta := time.Duration(deltaDays) * 24 * time.Hour + excelTime = excelTime + float64(deltaDays) + t = t.Add(-delta) + } + // finally add remainder of UnixNano to keep nano precision + // and 25569 which is days between 1900 and 1970 + return excelTime + float64(t.UnixNano())/8.64e13 + 25569.0 } -// shiftJulianToNoon provides function to process julian date to noon. +// shiftJulianToNoon provides a function to process julian date to noon. func shiftJulianToNoon(julianDays, julianFraction float64) (float64, float64) { switch { case -0.5 < julianFraction && julianFraction < 0.5: @@ -33,7 +56,7 @@ func shiftJulianToNoon(julianDays, julianFraction float64) (float64, float64) { return julianDays, julianFraction } -// fractionOfADay provides function to return the integer values for hour, +// fractionOfADay provides a function to return the integer values for hour, // minutes, seconds and nanoseconds that comprised a given fraction of a day. // values would round to 1 us. func fractionOfADay(fraction float64) (hours, minutes, seconds, nanoseconds int) { @@ -54,7 +77,7 @@ func fractionOfADay(fraction float64) (hours, minutes, seconds, nanoseconds int) return } -// julianDateToGregorianTime provides function to convert julian date to +// julianDateToGregorianTime provides a function to convert julian date to // gregorian time. func julianDateToGregorianTime(part1, part2 float64) time.Time { part1I, part1F := math.Modf(part1) @@ -67,12 +90,12 @@ func julianDateToGregorianTime(part1, part2 float64) time.Time { return time.Date(year, time.Month(month), day, hours, minutes, seconds, nanoseconds, time.UTC) } -// By this point generations of programmers have repeated the algorithm sent to -// the editor of "Communications of the ACM" in 1968 (published in CACM, volume -// 11, number 10, October 1968, p.657). None of those programmers seems to have -// found it necessary to explain the constants or variable names set out by -// Henry F. Fliegel and Thomas C. Van Flandern. Maybe one day I'll buy that -// jounal and expand an explanation here - that day is not today. +// By this point generations of programmers have repeated the algorithm sent +// to the editor of "Communications of the ACM" in 1968 (published in CACM, +// volume 11, number 10, October 1968, p.657). None of those programmers seems +// to have found it necessary to explain the constants or variable names set +// out by Henry F. Fliegel and Thomas C. Van Flandern. Maybe one day I'll buy +// that jounal and expand an explanation here - that day is not today. func doTheFliegelAndVanFlandernAlgorithm(jd int) (day, month, year int) { l := jd + 68569 n := (4 * l) / 146097 @@ -87,9 +110,10 @@ func doTheFliegelAndVanFlandernAlgorithm(jd int) (day, month, year int) { return d, m, y } -// timeFromExcelTime provides function to convert an excelTime representation -// (stored as a floating point number) to a time.Time. +// timeFromExcelTime provides a function to convert an excelTime +// representation (stored as a floating point number) to a time.Time. func timeFromExcelTime(excelTime float64, date1904 bool) time.Time { + const MDD int64 = 106750 // Max time.Duration Days, aprox. 290 years var date time.Time var intPart = int64(excelTime) // Excel uses Julian dates prior to March 1st 1900, and Gregorian @@ -113,6 +137,13 @@ func timeFromExcelTime(excelTime float64, date1904 bool) time.Time { } else { date = time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC) } + + // Duration is limited to aprox. 290 years + for intPart > MDD { + durationDays := time.Duration(MDD) * time.Hour * 24 + date = date.Add(durationDays) + intPart = intPart - MDD + } durationDays := time.Duration(intPart) * time.Hour * 24 durationPart := time.Duration(dayNanoSeconds * floatPart) return date.Add(durationDays).Add(durationPart) diff --git a/date_test.go b/date_test.go new file mode 100644 index 0000000..06421b8 --- /dev/null +++ b/date_test.go @@ -0,0 +1,42 @@ +package excelize + +import ( + "testing" + "time" +) + +type dateTest struct { + ExcelValue float64 + GoValue time.Time +} + +func TestTimeToExcelTime(t *testing.T) { + trueExpectedInputList := []dateTest{ + {0.0, time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)}, + {25569.0, time.Unix(0, 0)}, + {43269.0, time.Date(2018, 6, 18, 0, 0, 0, 0, time.UTC)}, + {401769.0, time.Date(3000, 1, 1, 0, 0, 0, 0, time.UTC)}, + } + + for _, test := range trueExpectedInputList { + if test.ExcelValue != timeToExcelTime(test.GoValue) { + t.Fatalf("Expected %v from %v = true, got %v\n", test.ExcelValue, test.GoValue, timeToExcelTime(test.GoValue)) + } + } +} + +func TestTimeFromExcelTime(t *testing.T) { + trueExpectedInputList := []dateTest{ + {0.0, time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)}, + {60.0, time.Date(1900, 2, 28, 0, 0, 0, 0, time.UTC)}, + {61.0, time.Date(1900, 3, 1, 0, 0, 0, 0, time.UTC)}, + {41275.0, time.Date(2013, 1, 1, 0, 0, 0, 0, time.UTC)}, + {401769.0, time.Date(3000, 1, 1, 0, 0, 0, 0, time.UTC)}, + } + + for _, test := range trueExpectedInputList { + if test.GoValue != timeFromExcelTime(test.ExcelValue, false) { + t.Fatalf("Expected %v from %v = true, got %v\n", test.GoValue, test.ExcelValue, timeFromExcelTime(test.ExcelValue, false)) + } + } +} diff --git a/excelize.go b/excelize.go index 0972e96..d1f0b7f 100644 --- a/excelize.go +++ b/excelize.go @@ -1,3 +1,13 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. +// +// See https://xuri.me/excelize for more information about this package. package excelize import ( @@ -21,6 +31,7 @@ type File struct { Sheet map[string]*xlsxWorksheet SheetCount int Styles *xlsxStyleSheet + Theme *xlsxTheme WorkBook *xlsxWorkbook WorkBookRels *xlsxWorkbookRels XLSX map[string][]byte @@ -66,10 +77,11 @@ func OpenReader(r io.Reader) (*File, error) { } f.sheetMap = f.getSheetMap() f.Styles = f.stylesReader() + f.Theme = f.themeReader() return f, nil } -// setDefaultTimeStyle provides function to set default numbers format for +// setDefaultTimeStyle provides a function to set default numbers format for // time.Time type cell value by given worksheet name, cell coordinates and // number format code. func (f *File) setDefaultTimeStyle(sheet, axis string, format int) { @@ -79,8 +91,8 @@ func (f *File) setDefaultTimeStyle(sheet, axis string, format int) { } } -// workSheetReader provides function to get the pointer to the structure after -// deserialization by given worksheet name. +// workSheetReader provides a function to get the pointer to the structure +// after deserialization by given worksheet name. func (f *File) workSheetReader(sheet string) *xlsxWorksheet { name, ok := f.sheetMap[trimSheetName(sheet)] if !ok { @@ -103,7 +115,7 @@ func (f *File) workSheetReader(sheet string) *xlsxWorksheet { return f.Sheet[name] } -// checkSheet provides function to fill each row element and make that is +// checkSheet provides a function to fill each row element and make that is // continuous in a worksheet of XML. func checkSheet(xlsx *xlsxWorksheet) { row := len(xlsx.SheetData.Row) @@ -131,7 +143,7 @@ func checkSheet(xlsx *xlsxWorksheet) { xlsx.SheetData = sheetData } -// replaceWorkSheetsRelationshipsNameSpaceBytes provides function to replace +// replaceWorkSheetsRelationshipsNameSpaceBytes provides a function to replace // xl/worksheets/sheet%d.xml XML tags to self-closing for compatible Microsoft // Office Excel 2007. func replaceWorkSheetsRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte { @@ -180,7 +192,7 @@ func (f *File) UpdateLinkedValue() { } } -// adjustHelper provides function to adjust rows and columns dimensions, +// adjustHelper provides a function to adjust rows and columns dimensions, // hyperlinks, merged cells and auto filter when inserting or deleting rows or // columns. // @@ -202,7 +214,7 @@ func (f *File) adjustHelper(sheet string, column, row, offset int) { checkRow(xlsx) } -// adjustColDimensions provides function to update column dimensions when +// adjustColDimensions provides a function to update column dimensions when // inserting or deleting rows or columns. func (f *File) adjustColDimensions(xlsx *xlsxWorksheet, column, offset int) { for i, r := range xlsx.SheetData.Row { @@ -218,8 +230,8 @@ func (f *File) adjustColDimensions(xlsx *xlsxWorksheet, column, offset int) { } } -// adjustRowDimensions provides function to update row dimensions when inserting -// or deleting rows or columns. +// adjustRowDimensions provides a function to update row dimensions when +// inserting or deleting rows or columns. func (f *File) adjustRowDimensions(xlsx *xlsxWorksheet, rowIndex, offset int) { if rowIndex == -1 { return @@ -238,7 +250,7 @@ func (f *File) adjustRowDimensions(xlsx *xlsxWorksheet, rowIndex, offset int) { } } -// adjustHyperlinks provides function to update hyperlinks when inserting or +// adjustHyperlinks provides a function to update hyperlinks when inserting or // deleting rows or columns. func (f *File) adjustHyperlinks(sheet string, column, rowIndex, offset int) { xlsx := f.workSheetReader(sheet) @@ -278,8 +290,8 @@ func (f *File) adjustHyperlinks(sheet string, column, rowIndex, offset int) { } } -// adjustMergeCellsHelper provides function to update merged cells when inserting or -// deleting rows or columns. +// adjustMergeCellsHelper provides a function to update merged cells when +// inserting or deleting rows or columns. func (f *File) adjustMergeCellsHelper(xlsx *xlsxWorksheet, column, rowIndex, offset int) { if xlsx.MergeCells != nil { for k, v := range xlsx.MergeCells.Cells { @@ -319,8 +331,8 @@ func (f *File) adjustMergeCellsHelper(xlsx *xlsxWorksheet, column, rowIndex, off } } -// adjustMergeCells provides function to update merged cells when inserting or -// deleting rows or columns. +// adjustMergeCells provides a function to update merged cells when inserting +// or deleting rows or columns. func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, column, rowIndex, offset int) { f.adjustMergeCellsHelper(xlsx, column, rowIndex, offset) @@ -340,8 +352,8 @@ func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, column, rowIndex, offset in } } -// adjustAutoFilter provides function to update the auto filter when inserting -// or deleting rows or columns. +// adjustAutoFilter provides a function to update the auto filter when +// inserting or deleting rows or columns. func (f *File) adjustAutoFilter(xlsx *xlsxWorksheet, column, rowIndex, offset int) { f.adjustAutoFilterHelper(xlsx, column, rowIndex, offset) @@ -374,7 +386,7 @@ func (f *File) adjustAutoFilter(xlsx *xlsxWorksheet, column, rowIndex, offset in } } -// adjustAutoFilterHelper provides function to update the auto filter when +// adjustAutoFilterHelper provides a function to update the auto filter when // inserting or deleting rows or columns. func (f *File) adjustAutoFilterHelper(xlsx *xlsxWorksheet, column, rowIndex, offset int) { if xlsx.AutoFilter != nil { diff --git a/excelize_test.go b/excelize_test.go index 4dbc709..9f738f3 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -2,6 +2,7 @@ package excelize import ( "fmt" + "image/color" _ "image/gif" _ "image/jpeg" _ "image/png" @@ -88,7 +89,6 @@ func TestOpenFile(t *testing.T) { xlsx.SetCellValue("Sheet2", "F16", true) xlsx.SetCellValue("Sheet2", "F17", complex64(5+10i)) t.Log(letterOnlyMapF('x')) - t.Log(deepCopy(nil, nil)) shiftJulianToNoon(1, -0.6) timeFromExcelTime(61, true) timeFromExcelTime(62, true) @@ -162,6 +162,24 @@ func TestAddPicture(t *testing.T) { if err != nil { t.Log(err) } + err = xlsx.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", "jpg", make([]byte, 1)) + if err != nil { + t.Log(err) + } + // Test add picture to worksheet with invalid file data. + err = xlsx.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", ".jpg", make([]byte, 1)) + if err != nil { + t.Log(err) + } + file, err := ioutil.ReadFile("./test/images/excel.jpg") + if err != nil { + t.Error(err) + } + // Test add picture to worksheet from bytes. + err = xlsx.AddPictureFromBytes("Sheet1", "Q1", "", "Excel Logo", ".jpg", file) + if err != nil { + t.Log(err) + } // Test write file to given path. err = xlsx.SaveAs("./test/Book2.xlsx") if err != nil { @@ -211,9 +229,14 @@ func TestNewFile(t *testing.T) { if err != nil { t.Error(err) } - // Test add picture to worksheet with invalid formatset + // Test add picture to worksheet without formatset. err = xlsx.AddPicture("Sheet1", "C2", "./test/images/excel.png", "") if err != nil { + t.Error(err) + } + // Test add picture to worksheet with invalid formatset. + err = xlsx.AddPicture("Sheet1", "C2", "./test/images/excel.png", `{`) + if err != nil { t.Log(err) } err = xlsx.SaveAs("./test/Book3.xlsx") @@ -771,10 +794,6 @@ func TestAddTable(t *testing.T) { if err != nil { t.Error(err) } - err = xlsx.AddTable("Sheet2", "A2", "B5", ``) - if err != nil { - t.Log(err) - } err = xlsx.AddTable("Sheet2", "A2", "B5", `{"table_name":"table","table_style":"TableStyleMedium2", "show_first_column":true,"show_last_column":true,"show_row_stripes":false,"show_column_stripes":true}`) if err != nil { t.Error(err) @@ -817,6 +836,11 @@ func TestAddComments(t *testing.T) { if err != nil { t.Error(err) } + allComments := xlsx.GetComments() + if len(allComments) != 2 { + t.Error("Expected 2 comment entry elements.") + } + } func TestAutoFilter(t *testing.T) { @@ -1146,6 +1170,31 @@ func TestOutlineLevel(t *testing.T) { xlsx.SetColOutlineLevel("Sheet2", "B", 2) } +func TestThemeColor(t *testing.T) { + t.Log(ThemeColor("000000", -0.1)) + t.Log(ThemeColor("000000", 0)) + t.Log(ThemeColor("000000", 1)) +} + +func TestHSL(t *testing.T) { + var hsl HSL + t.Log(hsl.RGBA()) + t.Log(hslModel(hsl)) + t.Log(hslModel(color.Gray16{Y: uint16(1)})) + t.Log(HSLToRGB(0, 1, 0.4)) + t.Log(HSLToRGB(0, 1, 0.6)) + t.Log(hueToRGB(0, 0, -1)) + t.Log(hueToRGB(0, 0, 2)) + t.Log(hueToRGB(0, 0, 1.0/7)) + t.Log(hueToRGB(0, 0, 0.4)) + t.Log(hueToRGB(0, 0, 2.0/4)) + t.Log(RGBToHSL(255, 255, 0)) + t.Log(RGBToHSL(0, 255, 255)) + t.Log(RGBToHSL(250, 100, 50)) + t.Log(RGBToHSL(50, 100, 250)) + t.Log(RGBToHSL(250, 50, 100)) +} + func trimSliceSpace(s []string) []string { for { if len(s) > 0 && s[len(s)-1] == "" { @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -8,7 +17,7 @@ import ( "os" ) -// NewFile provides function to create new file by default template. For +// NewFile provides a function to create new file by default template. For // example: // // xlsx := NewFile() @@ -36,19 +45,20 @@ func NewFile() *File { f.WorkBookRels = f.workbookRelsReader() f.Sheet["xl/worksheets/sheet1.xml"] = f.workSheetReader("Sheet1") f.sheetMap["Sheet1"] = "xl/worksheets/sheet1.xml" + f.Theme = f.themeReader() return f } -// Save provides function to override the xlsx file with origin path. +// Save provides a function to override the xlsx file with origin path. func (f *File) Save() error { if f.Path == "" { - return fmt.Errorf("No path defined for file, consider File.WriteTo or File.Write") + return fmt.Errorf("no path defined for file, consider File.WriteTo or File.Write") } return f.SaveAs(f.Path) } -// SaveAs provides function to create or update to an xlsx file at the provided -// path. +// SaveAs provides a function to create or update to an xlsx file at the +// provided path. func (f *File) SaveAs(name string) error { file, err := os.OpenFile(name, os.O_WRONLY|os.O_TRUNC|os.O_CREATE, 0666) if err != nil { @@ -58,8 +68,14 @@ func (f *File) SaveAs(name string) error { return f.Write(file) } -// Write provides function to write to an io.Writer. +// Write provides a function to write to an io.Writer. func (f *File) Write(w io.Writer) error { + _, err := f.WriteTo(w) + return err +} + +// WriteTo implements io.WriterTo to write the file. +func (f *File) WriteTo(w io.Writer) (int64, error) { buf := new(bytes.Buffer) zw := zip.NewWriter(buf) f.contentTypesWriter() @@ -70,21 +86,17 @@ func (f *File) Write(w io.Writer) error { for path, content := range f.XLSX { fi, err := zw.Create(path) if err != nil { - return err + return 0, err } _, err = fi.Write(content) if err != nil { - return err + return 0, err } } err := zw.Close() if err != nil { - return err - } - - if _, err := buf.WriteTo(w); err != nil { - return err + return 0, err } - return nil + return buf.WriteTo(w) } @@ -0,0 +1,139 @@ +// Copyright (c) 2012 Rodrigo Moraes. All rights reserved. +// +// Redistribution and use in source and binary forms, with or without +// modification, are permitted provided that the following conditions are +// met: +// +// * Redistributions of source code must retain the above copyright +// notice, this list of conditions and the following disclaimer. +// * Redistributions in binary form must reproduce the above +// copyright notice, this list of conditions and the following disclaimer +// in the documentation and/or other materials provided with the +// distribution. +// * Neither the name of Google Inc. nor the names of its +// contributors may be used to endorse or promote products derived from +// this software without specific prior written permission. +// +// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS +// "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT +// LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR +// A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT +// OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, +// SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT +// LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, +// DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY +// THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +// (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE +// OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + +package excelize + +import ( + "image/color" + "math" +) + +// HSLModel converts any color.Color to a HSL color. +var HSLModel = color.ModelFunc(hslModel) + +// HSL represents a cylindrical coordinate of points in an RGB color model. +// +// Values are in the range 0 to 1. +type HSL struct { + H, S, L float64 +} + +// RGBA returns the alpha-premultiplied red, green, blue and alpha values +// for the HSL. +func (c HSL) RGBA() (uint32, uint32, uint32, uint32) { + r, g, b := HSLToRGB(c.H, c.S, c.L) + return uint32(r) * 0x101, uint32(g) * 0x101, uint32(b) * 0x101, 0xffff +} + +// hslModel converts a color.Color to HSL. +func hslModel(c color.Color) color.Color { + if _, ok := c.(HSL); ok { + return c + } + r, g, b, _ := c.RGBA() + h, s, l := RGBToHSL(uint8(r>>8), uint8(g>>8), uint8(b>>8)) + return HSL{h, s, l} +} + +// RGBToHSL converts an RGB triple to a HSL triple. +func RGBToHSL(r, g, b uint8) (h, s, l float64) { + fR := float64(r) / 255 + fG := float64(g) / 255 + fB := float64(b) / 255 + max := math.Max(math.Max(fR, fG), fB) + min := math.Min(math.Min(fR, fG), fB) + l = (max + min) / 2 + if max == min { + // Achromatic. + h, s = 0, 0 + } else { + // Chromatic. + d := max - min + if l > 0.5 { + s = d / (2.0 - max - min) + } else { + s = d / (max + min) + } + switch max { + case fR: + h = (fG - fB) / d + if fG < fB { + h += 6 + } + case fG: + h = (fB-fR)/d + 2 + case fB: + h = (fR-fG)/d + 4 + } + h /= 6 + } + return +} + +// HSLToRGB converts an HSL triple to a RGB triple. +func HSLToRGB(h, s, l float64) (r, g, b uint8) { + var fR, fG, fB float64 + if s == 0 { + fR, fG, fB = l, l, l + } else { + var q float64 + if l < 0.5 { + q = l * (1 + s) + } else { + q = l + s - s*l + } + p := 2*l - q + fR = hueToRGB(p, q, h+1.0/3) + fG = hueToRGB(p, q, h) + fB = hueToRGB(p, q, h-1.0/3) + } + r = uint8((fR * 255) + 0.5) + g = uint8((fG * 255) + 0.5) + b = uint8((fB * 255) + 0.5) + return +} + +// hueToRGB is a helper function for HSLToRGB. +func hueToRGB(p, q, t float64) float64 { + if t < 0 { + t++ + } + if t > 1 { + t-- + } + if t < 1.0/6 { + return p + (q-p)*6*t + } + if t < 0.5 { + return q + } + if t < 2.0/3 { + return p + (q-p)*(2.0/3-t)*6 + } + return p +} @@ -1,9 +1,17 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( "archive/zip" "bytes" - "encoding/gob" "io" "log" "math" @@ -26,7 +34,7 @@ func ReadZipReader(r *zip.Reader) (map[string][]byte, int, error) { return fileList, worksheets, nil } -// readXML provides function to read XML content as string. +// readXML provides a function to read XML content as string. func (f *File) readXML(name string) []byte { if content, ok := f.XLSX[name]; ok { return content @@ -34,8 +42,8 @@ func (f *File) readXML(name string) []byte { return []byte{} } -// saveFileList provides function to update given file content in file list of -// XLSX. +// saveFileList provides a function to update given file content in file list +// of XLSX. func (f *File) saveFileList(name string, content []byte) { newContent := make([]byte, 0, len(XMLHeader)+len(content)) newContent = append(newContent, []byte(XMLHeader)...) @@ -55,7 +63,7 @@ func readFile(file *zip.File) []byte { return buff.Bytes() } -// ToAlphaString provides function to convert integer to Excel sheet column +// ToAlphaString provides a function to convert integer to Excel sheet column // title. For example convert 36 to column title AK: // // excelize.ToAlphaString(36) @@ -73,9 +81,9 @@ func ToAlphaString(value int) string { return ans } -// TitleToNumber provides function to convert Excel sheet column title to int -// (this function doesn't do value check currently). For example convert AK -// and ak to column title 36: +// TitleToNumber provides a function to convert Excel sheet column title to +// int (this function doesn't do value check currently). For example convert +// AK and ak to column title 36: // // excelize.TitleToNumber("AK") // excelize.TitleToNumber("ak") @@ -115,17 +123,6 @@ func intOnlyMapF(rune rune) rune { return -1 } -// deepCopy provides method to creates a deep copy of whatever is passed to it -// and returns the copy in an interface. The returned value will need to be -// asserted to the correct type. -func deepCopy(dst, src interface{}) error { - var buf bytes.Buffer - if err := gob.NewEncoder(&buf).Encode(src); err != nil { - return err - } - return gob.NewDecoder(bytes.NewBuffer(buf.Bytes())).Decode(dst) -} - // boolPtr returns a pointer to a bool with the given value. func boolPtr(b bool) *bool { return &b } @@ -137,8 +134,8 @@ func defaultTrue(b *bool) bool { return *b } -// axisLowerOrEqualThan returns true if axis1 <= axis2 -// axis1/axis2 can be either a column or a row axis, e.g. "A", "AAE", "42", "1", etc. +// axisLowerOrEqualThan returns true if axis1 <= axis2 axis1/axis2 can be +// either a column or a row axis, e.g. "A", "AAE", "42", "1", etc. // // For instance, the following comparisons are all true: // @@ -159,7 +156,8 @@ func axisLowerOrEqualThan(axis1, axis2 string) bool { } } -// getCellColRow returns the two parts of a cell identifier (its col and row) as strings +// getCellColRow returns the two parts of a cell identifier (its col and row) +// as strings // // For instance: // @@ -176,3 +174,12 @@ func getCellColRow(cell string) (col, row string) { return cell, "" } + +// parseFormatSet provides a method to convert format string to []byte and +// handle empty string. +func parseFormatSet(formatSet string) []byte { + if formatSet != "" { + return []byte(formatSet) + } + return []byte("{}") +} @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -14,8 +23,8 @@ import ( "strings" ) -// parseFormatPictureSet provides function to parse the format settings of the -// picture with default value. +// parseFormatPictureSet provides a function to parse the format settings of +// the picture with default value. func parseFormatPictureSet(formatSet string) (*formatPicture, error) { format := formatPicture{ FPrintsWithSheet: true, @@ -26,7 +35,7 @@ func parseFormatPictureSet(formatSet string) (*formatPicture, error) { XScale: 1.0, YScale: 1.0, } - err := json.Unmarshal([]byte(formatSet), &format) + err := json.Unmarshal(parseFormatSet(formatSet), &format) return &format, err } @@ -78,23 +87,66 @@ func parseFormatPictureSet(formatSet string) (*formatPicture, error) { // positioning is move and size with cells. func (f *File) AddPicture(sheet, cell, picture, format string) error { var err error - var drawingHyperlinkRID int - var hyperlinkType string // Check picture exists first. if _, err = os.Stat(picture); os.IsNotExist(err) { return err } ext, ok := supportImageTypes[path.Ext(picture)] if !ok { - return errors.New("Unsupported image extension") + return errors.New("unsupported image extension") + } + file, _ := ioutil.ReadFile(picture) + _, name := filepath.Split(picture) + return f.AddPictureFromBytes(sheet, cell, format, name, ext, file) +} + +// AddPictureFromBytes provides the method to add picture in a sheet by given +// picture format set (such as offset, scale, aspect ratio setting and print +// settings), file base name, extension name and file bytes. For example: +// +// package main +// +// import ( +// "fmt" +// _ "image/jpeg" +// "io/ioutil" +// +// "github.com/360EntSecGroup-Skylar/excelize" +// ) +// +// func main() { +// xlsx := excelize.NewFile() +// +// file, err := ioutil.ReadFile("./image1.jpg") +// if err != nil { +// fmt.Println(err) +// } +// err = xlsx.AddPictureFromBytes("Sheet1", "A2", "", "Excel Logo", ".jpg", file) +// if err != nil { +// fmt.Println(err) +// } +// err = xlsx.SaveAs("./Book1.xlsx") +// if err != nil { +// fmt.Println(err) +// } +// } +// +func (f *File) AddPictureFromBytes(sheet, cell, format, name, extension string, file []byte) error { + var err error + var drawingHyperlinkRID int + var hyperlinkType string + ext, ok := supportImageTypes[extension] + if !ok { + return errors.New("unsupported image extension") } - readFile, _ := os.Open(picture) - image, _, _ := image.DecodeConfig(readFile) - _, file := filepath.Split(picture) formatSet, err := parseFormatPictureSet(format) if err != nil { return err } + image, _, err := image.DecodeConfig(bytes.NewReader(file)) + if err != nil { + return err + } // Read sheet data. xlsx := f.workSheetReader(sheet) // Add first picture for given sheet, create xl/drawings/ and xl/drawings/_rels/ folder. @@ -110,13 +162,13 @@ func (f *File) AddPicture(sheet, cell, picture, format string) error { } drawingHyperlinkRID = f.addDrawingRelationships(drawingID, SourceRelationshipHyperLink, formatSet.Hyperlink, hyperlinkType) } - f.addDrawingPicture(sheet, drawingXML, cell, file, image.Width, image.Height, drawingRID, drawingHyperlinkRID, formatSet) - f.addMedia(picture, ext) + f.addDrawingPicture(sheet, drawingXML, cell, name, image.Width, image.Height, drawingRID, drawingHyperlinkRID, formatSet) + f.addMedia(file, ext) f.addContentTypePart(drawingID, "drawings") return err } -// addSheetRelationships provides function to add +// addSheetRelationships provides a function to add // xl/worksheets/_rels/sheet%d.xml.rels by given worksheet name, relationship // type and target. func (f *File) addSheetRelationships(sheet, relType, target, targetMode string) int { @@ -149,9 +201,9 @@ func (f *File) addSheetRelationships(sheet, relType, target, targetMode string) return rID } -// deleteSheetRelationships provides function to delete relationships in -// xl/worksheets/_rels/sheet%d.xml.rels by given worksheet name and relationship -// index. +// deleteSheetRelationships provides a function to delete relationships in +// xl/worksheets/_rels/sheet%d.xml.rels by given worksheet name and +// relationship index. func (f *File) deleteSheetRelationships(sheet, rID string) { name, ok := f.sheetMap[trimSheetName(sheet)] if !ok { @@ -169,7 +221,7 @@ func (f *File) deleteSheetRelationships(sheet, rID string) { f.saveFileList(rels, output) } -// addSheetLegacyDrawing provides function to add legacy drawing element to +// addSheetLegacyDrawing provides a function to add legacy drawing element to // xl/worksheets/sheet%d.xml by given worksheet name and relationship index. func (f *File) addSheetLegacyDrawing(sheet string, rID int) { xlsx := f.workSheetReader(sheet) @@ -178,7 +230,7 @@ func (f *File) addSheetLegacyDrawing(sheet string, rID int) { } } -// addSheetDrawing provides function to add drawing element to +// addSheetDrawing provides a function to add drawing element to // xl/worksheets/sheet%d.xml by given worksheet name and relationship index. func (f *File) addSheetDrawing(sheet string, rID int) { xlsx := f.workSheetReader(sheet) @@ -187,7 +239,7 @@ func (f *File) addSheetDrawing(sheet string, rID int) { } } -// addSheetPicture provides function to add picture element to +// addSheetPicture provides a function to add picture element to // xl/worksheets/sheet%d.xml by given worksheet name and relationship index. func (f *File) addSheetPicture(sheet string, rID int) { xlsx := f.workSheetReader(sheet) @@ -196,7 +248,7 @@ func (f *File) addSheetPicture(sheet string, rID int) { } } -// countDrawings provides function to get drawing files count storage in the +// countDrawings provides a function to get drawing files count storage in the // folder xl/drawings. func (f *File) countDrawings() int { count := 0 @@ -208,7 +260,7 @@ func (f *File) countDrawings() int { return count } -// addDrawingPicture provides function to add picture by given sheet, +// addDrawingPicture provides a function to add picture by given sheet, // drawingXML, cell, file name, width, height relationship index and format // sets. func (f *File) addDrawingPicture(sheet, drawingXML, cell, file string, width, height, rID, hyperlinkRID int, formatSet *formatPicture) { @@ -263,8 +315,8 @@ func (f *File) addDrawingPicture(sheet, drawingXML, cell, file string, width, he f.saveFileList(drawingXML, output) } -// addDrawingRelationships provides function to add image part relationships in -// the file xl/drawings/_rels/drawing%d.xml.rels by given drawing index, +// addDrawingRelationships provides a function to add image part relationships +// in the file xl/drawings/_rels/drawing%d.xml.rels by given drawing index, // relationship type and target. func (f *File) addDrawingRelationships(index int, relType, target, targetMode string) int { var rels = "xl/drawings/_rels/drawing" + strconv.Itoa(index) + ".xml.rels" @@ -292,8 +344,8 @@ func (f *File) addDrawingRelationships(index int, relType, target, targetMode st return rID } -// countMedia provides function to get media files count storage in the folder -// xl/media/image. +// countMedia provides a function to get media files count storage in the +// folder xl/media/image. func (f *File) countMedia() int { count := 0 for k := range f.XLSX { @@ -304,17 +356,16 @@ func (f *File) countMedia() int { return count } -// addMedia provides function to add picture into folder xl/media/image by given -// file name and extension name. -func (f *File) addMedia(file, ext string) { +// addMedia provides a function to add picture into folder xl/media/image by +// given file and extension name. +func (f *File) addMedia(file []byte, ext string) { count := f.countMedia() - dat, _ := ioutil.ReadFile(file) media := "xl/media/image" + strconv.Itoa(count+1) + ext - f.XLSX[media] = dat + f.XLSX[media] = file } -// setContentTypePartImageExtensions provides function to set the content type -// for relationship parts and the Main Document part. +// setContentTypePartImageExtensions provides a function to set the content +// type for relationship parts and the Main Document part. func (f *File) setContentTypePartImageExtensions() { var imageTypes = map[string]bool{"jpeg": false, "png": false, "gif": false} content := f.contentTypesReader() @@ -334,7 +385,7 @@ func (f *File) setContentTypePartImageExtensions() { } } -// setContentTypePartVMLExtensions provides function to set the content type +// setContentTypePartVMLExtensions provides a function to set the content type // for relationship parts and the Main Document part. func (f *File) setContentTypePartVMLExtensions() { vml := false @@ -352,8 +403,8 @@ func (f *File) setContentTypePartVMLExtensions() { } } -// addContentTypePart provides function to add content type part relationships -// in the file [Content_Types].xml by given index. +// addContentTypePart provides a function to add content type part +// relationships in the file [Content_Types].xml by given index. func (f *File) addContentTypePart(index int, contentType string) { setContentType := map[string]func(){ "comments": f.setContentTypePartVMLExtensions, @@ -387,7 +438,7 @@ func (f *File) addContentTypePart(index int, contentType string) { }) } -// getSheetRelationshipsTargetByID provides function to get Target attribute +// getSheetRelationshipsTargetByID provides a function to get Target attribute // value in xl/worksheets/_rels/sheet%d.xml.rels by given worksheet name and // relationship index. func (f *File) getSheetRelationshipsTargetByID(sheet, rID string) string { @@ -406,9 +457,9 @@ func (f *File) getSheetRelationshipsTargetByID(sheet, rID string) string { return "" } -// GetPicture provides function to get picture base name and raw content embed -// in XLSX by given worksheet and cell name. This function returns the file name -// in XLSX and file contents as []byte data types. For example: +// GetPicture provides a function to get picture base name and raw content +// embed in XLSX by given worksheet and cell name. This function returns the +// file name in XLSX and file contents as []byte data types. For example: // // xlsx, err := excelize.OpenFile("./Book1.xlsx") // if err != nil { @@ -463,8 +514,9 @@ func (f *File) GetPicture(sheet, cell string) (string, []byte) { return "", []byte{} } -// getDrawingRelationships provides function to get drawing relationships from -// xl/drawings/_rels/drawing%s.xml.rels by given file name and relationship ID. +// getDrawingRelationships provides a function to get drawing relationships +// from xl/drawings/_rels/drawing%s.xml.rels by given file name and +// relationship ID. func (f *File) getDrawingRelationships(rels, rID string) *xlsxWorkbookRelation { _, ok := f.XLSX[rels] if !ok { @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -132,7 +141,7 @@ func (err ErrSheetNotExist) Error() string { // Rows return a rows iterator. For example: // -// rows, err := xlsx.GetRows("Sheet1") +// rows, err := xlsx.Rows("Sheet1") // for rows.Next() { // for _, colCell := range rows.Columns() { // fmt.Print(colCell, "\t") @@ -202,7 +211,7 @@ func (f *File) SetRowHeight(sheet string, row int, height float64) { xlsx.SheetData.Row[rowIdx].CustomHeight = true } -// getRowHeight provides function to get row height in pixels by given sheet +// getRowHeight provides a function to get row height in pixels by given sheet // name and row index. func (f *File) getRowHeight(sheet string, row int) int { xlsx := f.workSheetReader(sheet) @@ -215,7 +224,7 @@ func (f *File) getRowHeight(sheet string, row int) int { return int(defaultRowHeightPixels) } -// GetRowHeight provides function to get row height by given worksheet name +// GetRowHeight provides a function to get row height by given worksheet name // and row index. For example, get the height of the first row in Sheet1: // // xlsx.GetRowHeight("Sheet1", 1) @@ -231,7 +240,7 @@ func (f *File) GetRowHeight(sheet string, row int) float64 { return defaultRowHeightPixels } -// sharedStringsReader provides function to get the pointer to the structure +// sharedStringsReader provides a function to get the pointer to the structure // after deserialization of xl/sharedStrings.xml. func (f *File) sharedStringsReader() *xlsxSST { if f.SharedStrings == nil { @@ -246,8 +255,9 @@ func (f *File) sharedStringsReader() *xlsxSST { return f.SharedStrings } -// getValueFrom return a value from a column/row cell, this function is inteded -// to be used with for range on rows an argument with the xlsx opened file. +// getValueFrom return a value from a column/row cell, this function is +// inteded to be used with for range on rows an argument with the xlsx opened +// file. func (xlsx *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) { switch xlsx.T { case "s": @@ -315,9 +325,9 @@ func (f *File) SetRowOutlineLevel(sheet string, rowIndex int, level uint8) { xlsx.SheetData.Row[rowIndex].OutlineLevel = level } -// GetRowOutlineLevel provides a function to get outline level number of a single row by given -// worksheet name and row index. For example, get outline number of row 2 in -// Sheet1: +// GetRowOutlineLevel provides a function to get outline level number of a +// single row by given worksheet name and row index. For example, get outline +// number of row 2 in Sheet1: // // xlsx.GetRowOutlineLevel("Sheet1", 2) // @@ -329,8 +339,8 @@ func (f *File) GetRowOutlineLevel(sheet string, rowIndex int) uint8 { return xlsx.SheetData.Row[rowIndex].OutlineLevel } -// RemoveRow provides function to remove single row by given worksheet name and -// row index. For example, remove row 3 in Sheet1: +// RemoveRow provides a function to remove single row by given worksheet name +// and row index. For example, remove row 3 in Sheet1: // // xlsx.RemoveRow("Sheet1", 2) // @@ -349,8 +359,8 @@ func (f *File) RemoveRow(sheet string, row int) { } } -// InsertRow provides function to insert a new row before given row index. For -// example, create a new row before row 3 in Sheet1: +// InsertRow provides a function to insert a new row before given row index. +// For example, create a new row before row 3 in Sheet1: // // xlsx.InsertRow("Sheet1", 2) // @@ -362,8 +372,8 @@ func (f *File) InsertRow(sheet string, row int) { f.adjustHelper(sheet, -1, row, 1) } -// checkRow provides function to check and fill each column element for all rows -// and make that is continuous in a worksheet of XML. For example: +// checkRow provides a function to check and fill each column element for all +// rows and make that is continuous in a worksheet of XML. For example: // // <row r="15" spans="1:22" x14ac:dyDescent="0.2"> // <c r="A15" s="2" /> @@ -416,7 +426,7 @@ func checkRow(xlsx *xlsxWorksheet) { } } -// completeRow provides function to check and fill each column element for a +// completeRow provides a function to check and fill each column element for a // single row and make that is continuous in a worksheet of XML by given row // index and axis. func completeRow(xlsx *xlsxWorksheet, row, cell int) { @@ -448,9 +458,9 @@ func completeRow(xlsx *xlsxWorksheet, row, cell int) { } } -// convertRowHeightToPixels provides function to convert the height of a cell -// from user's units to pixels. If the height hasn't been set by the user we use -// the default value. If the row is hidden it has a value of zero. +// convertRowHeightToPixels provides a function to convert the height of a +// cell from user's units to pixels. If the height hasn't been set by the user +// we use the default value. If the row is hidden it has a value of zero. func convertRowHeightToPixels(height float64) float64 { var pixels float64 if height == 0 { @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -7,7 +16,7 @@ import ( "strings" ) -// parseFormatShapeSet provides function to parse the format settings of the +// parseFormatShapeSet provides a function to parse the format settings of the // shape with default value. func parseFormatShapeSet(formatSet string) (*formatShape, error) { format := formatShape{ @@ -29,8 +38,8 @@ func parseFormatShapeSet(formatSet string) (*formatShape, error) { // AddShape provides the method to add shape in a sheet by given worksheet // index, shape format set (such as offset, scale, aspect ratio setting and -// print settings) and properties set. For example, add text box (rect shape) in -// Sheet1: +// print settings) and properties set. For example, add text box (rect shape) +// in Sheet1: // // xlsx.AddShape("Sheet1", "G6", `{"type":"rect","color":{"line":"#4286F4","fill":"#8eb9ff"},"paragraph":[{"text":"Rectangle Shape","font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777","underline":"sng"}}],"width":180,"height": 90}`) // @@ -272,7 +281,7 @@ func (f *File) AddShape(sheet, cell, format string) error { return err } -// addDrawingShape provides function to add preset geometry by given sheet, +// addDrawingShape provides a function to add preset geometry by given sheet, // drawingXMLand format sets. func (f *File) addDrawingShape(sheet, drawingXML, cell string, formatSet *formatShape) { textUnderlineType := map[string]bool{"none": true, "words": true, "sng": true, "dbl": true, "heavy": true, "dotted": true, "dottedHeavy": true, "dash": true, "dashHeavy": true, "dashLong": true, "dashLongHeavy": true, "dotDash": true, "dotDashHeavy": true, "dotDotDash": true, "dotDotDashHeavy": true, "wavy": true, "wavyHeavy": true, "wavyDbl": true} @@ -397,7 +406,7 @@ func (f *File) addDrawingShape(sheet, drawingXML, cell string, formatSet *format f.saveFileList(drawingXML, output) } -// setShapeRef provides function to set color with hex model by given actual +// setShapeRef provides a function to set color with hex model by given actual // color value. func setShapeRef(color string, i int) *aRef { if color == "" { @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -5,17 +14,19 @@ import ( "encoding/json" "encoding/xml" "errors" + "io/ioutil" "os" "path" "strconv" "strings" "unicode/utf8" + + "github.com/mohae/deepcopy" ) // NewSheet provides function to create a new sheet by given worksheet name. -// When creating a new XLSX file, the default sheet will be created. -// Returns the number of sheets in the workbook (file) after appending the new -// sheet. +// When creating a new XLSX file, the default sheet will be created. Returns +// the number of sheets in the workbook (file) after appending the new sheet. func (f *File) NewSheet(name string) int { // Check if the worksheet already exists if f.GetSheetIndex(name) != 0 { @@ -35,7 +46,7 @@ func (f *File) NewSheet(name string) int { return f.SheetCount } -// contentTypesReader provides function to get the pointer to the +// contentTypesReader provides a function to get the pointer to the // [Content_Types].xml structure after deserialization. func (f *File) contentTypesReader() *xlsxTypes { if f.ContentTypes == nil { @@ -46,7 +57,7 @@ func (f *File) contentTypesReader() *xlsxTypes { return f.ContentTypes } -// contentTypesWriter provides function to save [Content_Types].xml after +// contentTypesWriter provides a function to save [Content_Types].xml after // serialize structure. func (f *File) contentTypesWriter() { if f.ContentTypes != nil { @@ -55,7 +66,7 @@ func (f *File) contentTypesWriter() { } } -// workbookReader provides function to get the pointer to the xl/workbook.xml +// workbookReader provides a function to get the pointer to the xl/workbook.xml // structure after deserialization. func (f *File) workbookReader() *xlsxWorkbook { if f.WorkBook == nil { @@ -66,7 +77,7 @@ func (f *File) workbookReader() *xlsxWorkbook { return f.WorkBook } -// workbookWriter provides function to save xl/workbook.xml after serialize +// workbookWriter provides a function to save xl/workbook.xml after serialize // structure. func (f *File) workbookWriter() { if f.WorkBook != nil { @@ -75,7 +86,7 @@ func (f *File) workbookWriter() { } } -// worksheetWriter provides function to save xl/worksheets/sheet%d.xml after +// worksheetWriter provides a function to save xl/worksheets/sheet%d.xml after // serialize structure. func (f *File) worksheetWriter() { for path, sheet := range f.Sheet { @@ -93,7 +104,7 @@ func (f *File) worksheetWriter() { } } -// trimCell provides function to trim blank cells which created by completeCol. +// trimCell provides a function to trim blank cells which created by completeCol. func trimCell(column []xlsxC) []xlsxC { col := make([]xlsxC, len(column)) i := 0 @@ -131,14 +142,22 @@ func (f *File) setSheet(index int, name string) { // allowed in sheet title. func (f *File) setWorkbook(name string, rid int) { content := f.workbookReader() + rID := 0 + for _, v := range content.Sheets.Sheet { + t, _ := strconv.Atoi(v.SheetID) + if t > rID { + rID = t + } + } + rID++ content.Sheets.Sheet = append(content.Sheets.Sheet, xlsxSheet{ Name: trimSheetName(name), - SheetID: strconv.Itoa(rid), + SheetID: strconv.Itoa(rID), ID: "rId" + strconv.Itoa(rid), }) } -// workbookRelsReader provides function to read and unmarshal workbook +// workbookRelsReader provides a function to read and unmarshal workbook // relationships of XLSX file. func (f *File) workbookRelsReader() *xlsxWorkbookRels { if f.WorkBookRels == nil { @@ -149,7 +168,7 @@ func (f *File) workbookRelsReader() *xlsxWorkbookRels { return f.WorkBookRels } -// workbookRelsWriter provides function to save xl/_rels/workbook.xml.rels after +// workbookRelsWriter provides a function to save xl/_rels/workbook.xml.rels after // serialize structure. func (f *File) workbookRelsWriter() { if f.WorkBookRels != nil { @@ -238,8 +257,8 @@ func (f *File) SetActiveSheet(index int) { } } -// GetActiveSheetIndex provides function to get active sheet index of the XLSX. If not -// found the active sheet will be return integer 0. +// GetActiveSheetIndex provides a function to get active sheet index of the +// XLSX. If not found the active sheet will be return integer 0. func (f *File) GetActiveSheetIndex() int { buffer := bytes.Buffer{} content := f.workbookReader() @@ -260,7 +279,7 @@ func (f *File) GetActiveSheetIndex() int { return 0 } -// SetSheetName provides function to set the worksheet name be given old and new +// SetSheetName provides a function to set the worksheet name be given old and new // worksheet name. Maximum 31 characters are allowed in sheet title and this // function only changes the name of the sheet and will not update the sheet // name in the formula or reference associated with the cell. So there may be @@ -278,7 +297,7 @@ func (f *File) SetSheetName(oldName, newName string) { } } -// GetSheetName provides function to get worksheet name of XLSX by given +// GetSheetName provides a function to get worksheet name of XLSX by given // worksheet index. If given sheet index is invalid, will return an empty // string. func (f *File) GetSheetName(index int) string { @@ -297,7 +316,7 @@ func (f *File) GetSheetName(index int) string { return "" } -// GetSheetIndex provides function to get worksheet index of XLSX by given sheet +// GetSheetIndex provides a function to get worksheet index of XLSX by given sheet // name. If given worksheet name is invalid, will return an integer type value // 0. func (f *File) GetSheetIndex(name string) int { @@ -316,7 +335,7 @@ func (f *File) GetSheetIndex(name string) int { return 0 } -// GetSheetMap provides function to get worksheet name and index map of XLSX. +// GetSheetMap provides a function to get worksheet name and index map of XLSX. // For example: // // xlsx, err := excelize.OpenFile("./Book1.xlsx") @@ -342,7 +361,7 @@ func (f *File) GetSheetMap() map[int]string { return sheetMap } -// getSheetMap provides function to get worksheet name and XML file path map of +// getSheetMap provides a function to get worksheet name and XML file path map of // XLSX. func (f *File) getSheetMap() map[string]string { maps := make(map[string]string) @@ -352,8 +371,8 @@ func (f *File) getSheetMap() map[string]string { return maps } -// SetSheetBackground provides function to set background picture by given -// worksheet name. +// SetSheetBackground provides a function to set background picture by given +// worksheet name and file path. func (f *File) SetSheetBackground(sheet, picture string) error { var err error // Check picture exists first. @@ -362,17 +381,18 @@ func (f *File) SetSheetBackground(sheet, picture string) error { } ext, ok := supportImageTypes[path.Ext(picture)] if !ok { - return errors.New("Unsupported image extension") + return errors.New("unsupported image extension") } pictureID := f.countMedia() + 1 rID := f.addSheetRelationships(sheet, SourceRelationshipImage, "../media/image"+strconv.Itoa(pictureID)+ext, "") f.addSheetPicture(sheet, rID) - f.addMedia(picture, ext) + file, _ := ioutil.ReadFile(picture) + f.addMedia(file, ext) f.setContentTypePartImageExtensions() return err } -// DeleteSheet provides function to delete worksheet in a workbook by given +// DeleteSheet provides a function to delete worksheet in a workbook by given // worksheet name. Use this method with caution, which will affect changes in // references such as formulas, charts, and so on. If there is any referenced // value of the deleted worksheet, it will cause a file error when you open it. @@ -396,7 +416,7 @@ func (f *File) DeleteSheet(name string) { f.SetActiveSheet(len(f.GetSheetMap())) } -// deleteSheetFromWorkbookRels provides function to remove worksheet +// deleteSheetFromWorkbookRels provides a function to remove worksheet // relationships by given relationships ID in the file // xl/_rels/workbook.xml.rels. func (f *File) deleteSheetFromWorkbookRels(rID string) string { @@ -410,7 +430,7 @@ func (f *File) deleteSheetFromWorkbookRels(rID string) string { return "" } -// deleteSheetFromContentTypes provides function to remove worksheet +// deleteSheetFromContentTypes provides a function to remove worksheet // relationships by given target name in the file [Content_Types].xml. func (f *File) deleteSheetFromContentTypes(target string) { content := f.contentTypesReader() @@ -421,7 +441,7 @@ func (f *File) deleteSheetFromContentTypes(target string) { } } -// CopySheet provides function to duplicate a worksheet by gave source and +// CopySheet provides a function to duplicate a worksheet by gave source and // target worksheet index. Note that currently doesn't support duplicate // workbooks that contain tables, charts or pictures. For Example: // @@ -432,20 +452,17 @@ func (f *File) deleteSheetFromContentTypes(target string) { // func (f *File) CopySheet(from, to int) error { if from < 1 || to < 1 || from == to || f.GetSheetName(from) == "" || f.GetSheetName(to) == "" { - return errors.New("Invalid worksheet index") + return errors.New("invalid worksheet index") } - return f.copySheet(from, to) + f.copySheet(from, to) + return nil } -// copySheet provides function to duplicate a worksheet by gave source and +// copySheet provides a function to duplicate a worksheet by gave source and // target worksheet name. -func (f *File) copySheet(from, to int) error { +func (f *File) copySheet(from, to int) { sheet := f.workSheetReader("sheet" + strconv.Itoa(from)) - worksheet := xlsxWorksheet{} - err := deepCopy(&worksheet, &sheet) - if err != nil { - return err - } + worksheet := deepcopy.Copy(sheet).(*xlsxWorksheet) path := "xl/worksheets/sheet" + strconv.Itoa(to) + ".xml" if len(worksheet.SheetViews.SheetView) > 0 { worksheet.SheetViews.SheetView[0].TabSelected = false @@ -453,17 +470,16 @@ func (f *File) copySheet(from, to int) error { worksheet.Drawing = nil worksheet.TableParts = nil worksheet.PageSetUp = nil - f.Sheet[path] = &worksheet + f.Sheet[path] = worksheet toRels := "xl/worksheets/_rels/sheet" + strconv.Itoa(to) + ".xml.rels" fromRels := "xl/worksheets/_rels/sheet" + strconv.Itoa(from) + ".xml.rels" _, ok := f.XLSX[fromRels] if ok { f.XLSX[toRels] = f.XLSX[fromRels] } - return err } -// SetSheetVisible provides function to set worksheet visible by given worksheet +// SetSheetVisible provides a function to set worksheet visible by given worksheet // name. A workbook must contain at least one visible worksheet. If the given // worksheet has been activated, this setting will be invalidated. Sheet state // values as defined by http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.sheetstatevalues.aspx @@ -505,14 +521,14 @@ func (f *File) SetSheetVisible(name string, visible bool) { } } -// parseFormatPanesSet provides function to parse the panes settings. +// parseFormatPanesSet provides a function to parse the panes settings. func parseFormatPanesSet(formatSet string) (*formatPanes, error) { format := formatPanes{} err := json.Unmarshal([]byte(formatSet), &format) return &format, err } -// SetPanes provides function to create and remove freeze panes and split panes +// SetPanes provides a function to create and remove freeze panes and split panes // by given worksheet name and panes format set. // // activePane defines the pane that is active. The possible values for this @@ -626,7 +642,7 @@ func (f *File) SetPanes(sheet, panes string) { xlsx.SheetViews.SheetView[len(xlsx.SheetViews.SheetView)-1].Selection = s } -// GetSheetVisible provides function to get worksheet visible by given worksheet +// GetSheetVisible provides a function to get worksheet visible by given worksheet // name. For example, get visible state of Sheet1: // // xlsx.GetSheetVisible("Sheet1") @@ -644,7 +660,7 @@ func (f *File) GetSheetVisible(name string) bool { return visible } -// trimSheetName provides function to trim invaild characters by given worksheet +// trimSheetName provides a function to trim invaild characters by given worksheet // name. func trimSheetName(name string) string { r := []rune{} @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize // SheetPrOption is an option of a view of a worksheet. See SetSheetPrOptions(). @@ -98,7 +107,7 @@ func (o *AutoPageBreaks) getSheetPrOption(pr *xlsxSheetPr) { *o = AutoPageBreaks(pr.PageSetUpPr.AutoPageBreaks) } -// SetSheetPrOptions provides function to sets worksheet properties. +// SetSheetPrOptions provides a function to sets worksheet properties. // // Available options: // CodeName(string) @@ -120,7 +129,7 @@ func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error { return nil } -// GetSheetPrOptions provides function to gets worksheet properties. +// GetSheetPrOptions provides a function to gets worksheet properties. // // Available options: // CodeName(string) diff --git a/sheetview.go b/sheetview.go index 679e915..e76325c 100644 --- a/sheetview.go +++ b/sheetview.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "fmt" @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -10,8 +19,8 @@ import ( ) // Excel styles can reference number formats that are built-in, all of which -// have an id less than 164. This is a possibly incomplete list comprised of as -// many of them as I could find. +// have an id less than 164. This is a possibly incomplete list comprised of +// as many of them as I could find. var builtInNumFmt = map[int]string{ 0: "general", 1: "0", @@ -798,45 +807,46 @@ var validType = map[string]string{ // criteriaType defined the list of valid criteria types. var criteriaType = map[string]string{ - "between": "between", - "not between": "notBetween", - "equal to": "equal", - "=": "equal", - "==": "equal", - "not equal to": "notEqual", - "!=": "notEqual", - "<>": "notEqual", - "greater than": "greaterThan", - ">": "greaterThan", - "less than": "lessThan", - "<": "lessThan", + "between": "between", + "not between": "notBetween", + "equal to": "equal", + "=": "equal", + "==": "equal", + "not equal to": "notEqual", + "!=": "notEqual", + "<>": "notEqual", + "greater than": "greaterThan", + ">": "greaterThan", + "less than": "lessThan", + "<": "lessThan", "greater than or equal to": "greaterThanOrEqual", - ">=": "greaterThanOrEqual", - "less than or equal to": "lessThanOrEqual", - "<=": "lessThanOrEqual", - "containing": "containsText", - "not containing": "notContains", - "begins with": "beginsWith", - "ends with": "endsWith", - "yesterday": "yesterday", - "today": "today", - "last 7 days": "last7Days", - "last week": "lastWeek", - "this week": "thisWeek", - "continue week": "continueWeek", - "last month": "lastMonth", - "this month": "thisMonth", - "continue month": "continueMonth", + ">=": "greaterThanOrEqual", + "less than or equal to": "lessThanOrEqual", + "<=": "lessThanOrEqual", + "containing": "containsText", + "not containing": "notContains", + "begins with": "beginsWith", + "ends with": "endsWith", + "yesterday": "yesterday", + "today": "today", + "last 7 days": "last7Days", + "last week": "lastWeek", + "this week": "thisWeek", + "continue week": "continueWeek", + "last month": "lastMonth", + "this month": "thisMonth", + "continue month": "continueMonth", } -// formatToString provides function to return original string by given built-in -// number formats code and cell string. +// formatToString provides a function to return original string by given +// built-in number formats code and cell string. func formatToString(i int, v string) string { return v } -// formatToInt provides function to convert original string to integer format as -// string type by given built-in number formats code and cell string. +// formatToInt provides a function to convert original string to integer +// format as string type by given built-in number formats code and cell +// string. func formatToInt(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -845,8 +855,9 @@ func formatToInt(i int, v string) string { return fmt.Sprintf("%d", int(f)) } -// formatToFloat provides function to convert original string to float format as -// string type by given built-in number formats code and cell string. +// formatToFloat provides a function to convert original string to float +// format as string type by given built-in number formats code and cell +// string. func formatToFloat(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -855,8 +866,8 @@ func formatToFloat(i int, v string) string { return fmt.Sprintf("%.2f", f) } -// formatToA provides function to convert original string to special format as -// string type by given built-in number formats code and cell string. +// formatToA provides a function to convert original string to special format +// as string type by given built-in number formats code and cell string. func formatToA(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -870,8 +881,8 @@ func formatToA(i int, v string) string { return fmt.Sprintf("%d", t) } -// formatToB provides function to convert original string to special format as -// string type by given built-in number formats code and cell string. +// formatToB provides a function to convert original string to special format +// as string type by given built-in number formats code and cell string. func formatToB(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -883,8 +894,8 @@ func formatToB(i int, v string) string { return fmt.Sprintf("%.2f", f) } -// formatToC provides function to convert original string to special format as -// string type by given built-in number formats code and cell string. +// formatToC provides a function to convert original string to special format +// as string type by given built-in number formats code and cell string. func formatToC(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -894,8 +905,8 @@ func formatToC(i int, v string) string { return fmt.Sprintf("%d%%", int(f)) } -// formatToD provides function to convert original string to special format as -// string type by given built-in number formats code and cell string. +// formatToD provides a function to convert original string to special format +// as string type by given built-in number formats code and cell string. func formatToD(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -905,8 +916,8 @@ func formatToD(i int, v string) string { return fmt.Sprintf("%.2f%%", f) } -// formatToE provides function to convert original string to special format as -// string type by given built-in number formats code and cell string. +// formatToE provides a function to convert original string to special format +// as string type by given built-in number formats code and cell string. func formatToE(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -915,17 +926,17 @@ func formatToE(i int, v string) string { return fmt.Sprintf("%.e", f) } -// parseTime provides function to returns a string parsed using time.Time. +// parseTime provides a function to returns a string parsed using time.Time. // Replace Excel placeholders with Go time placeholders. For example, replace -// yyyy with 2006. These are in a specific order, due to the fact that m is used -// in month, minute, and am/pm. It would be easier to fix that with regular -// expressions, but if it's possible to keep this simple it would be easier to -// maintain. Full-length month and days (e.g. March, Tuesday) have letters in -// them that would be replaced by other characters below (such as the 'h' in -// March, or the 'd' in Tuesday) below. First we convert them to arbitrary -// characters unused in Excel Date formats, and then at the end, turn them to -// what they should actually be. -// Based off: http://www.ozgrid.com/Excel/CustomFormats.htm +// yyyy with 2006. These are in a specific order, due to the fact that m is +// used in month, minute, and am/pm. It would be easier to fix that with +// regular expressions, but if it's possible to keep this simple it would be +// easier to maintain. Full-length month and days (e.g. March, Tuesday) have +// letters in them that would be replaced by other characters below (such as +// the 'h' in March, or the 'd' in Tuesday) below. First we convert them to +// arbitrary characters unused in Excel Date formats, and then at the end, +// turn them to what they should actually be. Based off: +// http://www.ozgrid.com/Excel/CustomFormats.htm func parseTime(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -983,7 +994,7 @@ func is12HourTime(format string) bool { return strings.Contains(format, "am/pm") || strings.Contains(format, "AM/PM") || strings.Contains(format, "a/p") || strings.Contains(format, "A/P") } -// stylesReader provides function to get the pointer to the structure after +// stylesReader provides a function to get the pointer to the structure after // deserialization of xl/styles.xml. func (f *File) stylesReader() *xlsxStyleSheet { if f.Styles == nil { @@ -994,7 +1005,7 @@ func (f *File) stylesReader() *xlsxStyleSheet { return f.Styles } -// styleSheetWriter provides function to save xl/styles.xml after serialize +// styleSheetWriter provides a function to save xl/styles.xml after serialize // structure. func (f *File) styleSheetWriter() { if f.Styles != nil { @@ -1003,7 +1014,7 @@ func (f *File) styleSheetWriter() { } } -// parseFormatStyleSet provides function to parse the format settings of the +// parseFormatStyleSet provides a function to parse the format settings of the // cells and conditional formats. func parseFormatStyleSet(style string) (*formatStyle, error) { format := formatStyle{ @@ -1013,8 +1024,8 @@ func parseFormatStyleSet(style string) (*formatStyle, error) { return &format, err } -// NewStyle provides function to create style for cells by given style format. -// Note that the color field uses RGB color code. +// NewStyle provides a function to create style for cells by given style +// format. Note that the color field uses RGB color code. // // The following shows the border styles sorted by excelize index number: // @@ -1404,7 +1415,7 @@ func parseFormatStyleSet(style string) (*formatStyle, error) { // 173 | $ English (New Zealand) // 174 | $ English (Singapore) // 175 | $ English (Trinidad & Tobago) -// 176 | $ English (U.S. Vigin Islands) +// 176 | $ English (U.S. Virgin Islands) // 177 | $ English (United States) // 178 | $ French (Canada) // 179 | $ Hawaiian (United States) @@ -1906,10 +1917,10 @@ func (f *File) NewStyle(style string) (int, error) { return cellXfsID, nil } -// NewConditionalStyle provides function to create style for conditional format -// by given style format. The parameters are the same as function NewStyle(). -// Note that the color field uses RGB color code and only support to set font, -// fills, alignment and borders currently. +// NewConditionalStyle provides a function to create style for conditional +// format by given style format. The parameters are the same as function +// NewStyle(). Note that the color field uses RGB color code and only support +// to set font, fills, alignment and borders currently. func (f *File) NewConditionalStyle(style string) (int, error) { s := f.stylesReader() fs, err := parseFormatStyleSet(style) @@ -1935,7 +1946,8 @@ func (f *File) NewConditionalStyle(style string) (int, error) { return s.Dxfs.Count - 1, nil } -// setFont provides function to add font style by given cell format settings. +// setFont provides a function to add font style by given cell format +// settings. func setFont(formatStyle *formatStyle) *font { fontUnderlineType := map[string]string{"single": "single", "double": "double"} if formatStyle.Font.Size < 1 { @@ -1963,8 +1975,8 @@ func setFont(formatStyle *formatStyle) *font { return &f } -// setNumFmt provides function to check if number format code in the range of -// built-in values. +// setNumFmt provides a function to check if number format code in the range +// of built-in values. func setNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int { dp := "0." numFmtID := 164 // Default custom number format code from 164. @@ -2011,7 +2023,7 @@ func setNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int { return formatStyle.NumFmt } -// setCustomNumFmt provides function to set custom number format code. +// setCustomNumFmt provides a function to set custom number format code. func setCustomNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int { nf := xlsxNumFmt{FormatCode: *formatStyle.CustomNumFmt} if style.NumFmts != nil { @@ -2029,7 +2041,7 @@ func setCustomNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int { return nf.NumFmtID } -// setLangNumFmt provides function to set number format code with language. +// setLangNumFmt provides a function to set number format code with language. func setLangNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int { numFmts, ok := langNumFmt[formatStyle.Lang] if !ok { @@ -2056,8 +2068,8 @@ func setLangNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int { return nf.NumFmtID } -// setFills provides function to add fill elements in the styles.xml by given -// cell format settings. +// setFills provides a function to add fill elements in the styles.xml by +// given cell format settings. func setFills(formatStyle *formatStyle, fg bool) *xlsxFill { var patterns = []string{ "none", @@ -2137,9 +2149,10 @@ func setFills(formatStyle *formatStyle, fg bool) *xlsxFill { return &fill } -// setAlignment provides function to formatting information pertaining to text -// alignment in cells. There are a variety of choices for how text is aligned -// both horizontally and vertically, as well as indentation settings, and so on. +// setAlignment provides a function to formatting information pertaining to +// text alignment in cells. There are a variety of choices for how text is +// aligned both horizontally and vertically, as well as indentation settings, +// and so on. func setAlignment(formatStyle *formatStyle) *xlsxAlignment { var alignment xlsxAlignment if formatStyle.Alignment != nil { @@ -2156,7 +2169,7 @@ func setAlignment(formatStyle *formatStyle) *xlsxAlignment { return &alignment } -// setProtection provides function to set protection properties associated +// setProtection provides a function to set protection properties associated // with the cell. func setProtection(formatStyle *formatStyle) *xlsxProtection { var protection xlsxProtection @@ -2167,7 +2180,7 @@ func setProtection(formatStyle *formatStyle) *xlsxProtection { return &protection } -// setBorders provides function to add border elements in the styles.xml by +// setBorders provides a function to add border elements in the styles.xml by // given borders format settings. func setBorders(formatStyle *formatStyle) *xlsxBorder { var styles = []string{ @@ -2219,7 +2232,7 @@ func setBorders(formatStyle *formatStyle) *xlsxBorder { return &border } -// setCellXfs provides function to set describes all of the formatting for a +// setCellXfs provides a function to set describes all of the formatting for a // cell. func setCellXfs(style *xlsxStyleSheet, fontID, numFmtID, fillID, borderID int, applyAlignment, applyProtection bool, alignment *xlsxAlignment, protection *xlsxProtection) int { var xf xlsxXf @@ -2246,9 +2259,10 @@ func setCellXfs(style *xlsxStyleSheet, fontID, numFmtID, fillID, borderID int, a return style.CellXfs.Count - 1 } -// SetCellStyle provides function to add style attribute for cells by given +// SetCellStyle provides a function to add style attribute for cells by given // worksheet name, coordinate area and style ID. Note that diagonalDown and -// diagonalUp type border should be use same color in the same coordinate area. +// diagonalUp type border should be use same color in the same coordinate +// area. // // For example create a borders of cell H9 on Sheet1: // @@ -2352,9 +2366,10 @@ func (f *File) SetCellStyle(sheet, hcell, vcell string, styleID int) { } } -// SetConditionalFormat provides function to create conditional formatting rule -// for cell value. Conditional formatting is a feature of Excel which allows you -// to apply a format to a cell or a range of cells based on certain criteria. +// SetConditionalFormat provides a function to create conditional formatting +// rule for cell value. Conditional formatting is a feature of Excel which +// allows you to apply a format to a cell or a range of cells based on certain +// criteria. // // The type option is a required parameter and it has no default value. // Allowable type values and their associated parameters are: @@ -2407,7 +2422,7 @@ func (f *File) SetCellStyle(sheet, hcell, vcell string, styleID int) { // // The criteria parameter is used to set the criteria by which the cell data // will be evaluated. It has no default value. The most common criteria as -// applied to {'type': 'cell'} are: +// applied to {"type"："cell"} are: // // between | // not between | @@ -2606,9 +2621,9 @@ func (f *File) SetConditionalFormat(sheet, area, formatSet string) error { return err } -// drawCondFmtCellIs provides function to create conditional formatting rule for -// cell value (include between, not between, equal, not equal, greater than and -// less than) by given priority, criteria type and format settings. +// drawCondFmtCellIs provides a function to create conditional formatting rule +// for cell value (include between, not between, equal, not equal, greater +// than and less than) by given priority, criteria type and format settings. func drawCondFmtCellIs(p int, ct string, format *formatConditional) *xlsxCfRule { c := &xlsxCfRule{ Priority: p + 1, @@ -2629,8 +2644,8 @@ func drawCondFmtCellIs(p int, ct string, format *formatConditional) *xlsxCfRule return c } -// drawCondFmtTop10 provides function to create conditional formatting rule for -// top N (default is top 10) by given priority, criteria type and format +// drawCondFmtTop10 provides a function to create conditional formatting rule +// for top N (default is top 10) by given priority, criteria type and format // settings. func drawCondFmtTop10(p int, ct string, format *formatConditional) *xlsxCfRule { c := &xlsxCfRule{ @@ -2647,9 +2662,9 @@ func drawCondFmtTop10(p int, ct string, format *formatConditional) *xlsxCfRule { return c } -// drawCondFmtAboveAverage provides function to create conditional formatting -// rule for above average and below average by given priority, criteria type and -// format settings. +// drawCondFmtAboveAverage provides a function to create conditional +// formatting rule for above average and below average by given priority, +// criteria type and format settings. func drawCondFmtAboveAverage(p int, ct string, format *formatConditional) *xlsxCfRule { return &xlsxCfRule{ Priority: p + 1, @@ -2659,7 +2674,7 @@ func drawCondFmtAboveAverage(p int, ct string, format *formatConditional) *xlsxC } } -// drawCondFmtDuplicateUniqueValues provides function to create conditional +// drawCondFmtDuplicateUniqueValues provides a function to create conditional // formatting rule for duplicate and unique values by given priority, criteria // type and format settings. func drawCondFmtDuplicateUniqueValues(p int, ct string, format *formatConditional) *xlsxCfRule { @@ -2670,16 +2685,29 @@ func drawCondFmtDuplicateUniqueValues(p int, ct string, format *formatConditiona } } -// drawCondFmtColorScale provides function to create conditional formatting rule -// for color scale (include 2 color scale and 3 color scale) by given priority, -// criteria type and format settings. +// drawCondFmtColorScale provides a function to create conditional formatting +// rule for color scale (include 2 color scale and 3 color scale) by given +// priority, criteria type and format settings. func drawCondFmtColorScale(p int, ct string, format *formatConditional) *xlsxCfRule { + minValue := format.MinValue + if minValue == "" { + minValue = "0" + } + maxValue := format.MaxValue + if maxValue == "" { + maxValue = "0" + } + midValue := format.MidValue + if midValue == "" { + midValue = "50" + } + c := &xlsxCfRule{ Priority: p + 1, Type: "colorScale", ColorScale: &xlsxColorScale{ Cfvo: []*xlsxCfvo{ - {Type: format.MinType}, + {Type: format.MinType, Val: minValue}, }, Color: []*xlsxColor{ {RGB: getPaletteColor(format.MinColor)}, @@ -2687,16 +2715,16 @@ func drawCondFmtColorScale(p int, ct string, format *formatConditional) *xlsxCfR }, } if validType[format.Type] == "3_color_scale" { - c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MidType, Val: 50}) + c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MidType, Val: midValue}) c.ColorScale.Color = append(c.ColorScale.Color, &xlsxColor{RGB: getPaletteColor(format.MidColor)}) } - c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MaxType}) + c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MaxType, Val: maxValue}) c.ColorScale.Color = append(c.ColorScale.Color, &xlsxColor{RGB: getPaletteColor(format.MaxColor)}) return c } -// drawCondFmtDataBar provides function to create conditional formatting rule -// for data bar by given priority, criteria type and format settings. +// drawCondFmtDataBar provides a function to create conditional formatting +// rule for data bar by given priority, criteria type and format settings. func drawCondFmtDataBar(p int, ct string, format *formatConditional) *xlsxCfRule { return &xlsxCfRule{ Priority: p + 1, @@ -2708,8 +2736,8 @@ func drawCondFmtDataBar(p int, ct string, format *formatConditional) *xlsxCfRule } } -// drawConfFmtExp provides function to create conditional formatting rule for -// expression by given priority, criteria type and format settings. +// drawConfFmtExp provides a function to create conditional formatting rule +// for expression by given priority, criteria type and format settings. func drawConfFmtExp(p int, ct string, format *formatConditional) *xlsxCfRule { return &xlsxCfRule{ Priority: p + 1, @@ -2719,7 +2747,34 @@ func drawConfFmtExp(p int, ct string, format *formatConditional) *xlsxCfRule { } } -// getPaletteColor provides function to convert the RBG color by given string. +// getPaletteColor provides a function to convert the RBG color by given +// string. func getPaletteColor(color string) string { return "FF" + strings.Replace(strings.ToUpper(color), "#", "", -1) } + +// themeReader provides a function to get the pointer to the xl/theme/theme1.xml +// structure after deserialization. +func (f *File) themeReader() *xlsxTheme { + var theme xlsxTheme + _ = xml.Unmarshal([]byte(f.readXML("xl/theme/theme1.xml")), &theme) + return &theme +} + +// ThemeColor applied the color with tint value. +func ThemeColor(baseColor string, tint float64) string { + if tint == 0 { + return "FF" + baseColor + } + r, _ := strconv.ParseInt(baseColor[0:2], 16, 64) + g, _ := strconv.ParseInt(baseColor[2:4], 16, 64) + b, _ := strconv.ParseInt(baseColor[4:6], 16, 64) + h, s, l := RGBToHSL(uint8(r), uint8(g), uint8(b)) + if tint < 0 { + l *= (1 + tint) + } else { + l = l*(1-tint) + (1 - (1 - tint)) + } + br, bg, bb := HSLToRGB(h, s, l) + return fmt.Sprintf("FF%02X%02X%02X", br, bg, bb) +} diff --git a/styles_test.go b/styles_test.go new file mode 100644 index 0000000..baa66f0 --- /dev/null +++ b/styles_test.go @@ -0,0 +1,134 @@ +package excelize + +import ( + "testing" + + "github.com/stretchr/testify/assert" +) + +func TestSetConditionalFormat(t *testing.T) { + cases := []struct { + label string + format string + rules []*xlsxCfRule + }{{ + label: "3_color_scale", + format: `[{ + "type":"3_color_scale", + "criteria":"=", + "min_type":"num", + "mid_type":"num", + "max_type":"num", + "min_value": "-10", + "mid_value": "0", + "max_value": "10", + "min_color":"ff0000", + "mid_color":"00ff00", + "max_color":"0000ff" + }]`, + rules: []*xlsxCfRule{{ + Priority: 1, + Type: "colorScale", + ColorScale: &xlsxColorScale{ + Cfvo: []*xlsxCfvo{{ + Type: "num", + Val: "-10", + }, { + Type: "num", + Val: "0", + }, { + Type: "num", + Val: "10", + }}, + Color: []*xlsxColor{{ + RGB: "FFFF0000", + }, { + RGB: "FF00FF00", + }, { + RGB: "FF0000FF", + }}, + }, + }}, + }, { + label: "3_color_scale default min/mid/max", + format: `[{ + "type":"3_color_scale", + "criteria":"=", + "min_type":"num", + "mid_type":"num", + "max_type":"num", + "min_color":"ff0000", + "mid_color":"00ff00", + "max_color":"0000ff" + }]`, + rules: []*xlsxCfRule{{ + Priority: 1, + Type: "colorScale", + ColorScale: &xlsxColorScale{ + Cfvo: []*xlsxCfvo{{ + Type: "num", + Val: "0", + }, { + Type: "num", + Val: "50", + }, { + Type: "num", + Val: "0", + }}, + Color: []*xlsxColor{{ + RGB: "FFFF0000", + }, { + RGB: "FF00FF00", + }, { + RGB: "FF0000FF", + }}, + }, + }}, + }, { + label: "2_color_scale default min/max", + format: `[{ + "type":"2_color_scale", + "criteria":"=", + "min_type":"num", + "max_type":"num", + "min_color":"ff0000", + "max_color":"0000ff" + }]`, + rules: []*xlsxCfRule{{ + Priority: 1, + Type: "colorScale", + ColorScale: &xlsxColorScale{ + Cfvo: []*xlsxCfvo{{ + Type: "num", + Val: "0", + }, { + Type: "num", + Val: "0", + }}, + Color: []*xlsxColor{{ + RGB: "FFFF0000", + }, { + RGB: "FF0000FF", + }}, + }, + }}, + }} + + for _, testCase := range cases { + xl := NewFile() + const sheet = "Sheet1" + const cellRange = "A1:A1" + + err := xl.SetConditionalFormat(sheet, cellRange, testCase.format) + if err != nil { + t.Fatalf("%s", err) + } + + xlsx := xl.workSheetReader(sheet) + cf := xlsx.ConditionalFormatting + assert.Len(t, cf, 1, testCase.label) + assert.Len(t, cf[0].CfRule, 1, testCase.label) + assert.Equal(t, cellRange, cf[0].SQRef, testCase.label) + assert.EqualValues(t, testCase.rules, cf[0].CfRule, testCase.label) + } +} @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import ( @@ -9,14 +18,14 @@ import ( "strings" ) -// parseFormatTableSet provides function to parse the format settings of the +// parseFormatTableSet provides a function to parse the format settings of the // table with default value. func parseFormatTableSet(formatSet string) (*formatTable, error) { format := formatTable{ TableStyle: "", ShowRowStripes: true, } - err := json.Unmarshal([]byte(formatSet), &format) + err := json.Unmarshal(parseFormatSet(formatSet), &format) return &format, err } @@ -75,8 +84,8 @@ func (f *File) AddTable(sheet, hcell, vcell, format string) error { return err } -// countTables provides function to get table files count storage in the folder -// xl/tables. +// countTables provides a function to get table files count storage in the +// folder xl/tables. func (f *File) countTables() int { count := 0 for k := range f.XLSX { @@ -87,7 +96,7 @@ func (f *File) countTables() int { return count } -// addSheetTable provides function to add tablePart element to +// addSheetTable provides a function to add tablePart element to // xl/worksheets/sheet%d.xml by given worksheet name and relationship index. func (f *File) addSheetTable(sheet string, rID int) { xlsx := f.workSheetReader(sheet) @@ -101,8 +110,8 @@ func (f *File) addSheetTable(sheet string, rID int) { xlsx.TableParts.TableParts = append(xlsx.TableParts.TableParts, table) } -// addTable provides function to add table by given worksheet name, coordinate -// area and format set. +// addTable provides a function to add table by given worksheet name, +// coordinate area and format set. func (f *File) addTable(sheet, tableXML string, hxAxis, hyAxis, vxAxis, vyAxis, i int, formatSet *formatTable) { // Correct the minimum number of rows, the table at least two lines. if hyAxis == vyAxis { @@ -157,7 +166,7 @@ func (f *File) addTable(sheet, tableXML string, hxAxis, hyAxis, vxAxis, vyAxis, f.saveFileList(tableXML, table) } -// parseAutoFilterSet provides function to parse the settings of the auto +// parseAutoFilterSet provides a function to parse the settings of the auto // filter. func parseAutoFilterSet(formatSet string) (*formatAutoFilter, error) { format := formatAutoFilter{} @@ -264,7 +273,7 @@ func (f *File) AutoFilter(sheet, hcell, vcell, format string) error { return f.autoFilter(sheet, ref, refRange, hxAxis, formatSet) } -// autoFilter provides function to extract the tokens from the filter +// autoFilter provides a function to extract the tokens from the filter // expression. The tokens are mainly non-whitespace groups. func (f *File) autoFilter(sheet, ref string, refRange, hxAxis int, formatSet *formatAutoFilter) error { xlsx := f.workSheetReader(sheet) @@ -282,7 +291,7 @@ func (f *File) autoFilter(sheet, ref string, refRange, hxAxis int, formatSet *fo col := TitleToNumber(formatSet.Column) offset := col - hxAxis if offset < 0 || offset > refRange { - return fmt.Errorf("Incorrect index of column '%s'", formatSet.Column) + return fmt.Errorf("incorrect index of column '%s'", formatSet.Column) } filter.FilterColumn = &xlsxFilterColumn{ ColID: offset, @@ -290,7 +299,7 @@ func (f *File) autoFilter(sheet, ref string, refRange, hxAxis int, formatSet *fo re := regexp.MustCompile(`"(?:[^"]|"")*"|\S+`) token := re.FindAllString(formatSet.Expression, -1) if len(token) != 3 && len(token) != 7 { - return fmt.Errorf("Incorrect number of tokens in criteria '%s'", formatSet.Expression) + return fmt.Errorf("incorrect number of tokens in criteria '%s'", formatSet.Expression) } expressions, tokens, err := f.parseFilterExpression(formatSet.Expression, token) if err != nil { @@ -301,8 +310,8 @@ func (f *File) autoFilter(sheet, ref string, refRange, hxAxis int, formatSet *fo return nil } -// writeAutoFilter provides function to check for single or double custom filters -// as default filters and handle them accordingly. +// writeAutoFilter provides a function to check for single or double custom +// filters as default filters and handle them accordingly. func (f *File) writeAutoFilter(filter *xlsxAutoFilter, exp []int, tokens []string) { if len(exp) == 1 && exp[0] == 2 { // Single equality. @@ -329,7 +338,7 @@ func (f *File) writeAutoFilter(filter *xlsxAutoFilter, exp []int, tokens []strin } } -// writeCustomFilter provides function to write the <customFilter> element. +// writeCustomFilter provides a function to write the <customFilter> element. func (f *File) writeCustomFilter(filter *xlsxAutoFilter, operator int, val string) { operators := map[int]string{ 1: "lessThan", @@ -353,8 +362,9 @@ func (f *File) writeCustomFilter(filter *xlsxAutoFilter, operator int, val strin } } -// parseFilterExpression provides function to converts the tokens of a possibly -// conditional expression into 1 or 2 sub expressions for further parsing. +// parseFilterExpression provides a function to converts the tokens of a +// possibly conditional expression into 1 or 2 sub expressions for further +// parsing. // // Examples: // @@ -394,7 +404,7 @@ func (f *File) parseFilterExpression(expression string, tokens []string) ([]int, return expressions, t, nil } -// parseFilterTokens provides function to parse the 3 tokens of a filter +// parseFilterTokens provides a function to parse the 3 tokens of a filter // expression and return the operator and token. func (f *File) parseFilterTokens(expression string, tokens []string) ([]int, string, error) { operators := map[string]int{ @@ -414,7 +424,7 @@ func (f *File) parseFilterTokens(expression string, tokens []string) ([]int, str operator, ok := operators[strings.ToLower(tokens[1])] if !ok { // Convert the operator from a number to a descriptive string. - return []int{}, "", fmt.Errorf("Unknown operator: %s", tokens[1]) + return []int{}, "", fmt.Errorf("unknown operator: %s", tokens[1]) } token := tokens[2] // Special handling for Blanks/NonBlanks. @@ -422,7 +432,7 @@ func (f *File) parseFilterTokens(expression string, tokens []string) ([]int, str if re { // Only allow Equals or NotEqual in this context. if operator != 2 && operator != 5 { - return []int{operator}, token, fmt.Errorf("The operator '%s' in expression '%s' is not valid in relation to Blanks/NonBlanks'", tokens[1], expression) + return []int{operator}, token, fmt.Errorf("the operator '%s' in expression '%s' is not valid in relation to Blanks/NonBlanks'", tokens[1], expression) } token = strings.ToLower(token) // The operator should always be 2 (=) to flag a "simple" equality in diff --git a/templates.go b/templates.go index ef6058c..1d0655d 100644 --- a/templates.go +++ b/templates.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. +// // This file contains default templates for XML files we don't yet populated // based on content. diff --git a/vmlDrawing.go b/vmlDrawing.go index 307186a..c17dde7 100644 --- a/vmlDrawing.go +++ b/vmlDrawing.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlChart.go b/xmlChart.go index a263334..78218a0 100644 --- a/xmlChart.go +++ b/xmlChart.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlComments.go b/xmlComments.go index fadc9b3..9075c88 100644 --- a/xmlComments.go +++ b/xmlComments.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" @@ -53,3 +62,11 @@ type formatComment struct { Author string `json:"author"` Text string `json:"text"` } + +// Comment directly maps the comment information. +type Comment struct { + Author string `json:"author"` + AuthorID int `json:"author_id"` + Ref string `json:"ref"` + Text string `json:"text"` +} diff --git a/xmlContentTypes.go b/xmlContentTypes.go index 121c684..8d09d51 100644 --- a/xmlContentTypes.go +++ b/xmlContentTypes.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlDecodeDrawing.go b/xmlDecodeDrawing.go index fff6b9d..d21c3f0 100644 --- a/xmlDecodeDrawing.go +++ b/xmlDecodeDrawing.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlDrawing.go b/xmlDrawing.go index beb6bc9..6ba7d31 100644 --- a/xmlDrawing.go +++ b/xmlDrawing.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlSharedStrings.go b/xmlSharedStrings.go index c8b54a0..782ed61 100644 --- a/xmlSharedStrings.go +++ b/xmlSharedStrings.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlStyles.go b/xmlStyles.go index 05ff22b..7ba4379 100644 --- a/xmlStyles.go +++ b/xmlStyles.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlTable.go b/xmlTable.go index b238350..7e155e6 100644 --- a/xmlTable.go +++ b/xmlTable.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlTheme.go b/xmlTheme.go new file mode 100644 index 0000000..b4140b6 --- /dev/null +++ b/xmlTheme.go @@ -0,0 +1,149 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + +package excelize + +import "encoding/xml" + +// xlsxTheme directly maps the theme element in the namespace +// http://schemas.openxmlformats.org/drawingml/2006/main +type xlsxTheme struct { + ThemeElements xlsxThemeElements `xml:"themeElements"` + ObjectDefaults xlsxObjectDefaults `xml:"objectDefaults"` + ExtraClrSchemeLst xlsxExtraClrSchemeLst `xml:"extraClrSchemeLst"` + ExtLst *xlsxExtLst `xml:"extLst"` +} + +// objectDefaults element allows for the definition of default shape, line, +// and textbox formatting properties. An application can use this information +// to format a shape (or text) initially on insertion into a document. +type xlsxObjectDefaults struct { + ObjectDefaults string `xml:",innerxml"` +} + +// xlsxExtraClrSchemeLst element is a container for the list of extra color +// schemes present in a document. +type xlsxExtraClrSchemeLst struct { + ExtraClrSchemeLst string `xml:",innerxml"` +} + +// xlsxThemeElements directly maps the element defines the theme formatting +// options for the theme and is the workhorse of the theme. This is where the +// bulk of the shared theme information is contained and used by a document. +// This element contains the color scheme, font scheme, and format scheme +// elements which define the different formatting aspects of what a theme +// defines. +type xlsxThemeElements struct { + ClrScheme xlsxClrScheme `xml:"clrScheme"` + FontScheme xlsxFontScheme `xml:"fontScheme"` + FmtScheme xlsxFmtScheme `xml:"fmtScheme"` +} + +// xlsxClrScheme element specifies the theme color, stored in the document's +// Theme part to which the value of this theme color shall be mapped. This +// mapping enables multiple theme colors to be chained together. +type xlsxClrScheme struct { + Name string `xml:"name,attr"` + Children []xlsxClrSchemeEl `xml:",any"` +} + +// xlsxFontScheme element defines the font scheme within the theme. The font +// scheme consists of a pair of major and minor fonts for which to use in a +// document. The major font corresponds well with the heading areas of a +// document, and the minor font corresponds well with the normal text or +// paragraph areas. +type xlsxFontScheme struct { + Name string `xml:"name,attr"` + MajorFont xlsxMajorFont `xml:"majorFont"` + MinorFont xlsxMinorFont `xml:"minorFont"` + ExtLst *xlsxExtLst `xml:"extLst"` +} + +// xlsxMajorFont element defines the set of major fonts which are to be used +// under different languages or locals. +type xlsxMajorFont struct { + Children []xlsxFontSchemeEl `xml:",any"` +} + +// xlsxMinorFont element defines the set of minor fonts that are to be used +// under different languages or locals. +type xlsxMinorFont struct { + Children []xlsxFontSchemeEl `xml:",any"` +} + +// xlsxFmtScheme element contains the background fill styles, effect styles, +// fill styles, and line styles which define the style matrix for a theme. The +// style matrix consists of subtle, moderate, and intense fills, lines, and +// effects. The background fills are not generally thought of to directly be +// associated with the matrix, but do play a role in the style of the overall +// document. Usually, a given object chooses a single line style, a single +// fill style, and a single effect style in order to define the overall final +// look of the object. +type xlsxFmtScheme struct { + Name string `xml:"name,attr"` + FillStyleLst xlsxFillStyleLst `xml:"fillStyleLst"` + LnStyleLst xlsxLnStyleLst `xml:"lnStyleLst"` + EffectStyleLst xlsxEffectStyleLst `xml:"effectStyleLst"` + BgFillStyleLst xlsxBgFillStyleLst `xml:"bgFillStyleLst"` +} + +// xlsxFillStyleLst element defines a set of three fill styles that are used +// within a theme. The three fill styles are arranged in order from subtle to +// moderate to intense. +type xlsxFillStyleLst struct { + FillStyleLst string `xml:",innerxml"` +} + +// xlsxLnStyleLst element defines a list of three line styles for use within a +// theme. The three line styles are arranged in order from subtle to moderate +// to intense versions of lines. This list makes up part of the style matrix. +type xlsxLnStyleLst struct { + LnStyleLst string `xml:",innerxml"` +} + +// xlsxEffectStyleLst element defines a set of three effect styles that create +// the effect style list for a theme. The effect styles are arranged in order +// of subtle to moderate to intense. +type xlsxEffectStyleLst struct { + EffectStyleLst string `xml:",innerxml"` +} + +// xlsxBgFillStyleLst element defines a list of background fills that are +// used within a theme. The background fills consist of three fills, arranged +// in order from subtle to moderate to intense. +type xlsxBgFillStyleLst struct { + BgFillStyleLst string `xml:",innerxml"` +} + +// xlsxClrScheme maps to children of the clrScheme element in the namespace +// http://schemas.openxmlformats.org/drawingml/2006/main - currently I have +// not checked it for completeness - it does as much as I need. +type xlsxClrSchemeEl struct { + XMLName xml.Name + SysClr *xlsxSysClr `xml:"sysClr"` + SrgbClr *attrValString `xml:"srgbClr"` +} + +// xlsxFontSchemeEl directly maps the major and minor font of the style's font +// scheme. +type xlsxFontSchemeEl struct { + XMLName xml.Name + Script string `xml:"script,attr,omitempty"` + Typeface string `xml:"typeface,attr"` + Panose string `xml:"panose,attr,omitempty"` + PitchFamily string `xml:"pitchFamily,attr,omitempty"` + Charset string `xml:"charset,attr,omitempty"` +} + +// xlsxSysClr element specifies a color bound to predefined operating system +// elements. +type xlsxSysClr struct { + Val string `xml:"val,attr"` + LastClr string `xml:"lastClr,attr"` +} diff --git a/xmlWorkbook.go b/xmlWorkbook.go index 816d5a4..f00a0b8 100644 --- a/xmlWorkbook.go +++ b/xmlWorkbook.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" diff --git a/xmlWorksheet.go b/xmlWorksheet.go index 37c0d18..072ecce 100644 --- a/xmlWorksheet.go +++ b/xmlWorksheet.go @@ -1,3 +1,12 @@ +// Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of +// this source code is governed by a BSD-style license that can be found in +// the LICENSE file. +// +// Package excelize providing a set of functions that allow you to write to +// and read from XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + package excelize import "encoding/xml" @@ -18,7 +27,7 @@ type xlsxWorksheet struct { MergeCells *xlsxMergeCells `xml:"mergeCells"` PhoneticPr *xlsxPhoneticPr `xml:"phoneticPr"` ConditionalFormatting []*xlsxConditionalFormatting `xml:"conditionalFormatting"` - DataValidations *xlsxDataValidations `xml:"dataValidations"` + DataValidations *xlsxDataValidations `xml:"dataValidations,omitempty"` Hyperlinks *xlsxHyperlinks `xml:"hyperlinks"` PrintOptions *xlsxPrintOptions `xml:"printOptions"` PageMargins *xlsxPageMargins `xml:"pageMargins"` @@ -294,11 +303,30 @@ type xlsxMergeCells struct { // xlsxDataValidations expresses all data validation information for cells in a // sheet which have data validation features applied. type xlsxDataValidations struct { - Count int `xml:"count,attr,omitempty"` - DisablePrompts bool `xml:"disablePrompts,attr,omitempty"` - XWindow int `xml:"xWindow,attr,omitempty"` - YWindow int `xml:"yWindow,attr,omitempty"` - DataValidation string `xml:",innerxml"` + Count int `xml:"count,attr,omitempty"` + DisablePrompts bool `xml:"disablePrompts,attr,omitempty"` + XWindow int `xml:"xWindow,attr,omitempty"` + YWindow int `xml:"yWindow,attr,omitempty"` + DataValidation []*DataValidation `xml:"dataValidation"` +} + +// DataValidation directly maps the a single item of data validation defined +// on a range of the worksheet. +type DataValidation struct { + AllowBlank bool `xml:"allowBlank,attr"` + Error *string `xml:"error,attr"` + ErrorStyle *string `xml:"errorStyle,attr"` + ErrorTitle *string `xml:"errorTitle,attr"` + Operator string `xml:"operator,attr"` + Prompt *string `xml:"prompt,attr"` + PromptTitle *string `xml:"promptTitle"` + ShowDropDown bool `xml:"showDropDown,attr"` + ShowErrorMessage bool `xml:"showErrorMessage,attr"` + ShowInputMessage bool `xml:"showInputMessage,attr"` + Sqref string `xml:"sqref,attr"` + Type string `xml:"type,attr"` + Formula1 string `xml:"formula1"` + Formula2 string `xml:"formula2"` } // xlsxC directly maps the c element in the namespace @@ -446,7 +474,7 @@ type xlsxIconSet struct { type xlsxCfvo struct { Gte bool `xml:"gte,attr,omitempty"` Type string `xml:"type,attr,omitempty"` - Val int `xml:"val,attr"` + Val string `xml:"val,attr"` ExtLst *xlsxExtLst `xml:"extLst"` } |