Example #1
0
// 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
}
Example #2
0
// 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
}