poi导出(带自选地址)

1.导包

poi-3.7.jar

2.工具类

public class PoiExcelExport {
	HttpServletResponse response;
	// 文件名
	private String fileName ;
	//文件保存路径
	private String fileDir;
	//sheet名
	private String sheetName;
	//表头字体
	private String titleFontType = "Arial Unicode MS";
	//表头背景色
	private String titleBackColor = "C1FBEE";
	//表头字号
	private short titleFontSize = 12;
	//添加自动筛选的列 如 A:M
	private String address = "";
	//正文字体
	private String contentFontType = "Arial Unicode MS";
	//正文字号
	private short contentFontSize = 12;
	//设置列的公式
	private String colFormula[] = null;
	
	private HSSFWorkbook workbook = null;
	
	public PoiExcelExport(String fileDir,String sheetName){
	     this.fileDir = fileDir;
	     this.sheetName = sheetName;
	     workbook = new HSSFWorkbook();
	}
	
	public PoiExcelExport(HttpServletResponse response,String fileName,String sheetName){
		 this.response = response;
		 this.fileName = fileName;
		 this.sheetName = sheetName;
	     workbook = new HSSFWorkbook();
	}
   /**
    * 设置表头字体.
    * @param titleFontType
    */
	public void setTitleFontType(String titleFontType) {
		this.titleFontType = titleFontType;
	}
   /**
    * 设置表头背景色.
    * @param titleBackColor 十六进制
    */
	public void setTitleBackColor(String titleBackColor) {
		this.titleBackColor = titleBackColor;
	}
   /**
    * 设置表头字体大小.
    * @param titleFontSize
    */
	public void setTitleFontSize(short titleFontSize) {
		this.titleFontSize = titleFontSize;
	}
   /**
    * 设置表头自动筛选栏位,如A:AC.
    * @param address
    */
	public void setAddress(String address) {
		this.address = address;
	}
   /**
    * 设置正文字体.
    * @param contentFontType
    */
	public void setContentFontType(String contentFontType) {
		this.contentFontType = contentFontType;
	}
   /**
    * 设置正文字号.
    * @param contentFontSize
    */
	public void setContentFontSize(short contentFontSize) {
		this.contentFontSize = contentFontSize;
	}
	/**
	 * 设置列的公式 
	 * @param colFormula  存储i-1列的公式 涉及到的行号使用@替换 如A@+B@
	 */
	public void setColFormula(String[] colFormula) {
		this.colFormula = colFormula;
	}
	/**
	 * 导出excel.在一个页面中单独导出Excel
	 * @param titleColumn  对应bean的属性名
	 * @param titleName   excel要导出的表名
	 * @param titleSize   列宽
	 * @param dataList  数据
	 */
	public void wirteExcel(String titleColumn[],String titleName[],int titleSize[],List<?> dataList){
		HttpServletRequest request = ServletActionContext.getRequest();
		//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
		Sheet sheet = workbook.createSheet(this.sheetName);  
		//新建文件
		OutputStream out = null;
		try {	 
			if(fileDir!=null){
				deleteExcel(fileDir);
				//有文件路径
				out = new FileOutputStream(fileDir);    			
			}else{
				//否则，直接写到输出流中
				out = response.getOutputStream();
				fileName = fileName+".xls";
				response.setContentType("application/x-msdownload");
				final String userAgent = request.getHeader("USER-AGENT");  //获取浏览器的代理
				//下面主要是让文件名适应不同浏览器的编码格式
				String finalFileName = null;
				if(StringUtils.contains(userAgent, "MSIE")) {
					finalFileName = URLEncoder.encode(fileName,"UTF8");
				}else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
					finalFileName = new String(fileName.getBytes(), "ISO8859-1");
				}else{
					finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
				}
				response.setHeader("Content-Disposition", "attachment; filename=\"" + 
						finalFileName + "\"");
			}
			
			//写入excel的表头
			Row titleNameRow = workbook.getSheet(sheetName).createRow(0); 
			
			//设置样式
			HSSFCellStyle titleStyle = workbook.createCellStyle();  
			titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, titleFontType, (short) titleFontSize);
			titleStyle = (HSSFCellStyle) setColor(titleStyle, titleBackColor, (short)10);
			titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			
			for(int i = 0;i < titleName.length;i++){
				sheet.setColumnWidth(i, titleSize[i]*256);    //设置宽度   		
				Cell cell = titleNameRow.createCell(i);
				cell.setCellStyle(titleStyle);
				cell.setCellValue(titleName[i].toString());
			}
			
