Golang导出并下载excel封装
博客地址
封装了两个方法,数据源为map和struct(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 string
var widthRow string
for 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 := 1
for _, value := range data {
row := make([]interface{}, 0)
var dataSlice []string
for 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)
}
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 string
var widthRow string
for 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 := 1
for _, 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)
}
其他方法
func Letter(length int) []string {
var str []string
for i := 0; i < length; i++ {
str = append(str, string(rune('A'+i)))
}
return str
}