List集合导出成Excel表格

你的名字 2022-06-07 03:22 168阅读 0赞

**需要的jar包:poi-3.14.jar**

**List集合导出成Excel表格**  
*实体类*

package job.util;
    
    /** * * @className Employee * @deprecated 导出的实体 * @author pengfei.xiong * @date 2017年10月9日 */
    public class Employee { 
    
        private String name;
    
          private String clazz;
    
          private Integer year;
    
          private Integer month;
    
          private Integer day;
    
          private double salary;
    
          public Employee() {
          };
    
          public Employee(String name, String clazz, Integer year, Integer month, Integer day,
              double salary) {
            super();
            this.name = name;
            this.clazz = clazz;
            this.year = year;
            this.month = month;
            this.day = day;
            this.salary = salary;
          }
    
          /** * @return name */
          public String getName() {
            return name;
          }
    
          /** * @return year */
          public Integer getYear() {
            return year;
          }
    
          /** * @return month */
          public Integer getMonth() {
            return month;
          }
    
          /** * @return day */
          public Integer getDay() {
            return day;
          }
    
          /** * @return salary */
          public double getSalary() {
            return salary;
          }
    
          /** * @param name * set name */
          public void setName(String name) {
            this.name = name;
          }
    
          /** * @param year * set year */
          public void setYear(Integer year) {
            this.year = year;
          }
    
          /** * @param month * set month */
          public void setMonth(Integer month) {
            this.month = month;
          }
    
          /** * @param day * set day */
          public void setDay(Integer day) {
            this.day = day;
          }
    
          /** * @param salary * set salary */
          public void setSalary(double salary) {
            this.salary = salary;
          }
    
          /** * @return clazz */
          public String getClazz() {
            return clazz;
          }
    
          /** * @param clazz * set clazz */
          public void setClazz(String clazz) {
            this.clazz = clazz;
          }
    
          @Override
          public String toString() {
            return "Employee [name=" + name + ", clazz=" + clazz + ", year=" + year + ", month=" + month
                + ", day=" + day + ", salary=" + salary + "]";
          }
    
    }

*工具类*

