Golang导出并下载excel封装(基于Excelize)

Golang导出并下载excel封装

博客地址

这里使用的是: github.com/xuri/excelize/v2

封装了两个方法,数据源为map和struct(map数据源表头和数据需要自己排序)

// ExportExcelByMap 导出excel 数据源为[]map
func ExportExcelByMap(c *gin.Context, titleList []string, data []map[string]interface{}, fileName, sheetName string) error {f := excelize.NewFile()f.SetSheetName("Sheet1", sheetName)header := make([]string, 0)for _, v := range titleList {header = append(header, v)}//表格样式rowStyleID, _ := f.NewStyle(`{"font":{"color":"#666666","size":13,"family":"arial"},"alignment":{"vertical":"center","horizontal":"center"}}`)_ = f.SetSheetRow(sheetName, "A1", &header)_ = f.SetRowHeight(sheetName, 1, 30)length := len(titleList)headStyle := Letter(length)var lastRow stringvar widthRow stringfor k, v := range headStyle {if k == length-1 {lastRow = fmt.Sprintf("%s1", v)widthRow = v}}if err := f.SetColWidth(sheetName, "A", widthRow, 30); err != nil {logger.Logdd.Error(err)}rowNum := 1for _, value := range data {row := make([]interface{}, 0)var dataSlice []stringfor key := range value {dataSlice = append(dataSlice, key)}sort.Strings(dataSlice)for _, v := range dataSlice {if val, ok := value[v]; ok {row = append(row, val)}}rowNum++if err := f.SetSheetRow(sheetName, fmt.Sprintf("A%d", rowNum), &row); err != nil {logger.Logdd.Error(err)}if err := f.SetCellStyle(sheetName, fmt.Sprintf("A%d", rowNum), fmt.Sprintf("%s", lastRow), rowStyleID); err != nil {logger.Logdd.Error(err)}}disposition := fmt.Sprintf("attachment; filename=%s-%s.xlsx", url.QueryEscape(fileName), time.Now().Format(constant.FormatTime))c.Writer.Header().Set("Content-Type", "application/octet-stream")c.Writer.Header().Set("Content-Disposition", disposition)c.Writer.Header().Set("Content-Transfer-Encoding", "binary")c.Writer.Header().Set("Access-Control-Expose-Headers", "Content-Disposition")return f.Write(c.Writer)
}// ExportExcelByStruct excel导出(数据源为Struct)
func ExportExcelByStruct(c *gin.Context, titleList []string, data []interface{}, fileName string, sheetName string) error {f := excelize.NewFile()f.SetSheetName("Sheet1", sheetName)header := make([]string, 0)for _, v := range titleList {header = append(header, v)}rowStyleID, _ := f.NewStyle(`{"font":{"color":"#666666","size":13,"family":"arial"},"alignment":{"vertical":"center","horizontal":"center"}}`)_ = f.SetSheetRow(sheetName, "A1", &header)_ = f.SetRowHeight("Sheet1", 1, 30)length := len(titleList)headStyle := Letter(length)var lastRow stringvar widthRow stringfor k, v := range headStyle {if k == length-1 {lastRow = fmt.Sprintf("%s1", v)widthRow = v}}if err := f.SetColWidth(sheetName, "A", widthRow, 30); err != nil {logger.Logdd.Error(err)}rowNum := 1for _, v := range data {t := reflect.TypeOf(v)value := reflect.ValueOf(v)row := make([]interface{}, 0)for l := 0; l < t.NumField(); l++ {val := value.Field(l).Interface()row = append(row, val)}rowNum++err := f.SetSheetRow(sheetName, "A"+strconv.Itoa(rowNum), &row)_ = f.SetCellStyle(sheetName, fmt.Sprintf("A%d", rowNum), fmt.Sprintf("%s", lastRow), rowStyleID)if err != nil {return err}}disposition := fmt.Sprintf("attachment; filename=%s-%s.xlsx", url.QueryEscape(fileName), time.Now().Format(constant.FormatTime))c.Writer.Header().Set("Content-Type", "application/octet-stream")c.Writer.Header().Set("Content-Disposition", disposition)c.Writer.Header().Set("Content-Transfer-Encoding", "binary")c.Writer.Header().Set("Access-Control-Expose-Headers", "Content-Disposition")return f.Write(c.Writer)
}

其他方法

// Letter 遍历a-z
func Letter(length int) []string {var str []stringfor i := 0; i < length; i++ {str = append(str, string(rune('A'+i)))}return str
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！