java poi导出execl文件

1.导入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

2.导出工具类

package com.zhgd.zhgd.api.common;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.usermodel.*;


/**
 * 
 * excel工具类
 *
 */
@SuppressWarnings({ "deprecation" })
public class ExcelUtil2<T> {

	// excel的标题
	private String title;
	// excel的列名
	private String[] rowNames;
	// excel的数据
	private List<Object[]> dataList = new ArrayList<Object[]>();
	
	private HttpServletResponse response;
	
	/**
	 * 构造方法(一)
	 * @param title excel的标题
	 * @param rowNames excel的列名
	 * @param response
	 */
	public ExcelUtil2(String title,String[] rowNames,List<Object[]> dataList,HttpServletResponse response){
    	
		this.title = title;
		this.rowNames = rowNames;
		this.response = response;
		this.dataList = dataList;

    }
    public void export(){
    	 try {
    		// 声明一个工作薄
			 XSSFWorkbook  workBook = new XSSFWorkbook();
    		// 生成一个表格
			 XSSFSheet sheet = workBook.createSheet();
			 //生成一个工作表
             workBook.setSheetName(0,"Sheet1");
             //数据行单元格样式
             XSSFCellStyle style = this.getStyle(workBook);
             //表头单元格样式
             XSSFCellStyle columnTopStyle = this.getColumnTopStyle(workBook);
             //创建标题行 第一行
             XSSFRow titleTop = sheet.createRow(0);
             //标题样式
             XSSFCellStyle title = this.getTitle(workBook);
             //合并单元格
             CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, rowNames.length - 1);
             sheet.addMergedRegion(titleRegion);
             //生成单元格
             XSSFCell cell1 = titleTop.createCell(0, CellType.STRING);
             //给单元格附上值即标题
             cell1.setCellValue(this.title);
             //给单元格添加样式
             cell1.setCellStyle(title);

             // 创建表头行 第二行
             XSSFRow titleRow = sheet.createRow(1);
             //给表头设置行高
             titleRow.setHeight((short) 500);

		    for(int i=0;i<rowNames.length;i++){
		        //设置宽度
                sheet.setColumnWidth(i, 4500);
                XSSFCell cell = titleRow.createCell(i, CellType.STRING);
                cell.setCellStyle(columnTopStyle);
                cell.setCellValue(rowNames[i]);
		    }
		    //插入需导出的数据
		    for(int i=0;i<dataList.size();i++){
		    	Object[] obj = dataList.get(i);//遍历每个对象  
				XSSFRow row = sheet.createRow(i+2);//创建所需的行数
                row.setHeight((short) 500);
		        for(int j=0; j<obj.length; j++){
					XSSFCell cell = row.createCell(j, CellType.STRING);
                    //设置单元格的数据类型
                    cell.setCellStyle(style);
		        	if(!"".equals(obj[j]) && obj[j] != null){
		        		if(obj[j].toString().split("-").length > 0){
		        			cell.setCellValue(obj[j].toString().replace("-", "")); //设置单元格的值
		        		}else{
		        			cell.setCellValue(obj[j].toString()); //设置单元格的值
		        		}
                    }else{
                   	 cell.setCellValue(""); 
                    }
		        }
		    }
		    
		    if(workBook !=null){
	             try {
	            	 if(this.title !=null){
	            		response.setContentType("application/octet-stream;charset=utf-8");
	             	    response.setHeader("Content-Disposition", "attachment;filename=" + new String(this.title.getBytes(),"iso-8859-1") + ".xlsx");
	            	 }else{
	            		 String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xlsx";  
	                     String headStr = "attachment; filename=\"" + fileName + "\""; 
	                     response.setContentType("APPLICATION/OCTET-STREAM");
	                     response.setHeader("Content-Disposition", headStr);
	            	 }
	            	  OutputStream out = response.getOutputStream();  
	            	  workBook.write(out); 
	             }
	             catch (IOException e)  {  
	                 e.printStackTrace();  
	             }
	         }
    		 
    		 
    		 
    		 
    	 }catch(Exception e){
    		 e.printStackTrace();  
    	 }
    }

    /**
     * 标题样式
     * @param workbook
     * @return
     */
    public XSSFCellStyle getTitle(XSSFWorkbook workbook) {

        //设置字体
        XSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)20);
        //字体加粗
        font.setBold(true);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        XSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);


        return style;

    }
    
    /**
     * 列头单元格样式     
     * @param workbook
     * @return
     */
    public XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) {

          //设置字体
		XSSFFont font = workbook.createFont();
          //设置字体大小
          font.setFontHeightInPoints((short)12);
          //字体加粗
		 font.setBold(true);
          //设置字体名字
          font.setFontName("Courier New");
          //设置样式;
		XSSFCellStyle style = workbook.createCellStyle();
          //设置底边框;
          style.setBorderBottom(BorderStyle.THIN);
          //设置底边框颜色;
          style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //设置左边框;
          style.setBorderLeft(BorderStyle.THIN);
          //设置左边框颜色;
          style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //设置右边框;
          style.setBorderRight(BorderStyle.THIN);
          //设置右边框颜色;
          style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //设置顶边框;
          style.setBorderTop(BorderStyle.THIN);
          //设置顶边框颜色;
          style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //在样式用应用设置的字体;
          style.setFont(font);
          //设置自动换行;
          style.setWrapText(false);
          //设置水平对齐的样式为居中对齐;
          style.setAlignment(HorizontalAlignment.CENTER);
          //设置垂直对齐的样式为居中对齐;
          style.setVerticalAlignment(VerticalAlignment.CENTER);


          return style;

    }
      
    /**
     * 列数据信息单元格样式   
     * @param workbook
     * @return
     */
    public XSSFCellStyle getStyle(XSSFWorkbook workbook) {
          //设置字体
		XSSFFont font = workbook.createFont();
          font.setFontName("Courier New");
        font.setFontHeightInPoints((short)12);
          //设置样式;
		XSSFCellStyle style = workbook.createCellStyle();
          //设置底边框;
          style.setBorderBottom(BorderStyle.THIN);
          //设置底边框颜色;
          style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //设置左边框;
          style.setBorderLeft(BorderStyle.THIN);
          //设置左边框颜色;
          style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //设置右边框;
          style.setBorderRight(BorderStyle.THIN);
          //设置右边框颜色;
          style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //设置顶边框;
          style.setBorderTop(BorderStyle.THIN);
          //设置顶边框颜色;
          style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
          //在样式用应用设置的字体;
          style.setFont(font);
          //设置自动换行;
          style.setWrapText(false);
          //设置水平对齐的样式为居中对齐;
          style.setAlignment(HorizontalAlignment.CENTER);
          //设置垂直对齐的样式为居中对齐;
          style.setVerticalAlignment(VerticalAlignment.CENTER);

          return style;

    }

}

3.导出调用方法

       //数据
       List<Object[]> objects = new ArrayList<>();
      //标题
       String title =departName+"项目"+nowDate[0]+"年"+nowDate[1]+"月人员工资表";
       //表头
       String[] rowNames = new String[]{"人员姓名","人员编号","身份证号","工人类型","工种","系统出勤天数","实际出勤天数","工资总额（元）","借款（元）","应发工资（元）","实发工资（元）","发放日期","备注"};
       ExcelUtil2 excelUtil2 = new ExcelUtil2(title,rowNames,objects,response);
       excelUtil2.export();