sofh7777 发表于 2018-9-20 09:28:57

golang csv,xls,xlsx

  要用到的包:
  "golang.org/x/text/encoding/simplifiedchinese"
  "golang.org/x/text/transform"
  func Decode(s []byte) ([]byte, error) {
  I := bytes.NewReader(s)
  O := transform.NewReader(I, simplifiedchinese.GBK.NewDecoder())
  d, e := ioutil.ReadAll(O)
  if e != nil {
  return nil, e
  }
  return d, nil
  }
  func ReadCsv(execlFileName string) {
  ProBar.Exist = 0
  ProBar.Sucess = 0
  ProBar.Fail = 0
  ProBar.Count = 0
  var (
  err      error
  sucess   int64
  fail       int64
  exist      int64
  errcontent string
  OrderTimetime.Time
  tradedata[]TradeData
  )
  cntb, err := ioutil.ReadFile(execlFileName)
  ress, err := Decode(cntb)
  if err != nil {
  beego.Error("change is err", err.Error())
  return
  }
  buffer := bytes.NewBuffer(ress)
  bufferline := bytes.NewBuffer(ress)
  var cols int
  for {
  s, err := bufferline.ReadString(byte('\n'))
  if err != nil {
  beego.Error("err", err)
  break
  } else {
  linestr := strings.Split(s, ",")
  if len(linestr) > 5 && len(linestr) > 5 {
  cols++
  }
  }
  }
  ProBar.TotalPro = int64((cols - 1) * 3)
  var linenum int
  for {
  s, err := buffer.ReadString(byte('\n'))
  if err != nil {
  beego.Error("err", err)
  break
  } else {
  if linenum > 0 {
  linestr := strings.Split(s, ",")
  if len(linestr) > 5 && len(linestr) > 5 {
  beego.Debug("linestr lenght:", len(linestr))
  var (
  trade TradeData
  )
  for i := 0; i < len(linestr); i++ {
  str := linestr
  if strings.TrimSpace(str) != "" {
  ProBar.CurrentPro++
  // for i := 0; i < len(str); i++ {
  if strings.TrimSpace(linestr) != "" {
  trimsrt := strings.TrimRight(strings.TrimLeft(linestr, `="`), `"`)
  beego.Debug("str : i", i, trimsrt)
  if i == 1 {
  customercode, _ := strconv.ParseFloat(trimsrt, 64) //客户账号
  trade.CustomerCode = int64(customercode)         //客户账号
  }
  if i == 2 {
  trade.CustomerName = trimsrt //账户名称
  }
  if i == 3 {
  trade.SuccessOrderCode = trimsrt //成交单号
  }
  if i == 4 {
  trade.CommodityCode = trimsrt //成交代码
  }
  if i == 5 {
  trade.CommodityName = trimsrt //商品名称
  }
  if i == 6 {
  switch trimsrt {
  case "买":
  trade.TradeType = 1 //交易类型(0-未知,1-买,2-卖)
  break
  case "卖":
  trade.TradeType = 2 //交易类型(0-未知,1-买,2-卖)
  break
  default:
  trade.TradeType = 0 //交易类型(0-未知,1-买,2-卖)
  if trade.TradeType == 0 {
  fail++
  beego.Debug("Transaction type error : ", linestr)
  errcontent = errcontent + strconv.Itoa(i) + "行买卖类型错误"
  break
  }
  }
  }
  if i == 7 {
  trade.OrderCode = trimsrt //委托单号
  }
  if i == 8 {
  trancount, _ := strconv.ParseFloat(trimsrt, 64) //成交数量
  trade.TranCount = int64(trancount)
  }
  if i == 9 {
  trade.TranPrice, _ = strconv.ParseFloat(trimsrt, 64) //成交价格
  }
  if i == 10 {
  trade.TranAmount, _ = strconv.ParseFloat(trimsrt, 64) //成交金额
  }
  if i == 11 {
  tranuser, _ := strconv.ParseFloat(trimsrt, 64) //交易员
  trade.TranUser = int64(tranuser)               //交易员
  }
  if i == 12 {
  loc, _ := time.LoadLocation("Local")                                             //重要:获取时区
  trade.TradeTime, err = time.ParseInLocation("2006-01-02 15:04:05", trimsrt, loc) //成交时间
  if err != nil {
  fail++
  beego.Debug("TradeTime wrong time : ", trimsrt)
  errcontent = errcontent + strconv.Itoa(i) + "行 时间格式错误"
  break
  }
  }
  if i == 13 {
  trade.ProfitLoss, _ = strconv.ParseFloat(trimsrt, 64) //转让盈亏
  }
  if i == 14 {
  trade.PoundAge, _ = strconv.ParseFloat(trimsrt, 64) //手续费
  }
  if i >= 15 && strings.TrimSpace(trimsrt) != "" {
  if i == 15 {
  trade.OrderPrice, _ = strconv.ParseFloat(trimsrt, 64) //订立价格
  }
  if i == 16 {
  loc, _ := time.LoadLocation("Local")
  OrderTime, err = time.ParseInLocation("2006-01-02 15:04:05", trimsrt, loc) //订立时间
  trade.OrderTime = OrderTime.Unix()
  if err != nil {
  fail++
  beego.Debug("TradeTime wrong time : ", trimsrt)
  errcontent = errcontent + strconv.Itoa(i) + "行 时间格式错误"
  break
  }
  }
  }
  }
  }
  }
  beego.Debug("trade", linenum, trade)
  checktime := trade.TradeTime.Format("2006-01-02") //登入的时间
  IsExist := CheckIsTradeData(checktime)
  if !IsExist {
  tradedata = append(tradedata, trade)
  sucess++
  } else {
  exist++
  beego.Debug("Database duplicate data : ", trade)
  errcontent = errcontent + strconv.Itoa(linenum) + "行 数据库重复"
  }
  }
  }
  time.Sleep(time.Millisecond * 0)
  }
  linenum++
  }
  if sucess > 0 {
  var commitfail int64
  sucess, commitfail = AddTradeData(tradedata, sucess)
  if commitfail > 0 {
  fail = 0
  sucess = 0
  commitfail = int64(cols - 1)
  }
  fail += commitfail
  os.Remove(execlFileName) //删除文件
  }
  ProBar.Sucess = sucess
  ProBar.Fail = fail
  ProBar.Exist = exist
  ProBar.Count = int64(cols - 1)
  ProBar.ErrorContent = errcontent
  ProBar.Status = false
  ProBar.CurrentPro = ProBar.TotalPro
  }
  func ReadXls(excelFileName string) (res [][]string) {
  xlFile, err := xls.Open(excelFileName, "utf-8")
  if err == nil {
  beego.Debug(excelFileName, " file data read...................")
  for i := 0; i < xlFile.NumSheets(); i++ {
  sheet := xlFile.GetSheet(i)
  temp := make([][]string, sheet.MaxRow+1)
  for k, row := range sheet.Rows {
  data := make([]string, 0)
  for _, col := range row.Cols {
  if uint16(len(data))
页: [1]
查看完整版本: golang csv,xls,xlsx