POI入门操作Excel单元格

最新推荐文章于 2023-03-30 16:46:51 发布

赵广陆

最新推荐文章于 2023-03-30 16:46:51 发布

阅读量1k

点赞数

分类专栏： tool

本文链接：https://blog.csdn.net/ZGL_cyy/article/details/111243383

版权

tool 专栏收录该内容

62 篇文章 11 订阅

订阅专栏

1 依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>

2 写入

//1.打开excel
XSSFWorkbook workbook = new XSSFWorkbook();
//2.创建sheet页
XSSFSheet sheet1 = workbook.createSheet("sheet1");//sheet:工作表
//3.创建行,索引从0开始
XSSFRow row = sheet1.createRow(0);//row 行
//4.创建单元格,索引从0开始
XSSFCell cell = row.createCell(0);//cell:单元格
//5.向单元格中插入数据
cell.setCellValue("你好");
//6.将数据写入文件,相当于另存为
workbook.write(new FileOutputStream("d://b.xlsx"));

在这里插入图片描述

3 读取

//1.使用excel打开指定的xlsx文件
XSSFWorkbook fw = new XSSFWorkbook("d://b.xlsx");
//2.获取第一个sheet页
XSSFSheet sheet1 = fw.getSheetAt(0);
//3.获取第0行
XSSFRow row = sheet1.getRow(0);
//4.获取第0个单元格
XSSFCell cell = row.getCell(0);
//5.获取单元格中的数据
System.out.println(cell.getStringCellValue());

4 练习

查看一下excel数据结构，使用POI实现

/*1.创建EXCEL文件*/
XSSFWorkbook workbook = new XSSFWorkbook();

/*2.创建sheet页面*/
XSSFSheet sheet1 = workbook.createSheet("sheet1");
sheet1.addMergedRegion(new CellRangeAddress(0,0,0,2));

/*3.创建学生信息头*/
XSSFRow row = sheet1.createRow(0);
XSSFCell cell = row.createCell(0);
XSSFCellStyle textAlignCenter = workbook.createCellStyle();
textAlignCenter.setAlignment(HorizontalAlignment.CENTER);//横着对其
textAlignCenter.setVerticalAlignment(VerticalAlignment.CENTER);//上下对齐
cell.setCellStyle(textAlignCenter);
cell.setCellValue("学生信息");

/*4.创建表头*/
XSSFRow titleRow = sheet1.createRow(1);
XSSFCell idCell = titleRow.createCell(0);
idCell.setCellValue("编号");
XSSFCell nameCell = titleRow.createCell(1);
nameCell.setCellValue("姓名");
XSSFCell ageCell = titleRow.createCell(2);
ageCell.setCellValue("年龄");


/*5.创建数据*/
XSSFRow dataRow1 = sheet1.createRow(2);
XSSFCell idCell1 = dataRow1.createCell(0);
idCell1.setCellValue("1");
XSSFCell nameCell1 = dataRow1.createCell(1);
nameCell1.setCellValue("牛宁宁");
XSSFCell ageCell1 = dataRow1.createCell(2);
ageCell1.setCellValue("22");

XSSFRow dataRow2 = sheet1.createRow(3);
XSSFCell idCell2 = dataRow2.createCell(0);
idCell2.setCellValue("1");
XSSFCell nameCell2 = dataRow2.createCell(1);
nameCell2.setCellValue("牛宁宁");
XSSFCell ageCell2 = dataRow2.createCell(2);
ageCell2.setCellValue("22");


workbook.write(new FileOutputStream("d://b.xlsx"));

使用循环生成

/*1.创建EXCEL文件*/
XSSFWorkbook workbook = new XSSFWorkbook();

/*2.创建sheet页面*/
XSSFSheet sheet1 = workbook.createSheet("sheet1");
sheet1.addMergedRegion(new CellRangeAddress(0,0,0,2));

/*3.创建学生信息头*/
XSSFRow row = sheet1.createRow(0);
XSSFCell cell = row.createCell(0);
XSSFCellStyle textAlignCenter = workbook.createCellStyle();
textAlignCenter.setAlignment(HorizontalAlignment.CENTER);
textAlignCenter.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(textAlignCenter);
cell.setCellValue("学生信息");

/*4.创建表头*/
XSSFRow titleRow = sheet1.createRow(1);
XSSFCell idCell = titleRow.createCell(0);
idCell.setCellValue("编号");
XSSFCell nameCell = titleRow.createCell(1);
nameCell.setCellValue("姓名");
XSSFCell ageCell = titleRow.createCell(2);
ageCell.setCellValue("年龄");




/*5.创建数据*/
ArrayList<Student> students = new ArrayList<>();
students.add(new Student(1,"牛宁宁",22));
students.add(new Student(2,"冯荣",222));
students.add(new Student(3,"张家齐",212));
students.add(new Student(4,"范亚涛",122));


for (int i = 0; i < students.size(); i++) {
    XSSFRow dataRow1 = sheet1.createRow(2+i);
    XSSFCell idCell1 = dataRow1.createCell(0);
    idCell1.setCellValue(students.get(i).getId());
    XSSFCell nameCell1 = dataRow1.createCell(1);
    nameCell1.setCellValue(students.get(i).getName());
    XSSFCell ageCell1 = dataRow1.createCell(2);
    ageCell1.setCellValue(students.get(i).getAge());
}

workbook.write(new FileOutputStream("d://b.xlsx"));

5 文件下载

整体流程
在这里插入图片描述

