建导出的工具类 设置导出Excel的格式
public class ExportUtil {
/**
*
* @param title 标题
* @param headers 表头
* @param values 表中元素
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String title, String headers[], String [][] values){
//创建一个HSSFWorkbook,对应一个Excel文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet hssfSheet = hssfWorkbook.createSheet(title);
//创建标题合并行
hssfSheet.addMergedRegion(new CellRangeAddress(0,(short)0,0,(short)headers.length - 1));
//设置标题样式
HSSFCellStyle style = hssfWorkbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //设置居中样式
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置标题字体
Font titleFont = hssfWorkbook.createFont();
titleFont.setFontHeightInPoints((short) 14);
style.setFont(titleFont);
//设置值表头样式 设置表头居中
HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); //设置居中样式
hssfCellStyle.setBorderBottom(BorderStyle.THIN);
hssfCellStyle.setBorderLeft(BorderStyle.THIN);
hssfCellStyle.setBorderRight(BorderStyle.THIN);
hssfCellStyle.setBorderTop(BorderStyle.THIN);
//设置表内容样式
//创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style1 = hssfWorkbook.createCellStyle();
style1.setBorderBottom(BorderStyle.THIN);
style1.setBorderLeft(BorderStyle.THIN);
style1.setBorderRight(BorderStyle.THIN);
style1.setBorderTop(BorderStyle.THIN);
//产生标题行
HSSFRow hssfRow = hssfSheet.createRow(0);
HSSFCell cell = hssfRow.createCell(0);
cell.setCellValue(title);
cell.setCellStyle(style);
//产生表头
HSSFRow row1 = hssfSheet.createRow(1);
for (int i = 0; i < headers.length; i++) {
HSSFCell hssfCell = row1.createCell(i);
hssfCell.setCellValue(headers[i]);
hssfCell.setCellStyle(hssfCellStyle);
}
//创建内容
for (int i = 0; i <values.length; i++){
row1 = hssfSheet.createRow(i +2);
for (int j = 0; j < values[i].length; j++){
//将内容按顺序赋给对应列对象
HSSFCell hssfCell = row1.createCell(j);
hssfCell.setCellValue(values[i][j]);
hssfCell.setCellStyle(style1);
}
}
return hssfWorkbook;
}
}
Controller返回
/*导出excel*/
@RequestMapping(value = "/export", method = RequestMethod.POST)
public void export(HttpServletRequest request, HttpServletResponse response,Map<String, Object> map){
//excel标题
String title = "明细表";
//excel表名
String [] headers = {"表名称", "**数量", "**数量","**数量","总数"};
//excel文件名
String fileName = title +".xls";
List<NumEntity> list = kpiNumService.getexportKPINumdata(map);
for (NumEntity map1 : list) {
Set<Map.Entry<String, Object>> entries = map.entrySet( );
if(entries != null) {
Iterator<Map.Entry<String, Object>> iterator = entries.iterator( );
while(iterator.hasNext( )) {
Map.Entry<String, Object> entry =(Map.Entry<String, Object>) iterator.next( );
Object key = entry.getKey( );
Object value = entry.getValue();
System.out.println(key+":"+value);
}
}
}
//excel元素
String content[][] = new String[list.size()][5];
for (int i = 0; i < list.size(); i++) {
content[i] = new String[headers.length];
content[i][0] = list.get(i).getName().toString();
content[i][1] = list.get(i).getNum().toString();
content[i][2] = list.get(i).getNum().toString();
content[i][3] = list.get(i).getNum().toString();
content[i][4] = list.get(i).getAllNum().toString();
}
//创建HSSFWorkbook
HSSFWorkbook wb = ExportUtil.getHSSFWorkbook(title, headers, content);
//响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
结尾:Controller中可能代码有点多可以把发送响应流的方法那一段写到ExportUtil的工具类中 可以减少Controller中的代码量