java使用jxl进行excel文件的导出

jxl

jxl是通过java操作excel表格的工具类库。jxl实现原理也是poi，只不过jxl将poi的一些操作进行了封装，所以更显轻量简洁，还可以在excel指定单元格进行函数方式的格式化输出等高级操作。

引入maven依赖

		<dependency>
		    <groupId>net.sf.jxls</groupId>
		    <artifactId>jxls-core</artifactId>
		    <version>0.9.8</version>
		</dependency>
		<dependency>
		    <groupId>org.jxls</groupId>
		    <artifactId>jxls</artifactId>
		    <version>2.4.0</version>
		</dependency>
		<dependency>
		    <groupId>org.jxls</groupId>
		    <artifactId>jxls-poi</artifactId>
		    <version>1.0.9</version>
		</dependency>
		<!-- 扩展命令所需jar -->
		<dependency>
		    <groupId>org.jxls</groupId>
		    <artifactId>jxls-jexcel</artifactId>
		    <version>1.0.6</version>
		</dependency>
		<!-- jxls导入Excel -->
		<dependency>
		    <groupId>net.sf.jxls</groupId>
		    <artifactId>jxls-reader</artifactId>
		    <version>1.0.6</version>
		</dependency>

定义excel文件模板

1、第一行定义批注：如下格式，D3表示从第3行开始，最后一列为D，jx:area(lastCell=“D3”)

2、第三行定义批注，users是代码里面的集合变量名j，user表示循环的变量，然后在各列定义好该列指定的变量名，jx:each(items=“users” var=“user” lastCell=“D3”)var=“user” lastCell=“D3”)

代码实现

controller

模板存放位置：

controller

@RequestMapping(value = "/user/export")
public void userExport(HttpServletRequest request,HttpServletResponse response){
	
	Map<String , Object> model=new HashMap<String , Object>();
	Map<String, Object> funcs = new HashMap<String, Object>();
	funcs.put("utils", new ExportUtil());//定义时间格式化工具类
	String templatePath = "static/jxls/template/";
	ServletOutputStream os = null;
	try {
		response.addHeader("Content-Type","application/octet-stream");
		String userAgent = request.getHeader("USER-AGENT");
		String fileName = "xxxx信息.xlsx";
		String finalFileName = null;
		
		if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
			finalFileName = URLEncoder.encode(fileName, "UTF8");
		} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
			finalFileName = new String(fileName.getBytes(), "ISO8859-1");
		} else {
			finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
		}
		
		response.addHeader("Content-Disposition","attachment;filename=" + finalFileName);
		
		List<Map<String,Object>> userList = new ArrayList<>();
		
		userList.add(new HashMap<String,Object>(){
			{
			put("name","张三");
			put("sex","男");
			put("phone","13800138000");
			put("bornDate",new Date());
			}
		});
		
		model.put("users", userList);
		os = response.getOutputStream();
		
		JxlsUtils.exportExcel(templatePath,"user.xlsx", os, model, funcs);
		os.close();
		
	} catch (IOException e) {
		e.printStackTrace();
	}
}

时间格式化工具类

public class ExportUtil{
	
	public String getDateStr(Date date) {
		if (date != null) {
			return String.format("%tY-%tm-%td", date,date,date);
		}else {
			return "";
		}
	}
	
	public String getFommatIdCard(String idCard) {
		if (StringUtils.isNoneBlank(idCard) && idCard.length() > 8) {
			return new StringBuilder().append("********").append(idCard.substring(idCard.length()-8)).toString();
		}else {
			return "";
		}
	}
	
	public String getFommatPassNo(String passNo) {
		if (StringUtils.isNoneBlank(passNo) && passNo.length() > 4) {
			return new StringBuilder().append("****").append(passNo.substring(passNo.length()-4)).toString();
		}else {
			return "";
		}
	}
}

Jxls插件导出Excel工具类

在springboot打包成jar包的情况，需要通过ClassPathResource的方式来加载模板文件，直接使用流的方式进行输入输出。

public class JxlsUtils {

