func (x *xlsxConverter) Convert(w io.Writer) error {
var xlFile, err = xlsx.OpenFile(x.fileName)
if err != nil {
return err
}
var sheet *xlsx.Sheet
for s := range xlFile.Sheets {
if s == (x.sheetNumber - 1) {
sheet = xlFile.Sheets[s]
break
}
}
if sheet == nil {
return fmt.Errorf("did not find sheet %d in %s\n",
x.sheetNumber, x.fileName)
}
var doc = newXliffDoc(x.fileName, x.sourceLang)
x.SheetToDoc(doc, sheet)
var out []byte
if out, err = xml.MarshalIndent(doc, "", " "); err != nil {
return err
}
io.WriteString(w, xml.Header)
w.Write(out)
return nil
}
func main() {
// xlsxファイル読み込み
file, err := xlsx.OpenFile("./xlsx/test.xlsx")
if err != nil {
log.Fatal(err)
os.Exit(1)
}
// 出力CSVファイル作成
csv_file, _ := os.Create("./csv/test.csv")
writer := csv.NewWriter(csv_file)
writer.Comma = ','
// シートの走査
for _, sheet := range file.Sheets {
// 行の走査
for _, row := range sheet.Rows {
var rowCells []string
rowCells = make([]string, 0)
// 行内のセル走査
for _, cell := range row.Cells {
rowCells = append(rowCells, cell.String())
}
writer.Write(rowCells)
writer.Flush()
}
}
}
func ParseFile(fileName string, outDir string, exportType string, soc string) error {
defer trap_error()()
ofn, err := getOutFileName(fileName)
if err != nil {
return err
}
outFileName := filepath.Join(outDir, ofn)
f, err := os.Create(outFileName)
if err != nil {
return err
}
defer f.Close()
file, err := xlsx.OpenFile(fileName)
if err != nil {
return err
}
//默认excel的第一个sheet为配置
sheet := file.Sheets[0]
rows := sheet.Rows
rowNum := len(rows)
if rowNum < 3 {
err = errors.New("validate config excel")
return err
}
firseRowCells := rows[0].Cells
secondRowCells := rows[1].Cells
threeRowCells := rows[2].Cells
//
titles := make(map[int]string)
secondConfigCells := make([]*configCell, 0, len(firseRowCells))
threeConfigCells := make([]*configCell, 0, len(firseRowCells))
for i := 0; i < len(firseRowCells); i++ {
if title := strings.TrimSpace(firseRowCells[i].Value); title != "" {
titles[i] = title
secondConfigCell, err := make_config_cell(secondRowCells[i].Value, i)
if err != nil {
return err
}
if secondConfigCell != nil {
secondConfigCells = append(secondConfigCells, secondConfigCell)
}
threeConfigCell, err := make_config_cell(threeRowCells[i].Value, i)
if err != nil {
return err
}
if threeConfigCell != nil {
threeConfigCells = append(threeConfigCells, threeConfigCell)
}
}
}
if exportType == "json" {
if soc == "server" {
err = parse2json(f, rows[3:], secondConfigCells, titles, 3)
} else {
err = parse2json(f, rows[3:], threeConfigCells, titles, 3)
}
}
return err
}
func getEventListInEventFile() []EventInformation {
eventListFromFile := make([]EventInformation, 20)
xlFile, err := xlsx.OpenFile(eventFilePath)
if err != nil {
log.Fatalf("Can not read file: %v", err)
}
for i, sheet := range xlFile.Sheets {
for _, row := range sheet.Rows {
if len(row.Cells) >= MIN_NECESSARY_CELL_SIZE {
date, _ := row.Cells[0].String()
title, _ := row.Cells[1].String()
description, _ := row.Cells[2].String()
if isConvenientEvent(i, date, description) {
eventListFromFile = append(eventListFromFile, EventInformation{
date: date,
title: title,
description: description,
location: EVENT_LOCATION,
})
}
}
}
}
return eventListFromFile
}
// GetSheetByIndex returns Sheet
// index from 0
func GetSheetByIndex(file string, sheetIndex int) (*Sheet, error) {
book, err := xlsx.OpenFile(file)
if nil != err {
return nil, err
}
return GetSheetFromBookByIndex(book, sheetIndex)
}
func main() {
loadConfig()
excelNames := getExcelList()
for _, fName := range excelNames {
fmt.Println("Excel file name", fName)
iFile, err := xlsx.OpenFile(fName)
if err != nil {
panic(err.Error())
}
fileName := fName[strings.LastIndex(fName, "\\")+1 : strings.Index(fName, ".")]
exportFileName := exportRoot + fileName + ".txt"
fmt.Println("Export file name", exportFileName)
err = os.Remove(exportFileName)
if err != nil {
//panic(err.Error())
}
eFile, err := os.Create(exportFileName)
if err != nil {
panic(err.Error())
}
defer eFile.Close()
//get filter
excelFilter, ok := filterMap[fileName]
if !ok {
excelFilter = filterMap["default"]
}
export(iFile, eFile, excelFilter)
for i := 0; i < len(conns); i++ {
import2db(conns[i], eFile, fileName)
}
}
for _, dbconn := range conns {
dbconn.Close()
}
}
// LoadXLSX used to read file in xlsx type
func LoadXLSX(path string) tool.File {
xl, err := xlsx.OpenFile(path)
if err != nil {
fmt.Println("xlsx read error : " + err.Error())
return nil
}
for _, sheet := range xl.Sheets {
for _, row := range sheet.Rows {
for _, cell := range row.Cells {
s, _ := cell.String()
fmt.Printf("%s_", s)
// fmt.Printf("#%s", cell.String())
}
fmt.Printf("\n")
}
}
return nil
}
func generateCSVFromXLSXFile(excelFileName string, sheetIndex int, outputf outputer) error {
xlFile, error := xlsx.OpenFile(excelFileName)
if error != nil {
return error
}
sheetLen := len(xlFile.Sheets)
switch {
case sheetLen == 0:
return errors.New("This XLSX file contains no sheets.")
case sheetIndex >= sheetLen:
return fmt.Errorf("No sheet %d available, please select a sheet between 0 and %d\n", sheetIndex, sheetLen-1)
}
sheet := xlFile.Sheets[sheetIndex]
for _, row := range sheet.Rows {
var vals []string
if row != nil {
for _, cell := range row.Cells {
str, err := cell.String()
if err != nil {
vals = append(vals, err.Error())
}
vals = append(vals, fmt.Sprintf("%q", str))
}
outputf(strings.Join(vals, *delimiter) + "\n")
}
}
return nil
}
func main() {
db, err := sql.Open("mysql", "root:123456@tcp(localhost:3306)/test?charset=utf8")
checkErr(err)
excelFileName := "./ccc.xlsx"
xlFile, error := xlsx.OpenFile(excelFileName)
if error != nil {
fmt.Println("出错了,小伙")
}
//插入数据
stmt, err := db.Prepare("INSERT excelInfo SET id=?,name=?")
checkErr(err)
for _, sheet := range xlFile.Sheets {
for _, row := range sheet.Rows {
fmt.Printf("%s\n", sheet.Rows)
if row.Cells[0].String() == "id" || row.Cells[1].String() == "name" {
continue
}
stmt.Exec(row.Cells[0].String(), row.Cells[1].String())
checkErr(err)
}
}
}
func (this *ImportController) Post() {
//check islogin
session := this.GetSession("Admin")
if session == nil {
beego.Trace("session verify failed!")
this.Redirect("/", 302)
return
}
saveFileName := "temp" + strconv.FormatInt(int64(time.Now().UnixNano()), 10) + ".xlsx"
beego.Debug("saveFileName=", saveFileName)
this.SaveToFile("file", saveFileName)
devices := make([]models.Deviceinfo, 0)
xlFile, err := xlsx.OpenFile(saveFileName)
if err != nil {
beego.Error("Open excel file!", err)
}
for _, sheet := range xlFile.Sheets {
for _, row := range sheet.Rows {
for _, cell := range row.Cells {
device := models.Deviceinfo{
Mac: cell.String(),
ImportTime: time.Now(),
InvalidTime: time.Now().Add(3 * 30 * 24 * time.Hour),
}
//check mac address format
if !CheckMacFormat(device.Mac) {
beego.Info(device.Mac, "not a mac address!")
continue
}
//if the mac address had existed,skip it
if models.ImportDeviceCheck(&device) {
beego.Info(device.Mac, "had been imported!")
continue
}
devices = append(devices, device)
}
}
}
//delete used file
err = os.Remove(saveFileName)
if err != nil {
beego.Error("Remove temp excel file failed!", err)
}
ok := models.ImportDevices(&devices)
if ok {
beego.Info("ImportDevices success!")
this.Redirect("/home", 301)
this.Data["IsImported"] = true
return
} else {
beego.Info("ImportDevices failed! once again")
this.Redirect("/import", 302)
return
}
}
func processFile(fileName string) (uint16, error) {
xlFile, err := xlsx.OpenFile(fileName)
if err != nil {
fmt.Printf(err.Error())
}
model := StaffModel.NewObj()
fmt.Println((*model).TabName)
var cnt uint16 = 0
for _, sheet := range xlFile.Sheets {
Rlen := len(sheet.Rows)
for j := 1; j < Rlen; j++ {
row := sheet.Rows[j]
tmp := make(map[string]string)
len := len(row.Cells)
if len < 3 {
continue
}
cnt++
tmp["name"] = "hello"
tmp["company_id"] = "591591347249199023"
tmp["id_no"] = row.Cells[2].Value
tmp["uuid"] = "0"
tmp["status"] = "0"
tmp["mobilephone"] = row.Cells[1].Value
id := model.Insert(tmp)
fmt.Println(id)
}
}
fmt.Println("done")
return cnt, nil
}
// Opens the given excel file, reads the value of each cell in the given cell range
// and tries to open each value in the default browser
func openUrlsFromExcelCellRange(excelFileName string, cellRange string, sheetIndex int) {
xlFile, error := xlsx.OpenFile(excelFileName)
if error != nil {
log.Fatalln("Error while opening excel file", excelFileName)
}
log.Println("Excel File has", len(xlFile.Sheets), "sheets")
sheet := xlFile.Sheets[0]
log.Println("First Sheet has", len(sheet.Rows), "rows")
log.Println("Trying to parse cell range", cellRange)
columnStart, columnEnd, rowStart, rowEnd := parseRange(cellRange)
log.Println("Parsed to (zero-based) rowStart:", rowStart-1, "rowEnd:", rowEnd-1, "columnStart:", columnStart-1, "columnEnd:", columnEnd-1)
for r := rowStart - 1; r < rowEnd; r++ {
if len(sheet.Rows) <= r {
log.Println("There is no row with rownum", r)
continue
}
row := sheet.Rows[r]
for c := columnStart - 1; c < columnEnd; c++ {
if row == nil || row.Cells == nil || len(row.Cells) <= c {
log.Println("Does NOT contain a valid cell value in rownum", r, "colnum", c)
continue
}
cellValue := row.Cells[c].String()
match, err := regexp.MatchString("http(s?)://", cellValue)
if err != nil {
log.Println("Error while trying to parse value", cellValue, "from cell rownum", r, "colnum", c)
}
if match {
openUrl(cellValue)
}
}
}
}
func readExcel(readFilePath string) {
xlFile, err := xlsx.OpenFile(readFilePath)
if err != nil {
log.Fatal(err)
}
for _, sheet := range xlFile.Sheets {
// sheet単位でfile生成
fmt.Println(sheet.Name)
f, err := os.Create("./sample/" + sheet.Name + ".txt")
if err != nil {
log.Fatal(err)
}
// rowはまとめて1行にする
for _, row := range sheet.Rows {
fmt.Println(row.Cells)
for idx, cell := range row.Cells {
if idx != 0 {
f.WriteString(",")
}
f.WriteString(cell.String())
}
f.WriteString("\n")
}
f.Close()
}
}
func readTable() []Table {
xlFile, err := xlsx.OpenFile("court/Copy of 法院名称第二次转换 (3).xlsx")
if err != nil {
panic(err)
}
if len(xlFile.Sheets) < 1 {
fmt.Println("There are sheets here.")
os.Exit(1)
}
sheet1 := xlFile.Sheets[0]
tables := make([]Table, 0)
for _, row := range sheet1.Rows[1:] {
if len(row.Cells) < 3 {
continue
}
oldName := strings.Replace(row.Cells[1].String(), " ", "", -1)
newName := strings.Replace(row.Cells[2].String(), " ", "", -1)
if oldName != "" && newName != "" {
table := Table{
Old: oldName,
New: newName,
}
tables = append(tables, table)
}
}
return tables
}
func generateCSVFromXLSXFile(w io.Writer, excelFileName string, sheetIndex int, csvOpts csvOptSetter) error {
xlFile, error := xlsx.OpenFile(excelFileName)
if error != nil {
return error
}
sheetLen := len(xlFile.Sheets)
switch {
case sheetLen == 0:
return errors.New("This XLSX file contains no sheets.")
case sheetIndex >= sheetLen:
return fmt.Errorf("No sheet %d available, please select a sheet between 0 and %d\n", sheetIndex, sheetLen-1)
}
cw := csv.NewWriter(w)
if csvOpts != nil {
csvOpts(cw)
}
sheet := xlFile.Sheets[sheetIndex]
var vals []string
for _, row := range sheet.Rows {
if row != nil {
vals = vals[:0]
for _, cell := range row.Cells {
vals = append(vals, cell.String())
}
if err := cw.Write(vals); err != nil {
return err
}
}
}
cw.Flush()
return cw.Error()
}
// get all raw data from excel
// output index mean=> sheetIndex ,row ,cell ,value
// not remove any cells
func XlsxFile2Array(path string) ([][][]string, error) {
file, err := xlsx.OpenFile(path)
if err != nil {
return nil, err
}
return xlsx2ArrayXlsxFile(file)
}
// ExportCSV export
func ExportCSV(excelFileName string, sheetIndex int, delimiter string, out io.Writer) error {
xlFile, error := xlsx.OpenFile(excelFileName)
// check file exists
if error != nil {
return error
}
sheetLen := len(xlFile.Sheets)
// check sheet exists.
switch {
case sheetLen == 0:
return errors.New("This XLSX file contains no sheets.")
case sheetIndex >= sheetLen:
return fmt.Errorf("No sheet %d available, sheetIndex between 0 and %d\n", sheetIndex, sheetLen-1)
}
if delimiter == "" {
delimiter = ","
}
sheet := xlFile.Sheets[sheetIndex]
// walk rows and cols
for _, row := range sheet.Rows {
var vals []string
if row != nil {
for _, cell := range row.Cells {
vals = append(vals, fmt.Sprintf("%q", cell.Value))
}
line := strings.Join(vals, delimiter) + "\n"
out.Write([]byte(line))
}
}
return nil
}
func ReadXlsx_test(t *testing.T) {
xlFile, err = xlsx.OpenFile("/usr/local/connect.xlsx")
if err != nil {
t.Error(err)
}
i := 0
for _, sheet := range xlFile.Sheets {
for _, row := range sheet.Rows {
j := 0
for _, cell := range row.Cells {
j += 1
if j == 4 {
fmt.Printf("device: %s ", cell.String())
}
if j == 15 {
fmt.Printf("ID_CART: %s\n", cell.String())
}
}
i += 1
if i > 10 {
break
}
}
}
}
func Load(fname string, head bool) Panel {
p := make(Panel)
switch path.Ext(fname) {
case ".csv":
h := []string{}
f, _ := os.Open(fname)
headless := false
if !head {
headless = true
}
r := csv.NewReader(bufio.NewReader(f))
for {
if head {
line, _ := r.Read()
for _, val := range line {
h = append(h, strings.ToLower(val))
}
head = false
} else {
line, err := r.Read()
if err == io.EOF {
break
}
for key, val := range line {
if headless {
k := strconv.Itoa(key)
p[k] = append(p[k], val)
} else {
p[h[key]] = append(p[h[key]], val)
}
}
}
}
case ".xml":
case ".tsv":
case ".json":
case ".xlsx":
file, err := xlsx.OpenFile(fname)
if err != nil {
fmt.Println(err)
}
for _, sheet := range file.Sheets {
for _, row := range sheet.Rows {
for _, cell := range row.Cells {
c, _ := cell.String()
fmt.Printf("%s\n", c)
}
}
}
}
return p.Clean()
}
func (e *Goexcel) OpenFile(filename string) error {
file, err := xlsx.OpenFile(filename)
if err != nil {
return err
}
e.File = file
return nil
}
func (this *BaseXlsParser) traversal(path string) error {
xlFile, err := xlsx.OpenFile(path)
if nil != err {
return err
}
this.root = toXlsObject(xlFile)
return nil
}
func TestNumbers(t *testing.T) {
excelFileName := "test.xlsx"
xlFile, err := xlsx.OpenFile(excelFileName)
if err != nil {
t.Fatal(err)
}
_default_numbers.parse(xlFile.Sheets)
spew.Dump(_default_numbers)
}
func main() {
fmt.Println("Opening original.xlsx")
originalXlFile, err := xlsx.OpenFile("original.xlsx")
if err != nil {
panic(err)
}
fmt.Println("Saving after_write.xlsx")
originalXlFile.Save("after_write.xlsx")
fmt.Println("Opening after_write.xlsx again")
_, err = xlsx.OpenFile("after_write.xlsx")
if err != nil {
panic(err)
}
}
func main() {
// xlsx-->sheets-->rows-->cells
xl, err := xlsx.OpenFile("testfile.xlsx")
if err != nil {
return
}
sheetByName(xl)
//allSheet(xl)
}
func ReadAndSave(filePath string) bool {
// 文件路径
// file := "/Users/Laughing/Downloads/选案资料.xlsx"
// file := "F:/workspace/go/taxrecrd/file/选案资料.xlsx"
xlFile, err := xlsx.OpenFile(filePath)
if err != nil {
fmt.Println(err)
}
// 切片数量
sls := 0
// 结果
result := false
// 循环读取表格
for sheetnum, sheet := range xlFile.Sheets {
if sheetnum >= 1 {
// return true
break
}
// 按行数计算切片循环数
fmt.Println("Total Rows:", sheet.MaxRow)
fmt.Println("Remainder:", (sheet.MaxRow)%100)
sls = (sheet.MaxRow) / 100
if (sheet.MaxRow)%100 > 0 {
sls += 1
}
// 创建channel用于接收每个并发的完成情况
c := make(chan bool)
fmt.Println("Total Slice:", sls)
// 循环拆分切片
for i := 1; i <= sls; i++ {
start := (i-1)*100 + 1
end := start + 100
if start < 0 {
start = 0
}
// 如果超出边界则回到最大边界值
if end > sheet.MaxRow {
end = sheet.MaxRow
}
// fmt.Println("Start:", start, " End:", end)
tempSlice := sheet.Rows[start:end]
// 去执行插入数据
go readRows(tempSlice, c, int64(sls))
}
for {
time.Sleep(1000)
if <-c {
result = true
break
}
}
}
fmt.Println("Result:", result)
return result
}
func OpenExcelFile(filename string) *xlsx.File {
fmt.Println("| Opening file")
xlFile, err := xlsx.OpenFile(filename)
if err != nil {
fmt.Printf("| Can't open file - \"%s\"\n", filename)
return nil
} else {
fmt.Printf("| File \"%s\" successfully opened\n", filename)
return xlFile
}
}
func SaveExcelEntries(excelFileName string, namespace string) {
fmt.Println("Inserting Records to Database....")
rowcount := 0
colunmcount := 0
var exceldata []map[string]interface{}
var colunName []string
//file read
xlFile, error := xlsx.OpenFile(excelFileName)
if error == nil {
for _, sheet := range xlFile.Sheets {
rowcount = (sheet.MaxRow - 1)
colunmcount = sheet.MaxCol
colunName = make([]string, colunmcount)
for _, row := range sheet.Rows {
for j, cel := range row.Cells {
colunName[j] = cel.String()
}
break
}
exceldata = make(([]map[string]interface{}), rowcount)
if error == nil {
for _, sheet := range xlFile.Sheets {
for rownumber, row := range sheet.Rows {
currentRow := make(map[string]interface{})
if rownumber != 0 {
exceldata[rownumber-1] = currentRow
for cellnumber, cell := range row.Cells {
exceldata[rownumber-1][colunName[cellnumber]] = cell.String()
}
}
}
}
}
Id := colunName[0]
var extraMap map[string]interface{}
extraMap = make(map[string]interface{})
extraMap["File"] = "exceldata"
fmt.Println("Namespace : " + namespace)
fmt.Println("filename : " + getExcelFileName(excelFileName) /*+ strings.ToLower(sheet.Name)*/)
client.GoExtra("token", namespace, getExcelFileName(excelFileName) /*+strings.ToLower(sheet.Name)*/, extraMap).StoreObject().WithKeyField(Id).AndStoreMapInterface(exceldata).Ok()
//client.GoExtra("token", namespace, getExcelFileName(excelFileName), extraMap).StoreObject().WithKeyField(Id).AndStoreMapInterface(exceldata).Ok()
}
}
return
}
func testReadExcel() {
excelFileName := "MyXLSXFile1.xlsx"
xlFile, err := xlsx.OpenFile(excelFileName)
if err != nil {
}
for _, sheet := range xlFile.Sheets {
for _, row := range sheet.Rows {
for _, cell := range row.Cells {
fmt.Printf("%s\n", cell.String())
}
}
}
}
// Generate
func (d *REGenerationApendix) Generate(base *BaseController) {
var (
folderName string = "Generation Apendix"
)
if base != nil {
d.BaseController = base
}
ctx := d.BaseController.Ctx
dataSources, path := base.GetDataSource(folderName)
tk.Println("Generating Generation Apendix from Excel File..")
for _, source := range dataSources {
tk.Println(path + "\\" + source.Name())
file, e := xlsx.OpenFile(path + "\\" + source.Name())
if e != nil {
tk.Println(e)
os.Exit(0)
}
sheet := file.Sheet["Central"]
for _, row := range sheet.Rows {
firstCell := ""
if len(row.Cells) > 1 {
firstCell, _ = row.Cells[1].String()
}
if len(row.Cells) > 0 && strings.Trim(strings.ToLower(firstCell), " ") != "power plant" && strings.Trim(strings.ToLower(firstCell), " ") != "" {
data := new(GenerationAppendix)
data.Plant, _ = row.Cells[1].String()
data.Type, _ = row.Cells[2].String()
data.Units, _ = row.Cells[3].Int()
data.ContractedCapacity = CheckNumberValue(row.Cells[4].Float())
data.CCR = CheckNumberValue(row.Cells[5].Float())
data.FOMR = CheckNumberValue(row.Cells[6].Float())
data.VOMR = CheckNumberValue(row.Cells[7].Float())
data.AGP = CheckNumberValue(row.Cells[8].Float())
data.LCSummer = CheckNumberValue(row.Cells[9].Float())
data.LCWinter = CheckNumberValue(row.Cells[10].Float())
data.LCTotal = CheckNumberValue(row.Cells[11].Float())
data.Startup = CheckNumberValue(row.Cells[12].Float())
data.Deduct = CheckNumberValue(row.Cells[13].Float())
_, e := ctx.InsertOut(data)
if e != nil {
tk.Println("ERR on file :", source.Name(), " | ROW :", idx)
tk.Println(e)
}
}
}
}
tk.Println("Generation Apendix from Excel File : COMPLETE")
}
func main() {
flag.BoolVar(&debug, "d", false, "-d=true打印debug信息")
flag.Parse()
var readline int
fmt.Printf("1:同步线上状态服务器列表\n2:更新server.xlsx中update的内容\n3:插入中server.xlsx中的insert的内容\n")
fmt.Print("输入操作选项：")
fmt.Scan(&readline)
engines := Engines()
switch readline {
case 1:
getOnLineList()
writehead()
fmt.Println("已同步线上最新列表,保存在server.xlsx")
case 2:
File, e := xlsx.OpenFile("server.xlsx")
if e != nil {
fmt.Println(e)
return
}
fmt.Println("更新server.xlsx中update的内容")
update(engines, File.Sheet["update"])
case 3:
File, e := xlsx.OpenFile("server.xlsx")
if e != nil {
fmt.Println(e)
return
}
fmt.Println("插入server.xlsx中insert的内容")
insert(engines, File.Sheet["insert"])
default:
fmt.Println("使用方法：输入想要操作的选项（例如：1）")
}
fmt.Printf("\n\n20秒后自动退出,也可直接关闭\n")
for i := 20; i > 0; i-- {
fmt.Printf("\r剩余%d秒...", i)
time.Sleep(1e9)
}
}
