java中导出

实现步骤

        1. 生成文件名称

        2.定义好导出模板 (也可在java中代码实现,可以创建表头,表头默认为第一行)

        3.把模板塞入到工作簿中

        4.查询数据 填充到excel中

        5.创建输入流(把文件放入FileOutputStream管道中),在工作簿写入文件

       6.释放资源(关闭管道)

代码实现如下

/**
	 * 描述 :数据维护版本管理导出
	 * 
	 */
    @FileProvider
    public DownloadFile export(Map<String, Object> parameter) throws IOException, Exception{
    	
    	List<BondDetailCarbodyNo> list = new ArrayList<BondDetailCarbodyNo>();
    
    	
    	try {
	    	logger.debug("export()-> ");
	    	// 时间格式转换
	    	SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
	    	String SystemDate=df.format(new Date());
	    	//生成的文件名称
	    	String fileName = "数据维护版本管理_"+SystemDate+".xlsx";
	    	fileName=URLDecoder.decode(fileName, "UTF-8");
	    	String tempPath =ExcelUtils.getTemplate()+"/template/unit/bondDetailCarbodyNo.xlsx";
	    	InputStream instream = new FileInputStream(tempPath);
	    	// 创建工作薄 
	    	XSSFWorkbook wb = new XSSFWorkbook(instream);
	    	// 创建行
	    	XSSFRow row = null;
	    	// 创建列
	    	XSSFCell cell =null;
	        // 正文样式
	        XSSFCellStyle textStyle =ExcelUtils.initStyleX(wb,"ONEHEAD_STYLE");
	        
	        XSSFCellStyle cellStyle = wb.createCellStyle();
	        XSSFDataFormat format = wb.createDataFormat();
	        cellStyle.setDataFormat(format.getFormat("yyyy/m/d"));
	        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    		cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
    		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
    		cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
    		cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
    		
    		
    		
    		  // 获取第一个sheet页
	        XSSFSheet sheet = wb.getSheetAt(0);
	        
	        int rowNo = 1;
	        //链接数据库获取数据
	        BondDetailCarbodyNoSearch search = new BondDetailCarbodyNoSearch();
	        search= parameterOrSearch(parameter);
	        //查询总条数
	        Long count= bondDetailCarbodyNoService.queryBondDetailCount(search);
	        Long low = (long)0;
	        
			if(count>0){
				search.setCount(count);
				//抽取数据次数
	        	int pageNo=(int)(count/2000)+1;
				for (int i = 1; i <= pageNo; i++) {
					search.setPageSize(2000);
					search.setPageIndex(i);
					search.setLow(0);
					search.setHigh(2000);
					if(i>1){
						search.setLow(MathUtil.multiply(new BigDecimal(2000),new BigDecimal(i-1)).longValue());
						search.setHigh(MathUtil.multiply(new BigDecimal(2000),new BigDecimal(i)).longValue());
					}
				  
					list = bondDetailCarbodyNoService.queryBondDetail(search);
					
					/*	向单元格里填充获取到的数据   
					 * 	rowNo 第几行
					 * 	cellNo 第几列
					 * 
					 */
					
					if (list.size()>0) {
						
						for (BondDetailCarbodyNo bondDetailCarbodyNos : list) {
							row = sheet.createRow(rowNo);
							// 法人
			    			cell = row.createCell(0);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getCorp())){
			    				Branch branch = new Branch();
			    				branch.setCode(bondDetailCarbodyNos.getCorp());
			    				branch.setRepFlag("1");
			    				branch = branchService.getByBizKey(branch);
			    				if (branch != null) {
			    					cell.setCellValue(branch.getSname());
								}else {
									cell.setCellValue(bondDetailCarbodyNos.getCorp());
								}
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			
			    			// 车号
			    			cell = row.createCell(1);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getMachineSerialNo())){
			    				cell.setCellValue(bondDetailCarbodyNos.getMachineSerialNo());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			// 用户名
			    			cell = row.createCell(2);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getUseName())){
			    				cell.setCellValue(bondDetailCarbodyNos.getUseName());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			// 经销商
			    			cell = row.createCell(3);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getDbCode())){
			    				Branch branch = new Branch();
			    				branch.setCode(bondDetailCarbodyNos.getDbCode());
			    				branch.setRepFlag("0");
			    				branch.setType("1");//经销商
			    				branch = branchService.getByBizKey(branch);
			    				if (branch != null) {
			    					cell.setCellValue(branch.getSname());
								}else {
									cell.setCellValue(bondDetailCarbodyNos.getDbCode());
								}
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			// 等级
			    			cell = row.createCell(4);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getGrade())){
			    				cell.setCellValue(bondDetailCarbodyNos.getGrade());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			// 销售方式
			    			cell = row.createCell(5);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getSaleType())){
			    				Code code = new Code();// 数字字典
			    				code.setCodeId("CAUSE_SALE_TYPE");
			    				code.setCode(bondDetailCarbodyNos.getSaleType());
			    				code = codeService.getByBizKey(code);
			    				if (code != null) {
			    					cell.setCellValue(code.getCodeNameC());
								}else {
									cell.setCellValue(bondDetailCarbodyNos.getSaleType());
								}
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			// 债权分类 
			    			cell = row.createCell(6);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getBondType())){
			    				Code code = new Code();// 数字字典
			    				code.setCodeId("UNIT_BOND_DETAIL_TYPE");
			    				code.setCode(bondDetailCarbodyNos.getBondType());
			    				code = codeService.getByBizKey(code);
			    				if (code != null) {
			    					cell.setCellValue(code.getCodeNameC());
								}else {
									cell.setCellValue(bondDetailCarbodyNos.getBondType());
								}
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			
			    			//债权生成日期
			    			cell = row.createCell(7);
			    			if(bondDetailCarbodyNos.getBondDate() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getBondDate());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(cellStyle);
			    			
			    			// 首次出库日期
			    			cell = row.createCell(8);
			    			if(bondDetailCarbodyNos.getOutStoreDate() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getOutStoreDate());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(cellStyle);
			    			
			    			
			    			
			    			//年份
			    			cell = row.createCell(9);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getYears())){
			    				cell.setCellValue(bondDetailCarbodyNos.getYears());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			// 最近收款日
			    			cell = row.createCell(10);
			    			if(bondDetailCarbodyNos.getReceiveDate() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getReceiveDate());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(cellStyle);
			    			// 债权到期日
			    			cell = row.createCell(11);
			    			if(bondDetailCarbodyNos.getBondDueDate() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getBondDueDate());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(cellStyle);
			    			
			    			//剩余期数
			    			cell = row.createCell(12);
			    			if(bondDetailCarbodyNos.getNper()!= null){
			    				cell.setCellValue(bondDetailCarbodyNos.getNper());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//剩余期数
			    			cell = row.createCell(13);
			    			if(bondDetailCarbodyNos.getOverdueNumber()!= null){
			    				cell.setCellValue(bondDetailCarbodyNos.getOverdueNumber().intValue());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//当月本金
			    			cell = row.createCell(14);
			    			if(bondDetailCarbodyNos.getPrincipal() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getPrincipal().doubleValue());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			
			    			// 债权总额
			    			cell = row.createCell(15);
			    			if(bondDetailCarbodyNos.getBondAmt() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getBondAmt().doubleValue());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//逾期余额
			    			cell = row.createCell(16);
			    			if(bondDetailCarbodyNos.getOverdueBalance() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getOverdueBalance().doubleValue());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//逾期本金
			    			cell = row.createCell(17);
			    			if(bondDetailCarbodyNos.getOverduePrincipal() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getOverduePrincipal().doubleValue());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//实际逾期
			    			cell = row.createCell(18);
			    			if(bondDetailCarbodyNos.getOverdueAmt() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getOverdueAmt().doubleValue());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//管理逾期
			    			cell = row.createCell(19);
			    			if(bondDetailCarbodyNos.getManageOverdue() != null){
			    				cell.setCellValue(bondDetailCarbodyNos.getManageOverdue().doubleValue());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//风控等级
			    			cell = row.createCell(20);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getRiskGrade())){
			    				cell.setCellValue(bondDetailCarbodyNos.getRiskGrade());
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			
			    			//经销商属性
			    			cell = row.createCell(21);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getDbProperty())){
			    				Code code = new Code();// 数字字典
			    				code.setCodeId("NORMAL_STATUS");
			    				code.setCode(bondDetailCarbodyNos.getDbProperty());
			    				code = codeService.getByBizKey(code);
			    				if (code != null) {
			    					cell.setCellValue(code.getCodeNameC());
								}else {
									cell.setCellValue(bondDetailCarbodyNos.getDbProperty());
								}
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//设备分类
			    			cell = row.createCell(22);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getEquipment())){
			    				Code code = new Code();// 数字字典
			    				code.setCodeId("MODEL_TYPE");
			    				code.setCode(bondDetailCarbodyNos.getEquipment());
			    				code = codeService.getByBizKey(code);
			    				if (code != null) {
			    					cell.setCellValue(code.getCodeNameC());
			    				}else {
			    					cell.setCellValue(bondDetailCarbodyNos.getEquipment());
								}
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			//债权区分
			    			cell = row.createCell(23);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getBondType())){
			    				Code code = new Code();// 数字字典
			    				code.setCodeId("UNIT_BOND_DETAIL_TYPE");
			    				code.setCode(bondDetailCarbodyNos.getBondType());
			    				code = codeService.getByBizKey(code);
			    				if (code != null) {
			    					cell.setCellValue(code.getCodeNameC());
			    				}else {
			    					cell.setCellValue(bondDetailCarbodyNos.getBondType());
								}
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			cell.setCellStyle(textStyle);
			    			
			    			
			    			//版本
			    			cell = row.createCell(24);
			    			if(StringUtils.isNotBlank(bondDetailCarbodyNos.getStatus())){
			    				cell.setCellValue(bondDetailCarbodyNos.getStatus()=="1"?"提交版":"草稿版");
			    			}else{
			    				cell.setCellValue("");
			    			}
			    			
			    			cell.setCellStyle(textStyle);
			    			rowNo++;
						}
				     }
			      }
				//写入文件
				FileOutputStream out = new FileOutputStream(getDestPath("unit")+"/"+fileName);
				wb.write(out);
				out.close();
				DownloadFile files = getDownloadFile(fileName);
				return files;
			}else{
	    		//如果没有获取到数据返回空模板
		    	FileOutputStream out = new FileOutputStream(getDestPath("unit")+"/"+fileName);
		    	wb.write(out);
		    	out.close();
		    	DownloadFile files = getDownloadFile(fileName);
		    	return files;
	    	}
		}catch (Exception e) {
			logger.error("export() - ", e);
			ExceptionUtil.throwex(e,"");
		}
		return null;

