C4考试之表格读写

1.1 任务目标

运用财务部门提供的数据（xx部-薪酬表.xlsx和员工五险一金申报表.xlsx)，根据以下规则核算出四个部门10月份的薪酬数据并将汇总数据提供到“企业人员月度工资成本支付表.xlsx“模版中。

1.2 任务说明

核心业务：合并报表、计算应纳税额

1.3 具体要求

薪酬统计规则如下：

1. 部门薪资明细中的底薪与岗位工资以及绩效、奖金、补助等各项正收益合并至“企业人员月度工资成本支付表.xlsx”的“工资”一栏。

2. 部门薪资明细中的考勤扣除、违规处罚等负收益项全部合并至“企业人员月度工资成本支付表.xlsx”的“扣款”一栏。

3. “工资-扣款”的计算结果放入“应发工资”一栏。

4. “员工五险一金申报表.xlsx”中提供了员工申报五险一金的基数，根据每人的申报基数，可计算五险一金的具体缴纳数额，将计算结果填写至“企业人员月度工资成本支付表.xlsx“的五险一金的“个人缴纳部分”和“企业缴纳部分”对应栏，不缴纳的项目填写0。

   五险一金计算规则如下：

   • 养老保险：单位，20%，个人，8%
   • 医疗保险：单位，8%，个人，2%；
   • 失业保险：单位，2%，个人，1%；
   • 工伤保险：单位，0.5%，个人不用缴费；
   • 生育保险：单位，0.7%，个人不用缴费；
   • 住房公积金缴纳比例有 8%、10%、12%三档，每人具体公积金缴纳比例数据记录在“员工五险一金申报表.xlsx”中。（例如：张三缴纳基数为5000，对应缴纳比例为12%，那么张三个人应缴纳公积金为600元，企业应缴纳公积金600元）

5. “应发工资+企业缴纳五险一金”的结果填入“企业人员月度工资成本支付表.xlsx“的“企业支出成本”一栏。

6. 根据“个税累计预扣法”定义及《个人所得税预扣率表》得出应纳税额，并将其填入“企业人员月度工资成本支付表.xlsx“的“应纳税额”一栏

7. “应发工资-五险一金个人缴纳部分-个税”的结果填入“企业人员月度工资成本支付表.xlsx“的“实发工资”一栏。

8. 根据以上规则核算出四个部门10月份的薪酬数据并将汇总数据提供到“企业人员月度工资成本支付表.xlsx“模版中。

2.1 任务实现

首先需要导入依赖

 <!--       导入Apache POI 5.2.2 依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version>
        </dependency>


        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>

2.1.1 文件读取类

package zhanglong;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;

public class ExcelRead {
    private LinkedList<String> cellArr;//定义一个集合，来存储excel表格中，每一列单元格的数据
    private ArrayList<LinkedList<String>> excelArr;//定义一个集合，来存储excel表格中，每一行各列的数据

    /**
     *
     * @param inputFile 需要读取的文件夹
     * @return 返回一个从excel表格中读取出来的数据集合
     * @throws IOException
     * @throws InvalidFormatException
     */
    public ArrayList<LinkedList<String>> readExcel(File inputFile) throws IOException, InvalidFormatException {
        File[] files = inputFile.listFiles();//获取需要读取的文件对象里所有的子文件对象
        excelArr = new ArrayList<>();//创建一个集合对象
        if(files != null)//如果该文件的子文件不为空，则依次读取其子文件
        {
            for (File file : files) {
               if(file.isFile())//如果子文件是文件的话调用文件读取方法
               {
                   readFile(file,excelArr);
               }
            }
        }
        else if(inputFile.isFile()){
            readFile(inputFile,excelArr);
        }
        return excelArr;
    }

