使用POI技术简单的将数据库中的数据读取出为Excel文件

利用POI生成.xlxs

效果图

利用反射将实体类的属性读取出来,然后对应数据库表中的字段,循环插入对应的数据

数据来源

本次测试的数据为数据库查询得出,所以需要MyBatis查询数据库,然后根据反射将实体类的属性匹配字段的数据循环读取出来,并写入文件.

代码详解(POI写和读在代码中的不同测试方法中)

package com.fs.testpoi;

import com.fs.domain.store.Question;
import com.fs.service.store.impl.QuestionServiceImpl;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;

/**
 * 使用POI读取数据库生成为Excel表格文件
 */
public class TestPOI {

    //需要调用业务层方法来查询数据库中所有数据
    private QuestionServiceImpl questionService = new QuestionServiceImpl();

    /**
     * 利用反射实现POI将数据写入
     */
    @Test
    public void POIToFS(){
        //poi写excel
        //创建xlxs文件对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建工作表对象
        XSSFSheet sheet = workbook.createSheet("题目数据文件");
        /**
         * 整体流程
         * 相当于workbook对象--->sheet对象--->row对象--->cell对象--->设置样式
         */
        /*
        通用样式配置
         */
        //创建一个标题样式
        CellStyle cellStyle_title_fields = workbook.createCellStyle();
        //设置水平对齐
        cellStyle_title_fields.setAlignment(HorizontalAlignment.CENTER);
        //设置上下左右的线条
        cellStyle_title_fields.setBorderTop(BorderStyle.HAIR);
        cellStyle_title_fields.setBorderBottom(BorderStyle.HAIR);
        cellStyle_title_fields.setBorderLeft(BorderStyle.HAIR);
        cellStyle_title_fields.setBorderRight(BorderStyle.HAIR);

        /*
                //制作标题
         */
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 13));
        //获取第一行
        XSSFRow row_1 = sheet.createRow(1);
        //第一行的第一列
        XSSFCell cell_1_1 = row_1.createCell(1);
        //给这个格子里面设置值
        cell_1_1.setCellValue("在线试题导出信息");
        //创建一个标题样式
        CellStyle cellStyle_title = workbook.createCellStyle();
        //设置水平对齐
        cellStyle_title.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐
        cellStyle_title.setVerticalAlignment(VerticalAlignment.CENTER);
        //给这标题设置样式
        cell_1_1.setCellStyle(cellStyle_title);
        /*
                //制作表头
         */
        //定义一个数组,来存储表头数据
        String[] fields = {"题目ID", "所属公司ID", "所属目录ID", "题目简介", "题干描述", "题干配图", "题目分析", "题目类型", "题目难度", "是否经典题", "题目状态", "审核状态","创建时间"};
        //获取第二行
        XSSFRow row_2 = sheet.createRow(2);
        for (int i = 0; i < fields.length; i++) {
            //第二行循环加入列
            XSSFCell cell_2_for = row_2.createCell(1 + i);
            //给这个格子里面设置值
            cell_2_for.setCellValue(fields[i]);
//            //创建一个标题样式
//            CellStyle cellStyle_title_fields = wb.createCellStyle();
//            //设置水平对齐
//            cellStyle_title_fields.setAlignment(HorizontalAlignment.CENTER);
            //给这标题设置样式
            cell_2_for.setCellStyle(cellStyle_title_fields);
        }



        /**
         * 反射实现数据写入
         * 每一个Question数据就是一行数据,Question的属性就没每个列
         */
        //调用业务层查询出所有的Question数据
        List<Question> datas = questionService.findAll();
        for (int i = 0; i < datas.size(); i++) {
            //每次循环创建一行来存储Question
            XSSFRow row = sheet.createRow(3 + i);
            //得到每个Question
            Question question = datas.get(i);
            //通过反射获取每个字段
            Field[] declaredFields = question.getClass().getDeclaredFields();
            //遍历每个字段当做列,因为我实体类中的字段有2个是关联实体类,就不需要获取这2列,所有-2.少两列
            for (int j = 0; j < declaredFields.length-2; j++) {
                Field declaredField = declaredFields[j];
                //暴力反射获取字段
                declaredField.setAccessible(true);
                //获取到每个字段名
                Object o = null;
                try {
                    o = declaredField.get(question);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                //每获取到一个字段就创建一行
                XSSFCell cell = row.createCell(1 + j);
                //给这一个格设置值,因为表中的字段有的为null,所以要先判断一下在写入,否则会空指针
                if (o!=null) {
                    //调用toString方法将数据以字符串的形式写入表格
                    cell.setCellValue(o.toString());
                }
            }
        }

        //创建文件对象.作为工作薄内容的输出文件
        File file = new File("test.xlsx");


        //输出时通过流的形式对外输出
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
            //关流
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 使用POI技术读取Excel文件
     */
    @Test
    public void ReadExcelByPOI(){
        //创建读取的文件工作薄对象
        XSSFWorkbook sheets = null;
        try {
            sheets = new XSSFWorkbook("test.xlsx");
        } catch (IOException e) {
            e.printStackTrace();
        }
        //获取工作表对象,第1个,索引从0开始
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        //循环读取表中的行
        int rowIndex = 2;
        int cellIndex = 1;
        while (true){
            //获取表中的行
            XSSFRow row = sheetAt.getRow(rowIndex);
            while (true){
                //获取对应坐标的这一格
                XSSFCell cell = row.getCell(cellIndex);
                //读取格子中的数据
                String stringCellValue = cell.getStringCellValue();
                //打印输出一下
                System.out.print(stringCellValue+"__");

                //假设我们知道我们表中有多少列数据,循环多少次
                cellIndex++;
                if (cellIndex==14){
                    break;
                }
            }
            //每次读取一行换一行
            System.out.println();
            //将列归1,索引是从0开始,我们数据在1列才有
            cellIndex = 1;
            //假设我们只循环18行数据
            rowIndex++;
            if (rowIndex==18){
                break;
            }
        }
    }
}

POI读的控制台输出

poi读取excel工具类

package com.fs.utils;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

public class POIUtils {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    private final static String DATE_FORMAT = "yyyy/MM/dd";
    /**
     * 读入excel文件，解析后返回
     * @param file
     * @throws IOException
     */
    public static List<String[]> readExcel(MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum  = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了第一行的所有行
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    //循环当前行
                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    list.add(cells);
                }
            }
            workbook.close();
        }
        return list;
    }

    //校验文件是否合法
    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            throw new IOException(fileName + "不是excel文件");
        }
    }
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型，需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}