2016年12月19日 星期一

导出带图片的Excel

导出二维数据excel,其实很简单,使用cvs就可以了。但是如果导出格式复杂还带样式还带图片的怎么办?客户有时就是这部变态。呵呵。如果是用.net,微软提供的有库,使用php也提供的有库。大致了解一下,都可以实现带图片。可惜我现在使用的golang没有那么库支持。只能裸搞了。


excel应该分两种,一种是office 2007以前的和office 2007以后的,office 2007 以后的版本和现在wps都支持Open XML 了。2007年到现在都7年了,所以这次我们就使用Open XML 技术解决我们的问题。

通过对Open XML 文档学习,大致实现步骤可以分三步:
1、使用office 2010制作一个我们想要样式的excel。

2、解压后使用占位符,修改里面相应的XML文件,然后压缩,这个压缩文件称为模板.

3、程序解压这个模板,使用查询的数据替换里面的占位符,最后再压缩,这个压缩文件就是我们最终的结果。
func (c Order) Excel(pageIndex int, pageSize int, sortField string, sortOrder string, customId int64, state string, orderTime string) revel.Result {
	sql := "select a.name A,b.name B,e.name C,d.name D,c.name E,a.order_time F,a.money G,a.state H,a.image I,a.width J,a.height K,a.area L,a.unit M,a.amount N,a.price O,f.alias P,a.remarks Q from ad_order a,ad_custom b,ad_product c,ad_stuff d,ad_stuff_cat e,ad_user f where a.product_id=c.id and c.stuff_id=d.id and d.cat_id=e.id and a.custom_id=b.id and a.user_id=f.id and a.del_state='未删' %s %s %s order by a.id desc"
	sql = fmt.Sprintf(sql, fmt.Sprintf("and a.custom_id=%d", customId), "%s", "%s")
	if state != "" {
		sql = fmt.Sprintf(sql, fmt.Sprintf("and a.state='%s'", state), "%s")
	} else {
		sql = fmt.Sprintf(sql, "", "%s")
	}
	if orderTime != "" {
		sql = fmt.Sprintf(sql, fmt.Sprintf("and a.order_time='%s'", orderTime))
	} else {
		sql = fmt.Sprintf(sql, "")
	}
	orders, err := Orm.Query(sql)
	if err != nil {
		return c.RenderJson(models.Message{State: "failure", Msg: err.Error()})
	}
	rows := make([]Rows, 0)
	col := 17
	for i := 1; i <= len(orders); i++ {
		row := Rows{}
		row.RowId = i + 1
		for k := 0; k < col; k++ {
			row.Columns = append(row.Columns, Columns{R: fmt.Sprintf("%s%d", string(ABC[k]), i+1), V: i*col + k})
		}
		rows = append(rows, row)
	}
	vmlDrawings := make([]VmlDrawing, 0)
	sharedStrings := make([]string, 0)
	sharedStrings = append(sharedStrings, "订单名称")
	sharedStrings = append(sharedStrings, "客户名称")
	sharedStrings = append(sharedStrings, "产品目录")
	sharedStrings = append(sharedStrings, "产品材料")
	sharedStrings = append(sharedStrings, "产品名称")
	sharedStrings = append(sharedStrings, "订单日期")
	sharedStrings = append(sharedStrings, "金额")
	sharedStrings = append(sharedStrings, "收费状态")
	sharedStrings = append(sharedStrings, "产品图片")
	sharedStrings = append(sharedStrings, "宽度(米)")
	sharedStrings = append(sharedStrings, "高度(米)")
	sharedStrings = append(sharedStrings, "面积")
	sharedStrings = append(sharedStrings, "单位")
	sharedStrings = append(sharedStrings, "数量")
	sharedStrings = append(sharedStrings, "单价")
	sharedStrings = append(sharedStrings, "经手人")
	sharedStrings = append(sharedStrings, "备注")
	for i, row := range orders {
		if string(row["I"]) != "" {
			img := string(row["I"])
			vmlDrawing := VmlDrawing{}
			vmlDrawing.Index = i
			vmlDrawing.Id = img[:strings.Index(img, ".")]
			vmlDrawing.Name = fmt.Sprintf("S%s", img)
			vmlDrawing.RowBegin = i + 1
			vmlDrawing.RowEnd = i + 2
			vmlDrawings = append(vmlDrawings, vmlDrawing)
		}
		sharedStrings = append(sharedStrings, string(row["A"])) //订单名称
		sharedStrings = append(sharedStrings, string(row["B"])) //客户名称
		sharedStrings = append(sharedStrings, string(row["C"])) //产品目录
		sharedStrings = append(sharedStrings, string(row["D"])) //产品材料
		sharedStrings = append(sharedStrings, string(row["E"])) //产品名称
		sharedStrings = append(sharedStrings, string(row["F"])) //订单日期
		f1, _ := strconv.ParseFloat(string(row["G"]), 32)
		sharedStrings = append(sharedStrings, fmt.Sprintf("%.2f", f1)) //金额
		sharedStrings = append(sharedStrings, string(row["H"]))        //收费状态
		sharedStrings = append(sharedStrings, "")                      //产品图片
		f2, _ := strconv.ParseFloat(string(row["J"]), 32)
		sharedStrings = append(sharedStrings, fmt.Sprintf("%.2f", f2)) //宽度(米)
		f3, _ := strconv.ParseFloat(string(row["K"]), 32)
		sharedStrings = append(sharedStrings, fmt.Sprintf("%.2f", f3)) //高度(米)
		f4, _ := strconv.ParseFloat(string(row["L"]), 32)
		sharedStrings = append(sharedStrings, fmt.Sprintf("%.4f", f4)) //面积
		sharedStrings = append(sharedStrings, string(row["M"]))        //单位
		sharedStrings = append(sharedStrings, string(row["N"]))        //数量
		f5, _ := strconv.ParseFloat(string(row["O"]), 32)
		sharedStrings = append(sharedStrings, fmt.Sprintf("%.2f", f5)) //单价
		sharedStrings = append(sharedStrings, string(row["P"]))        //经手人
		sharedStrings = append(sharedStrings, string(row["Q"]))        //备注
	}
	basePath := revel.BasePath
	basePathPrefix := fpath.Join(basePath, fpath.FromSlash("app/templates"))
	file, _ := os.Create(fpath.Join(basePathPrefix, fpath.FromSlash(fmt.Sprintf("%s.xlsx", "orders"))))
	w := zip.NewWriter(file)
	defer w.Close()
	r, _ := zip.OpenReader(fpath.Join(basePathPrefix, fpath.FromSlash(fmt.Sprintf("%s.xlsx", "order"))))
	defer r.Close()
	for _, f := range r.File {
		switch f.Name {
		case "xl/worksheets/sheet1.xml":
			buf := new(bytes.Buffer)
			rc, _ := f.Open()
			data, _ := ioutil.ReadAll(rc)
			rc.Close()
			tmpl, _ := template.New("sheet").Parse(string(data))
			tmpl.Execute(buf, rows)
			ff, _ := w.Create(f.Name)
			ff.Write(buf.Bytes())
			break
		case "xl/sharedStrings.xml":
			buf := new(bytes.Buffer)
			rc, _ := f.Open()
			data, _ := ioutil.ReadAll(rc)
			rc.Close()
			tmpl, _ := template.New("sharedStrings").Parse(string(data))
			tmpl.Execute(buf, sharedStrings)
			ff, _ := w.Create(f.Name)
			ff.Write(buf.Bytes())
			break
		case "xl/drawings/_rels/vmlDrawing1.vml.rels":
			buf := new(bytes.Buffer)
			rc, _ := f.Open()
			data, _ := ioutil.ReadAll(rc)
			rc.Close()
			tmpl, _ := template.New("vmlDrawing1.vml").Parse(string(data))
			tmpl.Execute(buf, vmlDrawings)
			ff, _ := w.Create(f.Name)
			ff.Write(buf.Bytes())
			break
		case "xl/drawings/vmlDrawing1.vml":
			buf := new(bytes.Buffer)
			rc, _ := f.Open()
			data, _ := ioutil.ReadAll(rc)
			rc.Close()
			tmpl, _ := template.New("vmlDrawing1").Parse(string(data))
			tmpl.Execute(buf, vmlDrawings)
			ff, _ := w.Create(f.Name)
			ff.Write(buf.Bytes())
			basePath := revel.BasePath
			basePathPrefix := fpath.Join(basePath, fpath.FromSlash("public/upload"))
			for _, v := range vmlDrawings {
				fsmall := fpath.Join(basePathPrefix, fpath.FromSlash(v.Name))
				file, _ := os.Open(fsmall)
				data, _ := ioutil.ReadAll(file)
				ff, _ := w.Create(fmt.Sprintf("xl/media/%s", v.Name))
				ff.Write(data)
			}
			break
		default:
			ff, _ := w.Create(f.Name)
			rc, _ := f.Open()
			data, _ := ioutil.ReadAll(rc)
			ff.Write(data)
			rc.Close()
		}
	}
	return c.RenderFile(file, revel.Attachment)
}