Springmvc generate Excel directly in memory to allow users to download directly in the browser using the

1. How to query the schema of the database, where the core SQL statements are posted

SELECT column_name as field, column_comment as Commentfrom information_schema. Columnswhere table_name =?

Because some of the comments will be very long, all taken out as a grid of Excel, it will be very bad, in fact, we just need important meaning, so, here is the precondition is to separate the comments with separators

For example: name=> Name: User nickname, here I can use ":" to split, only remove the "name" to. So, a convenient thing is to need a lot of agreement, especially in team work.

To get the field name of a table, and a comment, a common class is extracted:

/** * Created with Antnest-platform * User:Vernon.Chen * DATE:2015/3/16 * time:17:03 * * @Servicepublic class Queryschema    {@Resource private jdbctemplate jdbctemplate; Public list<map<string, object>> getschemabytablename (String tableName) {if (Stringutils.isblank (table        Name) {return null;        } list<map<string, object>> schema = new arraylist<map<string, object>> ();        StringBuilder sqlsb = new StringBuilder ();        Sqlsb.append ("Select column_name as field, column_comment as COMMENT"); Sqlsb.append ("from INFORMATION_SCHEMA.        COLUMNS ");        Sqlsb.append ("WHERE table_name =?");        schema = Jdbctemplate.queryforlist (Sqlsb.tostring (), New Object[]{tablename});                if (schema! = null && schema.size () > 0) {for (map<string, object> Map:schema) {                String comment = (string) map.get ("comment"); if (Stringutils.isnotblank (comment)&& comment.indexof (":") >-1) {map.put ("comment", comment.substring (0, Comment.indexof (":")                ));    }}} return schema; }}

The result of this return is a list, in which each map is a {"field": "xxx", "comment": "xxx"}.

Here's how to build Excel? is also a relatively general step. Generate Excel I know that there are 2 jar packages, one is Jxl.jar and the other is the POI used in the project.

<dependency>                <groupId>org.apache.poi</groupId>                <artifactid>poi</artifactid >                <version>${poi.version}</version>            </dependency>

Here is my version:

< poi.version >3.10-final</poi.version>

Then take a look at a code that assembles Excel. Because you do not need to persist the generated files locally, it is good to return directly to byte[].

@Override public    byte[] Selectexcel () throws Exception {        bytearrayoutputstream out = null;        try {            Hssfworkbook workbook = new Hssfworkbook ();            Generateexcelforas (Cellmapper.selectexcel (), workbook);            out = new Bytearrayoutputstream ();            Hssfworkbook HSSWB = (hssfworkbook) workbook;            Hsswb.write (out);        } catch (IOException e) {            e.printstacktrace ();        } finally {            if (out! = null) {                try {                    out.close (); c14/>} catch (IOException e) {                    e.printstacktrace ();}}        }        return Out.tobytearray ();    }

Iteration builds each row

private void Generateexcelforas (list<map<string, object>> list, Hssfworkbook workbook) {if (list = = Nu ll | |        List.size () = = 0) {return;        } list<map<string, object>> title = Queryschema.getschemabytablename ("T_cell");        int excelrow = 0;            try {hssfsheet sheet = workbook.createsheet ();  for (int i = 0; i < list.size (); i++) {if (i = = 0) {map<string, object> first                    = List.get (0);                    Add caption int column = 0;                    Hssfrow row = Sheet.createrow (Excelrow); For (map<string, object> map:title) {Row.createcell (column). Setcellvalue (Map.get ("comment"                        ). ToString ());                    column++;                    } excelrow++;                    column = 0;  You also need to add the 1th row of data, starting from 0 row = Sheet.createrow (Excelrow);                  For (map<string, object> map:title) {if (!first.containskey (Map.get ("Fie                        LD ")) {column++; } else {String value = First.get (Map.get ("field")) = = null?                            "": First.get (Map.get ("field")). ToString ();                            Row.createcell (column). Setcellvalue (value);                        column++;                }} excelrow++;                    } else {Hssfrow row = Sheet.createrow (Excelrow);                    map<string, object> rowmap = List.get (i);                    int column = 0;                            For (map<string, object> map:title) {if (!rowmap.containskey (Map.get ("field"))) {                        column++; } else {String value = Rowmap.get (Map.get ("field")) = = null? "": Rowmap.get (MAP.GET ("field")). ToString ();                            Row.createcell (column). Setcellvalue (value);                        column++;                }} excelrow++;        }}} catch (Exception e) {e.printstacktrace (); }    }

The code is less efficient and inefficient, because it needs to be generated in the order specified. Maybe there is a better way, I just have to finish the demand on time.

This returns the desired byte[].

The final step is to return to the stream in the response.

@RequestMapping (value = "/xxx") public    Void cell (HttpServletResponse response) throws Exception {        byte[] bytes = Cellservice.selectexcel ();        Response.setcontenttype ("Application/x-msdownload");        Response.setheader ("Content-disposition", "attachment;filename=" + uuidutil.getuuid () + ". xls");        Response.setcontentlength (bytes.length);        Response.getoutputstream (). write (bytes);        Response.getoutputstream (). Flush ();        Response.getoutputstream (). Close ();    }

Here is the note:

response. setContentType ("Application/x-msdownload");

response. SetHeader ("content-disposition"+ uuidutil.  Getuuid". xls");

One is to tell the browser to download, the next one is to tell the process to download the file name. Here I use the UUID.

So far, it's all OK.

Springmvc generate Excel directly in memory to allow users to download directly in the browser using the