java使用jxl进行excel文件的导出
- jxl
- 引入maven依赖
- 定义excel文件模板
- 代码实现
- controller
- controller
- 时间格式化工具类
- Jxls插件导出Excel工具类
- EachCommand
- MergeCommand
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;
}
}