首页 > 代码库 > 实现打印级别且带图片的Excel 方案

实现打印级别且带图片的Excel 方案

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

Excel格式分为两种,第一种是封闭式,不知道使用啥格式,比如office 2003使用的格式,扩展名为*.xsl。另一种是开放式的,使用的是Open XML技术,比如office 2007 以后的版本,好在现在已经2014了,7年过去,大部分人都已经用到office 2012,即使国产的wps也早已经完美支持Open XML 了。 所以不用考虑兼容问题了。

这次我的解决方案就是从Open XML 入手。通过对Open XML学习,解决思路大致可分为3步骤:

第一步:使用支持Open XML 的软件,比如 office 2010制作一个我们想要的Excel,保存扩展名名为xlsx。

第二步:把xlsx修改扩展名为 zip,解压后使用占位符,修改里面相应的XML文件,然后压缩,再把扩展名修改为xlsx。这个压缩文件我们可称为导出模板。

第三步:使用程序动态解压,替换占位符,再压缩。其中对图片的处理,是把图片保存到相应文件夹以及相应XML,千言万语,不如代码更加直接,实现代码如下:

 

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)}

最后吐槽一下,博客园插入code,既然没有golang。

实现打印级别且带图片的Excel 方案