POI导入导出Excel

一、需要的jar包

二、外部Excel导入代码参考

package com.cn.service;

import com.cn.common.TextContainer;
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 java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.text.SimpleDateFormat;

public class ReadTextInfo implements Runnable {
    @Override
    public void run() {
        try {
            FileInputStream fileInput = new FileInputStream("C:\\Users\\hyj\\Desktop\\Analyse\\aa.xlsx");//创建文件输入流
            XSSFWorkbook wb = new XSSFWorkbook(fileInput);//由输入流文件得到工作簿对象
            XSSFSheet sheet = wb.getSheetAt(0);//获取第一个sheet
            int lastRowNum = sheet.getLastRowNum(); //获取表格内容的最后一行的行数

            //rowBegin代表要开始读取的行号，下面这个循环的作用是读取每一行内容
            for (int i = 1; i <= lastRowNum; ++i) {
                XSSFRow row = sheet.getRow(i);//获取每一行
                int columnNum = row.getLastCellNum();//获取每一行的最后一列的列号，即总列数
                String lineText = "";
                for (int j = 0; j < columnNum; ++j) {
                    XSSFCell cell = row.getCell(j);//获取每个单元格
                    if(j==0){
                        lineText += "##" + cell.getNumericCellValue();
                    }else if(j==1){
                        lineText += "##" + cell.getNumericCellValue();
                    }else if(j==2){
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        lineText += "##" + format.format(cell.getDateCellValue());
                    }else {
                        lineText += "##" + cell.getStringCellValue();
                    }

                    //System.out.printf("%s\t", cell.getStringCellValue());
                }
                TextContainer.blockingQueue.put(lineText.replaceFirst("##",""));
                //System.out.println(lineText.replaceFirst("#",""));
            }
            //数据读取完，做的标记。方便后面退出实时监控队列的消费进程。
            TextContainer.blockingQueue.put("0##0##0##end&&&");
            wb.close();
            fileInput.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

二、导出为Excel代码参考

package com.cn.service;

import com.cn.common.TextContainer;
import com.cn.entity.CdrInfo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;

public class OutputResult implements Runnable {
    @Override
    public void run() {
        //结果写入excel
        //创建一个workbook对应一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //在workbook中创建一个sheet对应excel中的sheet
        HSSFSheet sheet = workbook.createSheet("分析结果");
        //在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        //创建单元格，设置表头
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("name");
        cell = row.createCell(1);
        cell.setCellValue("age");
        cell = row.createCell(2);
        cell.setCellValue("year");
        cell = row.createCell(3);
        cell.setCellValue("phone");
        cell = row.createCell(4);
        cell.setCellValue("weight");
        int i=0;
        while (true){
           i++;
            try{
                Person person= TextContainer.resultBlockingQueue.take();
               // System.out.println(cdrInfo.getCallernm()+","+cdrInfo.getCallednm()+","+cdrInfo.getStarttalktime()+","+cdrInfo.getCall_full_text()+","+cdrInfo.getZpFeatures());
                HSSFRow row1 = sheet.createRow(i);
                //数据写入xls
                row1.createCell(0).setCellValue(person.getName());
                row1.createCell(1).setCellValue(person.getAge());
                row1.createCell(2).setCellValue(person.Year());
                row1.createCell(3).setCellValue(person.getPhone());
                row1.createCell(4).setCellValue(person.getWeight());

                if(person.getPhone.equals("end&&&")){
                    System.out.println("########################################################################:second blockingQueue is breaked");
                    break;
                }
            }catch (Exception e){
                e.printStackTrace();
            }

        }

        System.out.println("########################################################################:second blockingQueue is breaked");
        try {
            //FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\haoyajun\\Desktop\\zpAnalyse\\result.xls");
            FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\haoyajun\\Desktop\\zpAnalyse\\result.xlsx");
            workbook.write(fileOutputStream);
            fileOutputStream.close();
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}