<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
创建报表
/**
* @param titleList 标题
* @param titleName 文件名
* @param empList 数据集合
* @return
* @throws Exception
*/
public static HSSFWorkbook exportToExcel( List titleList, String titleName, List empList) throws Exception{
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(titleName);
// 设置列宽(如果不是很严谨的需要,或者其他地方共用次util类,可以不设置)
setSizeColumn(sheet , titleList.size());
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
// 声明列对象
HSSFCell cell = null;
try {
//创建标题行
for (int i= 0;i<titleList.size();i++){
cell = row.createCell(i);//第一行第零列
cell.setCellValue(titleList.get(i).toString());
System.out.println("titleList=="+titleList.get(i).toString());
HSSFCellStyle cellStyle = getCellStyle(wb, true);
cell.setCellStyle(cellStyle);
}
//循环一次创建一行数据
for (int j = 0; j < empList.size(); j++) {
// Object obj = objClass.newInstance();// 获取对象实例
// 获取一条数据
AmcLawyerEmployTitle emp = (AmcLawyerEmployTitle) empList.get(j);
row = sheet.createRow(j + 1);
//循环一次创建一个单元格数据
// 获取实体类的所有属性,返回Field数组
Field[] field = emp.getClass().getDeclaredFields();
for(int k = 0; k < field.length; k++){
// 获取属性的名字
String name = field[k].getName();
String firstBig = name.substring(0, 1).toUpperCase() + name.substring(1);
//获取属性的get方法
Method method = emp.getClass().getMethod("get" + firstBig);
//调用getter方法获取属性的值
row.createCell(k).setCellValue(method.invoke(emp)==null?"":method.invoke(emp).toString());
row.getCell(k).setCellStyle(style);
}
//当前sheet包含的列数
int columnNum = field.length;
//设置当前sheet的列宽自适应
setSizeColumn(sheet, columnNum);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return wb;
}
//样式设置
public static HSSFCellStyle getCellStyle(HSSFWorkbook wb, boolean isCenter) {
HSSFCellStyle style = wb.createCellStyle();
if (isCenter) {
style.setAlignment((short)2);
style.setVerticalAlignment((short)1);
}
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setBorderBottom((short)1);
style.setBorderLeft((short)1);
style.setBorderTop((short)1);
style.setBorderRight((short)1);
return style;
}
//列宽设置
public static void setSizeColumn(Sheet sheet , int size){
for(int colunmNum = 0; colunmNum < size; colunmNum ++){
int columnWidth = sheet.getColumnWidth(colunmNum) / 256;
for(int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows(); rowNum ++){
Row currentRow;
if(sheet.getRow(rowNum) == null){
currentRow = sheet.createRow(rowNum);
}else{
currentRow = sheet.getRow(rowNum);
}
if(currentRow.getCell(colunmNum) != null){
Cell currentCell = currentRow.getCell(colunmNum);
if(currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING){
int length = currentCell.getStringCellValue().getBytes().length;
if(columnWidth < length){
columnWidth = length;
}
}
}
sheet.setColumnWidth(colunmNum , columnWidth * 256);
}
}
}
核心代码
List titleList = getTitleList();
String sheetName = "数据统计表";
HSSFWorkbook wb = exportToExcel(titleList, sheetName, amcLawyerEmployTitles);
String fileName =sheetName;
fileName = new String((fileName + ".xls").getBytes(), "ISO8859-1");// ISO8859-1不能改为UTF-8,否则文件名是乱码
ServletOutputStream out = response.getOutputStream();
wb.write(out);
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// Content-Disposition内容配置;attachment附件;(下载完成提示)
out.flush();
out.close();
注意:使用ajax请求后台时,浏览器不会提示下载完成。需要用表单提交的方式发送请求。
模仿表单提交的请求方式:ul为请求路径
<script>
var dynamicSubmit = function(url) {
debugger;
var form = $('#dynamicForm');
if (form.length <= 0) {
form = $("<form>");
form.attr('id', 'dynamicForm');
form.attr('style', 'display:none');
form.attr('target', '');
form.attr('method', 'post');
$('body').append(form);
}
form = $('#dynamicForm');
form.attr('action', url);
form.empty();
form.submit();
};
</script>