Excel

格式解读

excel

依赖

<!--xls(03依赖)-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.9</version>
</dependency>

 <!--xlsx(07依赖)-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.9</version>
</dependency>

  <!--日期格式化工具-->
<dependency>
  <groupId>joda-time</groupId>
  <artifactId>joda-time</artifactId>
  <version>2.10.6</version>
</dependency>

    <!--test-->
<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.12</version>
</dependency>

Excel03版本写入

public class ExcelWrite03 {
    public static void main(String[] args) throws Exception {
        //1.创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建工作表
        Sheet sheet =workbook.createSheet("Java写入的表");
        //3.创建行(Excel是1.1)--也就是坐标Java是(0.0)
        Row row11 = sheet.createRow(0);
        //4.创建单元格(Excel是1.1)--Java是(0.1)
        Cell cell11 = row11.createCell(0);
        cell11.setCellValue("今天的收入"); //单元格里的内容
        //(1.2)
        Cell cell12 = row11.createCell(1);
        cell12.setCellValue(99999);


        //创建第二行(Excel是2.1)--Java是(1.0)
        Row row2 = sheet.createRow(1);
        //第二行单元格(Excel是2.1)--Java是(1.0)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("花费时间");//单元格里的内容

        //第二行单元格(Excel是2.2)的数据--Java是(1.1)
        Cell cell22 = row2.createCell(1);
        //格式化日期
        String time= new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);//单元格里的内容

        //创建输出流, 03版本是xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream("E:\\"+"03版本Java写入的表.xls");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();

        System.out.println("03版本Java写入的表,生成完毕!");


    }
}

Excel07版本写入

public class ExcelWrite07 {
    public static void main(String[] args) throws Exception {
        //1.创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建工作表
        Sheet sheet = workbook.createSheet("Java写入的表");
        //3.创建行(Excel是1.1)--也就是坐标Java是(0.0)
        Row row11 = sheet.createRow(0);
        //4.创建单元格(Excel是1.1)--Java是(0.1)
        Cell cell11 = row11.createCell(0);
        cell11.setCellValue("今天的收入"); //单元格里的内容
        //(1.2)
        Cell cell12 = row11.createCell(1);
        cell12.setCellValue(99999);


        //创建第二行(Excel是2.1)--Java是(1.0)
        Row row2 = sheet.createRow(1);
        //第二行单元格(Excel是2.1)--Java是(1.0)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("花费时间");//单元格里的内容

        //第二行单元格(Excel是2.2)的数据--Java是(1.1)
        Cell cell22 = row2.createCell(1);
        //格式化日期
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);//单元格里的内容

        //创建输出流, 07版本是xlsx结尾
        FileOutputStream fileOutputStream = new FileOutputStream("E:\\" + "07版本Java写入的表.xlsx");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();

        System.out.println("07版本Java写入的表,生成完毕!");
    }
}

Excel03版本写入批量数据

//03版本最多写入65536行

public class ExcelWrite03BigData {
    public static void main(String[] args) throws IOException {
        //开始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum <65536 ; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            //在每一行里创建单元格
            for (int cellNum = 0; cellNum <10 ; cellNum++) {
                Cell cell = row.createCell(cellNum); //创建
                cell.setCellValue(cellNum); //设置值
            }
        }
        System.out.println("写入完成!");
        FileOutputStream fileOutputStream = new FileOutputStream("E:\\"+"03版本Java写入的大数据表.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println("用时:"+(double)(end-begin)/1000+"s");
    }
}

//超过65536报出异常
for (int rowNum = 0; rowNum <65537 ; rowNum++) {}
IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

Excel07版本写入批量数据

public class ExcelWrite07BigData {
    public static void main(String[] args) throws IOException {
        //开始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum <100000 ; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            //在每一行里创建单元格
            for (int cellNum = 0; cellNum <10 ; cellNum++) {
                Cell cell = row.createCell(cellNum); //创建
                cell.setCellValue(cellNum); //设置值
            }
        }
        System.out.println("写入完成!");
        FileOutputStream fileOutputStream = new FileOutputStream("E:\\"+"07版本Java写入的大数据表.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println("用时:"+(double)(end-begin)/1000+"s");
    }
}

