signed

QiShunwang

“诚信为本、客户至上”

POI操作EXCEl表格(包括对长整型数字和日期的处理)

2021/6/24 19:07:20   来源:

POI操作EXCEl表格(包括对长整型数字和日期的处理)

      • 需求原因
      • 1、pom文件引入依赖
      • 3、读实现
      • 4、写实现

需求原因

很久没有输出了,深感惭愧!!!
由于需要将xlsx格式的excel表格的数据写入到.sql文件,再直接执行.sql文件后将数据插入到表格里。
表格里包含数字和文本,其中比较难处理的是较长的数值和日期,POI会将较长的数字转成科学计数法,日期也会转成数值,而我想要的结果都是维持原来的模样。
最后实现了自己的需求,参考了下面两篇博客:
1、POI导入数字变为科学计数和后面自动加.0解决方法
2、java poi 获取单元格值时间

我的实现方式直接双手献上,也有助于自己以后再用到可以很快找到

1、pom文件引入依赖

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

        <!-- joda日期处理-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.9.5</version>
        </dependency>

3、读实现

package com.fanhf;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicReference;

public class ExcelReadTest {
    private static String readPath = "D:\\data\\";
    private static final String INSERTDDL = "INSERT INTO `t_user`(`user_type` ,`user_code` ,`user_name` ,`create_time`) VALUES (";
    public static final String NORM_DATETIME_PATTERN = "yyyy-MM-dd HH:mm:ss";
    private static ArrayList<String> PoiDateList = new ArrayList<String>() {
        {
            add("yy");
            add("d");
            add("m");
        }
    };

    public static void main(String[] args) {
        long start = System.currentTimeMillis();
        readAndWirte();
        long end = System.currentTimeMillis();
        System.out.println("花费时间:" + (end - start) / 1000 + "s");
    }

    public static void readAndWirte() {
        try {
            FileInputStream fileInputStream = new FileInputStream(readPath + "data.xlsx");
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            Sheet sheetAt = workbook.getSheetAt(0);

            List<List<String>> cellList = Lists.newArrayList();
            System.out.println("表中总行数:" + sheetAt.getPhysicalNumberOfRows());

            for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                Row row = sheetAt.getRow(i);
                List<String> rowList = Lists.newArrayList();
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    Cell cell = row.getCell(j);
                    String value = "";
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            rowList.add(cell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            //  获取单元格值的格式化信息
                            String dataFormat = cell.getCellStyle().getDataFormatString();
                            //  判断格式化信息中是否存在:年月日
                            AtomicReference<Boolean> isDate = new AtomicReference<>(false);
                            if (StringUtils.isNotBlank(dataFormat)) {
                                PoiDateList.forEach(x -> isDate.set(isDate.get() || dataFormat.contains(x)));
                            }
                            if (!isDate.get()) {
                                //处理整数被转为小数的问题
                                NumberFormat nf = NumberFormat.getInstance();
                                value = nf.format(cell.getNumericCellValue());
                                //处理科学计数法的问题
                                if (value.indexOf(",") >= 0) {
                                    value = value.replace(",", "");
                                }
                            }
                            if (DateUtil.isCellDateFormatted(cell)) {
                                value = new SimpleDateFormat(NORM_DATETIME_PATTERN).format(cell.getDateCellValue());
                            }
                            rowList.add(value);
                            break;
                    }
                }
                cellList.add(rowList);
            }
            System.out.println("=======================已经读取完成,开始写入文件" + readPath + "ext.sql");

