java实现excel的demo

来源：互联网发布：python 生成高斯分布编辑：程序博客网时间：2024/05/24 06:13

创建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();    }

阅读全文

0 0