JAVA导出excel表格

工具类代码：
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExportUtil {
private XSSFWorkbook wb = null;  
 
  private XSSFSheet sheet = null;  
 
  /** 
   * @param wb 
   * @param sheet 
   */  
  public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet)  
  {  
      this.wb = wb;  
      this.sheet = sheet;  
  }  
 
  /** 
   * 合并单元格后给合并后的单元格加边框 
   *  
   * @param region 
   * @param cs 
   */  
  public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)  
  {  
 
      int toprowNum = region.getFirstRow();  
      for (int i = toprowNum; i <= region.getLastRow(); i++)  
      {  
          XSSFRow row = sheet.getRow(i);  
          for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)  
          {  
              XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,  
                                              // (short) j);  
              cell.setCellStyle(cs);  
          }  
      }  
  }  
 
  /** 
   * 设置表头的单元格样式 
   *  
   * @return 
   */  
  public XSSFCellStyle getHeadStyle()  
  {  
      // 创建单元格样式  
      XSSFCellStyle cellStyle = wb.createCellStyle();  
      // 设置单元格的背景颜色为淡蓝色  
      cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);  
      cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);  
      // 设置单元格居中对齐  
      cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
      // 设置单元格垂直居中对齐  
      cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
      // 创建单元格内容显示不下时自动换行  
      cellStyle.setWrapText(true);  
      // 设置单元格字体样式  
      XSSFFont font = wb.createFont(); 
      // 设置字体加粗  
      font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);  
      font.setFontName("微软雅黑");  
      font.setFontHeight((short) 220);  
      cellStyle.setFont(font);  
      // 设置单元格边框为细线条  
      cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
      return cellStyle;  
  }  
 
  /** 
   * 设置表体的单元格样式 
   *  
   * @return 
   */  
  public XSSFCellStyle getBodyStyle()  
  {  
      // 创建单元格样式  
      XSSFCellStyle cellStyle = wb.createCellStyle();  
      // 设置单元格居中对齐  
      cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
      // 设置单元格垂直居中对齐  
      cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
      // 创建单元格内容显示不下时自动换行  
      cellStyle.setWrapText(true);  
      // 设置单元格字体样式  
      XSSFFont font = wb.createFont();  
      // 设置字体加粗  
        
      font.setFontName("微软雅黑");  
      font.setFontHeight((short) 200); 
      cellStyle.setFont(font);  
      // 设置单元格边框为细线条  
      cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
      return cellStyle;  
  }  
  
  /** 
   * 设置表体的单元格样式--数字 
   *  
   * @return 
   */  
  public XSSFCellStyle getNumberStyle()  
  {  
      // 创建单元格样式  
      XSSFCellStyle cellStyle = wb.createCellStyle();  
      // 设置单元格居中对齐  
      cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
      // 设置单元格垂直居中对齐  
      cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
      // 创建单元格内容显示不下时自动换行  
      cellStyle.setWrapText(true);  
      // 设置单元格字体样式  
      XSSFFont font = wb.createFont();  
      // 设置字体加粗  
        
      font.setFontName("微软雅黑");  
      font.setFontHeight((short) 200); 
      cellStyle.setFont(font);  
      // 设置单元格边框为细线条  
      cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
      cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
      XSSFDataFormat format =  wb.createDataFormat();
      cellStyle.setDataFormat(format.getFormat("#,##0.00"));
      return cellStyle;  
  }  

}

Controller代码：

@RequestMapping(value = "/exportBalanceSheet", method = RequestMethod.POST)
public void exportBalanceSheet(HttpServletResponse response, HttpSession session,
@RequestParam(value="startDates",required = false) String startDates,
@RequestParam(value="endDates",required = false) String endDates,
    @RequestParam(value="realNames",required = false ) String realNames) throws ParseException{
response.setContentType("application/binary;charset=ISO8859_1"); //设置内容格式
Map<String, Object>map = new HashMap<String, Object>();
//将可能出现中文乱码参数转码
String realName = "";
try {
realName = new String(realNames.getBytes("ISO8859_1"),"UTF-8");
} catch (Exception e) {
e.printStackTrace();
}
String beforeDate = null;
if(startDates == null || endDates == null){
startDates = DayUtil.Themonth();
beforeDate = sdf.format(DayUtil.getNextDay(sdf.parse(startDates)));
endDates = sdf.format(DayUtil.getNextDay(new Date()));
}else{
beforeDate = sdf.format(DayUtil.getNextDay(sdf.parse(startDates)));
}
//封装参数
map.put("beforeDate", beforeDate);
map.put("realName", realName);
map.put("startDate", startDates);
map.put("endDate", endDates);

//构建数据，创建文件
ServletOutputStream outputStream = null;
try {
outputStream = response.getOutputStream(); //获取输出流
String[] titles = {"借款人","前期余额","本期充值金额","本期偿还金额","期末金额"};
String fileName = new String("余额统计".getBytes(), "ISO8859_1"); //设置文件名
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); // 组装附件名称和格式  

//查询数据
List<BalanceSheetVO> list = rechargeService.queryBalanceSheetListToExcel(map);//数据
XSSFWorkbook workBook = new XSSFWorkbook();  //创建一个wookbook对应一个excel应用文件
XSSFSheet sheet = workBook.createSheet("导出excel");// 创建一个workbook 对应一个excel应用文件
sheet.setDefaultColumnWidth(30); //设置单元格样式
sheet.setDefaultRowHeightInPoints(18);
ExportUtil exportUtil = new ExportUtil(workBook, sheet);
XSSFCellStyle headStyle = exportUtil.getHeadStyle();
XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
XSSFCellStyle numberStyle = exportUtil.getNumberStyle();
//构建表头
XSSFRow headRow = sheet.createRow(0);
XSSFCell cell = null;
for(int i = 0;i < titles.length; i++){
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(titles[i]);
}
//构建表体数据
if(list != null && list.size()>0){
for(int j = 0; j<list.size(); j++){
XSSFRow bodyRow = sheet.createRow(j + 1);
BalanceSheetVO balanceSheetVO = list.get(j);

//计算所有数值
BigDecimal balance = balanceSheetVO.getBalance();  //期末余额
BigDecimal recharge = balanceSheetVO.getRecharge();  //本期充值金额
BigDecimal beforeBalance = balanceSheetVO.getBeforeBalance();  //前期余额
BigDecimal repayMoney = balanceSheetVO.getRepayMoney();  //本期偿还金额

//创建内容
cell = bodyRow.createCell(0);
cell.setCellStyle(bodyStyle);
cell.setCellValue(balanceSheetVO.getRealName());    //贷款人

cell = bodyRow.createCell(1);
cell.setCellStyle(numberStyle);
cell.setCellValue(Double.parseDouble(balance.toString()));//期末余额

cell = bodyRow.createCell(2);
cell.setCellStyle(numberStyle);
cell.setCellValue(Double.parseDouble(recharge.toString()));  //本期充值金额

cell = bodyRow.createCell(3);
cell.setCellStyle(numberStyle);
cell.setCellValue(Double.parseDouble(beforeBalance.toString()));  //前期余额

cell = bodyRow.createCell(4);
cell.setCellStyle(numberStyle);
cell.setCellValue(Double.parseDouble(repayMoney.toString()));  //本期偿还金额
}
}
workBook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(outputStream != null){
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}