// 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
}
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)
}
}
// 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
}
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))
}
}