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

系统管理员 2022-07-21 00:22 185阅读 0赞

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);
    	}