java 使用ＰＯＩ框架进行Excel表格的导出

不念不忘少年蓝@ 2022-06-09 03:18 221阅读 0赞

首先附上代码,是对一个复杂集合进行遍历导出的,集合的类型是

Map<String,List<Attendance>> testMap = new LinkedHashMap<String,List<Attendance>>();

其中attendance是一个实体类,Map的key是拼接字符串,下面直接上代码,套用这个模板可以解决大多数Excel导出问题,且此代码的列数也是动态生成的,话不多说,代码如下

`package cn.test;
    import java.io.FileOutputStream;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Map.Entry;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    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.hssf.util.HSSFColor;
    
    public class Test1 {
    
        public static void main(String[] args) throws Exception {
            //创建测试集
            Map<String,List<Attendance>> testMap = new LinkedHashMap<String,List<Attendance>>();
            ArrayList<Attendance> list1= new ArrayList<Attendance>();
            ArrayList<Attendance> list2= new ArrayList<Attendance>();
            Person p1 =new Person(1,"大一","研发部"); 
            Person p2 =new Person(2,"老二","门面部"); 
            Person p3 =new Person(3,"老三","产品部"); 
            Person p4 =new Person(4,"老四","事务部"); 
            Person p5 =new Person(5,"老五","海外部"); 
            Attendance kq1=new Attendance(1,timeFormat(),DayBefore(timeFormat()),"normal");
            Attendance kq2=new Attendance(2,timeFormat(),DayAfter(timeFormat()),"normal"); 
            Attendance kq3=new Attendance(3,DayBefore(timeFormat()),DayAfter(timeFormat()),"normal"); 
            list1.add(kq1);
            list1.add(kq2);
            list1.add(kq3); 
            list2.add(kq2);
            list2.add(kq3); 
            testMap.put(p1.toString(),list1);
            testMap.put(p2.toString(),list2);
            testMap.put(p3.toString(),list2);
            testMap.put(p4.toString(),list1);
            testMap.put(p5.toString(),list1);
            FileOutputStream output = null;
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
              // 生成一个表格
              try{
                  //页码
                  HSSFSheet sheet = workbook.createSheet("测试统计");
                  // 设置表格默认列宽度为18个字节
                  sheet.setDefaultColumnWidth((short) 18);
                  // 生成一个样式
                  HSSFCellStyle style = workbook.createCellStyle();
                  style.setWrapText(true); 
                  // 设置这些样式
                  style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
                  // 生成一个字体
                  HSSFFont font = workbook.createFont();
                  font.setColor(HSSFColor.VIOLET.index);
                  font.setFontHeightInPoints((short) 12);
                  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                  // 把字体应用到当前的样式
                  style.setFont(font); 
    
                  HSSFRow row =sheet.createRow(0);
                  //创建person类标题
                  (row.createCell(0)).setCellValue("姓名");
                  (row.createCell(1)).setCellValue("部门");
                  //取得list集合的最大值，建立attendance类标题
                  int listMaxSize=Math.max(list1.size(),list2.size());
                  for(int i=1;i<=listMaxSize;i++){ 
                      HSSFCell cell = row.createCell(i+1);
                      cell.setCellValue(String.valueOf(i));
                  }          
                    int index=1;
                    Iterator<Entry<String, List<Attendance>>> entries = testMap.entrySet().iterator(); 
                    while (entries.hasNext()) {     
                        row = sheet.createRow(index);
                        Map.Entry<String, List<Attendance>> entry = entries.next();
                       //Person数据
                        String key[] = entry.getKey().split("-");
                      //由于key是id-name-department的拼接，所以要切分，并且id不用于显示
                        (row.createCell(0)).setCellValue(key[1]);
                        (row.createCell(1)).setCellValue(key[2]);
                        //Attendance数据
                        List<Attendance> alist = entry.getValue();
    
                        //填充Attendance类中除AID以外的数据
                        for(int j=0;j<alist.size();j++){ 
                            //因为person的信息已经占了两个cell，所以这里的需要j+2
                             HSSFCell cell = row.createCell(j+2);
                             cell.setCellStyle(style); 
                             cell.setCellValue("上午:"+alist.get(j).getStartTime() +"\n备注："+alist.get(j).getNote()
                                +"\n下午："+alist.get(j).getEndTime()+"\n备注："+alist.get(j).getNote()); 
                        }                 
                        index++; 
                    }   
                    // 写入数据并关闭文件
                    output=new FileOutputStream("D:\\result.xls"); 
                    workbook.write(output); 
                    output.flush(); 
            } catch (Exception e) {
                System.out.println(e);
            } finally {
                if (output != null|| workbook!=null) {
                    try {
                        output.close();
                        workbook.close();
                        System.out.println("#######导出成功########");
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
    
        }
    
    
        /* 获得现在日期 */ 
        private static String timeFormat(){
            return new SimpleDateFormat("HH:mm:ss").format(new Date());
        }
        /* 获得指定日期的前一天 */  
        public static String DayBefore(String specifiedDay) {  
            Calendar c = Calendar.getInstance(); 
            Date date = null; 
            try {  
                date = new SimpleDateFormat("HH:mm:ss").parse(specifiedDay); 
            } catch (ParseException e) {  
                e.printStackTrace(); 
            }  
            c.setTime(date); 
            int day = c.get(Calendar.DATE); 
            c.set(Calendar.DATE, day - 1); 
            String dayAfter = new SimpleDateFormat("HH:mm:ss").format(c.getTime()); 
            return dayAfter; 
        }   
    
        /* 获得指定日期的后一天 */  
        public static String DayAfter(String specifiedDay) {  
            Calendar c = Calendar.getInstance(); 
            Date date = null; 
            try {  
                date = new SimpleDateFormat("HH:mm:ss").parse(specifiedDay); 
            } catch (ParseException e) {  
                e.printStackTrace(); 
            }  
            c.setTime(date); 
            int day = c.get(Calendar.DATE); 
            c.set(Calendar.DATE, day + 1); 
            String dayAfter = new SimpleDateFormat("HH:mm:ss").format(c.getTime()); 
            return dayAfter; 
        }   
    
    }

`后来又做一个导出涉及到表头的行合并和列合并,下面再附上行列合并的代码,值得注意的是,在进行行列合并前,先注意表头分为几个层级,然后再进行行合并和列合并代码如下` //创建单元格,设置行合并列合并

HSSFSheet sheet = workbook.createSheet("个人薪酬");//创建单元名
                sheet.addMergedRegion(new CellRangeAddress(0,0,4,9));//横向合并5-9
                sheet.addMergedRegion(new CellRangeAddress(0,0,11,19));
                sheet.addMergedRegion(new CellRangeAddress(0,0,20,22));
                sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));//纵向：合并第一列的第1行和第2行第
                sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));//纵向：合并第二列的第1行和第2行第
                sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));
                sheet.addMergedRegion(new CellRangeAddress(0,1,3,3));
                sheet.addMergedRegion(new CellRangeAddress(0,1,10,10));
                sheet.addMergedRegion(new CellRangeAddress(0,1,23,23));
                sheet.addMergedRegion(new CellRangeAddress(0,1,24,24));
                sheet.addMergedRegion(new CellRangeAddress(0,1,25,25));
                sheet.addMergedRegion(new CellRangeAddress(0,1,26,26));
                sheet.addMergedRegion(new CellRangeAddress(0,1,27,27));
                //首行
                HSSFRow row = sheet.createRow(0);
    
                row.createCell(4).setCellStyle(style);
                row.createCell(4).setCellValue("代扣项目");
                row.createCell(11).setCellStyle(style);
                row.createCell(11).setCellValue("福利");
                row.createCell(20).setCellStyle(style);
                row.createCell(20).setCellValue("其他");`

