Excel solution with print level and Image

Exporting two-dimensional data Excel is actually very simple. You can use CVs. But what if the exported format is complex, with a style and an image? Customers' requirements are sometimes so abnormal. Haha. If you use. net, Microsoft provides libraries, and PHP also seems to have ready-made libraries. I generally understand these databases to meet our needs. Unfortunately, golang I am currently using is not so much library support, so I had to do it naked.

There are two types of EXCEL formats. The first one is closed and I don't know what format to use. For example, the format used by Office 2003 with the extension *. XSL. The other is open and uses open XML technology, such as Office 2007 and later versions. Fortunately, it is 2014 now. Over the past seven years, most people have used office 2012, even domestic WPS has already supported open XML perfectly. Therefore, you do not need to consider compatibility issues.

This time, my solution is to start with open XML. By learning open XML, the solution can be roughly divided into three steps:

Step 1: use software that supports open XML, such as office 2010 to create an Excel file and save the file name with the extension XLSX.

Step 2: Change the XLSX extension to zip, decompress it, use a placeholder, modify the corresponding XML file, compress it, and then change the extension to XLSX. This compressed file is called an export template.

Step 3: Use the program to decompress the package dynamically, replace the placeholder, and then compress the package. The processing of the image is to save the image to the corresponding folder and the corresponding XML, which is more straightforward than the code. The implementation code is as follows:

 

Func (C order) Excel (pageindex int, pagesize int, sortfield string, sortorder string, customid int64, State string, ordertime string) revel. result {SQL: = "SELECT. name a, B. name B, E. name C, D. name D, C. name E,. order_time F,. money G,. state H,. image I,. width J,. height K,. area L,. unit M,. amount N,. price O, F. alias P,. remarks Q from ad_order A, ad_custom B, ad_product C, ad_stuff D, ad_stuff_cat E, ad_user f Where. product_id = C. ID and C. stuff_id = D. ID and D. cat_id = E. ID and. custom_id = B. ID and. user_id = f. ID and. del_state = 'undeleted' % S % s order by. id DESC "SQL = FMT. sprintf (SQL, FMT. sprintf ("and. custom_id = % d ", customid)," % s "," % s ") if state! = "" {SQL = FMT. sprintf (SQL, FMT. sprintf ("and. state = '% S' ", State)," % s ")} else {SQL = FMT. sprintf (SQL, "", "% s")} If ordertime! = "" {SQL = FMT. sprintf (SQL, FMT. sprintf ("and. 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, "order name") sharedstrings = append (sharedstrings, "customer name") sharedstrings = append (sharedstrings, "product catalog ") sharedstrings = append (sharedstrings, "product materials") sharedstrings = append (sharedstrings, "Product Name") sharedstrings = append (sharedstrings, "order date") sharedstrings = append (sharedstrings, "amount") sharedstrings = append (sharedstrings, "billing status") sharedstrings = appen D (sharedstrings, "product image") sharedstrings = append (sharedstrings, "width (meters)") sharedstrings = append (sharedstrings, "height (meters )") sharedstrings = append (sharedstrings, "area") sharedstrings = append (sharedstrings, "unit") sharedstrings = append (sharedstrings, "quantity") sharedstrings = append (sharedstrings, "unit price ") sharedstrings = append (sharedstrings, "handled by") sharedstrings = append (sharedstrings, "Remarks") for I, row: = Ra Nge 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"]) // order name sharedstrings = append (sharedstrings, string (row ["B"]) // CUSTOMER NAME sharedstrings = append (sharedstrings, string (row ["C"]) // product directory sharedstrings = append (sharedstrings, string (row ["D"]) // product material sharedstrings = append (sharedstrings, string (row ["E"]) // product name sharedstrings = append (sharedstrings, string (row ["F"]) // order date F1, _: = strconv. parsefloat (string (row ["G"]), 32) sharedstrings = append (sharedstrings, FMT. sprintf ("%. 2f ", F1) // amount sharedstrings = append (sharedstrings, string (row [" H "]) // billing status sharedstrings = append (sharedstrings ,"") // product image F2, _: = strconv. parsefloat (string (row ["J"]), 32) sharedstrings = append (sharedstrings, FMT. sprintf ("%. 2f ", F2) // width (meters) F3, _: = strconv. parsefloat (string (row ["K"]), 32) sharedstrings = append (sharedstrings, FMT. sprintf ("%. 2f ", F3) // height (meters) F4, _: = strconv. parsefloat (string (row ["L"]), 32) sharedstrings = append (sharedstrings, FMT. sprintf ("%. 4f ", F4) // area sharedstrings = append (sharedstrings, string (row [" M "]) // unit sharedstrings = append (sharedstrings, string (row ["N"]) // quantity F5, _: = strconv. parsefloat (string (row ["O"]), 32) sharedstrings = append (sharedstrings, FMT. sprintf ("%. 2f ", F5) // unit price sharedstrings = append (sharedstrings, string (row [" p "]) // handled by sharedstrings = append (sharedstrings, string (row ["Q"]) // remarks} basepath: = revel. basepath basepathprefix: = fpath. join (basepath, fpath. fromslash ("app/templates") file, _: = OS. create (fpath. join (basepathprefix, fpath. fromslash (FMT. sprintf ("eclips.xlsx", "orders") W: = zip. newwriter (File) Defer W. close () R, _: = zip. openreader (fpath. join (basepathprefix, fpath. fromslash (FMT. sprintf ("maid", "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 )}

Finally, I spoke about it and inserted code in the blog garden. Since there is no golang.

Excel solution with print level and Image