poi导出数据(需要合并单元格)

以你之姓@ 2022-07-16 06:19 248阅读 0赞

import java.io.IOException;
    import java.io.OutputStream;
    import java.io.UnsupportedEncodingException;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.math.RoundingMode;
    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();
    			}
    		}
    	}
    	
    	public static void setExcelResponse(HttpServletResponse response,String name,XSSFWorkbook workbook){
    		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");// 定义输出类型
    			workbook.write(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);    
                    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[]  
                        {}); 
                        // 判断值的类型后进行强制类型转换  
                        if(value==null){
                        	continue;
                        }else   
                        {  
                        	getValue(cell,value,pattern,style2);
                        }
                    }  
                    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();  
            }  
        } 
    	
    	/**
    	 * 获取时间格式的值
    	 * @param cell
    	 * @param value
    	 * @param style
    	 */
    	public static void getValue(XSSFCell cell,Object value,XSSFCellStyle style){
    		getValue(cell,value,"yyyy/MM/dd",style);
    	}
    	
    	/**
    	 * 通过反射获取值
    	 * @param fieldName
    	 * @param obj
    	 * @param className
    	 * @return
    	 */
    	@SuppressWarnings({ "rawtypes", "unchecked" })
    	public static Object getValueByReflect(String fieldName,Object obj,String className){
    		String getMethodName = "get"  
                    + fieldName.substring(0, 1).toUpperCase()  
                    + fieldName.substring(1);   		
    		try {
    			Class tcls=Class.forName(className);
    			Method	getMethod = tcls.getMethod(getMethodName,  new Class[]  {});
    			Object value = getMethod.invoke(obj, new Object[] {});
    			return value;
    		} catch (NoSuchMethodException e) {
    			e.printStackTrace();
    		} catch (IllegalAccessException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (IllegalArgumentException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (InvocationTargetException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (ClassNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}  
    		return null;
    	}
    	
    	/**
    	 * 把值写入excel
    	 * @param cell
    	 * @param value
    	 * @param pattern
    	 * @param style
    	 */
    	public static void getValue(XSSFCell cell,Object value,String pattern,XSSFCellStyle style){
    		String textValue=null;
    		 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.0000");//格式化设置  
             	 decimalFormat.setRoundingMode(RoundingMode.HALF_UP);
             	 textValue=decimalFormat.format(dou);
             }else  
             {  
                 if(value!=null){
                	 textValue = value.toString();  
                 }
             }  
    		 if(style!=null){
    	    	 cell.setCellStyle(style);
    		 }
    		 if (textValue != null)  
             {  
                 Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");  
                 Matcher matcher = p.matcher(textValue);  
                 if (matcher.matches())  
                 {  
                     // 是数字当作double处理  
                     cell.setCellValue(Double.parseDouble(textValue));
                 }  
                 else  
                 {  
                     cell.setCellValue(textValue);  
                 }  
             }  
    		 cell.setCellValue(textValue);
    	}
    	
    	/**
    	 * 根据实体字段转化对应的实际类型
    	 * @param cell
    	 * @param value
    	 * @param pattern
    	 * @param style
    	 */
    	public static void getActualValue(XSSFCell cell,Object value,String pattern,XSSFCellStyle style){
            String textValue=null;
             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.0000");//格式化设置  
                 decimalFormat.setRoundingMode(RoundingMode.HALF_UP);
                 textValue=decimalFormat.format(dou);
             }else  
             {  
                 if(value!=null){
                     textValue = value.toString();  
                 }
             }  
             if(style!=null){
                 cell.setCellStyle(style);
             }
             cell.setCellValue(textValue);
        }
    	
    	/**
    	 * 填充数据(利用反射循环填充一行数据)
    	 * @param rowNum 行数
    	 * @param sheet sheet页
    	 * @param style  样式
    	 * @param obj   数据对象
    	 * @param keyBean  反射的熟悉名（按excel顺序）
    	 * @param cla    反射的bean
    	 * @return
    	 */
    	public static int setValueForExcel(int rowNum, XSSFSheet sheet,XSSFCellStyle style,Object obj,String[] keyBean,@SuppressWarnings("rawtypes") Class cla){
    		if(obj==null){
    			return rowNum;
    		}
    		XSSFRow row = sheet.createRow(rowNum);
    		for(int i=0;i<keyBean.length;i++){
    			Object value=getValueByReflect(keyBean[i], obj, cla.getName());
    			getValue(row.createCell(i), value, style);
    		}
    		return rowNum;
    	}
    	
    	/**
    	 * 填充数据(利用反射循环填充一行数据)
    	 * @param rowNum 行数
    	 * @param sheet sheet页
    	 * @param style  样式
    	 * @param obj   数据对象
    	 * @param keyBean  反射的熟悉名（按excel顺序）
    	 * @param cla    反射的bean
    	 * @return
    	 */
    	public static void setValueForExcel(XSSFRow row,int firCol, XSSFSheet sheet,XSSFCellStyle style,Object obj,String[] keyBean,@SuppressWarnings("rawtypes") Class cla){
    		for(int i=0;i<keyBean.length;i++){
    			Object value=getValueByReflect(keyBean[i], obj, cla.getName());
    			getValue(row.createCell(i+firCol), value, style);
    		}
    	}
    	
    	
    	
    	/**
    	 * 当值遇到-1时，变为-(此方法有待修改，可以封装全面一些)
    	 * @param value
    	 * @return
    	 */
    	public static Object changeValueByMark(Object value){
    		try {
    			int val = (int)Double.parseDouble(value.toString());
    			if (val==-1) {
    				return "-";
    			} else {
    				return value;
    			}
    		} catch (Exception e) {
                return value;
    		}
    	}
    }

