excel读取

Person类：读取7-12月份数据时，对于excel表格中每一行数据都封装成为一个Person对象

public class Person { 
	private int roomNumber; //房间号
 	private String name; //主播名
    private double income; //主播这个月的收入 
    public Person(int roomNumber, String name, double income) { 
  		this.roomNumber = roomNumber;
   		this.name = name; 
   		this.income = income; 
   }
   public int getRoomNumber() { 
   		return roomNumber; 
   }
   public void setRoomNumber(int roomNumber) {  
   		this.roomNumber = roomNumber; 
   }
   public String getName() { 
   		return name; 
   }
   public void setName(String name) { 
   		this.name = name; 
   }
   public double getIncome() { 
   		return income; 
   }
   public void setIncome(double income) {
   		this.income = income; 
   }
   @Override 
   public String toString() { 
   		return "Person{" + "roomNumber=" + roomNumber + ", name='" + name + '\'' + ", income=" + income + '}'; 
   	} 
} 

Result类：存放的是最后写入excel表格的数据

public class Result {
    int roomNumber;
    String name;
    double payment;//上缴费用
    double money;//到手打赏

    public Result(int roomNumber, String name, double payment, double money) {
        this.roomNumber = roomNumber;
        this.name = name;
        this.payment = payment;
        this.money = money;
    }

    public int getRoomNumber() {
        return roomNumber;
    }

    public void setRoomNumber(int roomNumber) {
        this.roomNumber = roomNumber;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPayment() {
        return payment;
    }

    public void setPayment(double payment) {
        this.payment = payment;
    }

    public double getMoney() {
        return money;
    }

    public void setMoney(double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Result{" +
                "roomNumber=" + roomNumber +
                ", name='" + name + '\'' +
                ", payment=" + payment +
                ", money=" + money +
                '}';
    }
}

测试类：
main方法：

import org.apache.poi.hssf.usermodel.*;
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.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class read {
    public static void main(String[] args) throws IOException {
        //7-12月份主播收入的数据，Person对象中包含主播房间号、主播名、收入
        List<Person> July = new ArrayList<Person>();
        List<Person> August= new ArrayList<Person>();
        List<Person> September = new ArrayList<Person>();
        List<Person> October = new ArrayList<Person>();
        List<Person> November = new ArrayList<Person>();
        List<Person> December = new ArrayList<Person>();
        //获取文件中的数据
        readFile("C:\\Users\\Administrator\\Desktop\\主播打赏收入计算\\主播上缴平台费用\\7月",July);
        readFile("C:\\Users\\Administrator\\Desktop\\主播打赏收入计算\\主播上缴平台费用\\8月",August);
        readFile("C:\\Users\\Administrator\\Desktop\\主播打赏收入计算\\主播上缴平台费用\\9月",September);
        readFile("C:\\Users\\Administrator\\Desktop\\主播打赏收入计算\\主播上缴平台费用\\10月",October);
        readFile("C:\\Users\\Administrator\\Desktop\\主播打赏收入计算\\主播上缴平台费用\\11月",November);
        readFile("C:\\Users\\Administrator\\Desktop\\主播打赏收入计算\\主播上缴平台费用\\12月",December);
        //获取主播数量
        int num = July.size();
        //依次计算每个主播的上缴费用和所得收入并将结果存于result中
        List<Result> results = new ArrayList<Result>();
        for(int i = 0 ; i < num ; i++){
            calculate(December.get(i),results,July.get(i),August.get(i),September.get(i),October.get(i),November.get(i),December.get(i));
        }
        for(Result result : results){
            System.out.println(result.toString());
        }
        //将结果写入文件中
        writeFile("C:\\Users\\Administrator\\Desktop\\主播打赏收入计算\\主播上缴费用表-12月.xlsx",results);
    }

    public static void readFile(String path , List<Person> persons) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(path+"\\主播上缴平台费用.xlsx");
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        int rowCount = sheet.getPhysicalNumberOfRows();
        Row row = sheet.getRow(0);
        //得到列
        int cellCount = row.getPhysicalNumberOfCells();
        for(int cellNum = 0 ; cellNum < cellCount ; cellNum++){
            Cell cell = row.getCell(cellNum);
            if(cell!=null){
                System.out.print(cell.getStringCellValue()+"|");
            }
        }
        System.out.println();

        for(int rowNum = 1 ; rowNum < rowCount ; rowNum++){
            Row rowData = sheet.getRow(rowNum);
            if(rowData!=null){
                int roomNumber = 0;
                String name = "";
                double income = 0;
                for(int cellNum = 0 ; cellNum < cellCount ; cellNum++){
                    Cell cell = rowData.getCell(cellNum);
                    if(cell!=null){
                        if(cellNum == 0){
                            roomNumber = (int) cell.getNumericCellValue();
                        }
                        if(cellNum == 1){
                            name = cell.getStringCellValue();
                        }
                        else{
                            income =  cell.getNumericCellValue();
                        }
                    }
                }
                Person person = new Person(roomNumber,name,income);
                persons.add(person);
                System.out.println(person.toString());
            }
            System.out.println();
        }

        fileInputStream.close();
    }
    //计算主播12月份上缴费用和所得打赏
    //第一个参数是传入的12月份的数据，以便获取到12月份的收入和主播房间号和名字
    public static void calculate(Person person, List<Result> results, Person...persons){
        double payment = 0;//上缴费用
        double money = 0;//所得收入
        double totalPayment=0;//累计上缴额
        //通过循环处理可以得到最后一个月的累计上缴额和上缴费用
        for(Person p : persons){
            totalPayment += p.getIncome()-5000;
            payment = totalPayment*getPaymentRate(totalPayment)-payment;
        }
        //十二月份到手打赏
        money = person.getIncome()-payment;
        Result result = new Result(person.getRoomNumber(),person.getName(),payment,money);
        results.add(result);
    }

    public static double getPaymentRate(double money){
        if(money <= 50000){
            return 0.01;
        }else if(money > 50000 && money <= 100000){
            return 0.02;
        }else if (money > 100000 && money <= 200000){
            return 0.04;
        }else if (money >= 200000 && money <= 400000){
            return 0.06;
        }else if (money >= 400000 && money <= 800000){
            return 0.08;
        }else if(money >= 800000 && money < 1600000){
            return 0.1;
        }else{
            return 0.12;
        }
    }


    public static void writeFile(String path , List<Result> results) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        //设置表头
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("主播房间号");
        cell = row.createCell(1);
        cell.setCellValue("主播姓名");
        cell = row.createCell(2);
        cell.setCellValue("上缴费用");
        cell = row.createCell(3);
        cell.setCellValue("到手打赏");

        //写入具体的数据
        for(int i = 0 ; i < results.size() ; i++){
            HSSFRow row1 = sheet.createRow(i+1);
            Result result = results.get(i);
            row1.createCell(0).setCellValue(result.getRoomNumber());
            row1.createCell(1).setCellValue(result.getName());
            row1.createCell(2).setCellValue(result.getPayment());
            row1.createCell(3).setCellValue(result.getMoney());
        }

        //将文件保存到指定的位置
        FileOutputStream fos = new FileOutputStream(path);
        workbook.write(fos);
        fos.close();
    }
}