按照指定模板导出EXCEL

1. 设置占位符的数值
   
2. EXCEL导出工具类

package com.report.util.excle2;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Number;
import jxl.write.*;
import org.springframework.core.io.ClassPathResource;
import sun.misc.BASE64Encoder;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelTool {
    /**
     * 导出excel
     * @param year 年份
     * @param other 其他内容
     * @param path 模板路径
     * @param list 数据集合List
     * @return
     */
    public static void exportExcel(String year, String other, String path, List<?> list, HttpServletRequest request, HttpServletResponse response, String fileName) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();   //声明一个map
        map.put("year", year);
        map.put("other", other);
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        map.put("yyyy-MM-dd", sdf.format(date));

        map.put("list", list);
        /**这是循环结束了*/
            //这个最主要，是导出Excel的方法
        exportExcel(path, map, request, response ,fileName);

    }

    /**
     * 汉字文件名称
     * @param fileName
     * @param request
     * @return
     * @throws UnsupportedEncodingException
     */
    private static String getFileName(String fileName , HttpServletRequest request) throws UnsupportedEncodingException {

        String userAgent = request.getHeader("User-Agent");
        if ("Edge".contains(userAgent)) {
            fileName = URLEncoder.encode(fileName, "UTF-8").replace("+", "%20");
        }else if ("Chrome".contains(userAgent)) {
            fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
        }else if ("Firefox".contains(userAgent)) {
            fileName = "=?utf-8?b?"+new BASE64Encoder().encode(fileName.getBytes("utf-8"))+"?=";
        }else if ("Trident".contains(userAgent)) {
            fileName = URLEncoder.encode(fileName, "UTF-8").replace("+", "%20");
        }else {
            fileName = new String(fileName.getBytes("gbk"),"ISO8859-1");
        }
        return fileName;
    }

    /**
     * 导出 Excel
     * 
     * @param template
     *            Excel模板路径
     * @param datas
     *            数据
     * @return
     */
    private static void exportExcel(String template, Map<String, Object> datas, HttpServletRequest request, HttpServletResponse response, String fileName) throws Exception {
        ServletOutputStream out = null;
        BufferedOutputStream bos = null;
        BufferedInputStream bis = null;
        // 获取路径下的模板
        ClassPathResource classPathResource = new ClassPathResource(template);
        InputStream is = classPathResource.getInputStream();
        try {
            if (is != null) {
                Workbook book = Workbook.getWorkbook(is);
                File tempFile = File.createTempFile("temp", ".xls");
                
                WritableWorkbook wWorkbook = Workbook.createWorkbook(tempFile, book);

                /** 处理【表达式】类型的数据。 **/
                generateExpData(book, wWorkbook, datas);
                /** 处理【循环结果集】类型的数据。 **/
                generateEachData(book, wWorkbook, datas);

                wWorkbook.write();
                wWorkbook.close();

                bis = new BufferedInputStream(new FileInputStream(tempFile));

                response.addHeader("Content-Disposition", "attachment; filename=" + getFileName(fileName, request));
                response.addHeader("Content-Type","application/octet-stream;charset=utf-8");

                out=response.getOutputStream();

                byte[] data = new byte[1024];
                int bytes = 0;
                bos = new BufferedOutputStream(out);
                while ((bytes = bis.read(data, 0 , data.length)) != -1) {
                    bos.write(data, 0, bytes);
                }
                bos.flush();

            }
        }  catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally{
            if(bos!=null){
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(out!=null){
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(bis!=null){
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(is!=null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 处理【表达式】类型的数据。
     * 
     * @param book
     *            【模板】对象
     * @param wWorkbook
     *            根据模板创建的【新数据文件】对象
     */
    private static void generateExpData(Workbook book, WritableWorkbook wWorkbook, Map<String, Object> datas) throws Exception {
        List<ExcelCells> expcells = search("${", book);
        for (ExcelCells cell : expcells) {
            wWorkbook.getSheet(cell.getSheetIndex()).addCell(getValueByExp(cell, datas));
        }
    }


    /**
     * 处理【循环结果集】类型的数据
     * 
     * @param book
     *            【模板】对象
     * @param wWorkbook
     *            根据模板创建的【新数据文件】对象
     */
    private static void generateEachData(Workbook book, WritableWorkbook wWorkbook, Map<String, Object> datas) throws Exception {

        List<ExcelCells> each = search("each.", book);
        /* 先对模板列对象，进行分组。 */
        Map<String, List<ExcelCells>> map = new LinkedHashMap<String, List<ExcelCells>>();//
        for (ExcelCells cell : each) {
            String[] array = cell.getCell().getContents().trim().split("\\.");
            if (array.length >= 3) {
                String key = array[0] + "." + array[1];
                List<ExcelCells> list = map.get(key);
                if (list == null) {
                    list = new ArrayList<ExcelCells>();
                    map.put(key, list);
                }
                list.add(cell);
            }
        }

        Iterator<String> iterator = map.keySet().iterator();

        int insertrow = 0;//标识当前工作表新增了多少条数据。
        int lastSheetIndex = -1;//标识上一次工作表的下标。

        while (iterator.hasNext()) {
            String key = iterator.next();
            List<ExcelCells> list = map.get(key);
            int sheetIndex = list.get(0).getSheetIndex();// 获取面板下标。
            //当切换工作表事  insertrow 清 0
            if(lastSheetIndex != -1 && lastSheetIndex != sheetIndex) insertrow = 0;
            lastSheetIndex = sheetIndex;
            
            int startRow = list.get(0).getCell().getRow() + insertrow;// 获取开始行下标。

            String[] array = list.get(0).getCell().getContents().trim().split("\\.");
            if (array.length > 0) {
                String indexName = array[1];
                Object data = datas.get(indexName);
                if (data != null && !data.getClass().getName().equals(List.class.getName()) && !data.getClass().getName().equals(ArrayList.class.getName())) {
                    throw new Exception("数据：" + indexName + "不是一个集合类！");
                }
                List<Object> rowsData = (List<Object>) data;
                // 有数据时。
                if (rowsData != null && rowsData.size() > 0) {
                    for (int i = 0; i < rowsData.size(); i++) {
                        /* 第一行数据，覆盖模板位置，所以不需要创建新行 */
                        if (i == 0) {
                            for (ExcelCells cell : list) {
                                wWorkbook.getSheet(sheetIndex).addCell(getValueByEach(cell, rowsData.get(i), startRow, cell.getCell().getColumn()));
                            }
                            continue;
                        }
                        /* 创建新行 */
                        wWorkbook.getSheet(sheetIndex).insertRow(startRow + i);
                        for (ExcelCells cell : list) {
                            wWorkbook.getSheet(sheetIndex).addCell(getValueByEach(cell, rowsData.get(i), startRow + i, cell.getCell().getColumn()));
                        }
                        insertrow++;
                    }

                } else {  // 无数据时。
                    for (ExcelCells cell : list) {
                        wWorkbook.getSheet(sheetIndex).addCell(getValueByEach(cell, null, startRow, cell.getCell().getColumn()));
                    }
                }
            }
        }

    }

    /**
     * 根据【表达式】从数据集中获取相应数据。
     * 
     * @param cells
     *            表达式
     * @param datas
     *            数据集
     * @return
     */
    private static WritableCell getValueByExp(ExcelCells cells, Map<String, Object> datas) {
        WritableCell writableCell = null;

        List<Object> values = new ArrayList<Object>();
        List<String> exps = cells.getExps();// 获取表达式集合。

        String old_c = cells.getCell().getContents();// 模板原内容。

        for (String exp : exps) {

            String[] names = exp.replace("${", "").replace("}", "").split("\\.");

            Object object = null;
            for (String name : names) {
                if (object == null)
                    object = ObjectCustomUtil.getValueByFieldName(name, datas);
                else
                    object = ObjectCustomUtil.getValueByFieldName(name, object);
            }
            // ${asd.sdfa}
            if (!old_c.isEmpty()) {
                while (old_c.indexOf(exp) != -1)
                    old_c = old_c.replace(exp, object.toString());
            }
        }

        writableCell = getWritableCellByObject(cells.getCell().getRow(), cells.getCell().getColumn(), old_c);
        writableCell.setCellFormat(cells.getCell().getCellFormat());

        return writableCell;
    }

    /**
     * 根据【Each表达式】从数据集中获取相应数据。
     * 
     * @param cells
     *            表达式
     * @param datas
     *            数据集
     * @return
     */
    private static WritableCell getValueByEach(ExcelCells cells, Object datas, int rows, int column) {
        WritableCell writableCell = null;

        if (datas != null) {
            List<Object> values = new ArrayList<Object>();
            String[] exps = cells.getCell().getContents().trim().split("\\.");// 获取表达式集合。

            Object object = null;
            for (int i = 2; i < exps.length; i++) {
                if (object == null)
                    object = ObjectCustomUtil.getValueByFieldName(exps[i], datas);
                else
                    object = ObjectCustomUtil.getValueByFieldName(exps[i], object);
            }
            writableCell = getWritableCellByObject(rows, column, object);
        } else {
            writableCell = getWritableCellByObject(rows, column, null);
        }
        writableCell.setCellFormat(cells.getCell().getCellFormat());

        return writableCell;
    }

    /**
     * 根据提供的【列标】、【行标】、【对象值】构建一个Excel列对象。
     * 
     * @param beginRow
     *            【行标】
     * @param beginColumn
     *            【列标】
     * @param obj
     *            【对象值】
     * @return
     */
    private static WritableCell getWritableCellByObject(int beginRow, int beginColumn, Object obj) {
        WritableCell cell = null;

        if (obj == null)
            return new Label(beginColumn, beginRow, "");
        if (obj.getClass().getName().equals(String.class.getName())) {
            cell = new Label(beginColumn, beginRow, obj.toString());
        } else if (obj.getClass().getName().equals(int.class.getName()) || obj.getClass().getName().equals(Integer.class.getName())) {
            // jxl.write.Number
            cell = new Number(beginColumn, beginRow, Integer.parseInt(obj.toString()));
        } else if (obj.getClass().getName().equals(float.class.getName()) || obj.getClass().getName().equals(Float.class.getName())) {
            cell = new Number(beginColumn, beginRow, Float.parseFloat(obj.toString()));
        } else if (obj.getClass().getName().equals(double.class.getName()) || obj.getClass().getName().equals(Double.class.getName())) {
            cell = new Number(beginColumn, beginRow, Double.parseDouble(obj.toString()));
        } else if (obj.getClass().getName().equals(long.class.getName()) || obj.getClass().getName().equals(Long.class.getName())) {
            cell = new Number(beginColumn, beginRow, Long.parseLong(obj.toString()));
        } else if (obj.getClass().getName().equals(Date.class.getName())) {
            cell = new DateTime(beginColumn, beginRow, (Date)obj);
        } else {
            cell = new Label(beginColumn, beginRow, obj.toString());
        }
        return cell;
    }

    /**
     * 查找某字符第一次出现的位置。
     * 
     * @param text
     *            【文本】
     * @param book
     *            【Excel对象】
     * @return
     */
    private static ExcelCells searchFirstText(String text, Workbook book) {
        ExcelCells Rcell = null;
        Sheet[] sheets = book.getSheets();
        if (sheets != null) {
            int sheetIndex = 0;
            for (Sheet sheet : sheets) {
                if (sheet != null) {
                    int rows = sheet.getRows();
                    if (rows > 0) {
                        for (int i = 0; i < rows; i++) {
                            Cell[] cells = sheet.getRow(i);
                            if (cells != null) {
                                for (Cell cell : cells) {
                                    if (cell != null && !StringUtils.isNull(cell.getContents())) {
                                        String contents = cell.getContents();
                                        if (contents.equals(text))
                                            return new ExcelCells(sheet, cell, sheetIndex);
                                    }
                                }
                            }
                        }
                    }
                }
                sheetIndex++;
            }
        }
        return Rcell;
    }

    /**
     * 查找包含某字符所有的列对象。
     * 
     * @param text
     *            【文本】
     * @param book
     *            【Excel对象】
     * @return
     */
    private static List<ExcelCells> search(String text, Workbook book) {
        List<ExcelCells> rcells = new ArrayList<ExcelCells>();

        Sheet[] sheets = book.getSheets();
        if (sheets != null)
            for (Sheet sheet : sheets) {
                if (sheet != null) {
                    int rows = sheet.getRows();
                    if (rows > 0) {
                        for (int i = 0; i < rows; i++) {
                            Cell[] cells = sheet.getRow(i);
                            if (cells != null) {
                                for (Cell cell : cells) {
                                    if (cell != null && !StringUtils.isNull(cell.getContents())) {
                                        String contents = cell.getContents();
                                        if (contents.indexOf(text) != -1)
                                            rcells.add(new ExcelCells(sheet, cell));
                                    }
                                }
                            }
                        }
                    }
                }
            }
        return rcells;
    }

}

3. 模板样式
   
4. POM

       <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
        </dependency>
       <!-- 引入poi，解析workbook视图 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>        

5. 总结：不是原创。结合自己整理。