    /**
     *
     * @param file 需要读取的excel文件对象
     * @param excelArr 用于存储excel表格数据的集合
     * @throws IOException
     * @throws InvalidFormatException
     */
    public void readFile(File file,ArrayList<LinkedList<String>> excelArr) throws IOException, InvalidFormatException {
        XSSFWorkbook workbook = new XSSFWorkbook(file);//获取该excel表格的工作簿（xlsx 07版）
        XSSFSheet sheet = workbook.getSheetAt(0);//获取工作簿的页面

        Iterator<Row> rowIterator = sheet.iterator();//获取页面的行迭代器
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            DataFormatter formatter = new DataFormatter();//创建一个数据格式对象，用于读取表格里单元格不同类型的数据（String 、int）
            //如果该行第一列的单元格的数据等于“工号"说明是表头，忽略，跳过该行不读取
            if(formatter.formatCellValue(row.getCell(0)).equals("工号"))
            {
                continue;
            }
            Iterator<Cell> cellIterator = row.iterator();
            cellArr = new LinkedList<>();//用于存储每一行的每一列的单元格数据

            while (cellIterator.hasNext())
            {
                Cell cell = cellIterator.next();
                String s = formatter.formatCellValue(cell);//获取列单元格的数据
//                System.out.printf("%10s",s);
                cellArr.add(s);
            }
//            System.out.println();
            excelArr.add(cellArr);
        }
        workbook.close();
    }
}

2.1.2 文件写入类

package zhanglong;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedList;

