java实现excel的demo

最新推荐文章于 2022-12-30 15:41:32 发布

发疯的man

最新推荐文章于 2022-12-30 15:41:32 发布

阅读量1.1k

点赞数

分类专栏： java项目文章标签： excel exception

本文链接：https://blog.csdn.net/lzh657083979/article/details/73252657

版权

java项目专栏收录该内容

14 篇文章 0 订阅

订阅专栏

创建2003excel表格

@Test
    public void writeExcel2003() throws Exception {
        //1.创建工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建工作表
        HSSFSheet sheet = workbook.createSheet("hello world");
        //3.创建行
        HSSFRow row = sheet.createRow(2);
        //4.创建单元格
        HSSFCell cell = row.createCell(2);
        //5.向单元格写入数据
        cell.setCellValue("hello world!");

        //6.写入硬盘文件
        FileOutputStream fos = new FileOutputStream("d:\\yun\\hello2003.xls");
        workbook.write(fos);
        fos.close();


    }

读取2003excel表格

@Test
    public void readExcel2003() throws Exception {

        FileInputStream fis = new FileInputStream("d:\\yun\\hello2003.xls");
        //1.创建工作薄
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        //2.获取工作表
        HSSFSheet sheet = workbook.getSheet("hello world");
        //3.获取行
        HSSFRow row = sheet.getRow(2);
        //4.获取单元格
        HSSFCell cell = row.getCell(2);
        //5.向单元格写入数据
        String cellValue = cell.getStringCellValue();

        System.out.println("单元格的值是："+cellValue);

        fis.close();
    }

创建2007表格

@Test
    public void writeExcel2007() throws Exception {
        //1.创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2.创建工作表
        XSSFSheet sheet = workbook.createSheet("hello world");
        //3.创建行
        XSSFRow row = sheet.createRow(2);
        //4.创建单元格
        XSSFCell cell = row.createCell(2);
        //5.向单元格写入数据
        cell.setCellValue("hello world!");

        //6.写入硬盘文件
        FileOutputStream fos = new FileOutputStream("d:\\yun\\hello2007.xlsx");
        workbook.write(fos);
        fos.close();
    }

读取2003excel表格

@Test
    public void readExcel2003And2007() throws Exception {
        String fileName = "d:\\yun\\hello2007.xlsx";
        FileInputStream fis = new FileInputStream(fileName);
        boolean b = fileName.matches("^.+(.xlsx)+$");

        //1.创建工作薄
        Workbook workbook = null;
        workbook = (b) ? new XSSFWorkbook(fis) : new HSSFWorkbook(fis);
        //2.获取工作表
        Sheet sheet = workbook.getSheet("hello world");
        //3.获取行
        Row row = sheet.getRow(2);
        //4.获取单元格
        Cell cell = row.getCell(2);
        //5.读取单元格数据
        String cellValue = cell.getStringCellValue();

        System.out.println("单元格的值是："+cellValue);

        fis.close();
        workbook.close();
    }

设置单元格样式

public CellStyle createStyle(Workbook workbook,int fontsize){
        //1.2设置单元格样式
        CellStyle style = workbook.createCellStyle();
        // 设置水平居中
        style.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置垂直居中
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 1.3设置字体
        Font font = workbook.createFont();
        // 设置字体为ARIAL
        font.setFontName(HSSFFont.FONT_ARIAL);
        // 设置粗体
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 设置字体颜色
        //font.setColor(HSSFColor.BLUE.index);
        // 设置字体大小
        font.setFontHeightInPoints((short) fontsize);
        // 将字体加入样式
        style.setFont(font);
        return style;
    }

设置带有样式的单元格

