Spring java excel_Java导出Excel的Springmvc实例

@RequestMapping(value = "downloadExcel", method = RequestMethod.GET)

public String download(HttpServletRequest request,HttpServletResponse response) throws IOException{

String fileName="excel文件";

//填充projects数据

ExcelUtil excelUtil = new ExcelUtil();

List projects = excelUtil.createData();

List> list = excelUtil.createExcelRecord(projects);

String columnNames[]={"ID","项目名","销售人","负责人","所用技术","备注"};//列名

String keys[]   =    {"id","name","saler","principal","technology","remarks"};//map中的key

ByteArrayOutputStream os = new ByteArrayOutputStream();

try {

ExcelUtil.createWorkBook(list,keys,columnNames).write(os);

} catch (IOException e) {

e.printStackTrace();

}

byte[] content = os.toByteArray();

InputStream is = new ByteArrayInputStream(content);

// 设置response参数，可以打开下载页面

response.reset();

response.setContentType("application/vnd.ms-excel;charset=utf-8");

response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "utf-8"));

ServletOutputStream out = response.getOutputStream();

BufferedInputStream bis = null;

BufferedOutputStream bos = null;

try {

bis = new BufferedInputStream(is);

bos = new BufferedOutputStream(out);

byte[] buff = new byte[2048];

int bytesRead;

while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {

bos.write(buff, 0, bytesRead);

}

bos.flush();

} catch (final IOException e) {

logger.error("导出Excel异常:",e);

throw e;

} finally {

if (bis != null)

bis.close();

if (bos != null)

bos.close();

}

return null;

}

/**

* 导出Excel

* @param request

* @param response

*/

@RequestMapping(value = "ProcessRequestExcel", method = RequestMethod.GET)

public void ProcessRequest(HttpServletRequest request,HttpServletResponse response){

java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("yyyyMMddhhmmss");

String now = format.format(new Date());

String exportFileName = "信息导出_"+now+".xls";//导出文件名

List list = getInfoList();

HSSFWorkbook workBook = null;

String[] cellTitle = {"序号", "姓名", "性别", "部门"};

try {

workBook = new HSSFWorkbook();//创建工作薄

HSSFSheet sheet = workBook.createSheet();

workBook.setSheetName(0, "订单信息");//工作簿名称

HSSFFont font = workBook.createFont();

font.setColor(HSSFFont.COLOR_NORMAL);

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

HSSFCellStyle cellStyle = workBook.createCellStyle();//创建格式

cellStyle.setFont(font);

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//创建第一行标题

HSSFRow titleRow = sheet.createRow((short) 0);//第一行标题

for(int i = 0,size = cellTitle.length; i < size; i++){//创建第1行标题单元格

sheet.setColumnWidth(i,4000);

HSSFCell cell = titleRow.createCell(i,0);

cell.setCellStyle(cellStyle);

cell.setCellValue(cellTitle[i]);

}

//从第二行开始写入数据

for(int i=1,size = list.size();i

HSSFRow row = sheet.createRow((short) i);

Basicinfo entity = list.get(i);

for (int j = 0,length=cellTitle.length; j < length; j++) {

HSSFCell cell = row.createCell(j, 0);// 在上面行索引0的位置创建单元格

cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型

switch(j){// 在单元格中输入一些内容

case 0:

cell.setCellValue(String.valueOf(i));

break;

case 1:

cell.setCellValue(entity.getName());

break;

case 2:

cell.setCellValue(entity.getSex());

break;

case 3:

cell.setCellValue(entity.getDepart());

break;

}

}

}

// 表示以附件的形式把文件发送到客户端

response.setHeader("Content-Disposition", "attachment;filename=" + new String((exportFileName).getBytes(), "ISO8859-1"));//设定输出文件头

response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型

// 通过response的输出流把工作薄的流发送浏览器形成文件

OutputStream outStream = response.getOutputStream();

workBook.write(outStream);

outStream.flush();

outStream.close();

}catch(IOException e){

System.out.println("IO 异常！"+e.getMessage());

e.printStackTrace();

}finally{

}

}

/**

* 模拟数据库获取信息

* @return

*/

@SuppressWarnings("unchecked")

public List getInfoList(){

List list = new ArrayList();

for(int i=1;i<101;i++){

Basicinfo entity = new Basicinfo();

entity.setName("员工"+i);

entity.setSex(i%2==1?"男":"女");

entity.setDepart(i>80?"销售部":"财务部");

list.add(entity);

}

return list;

}