废话不多说,直接上代码了,还是补一句,就是:数据放到实体类里边,然后放到map集合中,再把map放到List集合中,最后调用工具类,即可导出。
1. maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
<exclusions>
<exclusion>
<artifactId>xmlbeans</artifactId>
<groupId>org.apache.xmlbeans</groupId>
</exclusion>
</exclusions>
</dependency>
注意:如果使用的是springboot记得exclusions掉xmlbeans,因为可能会有冲突
2. excel导出工具类
package //TODO
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelUtils {
private static final int PAGE_NUM = 65500;
/**
* 非空判断
* @param obj
* @return
*/
public static boolean isEmptyOrNull(Object obj){
if(obj == null){
return true;
}
if(obj instanceof Map){
return ((Map) obj).isEmpty();
}else if(obj instanceof List){
return ((List) obj).isEmpty();
}
return obj.toString().isEmpty();
}
/**
* 获取工作簿
* @param list
* @param columnMap
* @return
* @throws Exception
*/
public static HSSFWorkbook getHSSFWorkbook(List<Map<String,Object>> list, Map<String, String> columnMap){
HSSFWorkbook wb = new HSSFWorkbook();
//对集合进行非空判断
if(isEmptyOrNull(list) || isEmptyOrNull(columnMap)){
wb.createSheet();
return wb;
}
int pageNum = PAGE_NUM;//每页数量
int size = list.size();//数据数量
int pages = (size % pageNum > 0) ? (size / pageNum + 1) : (size / pageNum);//导出页数
HSSFCellStyle cs = null;//单元格样式
HSSFSheet sheet = null;//页面
HSSFFont font = wb.createFont();//字体
font.setFontHeightInPoints((short) 11);//设置字号
cs = wb.createCellStyle();
cs.setFont(font);
cs.setFillPattern(HSSFCellStyle.FINE_DOTS);
Set<String> keySet = columnMap.keySet();
HSSFRow row = null;//单元格行
Object columnvalue = null;
String columnTitle = null;
Map<String,Object> dataMap = null;
for (int i = 0; i < pages; i++) {
sheet = wb.createSheet();
wb.setSheetName(i, (String.valueOf((i + 1))));
row = sheet.createRow(0);
for(String keyColumn : keySet){
columnTitle = columnMap.get(keyColumn);
createStringCell(row, (short) row.getLastCellNum() == -1 ? 0 : (short) row.getLastCellNum(), columnTitle, cs);
}
for(int j = 0; j < size; j++){
dataMap = list.get(j);
row = sheet.createRow(j + 1 - (pageNum * i));
for(String keyColumn : keySet){
columnvalue = dataMap.get(keyColumn);
createStringCell(row, (short) row.getLastCellNum() == -1 ? 0 : (short) row.getLastCellNum(), columnvalue, cs);
}
}
}
return wb;
}
/**
* 创建单元格
* @param row
* @param index
* @param value
* @param cs
*/
private static void createStringCell(HSSFRow row, short index, Object value, HSSFCellStyle cs) {
HSSFCell cell = row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
if(!isEmptyOrNull(value) && value instanceof Date){
value = doDate2String((Date)value);
}
cell.setCellValue(null2Empty(value));
}
/**
* 将日期转化为String
* @param confirmDate
* @param patten
* @return
*/
public static String doDate2String(Date confirmDate, String... patten) {
if(confirmDate == null){
return "";
}
SimpleDateFormat sdf = null;
if(patten == null || patten.length == 0){
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}else{
sdf = new SimpleDateFormat(patten[0]);
}
return sdf.format(new Date(confirmDate.getTime()));
}
/**
* 将null转化为Empty
* @param areaId
* @return
*/
public static String null2Empty(Object areaId) {
return areaId == null ? "" : areaId.toString();
}
}
注意:关键方法:getHSSFWorkbook
3. controller方法:
@RequestMapping(value="download")
public void downloadExcel(HttpServletRequest request,HttpServletResponse response,IsEntity isEntity){
StringBuffer sbBuffer = new StringBuffer();
sbBuffer.append("这是文件名");
sbBuffer.append(CommonUtil.dateToString(new Date()));
sbBuffer.append(".xls");
String fileName = sbBuffer.toString();
//excel 写入数据 service层 TODO 这个自己注入进来
HSSFWorkbook wb = this.offlineService.downloadExcel(isEntity);
this.setResponseHeader(response, fileName);
try {
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (IOException e) {
//TODO 处理异常
}
}
//发送响应流方法
private void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
//TODO 处理异常
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
//TODO 处理异常
}
}
3. 调用service的方法
/**
* 导出excel
* @param offlineQueryEntity
*/
public HSSFWorkbook downloadExcel(IsEntity isEntity) {
try {
//查询数据
List<IsEntity > query = this.query(IsEntity );
//标题
Map<String, String> column = new TreeMap<String, String>();
column.put("A1","excel标题");
column.put("A2","excel标题");
column.put("A3","excel标题");
//excel内容
List<Map<String, Object>> listResult = new ArrayList<Map<String, Object>>();
if(query!=null && !query.isEmpty()){
for (IsEntity vo: query) {
Map<String, Object> hashMap = new HashMap<String, Object>();
hashMap.put("A1",vo.getId());
hashMap.put("A2",vo.getName());
hashMap.put("A3",vo.getPassWord());
listResult.add(hashMap);
}
}
//去调用工具类的方法
HSSFWorkbook wb = ExcelUtils.getHSSFWorkbook(listResult, column);
return wb;
} catch (Exception e) {
//TODO处理异常
}
return null;
}
注意:标题是指导出excel的标题,标题名称自己修改,excel的内容即是查询出来的数据,放到实体类IsEntity中。
4. 实体类IsEntity
package //TODO 包名
public class IsEntity {
private String id;
private String name;
private String passWord;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
}