java poi导出excel_JAVA POI导出excel

1.引入maven 依赖

org.apache.poi

poi

${poi.version}

org.apache.poi

poi-ooxml

${poi.version}

org.apache.poi

poi-ooxml-schemas

${poi.version}

2.工具类

public class ExcelUtils {

/**

* @param response

* @param fileName excel文件名

* @param headMap 表头map

* @param dataList 表格数据

*/

public static void exportXlsx(HttpServletResponse response, String fileName,

Map headMap, List> dataList) {

Workbook workbook = exportXlsx(fileName, headMap, dataList);

response.setContentType("application/binary;charset=ISO8859_1");

OutputStream outputStream = null;

try {

outputStream = response.getOutputStream();

String fn = new String(fileName.getBytes(), "ISO8859_1");

response.setHeader("Content-disposition", "attachment; filename=" + fn + ".xlsx");

workbook.write(outputStream);

} catch (Exception e) {

e.printStackTrace();

} finally {

if (outputStream != null) {

try {

outputStream.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

}

/**

* 导出数据

*

* @param headMap

* @param dataList

*/

public static Workbook exportXlsx(String sheetName, Map headMap, List> dataList) {

Workbook workbook = new XSSFWorkbook();

Sheet sheet = workbook.createSheet(sheetName);

int rowIndex = 0, columnIndex = 0;

Set keys = headMap.keySet();

//表头

Row row = sheet.createRow(rowIndex++);

for (String key : keys) {

Cell cell = row.createCell(columnIndex++);

cell.setCellValue(headMap.get(key));

}

//内容

if (dataList != null && !dataList.isEmpty()) {

for (Map map : dataList) {

row = sheet.createRow(rowIndex++);

columnIndex = 0;

for (String key : keys) {

Cell cell = row.createCell(columnIndex++);

setCellValue(cell, map.get(key));

}

}

}

return workbook;

}

private static void setCellValue(Cell cell, Object obj) {

if (obj == null) {

return;

}

if (obj instanceof String) {

cell.setCellValue((String) obj);

} else if (obj instanceof Date) {

Date date = (Date) obj;

if (date != null) {

cell.setCellValue(DateUtils.dfDateTime.format(date));

}

} else if (obj instanceof Calendar) {

Calendar calendar = (Calendar) obj;

if (calendar != null) {

cell.setCellValue(DateUtils.dfDateTime.format(calendar.getTime()));

}

} else if (obj instanceof Timestamp) {

Timestamp timestamp = (Timestamp) obj;

if (timestamp != null) {

cell.setCellValue(DateUtils.dfDateTime.format(new Date(timestamp.getTime())));

}

} else if (obj instanceof Double) {

cell.setCellValue((Double) obj);

} else {

cell.setCellValue(obj.toString());

}

}

}

3.controller导出用户数据

@RequestMapping("/export")

public String export(String username, HttpServletRequest request, HttpServletResponse repsonse) {

//查询用户数据

UserQueryDTO queryDTO = new UserQueryDTO() {{

setUsername(username);

}};

List userList = userService.findUser(queryDTO);

//表头

Map headNameMap = new LinkedHashMap();

headNameMap.put("userId", "ID");

headNameMap.put("roleName", "角色");

headNameMap.put("userName", "账号");

headNameMap.put("realName", "姓名");

headNameMap.put("mobile", "电话号码");

headNameMap.put("createDate", "创建时间");

headNameMap.put("status", "状态");

//表格数据

List> list = new ArrayList>();

if (userList != null && userList.size() > 0) {

for (CmsUser user : userList) {

String statusName = "正常";

if (StringUtils.isNotBlank(user.getDeleteFlag()) && user.getDeleteFlag().equals(ConstantHelper.DELETE_FLAG_DELETED)) {

statusName = "删除";

}

String createDate = "";

if (user.getCreateDate() != null) {

createDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getCreateDate());

}

String roleName = "";

Set roleSet = roleService.findByUserId(user.getUserId());

if (roleSet != null && roleSet.size() > 0) {

for (CmsRole r : roleSet) {

roleName += r.getName() + " ";

}

}

Map map = new HashMap();

map.put("userId", user.getUserId());

map.put("roleName", roleName);

map.put("userName", user.getUsername());

map.put("realName", user.getRealName());

map.put("mobile", user.getMobile());

map.put("createDate", createDate);

map.put("status", statusName);

list.add(map);

}

}

ExcelUtils.exportXlsx(repsonse, "用户", headNameMap, list);

return null;

}