package fi.em.formPlugin;
import kd.bos.bill.AbstractBillPlugIn;
import kd.bos.cache.CacheFactory;
import kd.bos.cache.TempFileCache;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.exception.KDBizException;
import kd.bos.form.control.AttachmentPanel;
import kd.bos.form.control.Toolbar;
import kd.bos.form.control.events.UploadEvent;
import kd.bos.form.control.events.UploadListener;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
/**
* @author cjz
* @date 2024/8/20 16:18
* @description:薪酬计提单上传附件，并将附件中的数据写入分录
*/
public class SalaryFileUploadEditPlugin extends AbstractBillPlugIn implements UploadListener {
private static String bos_org = "bos_org"; //业务单元组织标识
private static String bos_adminorg="bos_adminorg";//行政组织标识
//给附件面板添加监听
@Override
public void registerListener(EventObject e) {
super.registerListener(e);
//按钮面板
Toolbar toolbar = this.getControl("tbmain");
toolbar.addUploadListener(this);
//附件面板
AttachmentPanel attachmentPanel = getControl("attachmentpanel");
attachmentPanel.addUploadListener(this);
}
@Override
public void afterUpload(UploadEvent evt) {
//获取当前操作单据
DynamicObject dynamicObject=this.getModel().getDataEntity(true);
String nckd_entrytype=dynamicObject.getString("nckd_entrytype");
//解析附件面板
AttachmentPanel attachmentPanel=this.getControl("attachmentpanel");
List<Map<String,Object>> attachmentData=attachmentPanel.getAttachmentData();
//获取最新上传的文件路径
String fileurl = (String) attachmentData.get(attachmentData.size()-1).get("url");
//获取文件缓存
TempFileCache cache = CacheFactory.getCommonCacheFactory().getTempFileCache();
//从缓存中拿到输入流
InputStream inputStream = cache.getInputStream(fileurl);
String billtype="";
//根据分录类型获取对应分录标识
if ("A".equals(nckd_entrytype)) {
billtype="nckd_staffentry";
readExcelByRowOrCell(4,44,inputStream,billtype);
}else if ("B".equals(nckd_entrytype)) {
billtype="nckd_earlyretiredentry";
readExcelByRowOrCell(4,31,inputStream,billtype);
} else if ("C".equals(nckd_entrytype)) {
billtype="nckd_retireentry";
readExcelByRowOrCell(4,15,inputStream,billtype);
} else if ("D".equals(nckd_entrytype)) {
billtype="nckd_salaryentry";
readExcelByRowOrCell(4,78,inputStream,billtype);
} else if (null==nckd_entrytype) {
throw new KDBizException("请选择分录类型");
}
}
/**
* @line execel行数
* @column excel列数
* @inputStream 文件流
* @nckd_entrytype 分录类型
* @description:读取上传的excel，并将数据写入对应分录中
*/
public void readExcelByRowOrCell(int line,int column,InputStream inputStream,String billtype)
{
try {
Workbook wb = WorkbookFactory.create(inputStream);
Sheet filesheet=wb.getSheetAt(0);//默认取第一个工作表
//表中存的数据
List<Row> rowList=new ArrayList<>();
//表头字段
List<Row> headList=new ArrayList<>();
//人力薪酬和薪酬明细等excel分开处理
if (billtype.equals("nckd_salaryentry")) {
headList.add(filesheet.getRow(line-3));
}else {
headList.add(filesheet.getRow(line-2));
}
//读取每行的数据存入list中
for (int i=line;i<=filesheet.getLastRowNum();i++) {
//获取每一行
Row row = filesheet.getRow(i);
if (null==row.getCell(1)) {
break;
}
//根据字段行循环，没有值的单元格则赋值，防止空指针
for(int j=1;j<column;j++){
Cell cell=row.getCell(j);
if (null==cell) {
row.createCell(j);
}
}
rowList.add(row);
}
//excel数据校验
dataVerify(headList,rowList,billtype);
//分录赋值
createNewRow(rowList,billtype);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* @rowList 读取的excel数据
* @nckd_entrytype 分录标识
* @description:根据分录类型不同，赋值分录
*/
public void createNewRow(List<Row> rowList,String nckd_entry){
//薪酬明细（一般员工）分录字段标识
List<String> nckd_generalemployees =Arrays.asList("nckd_se_institution","nckd_se_empname","nckd_se_empnumber"
,"nckd_se_idnumber","nckd_se_banknumber","nckd_se_unit","nckd_se_org","nckd_se_department","nckd_se_allbasicsry"
,"nckd_se_basicsry","nckd_se_acmsry","nckd_se_reacmsry","nckd_se_bsnrwd","nckd_se_otreward","nckd_se_cmcsbd"
,"nckd_se_otherpysry","nckd_se_clearrwd","nckd_se_awareward","nckd_se_otherdevreward","nckd_se_coldsbad"
,"nckd_se_warmsbad","nckd_se_nursabd","nckd_se_otherallowance","nckd_se_salarypayable","nckd_se_per_endins"
,"nckd_se_per_hopins","nckd_se_per_bhopins","nckd_se_per_ljins","nckd_se_per_hosins","nckd_se_per_cmymey"
,"nckd_se_unionmey","nckd_se_tax","nckd_se_otherreduce","nckd_se_desalary","nckd_se_netsalary","nckd_se_cpy_endins"
,"nckd_se_cpy_hopins","nckd_se_cpy_bhopins","nckd_se_cpy_ljins","nckd_se_cpy_birthins","nckd_se_cpy_dmgins","nckd_se_cpy_hosins"
,"nckd_se_cpy_cmymey");
//薪酬明细（内退）分录字段标识
List<String> nckd_earlyretired=Arrays.asList("nckd_ee_institution","nckd_ee_emptype","nckd_ee_empnumber","nckd_ee_empname"
,"nckd_ee_org","nckd_ee_department","nckd_ee_idnumber","nckd_ee_cost","nckd_ee_allbasicsry","nckd_ee_allowance"
,"nckd_ee_otherallowance","nckd_ee_per_endins","nckd_ee_per_ljins","nckd_ee_per_hopins","nckd_ee_per_bhopins"
,"nckd_ee_per_hosins","nckd_ee_per_cmymey","nckd_ee_unionmey","nckd_ee_tax","nckd_ee_otherreduce","nckd_ee_desalary"
,"nckd_ee_netsalary","nckd_ee_cpy_endins","nckd_ee_cpy_hopins","nckd_ee_cpy_bhopins","nckd_ee_cpy_ljins","nckd_ee_cpy_birthins"
,"nckd_ee_cpy_dmgins","nckd_ee_cpy_hosins","nckd_ee_cpy_cmymey");
//薪酬明细（退休）分录字段标识
List<String> nckd_retired=Arrays.asList("nckd_re_institution","nckd_re_emptype","nckd_re_empnumber","nckd_re_empname","nckd_re_org"
,"nckd_re_department","nckd_re_idnumber","nckd_re_cost","nckd_re_livallowance","nckd_re_otherallowance","nckd_re_reward"
,"nckd_re_salarypayable","nckd_re_desalary","nckd_re_netsalary");
//人力薪酬分录标识
List<String> nckd_pay=Arrays.asList("nckd_sae_institution","nckd_sae_empname","nckd_sae_jobid","nckd_sae_idnumber","nckd_sae_company"
,"nckd_sae_institutions","nckd_sae_department","nckd_sae_levelsry","nckd_sae_dutiessry","nckd_sae_updutiessry","nckd_sae_workyearsry"
,"nckd_sae_depyear","nckd_sae_eduasry","nckd_sae_transry","nckd_sae_jobsry","nckd_sae_basicsry","nckd_sae_allbasicsry","nckd_sae_acmsry"
,"nckd_sae_mountsry","nckd_sae_reacmsry","nckd_sae_balyearsry","nckd_sae_blastyearsry","nckd_sae_allsry","nckd_sae_bsnrwd","nckd_sae_cpmrwd"
,"nckd_sae_psrwd","nckd_sae_comrwd","nckd_sae_cardrwd","nckd_sae_intwrd","nckd_sae_clearrwd","nckd_sae_allrwd","nckd_sae_retainsry"
,"nckd_sae_reward","nckd_sae_otreward","nckd_sae_cmcsbd","nckd_sae_trfsbd","nckd_sae_housesbd","nckd_sae_warmsbd","nckd_sae_edusbd"
,"nckd_sae_trafficsbd","nckd_sae_othersbd","nckd_sae_reduce","nckd_sae_othersry","nckd_sae_sickreduce","nckd_sae_sickhopreduce"
,"nckd_sae_eventreduce","nckd_sae_afwreduce","nckd_sae_latereduce","nckd_sae_cwasry","nckd_sae_cwareduce","nckd_sae_dgreduce"
,"nckd_sae_dcpreduce","nckd_sae_badreduce","nckd_sae_otherreduce","nckd_sae_allreduce","nckd_sae_lastsry","nckd_sae_allpay"
,"nckd_sae_per_endins","nckd_sae_per_ljins","nckd_sae_per_hopins","nckd_sae_per_bhopins","nckd_sae_per_hosins","nckd_sae_per_cmymey"
,"nckd_sae_per_ins","nckd_sae_unionmey","nckd_sae_tax","nckd_sae_allamount","nckd_sae_cash","nckd_sae_wages","nckd_sae_cpy_endins"
,"nckd_sae_cpy_hopins","nckd_sae_cpy_bhopins","nckd_sae_cpy_birthins","nckd_sae_cpy_ljins","nckd_sae_cpy_dmgins","nckd_sae_cpy_hosins"
,"nckd_sae_cpy_cmymey");
//将excel数据存入薪酬明细（一般员工）分录
if(nckd_entry.equals("nckd_staffentry")) {
//先清空分录
this.getModel().deleteEntryData("nckd_staffentry");
for (int i=0;i< rowList.size();i++) {
//新增动态单据分录
int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
//发薪机构
if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
//获取发薪机构
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
this.getModel().setValue(nckd_generalemployees.get(0),entrycostdept,rowIndex);
}
//员工名称
if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
this.getModel().setValue(nckd_generalemployees.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
}
//员工工号
if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
this.getModel().setValue(nckd_generalemployees.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
}
//证件号
if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
this.getModel().setValue(nckd_generalemployees.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
}
//银行账号
if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
this.getModel().setValue(nckd_generalemployees.get(4),getCellValue(rowList.get(i).getCell(5)),rowIndex);
}
//所在单位
if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()){
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
//获取所在单位
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_generalemployees.get(5),entrycostdept,rowIndex);
}
//一级机构
if (!getCellValue(rowList.get(i).getCell(7)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(7)));
//获取部门
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id,entrycostdept",new QFilter[]{filter});
this.getModel().setValue(nckd_generalemployees.get(6),entrycostdept,rowIndex);
}
//所在部门
if (!getCellValue(rowList.get(i).getCell(8)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
//获取部门
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_generalemployees.get(7),entrycostdept,rowIndex);
}
//金额类型字段赋值
for (int j=8;j<nckd_generalemployees.size();j++) {
Cell cell=rowList.get(i).getCell(j+1);
//空值则跳过
if (!Objects.equals(getCellValue(cell), "")) {
//金额字段转化为BigDecimal赋值进分录中
String string=getCellValue(cell);
BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
this.getModel().setValue(nckd_generalemployees.get(j),bigDecimal,rowIndex);
}
}
//刷新分录
this.getView().updateView("nckd_staffentry");
}
}
//薪酬明细（内退）分录
if (nckd_entry.equals("nckd_earlyretiredentry")) {
this.getModel().deleteEntryData("nckd_earlyretiredentry");
for (int i=0;i<rowList.size();i++) {
//新增动态单据分录
int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
//发薪机构
if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
//获取发薪机构
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
this.getModel().setValue(nckd_earlyretired.get(0),entrycostdept,rowIndex);
}
//员工类别
if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
this.getModel().setValue(nckd_earlyretired.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
}
//员工编号
if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
this.getModel().setValue(nckd_earlyretired.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
}
//姓名
if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
this.getModel().setValue(nckd_earlyretired.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
}
//所在机构
if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(5)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_earlyretired.get(4),entrycostdept,rowIndex);
}
//所在部门
if (!getCellValue(rowList.get(i).getCell(6)).isEmpty())
{
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_earlyretired.get(5),entrycostdept,rowIndex);
}
//身份证号
if (!getCellValue(rowList.get(i).getCell(7)).isEmpty()){
this.getModel().setValue(nckd_earlyretired.get(6),getCellValue(rowList.get(i).getCell(6)),rowIndex);
}
//成本归结单元
if (!getCellValue(rowList.get(i).getCell(8)).isEmpty())
{
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
this.getModel().setValue(nckd_earlyretired.get(7),entrycostdept,rowIndex);
}
//金额类型字段赋值
for (int j=8;j<nckd_earlyretired.size();j++) {
Cell cell=rowList.get(i).getCell(j+1);
//空值则跳过
if (!Objects.equals(getCellValue(cell), "")) {
//金额字段转化为BigDecimal赋值进分录中
String string=getCellValue(cell);
BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
this.getModel().setValue(nckd_earlyretired.get(j),bigDecimal,rowIndex);
}
}
}
}
//薪酬明细（退休）分录
if(nckd_entry.equals("nckd_retireentry")) {
this.getModel().deleteEntryData("nckd_retireentry");
for (int i=0;i<rowList.size();i++) {
//新增动态单据分录
int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
//发薪机构
if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
//获取发薪机构
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
this.getModel().setValue(nckd_retired.get(0),entrycostdept,rowIndex);
}
//员工类别
if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
this.getModel().setValue(nckd_retired.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
}
//员工编号
if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
this.getModel().setValue(nckd_retired.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
}
//姓名
if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
this.getModel().setValue(nckd_retired.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
}
//所在机构
if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(5)));
//获取部门
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_retired.get(4),entrycostdept,rowIndex);
}
//所在部门
if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_retired.get(5),entrycostdept,rowIndex);
}
//身份证号
if (!getCellValue(rowList.get(i).getCell(7)).isEmpty()){
this.getModel().setValue(nckd_retired.get(6),getCellValue(rowList.get(i).getCell(6)),rowIndex);
}
//成本归结单元
if (!getCellValue(rowList.get(i).getCell(8)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
this.getModel().setValue(nckd_retired.get(7),entrycostdept,rowIndex);
}
//金额类型字段赋值
for (int j=8;j<nckd_retired.size();j++) {
Cell cell=rowList.get(i).getCell(j+1);
//空值则跳过
if (!Objects.equals(getCellValue(cell), "")) {
//金额字段转化为BigDecimal赋值进分录中
String string=getCellValue(cell);
BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
this.getModel().setValue(nckd_retired.get(j),bigDecimal,rowIndex);
}
}
}
}
//人力薪酬分录
if(nckd_entry.equals("nckd_salaryentry")) {
this.getModel().deleteEntryData("nckd_salaryentry");
for (int i=0;i<rowList.size();i++) {
//新增动态单据分录
int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
//发薪机构
if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
//获取发薪机构
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
this.getModel().setValue(nckd_pay.get(0),entrycostdept,rowIndex);
}
//姓名
if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
this.getModel().setValue(nckd_pay.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
}
//工号
if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
this.getModel().setValue(nckd_pay.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
}
//证件号
if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
this.getModel().setValue(nckd_pay.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
}
//所在单位
if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(5)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_pay.get(4),entrycostdept,rowIndex);
}
//一级机构
if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_pay.get(5),entrycostdept,rowIndex);
}
//所在部门
if (!getCellValue(rowList.get(i).getCell(7)).isEmpty()) {
QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(7)));
DynamicObject entrycostdept= BusinessDataServiceHelper
.loadSingle(bos_adminorg,"id",new QFilter[]{filter});
this.getModel().setValue(nckd_pay.get(6),entrycostdept,rowIndex);
}
//金额类型字段赋值
for (int j=7;j<nckd_pay.size();j++) {
Cell cell=rowList.get(i).getCell(j+1);
//空值则跳过
if (!Objects.equals(getCellValue(cell), "")) {
//金额字段转化为BigDecimal赋值进分录中
String string=getCellValue(cell);
BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
this.getModel().setValue(nckd_pay.get(j),bigDecimal,rowIndex);
}
}
}
}
}
/**
* @headList 读取的excel表头数据
* @rowList 读取excel的表数据
* @billtype 分录标识
* @description：校验excel数据是否合法，传入的excel是否为正确的格式
*/
public void dataVerify(List<Row> headList,List<Row> rowList,String billtype) {
//获取对应单据的字段名
String flag;
//薪酬明细（一般员工）
if (billtype.equals("nckd_staffentry")) {
//获取单据的字段名
flag=headList.get(0).getCell(9).getStringCellValue();
if (!flag.equals("基本工资")) {
throw new KDBizException("上传的文件格式有误，请检查！");
}
for (int i=0;i<rowList.size();i++) {
//校验金额字段
for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
Cell cell=rowList.get(i).getCell(j);
//单元格不为空
if (!Objects.equals(getCellValue(cell), "")) {
if (cell.getCellType()!=CellType.NUMERIC){
//输入金额字段的类型不为数字则提示
throw new KDBizException("第"+(i+1)+"行第"+j+"列的数据:"+headList.get(i).getCell(j).getStringCellValue()+"字段有误，请检查！");
}
}
}
}
}
//薪酬明细（内退）
if (billtype.equals("nckd_earlyretiredentry")) {
flag=headList.get(0).getCell(9).getStringCellValue();
if (!flag.equals("内退员工基本工资")) {
throw new KDBizException("上传的文件格式有误，请检查！");
}
for (int i=0;i<rowList.size();i++) {
//校验金额字段
for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
Cell cell=rowList.get(i).getCell(j);
//单元格不为空
if (!Objects.equals(getCellValue(cell), "")) {
if (cell.getCellType()!=CellType.NUMERIC){
//输入金额字段的类型不为数字则提示
throw new KDBizException("第"+(i+1)+"行第"+j+"列的数据:"+headList.get(i).getCell(j).getStringCellValue()+"字段有误，请检查！");
}
}
}
}
}
//薪酬明细（退休）
if (billtype.equals("nckd_retireentry")) {
flag=headList.get(0).getCell(9).getStringCellValue();
if (!flag.equals("退休员工生活补贴")) {
throw new KDBizException("上传的文件格式有误，请检查！");
}
for (int i=0;i<rowList.size();i++) {
//校验金额字段
for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
Cell cell=rowList.get(i).getCell(j);
//单元格不为空
if (!Objects.equals(getCellValue(cell), "")) {
if (cell.getCellType()!=CellType.NUMERIC){
//输入金额字段的类型不为数字则提示
throw new KDBizException("第"+(i+1)+"行第"+j+"列的数据:"+headList.get(i).getCell(j).getStringCellValue()+"字段有误，请检查！");
}
}
}
}
}
//人力薪酬
if (billtype.equals("nckd_salaryentry")) {
flag=headList.get(0).getCell(9).getStringCellValue();
if (!flag.equals("职务工资")) {
throw new KDBizException("上传的文件格式有误，请检查！");
}
for (int i=0;i<rowList.size();i++) {
//校验金额字段
for (int j=8;j<rowList.get(i).getLastCellNum();j++) {
Cell cell=rowList.get(i).getCell(j);
//单元格不为空
if (!Objects.equals(getCellValue(cell), "")) {
if (cell.getCellType()!=CellType.NUMERIC){
//输入金额字段的类型不为数字则提示
throw new KDBizException("第"+(i+1)+"行第"+(j+1)+"列的数据:"+headList.get(i).getCell(j+1).getStringCellValue()+"字段有误，请检查！");
}
}
}
}
}
}
/**
* 对Excel的各个单元格的格式进行判断并转换
*/
public String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 判断数据的类型
switch (cell.getCellType()) {
case NUMERIC:
//读取数字将不必要的小数点去掉
BigDecimal bigDecimal=new BigDecimal(Double.toString(cell.getNumericCellValue()));
cellValue=bigDecimal.stripTrailingZeros().toPlainString();
break;
case STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue()).trim();
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "未知类型";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}