第一步:MAVEN: pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
第二步:SERVER: FileBPO
void exportExcelCellRangeAddress(HttpServletRequest req, HttpServletResponse res,String fileName, String sheetName, List<String> title,
List<Map<String, Object>> values, List<String> keys,List<Integer> column) throws Exception;
解释:fileName为文件名,sheetName为excel工作溥名,title为excel表头名称放在第一行,values为对象一般是对象转为map类型,keys为对象中的属性,column为要合并的哪一列。
第三步:SERVERIMPL实现类: FileBPOIMPL
@Override
public void exportExcelCellRangeAddress(HttpServletRequest req, HttpServletResponse res,String fileName, String sheetName,
List<String> title, List<Map<String, Object>> values, List<String> keys,List<Integer> column) throws Exception {
//创建Excel对象
HSSFWorkbook excel = createExcelCellRangeAddress(sheetName, title, values,keys, null,column);
ServletOutputStream out = null;
try {
String fileName1 = new String(fileName.getBytes(),"ISO8859-1");
res.setContentType("multipart/form-data");
res.setHeader("Content-Disposition", "attachment;filename="+ fileName1);
res.setCharacterEncoding("UTF-8");
res.setContentType("text/html");
out = res.getOutputStream();;
excel.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
if(out != null) {
out.flush();
out.close();
}
}
//创建Excel文件(仅有一个sheet)
public static HSSFWorkbook createExcelCellRangeAddress(String sheetName,List<String> title,List<Map<String,Object>> values,List<String> keys, HSSFWorkbook excel,List<Integer> column)throws Exception{
//创建Excel文件对象
if(excel == null) {
excel = new HSSFWorkbook();
}
//创建sheet名称
HSSFSheet sheet = excel.createSheet(sheetName);
//添加起始行
HSSFRow row = sheet.createRow(0);
//创建单元格,设置表头样式
HSSFCellStyle style = excel.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//申明单元格对象
HSSFCell cell = null;
//表头赋值
for(int i=0;i<title.size();i++) {
cell = row.createCell(i);
cell.setCellValue(title.get(i));
cell.setCellStyle(style);
}
//单元格赋值
for(int i=0;i<values.size();i++) {
Map<String,Object> m = values.get(i);
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(i+1);//no值
for(int j = 0 ;j<keys.size();j++) {
String value = null;
if(m.get(keys.get(j)) != null) {
value = m.get(keys.get(j)).toString();
}
row.createCell(j+1).setCellValue(value);
}
}
List<Integer> list=new ArrayList<Integer>();
int it=sheet.getLastRowNum();//获取行数
String s="";
if(column!=null && column.size()>0) {
for (Integer inte : column) {
for(int j=0;j<it;j++) {
//遍历每一行
String str=sheet.getRow(j).getCell(inte).getStringCellValue();
if(!s.equals(str)) {
if(j>1) {
list.add(j-1);
}
}
s=str;
}
list.add(it);
// System.out.println(list.toString());
int k=1;
for (Integer integer : list) {
// System.out.println(k+":"+integer);
if(integer-k>0) {
CellRangeAddress cra=new CellRangeAddress(k, integer, inte, inte);
sheet.addMergedRegion(cra);
}
//居中
HSSFCell hssfCell=sheet.getRow(k).getCell(inte);
hssfCell.setCellStyle(style);
k=integer+1;
}
list.clear();
s="";
}
}
// System.out.println(list.toString());
return excel;
}
第四步:调用
@Autowired
private FileBPO fileBPO;
@RequestMapping(value = "/doExportExcelAll")
@ResponseBody
public void doExportExcelAll(HttpServletRequest req, HttpServletResponse res) throws Exception{
//查看获取list
List<User> userList=dao.select();//这里查库的结果,在这里我就不写了,如果不懂得可以留言
String fileName = "测试.xls";
String sheetName = "测试";
List<String> title = new ArrayList<String>();//标题列表
title.add("用户名");title.add("密码");
List<String> keys = new ArrayList<String>();//Map获取值的key
keys.add("username");keys.add("password");
List<Map<String, Object>> values = new ArrayList<Map<String, Object>>();
userList.forEach( c ->{
User cd = c;
Map<String, Object> m = MapUtil.objectToMap(cd);
values.add(m);
});
List<Integer> column =new ArrayList<Integer>();
//合并第1列和第2列,也可以不合并,不设置就是不合并
column.add(1);
column.add(2);
fileBPO.exportExcelCellRangeAddress(req, res,fileName,sheetName, title, values, keys,column);
}
//对象转map方法,利用反射
public static Map<String, Object> objectToMap(Object obj) {
Map<String, Object> map = new HashMap<>();
Class<?> clazz = obj.getClass();
try {
for (Field field : clazz.getDeclaredFields()) {
field.setAccessible(true);//设置可以访问私有变量
String fieldName = field.getName();
Object value = null;
if(field.get(obj) != null) {
value = field.get(obj);
}
map.put(fieldName, value);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return map;
}