public class ExcelWrite {
    public static void writeExcel(File copyFile,File destFile, ArrayList<LinkedList<String>> arr, String type) throws IOException, InvalidFormatException {
        //创建一个成本表副本，用于读取应填表的表头
        XSSFWorkbook workbook = new XSSFWorkbook(copyFile);
        XSSFSheet sheet = workbook.getSheetAt(0);
        if (type.equals("薪酬表")) {
            for (int i = 0; i < arr.size(); i++) {
                XSSFRow row = sheet.createRow(i + 1);//跳过表头
                LinkedList<String> list = arr.get(i);
                //将前三列数据填入
                for (int j = 0; j < 3; j++) {
                    row.createCell(j).setCellValue(list.get(j));
                }

                //部门薪资明细中的底薪与岗位工资以及绩效、奖金、补助等各项正收益合并至“企业人员月度工资成本支付表.xlsx”的“工资”一栏。
                double salary = Double.parseDouble(list.get(3)) + Double.parseDouble(list.get(4)) + Double.parseDouble(list.get(5)) + Double.parseDouble(list.get(7)) + Double.parseDouble(list.get(8));
                row.createCell(3).setCellValue(salary);

                //部门薪资明细中的考勤扣除、违规处罚等负收益项全部合并至“企业人员月度工资成本支付表.xlsx”的“扣款”一栏。
                double debit = Double.parseDouble(list.get(6)) + Double.parseDouble(list.get(9));
                row.createCell(4).setCellValue(debit);

                //“工资-扣款”的计算结果放入“应发工资”一栏。
                double payableSalary = salary - debit;
                row.createCell(20).setCellValue(payableSalary);


            }
        } else if (type.equals("申报表")) {
            //养老保险：单位，20%，个人，8%
            //医疗保险：单位，8%，个人，2%；
            //失业保险：单位，2%，个人，1%；
            //工伤保险：单位，0.5%，个人不用缴费；
            //生育保险：单位，0.7%，个人不用缴费；
            //住房公积金缴纳比例有 8%、10%、12%三档，
            for (int i = 0; i < arr.size(); i++) {
                LinkedList<String> list = arr.get(i);
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    //如果申报表里工号和表格里某行第一列工号相同
                    if (list.get(0).equals(sheet.getRow(j).getCell(0).getStringCellValue())) {
                        double salaryNum = Double.parseDouble(list.get(3));//申报基数

                        //养老保险：单位，20%，个人，8%
                        double pension1 = salaryNum * 0.08;
                        double pension2 = salaryNum * 0.2;
                        sheet.getRow(j).createCell(5).setCellValue(pension1);
                        sheet.getRow(j).createCell(12).setCellValue(pension2);

                        //医疗保险：单位，8%，个人，2%；
                        double yiBao1 = salaryNum * 0.02;
                        double yiBao2 = salaryNum * 0.08;
                        sheet.getRow(j).createCell(6).setCellValue(yiBao1);
                        sheet.getRow(j).createCell(13).setCellValue(yiBao2);

                        //失业保险：单位，2%，个人，1%；
                        double loseWork1 = salaryNum * 0.01;
                        double loseWork2 = salaryNum * 0.02;
                        sheet.getRow(j).createCell(7).setCellValue(loseWork1);
                        sheet.getRow(j).createCell(14).setCellValue(loseWork2);

                        //工伤保险：单位，0.5%，个人不用缴费；
                        double workHurt1 = 0.0;
                        double workHurt2 = salaryNum * 0.05;
                        sheet.getRow(j).createCell(8).setCellValue(workHurt1);
                        sheet.getRow(j).createCell(15).setCellValue(workHurt2);

                        //生育保险：单位，0.7%，个人不用缴费；
                        double birth1 = 0.0;
                        double birth2 = salaryNum * 0.07;
                        sheet.getRow(j).createCell(9).setCellValue(birth1);
                        sheet.getRow(j).createCell(16).setCellValue(birth2);

                        //住房公积金缴纳比例有 8%、10%、12%三档，
                        double pro = Double.parseDouble(list.get(4));//公积金
                        double proMoney1 = salaryNum * pro;
                        double proMoney2 = salaryNum * pro;
                        sheet.getRow(j).createCell(10).setCellValue(proMoney1);
                        sheet.getRow(j).createCell(17).setCellValue(proMoney2);

                        //合计
                        double sum1 = pension1 + yiBao1 + loseWork1 + workHurt1 + birth1 + proMoney1;
                        double sum2 = pension2 + yiBao2 + loseWork2 + workHurt2 + birth2 + proMoney2;
                        sheet.getRow(j).createCell(11).setCellValue(sum1);
                        sheet.getRow(j).createCell(18).setCellValue(sum2);

                        //“应发工资+企业缴纳五险一金”的结果填入“企业人员月度工资成本支付表.xlsx“的“企业支出成本”一栏。
                        double payableSalary = sheet.getRow(j).getCell(20).getNumericCellValue();
                        double outlay = payableSalary + sum2;
                        sheet.getRow(j).createCell(22).setCellValue(outlay);

                        //根据“个税累计预扣法”定义及《个人所得税预扣率表》得出应纳税额
                        double taxNum = 0.0;
                        if(payableSalary < 5000)
                        {
                            sheet.getRow(j).createCell(19).setCellValue(taxNum);
                        }else
                        {
                            //应纳税所得额=应发工资-5000（累计减除免征额）-（累计五险一金个人部分扣除）；
                            double taxMoney = payableSalary - 5000 - sum1;
                            //如果应纳税所得额小于0，则应纳税额为0
                            if(taxMoney < 0)
                            {
                                sheet.getRow(j).createCell(19).setCellValue(taxNum);
                            }
                            else if(taxMoney <= 36000)
                            {
                                taxNum = taxMoney * 0.03;
                                sheet.getRow(j).createCell(19).setCellValue(taxNum);
                            }
                            else if(taxMoney > 36000 && taxMoney < 144000)
                            {
                                taxNum = 36000 * 0.03 + (taxMoney - 36000) * 0.1;
                                sheet.getRow(j).createCell(19).setCellValue(taxNum);
                            }
                        }

                        //“应发工资-五险一金个人缴纳部分-个税”的结果填入“企业人员月度工资成本支付表.xlsx“的“实发工资”一栏。
                        double realSalary = payableSalary - sum1 - taxNum;
                        sheet.getRow(j).createCell(21).setCellValue(realSalary);


                    }

                }
            }
        }
        workbook.write(new FileOutputStream(destFile));
        workbook.close();
    }
}

2.1.3 文件处理类

package zhanglong;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedList;

public class ExcelHand {
    public static void main(String[] args) throws IOException, InvalidFormatException {
        File inputFile = new File("C:\\Users\\asus\\Desktop\\22071103333016575248063653\\workspace\\data\\10月");
        File inputFile1 = new File("C:\\Users\\asus\\Desktop\\22071103333016575248063653\\workspace\\data\\员工五险一金申报表.xlsx");
        File copyFile = new File("C:\\Users\\asus\\Desktop\\22071103333016575248063653\\workspace\\企业员工月度工资成本支付表 - 副本.xlsx");
        File destFile = new File("C:\\Users\\asus\\Desktop\\22071103333016575248063653\\workspace\\企业员工月度工资成本支付表.xlsx");

        ExcelRead excelRead = new ExcelRead();

        ArrayList<LinkedList<String>> arr = excelRead.readExcel(inputFile);
        ArrayList<LinkedList<String>> arr1 = excelRead.readExcel(inputFile1);
        ExcelWrite.writeExcel(copyFile,destFile,arr,"薪酬表");
        ExcelWrite.writeExcel(copyFile,destFile,arr1,"申报表");
    }
}

2.1.4 成果展示