日前做一个项目要用到这个方法,这个方法在网上有人做过,不过都存在一些小问题,实际用起来也花费了一点时间,特此记录一下。
首先,为了项目的整体架构,这个方法的主要功能被封装到了Utils类中:
package com.shxt.framework.commons.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class ExcelUtil {
private ExcelUtil() {}//私有构造函数
private final String excel2003L = ".xls";//2003- 版本的excel
private final String excel2007U = ".xlsx";//2007+ 版本的excel
/**
* 根据文件后缀,自适应上传文件的版本
*/
private Workbook getWorkbook(InputStream inStr, String filename) throws Exception {
Workbook workbook;
String fileType = filename.substring(filename.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
workbook = new HSSFWorkbook(inStr);//2003-
} else if (excel2007U.equals(fileType)) {
workbook = new XSSFWorkbook(inStr);//2007+
} else {
throw new Exception("解析的文件格式有误");
}
return workbook;
}
/**
* 获取excel中的数据
*/
public List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {
List<List<Object>> list;
//创建Excel工作薄
Workbook work = getWorkbook(in, filename);
if (null == work) {
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = (Row) sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
//通过getCellValue方法获取当前行每一列中的数据
li.add(getCellValue(cell));
}
//将每一行的数据添加到list
list.add(li);
}
}
in.close();
return list;
}
/**
* 获取每个单元格的内容
*/
private Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
/**
* 数据转成excel
*
* @param dataList 需要转换的数据
* @param sheetName 生成的excel表名
* @param columnName 每一列的名字(key)
* @return
*/
public Workbook dataToExcel(List<Map<String, String>> dataList, String sheetName, String[] columnName) {
int columnNum = columnName.length;
Workbook workbook = new HSSFWorkbook();
//创建第一页,并命名
Sheet sheet = workbook.createSheet(sheetName);
//创建第一行
Row row = (Row) sheet.createRow(0);
// 创建两种单元格格式
CellStyle cs = workbook.createCellStyle();
CellStyle cs2 = workbook.createCellStyle();
// 创建两种字体
Font f = workbook.createFont();
Font f2 = workbook.createFont();
// 创建第一种字体样式(用于列名)
//f.setBoldweight(Font.BOLDWEIGHT_BOLD);
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
//设置列名
for (int i = 0; i < columnNum; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnName[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
int rowNum = dataList.size();
for (int j = 1; j <= rowNum; j++) {
//创建一行
Row r = sheet.createRow(j);
for (int k = 0; k < columnNum; k++) {
Cell cell = r.createCell(k);
cell.setCellValue(dataList.get(j - 1).get(columnName[k]));
cell.setCellStyle(cs2);
}
}
return workbook;
}
public static final ExcelUtil getInstance() {
return ExcelUtilHolder.instance;
}
private static final class ExcelUtilHolder {
private static final ExcelUtil instance = new ExcelUtil();
}
}
这其中将列名设置成加粗体的方法失效了,还没有去找替代方法,如果有需要改字体的建议POI查询官方文档
这其中的关键方法是dataToExcel这个方法,可以看到传递的参数是listMap形式的,最开始我试着通过MyBaties直接直接输出listMap形式的结果集,不过这个方法走不通,至少我没找到解决办法,评论里有大神可以指导我一下。
我在Service层将返回的对象重新组装成了listMap形式,这个方法应该是比较主流的方法。
Service层如下:
public List<Map<String, String>> loadMap(Integer user_id) {
System.out.println("111111111111111111");
//System.out.println(userDao.loadMap(info_id));
List<Map<String, String>> dataList = new ArrayList<>();
User user = userDao.load(user_id);
//System.out.println(user);
//重新组装
Map<String, String> dataMap = new HashMap<>();
dataMap.put("user_id",user.getUser_id().toString());
//System.out.println(dataMap);
dataMap.put("account",user.getAccount());
//System.out.println(dataMap);
dataMap.put("user_name",user.getUser_name());
//System.out.println(dataMap);
dataMap.put("status",user.getStatus().toString());
//System.out.println(dataMap);
//dataMap.put("blocked_date",user.getBlocked_date().toString());
//dataMap.put("create_time",user.getCreate_time().toString());
//dataMap.put("login_time",user.getLogin_time().toString());
dataMap.put("ip_address",user.getIp_address());
//System.out.println(dataMap);
//dataMap.put("photo",user.getPhoto());
//System.out.println(user.getRole_id());
/*if (user.getRole_id()==null){
System.out.println("1");
}*/
String role_id = user.getRole_id()==null?"无信息":user.getRole_id().toString();
dataMap.put("role_id",role_id);
dataList.add(dataMap);
return dataList;
}
这里面有个坑就是一定要将非空字段判断出来赋予其一个值,否则会出现{message:null}的错误。
Controller层的方法就没有什么坑了,直接用即可,ColumnName是数据库的字段名,导出的excel表格默认表头是用的字段名,若要改名的话加一列数组赋值到相应的获取表格第一行的位置即可。
public void downloadExcel( List<Map<String, String>> dataList, HttpServletResponse servletResponse) throws IOException {
String[] columnName = {"user_id", "account", "user_name", "status","ip_address","role_id"};
Workbook workbook = ExcelUtil.getInstance().dataToExcel(dataList, "UserInfo", columnName);
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
servletResponse.reset();
servletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
servletResponse.setHeader("Content-Disposition", "attachment;filename=" + new String(("UserInfo" + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = servletResponse.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
最后再在controller层调用这个方法即可
@GetMapping("/download")
public String download(HttpServletResponse servletResponse) throws IOException {
List<Map<String, String>> dataList=userService.loadMap(1);
System.out.println(dataList);
downloadExcel(dataList,servletResponse);
return "user/download";
}
这里为了测试,只获取了一行数据,若要获取所有数据,在LoadMap方法循环遍历所有id即可。