首页 > 编程语言 >JAVA操作Excel (POI)

JAVA操作Excel (POI)

时间:2022-09-28 18:56:23浏览次数:60  
标签:JAVA String Excel System workbook POI println sheet out

package com.quantum.utils;

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

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

public class ExcelUtils {

    public static Workbook workbook;
    public static ArrayList<String> activeManage;


    public static void openExcel(String filepath) {
        ExcelUtils.workbook = getWorkbook(filepath);
    }

    public static Workbook getWorkbook(String filepath) {
        FileInputStream inputStream = null;
        Workbook Workbook = null;
        try {
            inputStream = new FileInputStream(filepath);
            if (filepath.endsWith(".xls")) {
                Workbook = new HSSFWorkbook(inputStream);
            } else {
                Workbook = new XSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                assert inputStream != null;
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return Workbook;
    }

    public void modify(String username, String status, String email, String managerEmail, String managerStatus, String manager) {
        openExcel("src/main/resources/data/testData.xls");
        Sheet sheet = workbook.getSheet("checkStatus");

        int rows1 = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < rows1; i++) {
            // 第一列
            String value = sheet.getRow(i).getCell(0).getStringCellValue();
            System.out.println(value);
            if (username.equalsIgnoreCase(value)) {
                // status
                sheet.getRow(i).createCell(2).setCellValue(status);
                System.out.println("status写入成功");
                // email
                sheet.getRow(i).createCell(1).setCellValue(email);
                System.out.println("email写入成功");

                // manager
                sheet.getRow(i).createCell(3).setCellValue(manager);
                System.out.println("manager写入成功");

                // manager email
                sheet.getRow(i).createCell(4).setCellValue(managerEmail);
                System.out.println("manager email写入成功");

                // manager status
                sheet.getRow(i).createCell(5).setCellValue(managerStatus);
                System.out.println("manager status写入成功");

                break;
            }


        }
        generateExcel(workbook, "src/main/resources/data/testData.xls");


    }

    public static void writeData(String username, String status, int column) {

        Sheet sheet = workbook.getSheetAt(0);

        int rows1 = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < rows1; i++) {
            // 第一列 username
            String value = cellValue(sheet.getRow(i).getCell(0));
            System.out.println(value);
            if (username.equalsIgnoreCase(value)) {
                // status
                sheet.getRow(i).createCell(column).setCellValue(status);
                System.out.println("status写入成功");
                break;
            }
        }
    }

    public static void generateExcel(Workbook workbook, String pathName) {
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(pathName);
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


    }

    public static void getActiveManager() {
        Workbook workbook = getWorkbook("src/main/resources/data/activeManager.xlsx");

        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        ArrayList<String> managerList = new ArrayList<>();

        for (int i = 1; i < lastRowNum; i++) {
            String stringCellValue = sheet.getRow(i).getCell(0).getStringCellValue().trim();
            managerList.add(stringCellValue);
        }

        System.out.println(managerList);
        activeManage = managerList;

    }

    private static String cellValue(Cell cell) {
        String s = "";
        if (cell != null) {
            int cellType = cell.getCellType();
            switch (cellType) {
                case 0:
                    long numericCellValue = (long) cell.getNumericCellValue();
                    s = String.valueOf(numericCellValue);
                    break;
                case 1:
                    s = cell.getStringCellValue();
                    break;
            }
        }

        return s;
    }

    public static void setCellBackgroundAsRed(Cell cell) {

        CellStyle cellStyle = cell.getCellStyle();
        short color = cellStyle.getFillForegroundColor();
        short fillPattern = cellStyle.getFillPattern();

        if (color == IndexedColors.RED.getIndex() && fillPattern == CellStyle.SOLID_FOREGROUND) {
            System.out.println("无需再设置");
        } else {
            CellStyle redCell = workbook.createCellStyle();
            redCell.setFillForegroundColor(IndexedColors.RED.getIndex());
            redCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
            cell.setCellStyle(redCell);
        }

    }

