summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--cell.go14
-rw-r--r--col.go43
-rw-r--r--excelize.go4
-rw-r--r--excelize_test.go25
-rw-r--r--file.go18
-rw-r--r--lib.go2
-rw-r--r--rows.go4
-rw-r--r--sheet.go93
-rw-r--r--xmlDrawing.go1
-rw-r--r--xmlWorksheet.go12
10 files changed, 151 insertions, 65 deletions
diff --git a/cell.go b/cell.go
index 106b1f2..d881220 100644
--- a/cell.go
+++ b/cell.go
@@ -13,7 +13,7 @@ func (f *File) GetCellValue(sheet string, axis string) string {
var xlsx xlsxWorksheet
row, _ := strconv.Atoi(strings.Map(intOnlyMapF, axis))
xAxis := row - 1
- name := `xl/worksheets/` + strings.ToLower(sheet) + `.xml`
+ name := "xl/worksheets/" + strings.ToLower(sheet) + ".xml"
xml.Unmarshal([]byte(f.readXML(name)), &xlsx)
rows := len(xlsx.SheetData.Row)
if rows > 1 {
@@ -23,7 +23,7 @@ func (f *File) GetCellValue(sheet string, axis string) string {
}
}
if rows <= xAxis {
- return ``
+ return ""
}
for _, v := range xlsx.SheetData.Row {
if v.R != row {
@@ -38,7 +38,7 @@ func (f *File) GetCellValue(sheet string, axis string) string {
shardStrings := xlsxSST{}
xlsxSI := 0
xlsxSI, _ = strconv.Atoi(r.V)
- xml.Unmarshal([]byte(f.readXML(`xl/sharedStrings.xml`)), &shardStrings)
+ xml.Unmarshal([]byte(f.readXML("xl/sharedStrings.xml")), &shardStrings)
return shardStrings.SI[xlsxSI].T
case "str":
return r.V
@@ -47,7 +47,7 @@ func (f *File) GetCellValue(sheet string, axis string) string {
}
}
}
- return ``
+ return ""
}
// GetCellFormula provide function get formula from cell by given sheet index and axis in XLSX file.
@@ -56,7 +56,7 @@ func (f *File) GetCellFormula(sheet string, axis string) string {
var xlsx xlsxWorksheet
row, _ := strconv.Atoi(strings.Map(intOnlyMapF, axis))
xAxis := row - 1
- name := `xl/worksheets/` + strings.ToLower(sheet) + `.xml`
+ name := "xl/worksheets/" + strings.ToLower(sheet) + ".xml"
xml.Unmarshal([]byte(f.readXML(name)), &xlsx)
rows := len(xlsx.SheetData.Row)
if rows > 1 {
@@ -66,7 +66,7 @@ func (f *File) GetCellFormula(sheet string, axis string) string {
}
}
if rows <= xAxis {
- return ``
+ return ""
}
for _, v := range xlsx.SheetData.Row {
if v.R != row {
@@ -81,5 +81,5 @@ func (f *File) GetCellFormula(sheet string, axis string) string {
}
}
}
- return ``
+ return ""
}
diff --git a/col.go b/col.go
new file mode 100644
index 0000000..dbe004d
--- /dev/null
+++ b/col.go
@@ -0,0 +1,43 @@
+package excelize
+
+import (
+ "encoding/xml"
+ "strings"
+)
+
+// SetColWidth provides function to set the width of a single column or multiple columns.
+// For example:
+//
+// xlsx := excelize.CreateFile()
+// xlsx.SetColWidth("Sheet1", "A", "H", 20)
+// err := xlsx.Save()
+// if err != nil {
+// fmt.Println(err)
+// os.Exit(1)
+// }
+//
+func (f *File) SetColWidth(sheet, startcol, endcol string, width float64) {
+ min := titleToNumber(strings.ToUpper(startcol)) + 1
+ max := titleToNumber(strings.ToUpper(endcol)) + 1
+ if min > max {
+ min, max = max, min
+ }
+ var xlsx xlsxWorksheet
+ name := "xl/worksheets/" + strings.ToLower(sheet) + ".xml"
+ xml.Unmarshal([]byte(f.readXML(name)), &xlsx)
+ col := xlsxCol{
+ Min: min,
+ Max: max,
+ Width: width,
+ CustomWidth: true,
+ }
+ if xlsx.Cols != nil {
+ xlsx.Cols.Col = append(xlsx.Cols.Col, col)
+ } else {
+ cols := xlsxCols{}
+ cols.Col = append(cols.Col, col)
+ xlsx.Cols = &cols
+ }
+ output, _ := xml.Marshal(xlsx)
+ f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpace(string(output)))
+}
diff --git a/excelize.go b/excelize.go
index d7e08d5..f22af37 100644
--- a/excelize.go
+++ b/excelize.go
@@ -56,7 +56,7 @@ func (f *File) SetCellValue(sheet string, axis string, value interface{}) {
case []byte:
f.SetCellStr(sheet, axis, string(t))
default:
- f.SetCellStr(sheet, axis, ``)
+ f.SetCellStr(sheet, axis, "")
}
}
@@ -296,7 +296,7 @@ func (f *File) UpdateLinkedValue() {
xml.Unmarshal([]byte(f.readXML(name)), &xlsx)
for indexR, row := range xlsx.SheetData.Row {
for indexC, col := range row.C {
- if col.F != nil && col.V != `` {
+ if col.F != nil && col.V != "" {
xlsx.SheetData.Row[indexR].C[indexC].V = ""
xlsx.SheetData.Row[indexR].C[indexC].T = ""
}
diff --git a/excelize_test.go b/excelize_test.go
index e1bd4c7..3c926ca 100644
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -5,7 +5,7 @@ import (
"testing"
)
-func TestExcelize(t *testing.T) {
+func TestOpenFile(t *testing.T) {
// Test update a XLSX file.
f1, err := OpenFile("./test/Workbook1.xlsx")
if err != nil {
@@ -26,7 +26,9 @@ func TestExcelize(t *testing.T) {
f1.SetCellDefault("SHEET2", "A1", strconv.FormatFloat(float64(-100.1588), 'f', -1, 64))
f1.SetCellInt("SHEET2", "A1", 100)
f1.SetCellStr("SHEET2", "C11", "Knowns")
- f1.NewSheet(3, "Maximum 31 characters allowed in sheet title.")
+ f1.NewSheet(3, ":\\/?*[]Maximum 31 characters allowed in sheet title.")
+ // Test set sheet name with illegal name.
+ f1.SetSheetName("Maximum 31 characters allowed i", "[Rename]:\\/?* Maximum 31 characters allowed in sheet title.")
f1.SetCellInt("Sheet3", "A23", 10)
f1.SetCellStr("SHEET3", "b230", "10")
f1.SetCellStr("SHEET10", "b230", "10")
@@ -122,7 +124,9 @@ func TestExcelize(t *testing.T) {
if err != nil {
t.Log(err)
}
+}
+func TestCreateFile(t *testing.T) {
// Test create a XLSX file.
f3 := CreateFile()
f3.NewSheet(2, "XLSXSheet2")
@@ -131,7 +135,7 @@ func TestExcelize(t *testing.T) {
f3.SetCellStr("SHEET1", "B20", "42")
f3.SetActiveSheet(0)
// Test add picture to sheet.
- err = f3.AddPicture("Sheet1", "H2", "K12", "./test/images/excel.gif")
+ err := f3.AddPicture("Sheet1", "H2", "K12", "./test/images/excel.gif")
if err != nil {
t.Log(err)
}
@@ -143,7 +147,9 @@ func TestExcelize(t *testing.T) {
if err != nil {
t.Log(err)
}
+}
+func TestBrokenFile(t *testing.T) {
// Test set active sheet without BookViews and Sheets maps in xl/workbook.xml.
f4, err := OpenFile("./test/badWorkbook.xlsx")
f4.SetActiveSheet(2)
@@ -157,3 +163,16 @@ func TestExcelize(t *testing.T) {
t.Log(err)
}
}
+
+func TestSetColWidth(t *testing.T) {
+ f5, err := OpenFile("./test/Workbook1.xlsx")
+ if err != nil {
+ t.Log(err)
+ }
+ f5.SetColWidth("sheet1", "B", "A", 12)
+ f5.SetColWidth("sheet1", "A", "B", 12)
+ err = f5.Save()
+ if err != nil {
+ t.Log(err)
+ }
+}
diff --git a/file.go b/file.go
index 979f5e1..654fe59 100644
--- a/file.go
+++ b/file.go
@@ -11,15 +11,15 @@ import (
// xlsx := CreateFile()
func CreateFile() *File {
file := make(map[string]string)
- file[`_rels/.rels`] = templateRels
- file[`docProps/app.xml`] = templateDocpropsApp
- file[`docProps/core.xml`] = templateDocpropsCore
- file[`xl/_rels/workbook.xml.rels`] = templateWorkbookRels
- file[`xl/theme/theme1.xml`] = templateTheme
- file[`xl/worksheets/sheet1.xml`] = templateSheet
- file[`xl/styles.xml`] = templateStyles
- file[`xl/workbook.xml`] = templateWorkbook
- file[`[Content_Types].xml`] = templateContentTypes
+ file["_rels/.rels"] = templateRels
+ file["docProps/app.xml"] = templateDocpropsApp
+ file["docProps/core.xml"] = templateDocpropsCore
+ file["xl/_rels/workbook.xml.rels"] = templateWorkbookRels
+ file["xl/theme/theme1.xml"] = templateTheme
+ file["xl/worksheets/sheet1.xml"] = templateSheet
+ file["xl/styles.xml"] = templateStyles
+ file["xl/workbook.xml"] = templateWorkbook
+ file["[Content_Types].xml"] = templateContentTypes
return &File{
XLSX: file,
}
diff --git a/lib.go b/lib.go
index f24f25c..dfd1bdc 100644
--- a/lib.go
+++ b/lib.go
@@ -25,7 +25,7 @@ func ReadZipReader(r *zip.Reader) (map[string]string, int, error) {
for _, v := range r.File {
fileList[v.Name] = readFile(v)
if len(v.Name) > 18 {
- if v.Name[0:19] == `xl/worksheets/sheet` {
+ if v.Name[0:19] == "xl/worksheets/sheet" {
var xlsx xlsxWorksheet
xml.Unmarshal([]byte(fileList[v.Name]), &xlsx)
xlsx = checkRow(xlsx)
diff --git a/rows.go b/rows.go
index 90cabee..3d74b27 100644
--- a/rows.go
+++ b/rows.go
@@ -19,7 +19,7 @@ import (
func (f *File) GetRows(sheet string) [][]string {
xlsx := xlsxWorksheet{}
r := [][]string{}
- name := `xl/worksheets/` + strings.ToLower(sheet) + `.xml`
+ name := "xl/worksheets/" + strings.ToLower(sheet) + ".xml"
err := xml.Unmarshal([]byte(f.readXML(name)), &xlsx)
if err != nil {
return r
@@ -39,7 +39,7 @@ func (f *File) GetRows(sheet string) [][]string {
// readXMLSST read xmlSST simple function.
func readXMLSST(f *File) (xlsxSST, error) {
shardStrings := xlsxSST{}
- err := xml.Unmarshal([]byte(f.readXML(`xl/sharedStrings.xml`)), &shardStrings)
+ err := xml.Unmarshal([]byte(f.readXML("xl/sharedStrings.xml")), &shardStrings)
return shardStrings, err
}
diff --git a/sheet.go b/sheet.go
index 8338f14..eeccde9 100644
--- a/sheet.go
+++ b/sheet.go
@@ -27,22 +27,22 @@ func (f *File) NewSheet(index int, name string) {
// Read and update property of contents type of XLSX.
func (f *File) setContentTypes(index int) {
var content xlsxTypes
- xml.Unmarshal([]byte(f.readXML(`[Content_Types].xml`)), &content)
+ xml.Unmarshal([]byte(f.readXML("[Content_Types].xml")), &content)
content.Overrides = append(content.Overrides, xlsxOverride{
- PartName: `/xl/worksheets/sheet` + strconv.Itoa(index) + `.xml`,
- ContentType: `application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml`,
+ PartName: "/xl/worksheets/sheet" + strconv.Itoa(index) + ".xml",
+ ContentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml",
})
output, err := xml.Marshal(content)
if err != nil {
fmt.Println(err)
}
- f.saveFileList(`[Content_Types].xml`, string(output))
+ f.saveFileList("[Content_Types].xml", string(output))
}
// Update sheet property by given index.
func (f *File) setSheet(index int) {
var xlsx xlsxWorksheet
- xlsx.Dimension.Ref = `A1`
+ xlsx.Dimension.Ref = "A1"
xlsx.SheetViews.SheetView = append(xlsx.SheetViews.SheetView, xlsxSheetView{
WorkbookViewID: 0,
})
@@ -50,33 +50,35 @@ func (f *File) setSheet(index int) {
if err != nil {
fmt.Println(err)
}
- path := `xl/worksheets/sheet` + strconv.Itoa(index) + `.xml`
+ path := "xl/worksheets/sheet" + strconv.Itoa(index) + ".xml"
f.saveFileList(path, replaceWorkSheetsRelationshipsNameSpace(string(output)))
}
-// Update workbook property of XLSX. Maximum 31 characters allowed in sheet title.
+// Update workbook property of XLSX. Maximum 31 characters are allowed in sheet title.
func (f *File) setWorkbook(name string, rid int) {
var content xlsxWorkbook
+ r := strings.NewReplacer(":", "", "\\", "", "/", "", "?", "", "*", "", "[", "", "]", "")
+ name = r.Replace(name)
if len(name) > 31 {
name = name[0:31]
}
- xml.Unmarshal([]byte(f.readXML(`xl/workbook.xml`)), &content)
+ xml.Unmarshal([]byte(f.readXML("xl/workbook.xml")), &content)
content.Sheets.Sheet = append(content.Sheets.Sheet, xlsxSheet{
Name: name,
SheetID: strconv.Itoa(rid),
- ID: `rId` + strconv.Itoa(rid),
+ ID: "rId" + strconv.Itoa(rid),
})
output, err := xml.Marshal(content)
if err != nil {
fmt.Println(err)
}
- f.saveFileList(`xl/workbook.xml`, replaceRelationshipsNameSpace(string(output)))
+ f.saveFileList("xl/workbook.xml", replaceRelationshipsNameSpace(string(output)))
}
// Read and unmarshal workbook relationships of XLSX.
func (f *File) readXlsxWorkbookRels() xlsxWorkbookRels {
var content xlsxWorkbookRels
- xml.Unmarshal([]byte(f.readXML(`xl/_rels/workbook.xml.rels`)), &content)
+ xml.Unmarshal([]byte(f.readXML("xl/_rels/workbook.xml.rels")), &content)
return content
}
@@ -85,28 +87,28 @@ func (f *File) addXlsxWorkbookRels(sheet int) int {
content := f.readXlsxWorkbookRels()
rID := len(content.Relationships) + 1
ID := bytes.Buffer{}
- ID.WriteString(`rId`)
+ ID.WriteString("rId")
ID.WriteString(strconv.Itoa(rID))
target := bytes.Buffer{}
- target.WriteString(`worksheets/sheet`)
+ target.WriteString("worksheets/sheet")
target.WriteString(strconv.Itoa(sheet))
- target.WriteString(`.xml`)
+ target.WriteString(".xml")
content.Relationships = append(content.Relationships, xlsxWorkbookRelation{
ID: ID.String(),
Target: target.String(),
- Type: `http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet`,
+ Type: SourceRelationshipWorkSheet,
})
output, err := xml.Marshal(content)
if err != nil {
fmt.Println(err)
}
- f.saveFileList(`xl/_rels/workbook.xml.rels`, string(output))
+ f.saveFileList("xl/_rels/workbook.xml.rels", string(output))
return rID
}
// Update docProps/app.xml file of XML.
func (f *File) setAppXML() {
- f.saveFileList(`docProps/app.xml`, templateDocpropsApp)
+ f.saveFileList("docProps/app.xml", templateDocpropsApp)
}
// Some tools that read XLSX files have very strict requirements about
@@ -129,7 +131,7 @@ func (f *File) SetActiveSheet(index int) {
index = 1
}
index--
- xml.Unmarshal([]byte(f.readXML(`xl/workbook.xml`)), &content)
+ xml.Unmarshal([]byte(f.readXML("xl/workbook.xml")), &content)
if len(content.BookViews.WorkBookView) > 0 {
content.BookViews.WorkBookView[0].ActiveTab = index
} else {
@@ -142,15 +144,15 @@ func (f *File) SetActiveSheet(index int) {
if err != nil {
fmt.Println(err)
}
- f.saveFileList(`xl/workbook.xml`, replaceRelationshipsNameSpace(string(output)))
+ f.saveFileList("xl/workbook.xml", replaceRelationshipsNameSpace(string(output)))
index++
buffer := bytes.Buffer{}
for i := 0; i < sheets; i++ {
xlsx := xlsxWorksheet{}
sheetIndex := i + 1
- buffer.WriteString(`xl/worksheets/sheet`)
+ buffer.WriteString("xl/worksheets/sheet")
buffer.WriteString(strconv.Itoa(sheetIndex))
- buffer.WriteString(`.xml`)
+ buffer.WriteString(".xml")
xml.Unmarshal([]byte(f.readXML(buffer.String())), &xlsx)
if index == sheetIndex {
if len(xlsx.SheetViews.SheetView) > 0 {
@@ -175,21 +177,21 @@ func (f *File) SetActiveSheet(index int) {
return
}
-// GetActiveSheetIndex provide function to get active sheet of XLSX. If not found
+// GetActiveSheetIndex provides function to get active sheet of XLSX. If not found
// the active sheet will be return integer 0.
func (f *File) GetActiveSheetIndex() int {
content := xlsxWorkbook{}
buffer := bytes.Buffer{}
- xml.Unmarshal([]byte(f.readXML(`xl/workbook.xml`)), &content)
+ xml.Unmarshal([]byte(f.readXML("xl/workbook.xml")), &content)
for _, v := range content.Sheets.Sheet {
xlsx := xlsxWorksheet{}
- buffer.WriteString(`xl/worksheets/sheet`)
- buffer.WriteString(strings.TrimPrefix(v.ID, `rId`))
- buffer.WriteString(`.xml`)
+ buffer.WriteString("xl/worksheets/sheet")
+ buffer.WriteString(strings.TrimPrefix(v.ID, "rId"))
+ buffer.WriteString(".xml")
xml.Unmarshal([]byte(f.readXML(buffer.String())), &xlsx)
for _, sheetView := range xlsx.SheetViews.SheetView {
if sheetView.TabSelected {
- id, _ := strconv.Atoi(strings.TrimPrefix(v.ID, `rId`))
+ id, _ := strconv.Atoi(strings.TrimPrefix(v.ID, "rId"))
return id
}
}
@@ -198,20 +200,41 @@ func (f *File) GetActiveSheetIndex() int {
return 0
}
-// GetSheetName provide function to get sheet name of XLSX by given sheet index.
+// SetSheetName provides function to set the sheet name be given old and new sheet 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 problem formula error or reference missing.
+func (f *File) SetSheetName(oldName, newName string) {
+ var content = xlsxWorkbook{}
+ r := strings.NewReplacer(":", "", "\\", "", "/", "", "?", "", "*", "", "[", "", "]", "")
+ newName = r.Replace(newName)
+ if len(newName) > 31 {
+ newName = newName[0:31]
+ }
+ xml.Unmarshal([]byte(f.readXML("xl/workbook.xml")), &content)
+ for k, v := range content.Sheets.Sheet {
+ if v.Name == oldName {
+ content.Sheets.Sheet[k].Name = newName
+ }
+ }
+ output, _ := xml.Marshal(content)
+ f.saveFileList("xl/workbook.xml", replaceRelationshipsNameSpace(string(output)))
+}
+
+// GetSheetName provides function to get sheet name of XLSX by given sheet index.
// If given sheet index is invalid, will return an empty string.
func (f *File) GetSheetName(index int) string {
- content := xlsxWorkbook{}
- xml.Unmarshal([]byte(f.readXML(`xl/workbook.xml`)), &content)
+ var content = xlsxWorkbook{}
+ xml.Unmarshal([]byte(f.readXML("xl/workbook.xml")), &content)
for _, v := range content.Sheets.Sheet {
- if v.ID == `rId`+strconv.Itoa(index) {
+ if v.ID == "rId"+strconv.Itoa(index) {
return v.Name
}
}
- return ``
+ return ""
}
-// GetSheetMap provide function to get sheet map of XLSX. For example:
+// GetSheetMap provides function to get sheet map of XLSX. For example:
//
// xlsx, err := excelize.OpenFile("/tmp/Workbook.xlsx")
// if err != nil {
@@ -225,9 +248,9 @@ func (f *File) GetSheetName(index int) string {
func (f *File) GetSheetMap() map[int]string {
content := xlsxWorkbook{}
sheetMap := map[int]string{}
- xml.Unmarshal([]byte(f.readXML(`xl/workbook.xml`)), &content)
+ xml.Unmarshal([]byte(f.readXML("xl/workbook.xml")), &content)
for _, v := range content.Sheets.Sheet {
- id, _ := strconv.Atoi(strings.TrimPrefix(v.ID, `rId`))
+ id, _ := strconv.Atoi(strings.TrimPrefix(v.ID, "rId"))
sheetMap[id] = v.Name
}
return sheetMap
diff --git a/xmlDrawing.go b/xmlDrawing.go
index 75f6c03..b0f1f46 100644
--- a/xmlDrawing.go
+++ b/xmlDrawing.go
@@ -5,6 +5,7 @@ const (
SourceRelationship = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
SourceRelationshipImage = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
SourceRelationshipDrawingML = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"
+ SourceRelationshipWorkSheet = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
NameSpaceDrawingML = "http://schemas.openxmlformats.org/drawingml/2006/main"
NameSpaceSpreadSheetDrawing = "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
)
diff --git a/xmlWorksheet.go b/xmlWorksheet.go
index 9cc0e55..bc17403 100644
--- a/xmlWorksheet.go
+++ b/xmlWorksheet.go
@@ -228,19 +228,19 @@ type xlsxCols struct {
Col []xlsxCol `xml:"col"`
}
-// xlsxCol directly maps the col element in the namespace
-// http://schemas.openxmlformats.org/spreadsheetml/2006/main -
-// currently I have not checked it for completeness - it does as much
-// as I need.
+// xlsxCol directly maps the col (Column Width & Formatting). Defines column
+// width and column formatting for one or more columns of the worksheet.
type xlsxCol struct {
+ BestFit bool `xml:"bestFit,attr,omitempty"`
Collapsed bool `xml:"collapsed,attr"`
+ CustomWidth bool `xml:"customWidth,attr,omitempty"`
Hidden bool `xml:"hidden,attr"`
Max int `xml:"max,attr"`
Min int `xml:"min,attr"`
+ OutlineLevel uint8 `xml:"outlineLevel,attr,omitempty"`
+ Phonetic bool `xml:"phonetic,attr,omitempty"`
Style int `xml:"style,attr"`
Width float64 `xml:"width,attr"`
- CustomWidth int `xml:"customWidth,attr,omitempty"`
- OutlineLevel uint8 `xml:"outlineLevel,attr,omitempty"`
}
// xlsxDimension directly maps the dimension element in the namespace
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-18 15:04:20 +0000