EXCEL导出汇总

1.EXCEL导出工具类

（1）.xlsx后缀的EXCEL导出工具类

XSSFWorkbook

（2）.xls后缀的EXCEL导出工具类

HSSFWorkbook

2.导出格式为.xlsx后缀的Excel后台导出功能

//接收NEC明细列表（全件、非全件）——未开票的准备数据——导出EXCEL
	@SuppressWarnings({ "unchecked"})
	public void doNotNeedSessionAndSecurity_wkpListExport(){
		try {
			this.getRequest().setCharacterEncoding("utf-8");
			this.getResponse().setCharacterEncoding("utf-8");
			/* 得到当前从页面传递回来的类型参数 */
			String time = this.getRequest().getParameter("time");
			String DJHM_TIME = time.replace("-", "");
			String search_type = this.getRequest().getParameter("search_type");
			String name = "";
			String XYSTATUS = "";
			String sql = "";
			if(search_type.equals("1")){
				XYSTATUS = "Y";
				name = "接收成功的待开具发票明细报表（全件数据）";
			}else if(search_type.equals("2")){
				XYSTATUS = "N";
				name = "已验证通过明细报表（非全件数据）";
			}
			sql = "select GFHM,GFMC,STORE,DPT,"
					+ "JKSEQ,SSFLBM,SPMC,concat(SLV,'') SLV,concat(JE,'') JE,"
					+"DATE_ADD(date_format(substring(DJHM,1,8),'%Y-%m-%d'),"
					+ "INTERVAL 1 DAY) DJHM "
					+"from t_necdata_dzfp where XYSTATUS='"+XYSTATUS
					+"' and substring(DJHM,1,8)='"+DJHM_TIME+"'"
					+ " ORDER BY GFHM asc";
			List<HashMap<String,String>> list = billListServiceI.findBySql(sql);
			
			// 第一步，创建一个webbook，对应一个Excel文件
			XSSFWorkbook wb = new XSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			XSSFSheet sheet = wb.createSheet("Sheet1");
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			XSSFRow row0 = sheet.createRow((int) 0);
			XSSFRow row1 = sheet.createRow((int) 1);
			// 第四步，创建单元格，并设置值表头 设置表头居中
			
			short width = 12;
			sheet.setDefaultColumnWidth(width);//设置默认宽度
			
			//设置表头样式
			XSSFCellStyle styleTitle = wb.createCellStyle();
			styleTitle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
			styleTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);//前景填充色
			//styleTitle.setFillPattern(XSSFCellStyle.FINE_DOTS); 
			//styleTitle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置背景色    
			styleTitle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
			styleTitle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			styleTitle.setBorderBottom(XSSFCellStyle.BORDER_NONE); //下边框    
			styleTitle.setBorderTop(XSSFCellStyle.BORDER_NONE);//上边框  
			styleTitle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框    
			styleTitle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框  
			styleTitle.setWrapText(true);  // 开启自动换行
			XSSFFont font = wb.createFont();    
			font.setFontName("仿宋_GB2312");    
			font.setFontHeightInPoints((short) 10);    
			styleTitle.setFont(font);//选择需要用到的字体格式   
			
			//设置内容样式
			XSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
			style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style.setWrapText(true);  // 开启自动换行
			row1.setHeightInPoints(25);//设置表头高度
			style.setFont(font);//选择需要用到的字体格式   
			
			//设置标题样式
			XSSFCellStyle styleT = wb.createCellStyle();
			styleT.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
			styleT.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			styleT.setWrapText(true);  // 开启自动换行
			XSSFFont font2 = wb.createFont();    
			font2.setFontName("仿宋_GB2312");    
			font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示    
			font2.setFontHeightInPoints((short) 12);    
			styleT.setFont(font2);//选择需要用到的字体格式   
			
			XSSFCell row0_cell0 = row0.createCell(0);
			sheet.addMergedRegion(new CellRangeAddress(0,0,0,10));//合并单元格，第一行表名
			row0_cell0.setCellStyle(styleT);
			row0_cell0.setCellValue(name);
			row0.setHeightInPoints(25);//设置第一行高度
			
			XSSFCell cell0 = row1.createCell(0);
			cell0.setCellStyle(styleTitle);
			cell0.setCellValue("序号");
			XSSFCell cell1 = row1.createCell(1);
			cell1.setCellStyle(styleTitle);
			cell1.setCellValue("供应商编码");
			XSSFCell cell2 = row1.createCell(2);
			cell2.setCellStyle(styleTitle);
			cell2.setCellValue("供应商名称");
			XSSFCell cell3 = row1.createCell(3);
			cell3.setCellStyle(styleTitle);
			cell3.setCellValue("店铺号");
			XSSFCell cell4 = row1.createCell(4);
			cell4.setCellStyle(styleTitle);
			cell4.setCellValue("DPT");
			XSSFCell cell5 = row1.createCell(5);
			cell5.setCellStyle(styleTitle);
			cell5.setCellValue("交款序列号");
			XSSFCell cell6 = row1.createCell(6);
			cell6.setCellStyle(styleTitle);
			cell6.setCellValue("税收分类编码");
			XSSFCell cell7 = row1.createCell(7);
			cell7.setCellStyle(styleTitle);
			cell7.setCellValue("项目名称");
			XSSFCell cell8 = row1.createCell(8);
			cell8.setCellStyle(styleTitle);
			cell8.setCellValue("税率");
			XSSFCell cell9 = row1.createCell(9);
			cell9.setCellStyle(styleTitle);
			cell9.setCellValue("金额（含税）");
			XSSFCell cell10 = row1.createCell(10);
			cell10.setCellStyle(styleTitle);
			cell10.setCellValue("实际支付日");
			
			if(list!=null&&list.size()>0){
				for (int j = 0; j < list.size(); j++) {
					//XSSFRow r = sheet.createRow((int) (j + 2));
					Row r = sheet.createRow(j + 2);
					r.setHeightInPoints(60);//设置内容高度
					
					Cell r_cell0 = r.createCell(0);
					r_cell0.setCellStyle(style);
					r_cell0.setCellValue(""+(j + 1));
					
					String GFHM = (String) list.get(j).get("GFHM");
					Cell r_cell1 = r.createCell(1);
					r_cell1.setCellStyle(style);
					r_cell1.setCellValue(GFHM);
					String GFMC = (String) list.get(j).get("GFMC");
					Cell r_cell2 = r.createCell(2);
					r_cell2.setCellStyle(style);
					r_cell2.setCellValue(GFMC);
					String STORE = (String) list.get(j).get("STORE");
					Cell r_cell3 = r.createCell(3);
					r_cell3.setCellStyle(style);
					r_cell3.setCellValue(STORE);
					String DPT = (String) list.get(j).get("DPT");
					Cell r_cell4 = r.createCell(4);
					r_cell4.setCellStyle(style);
					r_cell4.setCellValue(DPT);
					String JKSEQ = (String) list.get(j).get("JKSEQ");
					Cell r_cell5 = r.createCell(5);
					r_cell5.setCellStyle(style);
					r_cell5.setCellValue(JKSEQ);
					String SSFLBM = (String) list.get(j).get("SSFLBM");
					Cell r_cell6 = r.createCell(6);
					r_cell6.setCellStyle(style);
					r_cell6.setCellValue(SSFLBM);
					String SPMC = (String) list.get(j).get("SPMC");
					Cell r_cell7 = r.createCell(7);
					r_cell7.setCellStyle(style);
					r_cell7.setCellValue(SPMC);
					String SLV = (String) list.get(j).get("SLV");
					Cell r_cell8 = r.createCell(8);
					r_cell8.setCellStyle(style);
					r_cell8.setCellValue(SLV);
					String JE = (String) list.get(j).get("JE");
					Cell r_cell9 = r.createCell(9);
					r_cell9.setCellStyle(style);
					r_cell9.setCellValue(JE);
					String DJHM = (String) list.get(j).get("DJHM");
					Cell r_cell10 = r.createCell(10);
					r_cell10.setCellStyle(style);
					r_cell10.setCellValue(DJHM);
	
				}
			}
			String uploadPath = ServletActionContext.getServletContext()
					.getRealPath("upload");

			SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");// 设置日期格式
			String date = df.format(new Date());// new Date()为获取当前系统时间
			String excelName = "";
			excelName = date + ".xlsx";
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(uploadPath + "/"
						+ excelName);
				wb.write(fout);
				fout.close();
			} catch (Exception e) {
				e.printStackTrace();
				BaseAction.LOG.info("接收NEC明细列表（全件、非全件）——未开票的准备数据——导出EXCEL:doNotNeedSessionAndSecurity_wkpListExport 时间:"+new Date());
			}

			Json json = new Json();
			json.setSuccess(true);
			json.setMsg(excelName);
			writeJson(json);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

3.导出后的EXCEL模板