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

最新推荐文章于 2024-07-10 16:39:04 发布

Reborn_Chang

最新推荐文章于 2024-07-10 16:39:04 发布

阅读量1.2k

点赞数

分类专栏： Java 文章标签： java excel

本文链接：https://blog.csdn.net/m0_37190495/article/details/77584612

版权

Java 专栏收录该内容

49 篇文章 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("其他");`

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

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

Reborn_Chang

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
java 使用ＰＯＩ框架进行Excel表格的导出

首先附上代码,是对一个复杂集合进行遍历导出的,集合的类型是Map<String,List<Attendance>> testMap = new LinkedHashMap<String,List<Attendance>>();其中attendance是一个实体类,Map的key是拼接字符串,下面直接上代码,套用这个模板可以解决大多数Excel导出问题,且此代码的列数也是动态生成的,话不多说,代码如下
复制链接

扫一扫

专栏目录