POI导入导出Excel

╰半夏微凉° 2022-12-15 11:23 239阅读 0赞

# 一、需要的jar包 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5al9raW5n_size_16_color_FFFFFF_t_70][]

# 二、外部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();
            }
    
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5al9raW5n_size_16_color_FFFFFF_t_70]: /images/20221123/1015322f8ab14b1a89468bb2fb7d40da.png