10-06 0 views
实现方法
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
import ( "database/sql" "encoding/csv" "fmt" "io" "os" "time" ) type Converter struct { Headers []string // 列头字段 WriteHeaders bool // 是否写入列头 TimeFormat string // 时间转换 FloatFormat string // Float类型转换 Delimiter rune // 分隔符 rows *sql.Rows // SQL查询结果 rowPreProcessor CsvPreProcessorFunc } type CsvPreProcessorFunc func(row []string, columnNames []string) (outputRow bool, processedRow []string) // WriteFile 将SQL结果写入到CSV文件 func WriteFile(fileName string, rows *sql.Rows) error { return New(rows).WriteFile(fileName) } func (c *Converter) SetRowPreProcessor(processor CsvPreProcessorFunc) { c.rowPreProcessor = processor } func (c Converter) WriteFile(csvFileName string) error { f, err := os.Create(csvFileName) if err != nil { return err } err = c.Write(f) if err != nil { f.Close() return err } return f.Close() } // Write 数据写入到CSV文件的具体实现 func (c Converter) Write(writer io.Writer) error { rows := c.rows // 新建一个Writer对象 csvWriter := csv.NewWriter(writer) // 分割符 if c.Delimiter != '\x00' { csvWriter.Comma = c.Delimiter } // 列字段 columnNames, err := rows.Columns() if err != nil { return err } // 是否需要列头 if c.WriteHeaders { var headers []string if len(c.Headers) > 0 { headers = c.Headers } else { headers = columnNames } // 写入列头 err = csvWriter.Write(headers) if err != nil { return fmt.Errorf("failed to write headers: %w", err) } } count := len(columnNames) values := make([]interface{}, count) valuePtrs := make([]interface{}, count) for rows.Next() { row := make([]string, count) for i, _ := range columnNames { valuePtrs[i] = &values[i] } if err = rows.Scan(valuePtrs...); err != nil { return err } // 遍历每个字段的类型 for i, _ := range columnNames { var value interface{} rawValue := values[i] byteArray, ok := rawValue.([]byte) if ok { value = string(byteArray) } else { value = rawValue } float64Value, ok := value.(float64) if ok && c.FloatFormat != "" { value = fmt.Sprintf(c.FloatFormat, float64Value) } else { float32Value, ok := value.(float32) if ok && c.FloatFormat != "" { value = fmt.Sprintf(c.FloatFormat, float32Value) } } timeValue, ok := value.(time.Time) if ok && c.TimeFormat != "" { value = timeValue.Format(c.TimeFormat) } if value == nil { row[i] = "" } else { row[i] = fmt.Sprintf("%v", value) } } writeRow := true if c.rowPreProcessor != nil { writeRow, row = c.rowPreProcessor(row, columnNames) } // 写入数据 if writeRow { err = csvWriter.Write(row) if err != nil { return fmt.Errorf("failed to write data row to csv %w", err) } } } err = rows.Err() // 刷到磁盘 csvWriter.Flush() return err } func New(rows *sql.Rows) *Converter { return &Converter{ rows: rows, WriteHeaders: true, Delimiter: ',', } } |
调用方法
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
csvfileName := "XXXX.csv" sql := "select * from tablename" # 初始化DB连接 Db := model.DB() // 执行sql语句 log.Printf("[%v] SQL execing...\n", csvfileName) rows, err := Db.Raw(sql).Rows() if err != nil { log.Fatal(err) } defer rows.Close() // 调用WriteFile方法，将结果写入CSV文件 log.Printf("[%v] CSV writing...\n", csvfileName) err = WriteFile(csvfileName, rows) if err != nil { log.Fatal(err) } |
如果想赏钱，可以用微信扫描下面的二维码，一来能刺激我写博客的欲望，二来好维护云主机的费用； 另外再次标注博客原地址 itnotebooks.com 感谢！