2.设置表头正文格式代码如下

 /**
     * EXCEL样式
     * xlsx
     * initType:  
     * 		标题 :TITLE_STYLE
     * 		一级表头:ONEHEAD_STYLE
     * 		二级表头:TOWHEAD_STYLE
     * 		正文:TEXT_STYLE
     * @param workbook initType
     */
    public static XSSFCellStyle initStyleX(XSSFWorkbook workbook,String initType){
    	
    	XSSFCellStyle customStyle = workbook.createCellStyle();
    	if(initType.equals("ZHU_STYLE")){
    		customStyle.setAlignment(CellStyle.ALIGN_LEFT);
    		XSSFFont zhuFont = workbook.createFont(); 
    		zhuFont.setFontHeightInPoints((short) 12);// 字体大小
    		zhuFont.setColor(HSSFColor.RED.index);
    		customStyle.setFont(zhuFont);
    		
    	// 标题
    	}else if(initType.equals("TITLE_STYLE")){
    		customStyle.setAlignment(CellStyle.ALIGN_CENTER);
	        XSSFFont titlFont = workbook.createFont(); 
	        titlFont.setFontHeightInPoints((short) 20);// 行高
	        titlFont.setFontHeightInPoints((short) 16);// 字体大小
	        customStyle.setFont(titlFont);
	     // 一级表头
    	}else if(initType.equals("ONEHEAD_STYLE")){
    		customStyle.setAlignment(CellStyle.ALIGN_LEFT);
	        XSSFFont headerFont = workbook.createFont(); 
	        headerFont.setFontHeightInPoints((short) 12);// 字体大小
	        customStyle.setFont(headerFont);
	     // 二级表头
    	}else if(initType.equals("TOWHEAD_STYLE")){
    		customStyle.setFillBackgroundColor(HSSFColor.AQUA.index);
    		customStyle.setAlignment(CellStyle.ALIGN_CENTER);
    		XSSFFont headerFont = workbook.createFont(); 
    		headerFont.setFontHeightInPoints((short) 12);// 行高
    		headerFont.setFontHeightInPoints((short) 12);// 字体大小
 	        customStyle.setFont(headerFont);
    		customStyle.setFont(headerFont);// 粗体
    		customStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    		customStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());// 背景色
    		customStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
    	}
    	// 正文
    	if(initType.equals("TOWHEAD_STYLE") || initType.equals("TEXT_STYLE") || initType.equals("ONEHEAD_STYLE")){
    		customStyle.setAlignment(CellStyle.ALIGN_CENTER);
    		customStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
    		customStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
    		customStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
    		customStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
    	}
		return customStyle;
    }





阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zouliping123456/article/details/79966247
个人分类: java code
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