Example #1
0
// Write a simple 1,000,000 row spreadsheet without using streaming
// This has a maximum resident set of ~240MB
func WriteNoStreaming() error {
	c := []xlsx.Column{
		xlsx.Column{Name: "Col1", Width: 10},
		xlsx.Column{Name: "Col2", Width: 10},
	}

	sh := xlsx.NewSheetWithColumns(c)
	sh.Title = "MySheet"

	for i := 0; i < 1000000; i++ {

		r := sh.NewRow()

		r.Cells[0] = xlsx.Cell{
			Type:  xlsx.CellTypeNumber,
			Value: strconv.Itoa(i + 1),
		}
		r.Cells[1] = xlsx.Cell{
			Type:  xlsx.CellTypeNumber,
			Value: "1",
		}

		sh.AppendRow(r)
	}

	err := sh.SaveToFile("test.xlsx")

	return err
}
Example #2
0
func main() {

	c := []xlsx.Column{
		xlsx.Column{Name: "Col1", Width: 10},
		xlsx.Column{Name: "Col2", Width: 10},
		xlsx.Column{Name: "Col3", Width: 10},
	}

	sh := xlsx.NewSheetWithColumns(c)
	r := sh.NewRow()

	r.Cells[0] = xlsx.Cell{
		Type:  xlsx.CellTypeNumber,
		Value: "10",
	}
	r.Cells[1] = xlsx.Cell{
		Type:  xlsx.CellTypeString,
		Value: "Apple",
	}
	r.Cells[2] = xlsx.Cell{
		Type:  xlsx.CellTypeDatetime,
		Value: time.Date(1980, 4, 24, 0, 0, 0, 0, time.UTC).Format(time.RFC3339),
	}

	sh.AppendRow(r)

	r2 := sh.NewRow()

	r2.Cells[0] = xlsx.Cell{
		Type:  xlsx.CellTypeNumber,
		Value: "10",
	}
	r2.Cells[1] = xlsx.Cell{
		Type:  xlsx.CellTypeString,
		Value: "Apple",
	}
	r2.Cells[2] = xlsx.Cell{
		Type:  xlsx.CellTypeDatetime,
		Value: time.Date(2008, 1, 9, 0, 0, 0, 0, time.UTC).Format(time.RFC3339),
	}

	sh.AppendRow(r2)

	err := sh.SaveToFile("test.xlsx")
	if err != nil {
		println(err)
	}
}
Example #3
0
// Write a simple 1,000,000 row spreadsheet using streaming
// This has a maximum resident set of ~6.6MB
func WriteStreaming() error {
	outputfile, err := os.Create("test.xlsx")

	w := bufio.NewWriter(outputfile)
	ww := xlsx.NewWorkbookWriter(w)

	c := []xlsx.Column{
		xlsx.Column{Name: "Col1", Width: 10},
		xlsx.Column{Name: "Col2", Width: 10},
	}

	sh := xlsx.NewSheetWithColumns(c)
	sh.Title = "MySheet"

	sw, err := ww.NewSheetWriter(&sh)

	for i := 0; i < 1000000; i++ {

		r := sh.NewRow()

		r.Cells[0] = xlsx.Cell{
			Type:  xlsx.CellTypeNumber,
			Value: strconv.Itoa(i + 1),
		}
		r.Cells[1] = xlsx.Cell{
			Type:  xlsx.CellTypeInlineString,
			Value: "Test",
		}

		err = sw.WriteRows([]xlsx.Row{r})
	}

	err = ww.Close()
	defer w.Flush()

	return err
}
Example #4
0
func main() {

	var err error

	flag.Usage = usage
	flag.Parse()

	if showversion {
		version()
		return
	}

	if outputfile == "" {
		exitWithError(fmt.Errorf("You must specify an output file name"))
	}

	user, err := user.Current()

	if err != nil {
		exitWithError(fmt.Errorf("unable to get current user: %s", err))
	}

	// unless specified, read the password from the ~/.PGPASS file or prompt
	var password string
	if !nopassword && username != "" {
		password, err = passwordFromPgpass(user)

		// TODO: when the SQL commands are also read from stdin perhaps display a
		// warning
		if err != nil {
			fmt.Print("Enter password: "******"unable to read password: %s", err.Error()))
			}
			password = string(b)
		}
	}

	// PG connections have useful defaults. Many of these are implemented
	// in lib/pq so we only need to pass options through where specified.

	conn := "sslmode=disable"
	// TODO: support other sslmodes

	if hostname != "" {
		conn = fmt.Sprintf("%s host=%s", conn, hostname)
	}

	if username != "" {
		conn = fmt.Sprintf("%s user=%s", conn, username)
	}

	if username != "" && password != "" {
		conn = fmt.Sprintf("%s password=%s", conn, password)
	}

	if dbname != "" {
		conn = fmt.Sprintf("%s dbname=%s", conn, dbname)
	}

	if port != "" {
		conn = fmt.Sprintf("%s port=%s", conn, port)
	}

	db, err := sql.Open("postgres", conn)
	if err != nil {
		exitWithError(fmt.Errorf("unable to connect to postgres. %s", err))
	}
	defer db.Close()

	// read query from input

	var query string

	if command != "" {
		query = command
	} else {
		var b []byte
		if filename != "" {
			b, err = ioutil.ReadFile(filename)
		} else {
			b, err = ioutil.ReadAll(os.Stdin)
		}

		if err != nil {
			exitWithError(fmt.Errorf("unable to read query: %s", err))
		}

		query = string(b)
	}

	rows, err := db.Query(query)

	if err != nil {
		exitWithError(fmt.Errorf("unable to run query: %s", err))
	}

	// Get column names
	columns, err := rows.Columns()
	if err != nil {
		exitWithError(fmt.Errorf("unable to get column names: %s", err))
	}
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))

	sheetColumns := make([]xlsx.Column, len(columns))
	for i, c := range columns {
		sheetColumns[i] = xlsx.Column{Name: c, Width: 10}
	}

	sheet := xlsx.NewSheetWithColumns(sheetColumns)

	// optionally add a row with column titles
	if columntitles {
		titleRow := sheet.NewRow()

		for i, c := range columns {
			titleRow.Cells[i] = xlsx.Cell{xlsx.CellTypeString, c}
		}
		sheet.AppendRow(titleRow)
	}

	// build the data rows
	for rows.Next() {
		sheetRow := sheet.NewRow()

		for i, _ := range columns {
			valuePtrs[i] = &values[i]
		}

		rows.Scan(valuePtrs...)

		for i, _ := range columns {
			val := values[i]
			sheetRow.Cells[i] = CellFromPostgres(val)
		}

		sheet.AppendRow(sheetRow)
	}

	err = sheet.SaveToFile(outputfile)
	if err != nil {
		exitWithError(fmt.Errorf("unable to get save xlsx sheet: %s", err))
	}
}