diff options
| -rw-r--r-- | excelize.go | 9 | ||||
| -rw-r--r-- | lib.go | 7 | ||||
| -rw-r--r-- | sheet.go | 96 | ||||
| -rw-r--r-- | xmlWorksheet.go | 73 |
4 files changed, 121 insertions, 64 deletions
diff --git a/excelize.go b/excelize.go index 86e2d99..af076fb 100644 --- a/excelize.go +++ b/excelize.go @@ -183,15 +183,6 @@ func replaceWorkSheetsRelationshipsNameSpace(workbookMarshal string) string { oldXmlns := `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">` newXmlns := `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">` workbookMarshal = strings.Replace(workbookMarshal, oldXmlns, newXmlns, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></tablePart>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></dimension>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></selection>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></sheetFormatPr>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></printOptions>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></pageSetup>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></pageMargins>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></mergeCell>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></drawing>`, ` />`, -1) return workbookMarshal } @@ -7,7 +7,6 @@ import ( "io" "log" "math" - "strings" ) // ReadZip takes a pointer to a zip.ReadCloser and returns a @@ -28,7 +27,7 @@ func ReadZipReader(r *zip.Reader) (map[string]string, int, error) { if len(v.Name) > 18 { if v.Name[0:19] == `xl/worksheets/sheet` { var xlsx xlsxWorksheet - xml.Unmarshal([]byte(strings.Replace(fileList[v.Name], `<drawing r:id=`, `<drawing rid=`, -1)), &xlsx) + xml.Unmarshal([]byte(fileList[v.Name]), &xlsx) xlsx = checkRow(xlsx) output, _ := xml.Marshal(xlsx) fileList[v.Name] = replaceRelationshipsID(replaceWorkSheetsRelationshipsNameSpace(string(output))) @@ -39,10 +38,10 @@ func ReadZipReader(r *zip.Reader) (map[string]string, int, error) { return fileList, worksheets, nil } -// Read XML content as string and replace drawing property in XML namespace of sheet. +// Read XML content as string. func (f *File) readXML(name string) string { if content, ok := f.XLSX[name]; ok { - return strings.Replace(content, `<drawing r:id=`, `<drawing rid=`, -1) + return content } return `` } @@ -8,6 +8,55 @@ import ( "strings" ) +// Define the empty element and self-close XML tags hack rules for +// xl/workbook.xml and xl/worksheets/sheet%d.xml. +var ( + WorkbookRules = []map[string]string{ + {`xmlns:relationships="http://schemas.openxmlformats.org/officeDocument/2006/relationships" relationships:id="`: `r:id="`}, + {`></sheet>`: ` />`}, + {`></workbookView>`: ` />`}, + {`></fileVersion>`: ` />`}, + {`></workbookPr>`: ` />`}, + {`></calcPr>`: ` />`}, + {`></workbookProtection>`: ` />`}, + {`></fileRecoveryPr>`: ` />`}, + {`></hyperlink>`: ` />`}, + {`></tabColor>`: ` />`}, + {`></pageSetUpPr>`: ` />`}, + {`></pane>`: ` />`}, + {`<extLst></extLst>`: ``}, + {`<fileRecoveryPr />`: ``}, + {`<workbookProtection />`: ``}, + {`<pivotCaches></pivotCaches>`: ``}, + {`<externalReferences></externalReferences>`: ``}, + {`<workbookProtection></workbookProtection>`: ``}, + {`<definedNames></definedNames>`: ``}, + {`<fileRecoveryPr></fileRecoveryPr>`: ``}, + {`<workbookPr />`: ``}, + } + SheetRules = []map[string]string{ + {`xmlns:relationships="http://schemas.openxmlformats.org/officeDocument/2006/relationships" relationships:id="`: `r:id="`}, + {`<drawing></drawing>`: ``}, + {`<drawing />`: ``}, + {`<hyperlinks></hyperlinks>`: ``}, + {`<tableParts count="0"></tableParts>`: ``}, + {`<picture></picture>`: ``}, + {`<legacyDrawing></legacyDrawing>`: ``}, + {`<tabColor></tabColor>`: ``}, + {`<sheetProtection></sheetProtection>`: ``}, + {`<conditionalFormatting></conditionalFormatting>`: ``}, + {`<extLst></extLst>`: ``}, + {`></tablePart>`: ` />`}, + {`></dimension>`: ` />`}, + {`></selection>`: ` />`}, + {`></sheetFormatPr>`: ` />`}, + {`></printOptions>`: ` />`}, + {`></pageSetup>`: ` />`}, + {`></pageMargins>`: ` />`}, + {`></mergeCell>`: ` />`}, + } +) + // NewSheet provice function to greate a new sheet by given index, when // creating a new XLSX file, the default sheet will be create, when you // create a new file, you need to ensure that the index is continuous. @@ -122,17 +171,6 @@ func replaceRelationshipsNameSpace(workbookMarshal string) string { return strings.Replace(workbookMarshal, oldXmlns, newXmlns, -1) } -// replace relationships ID in worksheets/sheet%d.xml -func replaceRelationshipsID(workbookMarshal string) string { - rids := strings.Replace(workbookMarshal, `<drawing rid="" />`, ``, -1) - rids = strings.Replace(rids, `<hyperlinks></hyperlinks>`, ``, -1) - rids = strings.Replace(rids, `<tableParts count="0"></tableParts>`, ``, -1) - rids = strings.Replace(rids, `<picture></picture>`, ``, -1) - rids = strings.Replace(rids, `<legacyDrawing></legacyDrawing>`, ``, -1) - rids = strings.Replace(rids, `<tabColor></tabColor>`, ``, -1) - return strings.Replace(rids, `<drawing rid="`, `<drawing r:id="`, -1) -} - // SetActiveSheet provide function to set default active sheet of XLSX by given index. func (f *File) SetActiveSheet(index int) { var content xlsxWorkbook @@ -188,26 +226,20 @@ func (f *File) SetActiveSheet(index int) { // Replace xl/workbook.xml XML tags to self-closing for compatible Office Excel 2007. func workBookCompatibility(workbookMarshal string) string { - workbookMarshal = strings.Replace(workbookMarshal, `xmlns:relationships="http://schemas.openxmlformats.org/officeDocument/2006/relationships" relationships:id="`, `r:id="`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></sheet>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></workbookView>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></fileVersion>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></workbookPr>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></calcPr>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></workbookProtection>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></fileRecoveryPr>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></hyperlink>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></tabColor>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></pageSetUpPr>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `></pane>`, ` />`, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<extLst></extLst>`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<fileRecoveryPr />`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<workbookProtection />`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<pivotCaches></pivotCaches>`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<externalReferences></externalReferences>`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<workbookProtection></workbookProtection>`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<definedNames></definedNames>`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<fileRecoveryPr></fileRecoveryPr>`, ``, -1) - workbookMarshal = strings.Replace(workbookMarshal, `<workbookPr />`, ``, -1) + for _, rules := range WorkbookRules { + for k, v := range rules { + workbookMarshal = strings.Replace(workbookMarshal, k, v, -1) + } + } + return workbookMarshal +} + +// replace relationships ID in worksheets/sheet%d.xml +func replaceRelationshipsID(workbookMarshal string) string { + for _, rules := range SheetRules { + for k, v := range rules { + workbookMarshal = strings.Replace(workbookMarshal, k, v, -1) + } + } return workbookMarshal } diff --git a/xmlWorksheet.go b/xmlWorksheet.go index 4752a15..0c5494d 100644 --- a/xmlWorksheet.go +++ b/xmlWorksheet.go @@ -9,29 +9,31 @@ import "encoding/xml" // currently I have not checked it for completeness - it does as much // as I need. type xlsxWorksheet struct { - XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main worksheet"` - SheetPr xlsxSheetPr `xml:"sheetPr"` - Dimension xlsxDimension `xml:"dimension"` - SheetViews xlsxSheetViews `xml:"sheetViews"` - SheetFormatPr xlsxSheetFormatPr `xml:"sheetFormatPr"` - Cols *xlsxCols `xml:"cols,omitempty"` - SheetData xlsxSheetData `xml:"sheetData"` - Hyperlinks xlsxHyperlinks `xml:"hyperlinks"` - MergeCells *xlsxMergeCells `xml:"mergeCells,omitempty"` - PrintOptions xlsxPrintOptions `xml:"printOptions"` - PageMargins xlsxPageMargins `xml:"pageMargins"` - PageSetUp xlsxPageSetUp `xml:"pageSetup"` - HeaderFooter xlsxHeaderFooter `xml:"headerFooter"` - Drawing xlsxDrawing `xml:"drawing"` - LegacyDrawing xlsxLegacyDrawing `xml:"legacyDrawing"` - Picture xlsxPicture `xml:"picture"` - TableParts xlsxTableParts `xml:"tableParts"` - ExtLst xlsxExtLst `xml:"extLst"` + XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main worksheet"` + SheetPr xlsxSheetPr `xml:"sheetPr"` + Dimension xlsxDimension `xml:"dimension"` + SheetViews xlsxSheetViews `xml:"sheetViews"` + SheetFormatPr xlsxSheetFormatPr `xml:"sheetFormatPr"` + Cols *xlsxCols `xml:"cols,omitempty"` + SheetData xlsxSheetData `xml:"sheetData"` + SheetProtection xlsxSheetProtection `xml:"sheetProtection"` + MergeCells *xlsxMergeCells `xml:"mergeCells,omitempty"` + ConditionalFormatting xlsxConditionalFormatting `xml:"conditionalFormatting"` + Hyperlinks xlsxHyperlinks `xml:"hyperlinks"` + PrintOptions xlsxPrintOptions `xml:"printOptions"` + PageMargins xlsxPageMargins `xml:"pageMargins"` + PageSetUp xlsxPageSetUp `xml:"pageSetup"` + HeaderFooter xlsxHeaderFooter `xml:"headerFooter"` + Drawing xlsxDrawing `xml:"drawing"` + LegacyDrawing xlsxLegacyDrawing `xml:"legacyDrawing"` + Picture xlsxPicture `xml:"picture"` + TableParts xlsxTableParts `xml:"tableParts"` + ExtLst xlsxExtLst `xml:"extLst"` } // xlsxDrawing change r:id to rid in the namespace. type xlsxDrawing struct { - RID string `xml:"rid,attr"` + RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"` } // xlsxHeaderFooter directly maps the headerFooter element in the namespace @@ -303,6 +305,39 @@ type xlsxF struct { Si string `xml:"si,attr,omitempty"` // Shared formula index } +// xlsxSheetProtection collection expresses the sheet protection options +// to enforce when the sheet is protected. +type xlsxSheetProtection struct { + AlgorithmName string `xml:"algorithmName,attr,omitempty"` + AutoFilter int `xml:"autoFilter,attr,omitempty"` + DeleteColumns int `xml:"deleteColumns,attr,omitempty"` + DeleteRows int `xml:"deleteRows,attr,omitempty"` + FormatCells int `xml:"formatCells,attr,omitempty"` + FormatColumns int `xml:"formatColumns,attr,omitempty"` + FormatRows int `xml:"formatRows,attr,omitempty"` + HashValue string `xml:"hashValue,attr,omitempty"` + InsertColumns int `xml:"insertColumns,attr,omitempty"` + InsertHyperlinks int `xml:"insertHyperlinks,attr,omitempty"` + InsertRows int `xml:"insertRows,attr,omitempty"` + Objects int `xml:"objects,attr,omitempty"` + PivotTables int `xml:"pivotTables,attr,omitempty"` + SaltValue string `xml:"saltValue,attr,omitempty"` + Scenarios int `xml:"scenarios,attr,omitempty"` + SelectLockedCells int `xml:"selectLockedCells,attr,omitempty"` + SelectUnlockedCell int `xml:"selectUnlockedCell,attr,omitempty"` + Sheet int `xml:"sheet,attr,omitempty"` + Sort int `xml:"sort,attr,omitempty"` + SpinCount int `xml:"spinCount,attr,omitempty"` +} + +// A Conditional Format is a format, such as cell shading or font color, +// that a spreadsheet application can automatically apply to cells if a +// specified condition is true. This collection expresses conditional +// formatting rules applied to a particular cell or range. +type xlsxConditionalFormatting struct { + CfRule string `xml:",innerxml"` +} + // xlsxHyperlinks directly maps the hyperlinks element in the namespace // http://schemas.openxmlformats.org/spreadsheetml/2006/main type xlsxHyperlinks struct { |