// go mod init example && go get github.com/xuri/excelize/v2
package main

import (
	"fmt"
	"math/rand"
	"runtime"
	"strconv"
	"time"

	"github.com/xuri/excelize/v2"
)

func main() {
	start := time.Now()
	f := excelize.NewFile()
	defer f.Close()

	const sheet = "Sheet1"
	sw, err := f.NewStreamWriter(sheet)
	must(err)

	// 寫(xiě)表頭
	header := []interface{}{"ID", "Name", "Amount", "CreatedAt"}
	cell, _ := excelize.CoordinatesToCellName(1, 1)
	must(sw.SetRow(cell, header))

	// 模擬寫(xiě) 1,000,000 行
	n := 1_000_000
	for i := 1; i <= n; i++ {
		row := []interface{}{
			i,
			"User_" + strconv.Itoa(i),
			rand.Intn(10_000),
			time.Now().Add(time.Duration(i) * time.Second).Format(time.RFC3339),
		}
		cell, _ := excelize.CoordinatesToCellName(1, i+1)
		must(sw.SetRow(cell, row))

		// 每 50k 行打印一次內(nèi)存占用
		if i%50_000 == 0 {
			var m runtime.MemStats
			runtime.ReadMemStats(&m)
			fmt.Printf("[progress] rows=%d heap=%.2fMB\n", i, float64(m.HeapAlloc)/1024.0/1024.0)
		}
	}

	must(sw.Flush())

	// 可選：凍結(jié)首行 + 自動(dòng)列寬（注意：自動(dòng)列寬對(duì)流式無(wú)感，需在 Flush 后做固定寬度）
	// 凍結(jié)首行
	must(f.SetPanes(sheet, &excelize.Panes{
		Freeze:      true,
		YSplit:      1,
		ActivePane:  "bottomLeft",
		TopLeftCell: "A2",
	}))

	// 保存到磁盤(pán)（也可改為寫(xiě)到 HTTP ResponseWriter，見(jiàn)后文）
	must(f.SaveAs("bigdata.xlsx"))

	fmt.Printf("done in %v\n", time.Since(start))
}

func must(err error) {
	if err != nil {
		panic(err)
	}
}

package main

import (
	"encoding/csv"
	"fmt"
	"os"
	"strconv"
)

func main() {
	file, err := os.Create("bigdata.csv")
	if err != nil {
		panic(err)
	}
	defer file.Close()

	w := csv.NewWriter(file)
	defer w.Flush()

	// 表頭
	_ = w.Write([]string{"ID", "Name", "Age"})

	const n = 1_000_000
	for i := 1; i <= n; i++ {
		_ = w.Write([]string{
			strconv.Itoa(i),
			"User_" + strconv.Itoa(i),
			strconv.Itoa(18 + i%30),
		})
		if i%100_000 == 0 {
			fmt.Println("rows:", i)
			w.Flush() // 顯式?jīng)_刷，降低緩沖占用
		}
	}
}

package main

import (
	"log"
	"net/http"
	"strconv"
	"time"

	"github.com/xuri/excelize/v2"
)

func main() {
	http.HandleFunc("/export/xlsx", exportXLSX)
	http.HandleFunc("/export/csv", exportCSV)
	log.Println("listen :8080")
	log.Fatal(http.ListenAndServe(":8080", nil))
}

func exportXLSX(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
	w.Header().Set("Content-Disposition", "attachment; filename=\"report.xlsx\"")

	f := excelize.NewFile()
	defer f.Close()

	sw, err := f.NewStreamWriter("Sheet1")
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}
	_ = sw.SetRow("A1", []interface{}{"ID", "Name", "Amount", "CreatedAt"})

	n := 200_000 // 從 query 或業(yè)務(wù)層拿
	for i := 1; i <= n; i++ {
		cell, _ := excelize.CoordinatesToCellName(1, i+1)
		_ = sw.SetRow(cell, []interface{}{
			i,
			"User_" + strconv.Itoa(i),
			i % 1000,
			time.Now().Format(time.RFC3339),
		})
	}
	_ = sw.Flush()

	// 直接寫(xiě)到 ResponseWriter
	if err := f.Write(w); err != nil {
		// 客戶端中斷下載常見(jiàn)：write: broken pipe
		log.Println("write err:", err)
	}
}

func exportCSV(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "text/csv; charset=utf-8")
	w.Header().Set("Content-Disposition", "attachment; filename=\"report.csv\"")

	cw := NewCSVStream(w)
	defer cw.Flush()

	_ = cw.Write([]string{"ID", "Name", "Age"})
	for i := 1; i <= 500_000; i++ {
		_ = cw.Write([]string{
			strconv.Itoa(i),
			"User_" + strconv.Itoa(i),
			strconv.Itoa(18 + i%30),
		})
	}
}

// 輕薄包裝：將 http.ResponseWriter 適配成 csv.Writer
type CSVStream struct{ w *csv.Writer }

func NewCSVStream(w http.ResponseWriter) *CSVStream { return &CSVStream{w: csv.NewWriter(w)} }
func (c *CSVStream) Write(rec []string) error       { return c.w.Write(rec) }
func (c *CSVStream) Flush()                         { c.w.Flush() }

package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	_ "modernc.org/sqlite" // 為了示例，用 SQLite；生產(chǎn)用 MySQL/PostgreSQL 驅(qū)動(dòng)
)

type Row struct {
	ID        int64
	Name      string
	Amount    int64
	CreatedAt time.Time
}

func StreamRows(ctx context.Context, db *sql.DB, pageSize int, out chan<- Row) error {
	defer close(out)

	offset := 0
	for {
		// 替換為你的 SQL（MySQL 推薦 keyset/游標(biāo)翻頁(yè)）
		rows, err := db.QueryContext(ctx,
			`SELECT id, name, amount, created_at FROM t ORDER BY id LIMIT ? OFFSET ?`,
			pageSize, offset,
		)
		if err != nil {
			return err
		}
		n := 0
		for rows.Next() {
			var r Row
			if err := rows.Scan(&r.ID, &r.Name, &r.Amount, &r.CreatedAt); err != nil {
				_ = rows.Close()
				return err
			}
			select {
			case out <- r: // 下游慢時(shí)自然限速
			case <-ctx.Done():
				_ = rows.Close()
				return ctx.Err()
			}
			n++
		}
		_ = rows.Close()
		if n == 0 {
			return nil // 完成
		}
		offset += n
	}
}

func ExamplePipeline(ctx context.Context, db *sql.DB) error {
	out := make(chan Row, 1024) // 小緩沖，避免放大內(nèi)存
	go func() {
		_ = StreamRows(ctx, db, 5000, out)
	}()

	// 在這里對(duì)接 XLSX/CSV Writer（單寫(xiě)者，保持順序）
	for r := range out {
		// sw.SetRow(...)/csv.Write(...)
		_ = r
	}
	return ctx.Err()
}

func main() {
	fmt.Println("見(jiàn) ExamplePipeline；將其與前文 XLSX/CSV 寫(xiě)入代碼拼裝即可。")
}

ctx, cancel := context.WithTimeout(r.Context(), 5*time.Minute) // HTTP 請(qǐng)求 5 分鐘超時(shí)
defer cancel()

// 在所有 QueryContext/SetRow 處傳遞 ctx，遇到取消/超時(shí)立即停止

styleID, _ := f.NewStyle(&excelize.Style{Font: &excelize.Font{Bold: true}})
_ = f.SetCellStyle("Sheet1", "A1", "D1", styleID)
_ = f.SetColWidth("Sheet1", "A", "D", 18)