    public static void main(String[] args) {

        String path = "src/main/resources/data/activeUser1.xlsx";
        openExcel(path);
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        int lastRowNum = sheet.getLastRowNum();

        System.out.println("rows = " + rows);
        System.out.println("lastRowNum = " + lastRowNum);

        ArrayList<String> li2 = new ArrayList<>();


//        for (int i = 1; i < rows; i++) {
//            Row row = sheet.getRow(i);
//            String s2 = cellValue(row.getCell(6));
//            String s3 = cellValue(row.getCell(7));
//            String s4 = cellValue(row.getCell(8));
//
//            if (!"".equals(s2)) {
//                li2.add(s2);
//            }
//            if (!"".equals(s3)) {
//                li2.add(s3);
//            }
//            if (!"".equals(s4)) {
//                li2.add(s4);
//            }
//        }
//
//        for (int i = 1; i < rows; i++) {
//            Row row = sheet.getRow(i);
//            Cell cell = row.getCell(0);
//            String s = cellValue(cell);
//            if (li2.contains(s)) {
//                setCellBackgroundAsRed(cell);
//            }
//            Cell cell1 = row.getCell(1);
//            String s1 = cellValue(cell1);
//            if (li2.contains(s1)) {
//                setCellBackgroundAsRed(cell1);
//            }
//
//        }
//
//        generateExcel(workbook, path);


    }


}



标签:JAVA,String,Excel,System,workbook,POI,println,sheet,out
From: https://www.cnblogs.com/yan061/p/16739220.html

相关文章

  • JAVA Spring学习笔记------注解开发
    注解开发可以在对应的类中配置Bean具体方法如下:利用@Component来声明当前类为Bean@Component("userDaoimpl")//Reposity和component效果一样//spring对于不同的层......
  • JAVA Spring学习笔记------Spring整合Mybatis
    Spring整合Mybatis首先看一下项目目录   因为以前所有的知识点都在一个模块里,而且目录比较混乱所以对模块重构了一下首先我们要在pom.xml文件中声明所需要的依赖......
  • java异常
    西二旗是一个恐怖的地铁站,光排队得排很久。只要写代码就存在bug,唯一一个不出bug得方法就是不写代码。异常:在程序的运行过程中,出现的不正常情况叫做异常.注意:1.相同的代......
  • java.lang.RuntimeException: Unable to instantiate class java.lang.Integer with v
    代码为@Value("${address.count}")privateIntegercount;单元测试中代码修改为@InjectableprivateIntegercount=1000;单元测试中类型没法自动转换,......
  • linux系统下运行java项目,(做笔记)
    原链接处:Linuxjar包后台运行-阿里云开发者社区(aliyun.com) Linuxjar包后台运行2016-04-10 1755简介: Linux运行jar包命令如下:方式一:java -jarshareniu.ja......
  • Java11安装(win11)
    下载JDK解压版本下载后解压,并放到一个没有中文路径的目录,如图所示:配置Java环境变量以Windows11系统为例,打开设置,搜索“环境”,点击“编辑系统环境变量”,如图所示:点击......
  • Java Script 原型链原理
    所有对象都有隐式原型;原型也是对象,也有隐式原型.functionUser(){}console.log(User.prototype);functionUser(){}varu=newUser();console.log(u.hasOwnProper......
  • java常见面试补录
    ***外卖系统:(业务讲解)。前端:用户下单;后端:管理员;拦截器。提示:业务介绍;涉及哪些模块;业务流程(哪些角色做什么事)。*、Mysql,redis:mysql异步场景时,Mysql无法写入。(......
  • 【JAVA】探讨 Java 中 valueOf 和 parseInt 的区别
    前言在编程中,遇到类型转换,好像会经常用到parseInt和valueOf,当然这里只拿Integer类型进行陈述,其他类型也是雷同的;想必有读者也跟我一样,经常交叉使用这两个方法,但却不......
  • Java基础(二)| 基础语法之运算输入与控制语句
    ⭐本专栏旨在对JAVA的基础语法及知识点进行全面且详细的讲解,完成从0到1的java学习,面向零基础及入门的学习者,通过专栏的学习可以熟练掌握JAVA编程,同时为后续的框架学习,进阶开......