前端直接访问输出流即可:window.location.href="${baseURL}/aaa/bbb";
注意浏览器下载不能使用Ajax异步下载,否则没有效果
工具类:直接调用exportData方法即可,参数是前端请求发出的
package com.wb.util;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import com.wb.po.Person;
public class ExportToExcelUtil {
public static boolean exportData(HttpServletRequest request, HttpServletResponse response){
String fileName = getFileName();
HSSFWorkbook workbook = new HSSFWorkbook();
//填充内容
createSheet(workbook, fileName, 10);
//写入到输出流
try {
OutputStream outputStream = response.getOutputStream();
try {
fileName = URLEncoder.encode(fileName, "utf8");
} catch (Exception e) {
e.printStackTrace();
return false;
}
response.setCharacterEncoding("UTF-8");
response.setContentType("application/msexcel;charset=utf-8");// 设置contentType为excel格式
response.setHeader("Content-Disposition", "Attachment;Filename="+ fileName+".xls");
workbook.write(outputStream);
outputStream.close();
} catch (IOException e) {
workbook = null;
return false;
}
return true;
}
private static void createSheet(HSSFWorkbook workbook, String fileName, int num){
List<String> titles = getExcelTitle();
List<Person> persons = getPersonList(num);
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
Sheet sheet = workbook.createSheet(fileName);
//创建标题行
int rowNumber=0;
Row row = sheet.createRow(rowNumber++);
for(int i=0; i<titles.size(); i++){
Cell cell = row.createCell(i);
cell.setCellStyle(createTitleStyles(workbook));
cell.setCellValue(titles.get(i));
}
//创建正文内容
for(Person person : persons){
row = sheet.createRow(rowNumber);
Cell cell1 = row.createCell(0);
cell1.setCellValue(rowNumber++);
cell1.setCellStyle(createCellStyles(workbook));
Cell cell2 = row.createCell(1);
cell2.setCellValue(person.getName());
cell2.setCellStyle(createCellStyles(workbook));
Cell cell3 = row.createCell(2);
cell3.setCellValue(person.getAge());
cell3.setCellStyle(createCellStyles(workbook));
Cell cell4 = row.createCell(3);
cell4.setCellValue(person.getSex());
cell4.setCellStyle(createCellStyles(workbook));
Cell cell5 = row.createCell(4);
cell5.setCellValue(dateFormat.format(person.getBirthday()));
cell5.setCellStyle(createCellStyles(workbook));
}
//自适应列宽
for(int ii=0; ii<6; ii++){
sheet.autoSizeColumn(ii, true);
}
}
private static CellStyle createCellStyles(HSSFWorkbook wb) {
Font cellStyleFont = wb.createFont();
cellStyleFont.setFontHeightInPoints((short) 12);
cellStyleFont.setColor(IndexedColors.BLACK.getIndex());
cellStyleFont.setFontName("微软雅黑");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setFont(cellStyleFont);
return cellStyle;
}
private static CellStyle createTitleStyles(HSSFWorkbook wb) {
Font cellStyleFont = wb.createFont();
cellStyleFont.setFontHeightInPoints((short) 12);
cellStyleFont.setColor(IndexedColors.BLACK.getIndex());
cellStyleFont.setFontName("微软雅黑");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setFont(cellStyleFont);
HSSFPalette palette = wb.getCustomPalette(); //wb HSSFWorkbook对象
palette.setColorAtIndex((short)9, (byte)(0x3e), (byte)(0x94), (byte)(0xe1));
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor((short)9);
return cellStyle;
}
private static String getFileName(){
return "test_export";
}
private static List<String> getExcelTitle(){
List<String> titleList = new ArrayList<>();
titleList.add("序号");
titleList.add("姓名");
titleList.add("年龄");
titleList.add("性别");
titleList.add("出生日期");
return titleList;
}
private static List<Person> getPersonList(int num){
List<Person> list = new ArrayList<>();
for(int i=0; i<num; i++){
Person person = new Person("genox", 20, "男", new Date());
list.add(person);
}
return list;
}
}