@Test
    public void writeExcel2003Style() throws Exception {
        //1.创建工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();

        //1.1创建合并单元格
        CellRangeAddress cra = new CellRangeAddress(0,0,0,6);
        //1.2设置单元格样式
        HSSFCellStyle style = workbook.createCellStyle();
        //设置水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //1.3设置字体
        HSSFFont font = workbook.createFont();
        //设置字体为ARIAL
        font.setFontName(HSSFFont.FONT_ARIAL);
        //设置粗体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体颜色
        font.setColor(HSSFColor.BLUE.index);
        //设置字体大小
        font.setFontHeightInPoints((short)16);
        //将字体加入样式
        style.setFont(font);
        //设置填充模式为前景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //设置前景色为绿色
        style.setFillForegroundColor(HSSFColor.GREEN.index);
        //设置背景色为红色(如果使用前景色填充模式，不需要设置背景色)
        //style.setFillBackgroundColor(HSSFColor.RED.index);
        //2.创建工作表
        HSSFSheet sheet = workbook.createSheet("hello world");
        //2.1将合并单元格作用于sheet
        sheet.addMergedRegion(cra);
        //3.创建行
        HSSFRow row = sheet.createRow(0);
        //4.创建单元格
        HSSFCell cell = row.createCell(0);
        //5.向单元格写入数据
        cell.setCellValue("用户列表");
        //6.将单元格加入样式
        cell.setCellStyle(style);


        //6.写入硬盘文件
        FileOutputStream fos = new FileOutputStream("d:\\yun\\hello2003.xls");
        workbook.write(fos);
        fos.close();


    }

生成list对象的excel的demo

@Test
    public void writeExcel2003Users() throws Exception {
        //1.创建工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();

        //1.1创建合并单元格
        CellRangeAddress cra = new CellRangeAddress(0,0,0,6);

        //1.2创建标题样式
        CellStyle style1 = createStyle(workbook,16);
        //1.3创建列标题样式
        CellStyle style2 = createStyle(workbook,12);
        //2.创建工作表
        Sheet sheet = workbook.createSheet("hello world");
        //设置默认列宽
        sheet.setDefaultColumnWidth(15);
        //2.1将合并单元格作用于sheet
        sheet.addMergedRegion(cra);
        //3.创建行
        Row row = sheet.createRow(0);
        //4.创建单元格
        Cell cell = row.createCell(0);
        //5.向单元格写入数据
        cell.setCellValue("用户列表");
        //6.将单元格加入样式
        cell.setCellStyle(style1);

        //写列标题
        String[] titles = {"用户名","帐号","所属部门","性别","手机号码","电子邮箱","生日"};
        //创建列标题行
        Row row2 = sheet.createRow(1);
        for(int i = 0;i<titles.length;i++){
            //创建单元格
            Cell cell1 = row2.createCell(i);
            //向单元格写入数据
            cell1.setCellValue(titles[i]);
            //将单元格加入样式
            cell1.setCellStyle(style2);
        }

        //写具体的数据行
        List<User> list = new ArrayList<User>();
        list.add(new User(null,"测试","test",null,"部门A",null,true,"2344@qq.com","13888888888",null,new Date(),null));
        list.add(new User(null,"测试2","test2",null,"部门B",null,true,"2344@163.com","13888889999",null,new Date(),null));

        for(int i = 0;i<list.size();i++){
            User user = list.get(i);
            Row rowdata = sheet.createRow(i+2);
            //姓名
            Cell cell0 = rowdata.createCell(0);
            cell0.setCellValue(user.getName());
            //账号
            Cell cell1 = rowdata.createCell(1);
            cell1.setCellValue(user.getAccount());
            //部门
            Cell cell2 = rowdata.createCell(2);
            cell2.setCellValue(user.getDept());
            //性别
            Cell cell3 = rowdata.createCell(3);
            cell3.setCellValue(user.isGender()?"男":"女");
            //手机
            Cell cell4 = rowdata.createCell(4);
            cell4.setCellValue(user.getMobile());
            //电邮
            Cell cell5 = rowdata.createCell(5);
            cell5.setCellValue(user.getEmail());
            //生日
            Cell cell6 = rowdata.createCell(6);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
            cell6.setCellValue(sdf.format(user.getBirthday()));

        }

        //6.写入硬盘文件
        FileOutputStream fos = new FileOutputStream("d:\\yun\\hello2003.xls");
        workbook.write(fos);
        fos.close();


    }