			//为表头添加自动筛选
			if(!"".equals(address)){
				CellRangeAddress c = (CellRangeAddress) CellRangeAddress.valueOf(address);
				sheet.setAutoFilter(c);
			}
			
				//设置样式
				titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, contentFontType, (short) contentFontSize);
				//通过反射获取数据并写入到excel中
				if(dataList!=null&&dataList.size()>0){
				if(titleColumn.length>0){
					for(int rowIndex = 1;rowIndex<=dataList.size();rowIndex++){
						Object obj = dataList.get(rowIndex-1);     //获得该对象
						Class clsss = obj.getClass();     //获得该对对象的class实例
						HSSFSheet sheet2 = workbook.getSheet(sheetName);
						Row dataRow = sheet2.createRow(rowIndex);  
						
						for(int columnIndex = 0;columnIndex<titleColumn.length;columnIndex++){
							String title = titleColumn[columnIndex].toString().trim();
							if(!"".equals(title)){  //字段不为空
								//使首字母大写
								String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其首字母大写;
								String methodName  = "get"+UTitle;
								
								// 设置要执行的方法
								Method method = clsss.getDeclaredMethod(methodName); 
								
								//获取返回类型
								Class<?> returnType2 = method.getReturnType();
								String returnType = returnType2.getName(); 
								String data = method.invoke(obj)==null?"":method.invoke(obj).toString();
								Cell cell = dataRow.createCell(columnIndex);
								cell.setCellStyle(createCellContentStyle(workbook));
								if(data!=null&&!"".equals(data)){
									if("int".equals(returnType)){
										cell.setCellValue(Integer.parseInt(data));
										cell.setCellStyle(createCellContent4IntegerStyle(workbook));
									}else if("long".equals(returnType)){
										cell.setCellValue(Long.parseLong(data));
										cell.setCellStyle(createCellContent4IntegerStyle(workbook));
									}else if("float".equals(returnType)){
										cell.setCellValue(Float.parseFloat(data));
										cell.setCellStyle(createCellContent4DoubleStyle(workbook));
									}else if("double".equals(returnType)){
										cell.setCellValue(Double.parseDouble(data));
										cell.setCellStyle(createCellContent4DoubleStyle(workbook));
									}else{
										if(data.matches("\\d+")==true) {//判断能否转成数字
//											if(Long.parseLong(data)<2147483647) {//可转int类型
//												cell.setCellValue(Integer.parseInt(data));
//											}else {  //大于int类型
//												cell.setCellValue(data);  //cell.setCellValue(Long.parseLong(data));
//											}
											cell.setCellValue("'"+data);//转换成文本形式 避免 0001导出变成1
										}else {
											cell.setCellValue(data);
										}
									}
								}
							}else{   //字段为空 检查该列是否是公式
								if(colFormula!=null){
									String sixBuf = colFormula[columnIndex].replace("@", (rowIndex+1)+"");
									Cell cell = dataRow.createCell(columnIndex);
									cell.setCellFormula(sixBuf.toString());
								}
							}
						}
					}
					
				}
			}
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {  
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}  
	}
   /**
    * 将16进制的颜色代码写入样式中来设置颜色
    * @param style  保证style统一
    * @param color 颜色：66FFDD
    * @param index 索引 8-64 使用时不可重复
    * @return
    */
   public CellStyle setColor(CellStyle style,String color,short index){
   	if(color!=""&&color!=null){
			//转为RGB码
   		int r = Integer.parseInt((color.substring(0,2)),16);   //转为16进制
   		int g = Integer.parseInt((color.substring(2,4)),16);
   		int b = Integer.parseInt((color.substring(4,6)),16);
   		//自定义cell颜色
   		HSSFPalette palette = workbook.getCustomPalette(); 
   		palette.setColorAtIndex((short)index, (byte) r, (byte) g, (byte) b);
   	
   		style.setFillPattern(CellStyle.SOLID_FOREGROUND); 
   		style.setFillForegroundColor(index);
		}
       return style;	
   }
  
   /**
    * 设置字体并加外边框
    * @param style  样式
    * @param style  字体名
    * @param style  大小
    * @return
    */
   public CellStyle setFontAndBorder(CellStyle style,String fontName,short size){
   	HSSFFont font = workbook.createFont();  
       font.setFontHeightInPoints(size);    
       font.setFontName(fontName); 
//       font.setBold(true);
       style.setFont(font);
       style.setBorderBottom(CellStyle.BORDER_THIN); //下边框    
       style.setBorderLeft(CellStyle.BORDER_THIN);//左边框    
       style.setBorderTop(CellStyle.BORDER_THIN);//上边框    
       style.setBorderRight(CellStyle.BORDER_THIN);//右边框   
       return style;
   }
   /**
	 * 删除文件，当文件名出现重复的时候调用
	 * @param fileDir
	 * @return
	 */
   public boolean deleteExcel(String path){
   	boolean flag = false;
   	File file = new File(path);
   	// 判断目录或文件是否存在  
       if (!file.exists()) {  // 不存在返回 false  
           return flag;  
       } else {  
           // 判断是否为文件  
           if (file.isFile()) {  // 为文件时调用删除文件方法  
               file.delete();
               flag = true;
           } 
       }
       return flag;
   }
   
   /**
    * 单元格样式(Double)列表
    */
   private  CellStyle createCellContent4DoubleStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 正文样式
       style.setFillPattern(XSSFCellStyle.NO_FILL);
       style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
       // 把字体应用到当前的样式
       style.setFont(font);
       style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
       return style;
   }
   /**
    * 单元格样式(Integer)列表
    */
   private static CellStyle createCellContent4IntegerStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 正文样式
       style.setFillPattern(XSSFCellStyle.NO_FILL);
       style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
       // 把字体应用到当前的样式
       style.setFont(font);