            FileWriter fw = null;
            try {
                fw = new FileWriter(readPath + "user.sql", true);
                for (List<String> list : cellList) {
                    StringBuilder stringBuilder1 = new StringBuilder();
                    for (String s : list) {
                        stringBuilder1.append("'").append(s).append("'").append(",");
                    }
                    String rowss = INSERTDDL + stringBuilder1.toString();
                    rowss = rowss.substring(0, rowss.length() - 1) + ");";
                    fw.write(rowss);
                    fw.write("\r\n");
                }
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    fw.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("insert语句总行数:" + cellList.size());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

另外再附上自己之前写着完的往excel写文件的测试

4、写实现

package com.fanhf;

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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileOutputStream;
import java.io.IOException;


public class ExcelWriteTest {
    private static String outPath = "D:\\tools\\idea\\workspaces\\poi-excel\\";

    public void write03xls() {
        writeExcel(1,"time-address.xls");
//        long spendTime = writeExcelBigData(1, 65536, "bigData03.xls");
//        System.out.println(65536 + "行 花费时间:"+(double) spendTime/1000 +" ms");
    }

    public void write07xlsx() {
        writeExcel(2,"time-address.xlsx");
//        long spendTime = writeExcelBigData(2, 100000, "bigData07.xlsx");
//        System.out.println(100000 + "行 花费时间:"+(double)spendTime/1000 +" ms");
    }

    public void write07xlsxPro() {
//        writeExcel(3,"time-address.xlsx");
        long spendTime = writeExcelBigData(3, 100000, "bigData07.xlsx");
        System.out.println(100000 + "行 花费时间:"+(double)spendTime/1000 +" ms");
    }

    public void writeExcel(int type,String excelExtenceName){
        FileOutputStream fileOutputStream = null;
        Workbook workbook = null;
        try {
            if (1 == type) {
               workbook = new HSSFWorkbook();
            }else{
                workbook = new XSSFWorkbook();
            }
            Sheet sheet = workbook.createSheet();
            //(1)
            Row row1 = sheet.createRow(0);
            //(1,1)
            Cell cell11 = row1.createCell(0);
            cell11.setCellValue("时间");
            //(1,2)
            Cell cell12 = row1.createCell(1);
            cell12.setCellValue("地点");
            //(1,3)
            Cell cell13 = row1.createCell(2);
            cell13.setCellValue("人数");

            //(2)
            Row row2 = sheet.createRow(1);
            //(2,1)
            Cell cell21 = row2.createCell(0);
            cell21.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            //(2,2)
            Cell cell22 = row2.createCell(1);
            cell22.setCellValue("北京市海淀区");
            //(2,3)
            Cell cell23 = row2.createCell(2);
            cell23.setCellValue(100000);

            //(3)
            Row row3 = sheet.createRow(2);
            //(3,1)
            Cell cell31 = row3.createCell(0);
            cell31.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            //(3,2)
            Cell cell32 = row3.createCell(1);
            cell32.setCellValue("北京市朝阳区");
            //(3,3)
            Cell cell33 = row3.createCell(2);
            cell33.setCellValue(200000);

            fileOutputStream = new FileOutputStream(outPath + excelExtenceName);

            workbook.write(fileOutputStream);

            System.out.println("file " + excelExtenceName + " generated successfully");
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public long writeExcelBigData(int type,int rowsNum,String excelExtenceName){
        long start = System.currentTimeMillis();

        FileOutputStream fileOutputStream = null;
        Workbook workbook = null;
        try {
            if (1 == type) {
                workbook = new HSSFWorkbook();
            }else if(2 == type){
                workbook = new XSSFWorkbook();
            }else if(3 == type){
                workbook = new SXSSFWorkbook();
            }
            Sheet sheet = workbook.createSheet();
            for (int i = 0; i < rowsNum; i++) {
                //创建65535行
                Row row1 = sheet.createRow(i);
                //每行写10列数据,
                for (int j = 0; j < 10; j++) {
                    //每行单元格的数据是0到9
                    row1.createCell(j).setCellValue(j);
                }
            }
            fileOutputStream = new FileOutputStream(outPath + excelExtenceName);
            workbook.write(fileOutputStream);
            if(3 == type){
                //清除零时文件
                ((SXSSFWorkbook) workbook).dispose();
            }
            System.out.println("file " + excelExtenceName + " generated successfully");

        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        long end = System.currentTimeMillis();
        return end - start;
    }
}

需要的自取,有好的建议万望不吝赐教!!!

--------------你知道的越多,不知道的越多-------------