/**
* excel导出 导出明细
*
* @param response
*/
@RequestMapping(value = “/details”, method=RequestMethod.POST)
public void details(HttpServletResponse response, @RequestParam(“id”) String id, @RequestParam(“createyear”) String createyear) {
try {
String fileName = “上年额度奖金”;
response.setHeader(“Content-disposition”, “attachment;filename=” + new String(fileName.getBytes(“gb2312”), “ISO8859-1”) + “.xls”);
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.ms-excel");
OutputStream ouputStream = response.getOutputStream();
//查询导出是按照项目导出还是辅助段导出
List<List<String>> list = bonusService.details();
String[] headers = new String[8]; //开辟一个长度为3的数组
headers[0] = "年(yyyy)*";
headers[1] = "账套编码*";
headers[2] = "账套名称";
headers[3] = "成本中心编码";
headers[4] = "成本中心名称";
headers[5] = "项目编码";
headers[6] = "项目名称";
headers[7] = "上年额度奖金";
ExportExcelUtils eeu = new ExportExcelUtils();
XSSFWorkbook workbook = new XSSFWorkbook();
eeu.exportExcel(workbook, 0, "sheet", headers, list, ouputStream);
//多sheet页模式
//eeu.exportExcel(workbook, 1, "sheet", headers, list, ouputStream);
//原理就是将所有的数据一起写入,然后再关闭输入流。
workbook.write(ouputStream);
ouputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
- excel导出所用工具类
/
public class ExportExcelUtils {
/*-
@param workbook
-
@param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
-
@param sheetTitle (sheet的名称)
-
@param headers (表格的标题)
-
@param result (表格的数据)
-
@param out (输出流)
-
@throws Exception
-
@Title: exportExcel
-
@Description: 导出Excel的方法
-
@author: evan @ 2014-01-09
*/
public void exportExcel(XSSFWorkbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<List> result,
OutputStream out) throws Exception {// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
// style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());// 背景色
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);// 生成一个字体
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell((short) i);cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (List m : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (String str : m) {
XSSFCell cell = row.createCell((short) cellIndex);
cell.setCellValue(str);
cellIndex++;
}
index++;
}
}
}
-
}