diff options
| -rw-r--r-- | README.md | 79 | ||||
| -rw-r--r-- | excelize.go | 196 | ||||
| -rw-r--r-- | excelize.png | bin | 0 -> 43470 bytes | |||
| -rw-r--r-- | excelize_test.go | 55 | ||||
| -rw-r--r-- | file.go | 54 | ||||
| -rw-r--r-- | lib.go | 132 | ||||
| -rw-r--r-- | sheet.go | 189 | ||||
| -rw-r--r-- | templates.go | 40 | ||||
| -rw-r--r-- | test/Workbook1.xlsx | bin | 0 -> 19085 bytes | |||
| -rw-r--r-- | xmlContentTypes.go | 49 | ||||
| -rw-r--r-- | xmlWorkbook.go | 167 | ||||
| -rw-r--r-- | xmlWorksheet.go | 276 |
12 files changed, 1237 insertions, 0 deletions
diff --git a/README.md b/README.md new file mode 100644 index 0000000..e9fd91b --- /dev/null +++ b/README.md @@ -0,0 +1,79 @@ +* Excelize + + + +** Introduction +Excelize is a library written in pure Golang and providing a set of function that allow you to write to and read from XLSX files. + + + +** Basic Usage + +*** Installation + +``` +go get github.com/luxurioust/excelize +``` + +*** Create XLSX files + +Here is a minimal example usage that will create XLSX file. + +``` +package main + +import ( + "fmt" + "github.com/luxurioust/excelize" +) + +func main() { + xlsx := excelize.CreateFile() + xlsx = excelize.NewSheet(xlsx, 2, "Sheet2") + xlsx = excelize.NewSheet(xlsx, 3, "Sheet3") + xlsx = excelize.SetCellInt(xlsx, "Sheet2", "A23", 10) + xlsx = excelize.SetCellStr(xlsx, "Sheet3", "B20", "Hello") + err := excelize.Save(xlsx, "~/Workbook.xlsx") + if err != nil { + fmt.Println(err) + } +} +``` + +*** Writing XLSX files + +The following constitutes the bare minimum required to write an XLSX document. + +``` +package main + +import ( + "fmt" + "github.com/luxurioust/excelize" +) + +func main() { + xlsx, err := excelize.Openxlsx("~/Workbook.xlsx") + if err != nil { + fmt.Println(err) + } + xlsx = excelize.SetCellInt(xlsx, "Sheet2", "B2", 100) + xlsx = excelize.SetCellStr(xlsx, "Sheet2", "C11", "Hello") + xlsx = excelize.NewSheet(xlsx, 3, "TestSheet") + xlsx = excelize.SetCellInt(xlsx, "Sheet3", "A23", 10) + xlsx = excelize.SetCellStr(xlsx, "Sheet3", "b230", "World") + xlsx = excelize.SetActiveSheet(xlsx, 2) + if err != nil { + fmt.Println(err) + } + err = excelize.Save(xlsx, "~/Workbook.xlsx") +} +``` + +** Contributing + +Contributions are welcome! Open a pull request to fix a bug, or open an issue to discuss a new feature or change. + +** Licenses + +This program is under the terms of the BSD 3-Clause License. See <https://opensource.org/licenses/BSD-3-Clause>.
\ No newline at end of file diff --git a/excelize.go b/excelize.go new file mode 100644 index 0000000..3838231 --- /dev/null +++ b/excelize.go @@ -0,0 +1,196 @@ +package excelize + +import ( + "archive/zip" + "encoding/xml" + "fmt" + "strconv" + "strings" +) + +type FileList struct { + Key string + Value string +} + +// OpenFile() take the name of an XLSX file and returns a populated +// xlsx.File struct for it. +func OpenFile(filename string) (file []FileList, err error) { + var f *zip.ReadCloser + f, err = zip.OpenReader(filename) + if err != nil { + return nil, err + } + file, err = ReadZip(f) + return +} + +// Set int type value of a cell +func SetCellInt(file []FileList, sheet string, axis string, value int) []FileList { + axis = strings.ToUpper(axis) + var xlsx xlsxWorksheet + col := getColIndex(axis) + row := getRowIndex(axis) + xAxis := row - 1 + yAxis := titleToNumber(col) + + name := fmt.Sprintf("xl/worksheets/%s.xml", strings.ToLower(sheet)) + xml.Unmarshal([]byte(readXml(file, name)), &xlsx) + + rows := xAxis + 1 + cell := yAxis + 1 + + xlsx = checkRow(xlsx) + + xlsx = completeRow(xlsx, rows, cell) + xlsx = completeCol(xlsx, rows, cell) + + xlsx.SheetData.Row[xAxis].C[yAxis].T = "" + xlsx.SheetData.Row[xAxis].C[yAxis].V = strconv.Itoa(value) + + output, err := xml.MarshalIndent(xlsx, "", "") + if err != nil { + fmt.Println(err) + } + saveFileList(file, name, replaceRelationshipsID(replaceWorkSheetsRelationshipsNameSpace(string(output)))) + return file +} + +// Set string type value of a cell +func SetCellStr(file []FileList, sheet string, axis string, value string) []FileList { + axis = strings.ToUpper(axis) + var xlsx xlsxWorksheet + col := getColIndex(axis) + row := getRowIndex(axis) + xAxis := row - 1 + yAxis := titleToNumber(col) + + name := fmt.Sprintf("xl/worksheets/%s.xml", strings.ToLower(sheet)) + xml.Unmarshal([]byte(readXml(file, name)), &xlsx) + + rows := xAxis + 1 + cell := yAxis + 1 + + xlsx = checkRow(xlsx) + xlsx = completeRow(xlsx, rows, cell) + xlsx = completeCol(xlsx, rows, cell) + + xlsx.SheetData.Row[xAxis].C[yAxis].T = "str" + xlsx.SheetData.Row[xAxis].C[yAxis].V = value + + output, err := xml.MarshalIndent(xlsx, "", "") + if err != nil { + fmt.Println(err) + } + saveFileList(file, name, replaceRelationshipsID(replaceWorkSheetsRelationshipsNameSpace(string(output)))) + return file +} + +// Completion column element tags of XML in a sheet +func completeCol(xlsx xlsxWorksheet, row int, cell int) xlsxWorksheet { + if len(xlsx.SheetData.Row) < cell { + for i := len(xlsx.SheetData.Row); i < cell; i++ { + xlsx.SheetData.Row = append(xlsx.SheetData.Row, xlsxRow{ + R: i + 1, + }) + } + } + for k, v := range xlsx.SheetData.Row { + if len(v.C) < cell { + start := len(v.C) + for iii := start; iii < cell; iii++ { + xlsx.SheetData.Row[k].C = append(xlsx.SheetData.Row[k].C, xlsxC{ + R: toAlphaString(iii+1) + strconv.Itoa(k+1), + }) + } + } + } + return xlsx +} + +// Completion row element tags of XML in a sheet +func completeRow(xlsx xlsxWorksheet, row int, cell int) xlsxWorksheet { + if len(xlsx.SheetData.Row) < row { + for i := len(xlsx.SheetData.Row); i < row; i++ { + xlsx.SheetData.Row = append(xlsx.SheetData.Row, xlsxRow{ + R: i + 1, + }) + } + + for ii := 0; ii < row; ii++ { + start := len(xlsx.SheetData.Row[ii].C) + if start == 0 { + for iii := start; iii < cell; iii++ { + xlsx.SheetData.Row[ii].C = append(xlsx.SheetData.Row[ii].C, xlsxC{ + R: toAlphaString(iii+1) + strconv.Itoa(ii+1), + }) + } + } + } + } + return xlsx +} + +// Replace xl/worksheets/sheet%d.xml XML tags to self-closing for compatible Office Excel 2007 +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, `></sheetPr>`, ` />`, -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, `></headerFooter>`, ` />`, -1) + workbookMarshal = strings.Replace(workbookMarshal, `></drawing>`, ` />`, -1) + return workbookMarshal +} + +// Check XML tags and fix discontinuous case, for example: +// +// <row r="15" spans="1:22" x14ac:dyDescent="0.2"> +// <c r="A15" s="2" /> +// <c r="B15" s="2" /> +// <c r="F15" s="1" /> +// <c r="G15" s="1" /> +// </row> +// +// in this case, we should to change it to +// +// <row r="15" spans="1:22" x14ac:dyDescent="0.2"> +// <c r="A15" s="2" /> +// <c r="B15" s="2" /> +// <c r="C15" s="2" /> +// <c r="D15" s="2" /> +// <c r="E15" s="2" /> +// <c r="F15" s="1" /> +// <c r="G15" s="1" /> +// </row> +// +func checkRow(xlsx xlsxWorksheet) xlsxWorksheet { + for k, v := range xlsx.SheetData.Row { + lenCol := len(v.C) + endR := getColIndex(v.C[lenCol-1].R) + endRow := getRowIndex(v.C[lenCol-1].R) + endCol := titleToNumber(endR) + if lenCol < endCol { + oldRow := xlsx.SheetData.Row[k].C + xlsx.SheetData.Row[k].C = xlsx.SheetData.Row[k].C[:0] + tmp := []xlsxC{} + for i := 0; i <= endCol; i++ { + fixAxis := toAlphaString(i+1) + strconv.Itoa(endRow) + tmp = append(tmp, xlsxC{ + R: fixAxis, + }) + } + xlsx.SheetData.Row[k].C = tmp + for _, y := range oldRow { + colAxis := titleToNumber(getColIndex(y.R)) + xlsx.SheetData.Row[k].C[colAxis] = y + } + } + } + return xlsx +} diff --git a/excelize.png b/excelize.png Binary files differnew file mode 100644 index 0000000..b4b4529 --- /dev/null +++ b/excelize.png diff --git a/excelize_test.go b/excelize_test.go new file mode 100644 index 0000000..c2848a0 --- /dev/null +++ b/excelize_test.go @@ -0,0 +1,55 @@ +package excelize + +import ( + "fmt" + "math/rand" + "sync" + "testing" + "time" +) + +var ( + once sync.Once +) + +func testSetup() { + rand.Seed(time.Now().UnixNano()) +} + +func TestExcelize(t *testing.T) { + // Test update a XLSX file + file, err := OpenFile("./test/Workbook1.xlsx") + if err != nil { + fmt.Println(err) + } + file = SetCellInt(file, "SHEET2", "B2", 100) + file = SetCellStr(file, "SHEET2", "C11", "Knowns") + file = NewSheet(file, 3, "TestSheet") + file = SetCellInt(file, "Sheet3", "A23", 10) + file = SetCellStr(file, "SHEET3", "b230", "10") + file = SetActiveSheet(file, 2) + if err != nil { + fmt.Println(err) + } + for i := 1; i <= 300; i++ { + file = SetCellStr(file, "SHEET3", fmt.Sprintf("c%d", i), randToken(5)) + } + err = Save(file, "./test/Workbook_2.xlsx") + + // Test create a XLSX file + file2 := CreateFile() + file2 = NewSheet(file2, 2, "SHEETxxx") + file2 = NewSheet(file2, 3, "asd") + file2 = SetCellInt(file2, "Sheet2", "A23", 10) + file2 = SetCellStr(file2, "SHEET1", "B20", "10") + err = Save(file2, "./test/Workbook_3.xlsx") + if err != nil { + fmt.Println(err) + } +} + +func randToken(length int) string { + b := make([]byte, length) + rand.Read(b) + return fmt.Sprintf("%x", b) +} @@ -0,0 +1,54 @@ +package excelize + +import ( + "archive/zip" + "bytes" + "fmt" + "os" +) + +// Create a new xlsx file +// +// For example: +// +// xlsx := CreateFile() +// +func CreateFile() []FileList { + var file []FileList + file = saveFileList(file, `_rels/.rels`, TEMPLATE_RELS) + file = saveFileList(file, `docProps/app.xml`, TEMPLATE_DOCPROPS_APP) + file = saveFileList(file, `docProps/core.xml`, TEMPLATE_DOCPROPS_CORE) + file = saveFileList(file, `xl/_rels/workbook.xml.rels`, TEMPLATE_WORKBOOK_RELS) + file = saveFileList(file, `xl/theme/theme1.xml`, TEMPLATE_THEME) + file = saveFileList(file, `xl/worksheets/sheet1.xml`, TEMPLATE_SHEET) + file = saveFileList(file, `xl/styles.xml`, TEMPLATE_STYLES) + file = saveFileList(file, `xl/workbook.xml`, TEMPLATE_WORKBOOK) + file = saveFileList(file, `[Content_Types].xml`, TEMPLATE_CONTENT_TYPES) + return file +} + +// Save after create or update to an xlsx file at the provided path. +func Save(files []FileList, name string) error { + buf := new(bytes.Buffer) + w := zip.NewWriter(buf) + for _, file := range files { + f, err := w.Create(file.Key) + if err != nil { + fmt.Println(err) + } + _, err = f.Write([]byte(file.Value)) + if err != nil { + return err + } + } + err := w.Close() + if err != nil { + return err + } + f, err := os.OpenFile(name, os.O_WRONLY|os.O_TRUNC|os.O_CREATE, 0666) + if err != nil { + return err + } + buf.WriteTo(f) + return err +} @@ -0,0 +1,132 @@ +package excelize + +import ( + "archive/zip" + "bytes" + "io" + "log" + "math" + "os" + "regexp" + "strconv" + "strings" +) + +// ReadZip() takes a pointer to a zip.ReadCloser and returns a +// xlsx.File struct populated with its contents. In most cases +// ReadZip is not used directly, but is called internally by OpenFile. +func ReadZip(f *zip.ReadCloser) ([]FileList, error) { + defer f.Close() + return ReadZipReader(&f.Reader) +} + +// ReadZipReader() can be used to read an XLSX in memory without +// touching the filesystem. +func ReadZipReader(r *zip.Reader) ([]FileList, error) { + var fileList []FileList + for _, v := range r.File { + singleFile := FileList{ + Key: v.Name, + Value: readFile(v), + } + fileList = append(fileList, singleFile) + } + return fileList, nil +} + +// Read XML content as string and replace drawing property in XML namespace of sheet +func readXml(files []FileList, name string) string { + for _, file := range files { + if file.Key == name { + return strings.Replace(file.Value, "<drawing r:id=", "<drawing rid=", -1) + } + } + return `` +} + +// Update given file content in file list of XLSX +func saveFileList(files []FileList, name string, content string) []FileList { + for k, v := range files { + if v.Key == name { + files = files[:k+copy(files[k:], files[k+1:])] + files = append(files, FileList{ + Key: name, + Value: XMLHeader + content, + }) + return files + } + } + files = append(files, FileList{ + Key: name, + Value: XMLHeader + content, + }) + return files +} + +// Read file content as string in a archive file +func readFile(file *zip.File) string { + rc, err := file.Open() + if err != nil { + log.Fatal(err) + } + buff := bytes.NewBuffer(nil) + io.Copy(buff, rc) + rc.Close() + return string(buff.Bytes()) +} + +// Convert integer to Excel sheet column title +func toAlphaString(value int) string { + if value < 0 { + return `` + } + var ans string + i := value + for i > 0 { + ans = string((i-1)%26+65) + ans + i = (i - 1) / 26 + } + return ans +} + +// Convert Excel sheet column title to int +func titleToNumber(s string) int { + weight := 0.0 + sum := 0 + for i := len(s) - 1; i >= 0; i-- { + sum = sum + (int(s[i])-int('A')+1)*int(math.Pow(26, weight)) + weight++ + } + return sum - 1 +} + +// Check the file exists +func pathExist(_path string) bool { + _, err := os.Stat(_path) + if err != nil && os.IsNotExist(err) { + return false + } + return true +} + +// Split Excel sheet column title to string and integer, return XAxis +func getColIndex(axis string) string { + r, err := regexp.Compile(`[^\D]`) + if err != nil { + log.Fatal(err) + } + return string(r.ReplaceAll([]byte(axis), []byte(""))) +} + +// Split Excel sheet column title to string and integer, return YAxis +func getRowIndex(axis string) int { + r, err := regexp.Compile(`[\D]`) + if err != nil { + log.Fatal(err) + } + row, err := strconv.Atoi(string(r.ReplaceAll([]byte(axis), []byte("")))) + if err != nil { + log.Fatal(err) + } + return row +} diff --git a/sheet.go b/sheet.go new file mode 100644 index 0000000..732b5c1 --- /dev/null +++ b/sheet.go @@ -0,0 +1,189 @@ +package excelize + +import ( + "encoding/xml" + "fmt" + "strconv" + "strings" +) + +// Create 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. +func NewSheet(file []FileList, index int, name string) []FileList { + // Update docProps/app.xml + file = setAppXml(file) + // Update [Content_Types].xml + file = setContentTypes(file, index) + // Create new sheet /xl/worksheets/sheet%d.xml + file = setSheet(file, index) + // Update xl/_rels/workbook.xml.rels + file = addXlsxWorkbookRels(file, index) + // Update xl/workbook.xml + file = setWorkbook(file, index, name) + return file +} + +// Read and update property of contents type of XLSX +func setContentTypes(file []FileList, index int) []FileList { + var content xlsxTypes + xml.Unmarshal([]byte(readXml(file, `[Content_Types].xml`)), &content) + content.Overrides = append(content.Overrides, xlsxOverride{ + PartName: fmt.Sprintf("/xl/worksheets/sheet%d.xml", index), + ContentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", + }) + output, err := xml.MarshalIndent(content, "", "") + if err != nil { + fmt.Println(err) + } + return saveFileList(file, `[Content_Types].xml`, string(output)) +} + +// Update sheet property by given index +func setSheet(file []FileList, index int) []FileList { + var xlsx xlsxWorksheet + xlsx.Dimension.Ref = "A1" + xlsx.SheetViews.SheetView = append(xlsx.SheetViews.SheetView, xlsxSheetView{ + WorkbookViewId: 0, + }) + output, err := xml.MarshalIndent(xlsx, "", "") + if err != nil { + fmt.Println(err) + } + path := fmt.Sprintf("xl/worksheets/sheet%d.xml", index) + return saveFileList(file, path, replaceRelationshipsID(replaceWorkSheetsRelationshipsNameSpace(string(output)))) +} + +// Update workbook property of XLSX +func setWorkbook(file []FileList, index int, name string) []FileList { + var content xlsxWorkbook + xml.Unmarshal([]byte(readXml(file, `xl/workbook.xml`)), &content) + + rels := readXlsxWorkbookRels(file) + rId := len(rels.Relationships) + content.Sheets.Sheet = append(content.Sheets.Sheet, xlsxSheet{ + Name: name, + SheetId: strconv.Itoa(index), + Id: "rId" + strconv.Itoa(rId), + }) + output, err := xml.MarshalIndent(content, "", "") + if err != nil { + fmt.Println(err) + } + return saveFileList(file, `xl/workbook.xml`, replaceRelationshipsNameSpace(string(output))) +} + +// Read and unmarshal workbook relationships of XLSX +func readXlsxWorkbookRels(file []FileList) xlsxWorkbookRels { + var content xlsxWorkbookRels + xml.Unmarshal([]byte(readXml(file, `xl/_rels/workbook.xml.rels`)), &content) + return content +} + +// Update workbook relationships property of XLSX +func addXlsxWorkbookRels(file []FileList, sheet int) []FileList { + content := readXlsxWorkbookRels(file) + rId := len(content.Relationships) + 1 + content.Relationships = append(content.Relationships, xlsxWorkbookRelation{ + Id: "rId" + strconv.Itoa(rId), + Target: fmt.Sprintf("worksheets/sheet%d.xml", sheet), + Type: "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet", + }) + output, err := xml.MarshalIndent(content, "", "") + if err != nil { + fmt.Println(err) + } + return saveFileList(file, `xl/_rels/workbook.xml.rels`, string(output)) +} + +// Update docProps/app.xml file of XML +func setAppXml(file []FileList) []FileList { + return saveFileList(file, `docProps/app.xml`, TEMPLATE_DOCPROPS_APP) +} + +// Some tools that read XLSX files have very strict requirements about +// the structure of the input XML. In particular both Numbers on the Mac +// and SAS dislike inline XML namespace declarations, or namespace +// prefixes that don't match the ones that Excel itself uses. This is a +// problem because the Go XML library doesn't multiple namespace +// declarations in a single element of a document. This function is a +// horrible hack to fix that after the XML marshalling is completed. +func replaceRelationshipsNameSpace(workbookMarshal string) string { + // newWorkbook := strings.Replace(workbookMarshal, `xmlns:relationships="http://schemas.openxmlformats.org/officeDocument/2006/relationships" relationships:id`, `r:id`, -1) + // Dirty hack to fix issues #63 and #91; encoding/xml currently + // "doesn't allow for additional namespaces to be defined in the + // root element of the document," as described by @tealeg in the + // comments for #63. + oldXmlns := `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">` + newXmlns := `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">` + 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) + return strings.Replace(rids, `<drawing rid="`, `<drawing r:id="`, -1) +} + +// Set default active sheet of XLSX by given index +func SetActiveSheet(file []FileList, index int) []FileList { + var content xlsxWorkbook + if index < 1 { + index = 1 + } + index -= 1 + xml.Unmarshal([]byte(readXml(file, `xl/workbook.xml`)), &content) + if len(content.BookViews.WorkBookView) > 0 { + content.BookViews.WorkBookView[0].ActiveTab = index + } else { + content.BookViews.WorkBookView = append(content.BookViews.WorkBookView, xlsxWorkBookView{ + ActiveTab: index, + }) + } + sheets := len(content.Sheets.Sheet) + output, err := xml.MarshalIndent(content, "", "") + if err != nil { + fmt.Println(err) + } + file = saveFileList(file, `xl/workbook.xml`, workBookCompatibility(replaceRelationshipsNameSpace(string(output)))) + index += 1 + for i := 0; i < sheets; i++ { + xlsx := xlsxWorksheet{} + sheetIndex := i + 1 + path := fmt.Sprintf("xl/worksheets/sheet%d.xml", sheetIndex) + xml.Unmarshal([]byte(readXml(file, path)), &xlsx) + if index == sheetIndex { + if len(xlsx.SheetViews.SheetView) > 0 { + xlsx.SheetViews.SheetView[0].TabSelected = true + } else { + xlsx.SheetViews.SheetView = append(xlsx.SheetViews.SheetView, xlsxSheetView{ + TabSelected: true, + }) + } + } else { + if len(xlsx.SheetViews.SheetView) > 0 { + xlsx.SheetViews.SheetView[0].TabSelected = false + } + } + sheet, err := xml.MarshalIndent(xlsx, "", "") + if err != nil { + fmt.Println(err) + } + file = saveFileList(file, path, replaceRelationshipsID(replaceWorkSheetsRelationshipsNameSpace(string(sheet)))) + } + return file +} + +// 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, `></definedNames>`, ` />`, -1) + workbookMarshal = strings.Replace(workbookMarshal, `></calcPr>`, ` />`, -1) + workbookMarshal = strings.Replace(workbookMarshal, `></workbookProtection>`, ` />`, -1) + workbookMarshal = strings.Replace(workbookMarshal, `></fileRecoveryPr>`, ` />`, -1) + return workbookMarshal +} diff --git a/templates.go b/templates.go new file mode 100644 index 0000000..3d8e24f --- /dev/null +++ b/templates.go @@ -0,0 +1,40 @@ +// This file contains default templates for XML files we don't yet +// populated based on content. + +package excelize + +const XMLHeader = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n" + +const TEMPLATE_DOCPROPS_APP = `<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"> + <TotalTime>0</TotalTime> + <Application>Go XLSX</Application> +</Properties>` + +const TEMPLATE_CONTENT_TYPES = `<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"> + <Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/> + <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/> + <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/> + <Default Extension="xml" ContentType="application/xml"/> + <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/> + <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/> + <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/> + <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/> +</Types>` + +const TEMPLATE_WORKBOOK = `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505" /><workbookPr filterPrivacy="1" defaultThemeVersion="124226" /><bookViews><workbookView xWindow="240" yWindow="105" windowWidth="14805" windowHeight="8010" /></bookViews><sheets><sheet name="Sheet1" sheetId="1" r:id="rId1" /></sheets><calcPr calcId="122211" fullCalcOnLoad="1" /></workbook> +` + +const TEMPLATE_STYLES = `<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="1"><font><sz val="11"/><color theme="1"/><name val="宋体"/><family val="2"/><scheme val="minor"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills><borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles><dxfs count="0"/><tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleMedium9"/></styleSheet> +` +const TEMPLATE_SHEET = `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0"/></sheetViews><sheetFormatPr defaultRowHeight="15"/><sheetData/><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/></worksheet> +` + +const TEMPLATE_WORKBOOK_RELS = `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/></Relationships>` + +const TEMPLATE_DOCPROPS_CORE = `<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dc:creator>xuri</dc:creator><dcterms:created xsi:type="dcterms:W3CDTF">2006-09-16T00:00:00Z</dcterms:created><dcterms:modified xsi:type="dcterms:W3CDTF">2006-09-16T00:00:00Z</dcterms:modified></cp:coreProperties>` + +const TEMPLATE_RELS = `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>` + +const TEMPLATE_THEME = `<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office Theme"><a:themeElements><a:clrScheme name="Office"><a:dk1><a:sysClr val="windowText" lastClr="000000"/></a:dk1><a:lt1><a:sysClr val="window" lastClr="FFFFFF"/></a:lt1><a:dk2><a:srgbClr val="1F497D"/></a:dk2><a:lt2><a:srgbClr val="EEECE1"/></a:lt2><a:accent1><a:srgbClr val="4F81BD"/></a:accent1><a:accent2><a:srgbClr val="C0504D"/></a:accent2><a:accent3><a:srgbClr val="9BBB59"/></a:accent3><a:accent4><a:srgbClr val="8064A2"/></a:accent4><a:accent5><a:srgbClr val="4BACC6"/></a:accent5><a:accent6><a:srgbClr val="F79646"/></a:accent6><a:hlink><a:srgbClr val="0000FF"/></a:hlink><a:folHlink><a:srgbClr val="800080"/></a:folHlink></a:clrScheme><a:fontScheme name="Office"><a:majorFont><a:latin typeface="Cambria"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan" typeface="ＭＳ Ｐゴシック"/><a:font script="Hang" typeface="맑은 고딕"/><a:font script="Hans" typeface="宋体"/><a:font script="Hant" typeface="新細明體"/><a:font script="Arab" typeface="Times New Roman"/><a:font script="Hebr" typeface="Times New Roman"/><a:font script="Thai" typeface="Tahoma"/><a:font script="Ethi" typeface="Nyala"/><a:font script="Beng" typeface="Vrinda"/><a:font script="Gujr" typeface="Shruti"/><a:font script="Khmr" typeface="MoolBoran"/><a:font script="Knda" typeface="Tunga"/><a:font script="Guru" typeface="Raavi"/><a:font script="Cans" typeface="Euphemia"/><a:font script="Cher" typeface="Plantagenet Cherokee"/><a:font script="Yiii" typeface="Microsoft Yi Baiti"/><a:font script="Tibt" typeface="Microsoft Himalaya"/><a:font script="Thaa" typeface="MV Boli"/><a:font script="Deva" typeface="Mangal"/><a:font script="Telu" typeface="Gautami"/><a:font script="Taml" typeface="Latha"/><a:font script="Syrc" typeface="Estrangelo Edessa"/><a:font script="Orya" typeface="Kalinga"/><a:font script="Mlym" typeface="Kartika"/><a:font script="Laoo" typeface="DokChampa"/><a:font script="Sinh" typeface="Iskoola Pota"/><a:font script="Mong" typeface="Mongolian Baiti"/><a:font script="Viet" typeface="Times New Roman"/><a:font script="Uigh" typeface="Microsoft Uighur"/><a:font script="Geor" typeface="Sylfaen"/> +</a:majorFont><a:minorFont><a:latin typeface="Calibri"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan" typeface="ＭＳ Ｐゴシック"/><a:font script="Hang" typeface="맑은 고딕"/><a:font script="Hans" typeface="宋体"/><a:font script="Hant" typeface="新細明體"/><a:font script="Arab" typeface="Arial"/><a:font script="Hebr" typeface="Arial"/><a:font script="Thai" typeface="Tahoma"/><a:font script="Ethi" typeface="Nyala"/><a:font script="Beng" typeface="Vrinda"/><a:font script="Gujr" typeface="Shruti"/><a:font script="Khmr" typeface="DaunPenh"/><a:font script="Knda" typeface="Tunga"/><a:font script="Guru" typeface="Raavi"/><a:font script="Cans" typeface="Euphemia"/><a:font script="Cher" typeface="Plantagenet Cherokee"/><a:font script="Yiii" typeface="Microsoft Yi Baiti"/><a:font script="Tibt" typeface="Microsoft Himalaya"/><a:font script="Thaa" typeface="MV Boli"/><a:font script="Deva" typeface="Mangal"/><a:font script="Telu" typeface="Gautami"/><a:font script="Taml" typeface="Latha"/><a:font script="Syrc" typeface="Estrangelo Edessa"/><a:font script="Orya" typeface="Kalinga"/><a:font script="Mlym" typeface="Kartika"/><a:font script="Laoo" typeface="DokChampa"/><a:font script="Sinh" typeface="Iskoola Pota"/><a:font script="Mong" typeface="Mongolian Baiti"/><a:font script="Viet" typeface="Arial"/><a:font script="Uigh" typeface="Microsoft Uighur"/><a:font script="Geor" typeface="Sylfaen"/> +</a:minorFont></a:fontScheme><a:fmtScheme name="Office"><a:fillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="50000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="35000"><a:schemeClr val="phClr"><a:tint val="37000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:tint val="15000"/><a:satMod val="350000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="16200000" scaled="1"/></a:gradFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:shade val="51000"/><a:satMod val="130000"/></a:schemeClr></a:gs><a:gs pos="80000"><a:schemeClr val="phClr"><a:shade val="93000"/><a:satMod val="130000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="94000"/><a:satMod val="135000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="16200000" scaled="0"/></a:gradFill></a:fillStyleLst><a:lnStyleLst><a:ln w="9525" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"><a:shade val="95000"/><a:satMod val="105000"/></a:schemeClr></a:solidFill><a:prstDash val="solid"/></a:ln><a:ln w="25400" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln><a:ln w="38100" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln></a:lnStyleLst><a:effectStyleLst><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="20000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="38000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst><a:scene3d><a:camera prst="orthographicFront"><a:rot lat="0" lon="0" rev="0"/></a:camera><a:lightRig rig="threePt" dir="t"><a:rot lat="0" lon="0" rev="1200000"/></a:lightRig></a:scene3d><a:sp3d><a:bevelT w="63500" h="25400"/></a:sp3d></a:effectStyle></a:effectStyleLst><a:bgFillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="40000"/><a:satMod val="350000"/></a:schemeClr></a:gs><a:gs pos="40000"><a:schemeClr val="phClr"><a:tint val="45000"/><a:shade val="99000"/><a:satMod val="350000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="20000"/><a:satMod val="255000"/></a:schemeClr></a:gs></a:gsLst><a:path path="circle"><a:fillToRect l="50000" t="-80000" r="50000" b="180000"/></a:path></a:gradFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="80000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="30000"/><a:satMod val="200000"/></a:schemeClr></a:gs></a:gsLst><a:path path="circle"><a:fillToRect l="50000" t="50000" r="50000" b="50000"/></a:path></a:gradFill></a:bgFillStyleLst></a:fmtScheme></a:themeElements><a:objectDefaults/><a:extraClrSchemeLst/></a:theme>` diff --git a/test/Workbook1.xlsx b/test/Workbook1.xlsx Binary files differnew file mode 100644 index 0000000..cbdeda9 --- /dev/null +++ b/test/Workbook1.xlsx diff --git a/xmlContentTypes.go b/xmlContentTypes.go new file mode 100644 index 0000000..af42774 --- /dev/null +++ b/xmlContentTypes.go @@ -0,0 +1,49 @@ +package excelize + +import ( + "encoding/xml" +) + +type xlsxTypes struct { + XMLName xml.Name `xml:"http://schemas.openxmlformats.org/package/2006/content-types Types"` + Overrides []xlsxOverride `xml:"Override"` + Defaults []xlsxDefault `xml:"Default"` +} + +type xlsxOverride struct { + PartName string `xml:",attr"` + ContentType string `xml:",attr"` +} + +type xlsxDefault struct { + Extension string `xml:",attr"` + ContentType string `xml:",attr"` +} + +func MakeDefaultContentTypes() (types xlsxTypes) { + types.Overrides = make([]xlsxOverride, 8) + types.Defaults = make([]xlsxDefault, 2) + + types.Overrides[0].PartName = "/_rels/.rels" + types.Overrides[0].ContentType = "application/vnd.openxmlformats-package.relationships+xml" + types.Overrides[1].PartName = "/docProps/app.xml" + types.Overrides[1].ContentType = "application/vnd.openxmlformats-officedocument.extended-properties+xml" + types.Overrides[2].PartName = "/docProps/core.xml" + types.Overrides[2].ContentType = "application/vnd.openxmlformats-package.core-properties+xml" + types.Overrides[3].PartName = "/xl/_rels/workbook.xml.rels" + types.Overrides[3].ContentType = "application/vnd.openxmlformats-package.relationships+xml" + types.Overrides[4].PartName = "/xl/sharedStrings.xml" + types.Overrides[4].ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" + types.Overrides[5].PartName = "/xl/styles.xml" + types.Overrides[5].ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" + types.Overrides[6].PartName = "/xl/workbook.xml" + types.Overrides[6].ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" + types.Overrides[7].PartName = "/xl/theme/theme1.xml" + types.Overrides[7].ContentType = "application/vnd.openxmlformats-officedocument.theme+xml" + + types.Defaults[0].Extension = "rels" + types.Defaults[0].ContentType = "application/vnd.openxmlformats-package.relationships+xml" + types.Defaults[1].Extension = "xml" + types.Defaults[1].ContentType = "application/xml" + return +} diff --git a/xmlWorkbook.go b/xmlWorkbook.go new file mode 100644 index 0000000..2bbeeed --- /dev/null +++ b/xmlWorkbook.go @@ -0,0 +1,167 @@ +package excelize + +import ( + "encoding/xml" +) + +const ( + // sheet state values as defined by + // http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.sheetstatevalues.aspx + sheetStateVisible = "visible" + sheetStateHidden = "hidden" + sheetStateVeryHidden = "veryHidden" +) + +// xmlxWorkbookRels contains xmlxWorkbookRelations +// which maps sheet id and sheet XML +type xlsxWorkbookRels struct { + XMLName xml.Name `xml:"http://schemas.openxmlformats.org/package/2006/relationships Relationships"` + Relationships []xlsxWorkbookRelation `xml:"Relationship"` +} + +// xmlxWorkbookRelation maps sheet id and xl/worksheets/sheet%d.xml +type xlsxWorkbookRelation struct { + Id string `xml:",attr"` + Target string `xml:",attr"` + Type string `xml:",attr"` +} + +// xlsxWorkbook directly maps the workbook element from the namespace +// http://schemas.openxmlformats.org/spreadsheetml/2006/main - +// currently I have not checked it for completeness - it does as much +// as I need. +type xlsxWorkbook struct { + XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main workbook"` + FileVersion xlsxFileVersion `xml:"fileVersion"` + WorkbookPr xlsxWorkbookPr `xml:"workbookPr"` + WorkbookProtection xlsxWorkbookProtection `xml:"workbookProtection"` + BookViews xlsxBookViews `xml:"bookViews"` + Sheets xlsxSheets `xml:"sheets"` + DefinedNames xlsxDefinedNames `xml:"definedNames"` + CalcPr xlsxCalcPr `xml:"calcPr"` + FileRecoveryPr xlsxFileRecoveryPr `xml:"fileRecoveryPr"` +} + +// xlsxFileRecoveryPr maps sheet recovery information +type xlsxFileRecoveryPr struct { + RepairLoad int `xml:"repairLoad,attr"` +} + +// xlsxWorkbookProtection directly maps the workbookProtection element from the +// namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main +// - currently I have not checked it for completeness - it does as +// much as I need. +type xlsxWorkbookProtection struct { + // We don't need this, yet. +} + +// xlsxFileVersion directly maps the fileVersion element from the +// namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main +// - currently I have not checked it for completeness - it does as +// much as I need. +type xlsxFileVersion struct { + AppName string `xml:"appName,attr,omitempty"` + LastEdited string `xml:"lastEdited,attr,omitempty"` + LowestEdited string `xml:"lowestEdited,attr,omitempty"` + RupBuild string `xml:"rupBuild,attr,omitempty"` +} + +// xlsxWorkbookPr directly maps the workbookPr element from the +// namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main +// - currently I have not checked it for completeness - it does as +// much as I need. +type xlsxWorkbookPr struct { + DefaultThemeVersion string `xml:"defaultThemeVersion,attr,omitempty"` + BackupFile bool `xml:"backupFile,attr,omitempty"` + ShowObjects string `xml:"showObjects,attr,omitempty"` + Date1904 bool `xml:"date1904,attr"` +} + +// xlsxBookViews directly maps the bookViews element from the +// namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main +// - currently I have not checked it for completeness - it does as +// much as I need. +type xlsxBookViews struct { + WorkBookView []xlsxWorkBookView `xml:"workbookView"` +} + +// xlsxWorkBookView directly maps the workbookView element from the +// namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main +// - currently I have not checked it for completeness - it does as +// much as I need. +type xlsxWorkBookView struct { + ActiveTab int `xml:"activeTab,attr,omitempty"` + FirstSheet int `xml:"firstSheet,attr,omitempty"` + ShowHorizontalScroll bool `xml:"showHorizontalScroll,attr,omitempty"` + ShowVerticalScroll bool `xml:"showVerticalScroll,attr,omitempty"` + ShowSheetTabs bool `xml:"showSheetTabs,attr,omitempty"` + TabRatio int `xml:"tabRatio,attr,omitempty"` + WindowHeight int `xml:"windowHeight,attr,omitempty"` + WindowWidth int `xml:"windowWidth,attr,omitempty"` + XWindow string `xml:"xWindow,attr,omitempty"` + YWindow string `xml:"yWindow,attr,omitempty"` +} + +// xlsxSheets directly maps the sheets element from the namespace +// http://schemas.openxmlformats.org/spreadsheetml/2006/main - +// currently I have not checked it for completeness - it does as much +// as I need. +type xlsxSheets struct { + Sheet []xlsxSheet `xml:"sheet"` +} + +// xlsxSheet directly maps the sheet element from the namespace +// http://schemas.openxmlformats.org/spreadsheetml/2006/main - +// currently I have not checked it for completeness - it does as much +// as I need. +type xlsxSheet struct { + Name string `xml:"name,attr,omitempty"` + SheetId string `xml:"sheetId,attr,omitempty"` + Id string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"` + State string `xml:"state,attr,omitempty"` +} + +// xlsxDefinedNames directly maps the definedNames element from the +// namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main +// - currently I have not checked it for completeness - it does as +// much as I need. +type xlsxDefinedNames struct { + DefinedName []xlsxDefinedName `xml:"definedName"` +} + +// xlsxDefinedName directly maps the definedName element from the +// namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main +// - currently I have not checked it for completeness - it does as +// much as I need. +// for a descriptions of the attributes see +// https://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.definedname.aspx +type xlsxDefinedName struct { + Data string `xml:",chardata"` + Name string `xml:"name,attr"` + Comment string `xml:"comment,attr,omitempty"` + CustomMenu string `xml:"customMenu,attr,omitempty"` + Description string `xml:"description,attr,omitempty"` + Help string `xml:"help,attr,omitempty"` + ShortcutKey string `xml:"shortcutKey,attr,omitempty"` + StatusBar string `xml:"statusBar,attr,omitempty"` + LocalSheetID int `xml:"localSheetId,attr,omitempty"` + FunctionGroupID int `xml:"functionGroupId,attr,omitempty"` + Function bool `xml:"function,attr,omitempty"` + Hidden bool `xml:"hidden,attr,omitempty"` + VbProcedure bool `xml:"vbProcedure,attr,omitempty"` + PublishToServer bool `xml:"publishToServer,attr,omitempty"` + WorkbookParameter bool `xml:"workbookParameter,attr,omitempty"` + Xlm bool `xml:"xml,attr,omitempty"` +} + +// xlsxCalcPr directly maps the calcPr element from the namespace +// http://schemas.openxmlformats.org/spreadsheetml/2006/main - +// currently I have not checked it for completeness - it does as much +// as I need. +type xlsxCalcPr struct { + CalcId string `xml:"calcId,attr,omitempty"` + IterateCount int `xml:"iterateCount,attr,omitempty"` + RefMode string `xml:"refMode,attr,omitempty"` + Iterate bool `xml:"iterate,attr,omitempty"` + IterateDelta float64 `xml:"iterateDelta,attr,omitempty"` +} diff --git a/xmlWorksheet.go b/xmlWorksheet.go new file mode 100644 index 0000000..8c1e849 --- /dev/null +++ b/xmlWorksheet.go @@ -0,0 +1,276 @@ +package excelize + +import ( + "encoding/xml" +) + +// xlsxWorksheet directly maps the worksheet 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. +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"` + 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"` +} + +// xlsxDrawing change r:id to rid in the namespace +type xlsxDrawing struct { + RId string `xml:"rid,attr"` +} + +// xlsxHeaderFooter directly maps the headerFooter 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. +type xlsxHeaderFooter struct { + DifferentFirst bool `xml:"differentFirst,attr"` + DifferentOddEven bool `xml:"differentOddEven,attr"` + OddHeader []xlsxOddHeader `xml:"oddHeader"` + OddFooter []xlsxOddFooter `xml:"oddFooter"` +} + +// xlsxOddHeader directly maps the oddHeader 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. +type xlsxOddHeader struct { + Content string `xml:",chardata"` +} + +// xlsxOddFooter directly maps the oddFooter 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. +type xlsxOddFooter struct { + Content string `xml:",chardata"` +} + +// xlsxPageSetUp directly maps the pageSetup 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. +type xlsxPageSetUp struct { + PaperSize string `xml:"paperSize,attr,omitempty"` + Scale int `xml:"scale,attr"` + FirstPageNumber int `xml:"firstPageNumber,attr"` + FitToWidth int `xml:"fitToWidth,attr"` + FitToHeight int `xml:"fitToHeight,attr"` + PageOrder string `xml:"pageOrder,attr,omitempty"` + Orientation string `xml:"orientation,attr,omitempty"` + UsePrinterDefaults bool `xml:"usePrinterDefaults,attr"` + BlackAndWhite bool `xml:"blackAndWhite,attr"` + Draft bool `xml:"draft,attr"` + CellComments string `xml:"cellComments,attr,omitempty"` + UseFirstPageNumber bool `xml:"useFirstPageNumber,attr"` + HorizontalDPI float32 `xml:"horizontalDpi,attr"` + VerticalDPI float32 `xml:"verticalDpi,attr"` + Copies int `xml:"copies,attr"` +} + +// xlsxPrintOptions directly maps the printOptions 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. +type xlsxPrintOptions struct { + Headings bool `xml:"headings,attr"` + GridLines bool `xml:"gridLines,attr"` + GridLinesSet bool `xml:"gridLinesSet,attr"` + HorizontalCentered bool `xml:"horizontalCentered,attr"` + VerticalCentered bool `xml:"verticalCentered,attr"` +} + +// xlsxPageMargins directly maps the pageMargins 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. +type xlsxPageMargins struct { + Left float64 `xml:"left,attr"` + Right float64 `xml:"right,attr"` + Top float64 `xml:"top,attr"` + Bottom float64 `xml:"bottom,attr"` + Header float64 `xml:"header,attr"` + Footer float64 `xml:"footer,attr"` +} + +// xlsxSheetFormatPr directly maps the sheetFormatPr 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. +type xlsxSheetFormatPr struct { + DefaultColWidth float64 `xml:"defaultColWidth,attr,omitempty"` + DefaultRowHeight float64 `xml:"defaultRowHeight,attr"` + OutlineLevelCol uint8 `xml:"outlineLevelCol,attr,omitempty"` + OutlineLevelRow uint8 `xml:"outlineLevelRow,attr,omitempty"` +} + +// xlsxSheetViews directly maps the sheetViews 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. +type xlsxSheetViews struct { + SheetView []xlsxSheetView `xml:"sheetView"` +} + +// xlsxSheetView directly maps the sheetView 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. +type xlsxSheetView struct { + // WindowProtection bool `xml:"windowProtection,attr"` + // ShowFormulas bool `xml:"showFormulas,attr"` + // ShowGridLines bool `xml:"showGridLines,attr"` + // ShowRowColHeaders bool `xml:"showRowColHeaders,attr"` + // ShowZeros bool `xml:"showZeros,attr"` + // RightToLeft bool `xml:"rightToLeft,attr"` + TabSelected bool `xml:"tabSelected,attr"` + // ShowOutlineSymbols bool `xml:"showOutlineSymbols,attr"` + // DefaultGridColor bool `xml:"defaultGridColor,attr"` + // View string `xml:"view,attr"` + TopLeftCell string `xml:"topLeftCell,attr,omitempty"` + // ColorId int `xml:"colorId,attr"` + // ZoomScale float64 `xml:"zoomScale,attr"` + // ZoomScaleNormal float64 `xml:"zoomScaleNormal,attr"` + // ZoomScalePageLayoutView float64 `xml:"zoomScalePageLayoutView,attr"` + WorkbookViewId int `xml:"workbookViewId,attr"` + Selection []xlsxSelection `xml:"selection"` + Pane *xlsxPane `xml:"pane,omitempty"` +} + +// xlsxSelection directly maps the selection 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. +type xlsxSelection struct { + Pane string `xml:"pane,attr,omitempty"` + ActiveCell string `xml:"activeCell,attr"` + ActiveCellId int `xml:"activeCellId,attr"` + SQRef string `xml:"sqref,attr"` +} + +// xlsxSelection directly maps the selection 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. +type xlsxPane struct { + XSplit float64 `xml:"xSplit,attr"` + YSplit float64 `xml:"ySplit,attr"` + TopLeftCell string `xml:"topLeftCell,attr,omitempty"` + ActivePane string `xml:"activePane,attr,omitempty"` + State string `xml:"state,attr,omitempty"` // Either "split" or "frozen" +} + +// xlsxSheetPr directly maps the sheetPr 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. +type xlsxSheetPr struct { + FilterMode bool `xml:"filterMode,attr"` + PageSetUpPr []xlsxPageSetUpPr `xml:"pageSetUpPr"` +} + +// xlsxPageSetUpPr directly maps the pageSetupPr 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. +type xlsxPageSetUpPr struct { + FitToPage bool `xml:"fitToPage,attr"` +} + +// xlsxCols directly maps the cols 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. +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. +type xlsxCol struct { + Collapsed bool `xml:"collapsed,attr"` + Hidden bool `xml:"hidden,attr"` + Max int `xml:"max,attr"` + Min int `xml:"min,attr"` + 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 +// http://schemas.openxmlformats.org/spreadsheetml/2006/main - +// currently I have not checked it for completeness - it does as much +// as I need. +type xlsxDimension struct { + Ref string `xml:"ref,attr"` +} + +// xlsxSheetData directly maps the sheetData 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. +type xlsxSheetData struct { + XMLName xml.Name `xml:"sheetData"` + Row []xlsxRow `xml:"row"` +} + +// xlsxRow directly maps the row 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. +type xlsxRow struct { + R int `xml:"r,attr"` + Spans string `xml:"spans,attr,omitempty"` + Hidden bool `xml:"hidden,attr,omitempty"` + C []xlsxC `xml:"c"` + Ht string `xml:"ht,attr,omitempty"` + CustomHeight bool `xml:"customHeight,attr,omitempty"` + OutlineLevel uint8 `xml:"outlineLevel,attr,omitempty"` +} + +type xlsxMergeCell struct { + Ref string `xml:"ref,attr"` // ref: horiz "A1:C1", vert "B3:B6", both "D3:G4" +} + +type xlsxMergeCells struct { + XMLName xml.Name //`xml:"mergeCells,omitempty"` + Count int `xml:"count,attr,omitempty"` + Cells []xlsxMergeCell `xml:"mergeCell,omitempty"` +} + +// xlsxC directly maps the c 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. +type xlsxC struct { + R string `xml:"r,attr"` // Cell ID, e.g. A1 + S int `xml:"s,attr,omitempty"` // Style reference. + // Str string `xml:"str,attr,omitempty"` // Style reference. + T string `xml:"t,attr,omitempty"` // Type. + F *xlsxF `xml:"f,omitempty"` // Formula + V string `xml:"v,omitempty"` // Value +} + +// xlsxF directly maps the f 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. +type xlsxF struct { + Content string `xml:",chardata"` + T string `xml:"t,attr,omitempty"` // Formula type + Ref string `xml:"ref,attr,omitempty"` // Shared formula ref + Si int `xml:"si,attr,omitempty"` // Shared formula index +} |