	public static Logger logger = LoggerFactory.getLogger(JxlsUtils.class);
	
	
	static{
        //添加自定义指令（可覆盖jxls原指令）
        XlsCommentAreaBuilder.addCommandMapping("each", EachCommand.class);
    }
	
	private static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException{
		exportExcel(is,os,model,null);
	}
	
	private static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model,Map<String, Object> funcs) throws IOException{
		Context context = new Context();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer  = jxlsHelper.createTransformer(is, os);
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
        evaluator.getJexlEngine().setFunctions(funcs);
        jxlsHelper.processTemplate(context, transformer);
	}
	
	/**
	 * 导出Excel方法(不支持自定义方法)
	 * @param templateFilePath	Excel模板存放路径
	 * @param templateName	Excel模板文件名称
	 * @param os    Excel文件输出流
	 * @param model	数据集Map集合
	 */
    public static void exportExcel(String templateFilePath,String templateName, OutputStream os, Map<String, Object> model) throws FileNotFoundException, IOException {
    	InputStream template = getTemplate(templateFilePath,templateName);
    	if(template!=null){
        	exportExcel(template, os, model);	
    	}
    	
    }
    
    /**
	 * 导出Excel方法(支持自定义方法)
	 * @param templateFilePath	Excel模板存放路径
	 * @param templateName	Excel模板文件名称
	 * @param os    Excel文件输出流
	 * @param model	数据集Map集合
	 * @param funcs	自定义方法Map集合
	 * funcs使用方法：
	 * 	Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("utils", new JxlsUtils());    //添加自定义功能，value值为实例化utils方法所在的类
	 */
    public static void exportExcel(String templateFilePath,String templateName, OutputStream os, Map<String, Object> model,Map<String, Object> funcs) throws FileNotFoundException, IOException {
    	InputStream template = getTemplate(templateFilePath,templateName);
    	if(template!=null){
    		exportExcel(template, os, model,funcs);	
    	}
    }
    
    /**
     * 获取jxls模版文件
     * @param templateFilePath 目标存放路径
     * @param templateName	模板名称
     * @return
     */
    public static InputStream getTemplate(String templateFilePath,String templateName){
    	
        ClassPathResource classPathResource = new ClassPathResource(templateFilePath + templateName);
        
        if(classPathResource.exists()){
        	try {
    	        InputStream input = classPathResource.getInputStream();
    	        
    	        return input;
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
        }
        
        return null;
    }
    
	
}

EachCommand

public class EachCommand extends AbstractCommand {
    public enum Direction {RIGHT, DOWN}
 
    private String var;
    private String items;
    private String select;
    private Area area;
    private Direction direction = Direction.DOWN;
    private CellRefGenerator cellRefGenerator;
    private String multisheet;
 
    private String retainEmpty; //当集合大小为0时，是否最少保留一行空行数据
 
    public EachCommand() {
    }
 
    /**
     * @param var       name of the key in the context to contain each collection items during iteration
     * @param items     name of the collection bean in the context
     * @param direction defines processing by rows (DOWN - default) or columns (RIGHT)
     */
    public EachCommand(String var, String items, Direction direction) {
        this.var = var;
        this.items = items;
        this.direction = direction == null ? Direction.DOWN : direction;
    }
 
    public EachCommand(String var, String items, Area area) {
        this(var, items, area, Direction.DOWN);
    }
 
    public EachCommand(String var, String items, Area area, Direction direction) {
        this(var, items, direction);
        if (area != null) {
            this.area = area;
            addArea(this.area);
        }
    }
 
    /**
     * @param var              name of the key in the context to contain each collection items during iteration
     * @param items            name of the collection bean in the context
     * @param area             body area for this command
     * @param cellRefGenerator generates target cell ref for each collection item during iteration
     */
    public EachCommand(String var, String items, Area area, CellRefGenerator cellRefGenerator) {
        this(var, items, area, (Direction) null);
        this.cellRefGenerator = cellRefGenerator;
    }
 
    /**
     * Gets iteration directino
     *
     * @return current direction for iteration
     */
    public Direction getDirection() {
        return direction;
    }
 
    /**
     * Sets iteration direction
     *
     * @param direction
     */
    public void setDirection(Direction direction) {
        this.direction = direction;
    }
 