在这里插入图片描述

servlet需要做的事情

1.告诉浏览器返回的是EXCEL文件，并且要下载该文件，不能直接在浏览器打开

2.读取服务器上面的资源，写给客户端。

service层接口:QuestionService

 /**生成报表数据*/
ByteArrayOutputStream report()throws Exception;

service层接口的实现类

	@Override
    public ByteArrayOutputStream report() throws Exception {
        /*1.从数据库查询所有的问题列表*/
        List<Question> questionList = this.findAll();

        /*2.将查询到的数据写入内存，下面代码拷贝，别自己写*/
        Workbook wb = new XSSFWorkbook();
        Sheet s = wb.createSheet("题目数据文件");

        //设置通用配置
        //s.setColumnWidth(4,100);
        CellStyle cs_field = wb.createCellStyle();
        cs_field.setAlignment(HorizontalAlignment.CENTER);
        cs_field.setBorderTop(BorderStyle.THIN);
        cs_field.setBorderBottom(BorderStyle.THIN);
        cs_field.setBorderLeft(BorderStyle.THIN);
        cs_field.setBorderRight(BorderStyle.THIN);

        //制作标题
        s.addMergedRegion(new CellRangeAddress(1,1,1,12));

        Row row_1 = s.createRow(1);
        Cell cell_1_1 = row_1.createCell(1);
        cell_1_1.setCellValue("在线试题导出信息");
        //创建一个样式
        CellStyle cs_title = wb.createCellStyle();
        cs_title.setAlignment(HorizontalAlignment.CENTER);
        cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
        cell_1_1.setCellStyle(cs_title);

        //制作表头
        String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述",
                "题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"};
        Row row_2 = s.createRow(2);

        for (int i = 0; i < fields.length; i++) {
            Cell cell_2_temp = row_2.createCell(1 + i); //++
            cell_2_temp.setCellValue(fields[i]);    //++
            cell_2_temp.setCellStyle(cs_field);
        }

        //制作数据区
        int row_index = 0;
        for (Question q : questionList) {
            int cell_index = 0;
            Row row_temp = s.createRow(3 + row_index++);

            Cell cell_data_1 = row_temp.createCell(1 + cell_index++);
            cell_data_1.setCellValue(q.getId());    //++
            cell_data_1.setCellStyle(cs_field);

            Cell cell_data_2 = row_temp.createCell(1 + cell_index++);
            cell_data_2.setCellValue(q.getCompanyId());    //++
            cell_data_2.setCellStyle(cs_field);

            Cell cell_data_3 = row_temp.createCell(1 + cell_index++);
            cell_data_3.setCellValue(q.getCatalogId());    //++
            cell_data_3.setCellStyle(cs_field);

            Cell cell_data_4 = row_temp.createCell(1 + cell_index++);
            cell_data_4.setCellValue(q.getRemark());    //++
            cell_data_4.setCellStyle(cs_field);

            Cell cell_data_5 = row_temp.createCell(1 + cell_index++);
            cell_data_5.setCellValue(q.getSubject());    //++
            cell_data_5.setCellStyle(cs_field);

            Cell cell_data_6 = row_temp.createCell(1 + cell_index++);
            cell_data_6.setCellValue(q.getPicture());    //++
            cell_data_6.setCellStyle(cs_field);

            Cell cell_data_7 = row_temp.createCell(1 + cell_index++);
            cell_data_7.setCellValue(q.getAnalysis());    //++
            cell_data_7.setCellStyle(cs_field);

            Cell cell_data_8 = row_temp.createCell(1 + cell_index++);
            cell_data_8.setCellValue(q.getType());    //++
            cell_data_8.setCellStyle(cs_field);

            Cell cell_data_9 = row_temp.createCell(1 + cell_index++);
            cell_data_9.setCellValue(q.getDifficulty());    //++
            cell_data_9.setCellStyle(cs_field);

            Cell cell_data_10 = row_temp.createCell(1 + cell_index++);
            cell_data_10.setCellValue(q.getIsClassic());    //++
            cell_data_10.setCellStyle(cs_field);

            Cell cell_data_11 = row_temp.createCell(1 + cell_index++);
            cell_data_11.setCellValue(q.getState());    //++
            cell_data_11.setCellStyle(cs_field);

            Cell cell_data_12 = row_temp.createCell(1 + cell_index++);
            cell_data_12.setCellValue(q.getReviewStatus());    //++
            cell_data_12.setCellStyle(cs_field);
        }

        //将内存中的workbook数据写入到流中
        ByteArrayOutputStream os = new ByteArrayOutputStream();//ArrayList<Byte>
        wb.write(os);
        wb.close();

        return os;
    }

servlet: QuestionServlet

private void downloadReport(HttpServletRequest request, HttpServletResponse response) throws Exception {
        /*1.告诉浏览器返回的是EXCEL文件，并且要下载该文件，不能直接在浏览器打开*/
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.addHeader("Content-Disposition","attachment;fileName="+UUIDUtil.getSimpleUUID()+".xlsx");

        /*2.创建xlns文件，并且将EXCEL中的数据写到内存*/
        ByteArrayOutputStream os = questionService.report();

        /*3.将内存中的数据变成字节数据写入到浏览器*/
        os.writeTo(response.getOutputStream());

        os.close();
}

赵广陆

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
POI入门操作Excel单元格

目录1 依赖2 写入3 读取4 练习5 文件下载1 依赖<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version></dependency><dependency> <groupId>org.apache
复制链接

扫一扫