SpringMVC下载Excel报表,这样一个比较简单的功能,但是在项目中都会遇到一些这样那样的小问题,这里将工作中遇到的这些问题进行总结吧,希望能帮助初学者。
关于SpringMVC的知识这里就不多作细说,直接上代码。
ExcelAction.java 后端Controller类
package com.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.util.ExcelUtil;
@Controller
public class ExcelAction {
@RequestMapping("/download.do")
public ResponseEntity download(HttpServletRequest request,HttpServletResponse response) throws Exception{
byte[] content = new ExcelUtil().getExcelFileByte();
HttpHeaders headers = new HttpHeaders();
if(content == null || content.length == 0){
headers.setContentType(MediaType.APPLICATION_JSON_UTF8);
return new ResponseEntity(null, headers, HttpStatus.OK);
}
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment",new String("测试Excel.xls".getBytes("utf-8"),"ISO-8859-1"));
return new ResponseEntity(content, headers, HttpStatus.CREATED);
}
}
ExcelUtil.java 生成Excel的类
package com.util;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
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;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import com.bean.Student;
public class ExcelUtil {
public byte[] getExcelFileByte(){
ByteArrayOutputStream bos = new ByteArrayOutputStream();
byte[] content = null;
try {
// 1.准备数据
List<Student> list = new ArrayList<Student>();
list.add(new Student("小明","男","18","篮球"));
list.add(new Student("小唐","男","19","足球"));
list.add(new Student("小红","女","17","画画"));
list.add(new Student("小倩","女","19","看电影"));
// 2.创建excel
// 声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet("学生信息");
//产生表格标题行
HSSFRow row0 = sheet.createRow(0);
//创建表格
HSSFCell cellData = row0.createCell(0);
cellData.setCellStyle(getStatiscsRangeStyle(workbook));
cellData.setCellValue("学生信息统计信息");
//创建excel列名
String[] headers = new String[]{"姓名", "性别", "年龄", "爱好"};
for(int i=0;i<headers.length;i++){
sheet.setColumnWidth(i, 4000);
}
//合并单元格
CellRangeAddress cra = new CellRangeAddress(0,0,0,headers.length - 1);
sheet.addMergedRegion(cra);
HSSFRow row2 = sheet.createRow(1);
for(int i=0;i<headers.length;i++){
HSSFCell cell = row2.createCell(i);
cell.setCellStyle(getHeaderStyle(workbook));
cell.setCellValue(headers[i]);
}
//设置数据区
setCellValue(list,workbook,sheet);
workbook.write(bos);
content = bos.toByteArray();
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return content;
}
/**
* excel文档第一行的样式
* @param workbook
* @return
*/
private HSSFCellStyle getStatiscsRangeStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = getBaseCellStyle(workbook);
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
return style;
}
public HSSFCellStyle getBaseCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
return style;
}
/**
* 头数据格式
* @param workbook
* @return
*/
private HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = getBaseCellStyle(workbook);
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
return style;
}
/**
* excel写入数据
* @param list
* @param workbook
* @param sheet
*/
private void setCellValue(List<Student> list,HSSFWorkbook workbook, HSSFSheet sheet){
HSSFCellStyle cellStyle = getBaseCellStyle(workbook);
int index = 2;
for(Student stu : list){
HSSFRow row = sheet.createRow(index);
HSSFCell cell0 = row.createCell(0);
cell0.setCellStyle(cellStyle);
cell0.setCellValue(stu.getName());
HSSFCell cell1 = row.createCell(1);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(stu.getSex());
HSSFCell cell2 = row.createCell(2);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(stu.getAge());
HSSFCell cell3 = row.createCell(3);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(stu.getLove());
index++;
}
}
}
Student.java 实体bean
package com.bean;
public class Student {
private String name;
private String sex;
private String age;
private String love;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getLove() {
return love;
}
public void setLove(String love) {
this.love = love;
}
public Student(String name, String sex, String age, String love) {
super();
this.name = name;
this.sex = sex;
this.age = age;
this.love = love;
}
@Override
public String toString() {
return "Student [name=" + name + ", sex=" + sex + ", age=" + age + ", love=" + love + "]";
}
}
<!DOCTYPE html>
<html>
<head>
<title>我的测试</title>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
</head>
<body>
<a id="download1">下载excel文档</a>
<br>
<a id="download2">下载Excel文档2</a>
</body>
<script type="text/javascript" src="../js/jquery.min.js"></script>
<script type="text/javascript">
$("#download1").click(function(){
var contextHref = getContextPath();
window.location.href = contextHref+"/download.do";
});
function getContextPath(){
var pathName = document.location.pathname;
var index = pathName.substr(1).indexOf("/");
var contextRoot = pathName.substr(0,index+1);
var port = window.location.port;
return "http://localhost:"+port+contextRoot;
}
</script>
</html>
这里前端调用用的是window.location.href 的方式,本机测试通过