    public void setDirection(String direction) {
        this.direction = Direction.valueOf(direction);
    }
 
    /**
     * Gets defined cell ref generator
     *
     * @return current {@link CellRefGenerator} instance or null
     */
    public CellRefGenerator getCellRefGenerator() {
        return cellRefGenerator;
    }
 
    public void setCellRefGenerator(CellRefGenerator cellRefGenerator) {
        this.cellRefGenerator = cellRefGenerator;
    }
 
    public String getName() {
        return "each";
    }
 
    /**
     * Gets current variable name for collection item in the context during iteration
     *
     * @return collection item key name in the context
     */
    public String getVar() {
        return var;
    }
 
    /**
     * Sets current variable name for collection item in the context during iteration
     *
     * @param var
     */
    public void setVar(String var) {
        this.var = var;
    }
 
    /**
     * Gets collection bean name
     *
     * @return collection bean name in the context
     */
    public String getItems() {
        return items;
    }
 
    /**
     * Sets collection bean name
     *
     * @param items collection bean name in the context
     */
    public void setItems(String items) {
        this.items = items;
    }
 
    /**
     * Gets current 'select' expression for filtering out collection items
     *
     * @return current 'select' expression or null if undefined
     */
    public String getSelect() {
        return select;
    }
 
    /**
     * Sets current 'select' expression for filtering collection
     *
     * @param select filtering expression
     */
    public void setSelect(String select) {
        this.select = select;
    }
 
    /**
     * @return Context variable name holding a list of Excel sheet names to output the collection to
     */
    public String getMultisheet() {
        return multisheet;
    }
 
    /**
     * Sets name of context variable holding a list of Excel sheet names to output the collection to
     * @param multisheet
     */
    public void setMultisheet(String multisheet) {
        this.multisheet = multisheet;
    }
 
    @Override
    public Command addArea(Area area) {
        if (area == null) {
            return this;
        }
        if (super.getAreaList().size() >= 1) {
            throw new IllegalArgumentException("You can add only a single area to 'each' command");
        }
        this.area = area;
        return super.addArea(area);
    }
 
    @SuppressWarnings("rawtypes")
    public Size applyAt(CellRef cellRef, Context context) {
        Collection itemsCollection = Util.transformToCollectionObject(getTransformationConfig().getExpressionEvaluator(), items, context);
        int width = 0;
        int height = 0;
        int index = 0;
        CellRefGenerator cellRefGenerator = this.cellRefGenerator;
        if (cellRefGenerator == null && multisheet != null) {
            List<String> sheetNameList = extractSheetNameList(context);
            cellRefGenerator = new SheetNameGenerator(sheetNameList, cellRef);
        }
        CellRef currentCell = cellRefGenerator != null ? cellRefGenerator.generateCellRef(index, context) : cellRef;
        JexlExpressionEvaluator selectEvaluator = null;
        if (select != null) {
            selectEvaluator = new JexlExpressionEvaluator(select);
        }
        for (Object obj : itemsCollection) {
            context.putVar(var, obj);
            context.putVar(var+"_index", index);
            if (selectEvaluator != null && !Util.isConditionTrue(selectEvaluator, context)) {
                context.removeVar(var);
                context.removeVar(var+"_index");
                continue;
            }
            Size size = area.applyAt(currentCell, context);
            index++;
            if (cellRefGenerator != null) {
                width = Math.max(width, size.getWidth());
                height = Math.max(height, size.getHeight());
                if(index < itemsCollection.size()) {
                    currentCell = cellRefGenerator.generateCellRef(index, context);
                }
            } else if (direction == Direction.DOWN) {
                currentCell = new CellRef(currentCell.getSheetName(), currentCell.getRow() + size.getHeight(), currentCell.getCol());
                width = Math.max(width, size.getWidth());
                height += size.getHeight();
            } else {
                currentCell = new CellRef(currentCell.getSheetName(), currentCell.getRow(), currentCell.getCol() + size.getWidth());
                width += size.getWidth();
                height = Math.max(height, size.getHeight());
            }
            context.removeVar(var);
            context.removeVar(var+"_index");
        }
        if("true".equalsIgnoreCase(retainEmpty) && width == 0 && height == 0){
            return area.applyAt(currentCell, context);
        }
        return new Size(width, height);
    }
 
