diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | README.md | 8 | ||||
| -rw-r--r-- | README_zh.md | 10 | ||||
| -rw-r--r-- | cell_test.go | 4 | ||||
| -rw-r--r-- | chart_test.go | 2 | ||||
| -rw-r--r-- | col.go | 2 | ||||
| -rw-r--r-- | col_test.go | 5 | ||||
| -rw-r--r-- | crypt_test.go | 1 | ||||
| -rw-r--r-- | docProps_test.go | 2 | ||||
| -rw-r--r-- | errors.go | 3 | ||||
| -rw-r--r-- | excelize.go | 53 | ||||
| -rw-r--r-- | excelize_test.go | 28 | ||||
| -rw-r--r-- | file.go | 12 | ||||
| -rw-r--r-- | file_test.go | 7 | ||||
| -rw-r--r-- | lib.go | 71 | ||||
| -rw-r--r-- | lib_test.go | 43 | ||||
| -rw-r--r-- | merge_test.go | 4 | ||||
| -rw-r--r-- | picture.go | 3 | ||||
| -rw-r--r-- | picture_test.go | 5 | ||||
| -rw-r--r-- | rows.go | 43 | ||||
| -rw-r--r-- | rows_test.go | 25 | ||||
| -rw-r--r-- | sheet.go | 8 | ||||
| -rw-r--r-- | sheet_test.go | 3 | ||||
| -rw-r--r-- | stream_test.go | 15 |
24 files changed, 322 insertions, 36 deletions
@@ -5,6 +5,7 @@ test/Test*.xlsm test/BadEncrypt.xlsx test/BadWorkbook.SaveAsEmptyStruct.xlsx test/*.png +test/excelize-* *.out *.test .idea @@ -96,6 +96,10 @@ func main() { } fmt.Println() } + // Close the spreadsheet. + if err = f.Close(); err != nil { + fmt.Println(err) + } } ``` @@ -203,6 +207,10 @@ func main() { if err = f.Save(); err != nil { fmt.Println(err) } + // Close the spreadsheet. + if err = f.Close(); err != nil { + fmt.Println(err) + } } ``` diff --git a/README_zh.md b/README_zh.md index 359192b..3b90eec 100644 --- a/README_zh.md +++ b/README_zh.md @@ -96,6 +96,10 @@ func main() { } fmt.Println() } + // 关闭工作簿 + if err = f.Close(); err != nil { + fmt.Println(err) + } } ``` @@ -199,10 +203,14 @@ func main() { }`); err != nil { fmt.Println(err) } - // 保存文件 + // 保存工作簿 if err = f.Save(); err != nil { fmt.Println(err) } + // 关闭工作簿 + if err = f.Close(); err != nil { + fmt.Println(err) + } } ``` diff --git a/cell_test.go b/cell_test.go index 5467e43..35eaa96 100644 --- a/cell_test.go +++ b/cell_test.go @@ -72,6 +72,7 @@ func TestConcurrency(t *testing.T) { } assert.Equal(t, "1", val) assert.NoError(t, f.SaveAs(filepath.Join("test", "TestConcurrency.xlsx"))) + assert.NoError(t, f.Close()) } func TestCheckCellInArea(t *testing.T) { @@ -325,6 +326,7 @@ func TestOverflowNumericCell(t *testing.T) { assert.NoError(t, err) // GOARCH=amd64 - all ok; GOARCH=386 - actual: "-2147483648" assert.Equal(t, "8595602512225", val, "A1 should be 8595602512225") + assert.NoError(t, f.Close()) } func TestSetCellFormula(t *testing.T) { @@ -340,6 +342,7 @@ func TestSetCellFormula(t *testing.T) { assert.EqualError(t, f.SetCellFormula("Sheet1", "C", "SUM(Sheet2!D2,Sheet2!D9)"), `cannot convert cell "C" to coordinates: invalid cell name "C"`) assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula1.xlsx"))) + assert.NoError(t, f.Close()) f, err = OpenFile(filepath.Join("test", "CalcChain.xlsx")) if !assert.NoError(t, err) { @@ -351,6 +354,7 @@ func TestSetCellFormula(t *testing.T) { // Test remove all cell formula. assert.NoError(t, f.SetCellFormula("Sheet1", "B1", "")) assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula3.xlsx"))) + assert.NoError(t, f.Close()) // Test set shared formula for the cells. f = NewFile() diff --git a/chart_test.go b/chart_test.go index d2a3975..2cd7131 100644 --- a/chart_test.go +++ b/chart_test.go @@ -206,6 +206,7 @@ func TestAddChart(t *testing.T) { assert.EqualError(t, f.AddChart("Sheet2", "BD32", `{"type":"col","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"},{"name":"Sheet1!$A$33","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$33:$D$33"},{"name":"Sheet1!$A$34","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$34:$D$34"},{"name":"Sheet1!$A$35","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$35:$D$35"},{"name":"Sheet1!$A$36","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$36:$D$36"},{"name":"Sheet1!$A$37","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$37:$D$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"2D Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`, ""), "unexpected end of JSON input") // Test add combo chart with unsupported chart type assert.EqualError(t, f.AddChart("Sheet2", "BD64", `{"type":"barOfPie","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$A$30:$D$37","values":"Sheet1!$B$30:$B$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Bar of Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"major_grid_lines":true},"y_axis":{"major_grid_lines":true}}`, `{"type":"unknown","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$A$30:$D$37","values":"Sheet1!$B$30:$B$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Bar of Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"major_grid_lines":true},"y_axis":{"major_grid_lines":true}}`), "unsupported chart type unknown") + assert.NoError(t, f.Close()) } func TestAddChartSheet(t *testing.T) { @@ -254,6 +255,7 @@ func TestDeleteChart(t *testing.T) { assert.EqualError(t, f.DeleteChart("Sheet1", ""), `cannot convert cell "" to coordinates: invalid cell name ""`) // Test delete chart on no chart worksheet. assert.NoError(t, NewFile().DeleteChart("Sheet1", "A1")) + assert.NoError(t, f.Close()) } func TestChartWithLogarithmicBase(t *testing.T) { @@ -209,7 +209,7 @@ func (f *File) Cols(sheet string) (*Cols, error) { f.saveFileList(name, f.replaceNameSpaceBytes(name, output)) } var colIterator columnXMLIterator - colIterator.cols.sheetXML = f.readXML(name) + colIterator.cols.sheetXML = f.readBytes(name) decoder := f.xmlNewDecoder(bytes.NewReader(colIterator.cols.sheetXML)) for { token, _ := decoder.Token() diff --git a/col_test.go b/col_test.go index b19eadf..213c370 100644 --- a/col_test.go +++ b/col_test.go @@ -39,6 +39,7 @@ func TestCols(t *testing.T) { if !assert.Equal(t, collectedRows, returnedColumns) { t.FailNow() } + assert.NoError(t, f.Close()) f = NewFile() cells := []string{"C2", "C3", "C4"} @@ -75,6 +76,7 @@ func TestColumnsIterator(t *testing.T) { require.True(t, colCount <= expectedNumCol, "colCount is greater than expected") } assert.Equal(t, expectedNumCol, colCount) + assert.NoError(t, f.Close()) f = NewFile() cells := []string{"C2", "C3", "C4", "D2", "D3", "D4"} @@ -99,6 +101,7 @@ func TestColsError(t *testing.T) { } _, err = f.Cols("SheetN") assert.EqualError(t, err, "sheet SheetN is not exist") + assert.NoError(t, f.Close()) } func TestGetColsError(t *testing.T) { @@ -108,6 +111,7 @@ func TestGetColsError(t *testing.T) { } _, err = f.GetCols("SheetN") assert.EqualError(t, err, "sheet SheetN is not exist") + assert.NoError(t, f.Close()) f = NewFile() f.Sheet.Delete("xl/worksheets/sheet1.xml") @@ -283,6 +287,7 @@ func TestOutlineLevel(t *testing.T) { f, err = OpenFile(filepath.Join("test", "Book1.xlsx")) assert.NoError(t, err) assert.NoError(t, f.SetColOutlineLevel("Sheet2", "B", 2)) + assert.NoError(t, f.Close()) } func TestSetColStyle(t *testing.T) { diff --git a/crypt_test.go b/crypt_test.go index 68ff5b8..cb0b160 100644 --- a/crypt_test.go +++ b/crypt_test.go @@ -22,6 +22,7 @@ func TestEncrypt(t *testing.T) { f, err := OpenFile(filepath.Join("test", "encryptSHA1.xlsx"), Options{Password: "password"}) assert.NoError(t, err) assert.EqualError(t, f.SaveAs(filepath.Join("test", "BadEncrypt.xlsx"), Options{Password: "password"}), "not support encryption currently") + assert.NoError(t, f.Close()) } func TestEncryptionMechanism(t *testing.T) { diff --git a/docProps_test.go b/docProps_test.go index 40ae2dc..df1b6c6 100644 --- a/docProps_test.go +++ b/docProps_test.go @@ -44,6 +44,7 @@ func TestSetDocProps(t *testing.T) { assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetDocProps.xlsx"))) f.Pkg.Store("docProps/core.xml", nil) assert.NoError(t, f.SetDocProps(&DocProperties{})) + assert.NoError(t, f.Close()) // Test unsupported charset f = NewFile() @@ -62,6 +63,7 @@ func TestGetDocProps(t *testing.T) { f.Pkg.Store("docProps/core.xml", nil) _, err = f.GetDocProps() assert.NoError(t, err) + assert.NoError(t, f.Close()) // Test unsupported charset f = NewFile() @@ -131,4 +131,7 @@ var ( // ErrCellCharsLength defined the error message for receiving a cell // characters length that exceeds the limit. ErrCellCharsLength = fmt.Errorf("cell value must be 0-%d characters", TotalCellChars) + // ErrOptionsUnzipSizeLimit defined the error message for receiving + // invalid UnzipSizeLimit and WorksheetUnzipMemLimit. + ErrOptionsUnzipSizeLimit = errors.New("the value of UnzipSizeLimit should be greater than or equal to WorksheetUnzipMemLimit") ) diff --git a/excelize.go b/excelize.go index 24a1a4e..c5778c8 100644 --- a/excelize.go +++ b/excelize.go @@ -37,6 +37,7 @@ type File struct { checked map[string]bool sheetMap map[string]string streams map[string]*StreamWriter + tempFiles sync.Map CalcChain *xlsxCalcChain Comments map[string]*xlsxComments ContentTypes *xlsxTypes @@ -58,13 +59,26 @@ type File struct { type charsetTranscoderFn func(charset string, input io.Reader) (rdr io.Reader, err error) -// Options define the options for open and reading spreadsheet. RawCellValue -// specify if apply the number format for the cell value or get the raw -// value. +// Options define the options for open and reading spreadsheet. +// +// Password specifies the password of the spreadsheet in plain text. +// +// RawCellValue specifies if apply the number format for the cell value or get +// the raw value. +// +// UnzipSizeLimit specifies the unzip size limit in bytes on open the +// spreadsheet, this value should be greater than or equal to +// WorksheetUnzipMemLimit, the default size limit is 16GB. +// +// WorksheetUnzipMemLimit specifies the memory limit on unzipping worksheet in +// bytes, worksheet XML will be extracted to system temporary directory when +// the file size is over this value, this value should be less than or equal +// to UnzipSizeLimit, the default value is 16MB. type Options struct { - Password string - RawCellValue bool - UnzipSizeLimit int64 + Password string + RawCellValue bool + UnzipSizeLimit int64 + WorksheetUnzipMemLimit int64 } // OpenFile take the name of an spreadsheet file and returns a populated @@ -78,10 +92,8 @@ type Options struct { // // Note that the excelize just support decrypt and not support encrypt // currently, the spreadsheet saved by Save and SaveAs will be without -// password unprotected. -// -// UnzipSizeLimit specified the unzip size limit in bytes on open the -// spreadsheet, the default size limit is 16GB. +// password unprotected. Close the file by Close after opening the +// spreadsheet. func OpenFile(filename string, opt ...Options) (*File, error) { file, err := os.Open(filepath.Clean(filename)) if err != nil { @@ -99,10 +111,11 @@ func OpenFile(filename string, opt ...Options) (*File, error) { // newFile is object builder func newFile() *File { return &File{ - options: &Options{UnzipSizeLimit: UnzipSizeLimit}, + options: &Options{UnzipSizeLimit: UnzipSizeLimit, WorksheetUnzipMemLimit: StreamChunkSize}, xmlAttr: make(map[string][]xml.Attr), checked: make(map[string]bool), sheetMap: make(map[string]string), + tempFiles: sync.Map{}, Comments: make(map[string]*xlsxComments), Drawings: sync.Map{}, sharedStringsMap: make(map[string]int), @@ -125,6 +138,18 @@ func OpenReader(r io.Reader, opt ...Options) (*File, error) { f.options = parseOptions(opt...) if f.options.UnzipSizeLimit == 0 { f.options.UnzipSizeLimit = UnzipSizeLimit + if f.options.WorksheetUnzipMemLimit > f.options.UnzipSizeLimit { + f.options.UnzipSizeLimit = f.options.WorksheetUnzipMemLimit + } + } + if f.options.WorksheetUnzipMemLimit == 0 { + f.options.WorksheetUnzipMemLimit = StreamChunkSize + if f.options.UnzipSizeLimit < f.options.WorksheetUnzipMemLimit { + f.options.WorksheetUnzipMemLimit = f.options.UnzipSizeLimit + } + } + if f.options.WorksheetUnzipMemLimit > f.options.UnzipSizeLimit { + return nil, ErrOptionsUnzipSizeLimit } if bytes.Contains(b, oleIdentifier) { b, err = Decrypt(b, f.options) @@ -136,7 +161,7 @@ func OpenReader(r io.Reader, opt ...Options) (*File, error) { if err != nil { return nil, err } - file, sheetCount, err := ReadZipReader(zr, f.options) + file, sheetCount, err := f.ReadZipReader(zr) if err != nil { return nil, err } @@ -210,10 +235,10 @@ func (f *File) workSheetReader(sheet string) (ws *xlsxWorksheet, err error) { } ws = new(xlsxWorksheet) if _, ok := f.xmlAttr[name]; !ok { - d := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(name)))) + d := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readBytes(name)))) f.xmlAttr[name] = append(f.xmlAttr[name], getRootElement(d)...) } - if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(name)))). + if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readBytes(name)))). Decode(ws); err != nil && err != io.EOF { err = fmt.Errorf("xml decode error: %s", err) return diff --git a/excelize_test.go b/excelize_test.go index 02abce5..f556d83 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -167,6 +167,7 @@ func TestOpenFile(t *testing.T) { } assert.NoError(t, f.SaveAs(filepath.Join("test", "TestOpenFile.xlsx"))) assert.EqualError(t, f.SaveAs(filepath.Join("test", strings.Repeat("c", 199), ".xlsx")), ErrMaxFileNameLength.Error()) + assert.NoError(t, f.Close()) } func TestSaveFile(t *testing.T) { @@ -175,11 +176,13 @@ func TestSaveFile(t *testing.T) { t.FailNow() } assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSaveFile.xlsx"))) + assert.NoError(t, f.Close()) f, err = OpenFile(filepath.Join("test", "TestSaveFile.xlsx")) if !assert.NoError(t, err) { t.FailNow() } assert.NoError(t, f.Save()) + assert.NoError(t, f.Close()) } func TestSaveAsWrongPath(t *testing.T) { @@ -187,6 +190,7 @@ func TestSaveAsWrongPath(t *testing.T) { assert.NoError(t, err) // Test write file to not exist directory. assert.EqualError(t, f.SaveAs(""), "open .: is a directory") + assert.NoError(t, f.Close()) } func TestCharsetTranscoder(t *testing.T) { @@ -197,7 +201,7 @@ func TestCharsetTranscoder(t *testing.T) { func TestOpenReader(t *testing.T) { _, err := OpenReader(strings.NewReader("")) assert.EqualError(t, err, "zip: not a valid zip file") - _, err = OpenReader(bytes.NewReader(oleIdentifier), Options{Password: "password"}) + _, err = OpenReader(bytes.NewReader(oleIdentifier), Options{Password: "password", WorksheetUnzipMemLimit: UnzipSizeLimit + 1}) assert.EqualError(t, err, "decrypted file failed") // Test open spreadsheet with unzip size limit. @@ -210,6 +214,7 @@ func TestOpenReader(t *testing.T) { val, err := f.GetCellValue("Sheet1", "A1") assert.NoError(t, err) assert.Equal(t, "SECRET", val) + assert.NoError(t, f.Close()) // Test open password protected spreadsheet created by LibreOffice 7.0.0.3. f, err = OpenFile(filepath.Join("test", "encryptAES.xlsx"), Options{Password: "password"}) @@ -217,6 +222,11 @@ func TestOpenReader(t *testing.T) { val, err = f.GetCellValue("Sheet1", "A1") assert.NoError(t, err) assert.Equal(t, "SECRET", val) + assert.NoError(t, f.Close()) + + // Test open spreadsheet with invalid optioins. + _, err = OpenReader(bytes.NewReader(oleIdentifier), Options{UnzipSizeLimit: 1, WorksheetUnzipMemLimit: 2}) + assert.EqualError(t, err, ErrOptionsUnzipSizeLimit.Error()) // Test unexpected EOF. var b bytes.Buffer @@ -266,6 +276,7 @@ func TestBrokenFile(t *testing.T) { f3.GetActiveSheetIndex() f3.SetActiveSheet(1) assert.NoError(t, err) + assert.NoError(t, f3.Close()) }) t.Run("OpenNotExistsFile", func(t *testing.T) { @@ -340,6 +351,7 @@ func TestSetCellHyperLink(t *testing.T) { assert.EqualError(t, f.SetCellHyperLink("Sheet2", "", "Sheet1!D60", "Location"), `invalid cell name ""`) assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellHyperLink.xlsx"))) + assert.NoError(t, f.Close()) f = NewFile() _, err = f.workSheetReader("Sheet1") @@ -377,6 +389,7 @@ func TestGetCellHyperLink(t *testing.T) { link, target, err = f.GetCellHyperLink("Sheet3", "H3") assert.EqualError(t, err, "sheet Sheet3 is not exist") t.Log(link, target) + assert.NoError(t, f.Close()) f = NewFile() _, err = f.workSheetReader("Sheet1") @@ -398,7 +411,6 @@ func TestGetCellHyperLink(t *testing.T) { assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`) assert.Equal(t, link, false) assert.Equal(t, target, "") - } func TestSetSheetBackground(t *testing.T) { @@ -418,6 +430,7 @@ func TestSetSheetBackground(t *testing.T) { } assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetSheetBackground.xlsx"))) + assert.NoError(t, f.Close()) } func TestSetSheetBackgroundErrors(t *testing.T) { @@ -433,6 +446,7 @@ func TestSetSheetBackgroundErrors(t *testing.T) { err = f.SetSheetBackground("Sheet2", filepath.Join("test", "Book1.xlsx")) assert.EqualError(t, err, ErrImgExt.Error()) + assert.NoError(t, f.Close()) } // TestWriteArrayFormula tests the extended options of SetCellFormula by writing an array function @@ -1052,6 +1066,7 @@ func TestConditionalFormat(t *testing.T) { if !assert.NoError(t, err) { t.FailNow() } + assert.NoError(t, f.Close()) } func TestConditionalFormatError(t *testing.T) { @@ -1082,6 +1097,7 @@ func TestSharedStrings(t *testing.T) { t.FailNow() } assert.Equal(t, "Test Weight (Kgs)", rows[0][0]) + assert.NoError(t, f.Close()) } func TestSetSheetRow(t *testing.T) { @@ -1098,6 +1114,7 @@ func TestSetSheetRow(t *testing.T) { assert.EqualError(t, f.SetSheetRow("Sheet1", "B27", []interface{}{}), ErrParameterInvalid.Error()) assert.EqualError(t, f.SetSheetRow("Sheet1", "B27", &f), ErrParameterInvalid.Error()) assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetSheetRow.xlsx"))) + assert.NoError(t, f.Close()) } func TestHSL(t *testing.T) { @@ -1155,6 +1172,7 @@ func TestUnprotectSheet(t *testing.T) { assert.NoError(t, f.UnprotectSheet("Sheet1")) assert.NoError(t, f.SaveAs(filepath.Join("test", "TestUnprotectSheet.xlsx"))) + assert.NoError(t, f.Close()) } func TestSetDefaultTimeStyle(t *testing.T) { @@ -1323,7 +1341,11 @@ func fillCells(f *File, sheet string, colCount, rowCount int) { func BenchmarkOpenFile(b *testing.B) { for i := 0; i < b.N; i++ { - if _, err := OpenFile(filepath.Join("test", "Book1.xlsx")); err != nil { + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + if err != nil { + b.Error(err) + } + if err := f.Close(); err != nil { b.Error(err) } } @@ -82,6 +82,18 @@ func (f *File) SaveAs(name string, opt ...Options) error { return f.Write(file) } +// Close closes and cleanup the open temporary file for the spreadsheet. +func (f *File) Close() error { + var err error + f.tempFiles.Range(func(k, v interface{}) bool { + if err = os.Remove(v.(string)); err != nil { + return false + } + return true + }) + return err +} + // Write provides a function to write to an io.Writer. func (f *File) Write(w io.Writer) error { _, err := f.WriteTo(w) diff --git a/file_test.go b/file_test.go index 956ff92..ee5d322 100644 --- a/file_test.go +++ b/file_test.go @@ -9,6 +9,7 @@ import ( "testing" "github.com/stretchr/testify/assert" + "github.com/stretchr/testify/require" ) func BenchmarkWrite(b *testing.B) { @@ -62,3 +63,9 @@ func TestWriteTo(t *testing.T) { assert.Nil(t, err) } } + +func TestClose(t *testing.T) { + f := NewFile() + f.tempFiles.Store("/d/", "/d/") + require.Error(t, f.Close()) +} @@ -18,14 +18,15 @@ import ( "encoding/xml" "fmt" "io" + "io/ioutil" + "os" "regexp" "strconv" "strings" ) -// ReadZipReader can be used to read the spreadsheet in memory without touching the -// filesystem. -func ReadZipReader(r *zip.Reader, o *Options) (map[string][]byte, int, error) { +// ReadZipReader extract spreadsheet with given options. +func (f *File) ReadZipReader(r *zip.Reader) (map[string][]byte, int, error) { var ( err error docPart = map[string]string{ @@ -37,25 +38,49 @@ func ReadZipReader(r *zip.Reader, o *Options) (map[string][]byte, int, error) { unzipSize int64 ) for _, v := range r.File { - unzipSize += v.FileInfo().Size() - if unzipSize > o.UnzipSizeLimit { - return fileList, worksheets, newUnzipSizeLimitError(o.UnzipSizeLimit) + fileSize := v.FileInfo().Size() + unzipSize += fileSize + if unzipSize > f.options.UnzipSizeLimit { + return fileList, worksheets, newUnzipSizeLimitError(f.options.UnzipSizeLimit) } fileName := strings.Replace(v.Name, "\\", "/", -1) if partName, ok := docPart[strings.ToLower(fileName)]; ok { fileName = partName } - if fileList[fileName], err = readFile(v); err != nil { - return nil, 0, err - } if strings.HasPrefix(fileName, "xl/worksheets/sheet") { worksheets++ + if fileSize > f.options.WorksheetUnzipMemLimit && !v.FileInfo().IsDir() { + if tempFile, err := f.unzipToTemp(v); err == nil { + f.tempFiles.Store(fileName, tempFile) + continue + } + } + } + if fileList[fileName], err = readFile(v); err != nil { + return nil, 0, err } } return fileList, worksheets, nil } -// readXML provides a function to read XML content as string. +// unzipToTemp unzip the zip entity to the system temporary directory and +// returned the unzipped file path. +func (f *File) unzipToTemp(zipFile *zip.File) (string, error) { + tmp, err := ioutil.TempFile(os.TempDir(), "excelize-") + if err != nil { + return "", err + } + rc, err := zipFile.Open() + if err != nil { + return tmp.Name(), err + } + _, err = io.Copy(tmp, rc) + rc.Close() + tmp.Close() + return tmp.Name(), err +} + +// readXML provides a function to read XML content as bytes. func (f *File) readXML(name string) []byte { if content, _ := f.Pkg.Load(name); content != nil { return content.([]byte) @@ -66,6 +91,32 @@ func (f *File) readXML(name string) []byte { return []byte{} } +// readBytes read file as bytes by given path. +func (f *File) readBytes(name string) []byte { + content := f.readXML(name) + if len(content) != 0 { + return content + } + file, err := f.readTemp(name) + if err != nil { + return content + } + content, _ = ioutil.ReadAll(file) + f.Pkg.Store(name, content) + file.Close() + return content +} + +// readTemp read file from system temporary directory by given path. +func (f *File) readTemp(name string) (file *os.File, err error) { + path, ok := f.tempFiles.Load(name) + if !ok { + return + } + file, err = os.Open(path.(string)) + return +} + // saveFileList provides a function to update given file content in file list // of spreadsheet. func (f *File) saveFileList(name string, content []byte) { diff --git a/lib_test.go b/lib_test.go index 556ed91..84a52bb 100644 --- a/lib_test.go +++ b/lib_test.go @@ -1,13 +1,18 @@ package excelize import ( + "archive/zip" + "bytes" "encoding/xml" "fmt" + "os" "strconv" "strings" + "sync" "testing" "github.com/stretchr/testify/assert" + "github.com/stretchr/testify/require" ) var validColumns = []struct { @@ -296,3 +301,41 @@ func TestBstrMarshal(t *testing.T) { assert.Equal(t, expected, bstrMarshal(bstr)) } } + +func TestReadBytes(t *testing.T) { + f := &File{tempFiles: sync.Map{}} + sheet := "xl/worksheets/sheet1.xml" + f.tempFiles.Store(sheet, "/d/") + assert.Equal(t, []byte{}, f.readBytes(sheet)) +} + +func TestUnzipToTemp(t *testing.T) { + os.Setenv("TMPDIR", "test") + defer os.Unsetenv("TMPDIR") + assert.NoError(t, os.Chmod(os.TempDir(), 0444)) + f := NewFile() + data := []byte("PK\x03\x040000000PK\x01\x0200000" + + "0000000000000000000\x00" + + "\x00\x00\x00\x00\x00000000000000PK\x01" + + "\x020000000000000000000" + + "00000\v\x00\x00\x00\x00\x00000000000" + + "00000000000000PK\x01\x0200" + + "00000000000000000000" + + "00\v\x00\x00\x00\x00\x00000000000000" + + "00000000000PK\x01\x020000<" + + "0\x00\x0000000000000000\v\x00\v" + + "\x00\x00\x00\x00\x0000000000\x00\x00\x00\x00000" + + "00000000PK\x01\x0200000000" + + "0000000000000000\v\x00\x00\x00" + + "\x00\x0000PK\x05\x06000000\x05\x000000" + + "\v\x00\x00\x00\x00\x00") + z, err := zip.NewReader(bytes.NewReader(data), int64(len(data))) + assert.NoError(t, err) + + _, err = f.unzipToTemp(z.File[0]) + require.Error(t, err) + assert.NoError(t, os.Chmod(os.TempDir(), 0755)) + + _, err = f.unzipToTemp(z.File[0]) + assert.EqualError(t, err, "EOF") +} diff --git a/merge_test.go b/merge_test.go index 88fe4f9..8d9ad41 100644 --- a/merge_test.go +++ b/merge_test.go @@ -68,6 +68,7 @@ func TestMergeCell(t *testing.T) { assert.EqualError(t, f.MergeCell("SheetN", "N10", "O11"), "sheet SheetN is not exist") assert.NoError(t, f.SaveAs(filepath.Join("test", "TestMergeCell.xlsx"))) + assert.NoError(t, f.Close()) f = NewFile() assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3")) @@ -93,6 +94,7 @@ func TestMergeCellOverlap(t *testing.T) { assert.Equal(t, "A1", mc[0].GetStartAxis()) assert.Equal(t, "D3", mc[0].GetEndAxis()) assert.Equal(t, "", mc[0].GetCellValue()) + assert.NoError(t, f.Close()) } func TestGetMergeCells(t *testing.T) { @@ -139,6 +141,7 @@ func TestGetMergeCells(t *testing.T) { // Test get merged cells on not exists worksheet. _, err = f.GetMergeCells("SheetN") assert.EqualError(t, err, "sheet SheetN is not exist") + assert.NoError(t, f.Close()) } func TestUnmergeCell(t *testing.T) { @@ -162,6 +165,7 @@ func TestUnmergeCell(t *testing.T) { } assert.NoError(t, f.SaveAs(filepath.Join("test", "TestUnmergeCell.xlsx"))) + assert.NoError(t, f.Close()) f = NewFile() assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3")) @@ -497,6 +497,9 @@ func (f *File) getSheetRelationshipsTargetByID(sheet, rID string) string { // if err := ioutil.WriteFile(file, raw, 0644); err != nil { // fmt.Println(err) // } +// if err = f.Close(); err != nil { +// fmt.Println(err) +// } // func (f *File) GetPicture(sheet, cell string) (string, []byte, error) { col, row, err := CellNameToCoordinates(cell) diff --git a/picture_test.go b/picture_test.go index 3e12f5f..2927976 100644 --- a/picture_test.go +++ b/picture_test.go @@ -68,6 +68,7 @@ func TestAddPicture(t *testing.T) { // Test write file to given path. assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddPicture.xlsx"))) + assert.NoError(t, f.Close()) } func TestAddPictureErrors(t *testing.T) { @@ -90,6 +91,7 @@ func TestAddPictureErrors(t *testing.T) { // Test add picture to worksheet with invalid file data. err = f.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", ".jpg", make([]byte, 1)) assert.EqualError(t, err, "image: unknown format") + assert.NoError(t, f.Close()) } func TestGetPicture(t *testing.T) { @@ -137,7 +139,6 @@ func TestGetPicture(t *testing.T) { assert.NoError(t, err) if !assert.NotEmpty(t, filepath.Join("test", file)) || !assert.NotEmpty(t, raw) || !assert.NoError(t, ioutil.WriteFile(filepath.Join("test", file), raw, 0644)) { - t.FailNow() } @@ -146,6 +147,7 @@ func TestGetPicture(t *testing.T) { assert.NoError(t, err) assert.Empty(t, file) assert.Empty(t, raw) + assert.NoError(t, f.Close()) // Test get picture from none drawing worksheet. f = NewFile() @@ -194,6 +196,7 @@ func TestDeletePicture(t *testing.T) { assert.EqualError(t, f.DeletePicture("SheetN", "A1"), "sheet SheetN is not exist") // Test delete picture with invalid coordinates. assert.EqualError(t, f.DeletePicture("Sheet1", ""), `cannot convert cell "" to coordinates: invalid cell name ""`) + assert.NoError(t, f.Close()) // Test delete picture on no chart worksheet. assert.NoError(t, NewFile().DeletePicture("Sheet1", "A1")) } @@ -18,6 +18,7 @@ import ( "io" "log" "math" + "os" "strconv" "github.com/mohae/deepcopy" @@ -60,7 +61,7 @@ func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error) { max = cur } } - return results[:max], nil + return results[:max], rows.Close() } // Rows defines an iterator to a sheet. @@ -70,6 +71,7 @@ type Rows struct { rawCellValue bool sheet string f *File + tempFile *os.File decoder *xml.Decoder } @@ -84,6 +86,15 @@ func (rows *Rows) Error() error { return rows.err } +// Close closes the open worksheet XML file in the system temporary +// directory. +func (rows *Rows) Close() error { + if rows.tempFile != nil { + return rows.tempFile.Close() + } + return nil +} + // Columns return the current row's column values. func (rows *Rows) Columns(opts ...Options) ([]string, error) { var rowIterator rowXMLIterator @@ -196,6 +207,9 @@ func rowXMLHandler(rowIterator *rowXMLIterator, xmlElement *xml.StartElement, ra // } // fmt.Println() // } +// if err = rows.Close(); err != nil { +// fmt.Println(err) +// } // func (f *File) Rows(sheet string) (*Rows, error) { name, ok := f.sheetMap[trimSheetName(sheet)] @@ -215,8 +229,13 @@ func (f *File) Rows(sheet string) (*Rows, error) { inElement string row int rows Rows + needClose bool + decoder *xml.Decoder + tempFile *os.File ) - decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) + if needClose, decoder, tempFile, err = f.sheetDecoder(name); needClose && err == nil { + defer tempFile.Close() + } for { token, _ := decoder.Token() if token == nil { @@ -241,15 +260,29 @@ func (f *File) Rows(sheet string) (*Rows, error) { if xmlElement.Name.Local == "sheetData" { rows.f = f rows.sheet = name - rows.decoder = f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) - return &rows, nil + _, rows.decoder, rows.tempFile, err = f.sheetDecoder(name) + return &rows, err } - default: } } return &rows, nil } +// sheetDecoder creates XML decoder by given path in the zip from memory data +// or system temporary file. +func (f *File) sheetDecoder(name string) (bool, *xml.Decoder, *os.File, error) { + var ( + content []byte + err error + tempFile *os.File + ) + if content = f.readXML(name); len(content) > 0 { + return false, f.xmlNewDecoder(bytes.NewReader(content)), tempFile, err + } + tempFile, err = f.readTemp(name) + return true, f.xmlNewDecoder(tempFile), tempFile, err +} + // SetRowHeight provides a function to set the height of a single row. For // example, set the height of the first row in Sheet1: // diff --git a/rows_test.go b/rows_test.go index c0dc1d8..0ebe59d 100644 --- a/rows_test.go +++ b/rows_test.go @@ -32,6 +32,7 @@ func TestRows(t *testing.T) { if !assert.NoError(t, rows.Error()) { t.FailNow() } + assert.NoError(t, rows.Close()) returnedRows, err := f.GetRows(sheet2) assert.NoError(t, err) @@ -41,6 +42,7 @@ func TestRows(t *testing.T) { if !assert.Equal(t, collectedRows, returnedRows) { t.FailNow() } + assert.NoError(t, f.Close()) f = NewFile() f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<worksheet><sheetData><row r="1"><c r="A1" t="s"><v>1</v></c></row><row r="A"><c r="2" t="str"><v>B</v></c></row></sheetData></worksheet>`)) @@ -52,6 +54,14 @@ func TestRows(t *testing.T) { f.Pkg.Store("xl/worksheets/sheet1.xml", nil) _, err = f.Rows("Sheet1") assert.NoError(t, err) + + // Test reload the file to memory from system temporary directory. + f, err = OpenFile(filepath.Join("test", "Book1.xlsx"), Options{WorksheetUnzipMemLimit: 1024}) + assert.NoError(t, err) + value, err := f.GetCellValue("Sheet1", "A19") + assert.NoError(t, err) + assert.Equal(t, "Total:", value) + assert.NoError(t, f.Close()) } func TestRowsIterator(t *testing.T) { @@ -70,6 +80,8 @@ func TestRowsIterator(t *testing.T) { require.True(t, rowCount <= expectedNumRow, "rowCount is greater than expected") } assert.Equal(t, expectedNumRow, rowCount) + assert.NoError(t, rows.Close()) + assert.NoError(t, f.Close()) // Valued cell sparse distribution test f = NewFile() @@ -94,6 +106,7 @@ func TestRowsError(t *testing.T) { } _, err = f.Rows("SheetN") assert.EqualError(t, err, "sheet SheetN is not exist") + assert.NoError(t, f.Close()) } func TestRowHeight(t *testing.T) { @@ -871,6 +884,11 @@ func TestGetValueFromNumber(t *testing.T) { } } +func TestRoundPrecision(t *testing.T) { + _, err := roundPrecision("") + assert.EqualError(t, err, "strconv.ParseFloat: parsing \"\": invalid syntax") +} + func TestErrSheetNotExistError(t *testing.T) { err := ErrSheetNotExist{SheetName: "Sheet1"} assert.EqualValues(t, err.Error(), "sheet Sheet1 is not exist") @@ -920,6 +938,7 @@ func TestNumberFormats(t *testing.T) { cells = append(cells, col) } assert.Equal(t, []string{"", "200", "450", "200", "510", "315", "127", "89", "348", "53", "37"}, cells[3]) + assert.NoError(t, f.Close()) } func BenchmarkRows(b *testing.B) { @@ -934,6 +953,12 @@ func BenchmarkRows(b *testing.B) { } } } + if err := rows.Close(); err != nil { + b.Error(err) + } + } + if err := f.Close(); err != nil { + b.Error(err) } } @@ -419,6 +419,9 @@ func (f *File) GetSheetIndex(name string) int { // for index, name := range f.GetSheetMap() { // fmt.Println(index, name) // } +// if err = f.Close(); err != nil { +// fmt.Println(err) +// } // func (f *File) GetSheetMap() map[int]string { wb := f.workbookReader() @@ -462,6 +465,9 @@ func (f *File) getSheetMap() map[string]string { if _, ok := f.Pkg.Load(path); ok { maps[v.Name] = path } + if _, ok := f.tempFiles.Load(path); ok { + maps[v.Name] = path + } } } } @@ -858,7 +864,7 @@ func (f *File) searchSheet(name, value string, regSearch bool) (result []string, ) d = f.sharedStringsReader() - decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) + decoder := f.xmlNewDecoder(bytes.NewReader(f.readBytes(name))) for { var token xml.Token token, err = decoder.Token() diff --git a/sheet_test.go b/sheet_test.go index 0a604de..ef32d79 100644 --- a/sheet_test.go +++ b/sheet_test.go @@ -189,6 +189,7 @@ func TestSearchSheet(t *testing.T) { result, err = f.SearchSheet("Sheet1", "[0-9]", true) assert.NoError(t, err) assert.EqualValues(t, expected, result) + assert.NoError(t, f.Close()) // Test search worksheet data after set cell value f = NewFile() @@ -327,6 +328,7 @@ func TestGetSheetName(t *testing.T) { assert.Equal(t, "Sheet2", f.GetSheetName(1)) assert.Equal(t, "", f.GetSheetName(-1)) assert.Equal(t, "", f.GetSheetName(2)) + assert.NoError(t, f.Close()) } func TestGetSheetMap(t *testing.T) { @@ -341,6 +343,7 @@ func TestGetSheetMap(t *testing.T) { assert.Equal(t, expectedMap[idx], name) } assert.Equal(t, len(sheetMap), 2) + assert.NoError(t, f.Close()) } func TestSetActiveSheet(t *testing.T) { diff --git a/stream_test.go b/stream_test.go index fda44fb..6cfed07 100644 --- a/stream_test.go +++ b/stream_test.go @@ -115,6 +115,21 @@ func TestStreamWriter(t *testing.T) { cellValue, err := file.GetCellValue("Sheet1", "A1") assert.NoError(t, err) assert.Equal(t, "Data", cellValue) + + // Test stream reader for a worksheet with huge amounts of data. + file, err = OpenFile(filepath.Join("test", "TestStreamWriter.xlsx")) + assert.NoError(t, err) + rows, err := file.Rows("Sheet1") + assert.NoError(t, err) + cells := 0 + for rows.Next() { + row, err := rows.Columns() + assert.NoError(t, err) + cells += len(row) + } + assert.NoError(t, rows.Close()) + assert.Equal(t, 2559558, cells) + assert.NoError(t, file.Close()) } func TestStreamSetColWidth(t *testing.T) { |