java excel导出工具类

女爷i 2022-07-15 14:19 286阅读 0赞

使用的是apache poi与注解类实现：

maven:

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

1.注解类，将JAVA对象与excel标题对应

import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /**
     * 
     * @Description: excel导出注解类
     * @author kang
     * @date 2016年8月24日
     */
    @Target({ ElementType.METHOD, ElementType.FIELD, ElementType.TYPE })
    @Retention(RetentionPolicy.RUNTIME)
    public @interface ExcelField 
    {
    	//导出字段在excel中的名字
    	String title();
    }

2.工具类：

import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.util.ArrayList;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.List;
    
    
    import javax.servlet.http.HttpServletResponse;
    
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    /**
     * 
     * @Description: excel导出封装类
     * @author kang
     * @date 2016年8月24日
     */
    public class ExcelUtil {
    
    
        // 生成excel，list导出的数据，list里的实体class，sumData合计数据
    public static <Q> XSSFWorkbook createExcel(List<Q> list, Class<Q> cls, Q sumData) throws IOException, IllegalArgumentException, IllegalAccessException {
     XSSFWorkbook wb = new XSSFWorkbook();  Field[] fields = cls.getDeclaredFields();
            ArrayList<String> headList = new ArrayList<String>();
    
    
            // 添加合计数据
            if (sumData != null) {
                list.add(sumData);
            }
    
    
            for (Field f : fields) {
                ExcelField field = f.getAnnotation(ExcelField.class);
                if (field != null) {
                    headList.add(field.title());
                }
            }
    
    
            XSSFCellStyle style = getCellStyle(wb);
            XSSFSheet sheet = wb.createSheet();
            // 设置Excel表的第一行即表头
            XSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headList.size(); i++) {
                XSSFCell headCell = row.createCell(i);
                headCell.setCellType(Cell.CELL_TYPE_STRING);
                headCell.setCellStyle(style);// 设置表头样式
                headCell.setCellValue(String.valueOf(headList.get(i)));
                // sheet.autoSizeColumn((short) i);// 设置单元格自适应
                sheet.setColumnWidth(0, 15 * 256);
            }
    
    
            for (int i = 0; i < list.size(); i++) {
                XSSFRow rowdata = sheet.createRow(i + 1);// 创建数据行
                Q q = list.get(i);
                Field[] ff = q.getClass().getDeclaredFields();
                int j = 0;
                for (Field f : ff) {
                    ExcelField field = f.getAnnotation(ExcelField.class);
                    if (field == null) {
                        continue;
                    }
                    f.setAccessible(true);
                    Object obj = f.get(q);
                    XSSFCell cell = rowdata.createCell(j);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    // 当数字时
                    if (obj instanceof Integer) {
                        cell.setCellValue((Integer)obj);
                        // 将序号替换为123456
                        if (j == 0)
                            cell.setCellValue(i + 1);
                    }
                    // 当为字符串时
                    else if (obj instanceof String)
                        cell.setCellValue((String)obj);
                    // 当为布尔时
                    else if (obj instanceof Boolean)
                        cell.setCellValue((Boolean)obj);
                    // 当为时间时
                    else if (obj instanceof Date)
                        cell.setCellValue((String)obj);
                    // 当为时间时
                    else if (obj instanceof Calendar)
                        cell.setCellValue((Calendar)obj);
                    // 当为小数时
                    else if (obj instanceof Double)
                        cell.setCellValue((Double)obj);
                    j++;
                }
            }
    
    
            if (sumData != null) {
                int rowIndex = list.size();
                XSSFRow sumRow = sheet.getRow(rowIndex);
                XSSFCell sumCell = sumRow.getCell(0);
                sumCell.setCellStyle(style);
                sumCell.setCellValue("合计");
            }
            return wb;
        }
    
    
        // 导出
        public static void writeExcel(HttpServletResponse response, String fileName, XSSFWorkbook wb) throws IOException {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);
            OutputStream ouputStream = null;
            try {
                ouputStream = response.getOutputStream();
                wb.write(ouputStream);
            } finally {
                ouputStream.close();
            }
        }
    
    
        // 表头样式
        public static XSSFCellStyle getCellStyle(XSSFWorkbook wb) {
            XSSFCellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short)12);// 设置字体大小
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
            style.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);// 让单元格居中
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
            style.setWrapText(true);// 设置自动换行
            style.setFont(font);
            return style;
        }
    
    
    }

3.使用：比如查询结果List<User>，则在User对象上加上第一步的注解

public class User{
    	@ExcelField(title = "序号")
    	private String id;
    	
    	@ExcelField(title = "部门")
    	private String deptName;
    controller:
    
    	/**
    	 * @Description: 查询模块1表格数据
    	 * @author kang
    	 * @throws Exception
    	 * @date 2016年8月25日
    	 */
    	@RequestMapping(value = "exportExcel", method = RequestMethod.POST)
    	public void exportExcel(Parameters parameters, HttpServletResponse response) throws Exception
    	{
    	List<User> list = userService.selectByParameters(parameters);
            XSSFWorkbook wb = ExcelUtil.createExcel(list, User.class, null);
            String filename = getExcelName("表格");
            ExcelUtil.writeExcel(response, filename, wb);
        }
      // 转化为excel名称
        private String getExcelName(String filename) throws UnsupportedEncodingException {
            String excelName = StringUtils.join(filename, ".xlsx");
            return URLEncoder.encode(excelName, "UTF-8");
        }

前端：

1.如果是jsp ，有一个隐藏form，调用form的submit方法，下载完成后浏览器下方才会显示已下载的文件

2.或者如果用js

var f = document.createElement("form");
      document.body.appendChild(f);
      var input = document.createElement("input");
      input.type = "hidden";
      f.appendChild(input);
      input.name = "param";
      input.value = "xxx";
      f.method="POST";
      f.action = "xxx/exportExcel";
      f.submit();
      f.remove();