Excel07版本写入批量数据优化

public class ExcelWrite07BigDataPlus {
    public static void main(String[] args) throws IOException {
        //开始时间
        long begin = System.currentTimeMillis();
        //创建工作簿
        Workbook workbook = new SXSSFWorkbook(); //使用优化
        //创建工作表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum = 0; rowNum <100000 ; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            //在每一行里创建单元格
            for (int cellNum = 0; cellNum <10 ; cellNum++) {
                Cell cell = row.createCell(cellNum); //创建
                cell.setCellValue(cellNum); //设置值
            }
        }
        System.out.println("写入完成!");
        FileOutputStream fileOutputStream = new FileOutputStream("E:\\"+"07版本Java写入的大数据表优化版.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //使用优化会产生临时文件,需要清理
        //清理临时文件
        ((SXSSFWorkbook) workbook).dispose();

        long end=System.currentTimeMillis();
        System.out.println("用时:"+(double)(end-begin)/1000+"s");
    }
}

Excel03版本读取数据

public class ExcelRead03 {
    public static void main(String[] args) throws IOException {
        //获取文件输入流
        FileInputStream fileInputStream = new FileInputStream("E:\\" + "03版本Java写入的表.xls");

        //1.创建工作簿对象
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //2.得到表
        Sheet sheetAt = workbook.getSheetAt(0); //通过坐标
        //3.得到行
        Row row = sheetAt.getRow(0);
        //4.得到列
        Cell cell = row.getCell(1);

        //读取值的时候一定要注意类型
        System.out.println(cell.getNumericCellValue());
        fileInputStream.close();

    }
}

Excel07版本读取数据

public class ExcelRead07 {
    public static void main(String[] args) throws IOException {
        //获取文件输入流
        FileInputStream fileInputStream = new FileInputStream("E:\\" + "07版本Java写入的表.xlsx");

        //1.创建工作簿对象
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //2.得到表
        Sheet sheetAt = workbook.getSheetAt(0); //通过坐标
        //3.得到行
        Row row = sheetAt.getRow(0);
        //4.得到列
        Cell cell = row.getCell(1);

        //读取值的时候一定要注意类型
        System.out.println(cell.getNumericCellValue());
        fileInputStream.close();

    }
}

Excel读取不同的数据类型

public class ExcelReadType {
    public static void main(String[] args) throws Exception {
        //获取文件输入流
        FileInputStream fileInputStream = new FileInputStream("E:\\" + "不同数据类型.xls");
        //创建工作簿对象
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //创建工作表对象
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题的内容,标题左边在第0行
        Row rowTitle = sheet.getRow(0);
        if (rowTitle!=null){
            //获取单元格数量
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum <cellCount ; cellNum++) {
                ///获取单元格数据
                Cell cell = rowTitle.getCell(cellNum);
                if (cell!=null){
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();//需要注意类型
                    System.out.print(cellValue+"|");
                }
            }
            System.out.println();
        }

        //获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum <rowCount ; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData!=null){
                //读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum <cellCount ; cellNum++) {
                    System.out.println("["+(rowNum+1)+"-"+(cellNum+1)+"]");

                    Cell cell = rowData.getCell(cellNum);
                    //匹配列的数据类型
                    if(cell!=null){
                        int cellType = cell.getCellType();
                        String cellValue="";

                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING: //字符串
                                System.out.println("【String】");
                                cellValue=cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: //布尔值
                                System.out.println("【Boolean】");
                                cellValue=String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: //空
                                System.out.println("【Blank】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: //数字(日期、普通数字)
                                System.out.println("【Number】");
                                if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    System.out.println("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue=cell.toString();
                                }else {
                                    //不是日期格式,防止数字过长!
                                    System.out.println("【转换为字符串输出】");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue=cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: //字符串
                                System.out.println("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                    }

                }
            }
            fileInputStream.close();

        }

    }
}

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!