//       style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));//数据格式只显示整数
       return style;
   }
   /**
    * 创建单元格表头样式
    *
    * @param workbook 工作薄
    */
   private CellStyle createCellHeadStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 表头样式
       style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
       style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
       font.setFontHeightInPoints((short) 12);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
       // 把字体应用到当前的样式
       style.setFont(font);
       return style;
   }
   /**
    * 创建单元格正文样式
    *
    * @param workbook 工作薄
    */
   private static CellStyle createCellContentStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 正文样式
       style.setFillPattern(XSSFCellStyle.NO_FILL);
       style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
       // 把字体应用到当前的样式
       style.setFont(font);
       return style;
   }
   
   /**
    * 单元格样式列表,方便调用全部
    */
   private Map<String, CellStyle> styleMap(Workbook workbook) {
       Map<String, CellStyle> styleMap = new LinkedHashMap<>();
       styleMap.put("head", createCellHeadStyle(workbook));
       styleMap.put("content", createCellContentStyle(workbook));
       styleMap.put("integer", createCellContent4IntegerStyle(workbook));
       styleMap.put("double", createCellContent4DoubleStyle(workbook));
       return styleMap;
   }
   /**
    *	日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss
    */
   private String getCnDate(Date date) {
       String format = "yyyy-MM-dd HH:mm:ss";
       SimpleDateFormat sdf = new SimpleDateFormat(format);
       return sdf.format(date);
   }
}

3.调用

//文件名
String statisticsTypeName = "文件名";
//拼接地址文件名并放入
PoiExcelExport pee = new PoiExcelExport(response, statisticsTypeName+System.currentTimeMillis(), statisticsTypeName);

//titleColumn数组里面放的是你要导出的数据集合里面的列名
String titleColumn[] = {"FExchangeNo","FProductNo","FContractNo","FFilledNumCount","FFeeCount"};
//titleName数组是excel中你要显示的列头的名字
String titleName[] = {"交易所代码","商品代码","合约代码","成交数量","手续费"};
//titleSize数组是excel中列的宽度
int titleSize[] = {13,13,13,13,13};
pee.wirteExcel(titleColumn, titleName, titleSize, 这里是你要放导出的集合数据);