demo.xlsx

SetHyperlink in the first sheet works fine, but not fine in the second sheet.

Open with MS Office 2016:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
	<logFileName>error208200_01.xml</logFileName>
	<summary>在文件“C:\msys64\home\ofunc_7kwo01e\go\src\test\xlsx\demo.xlsx”中检测到错误</summary>
	<additionalInfo>
		<info>Excel 已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。</info>
	</additionalInfo>
	<repairedParts>
		<repairedPart>已修复的部件: 部件 /xl/worksheets/sheet2.xml。</repairedPart>
	</repairedParts>
</recoveryLog>

package main

import (
	"log"

	"github.com/plandem/xlsx"
)

func main() {
	xl, err := xlsx.Open("test.xlsx")
	if err != nil {
		log.Fatal(err)
	}
	defer xl.Close()

	// NOTE If uncomment the code, some conditional format information will lost.
	for cols := xl.Sheet(0).Cols(); cols.HasNext(); {
		cols.Next()
	}

	//	xl.Save() ERROR: Access is denied.
	xl.SaveAs("out.xlsx")
}

test.xlsx out.xlsx

While working on https://github.com/plandem/xlsx/issues/37, I tried to open a truncated version of an example spreadsheet in "not streaming" mode to make my modifications.

Here is the original sheet:

mfi101119holdingslist_100_noissuer.xlsx

The goal is to lookup matches for the "issuer" column in a database of previous entries and select the best match.

To this end I iterate though the sheet using row = rows.Next() collect some fields to use in a database query, then use

cell := match.Row.Cell(mi.IColIssuer)
// everything output by this diagnostic is reasonble
fmt.Println("I", match.Row.Bounds(), mi.IColIssuer, match.Issuer)
cell.SetText(match.Issuer)

to write the data (match.Issuer is of type string). Finally I save the data; it doesn't seem to be documented whether to call save before or after sheet.Close() so I tried both ways:

sheet.Close()
xl.SaveAs("./foo.xlsx") // also tried this first
xl.Close()

The resulting file is corrupt: foo.xlsx

when my project using govendor to manage golang packages, xlsx2go package cannot compile correctly, the "go:linkname" compile have error, the error belows: (can you remove the go:linkname in the package?)

liushun.lucien/own_study/vendor/github.com/plandem/xlsx/format/conditional.(*Info).Validate: relocation target github.com/plandem/xlsx/format/conditional/rule.fromRule not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx/format/conditional.AddRule.func1: relocation target github.com/plandem/xlsx/format/conditional/rule.fromRule not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*conditionals).Add: relocation target github.com/plandem/xlsx/format/conditional.from not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*hyperlinks).Add: relocation target github.com/plandem/xlsx/types/hyperlink.from not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.toRichText: relocation target github.com/plandem/xlsx/format/styles.toRichFont not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*styleSheet).addDiffStyle: relocation target github.com/plandem/xlsx/format/styles.from not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*styleSheet).addStyle: relocation target github.com/plandem/xlsx/format/styles.from not defined

Hi,

Given the following example: https://play.golang.org/p/G6QwfVo8CFb which instantiates a new doc, with a blank new sheet, my expectation when invoking InsertRow twice with index 0 was the first two rows being populated with the text.

It appears that the cell pointers are still somehow pegged to the old row: the text appears on the 3rd row.

Please advise if the approach is incorrect, and another approach is necessary when creating documents from scratch (maybe an example in the readme).

Cheers, silviu

I run my script

package main

import (
    "fmt"
    "github.com/plandem/xlsx"
)

func main() {
    xl, err := xlsx.Open("goserver/mser/go/Аналитика по МКД_clean.xlsx")
    if err != nil {
        panic(err)
    }
    defer xl.Close()
    fmt.Println(xl.GetSheetNames())
    fmt.Println(xl.Sheet(0))
}

And get an error:

user@c107 ~ $ go run goserver/mser/go/test4.go 
[МКД Организации conf]
panic: runtime error: index out of range

goroutine 1 [running]:
github.com/plandem/xlsx.(*sheetReadWrite).expandOnInit(0xc42000c0f8)
	/home/user/goserver/src/github.com/plandem/xlsx/sheet_readwrite.go:293 +0x601
github.com/plandem/xlsx.(*sheetReadWrite).(github.com/plandem/xlsx.expandOnInit)-fm()
	/home/user/goserver/src/github.com/plandem/xlsx/sheet_readwrite.go:394 +0x2a
github.com/plandem/ooxml.(*PackageFile).LoadIfRequired(0xc4200846e0, 0xc420111eb8)
	/home/user/goserver/src/github.com/plandem/ooxml/package_file.go:88 +0x9a
github.com/plandem/xlsx.(*sheetReadWrite).afterOpen(0xc42000c0f8)
	/home/user/goserver/src/github.com/plandem/xlsx/sheet_readwrite.go:394 +0x54
github.com/plandem/xlsx.(*Spreadsheet).Sheet(0xc420084370, 0x0, 0x1, 0x25)
	/home/user/goserver/src/github.com/plandem/xlsx/spreadsheet.go:63 +0xad
main.main()
	/home/user/goserver/mser/go/test4.go:17 +0x11e
exit status 2

What's wrong with my file?

360EntSecGroup-Skylar/excelize library reads this file correctly.

Description:

I am still new to go and perhaps this is not an issue but more of a lack of understanding on my part.

However, I am unable to use vars (type string) as type types.Ref in argument to sheet.Range when trying to access range by references in A1notation

