func (client *TrelloClient) formatterExcel(cards []*TrelloCardSearchResult) (err error) {
var (
file *xlsx.File
sheet *xlsx.Sheet
row *xlsx.Row
cell *xlsx.Cell
)
client.config.QuoteChar = "" // we do not need quoting in excel
file = xlsx.NewFile()
if sheet, err = file.AddSheet("Sheet1"); err != nil {
return
}
for _, card := range cards {
row = sheet.AddRow()
for _, column := range client.buildOutputLine(card) {
cell = row.AddCell()
cell.Value = column
}
}
if err != nil {
fmt.Printf(err.Error())
}
err = file.Write(os.Stdout)
if err != nil {
fmt.Printf(err.Error())
}
return err
}
func generateXLSXFromCSV(csvPath string, XLSXPath string, delimiter string) error {
csvFile, err := os.Open(csvPath)
if err != nil {
return err
}
defer csvFile.Close()
reader := csv.NewReader(csvFile)
if len(delimiter) > 0 {
reader.Comma = rune(delimiter[0])
} else {
reader.Comma = rune(';')
}
xlsxFile := xlsx.NewFile()
sheet, err := xlsxFile.AddSheet(csvPath)
if err != nil {
return err
}
fields, err := reader.Read()
for err == nil {
row := sheet.AddRow()
for _, field := range fields {
cell := row.AddCell()
cell.Value = field
}
fields, err = reader.Read()
}
if err != nil {
fmt.Printf(err.Error())
}
return xlsxFile.Save(XLSXPath)
}
func main() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
var RawNbr int = 6
file = xlsx.NewFile()
sheet = file.AddSheet("Sheet1")
// 每列标题
text := [6]string{"ID", "鱼类型", "出现帧", "脚本ID", "X", "Y"}
for i := 0; i < RawNbr; i++ {
if i == 0 {
row = sheet.AddRow()
}
cell = row.AddCell()
cell.Value = text[i]
}
files, err := ListDir("../data/timeline", ".dat")
for j := 0; j < len(files); j++ {
write(files[j], sheet, 6)
}
err = file.Save("../outexcel/巡游鱼路径.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func getOnLineList() {
File := xlsx.NewFile()
sheet := File.AddSheet("online")
File.AddSheet("update")
File.AddSheet("insert")
row := sheet.AddRow()
row.WriteSlice(&head, len(head))
Engine := Engines()
result := new(NxServerState)
lines, _ := Engine.Rows(result)
defer lines.Close()
lines.Next()
r := new(NxServerState)
var i int = 1
for {
err := lines.Scan(r)
if err != nil {
return
}
r.ServerName = convert([]byte(r.ServerName))
row := sheet.AddRow()
row.WriteStruct(r, len(head))
if !lines.Next() {
break
}
i++
}
File.Save("server.xlsx")
}
// ImportCSV func
func ImportCSV(csvPath string, delimiter string) (*xlsx.File, error) {
csvFile, err := os.Open(csvPath)
if err != nil {
return nil, err
}
defer csvFile.Close()
reader := csv.NewReader(csvFile)
if len(delimiter) > 0 {
reader.Comma = rune(delimiter[0])
} else {
reader.Comma = rune(',')
}
xlsxFile := xlsx.NewFile()
sheet, err := xlsxFile.AddSheet(csvPath)
if err != nil {
return nil, err
}
fields, err := reader.Read()
for err == nil {
row := sheet.AddRow()
for _, field := range fields {
cell := row.AddCell()
cell.Value = field
}
fields, err = reader.Read()
}
// parse csv file error
if err != nil {
return nil, err
}
// do save
return xlsxFile, nil
}
func main() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
var RawNbr int = 4
file = xlsx.NewFile()
sheetName := [2]string{"Big", "Small"}
datafile := [2]string{"../data/moveline/big", "../data/moveline/small"}
for i, v := range sheetName {
sheet = file.AddSheet(v)
// 每列标题
text := [4]string{"ID", "X", "Y", "Angle"}
for i := 0; i < RawNbr; i++ {
if i == 0 {
row = sheet.AddRow()
}
cell = row.AddCell()
cell.Value = text[i]
}
files, _ := ListDir(datafile[i], ".dat")
for j := 0; j < len(files); j++ {
write(files[j], sheet, RawNbr)
}
}
fmt.Println("正在向excel写数据......")
err = file.Save("../outexcel/单条鱼路径.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func SubjectSpreadsheet(e env.Env, f database.Filter, subj *subject.Subject, w io.Writer) error {
file := xlsx.NewFile()
g, err := e.GroupByFilteredClass(strconv.Itoa(subj.SubjID), "", f)
if err != nil {
return err
}
sheet, err := file.AddSheet("Progress Grid")
if err != nil {
return err
}
subjectGrid(sheet, g, subj, f.NatYear)
sheet, err = file.AddSheet("Students")
if err != nil {
return err
}
subjectBroadsheet(e, subj, sheet, g)
sheet, err = file.AddSheet("Export Details")
if err != nil {
return err
}
exportInfoSheet(sheet, e, f)
file.Write(w)
return nil
}
//驻外团、处
func zwtc() {
doc, err := goquery.NewDocument(URL_ZWTC)
if err != nil {
log.Fatal(err)
}
file := xlsx.NewFile()
sheet := file.AddSheet("驻外团、处")
totalUrl := 0
doc.Find(".w .fr .bd a").Each(func(i int, contentSelection *goquery.Selection) {
name := contentSelection.Text()
if href, exists := contentSelection.Attr("href"); exists {
href = URL_ZWTC + strings.Replace(href, ".", "", 1)
err := parse(sheet, href)
if err != nil {
fmt.Printf("[read error]第%d个：%s。url:%s。 %v", i+1, name, href, err)
}
totalUrl = i + 1
}
})
fileName := fmt.Sprintf("驻外团、处(%d个).xlsx", totalUrl)
err = file.Save(fileName)
if err != nil {
fmt.Printf(err.Error())
}
}
//экспорт данных datas в файл xlsx используя сортировку keys - массив указывающий в каком порядке выводить в таблицу
func savetoxlsx0(namef string, datas map[string]DataTelMans, keys []string) {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet, err = file.AddSheet("лог звонков")
if err != nil {
fmt.Println(err.Error())
}
//заголовок таблицы
row = sheet.AddRow() // добавить строку
cell = row.AddCell() // добавить ячейку в текущей строке
cell.Value = "выгружено: " + time.Now().String()
row = sheet.AddRow() // добавить строку
titletab := []string{"ФИО РГ",
"номер телефона",
"ФИО менеджера",
"всего продолжит-ть",
"всего кол-во звонков",
"кол-во уникальных телефонов",
"кол-во результ. звонков",
"продолжительность уникальных",
"средняя время звонка"}
for i := 0; i < len(titletab); i++ {
cell = row.AddCell() // добавить ячейку в текущей строке
cell.Value = titletab[i]
}
for i := 0; i < len(keys); i++ {
key := keys[i]
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = datas[key].fio_rg
cell = row.AddCell()
cell.Value = key
cell = row.AddCell()
cell.Value = datas[key].fio_man
cell = row.AddCell()
cell.Value = sec_to_s(datas[key].totalsec)
cell = row.AddCell()
cell.Value = strconv.Itoa(datas[key].totalzv)
cell = row.AddCell()
cell.Value = strconv.Itoa(datas[key].kolunik)
cell = row.AddCell()
cell.Value = strconv.Itoa(datas[key].kolresult)
cell = row.AddCell()
cell.Value = sec_to_s(datas[key].secresult)
cell = row.AddCell()
cell.Value = sec_to_s(devidezero(datas[key].totalsec, datas[key].totalzv))
}
err = file.Save(namef)
if err != nil {
fmt.Println(err.Error())
}
}
func combineCSVtoXLSX(sources []string, output string, delimiter string) error {
xlsxFile := xlsx.NewFile()
for _, s := range sources {
if err := appendCSVtoXLSX(s, xlsxFile, delimiter); err != nil {
return err
}
}
return xlsxFile.Save(output)
}
// XLSX exports the Dataset as a byte array representing the .xlsx format.
func (d *Dataset) XLSX() (*Exportable, error) {
file := xlsx.NewFile()
if err := d.addXlsxSheetToFile(file, "Sheet 1"); err != nil {
return nil, err
}
b := newBuffer()
file.Write(b)
return newExportable(b), nil
}
// XLSX returns a XLSX representation of the Databook as an exportable.
func (d *Databook) XLSX() (*Exportable, error) {
file := xlsx.NewFile()
for _, s := range d.sheets {
s.dataset.addXlsxSheetToFile(file, s.title)
}
b := newBuffer()
file.Write(b)
return newExportable(b), nil
}
func NewFile(descpool *pbmeta.DescriptorPool) *File {
self := &File{
Sheets: make([]*Sheet, 0),
SheetMap: make(map[string]*Sheet),
descpool: descpool,
Raw: xlsx.NewFile(),
}
return self
}
func createWorkbook(m map[string]messages) {
var channelNames sort.StringSlice
workbook := xlsx.NewFile()
fullPath := filepath.Clean(filepath.Join(outputDir, filename))
// First, sort the map by channel name
for channel := range m {
channelNames = append(channelNames, channel)
}
channelNames.Sort()
for _, channel := range channelNames {
sheet, err := workbook.AddSheet(channel)
if err != nil {
panic(err)
}
sort.Sort(sort.Reverse(m[channel]))
headingRow := sheet.AddRow()
headingRow.AddCell().SetString("Timestamp")
headingRow.AddCell().SetString("User")
headingRow.AddCell().SetString("Message")
if err := sheet.SetColWidth(0, 1, 20); err != nil {
panic(err)
}
if err := sheet.SetColWidth(2, 2, 200); err != nil {
panic(err)
}
for _, message := range m[channel] {
messageRow := sheet.AddRow()
messageRow.AddCell().SetString(message.Timestamp.Format("Jan 02, 2006 | 15:04"))
messageRow.AddCell().SetString(message.User)
messageRow.AddCell().SetString(message.Text)
}
}
if err := os.MkdirAll(filepath.Dir(fullPath), os.ModePerm); err != nil {
panic(err)
}
if err := workbook.Save(fullPath); err != nil {
panic(err)
}
}
func (s *UtilTestSuite) TestCheckGetHeader(c *C) {
GetCheckResultHeader()
checkResult := CheckResult{}
var file *xlsx.File
var sheet *xlsx.Sheet
file = xlsx.NewFile()
sheet, _ = file.AddSheet("Sheet1")
sheet.AddRow()
checkResult.WriteToRow(sheet.AddRow())
c.Log(checkResult)
}
// ValueToExcelWithLocale value to excel with locale
// rows should be slice type.
func ValueToExcelWithLocale(sheetName string,
rows interface{},
include string,
exclude string,
locale string,
enableLocale bool) (*xlsx.File, error) {
// check args
if sheetName == "" {
sheetName = "Sheet1"
}
v := reflect.ValueOf(rows)
if v.Kind() != reflect.Slice {
return nil, fmt.Errorf("rows should be a slice")
}
file := xlsx.NewFile()
sheet, err := file.AddSheet(sheetName)
if err != nil {
return nil, err
}
//fs := mcore.GetArrayFields(rows)
fs := mcore.GetUsedArrayFields(rows, include, exclude)
// add header
row := sheet.AddRow()
// f means fields
for _, f := range fs {
cell := row.AddCell()
// locale header
if enableLocale && locale != "" {
f = mmsg.GetModelFieldLabel(locale, mcore.GetArrayFirstElement(rows), f)
}
cell.Value = f
}
// add data
for i := 0; i < v.Len(); i++ {
rowValue := v.Index(i).Interface()
row := sheet.AddRow()
for _, f := range fs {
cell := row.AddCell()
// TODO adapter for all kinds data type
//cell.Value = mcore.GetFieldValue(rowValue, f)
cv := mcore.GetFieldValueOrigin(rowValue, f)
cell.SetValue(cv)
}
}
return file, nil
}
//领事安全预警
func lsyj() {
file := xlsx.NewFile()
sheet := file.AddSheet("领事安全预警")
doc, err := goquery.NewDocument(URL_LSYJ + "/default.shtml")
if err != nil {
log.Fatal(err)
}
totalUrl := 0
doc.Find(".ct3_m .news_list li a").Each(func(i int, contentSelection *goquery.Selection) {
name := contentSelection.Text()
if href, exists := contentSelection.Attr("href"); exists {
href = URL_LSYJ + strings.Replace(href, ".", "", 1)
err := parseLSYJ(sheet, href)
if err != nil {
fmt.Printf("[read error]第%d个：%s。url:%s。 %v", i+1, name, href, err)
}
totalUrl += 1
}
})
for i := 1; i <= 11; i++ {
url := fmt.Sprintf("/default_%d.shtml", i)
fmt.Printf("第%d个URL：%s", i, url)
doc, err := goquery.NewDocument(URL_LSYJ + url)
if err != nil {
log.Fatal(err)
}
doc.Find(".ct3_m .news_list li a").Each(func(i int, contentSelection *goquery.Selection) {
name := contentSelection.Text()
if href, exists := contentSelection.Attr("href"); exists {
href = URL_LSYJ + strings.Replace(href, ".", "", 1)
err := parseLSYJ(sheet, href)
if err != nil {
fmt.Printf("[read error]第%d个：%s。url:%s。 %v", i+1, name, href, err)
}
totalUrl += 1
}
})
}
fileName := fmt.Sprintf("领事安全预警(%d个).xlsx", totalUrl)
err = file.Save(fileName)
if err != nil {
fmt.Printf(err.Error())
}
}
func exportXslt(ctx appengine.Context) (*xlsx.File, error) {
users, err := getTransactionList(ctx)
if err != nil {
return nil, err
}
file := xlsx.NewFile()
sheet, err := file.AddSheet("Sheet1")
if err != nil {
return nil, err
}
addRow(
sheet,
"Medarbejder nr i ADK",
"Aktiveringsdato",
"Nr.",
"Fra dato",
"Til dato",
"Tidsskema",
"Bemærkninger",
)
for _, user := range users {
addRow(
sheet,
user.NavitasId,
"TODO",
user.NavitasId,
"TODO",
"TODO",
"24 Timers",
user.Email,
)
}
// Example:
// "Medarbejder nr i ADK" : "N0416"
// "Aktiveringsdato" : "30.06.2015"
// "Nr." : "N0416"
// "Fra dato" : "30-06-2015"
// "Til dato" : "06-01-2016"
// "Tidsskema" : "24 Timers"
// "Bemærkninger" : ""
return file, nil
}
/*-----------------------------------------------------------------------------
-- FUNCTION: generateReport
--
-- DATE: February 6, 2016
--
-- REVISIONS: February 13, 2016 generalised for any list of interface{}s
--
-- DESIGNER: Marc Vouve
--
-- PROGRAMMER: Marc Vouve
--
-- INTERFACE: func generateReport(fname string, connections *list.List)
-- fname: Name of the file
-- elements: A list of structures to be reported
--
-- RETURNS: void
--
-- NOTES: This function will only generate a report of up to ExcelMaxRows rows
-- They will all be on "Sheet 1" and a warning will be logged if more
-- data is present.
------------------------------------------------------------------------------*/
func generateReport(fname string, elements *list.List) {
doc := xlsx.NewFile()
if elements.Len() <= 0 {
return
}
report, _ := doc.AddSheet("Sheet 1") // TODO: make this more generalised?
generateHeaders(elements.Front().Value, report.AddRow())
for e := elements.Front(); e != nil; e = e.Next() {
generateRow(e.Value, report.AddRow())
if report.MaxRow >= ExcelMaxRows {
log.Println("Too many entries for report, stopping at ", report.MaxRow)
break
}
}
doc.Save(fname + ".xlsx")
}
// 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
}
func main() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet = file.AddSheet("Sheet1")
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "I am a cell!"
err = file.Save("d:/Desktop/demo_write.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
//使馆信息在一行的
func test3() {
file := xlsx.NewFile()
sheet := file.AddSheet("test3")
// url := "http://www.fmprc.gov.cn/mfa_chn/wjb_602314/zwjg_603776/zwsg_603778/yz_603780/t3320.shtml"
url := "http://www.fmprc.gov.cn/mfa_chn/wjb_602314/zwjg_603776/zwsg_603778/fz_603782/t1200664.shtml"
err := parsetest3(sheet, url)
if err != nil {
fmt.Printf("[read error] %v", err)
}
err = file.Save("test3.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
//标题无来源的
func test4() {
file := xlsx.NewFile()
sheet := file.AddSheet("test4")
// url := "http://www.fmprc.gov.cn/mfa_chn/wjb_602314/zwjg_603776/zwsg_603778/yz_603780/t3320.shtml"
url := "http://cs.fmprc.gov.cn/gyls/lsgz/lsyj/t1262650.shtml"
err := parseLSYJ(sheet, url)
if err != nil {
fmt.Printf("[read error] %v", err)
}
err = file.Save("test4.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func test1() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet = file.AddSheet("Sheet1")
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "000101"
cell = row.AddCell()
cell.Value = "中文"
err = file.Save("MyXLSXFile.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func generateExcel(houses map[string]*House) {
file := xlsx.NewFile()
sheet, err := file.AddSheet("Sheet1")
if err != nil {
fmt.Println(err)
return
}
row := sheet.AddRow()
cell := row.AddCell()
cell.SetString("house")
cell = row.AddCell()
cell.SetString("electricity kWh")
cell = row.AddCell()
cell.SetString("heat kWh")
cell = row.AddCell()
cell.SetString("water m3")
cell = row.AddCell()
cell.SetString("hot water m3")
for k, v := range houses {
row := sheet.AddRow()
cell := row.AddCell()
cell.SetString(k)
cell = row.AddCell()
cell.SetFloat(v.Power_kWh)
cell = row.AddCell()
cell.SetFloat(v.Heat_kWh)
cell = row.AddCell()
cell.SetFloat(v.ColdWater_m3)
cell = row.AddCell()
cell.SetFloat(v.HotWater_m3)
}
err = file.Save("MyXLSXFile.xlsx")
if err != nil {
log.Println(err)
}
}
// ArrayToExcel write data
func ArrayToExcel(sheetName string, data [][]string) (*xlsx.File, error) {
if sheetName == "" {
sheetName = "Sheet1"
}
file := xlsx.NewFile()
sheet, err := file.AddSheet(sheetName)
if err != nil {
return nil, err
}
for _, rowData := range data {
row := sheet.AddRow()
for _, colData := range rowData {
cell := row.AddCell()
cell.Value = colData
}
}
return file, nil
}
//创建excel
func CreateXlsx(fileName string, headers []string, list [][]string) {
f := xlsx.NewFile()
sheet := f.AddSheet("sheet1")
//头部
rows := sheet.AddRow()
for _, v := range headers {
rows.AddCell().Value = v
}
//数据
for _, v := range list {
rows := sheet.AddRow()
for _, vv := range v {
rows.AddCell().Value = vv
}
}
//保存
f.Save(fileName)
}
func EncodeXlsx(data [][]string) ([]byte, error) {
var writer bytes.Buffer
file := xlsx.NewFile()
sheet, err := file.AddSheet("Sheet1")
if err != nil {
return nil, err
}
for i := 0; i != len(data[0]); i++ {
sheet.Col(i).Width = 25
sheet.Col(i).SetType(xlsx.CellTypeString)
}
for i := 0; i != len(data); i++ {
row := sheet.AddRow()
for j := 0; j != len(data[i]); j++ {
cell := row.AddCell()
cell.SetString(data[i][j])
}
}
file.Write(&writer)
return writer.Bytes(), nil
}
func Test() {
xlFile := xlsx.NewFile()
sh := xlFile.AddSheet("result")
sh.SetColWidth(1, 2, 50)
sh.MaxCol = 2
header := sh.AddRow()
rCell := &xlsx.Cell{Value: "结果"}
rCell.SetStyle(red)
header.Cells = []*xlsx.Cell{
&xlsx.Cell{Value: "编号"},
rCell,
}
for i := 0; i < 10; i++ {
row := sh.AddRow()
rs := &xlsx.Cell{Value: "这是一个悲伤的故事，一旦这样就那样。。。。。。\n好的没问题！"}
rs.SetStyle(green)
row.Cells = []*xlsx.Cell{
&xlsx.Cell{Value: "aaaaaa"},
rs,
}
}
xlFile.Save("aaa.xlsx")
}
func (client *TrelloClient) memberFormatterExcel(members []*TrelloMember) (err error) {
var (
file *xlsx.File
sheet *xlsx.Sheet
row *xlsx.Row
cell *xlsx.Cell
)
client.config.QuoteChar = "" // we do not need quoting in excel
file = xlsx.NewFile()
if sheet, err = file.AddSheet("Sheet1"); err != nil {
return
}
header := strings.Split(client.config.SearchResultFields, ",")
row = sheet.AddRow()
for _, column := range header {
cell = row.AddCell()
cell.Value = strings.Title(column)
}
for _, member := range members {
row = sheet.AddRow()
for _, column := range client.buildMemberSlice(member) {
cell = row.AddCell()
cell.Value = column
}
}
if err != nil {
fmt.Printf(err.Error())
}
err = file.Write(os.Stdout)
if err != nil {
fmt.Printf(err.Error())
}
return err
}