导出excel的两种方式（二）

1.调用类如下：

@RequestMapping("/exportExcel4ServiceFee")
    public void exportExcel4ServiceFee(LoanSplitModel loanSplitModel,HttpServletRequest request, HttpServletResponse response) {
		try {
			PageView<LoanSplitModel> pageView = collectionService.downPageView4RemindersServiceFee(loanSplitModel);
			String[] titles = new String[]{"序号","合同编号","客户姓名","合同金额"};
			SXXExcel excel = new SXXExcel(titles);
			StringBuffer sb = null;
			int count = 1;
			for (LoanSplitModel item : pageView.getRecords()) {
				sb = new StringBuffer();
				sb = sb.append(count++).append(",")//序号
	                   .append(item.getContractCode() != null ? item.getContractCode() : "").append(",")//合同编号
	                   .append(item.getCustomerName() != null ? item.getCustomerName() : "").append(",")//客户姓名
	                   .append(item.getContractMoney() != null ? df.format(item.getContractMoney()) : "").append(",");//合同金额
				excel.addSXSSFRow(sb.toString().split(","));
			}
			excel.outSXSSFFile(response, "催收服务费统计表.xlsx");
		} catch (Exception e) {
			e.printStackTrace();
            logger.error("-----------催收服务费统计列表导出excel异常", e);
		}
    }

 SXXExcel类如下：

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class SXXExcel {
	
	private static SXSSFWorkbook workbook;
	private static CellStyle titleStyle; // 标题行样式
	private static Font titleFont; // 标题行字体
	private static CellStyle dateStyle; // 日期行样式
	private static Font dateFont; // 日期行字体
	private static CellStyle headStyle; // 表头行样式
	private static Font headFont; // 表头行字体
	private static CellStyle contentStyle; // 内容行样式
	private static Font contentFont; // 内容行字体
	private static CellStyle doubleContextStyle;
	
	/**
	 * @Description: 初始化
	 */
	private static void init() {
		workbook = new SXSSFWorkbook(1000);
		titleFont = workbook.createFont();
		titleStyle = workbook.createCellStyle();
		dateStyle = workbook.createCellStyle();
		dateFont = workbook.createFont();
		headStyle = workbook.createCellStyle();
		headFont = workbook.createFont();
		contentStyle = workbook.createCellStyle();
		contentFont = workbook.createFont();
		doubleContextStyle= workbook.createCellStyle();
		initTitleCellStyle();
		initTitleFont();
		initDateCellStyle();
		initDateFont();
		initHeadCellStyle();
		initHeadFont();
		initContentCellStyle();
		initContentFont();
		initDoubleContextStyle();
	}
	
	/**
	 * @Description: 初始化标题行样式
	 */
	private static void initTitleCellStyle() {
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		titleStyle.setFont(titleFont);
		titleStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());

	}

	/**
	 * @Description: 初始化日期行样式
	 */
	private static void initDateCellStyle() {
		dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
		dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		dateStyle.setFont(dateFont);
		dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex());
	}

	/**
	 * @Description: 初始化表头行样式
	 */
	private static void initHeadCellStyle() {

		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headStyle.setFont(headFont);
		headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
		headStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
		headStyle.setBorderBottom(CellStyle.BORDER_THIN);
		headStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headStyle.setBorderRight(CellStyle.BORDER_THIN);
		headStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
		headStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
		headStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
		headStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
	}

	/**
	 * @Description: 初始化内容行样式
	 */
	private static void initContentCellStyle() {
		contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
		contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		contentStyle.setFont(contentFont);
		contentStyle.setBorderTop(CellStyle.BORDER_THIN);
		contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
		contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
		contentStyle.setBorderRight(CellStyle.BORDER_THIN);
		contentStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setWrapText(true); // 字段换行
	}
	
	private static void initDoubleContextStyle(){
		doubleContextStyle.setAlignment(CellStyle.ALIGN_CENTER);
		doubleContextStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		doubleContextStyle.setFont(contentFont);
		doubleContextStyle.setBorderTop(CellStyle.BORDER_THIN);
		doubleContextStyle.setBorderBottom(CellStyle.BORDER_THIN);
		doubleContextStyle.setBorderLeft(CellStyle.BORDER_THIN);
		doubleContextStyle.setBorderRight(CellStyle.BORDER_THIN);
		doubleContextStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
		doubleContextStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
		doubleContextStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
		doubleContextStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
		doubleContextStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		doubleContextStyle.setWrapText(true); // 字段换行
	}

	/**
	 * @Description: 初始化标题行字体
	 */
	private static void initTitleFont() {
		titleFont.setFontName("华文楷体");
		titleFont.setFontHeightInPoints((short) 20);
		titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		titleFont.setCharSet(Font.DEFAULT_CHARSET);
		titleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}

	/**
	 * @Description: 初始化日期行字体
	 */
	private static void initDateFont() {
		dateFont.setFontName("隶书");
		dateFont.setFontHeightInPoints((short) 10);
		dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		dateFont.setCharSet(Font.DEFAULT_CHARSET);
		dateFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}

	/**
	 * @Description: 初始化表头行字体
	 */
	private static void initHeadFont() {
		headFont.setFontName("宋体");
		headFont.setFontHeightInPoints((short) 10);
		headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headFont.setCharSet(Font.DEFAULT_CHARSET);
		headFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}

	/**
	 * @Description: 初始化内容行字体
	 */
	private static void initContentFont() {
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 10);
		contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		contentFont.setCharSet(Font.DEFAULT_CHARSET);
		contentFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}
		
	public SXXExcel(String title, String[] headers){
		init();
		SXSSFSheet sheet = workbook.createSheet();
		SXSSFRow fristRow=sheet.createRow(0);
		SXSSFCell titleCell =fristRow.createCell(0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue(title);
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1));
		SXSSFRow dateRow=sheet.createRow(1);
		SXSSFCell dateCell = dateRow.createCell(0);
		dateCell.setCellStyle(dateStyle);
		dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd")
		.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(1,1,0,headers.length-1));
		
		SXSSFRow headerRow=sheet.createRow(2);
		for(int i=0;i<headers.length;i++){
			SXSSFCell cell = headerRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(headers[i]);
		}
	}
	
	public SXXExcel(String[] headers){
		init();
		SXSSFSheet sheet = workbook.createSheet();
		SXSSFRow headerRow=sheet.createRow(0);
		for(int i=0;i<headers.length;i++)
		{
			SXSSFCell cell = headerRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(headers[i]);
		}
	}
	
	/**
	 * 往EXCEL追加数据
	 * @param filePath
	 * @param rows
	 */
	public void addSXSSFRow(List<Object[]> rows){
		SXSSFSheet sheet = workbook.getSheetAt(0);
		for(Object[] cells : rows)
		{
			SXSSFRow excelRow=sheet.createRow(sheet.getLastRowNum()+1);
			for(int i=0;i<cells.length;i++)
			{
				if(cells[i] instanceof Number)
				{
					SXSSFCell cell = excelRow.createCell(i);
					cell.setCellStyle(contentStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellValue(((Number)cells[i]).doubleValue());
				}
				else
				{
					SXSSFCell cell = excelRow.createCell(i);
					cell.setCellStyle(contentStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(this.toString(cells[i]));
				}
			}
		}
	}
	
	/**
	 * 往EXCEL追加数据
	 * @param filePath
	 * @param rows
	 */
	public void addSXSSFRow(Object[] cells){
		SXSSFSheet sheet = workbook.getSheetAt(0);
		SXSSFRow excelRow=sheet.createRow(sheet.getLastRowNum()+1);
		Pattern p2=Pattern.compile("^(([1-9]\\d{0,9})|0)(\\.\\d{2})+$");
		
		for(int i=0;i<cells.length;i++)
		{
			sheet.setColumnWidth(i, 5900);
			if(cells[i] instanceof BigDecimal)
			{
				SXSSFCell cell = excelRow.createCell(i);
				cell.setCellStyle(contentStyle);
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(((BigDecimal)cells[i]).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue());
			}
			else
			{
				if(null !=cells[i] && !"".equals(cells[i].toString())){
					boolean flag2 = p2.matcher(cells[i].toString()).matches();
					if (flag2) {
						SXSSFCell cell = excelRow.createCell(i);
						cell.setCellStyle(contentStyle);
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(Double.parseDouble(cells[i].toString()));
					}else {
						SXSSFCell cell = excelRow.createCell(i);
						cell.setCellStyle(contentStyle);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(this.toString(cells[i]));
					}
				}else {
					SXSSFCell cell = excelRow.createCell(i);
					cell.setCellStyle(contentStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(this.toString(cells[i]));
				}
			}
		}
	}
	
	public void outSXSSFFile(String filePath){
		FileOutputStream outFile = null;
		java.io.BufferedOutputStream outStream = null;
		try {
			File file = new File(filePath);
			file.deleteOnExit();
			file.createNewFile();
			outFile = new FileOutputStream(filePath);
			outStream = new BufferedOutputStream(outFile);
			workbook.write(outStream);
			outStream.flush();
			workbook.dispose();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			IOUtils.closeQuietly(outFile);
			IOUtils.closeQuietly(outStream);
		}
	}
	
	public void outSXSSFFile(HttpServletResponse response, String filename){
		ServletOutputStream outStream = null;
		try {
			filename = new String(filename.getBytes("utf-8"),"iso-8859-1");
			response.setCharacterEncoding("utf-8");
			response.setContentType("multipart/form-data");
			response.setHeader("Content-Disposition", "attachment;fileName="+ filename);
			outStream = response.getOutputStream();
			workbook.write(outStream);
			outStream.flush();
			workbook.dispose();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			IOUtils.closeQuietly(outStream);
		}
	}
	
	private  String toString(Object val){
		if(val==null)
			return "";
		if(val instanceof String)
			return (String)val;
		else if(val instanceof Integer)
			return String.valueOf(val);
		else if(val instanceof Long)
			return String.valueOf(val);
		else if(val instanceof java.math.BigDecimal){
			NumberFormat format=NumberFormat.getNumberInstance() ;
			format.setMaximumFractionDigits(2);
			format.setGroupingUsed(false);
			return format.format(val) ; 
		}else if(val instanceof java.util.Date){
			SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
			return format.format(val) ; 
		}else{
			return val.toString();
		}
	}
}