    @SuppressWarnings("unchecked")
    private List<String> extractSheetNameList(Context context) {
        try {
            return (List<String>) context.getVar(multisheet);
        } catch (Exception e) {
            throw new JxlsException("Failed to get sheet names from " + multisheet, e);
        }
    }
 
    public String getRetainEmpty() {
        return retainEmpty;
    }
 
    public void setRetainEmpty(String retainEmpty) {
        this.retainEmpty = retainEmpty;
    }

 
}

MergeCommand

public class MergeCommand extends AbstractCommand {
    private String cols;    //合并的列数
    private String rows;    //合并的行数
    private Area area;
    private CellStyle cellStyle;    //第一个单元格的样式
 
    @Override
    public String getName() {
        return "merge";
    }
 
    @Override
    public Command addArea(Area area) {
        if (super.getAreaList().size() >= 1) {
            throw new IllegalArgumentException("You can add only a single area to 'merge' command");
        }
        this.area = area;
        return super.addArea(area);
    }
 
    @Override
    public Size applyAt(CellRef cellRef, Context context) {
        int rows = 1, cols = 1;
        if(StringUtils.isNotBlank(this.rows)){
            Object rowsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.rows, context.toMap());
            if(rowsObj != null && NumberUtils.isDigits(rowsObj.toString())){
                rows = NumberUtils.toInt(rowsObj.toString());
            }
        }
        if(StringUtils.isNotBlank(this.cols)){
            Object colsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.cols, context.toMap());
            if(colsObj != null && NumberUtils.isDigits(colsObj.toString())){
                cols = NumberUtils.toInt(colsObj.toString());
            }
        }
 
        if(rows > 1 || cols > 1){
            Transformer transformer = this.getTransformer();
            if(transformer instanceof PoiTransformer){
                return poiMerge(cellRef, context, (PoiTransformer)transformer, rows, cols);
            }else if(transformer instanceof JexcelTransformer){
                return jexcelMerge(cellRef, context, (JexcelTransformer)transformer, rows, cols);
            }
        }
        area.applyAt(cellRef, context);
        return new Size(1, 1);
    }
 
    protected Size poiMerge(CellRef cellRef, Context context, PoiTransformer transformer, int rows, int cols){
        Sheet sheet = transformer.getWorkbook().getSheet(cellRef.getSheetName());
        CellRangeAddress region = new CellRangeAddress(
                cellRef.getRow(),
                cellRef.getRow() + rows - 1,
                cellRef.getCol(),
                cellRef.getCol() + cols - 1);
        sheet.addMergedRegion(region);
 
        //合并之后单元格样式会丢失，以下操作将合并后的单元格恢复成合并前第一个单元格的样式
        area.applyAt(cellRef, context);
        if(cellStyle == null){
            PoiCellData cellData = (PoiCellData)transformer.getCellData(cellRef);
            cellStyle = cellData.getCellStyle();
        }
        setRegionStyle(cellStyle, region, sheet);
        return new Size(cols, rows);
    }
 
    protected Size jexcelMerge(CellRef cellRef, Context context, JexcelTransformer transformer, int rows, int cols){
    	try {
            transformer.getWritableWorkbook().getSheet(cellRef.getSheetName())
                .mergeCells(
                        cellRef.getRow(),
                        cellRef.getCol(),
                        cellRef.getRow() + rows - 1 ,
                        cellRef.getCol() + cols - 1);
            area.applyAt(cellRef, context);
        } catch (Exception e) {
            throw new IllegalArgumentException("合并单元格失败");
        }
        return new Size(cols, rows);
    }
 
    private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = sheet.getRow(i);
            if (row == null)
                row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                }
                cell.setCellStyle(cs);
            }
        }
    }
 
    public String getCols() {
        return cols;
    }
 
    public void setCols(String cols) {
        this.cols = cols;
    }
 
    public String getRows() {
        return rows;
    }
 
    public void setRows(String rows) {
        this.rows = rows;
    }
}