package job.util;
    
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import java.lang.reflect.Method;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    import java.util.UUID;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    /** * * @className ExportExcel * @deprecated List集合导出成Excel表格工具类 * @author pengfei.xiong * @date 2017年10月9日 */
    public final class ExportExcel { 
        /*** * 构造方法 */
          private ExportExcel() {
    
          }
    
          /*** * 工作簿 */
          private static HSSFWorkbook workbook;
    
          /*** * sheet */
          private static HSSFSheet sheet;
         /*** * 标题行开始位置 */
          private static final int TITLE_START_POSITION = 0;
    
          /*** * 时间行开始位置 */
          private static final int DATEHEAD_START_POSITION = 1;
    
          /*** * 表头行开始位置 */
          private static final int HEAD_START_POSITION = 2;
    
          /*** * 文本行开始位置 */
          private static final int CONTENT_START_POSITION = 3;
    
    
          /** * * @param dataList * 对象集合 * @param titleMap * 表头信息（对象属性名称->要显示的标题值)[按顺序添加] * @param sheetName * sheet名称和表头值 */
          public static void excelExport(List<?> dataList, Map<String, String> titleMap, String sheetName) {
            // 初始化workbook
            initHSSFWorkbook(sheetName);
            // 标题行
            createTitleRow(titleMap, sheetName);
            // 时间行
            createDateHeadRow(titleMap);
            // 表头行
            createHeadRow(titleMap);
            // 文本行
            createContentRow(dataList, titleMap);
            //设置自动伸缩
            //autoSizeColumn(titleMap.size());
            // 写入处理结果
            try {
              //生成UUID文件名称
              //UUID是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。
              UUID uuid = UUID.randomUUID();
              String filedisplay = uuid+".xls";
              //如果web项目，1、设置下载框的弹出（设置response相关参数)；2、通过httpservletresponse.getOutputStream()获取
              OutputStream out = new FileOutputStream("D:\\" + filedisplay);
              workbook.write(out);
              out.close();
            }
            catch (Exception e) {
              e.printStackTrace();
            }
          }
    
          /*** * * @param sheetName * sheetName */
          private static void initHSSFWorkbook(String sheetName) {
            workbook = new HSSFWorkbook();
            sheet = workbook.createSheet(sheetName);
          }
    
          /** * 生成标题（第零行创建） * @param titleMap 对象属性名称->表头显示名称 * @param sheetName sheet名称 */
          private static void createTitleRow(Map<String, String> titleMap, String sheetName) {
            CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, titleMap.size() - 1);
            sheet.addMergedRegion(titleRange);
            HSSFRow titleRow = sheet.createRow(TITLE_START_POSITION);
            HSSFCell titleCell = titleRow.createCell(0);
            titleCell.setCellValue(sheetName);
          }
    
          /** * 创建时间行（第一行创建） * @param titleMap 对象属性名称->表头显示名称 */
          private static void createDateHeadRow(Map<String, String> titleMap) {
            CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, titleMap.size() - 1);
            sheet.addMergedRegion(dateRange);
            HSSFRow dateRow = sheet.createRow(DATEHEAD_START_POSITION);
            HSSFCell dateCell = dateRow.createCell(0);   
            dateCell.setCellValue(new SimpleDateFormat("yyyy年MM月dd日").format(new Date()));
          }
    
          /** * 创建表头行（第二行创建） * @param titleMap 对象属性名称->表头显示名称 */
          private static void createHeadRow(Map<String, String> titleMap) {
            // 第1行创建
            HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);
            int i = 0;
            for (String entry : titleMap.keySet()) {
              HSSFCell headCell = headRow.createCell(i);
              headCell.setCellValue(titleMap.get(entry));
              i++;
            }
          }
    
         /** * * @param dataList 对象数据集合 * @param titleMap 表头信息 */
          private static void createContentRow(List<?> dataList, Map<String, String> titleMap) {
            try {
              int i=0;
              for (Object obj : dataList) {
                HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i);
                int j = 0;
                for (String entry : titleMap.keySet()) {
                  String method = "get" + entry.substring(0, 1).toUpperCase() + entry.substring(1);
                  Method m = obj.getClass().getMethod(method, null);
                  String value =   m.invoke(obj, null).toString();
                  HSSFCell textcell = textRow.createCell(j);
                  textcell.setCellValue(value);
                  j++;
                }
                i++;
              }
    
            }
            catch (Exception e) {
              e.printStackTrace();
            }
          }
          /** * 自动伸缩列（如非必要，请勿打开此方法，耗内存） * @param size 列数 */
          private static void autoSizeColumn(Integer size) { 
            for (int j = 0; j < size; j++) {
              sheet.autoSizeColumn(j);
            }
          }
    }

**测试类**

package job.util;
    
    import java.util.ArrayList;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    public class Test { 
    
        public static void main(String[] args) {
            /**模拟数据开始*/
            List<Employee> staffs = new ArrayList<Employee>();
            staffs.add(new Employee("test01", "一组", 2017, 9, 20, 20000));
            staffs.add(new Employee("test02", "一组", 2017, 9, 20, 20000));
            staffs.add(new Employee("test03", "一组", 2017, 9, 20, 20000));
            staffs.add(new Employee("test04", "一组", 2017, 9, 20, 20000));
            staffs.add(new Employee("test05", "一组", 2017, 9, 20, 20000));    
    
            Map<String,String> titleMap = new LinkedHashMap<String,String>();
            titleMap.put("name", "姓名");
            titleMap.put("clazz", "组号");
            titleMap.put("year", "年份");
            titleMap.put("month", "月份");
            titleMap.put("day", "天");
            titleMap.put("salary", "薪资");
            String sheetName = "信息导出";
            /**模拟数据结束*/
    
            System.out.println("start导出");
            long start = System.currentTimeMillis();
            ExportExcel.excelExport(staffs, titleMap, sheetName);
            long end = System.currentTimeMillis();
            System.out.println("end导出");
            System.out.println("耗时："+(end-start)+"ms");
          }
        }