如何使用Java + React計算個人所得稅？
2023-09-06 18:02:24
前言
在報表資料處理中，Excel公式擁有強大而多樣的功能，廣泛應用於各個業務領域。無論是投資收益計算、財務報表編制還是保險收益估算，Excel公式都扮演著不可或缺的角色。傳統的做法是直接依賴Excel來實現複雜的業務邏輯，並生成相應的Excel檔案。因此只需在預設位置輸入相應引數，Excel公式即可被啟用，迅速計算並呈現結果。正因如此，在這類場景中，企業積累了大量用於計算的Excel檔案，它們已經成為了無價的財富。
然而，傳統的Excel檔案方式存在難以管理和資料不安全的缺點。為了解決這些問題，可以採用B/S架構+Excel元件庫的方式。
本文將以個人所得稅的計算為例，使用React+Spring Boot+GcExcel來實現。首先準備好Excel檔案，按照國家稅務總局提供的個稅計算頁面進行建立。
個人所得稅的收入型別有8種：
- 工資薪金所得
- 年終獎所得
- 勞務報酬所得
- 個體工商戶、生產經營所得
- 酬勞所得
- 偶然所得
- 利息、股息、紅利所得
- 財產轉讓所得
其中，工資薪金所得最為複雜，包括社會保險和專項扣除。每種型別的計稅方式都不同，為了便於理解，我們為每個型別建立了一個工作表進行計算。
以下是準備好的Excel檔案，其中藍色部分為需要輸入引數的單元格，其他單元格將自動計算。
完成準備工作後，下面開始前後端工程的搭建。
實踐
前端 React
建立React工程
新建一個資料夾，如TaxCalculator，進入資料夾，在資源管理器的位址列裡輸入cmd，然後回車，開啟命令列視窗。使用下面的程式碼建立名為client-app的react app。
npx create-react-app salary-client
進入剛建立的salary-client資料夾，使用IDE，比如VisualStudio Code開啟資料夾。
介面部分
個人所得稅涉及的收入型別一共有8種，其中（「酬勞所得」，「偶然所得」，「利息、股息、紅利所得」，「財產轉讓所得」）四種的計算方式接近，UI佈局相似，藉助React的component特性，最終需要提供5種表單介面。
如下圖所示：
為了讓UI看起來更好看一些，可以先引入一個UI框架，這裡我們使用了MUI。
npm install @mui/material @emotion/react @emotion/styled
首先，更新Src/App.js的程式碼，其中新增了DarkMode的Theme, 程式碼如下：
import './App.css';
import { ThemeProvider } from '@emotion/react';
import { createTheme } from '@mui/material';
import { FormContainer } from './Component/FormContainer';
const darkTheme = createTheme({
palette: {
mode: 'dark',
},
});
function App() {
return (
<ThemeProvider theme={darkTheme}>
<div className="App-header">
<h2>個人所得稅計算器</h2>
<FormContainer></FormContainer>
</div>
</ThemeProvider>
);
}
export default App;
可以看到，App.js中參照了FormContainer，下來新增 ./Component/FormContainer.js。
FormContainer主要是提供一個Selector，讓使用者選擇收入型別，根據選擇的型別渲染不同的元件。
import React, { useState } from 'react';
import { SalaryIncome } from "./SalaryIncome"
import { NativeSelect, FormControl } from '@mui/material';
import { BounsIncome } from './BounsIncome';
import { CommercialIncome } from './CommercialIncome';
import { LaborIncome } from './LaborIncome';
import { OtherIncome } from './OtherIncome';
export const FormContainer = () => {
const [calcType, setCalcType] = useState("工資薪金所得");
const GetIncomeControl = () => {
switch (calcType) {
case "工資薪金所得":
return <SalaryIncome calcType={calcType}></SalaryIncome>;
case "年終獎所得":
return <BounsIncome calcType={calcType}></BounsIncome>;
case "勞務報酬所得":
return <LaborIncome calcType={calcType}></LaborIncome>;
case "個體工商戶、生產經營所得":
return <CommercialIncome calcType={calcType}></CommercialIncome>;
default:
return <OtherIncome calcType={calcType}></OtherIncome>;
}
}
return (
<div style={{ width: "60vw", marginTop: "5vh" }}>
<FormControl fullWidth sx={{ marginBottom: 2 }}>
<NativeSelect labelId="demo-simple-select-label" id="demo-simple-select"
value={calcType} label="型別" onChange={e => setCalcType(e.target.value)} >
<option value="工資薪金所得">工資薪金所得</option>
<option value="年終獎所得">年終獎所得</option>
<option Item value="勞務報酬所得">勞務報酬所得</option>
<option value="個體工商戶、生產經營所得">個體工商戶、生產經營所得</option>
<option value="酬勞所得">酬勞所得</option>
<option value="偶然所得">偶然所得</option>
<option value="利息、股息、紅利所得">利息、股息、紅利所得</option>
</NativeSelect>
</FormControl>
{GetIncomeControl()}
</div>);
}
例如：<SalaryIncome calcType={calcType}></SalaryIncome>; 同時會將calcType傳遞進去。
接下來，分別建立幾個xxxIncome元件。
1.工資薪金所得 SalaryIncome.js
import React, { useState } from 'react';
import { TextField, Button, Stack } from '@mui/material';
import axios from 'axios';
export const SalaryIncome = (props) => {
const [income, setIncome] = useState("");
const [insurance, setInsurance] = useState("");
const [childEdu, setChildEdu] = useState("");
const [selfEdu, setSelfEdu] = useState("");
const [treatment, setTreatment] = useState("");
const [loans, setLoans] = useState("");
const [rent, setRent] = useState("");
const [elder, setElder] = useState("");
const [taxableIncome, setTaxableIncome] = useState("");
const [taxRate, setTaxRate] = useState("");
const [deduction, setDeduction] = useState("");
const [tax, setTax] = useState("");
const [takeHomeSalary, setTakeHomeSalary] = useState("");
async function calculateTax(event) {
event.preventDefault();
let res = await axios.post("api/calcPersonTax", {
calcType: props.calcType,
income: income,
insurance: insurance,
childEdu: childEdu,
selfEdu: selfEdu,
treatment: treatment,
loans: loans,
rent: rent,
elder: elder,
});
if (res != null) {
let data = res.data;
setTaxableIncome(data.taxableIncome);
setTaxRate(data.taxRate);
setDeduction(data.deduction);
setTax(data.tax);
setTakeHomeSalary(data.takeHomeSalary);
}
}
function reset(event) {
event.preventDefault();
setIncome("");
setInsurance("");
setChildEdu("");
setSelfEdu("");
setTreatment("");
setLoans("");
setRent("");
setElder("");
setTaxableIncome("");
setTaxRate("");
setDeduction("");
setTax("");
setTakeHomeSalary("");
}
return (
<div>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='primary'
label="稅前工資" onChange={e => setIncome(e.target.value)}
value={income} fullWidth required size="small"/>
<TextField type="text" variant='outlined' color='secondary'
label="社會保險/公積金" onChange={e => setInsurance(e.target.value)}
value={insurance} fullWidth size="small"/>
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary'
label="子女教育專項扣除" onChange={e => setChildEdu(e.target.value)}
value={childEdu} fullWidth size="small"/>
<TextField type="text" variant='outlined' color='secondary'
label="繼續教育專項扣除" onChange={e => setSelfEdu(e.target.value)}
value={selfEdu} fullWidth size="small"/>
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary'
label="大病醫療專項扣除" onChange={e => setTreatment(e.target.value)}
value={treatment} fullWidth size="small"/>
<TextField type="text" variant='outlined' color='secondary'
label="住房貸款利息專項扣除" onChange={e => setLoans(e.target.value)}
value={loans} fullWidth size="small"/>
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary'
label="住房租金專項扣除" onChange={e => setRent(e.target.value)}
value={rent} fullWidth size="small"/>
<TextField type="text" variant='outlined' color='secondary'
label="贍養老人專項扣除" onChange={e => setElder(e.target.value)}
value={elder} fullWidth size="small"/>
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary'
label="起徵點" value="5000 元/月" fullWidth disabled size="small"/>
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<Button variant="outlined" color="primary" onClick={calculateTax} fullWidth size="large">計算</Button>
<Button variant="outlined" color="secondary" onClick={reset} fullWidth size="large">重置</Button>
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary'
label="應納稅所得額" value={taxableIncome} fullWidth disabled size="small"/>
<TextField type="text" variant='outlined' color='secondary'
label="稅率" value={taxRate} fullWidth disabled size="small"/>
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary'
label="速算扣除數" value={deduction} fullWidth disabled size="small"/>
<TextField type="text" variant='outlined' color='secondary'
label="應納稅額" value={tax} fullWidth disabled size="small"/>
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary'
label="稅後工資" value={takeHomeSalary} fullWidth disabled size="small"/>
</Stack>
</div>
)
}
2.年終獎金所得 BounsIncome.js
import React, { useState } from 'react';
import { TextField, Button, Stack } from '@mui/material';
import axios from 'axios';
export const BounsIncome = (props) => {
const [income, setIncome] = useState("");
const [taxableIncome, setTaxableIncome] = useState("");
const [taxRate, setTaxRate] = useState("");
const [deduction, setDeduction] = useState("");
const [monthlyWage, setMonthlyWage] = useState("");
const [tax, setTax] = useState("");
const [takeHomeSalary, setTakeHomeSalary] = useState("");
async function calculateTax(event) {
event.preventDefault();
let res = await axios.post("api/calcPersonTax", {
calcType: props.calcType,
income: income,
});
if (res != null) {
let data = res.data;
setTaxableIncome(data.taxableIncome);
setTaxRate(data.taxRate);
setDeduction(data.deduction);
setMonthlyWage(data.monthlyWage);
setTax(data.tax);
setTakeHomeSalary(data.takeHomeSalary);
}
}
function reset(event) {
event.preventDefault();
setIncome("");
setTaxableIncome("");
setTaxRate("");
setDeduction("");
setMonthlyWage("");
setTax("");
setTakeHomeSalary("");
}
return (
<div>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='primary' size="small"
label="稅前工資" onChange={e => setIncome(e.target.value)}
value={income} fullWidth required />
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<Button variant="outlined" color="primary" onClick={calculateTax} fullWidth size="large">計算</Button>
<Button variant="outlined" color="secondary" onClick={reset} fullWidth size="large">重置</Button>
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅所得額" value={taxableIncome} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅率" value={taxRate} fullWidth disabled />
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="速算扣除數" value={deduction} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="平均每月工資" value={monthlyWage} fullWidth disabled />
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅額" value={tax} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅後工資" value={takeHomeSalary} fullWidth disabled />
</Stack>
</div>
)
}
3.勞務報酬所得 LaborIncome.js
import React, { useState } from 'react';
import { TextField, Button, Stack } from '@mui/material';
import axios from 'axios';
export const LaborIncome = (props) => {
const [income, setIncome] = useState("");
const [taxableIncome, setTaxableIncome] = useState("");
const [taxRate, setTaxRate] = useState("");
const [deduction, setDeduction] = useState("");
const [nonTaxablePart, setNonTaxablePart] = useState("");
const [tax, setTax] = useState("");
const [takeHomeSalary, setTakeHomeSalary] = useState("");
async function calculateTax(event) {
event.preventDefault();
let res = await axios.post("api/calcPersonTax", {
calcType: props.calcType,
income: income,
});
if (res != null) {
let data = res.data;
setTaxableIncome(data.taxableIncome);
setTaxRate(data.taxRate);
setDeduction(data.deduction);
setNonTaxablePart(data.nonTaxablePart);
setTax(data.tax);
setTakeHomeSalary(data.takeHomeSalary);
}
}
function reset(event) {
event.preventDefault();
setIncome("");
setTaxableIncome("");
setTaxRate("");
setDeduction("");
setNonTaxablePart("");
setTax("");
setTakeHomeSalary("");
}
return (
<div>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='primary' size="small"
label="稅前工資" onChange={e => setIncome(e.target.value)}
value={income} fullWidth required />
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<Button variant="outlined" color="primary" onClick={calculateTax} fullWidth size="large">計算</Button>
<Button variant="outlined" color="secondary" onClick={reset} fullWidth size="large">重置</Button>
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅所得額" value={taxableIncome} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅率" value={taxRate} fullWidth disabled />
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="速算扣除數" value={deduction} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="減除費用" value={nonTaxablePart} fullWidth disabled />
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅額" value={tax} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅後工資" value={takeHomeSalary} fullWidth disabled />
</Stack>
</div>
)
}
4.個體工商戶、生產經營所得 CommercialIncome.js
import React, { useState } from 'react';
import { TextField, Button, Stack } from '@mui/material';
import axios from 'axios';
export const CommercialIncome = (props) => {
const [income, setIncome] = useState("");
const [taxableIncome, setTaxableIncome] = useState("");
const [taxRate, setTaxRate] = useState("");
const [deduction, setDeduction] = useState("");
const [tax, setTax] = useState("");
const [takeHomeSalary, setTakeHomeSalary] = useState("");
async function calculateTax(event) {
event.preventDefault();
let res = await axios.post("api/calcPersonTax", {
calcType: props.calcType,
income: income,
});
if (res != null) {
let data = res.data;
setTaxableIncome(data.taxableIncome);
setTaxRate(data.taxRate);
setDeduction(data.deduction);
setTax(data.tax);
setTakeHomeSalary(data.takeHomeSalary);
}
}
function reset(event) {
event.preventDefault();
setIncome("");
setTaxableIncome("");
setTaxRate("");
setDeduction("");
setTax("");
setTakeHomeSalary("");
}
return (
<div>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='primary' size="small"
label="稅前工資" onChange={e => setIncome(e.target.value)}
value={income} fullWidth required />
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<Button variant="outlined" color="primary" onClick={calculateTax} fullWidth size="large">計算</Button>
<Button variant="outlined" color="secondary" onClick={reset} fullWidth size="large">重置</Button>
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅所得額" value={taxableIncome} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅率" value={taxRate} fullWidth disabled />
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="速算扣除數" value={deduction} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅額" value={tax} fullWidth disabled />
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅後工資" value={takeHomeSalary} fullWidth disabled />
</Stack>
</div>
)
}
5.餘下四種型別 OtherIncome.js
import React, { useState } from 'react';
import { TextField, Button, Stack } from '@mui/material';
import axios from 'axios';
export const OtherIncome = (props) => {
const [income, setIncome] = useState("");
const [taxableIncome, setTaxableIncome] = useState("");
const [taxRate, setTaxRate] = useState("");
const [tax, setTax] = useState("");
const [takeHomeSalary, setTakeHomeSalary] = useState("");
async function calculateTax(event) {
event.preventDefault();
let res = await axios.post("api/calcPersonTax", {
calcType: props.calcType,
income: income,
});
if (res != null) {
let data = res.data;
setTaxableIncome(data.taxableIncome);
setTaxRate(data.taxRate);
setTax(data.tax);
setTakeHomeSalary(data.takeHomeSalary);
}
}
function reset(event) {
event.preventDefault();
setIncome("");
setTaxableIncome("");
setTaxRate("");
setTax("");
setTakeHomeSalary("");
}
return (
<div>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='primary' size="small"
label={props.calcType} onChange={e => setIncome(e.target.value)}
value={income} fullWidth required />
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<Button variant="outlined" color="primary" onClick={calculateTax} fullWidth size="large">計算</Button>
<Button variant="outlined" color="secondary" onClick={reset} fullWidth size="large">重置</Button>
</Stack>
<hr></hr>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅所得額" value={taxableIncome} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅率" value={taxRate} fullWidth disabled />
</Stack>
<Stack spacing={2} direction="row" sx={{ marginBottom: 2 }}>
<TextField type="text" variant='outlined' color='secondary' size="small"
label="應納稅額" value={tax} fullWidth disabled />
<TextField type="text" variant='outlined' color='secondary' size="small"
label="稅後工資" value={takeHomeSalary} fullWidth disabled />
</Stack>
</div>
)
}
此時，完成UI部分後，可以嘗試執行起來，效果如下：
//通過程式碼執行React app
npm start
可以試著填一些資料，但是當我們點選計算時會報錯，這是因為伺服器端還沒有準備好。
前端請求部分
熟悉Axios的同學可以跳過這部分，前面的程式碼裡，已經給出了Axois傳送請求的程式碼。
可以看到無論是哪一種型別的元件，請求都傳送到了相同的url（"api/calcPersonTax"），以SalaryIncome為例，程式碼如下：
async function calculateTax(event) {
event.preventDefault();
let res = await axios.post("api/calcPersonTax", {
calcType: props.calcType,
income: income,
insurance: insurance,
childEdu: childEdu,
selfEdu: selfEdu,
treatment: treatment,
loans: loans,
rent: rent,
elder: elder,
});
if (res != null) {
let data = res.data;
setTaxableIncome(data.taxableIncome);
setTaxRate(data.taxRate);
setDeduction(data.deduction);
setTax(data.tax);
setTakeHomeSalary(data.takeHomeSalary);
}
}
可以看到，整個請求變得非常簡單，主要是把state的值取出來，通過post請求傳送到伺服器端，然後根據返回值，把資料重新設給state，這樣就完成UI資料的更新了。
設定請求轉發中介軟體
我們在請求時存取的是相對地址，React本身有一個nodeJS，預設的埠是3000，而Spring Boot的預設埠是8080。前端直接存取會有跨域的問題，因此我們要做一個代理的設定。
在src資料夾下面新增檔案，名為setupProxy.js，程式碼如下：
const { createProxyMiddleware } = require('http-proxy-middleware');
module.exports = function(app) {
app.use(
'/api',
createProxyMiddleware({
target: 'http://localhost:8080',
changeOrigin: true,
})
);
};
伺服器端 Spring Boot
建立工程及新增依賴
使用IDEA建立一個Spring Boot工程，如果使用的是社群（community）版本，不能直接建立Spring Boot專案，那可以先建立一個空專案，idea建立project的過程，就跳過了，這裡我們以建立了一個gradle專案為例。
plugins {
id 'org.springframework.boot' version '3.0.0'
id 'io.spring.dependency-management' version '1.1.0'
id 'java'
id 'war'
}
group = 'org.example'
version = '1.0-SNAPSHOT'
repositories {
mavenCentral()
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'com.grapecity.documents:gcexcel:6.2.0'
implementation 'javax.json:javax.json-api:1.1.4'
providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat'
testImplementation('org.springframework.boot:spring-boot-starter-test')
}
test {
useJUnitPlatform()
}
在dependencies 中，我們除了依賴Spring Boot之外，還新增了GcExcel的依賴，後面匯出時會用到GcExcel，目前的版本是6.2.0。
新增API
在Application類上，新增屬性 @RequestMapping("/api").，並新增 calcPersonTax API。
@Spring BootApplication
@RestController
@RequestMapping("/api")
public class SalaryTaxCalculator {
public static void main(String[] args) {
SpringApplication.run(SalaryTaxCalculator.class, args);
}
@PostMapping("/calcPersonTax")
public CalcResult calcTax(@RequestBody CalcParameter par) {
Workbook workbook = new Workbook();
workbook.open(GetResourcePath());
return CalcInternal(workbook, par);
}
private String GetResourcePath(){
return Objects.requireNonNull(SalaryTaxCalculator.class.getClassLoader().getResource("PersonalTaxCalcEngine.xlsx")).getPath();
}
private CalcResult CalcInternal(Workbook workbook, CalcParameter par) {
//todo
}
}
可以看到在CalcInternal方法內，我們使用GcExcel，根據calcType來判斷使用哪一個sheet來進行計算。對不同Sheet只需要通過GcExcel設值，並從特定的格子裡取值即可。
同時，我們還需要建立兩個類，CalcParameter和CalcResult。CalcParameter用於從request中把post的data解析出來，CalcResult用於在response中返回的資料。
CalcParameter:
public class CalcParameter {
public String calcType;
public double income;
public double insurance;
public double childEdu;
public double selfEdu;
public double treatment;
public double loans;
public double rent;
public double elder;
}
CalcResult:
public class CalcResult {
public double taxableIncome;
public double taxRate;
public double deduction;
public double tax;
public double takeHomeSalary;
public double monthlyWage;
public double nonTaxablePart;
}
使用GcExcel完成公式計算
前面我們定義了 CalcInternal，在 CalcInternal 中，我們需要使用GcExcel來完成公式計算。
GcExcel的公式計算是自動完成的，我們使用workbook開啟Excel檔案後，只需要set相關的value。之後在取值時，GcExcel會自動計算響應公式的值。
private CalcResult CalcInternal(Workbook workbook, CalcParameter par) {
var result = new CalcResult();
var sheet = workbook.getWorksheets().get(par.calcType);
switch (par.calcType) {
case "工資薪金所得" -> {
sheet.getRange("B1").setValue(par.income);
sheet.getRange("D1").setValue(par.insurance);
sheet.getRange("B2").setValue(par.childEdu);
sheet.getRange("D2").setValue(par.selfEdu);
sheet.getRange("B3").setValue(par.treatment);
sheet.getRange("D3").setValue(par.loans);
sheet.getRange("B4").setValue(par.rent);
sheet.getRange("D4").setValue(par.elder);
result.taxableIncome = (double) sheet.getRange("B9").getValue();
result.taxRate = (double) sheet.getRange("D9").getValue();
result.deduction = (double) sheet.getRange("B10").getValue();
result.tax = (double) sheet.getRange("D10").getValue();
result.takeHomeSalary = (double) sheet.getRange("B11").getValue();
}
case "年終獎所得" -> {
sheet.getRange("B1").setValue(par.income);
result.taxableIncome = (double) sheet.getRange("B3").getValue();
result.taxRate = (double) sheet.getRange("D3").getValue();
result.deduction = (double) sheet.getRange("B4").getValue();
result.monthlyWage = (double) sheet.getRange("D4").getValue();
result.tax = (double) sheet.getRange("B5").getValue();
result.takeHomeSalary = (double) sheet.getRange("D5").getValue();
}
case "勞務報酬所得" -> {
sheet.getRange("B1").setValue(par.income);
result.taxableIncome = (double) sheet.getRange("B3").getValue();
result.taxRate = (double) sheet.getRange("D3").getValue();
result.deduction = (double) sheet.getRange("B4").getValue();
result.nonTaxablePart = (double) sheet.getRange("D4").getValue();
result.tax = (double) sheet.getRange("B5").getValue();
result.takeHomeSalary = (double) sheet.getRange("D5").getValue();
}
case "個體工商戶、生產經營所得" -> {
sheet.getRange("B1").setValue(par.income);
result.taxableIncome = (double) sheet.getRange("B3").getValue();
result.taxRate = (double) sheet.getRange("D3").getValue();
result.deduction = (double) sheet.getRange("B4").getValue();
result.tax = (double) sheet.getRange("D4").getValue();
result.takeHomeSalary = (double) sheet.getRange("B5").getValue();
}
default -> {
sheet.getRange("B1").setValue(par.income);
result.taxableIncome = (double) sheet.getRange("B3").getValue();
result.taxRate = (double) sheet.getRange("D3").getValue();
result.tax = (double) sheet.getRange("B4").getValue();
result.takeHomeSalary = (double) sheet.getRange("D4").getValue();
}
}
return result;
}
這樣就完成了伺服器端的程式碼。
最終效果
我們可以使用工資薪金所得試驗一下，可以看到資料被計算出來了。因為目的是為了分享伺服器端公式計算的方案，所以計算的結果是否正確，就不做細緻考慮。
總結
個稅計算的場景並不複雜，主要是通過Excel完成公式計算即可，在伺服器端使用GcExcel可以大幅度降低前後端的開發難度，系統的搭建過程可以完全不需要考慮計算的邏輯。
在實際的公式計算場景中，可能往往會比個稅計算的場景複雜，藉助GcExcel這樣Excel元件庫，可以很容易的把已有的Excel檔案遷移到線上，提高工作效率。
另外，本文中分享的程式碼並不是最符合實際工作中的要求，讀者還可以從以下角度去優化自己的程式碼。
- 收入型別可以抽成列舉，這樣維護和使用起來更容易。
- 目前每一個react元件裡的冗餘度還不低，還可以繼續抽象元件，避免重複寫程式碼。
- 在伺服器端，因為公式計算的邏輯是不會變的，在實際場景中，也有可能同一時間要載入複數個Excel檔案，可以考慮把workbook常駐記憶體，來提高效能。
擴充套件連結：