poi之excel导出(不需要合并单元格)

excel处理类:

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

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.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class CreateExcel<T> {
	
	public void expExcel(String title, String[] headers,String[] context,Collection<T> dataset, OutputStream out){
		exportExcel(title,headers,context,dataset,out,"yyyy/mm/dd");
	}
	
	public void expExcel(String title, String[] headers,String[] context,
            Collection<T> dataset, OutputStream out, String pattern){
		exportExcel(title,headers,context,dataset,out,pattern);
	}
	
	public void setExcelResponse(HttpServletResponse response,String name,String[] headers,String[] context,Collection<T> dataset){
		OutputStream out = null;
		try {
			out = response.getOutputStream();
			response.reset();// 清空输出流
			response.setHeader("Content-disposition", "attachment; filename="
					+ new String(name.getBytes("GBK"), "ISO-8859-1") + ".xlsx");// 设定输出文件头
			response.setContentType("application/ms-excel;charset=UTF-8");// 定义输出类型
			expExcel(name, headers, context, dataset, out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void exportExcel(String title, String[] headers, String[] context, 
            Collection<T> dataset, OutputStream out, String pattern)  
    {  
        // 声明一个工作薄  
        XSSFWorkbook workbook = new XSSFWorkbook();  
        // 生成一个表格  
        XSSFSheet sheet = workbook.createSheet(title);  
        // 生成一个样式  
        XSSFCellStyle style = workbook.createCellStyle();         
        XSSFCellStyle style2 = workbook.createCellStyle(); 
        XSSFDataFormat format = workbook.createDataFormat(); 
        style2.setDataFormat(format.getFormat("@")); 
  
        // 产生表格标题行  
        XSSFRow row = sheet.createRow(0);  
        for (int i = 0; i < headers.length; i++)  
        {  
            XSSFCell cell = row.createCell(i);  
            cell.setCellStyle(style);  
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);  
            cell.setCellValue(text);  
        }  
  
        // 遍历集合数据，产生数据行  
        Iterator<T> it = dataset.iterator();  
        int index = 0;  
        while (it.hasNext())  
        {  
            index++;  
            row = sheet.createRow(index);  
            T t = (T) it.next();  
            for (int i = 0; i < context.length; i++)  
            {  
                XSSFCell cell = row.createCell(i);  
                cell.setCellStyle(style2);   
                String fieldName = context[i];  
                String getMethodName = "get"  
                        + fieldName.substring(0, 1).toUpperCase()  
                        + fieldName.substring(1);  
                try  
                {  
                    Class tCls = t.getClass();  
                    Method getMethod = tCls.getMethod(getMethodName,  
                            new Class[]  
                            {});
                    Object value = getMethod.invoke(t, new Object[]  
                    {}); 
                    // 判断值的类型后进行强制类型转换  
                    String textValue = null;  
                    if(value==null){
                    	continue;
                    }
                    else if (value instanceof Date)  
                    {  
                        Date date = (Date) value;  
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);  
                        textValue = sdf.format(date);  
                    }else if(value instanceof Double)
                    {
                    	 double dou=(Double)value;
                    	 DecimalFormat decimalFormat = new DecimalFormat("##0.00");//格式化设置  
                    	 textValue=decimalFormat.format(dou);
                    }else  
                    {  
                        // 其它数据类型都当作字符串简单处理  
                        textValue = value.toString();  
                    }  
                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成  
                    if (textValue != null)  
                    {  
                        Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");  
                        Matcher matcher = p.matcher(textValue);  
                        if (matcher.matches())  
                        {  
                            // 是数字当作double处理  
                            cell.setCellValue(Double.parseDouble(textValue));  
                        }  
                        else  
                        {  
//                            HSSFRichTextString richString = new HSSFRichTextString(  
//                                    textValue);  
//                            HSSFFont font3 = workbook.createFont();  
//                            font3.setColor(HSSFColor.BLUE.index);  
//                            richString.applyFont(font3);  
                            cell.setCellValue(textValue);  
                        }  
                    }  
                }  
                catch (SecurityException e)  
                {  
                    e.printStackTrace();  
                }  
                catch (NoSuchMethodException e)  
                {  
                    e.printStackTrace();  
                }  
                catch (IllegalArgumentException e)  
                {  
                    e.printStackTrace();  
                }  
                catch (IllegalAccessException e)  
                {  
                    e.printStackTrace();  
                }  
                catch (InvocationTargetException e)  
                {  
                    e.printStackTrace();  
                }  
                finally  
                {  
                    // 清理资源  
                }  
            }  
        }  
        try  
        {  
            workbook.write(out);  
        }  
        catch (IOException e)  
        {  
            e.printStackTrace();  
        }  
    }  
}

对应controller:

@RequestMapping(value = "conversionDownLoad")
	public void conversionDownLoad(SearchField sf, String date) throws UnsupportedEncodingException {
        String[] head={"时间","大区","区域","分公司","产品类型","贷款期限(期)",
        		"还款方式","贷款方式","贷款金额(元)","申请提交","贷款审核","贷款审核退回","贷款审核流失量","贷款审核转化率(%)","审批通过","审批退回","审批拒绝","审批客户取消","审批流失 ",
        		"审批通过的转化率(%)","合同签约成功量","合同作废数量","合同退回数量","合同流失量","合同签约转化率(%)","财务复核通过量","财务复核退回量","财务复核中遗失量",
        		"财务复核转化率(%)","放款复核通过量","放款复核退回量","放款复核流失量","放款复核转化率(%)","放款通过量","放款退回量","放款流失量","放款转化率(%)","结清量","展期量",
        		"展期中结清量","展期中结清率(%)","展期中再贷量","展期中再贷率(%)","结清转化率(%)","再贷数量","再贷转化率(%)"};
        String[] context={"date","bigAreaName","smallAreaName","siteName","productName",
        		"loanTime","hkfsValue","dkfs","amount","applyCount","applyAuditCount","applyAuditBackCount","applyAuidtLostCount","applyAuditRate","applyAuditLastCount",
        		"applyAuditLastBackCount","applyAuditLastRefuseCount","applyAuditCancelCount","applyAuditLastLostCount","applyAuditLastRate",
        		"contractCount","contractOverCount","contractBackCount","contractLostCount","contractRate","fnCheckCount","fnCheckBackCount",
        		"fnCheckLostCount","fnCheckRate","loanCheckCount","loanCheckBackCount","loanCheckLostCount","loanCheckRate","loanCount","loanBackCount",
        		"loanLostCount","loanRate","settleCount","extensionCount","extensionSettleCount","extensionSettleRate","extensionContinueLoanCount",
        		"extensionContinueLoanRate","settleRate","continueLoan","continueRate"};
        List<ConversionReport> datas = getReportDatas(sf, date);
        CreateExcel<ConversionReport> crt =new CreateExcel<ConversionReport>();
		String name="xx转化率数据分析表";		
		crt.setExcelResponse(response, name, head, context, datas);
	}