代码如下。
@RequestMapping("/excel")
public String excel(HttpServletRequest request,HttpServletResponse response,Device device,String sname) throws Exception{
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
// 设置excel每列宽度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3500);
// 创建字体样式
HSSFFont font = wb.createFont();
font.setFontName("Verdana");
font.setBoldweight((short) 100);
font.setFontHeight((short) 300);
font.setColor(HSSFColor.BLUE.index);
// 创建单元格样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style.setBottomBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setFont(font);// 设置字体
style.setWrapText(true);// 自动换行
// 创建Excel的sheet的一行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 设定行的高度
HSSFCell cell=null;
// 创建一个Excel的单元格
String[] cellTitle = {"编号", "名称", "分光比","上联设备", "上联设备端口","接入模式", "入网时间","覆盖范围", "安装地址","归属的交光箱", "归属交光箱经度","归属交光箱纬度", "上联交光箱","落地纤总量", "空闲数量","一线维护人"};
for (int i = 0; i < cellTitle.length; i++) {
cell = row.createCell(i);
// 给Excel的单元格设置样式和赋值
cell.setCellStyle(style);
cell.setCellValue(cellTitle[i]);
}
try{
Map<String, Object> map=new HashMap<String, Object>();
map.put("name", StringUtil.formatLike(sname));
List<Device> users=deviceService.selectByPage(map);
int rowIndex=1;
Iterator<Device> ite=users.iterator();
while(ite.hasNext()){
device=ite.next();
row = sheet.createRow(rowIndex++);
cell = row.createCell(0);
cell.setCellValue(device.getId());
cell = row.createCell(1);
cell.setCellValue(device.getName());
cell = row.createCell(2);
cell.setCellValue(device.getFgb());
cell = row.createCell(3);
cell.setCellValue(device.getSlsb());
cell = row.createCell(4);
cell.setCellValue(device.getSlsbdk());
cell = row.createCell(5);
cell.setCellValue(device.getJrms());
cell = row.createCell(6);
cell.setCellValue(device.getRwsj());
cell = row.createCell(7);
cell.setCellValue(device.getFgfw());
cell = row.createCell(8);
cell.setCellValue(device.getAzdz());
cell = row.createCell(9);
cell.setCellValue(device.getSljgx());
cell = row.createCell(10);
cell.setCellValue(device.getJd());
cell = row.createCell(11);
cell.setCellValue(device.getWd());
cell = row.createCell(12);
cell.setCellValue(device.getSljgx());
cell = row.createCell(13);
cell.setCellValue(device.getSum());
cell = row.createCell(14);
cell.setCellValue(device.getKxsum());
cell = row.createCell(15);
cell.setCellValue(device.getDefend().getName());
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=device.xls");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}catch(Exception e){
e.printStackTrace();
}
return null;
}