对应的service:

@Override
        public void expExcelAll(String siteNo, String productType, String startDate, String endDate, HttpServletResponse response) {
         // 声明一个工作薄  
            XSSFWorkbook workbook = new XSSFWorkbook();  
            // 生成一个表格  
            XSSFSheet sheet = workbook.createSheet("详细数据");
            sheet.setDefaultColumnWidth(20);
            XSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            int num=0;
    
            //单元格,createCell(i),这里的i代表单元格是第几列，CellRangeAddress(firstRow,lastRow,firstCol,lastCol)里的参数分别表示需要合并的单元格起始行，起始列
            XSSFRow firstRow = sheet.createRow(num);
            CreateExcel.getValue(firstRow.createCell(0),"放款日期",style);
            sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
            CreateExcel.getValue(firstRow.createCell(1),"合同信息",style);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 15));
            CreateExcel.getValue(firstRow.createCell(16),"本次付款信息",style);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 31));
            
            XSSFRow secondRow = sheet.createRow(++num);
            CreateExcel.getValue(secondRow.createCell(16),"其他收费",style);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 30));
            CreateExcel.getValue(secondRow.createCell(31),"费用合计",style);
            sheet.addMergedRegion(new CellRangeAddress(1, 2, 31, 31));
            
            XSSFRow thirdRow = sheet.createRow(++num);
            CreateExcel.getValue(thirdRow.createCell(1),"借款人",style);
            CreateExcel.getValue(thirdRow.createCell(2),"合同号",style);
            CreateExcel.getValue(thirdRow.createCell(3),"分公司",style);
            CreateExcel.getValue(thirdRow.createCell(4),"是否直销",style);
            CreateExcel.getValue(thirdRow.createCell(5),"合作机构",style);
            CreateExcel.getValue(thirdRow.createCell(6),"贷款类型",style); 
            CreateExcel.getValue(thirdRow.createCell(7),"产品类型",style);
            CreateExcel.getValue(thirdRow.createCell(8),"还款方式",style);
            CreateExcel.getValue(thirdRow.createCell(9),"贷款期数",style);
            CreateExcel.getValue(thirdRow.createCell(10),"客户主任",style);
            CreateExcel.getValue(thirdRow.createCell(11),"客户经理",style);
            CreateExcel.getValue(thirdRow.createCell(12),"计息本金",style);
            CreateExcel.getValue(thirdRow.createCell(13),"付款类型",style);
            CreateExcel.getValue(thirdRow.createCell(14),"付款金额",style);
            CreateExcel.getValue(thirdRow.createCell(15),"履约保证金",style);
            CreateExcel.getValue(thirdRow.createCell(16),"考察费",style);
            CreateExcel.getValue(thirdRow.createCell(17),"GPS费",style);
            CreateExcel.getValue(thirdRow.createCell(18),"抵押登记费",style);
            CreateExcel.getValue(thirdRow.createCell(19),"停车费",style);
            CreateExcel.getValue(thirdRow.createCell(20),"盗抢险",style);
            CreateExcel.getValue(thirdRow.createCell(21),"刑侦费",style);
            CreateExcel.getValue(thirdRow.createCell(22),"评估费",style);
            CreateExcel.getValue(thirdRow.createCell(23),"律师签证费",style);
            CreateExcel.getValue(thirdRow.createCell(24),"加急费",style);
            CreateExcel.getValue(thirdRow.createCell(25),"风险金",style);
            CreateExcel.getValue(thirdRow.createCell(26),"抵押登记",style);
            CreateExcel.getValue(thirdRow.createCell(27),"手续费",style);
            CreateExcel.getValue(thirdRow.createCell(28),"征信费",style);
            CreateExcel.getValue(thirdRow.createCell(29),"快递费",style);
            CreateExcel.getValue(thirdRow.createCell(30),"其他",style);
            //单元格里面的值对应的实体bean字段
            String[] keyBean = {"confirmDate","custName","contractNo","siteName","isDirect","cooperation","loanType","productTypeName","paymentTypeName",
                    "totalPhases","customerDirector","customerManager","auditamt","payType","payAmt","lybzjFees","kcFeeS",
                    "gpsFees","dydjFees","tcFees","dqxFees","xzFees","pgFees","lsjzFees","jjFees","kdFees","gzFees","sxFees","zxsxFees","shouldglf","qtdsFees","total"};
            List<LoanCountVo>  loanCountVos = this.getAllLoanCountsForExport(siteNo, productType, startDate, endDate);
            for(int i=0;i<loanCountVos.size();i++){
                XSSFRow row = sheet.createRow(++num);
                for(int j=0;j<keyBean.length;j++){
                    Object value = CreateExcel.getValueByReflect(keyBean[j], loanCountVos.get(i), LoanCountVo.class.getName()); //设置单元格的值
                    CreateExcel.getActualValue(row.createCell(j), value,"yyyy/MM/dd", style);
                }           
            }        
            CreateExcel.setExcelResponse(response,"费用类科目自动对账全部数据",workbook);
        }