Steps to reproduce the issue:

Working code

This current code works well if range references are known and can be written into the script:

package main

import (
  "fmt"
  "strings"

  "github.com/plandem/xlsx"
)

// declare document
const doc string = "./test_files/example_simple.xlsx"

func main() {
  xl, err := xlsx.Open(doc)
  if err != nil {
    panic(err)
  }
  defer xl.Close()

  // Get sheet by 0-based index
  sheet := xl.Sheet(0)

  // // Get range by references
  area := sheet.Range("D10:H13")
  fmt.Println(strings.Join(area.Values(), ","))
}

Broken code

This can allow for changes to the range since the refs are vars but does not work:

package main

import (
  "fmt"
  "strings"

  "github.com/plandem/xlsx"
)

// declare document
const doc string = "./test_files/example_simple.xlsx"

func main() {
  xl, err := xlsx.Open(doc)
  if err != nil {
    panic(err)
  }
  defer xl.Close()

  // Get sheet by 0-based index
  sheet := xl.Sheet(0)

  // // Get range by vars
  var rangeStart string = "D10"
  var rangeEnd string = "H13"
  area := sheet.Range(rangeStart + ":" + rangeEnd)
  fmt.Println(strings.Join(area.Values(), ","))
}

Error from broken code:

.\read-range.go:44:40: cannot use rangeStart + ":" + rangeEnd (type string) as type types.Ref in argument to sheet.Range

Expected results:

It was expected that both the working code and the broken code would return the same results, however they do not.

Debug Info

Output of go version:

go version go1.11.1 windows/amd64

xlsx commit id

529e812

Open a file which has several conditional formats, and then save as a new file. The new file only has the last conditional format, the others conditional formats have been lost.

Argument of type '{ header: number; }' is not assignable to parameter of type 'Sheet2CSVOpts'. Object literal may only specify known properties, and 'header' does not exist in type 'Sheet2CSVOpts'.

const data = XLSX.utils.sheet_to_csv(ws,{ header: 1 });

I would like to do something like:

var flags xlsx.SheetMode 
if !save {
  flags = xlsx.SheetModeStream
}
...
[perhaps elsewhere...]
...
sheet := xl.Sheet(0, flags)

But:

• sheetMode can't be referenced
• nice to have a name for the "default mode" (SheetModeUnknown?) available.

Of course there are other ways to do this, but it would seem a trivial change to the package that would allow more idiomatic code.

Hi, man, this xlsx libary is very useful for me to read big excel files, but there have a pity the file can only save by fileName, why not provide a method to save as []byte or to io.Writer.

Currently, the number.Format method doesn't take into consideration the formatting code. This diff implements formatting for dates based on the code. The implementation doesn't look at the cell data type because in cases of date it's not being populated by Excel, so it's always empty.

Hi,

I'm trying to use the library to process very large files and they contain date columns. When reading the values, I'm always getting numbers (the underlying numeric value for the date). Since I don't know in advance what's the data type for a particular column, I'd like to be able to identify it from the formatting.

I tried using cell.Type() == types.CellTypeDate() to decide whether I could explicitly get the value parsed to Time (with the Date function) but it's always returning false. I then tried to get the style ID and use the style sheet to get the info and check whether it's a date, but then I saw that this method is not implemented. https://github.com/plandem/xlsx/blob/4538b54c4a21aba8c7661e299444f60090bfcf43/style_sheet.go#L214-L227

Is there a way I can identify dates right now? If not, can you point me to how I can solve this issue so I can send a pull request?

Thanks, Demontiê

Hi, I am trying to writing file with a lot of rows (from 200k to 1m) using streaming mode.

Writing cell with style is ok but when I start set width for column, it return panic with message not supported.

I have read the docs and can't find it. Is there anyway I can keep using stream writing and set column width?

Thank you.

p/s: is there an option to freeze top row or first column?

17 is the original number of columns the xlsx file has.

xl, err := xlsx.Open('my_file.xslx)
...

sheet := xl.Sheet(0)
...

colCount,_ := sheet.Dimension()
fmt.Println(colCount) // 17
...

sheet.InsertCol(17).Cell(0)
xl.Save()
fmt.Println(sheet.Dimension()) // 18, 132
...

for rows := sheet.Rows(); rows.HasNext(); {
		iRow, row := rows.Next()
                row.Cell(17).SetValue("testing123") // panic: runtime error: index out of range [17] with length 17
}

in sheet_readwrite.go I have tested the following:

func (s *sheetReadWrite) Cell(colIndex, rowIndex int) *Cell {
	s.expandIfRequired(colIndex, rowIndex)

	colIndex, rowIndex, _ = s.mergedCells.Resolve(colIndex, rowIndex)
	fmt.Println("LENGTH:", len(s.ml.SheetData[rowIndex].Cells)) // prints 17 inconsistently
	data := s.ml.SheetData[rowIndex].Cells[colIndex]

	//if there is no any data for this cell, then create it
	if data == nil {
		data = &ml.Cell{
			Ref: types.CellRefFromIndexes(colIndex, rowIndex),
		}

		s.ml.SheetData[rowIndex].Cells[colIndex] = data
	}

	return &Cell{ml: data, sheet: s.sheetInfo}
}

Hello! Modify an existing xlsx file and save it. However, the calculation results of related formulas are not updated. There is a simple solution: clear calcId in xl/workbook.xml. It is recommended to provide an method of Spreadsheet for doing this.