// JSONToExcel define values
// jsData should contain a array
func JSONToExcel(
sheetName string,
jsData []byte,
include string,
exclude string,
locale string,
) (*xlsx.File, error) {
var rows []map[string]interface{}
err := json.Unmarshal(jsData, &rows)
if err != nil {
return nil, err
}
if len(rows) < 1 {
return nil, fmt.Errorf("No datas found")
}
headMap := rows[0]
allKeys := GetMapKeys(headMap)
keys := mcore.GetFieldsUsed(allKeys, include, exclude)
if sheetName == "" {
sheetName = "Sheet1"
}
file := xlsx.NewFile()
sheet, err := file.AddSheet(sheetName)
if err != nil {
return nil, err
}
// add header row
row := sheet.AddRow()
for _, key := range keys {
cell := row.AddCell()
cell.Value = mmsg.GetTableColumnLabel(locale, "", key)
}
// add datas
for _, row := range rows {
sheetRow := sheet.AddRow()
for _, key := range keys {
cell := sheetRow.AddCell()
value := row[key]
cell.SetValue(value)
}
}
return file, nil
}
// SqlRowsToExcelWithLocale sql rows to excel
// Locale message, table column name.
// params:
// sheetName
// rows
// include
// exclude
// locale
// enableLocale
func SqlRowsToExcelWithLocale(sheetName string,
tableName string,
rows *sql.Rows,
include string,
exclude string,
locale string,
enableLocale bool) (*xlsx.File, error) {
defer rows.Close()
if sheetName == "" {
sheetName = "Sheet1"
}
file := xlsx.NewFile()
sheet, err := file.AddSheet(sheetName)
if logger.CheckError(err) {
return nil, err
}
colNames, err := rows.Columns()
if logger.CheckError(err) {
return nil, err
}
// add header
row := sheet.AddRow()
for _, colName := range colNames {
if !mcore.IsIncludeExcludeIn(colName, colNames, include, exclude) {
continue
}
cell := row.AddCell()
// colName to locale label
if enableLocale && locale != "" {
colName = mmsg.GetTableColumnLabel(locale, tableName, colName)
}
cell.Value = colName
}
scanArgs := make([]interface{}, len(colNames))
values := make([]interface{}, len(colNames))
for i := range values {
scanArgs[i] = &values[i]
}
// add rows data
for rows.Next() {
err := rows.Scan(scanArgs...)
if logger.CheckError(err) {
continue
}
row := sheet.AddRow()
index := -1
for _, v := range values {
index++
// skip for no include column
if !mcore.IsIncludeExcludeIn(colNames[index], colNames, include, exclude) {
continue
}
cell := row.AddCell()
// for float
fv, err := mcore.NewString(v).ToFloat64()
if err == nil {
cell.SetFloat(fv)
continue
}
//if d, b := v.(int64); b {
//cell.SetInt64(d)
//continue
//}
cell.SetValue(v)
}
}
return file, nil
}