合并后效果大致如图![这里写图片描述][SouthEast]  
再贴上完整的设置样式代码

HSSFWorkbook workbook = new HSSFWorkbook();//创建一个工作表格
                //设置样式
                HSSFCellStyle style = workbook.createCellStyle();
                style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                //style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                //style.setWrapText(true);
                //style.setFillForegroundColor((short) 5);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
                //设置字体
                HSSFFont font = workbook.createFont();//创建字体格式
                //font.setColor(HSSFColor.VIOLET.index);
                //font.setFontHeightInPoints((short) 6);//设置字体大小
                font.setFontHeight((short) 10);//大小
                font.setFontName("仿宋_GB2312");//字体
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
    
                style.setFont(font);
                //创建单元格,设置行合并列合并
                HSSFSheet sheet = workbook.createSheet("个人薪酬");//创建单元名
                sheet.addMergedRegion(new CellRangeAddress(0,0,4,9));//横向合并5-9
                sheet.addMergedRegion(new CellRangeAddress(0,0,11,19));
                sheet.addMergedRegion(new CellRangeAddress(0,0,20,22));
                sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));//纵向：合并第一列的第1行和第2行第
                sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));//纵向：合并第二列的第1行和第2行第
                sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));
                sheet.addMergedRegion(new CellRangeAddress(0,1,3,3));
                sheet.addMergedRegion(new CellRangeAddress(0,1,10,10));
                sheet.addMergedRegion(new CellRangeAddress(0,1,23,23));
                sheet.addMergedRegion(new CellRangeAddress(0,1,24,24));
                sheet.addMergedRegion(new CellRangeAddress(0,1,25,25));
                sheet.addMergedRegion(new CellRangeAddress(0,1,26,26));
                sheet.addMergedRegion(new CellRangeAddress(0,1,27,27));
                //首行
                HSSFRow row = sheet.createRow(0);
    
                row.createCell(4).setCellStyle(style);
                row.createCell(4).setCellValue("代扣项目");
                row.createCell(11).setCellStyle(style);
                row.createCell(11).setCellValue("福利");
                row.createCell(20).setCellStyle(style);
                row.createCell(20).setCellValue("其他");
                //第二行
                HSSFRow row1 = sheet.createRow(1);
                row1.setHeight((short) 800);

[SouthEast]: /images/20220609/13c331db91f849dcb0cc8df30b6f3bcb.png