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

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

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("其他");`

合并后效果大致如图
再贴上完整的设置样式代码

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);