1.导出功能(仅表头)
package nc.ui.yhlypx.yhlypxfkd.ace.action;
import java.awt.event.ActionEvent;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import nc.ui.pubapp.uif2app.model.BillManageModel;
import nc.ui.pubapp.uif2app.view.ShowUpableBillForm;
import nc.ui.uif2.NCAction;
import nc.ui.uif2.ShowStatusBarMsgUtil;
import nc.ui.yhlypx.util.NullValueUtils;
import nc.ui.yhlypx.yhlypxfkd.ace.dialog.ExportFileDialog;
import nc.vo.pub.BusinessException;
import nc.vo.pub.SuperVOUtil;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDouble;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nc.vo.yhlypx.yhlypxfkd.AggYhlypxhtfkdVO;
import nc.vo.yhlypx.yhlypxfkd.YhlypxhtfkdVO;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @desc:放款单导出按钮(只导出表头)
* @author hh
* @date:2023-03-24
*/
public class ExportActionHead extends NCAction {
private static final long serialVersionUID = -2288549251908687419L;
private BillManageModel model;
protected ShowUpableBillForm editor;
public ExportActionHead() {
setBtnName("导出表头");
setCode("exportAction");
}
@Override
public void doAction(ActionEvent arg0) throws Exception {
// TODO 自动生成的方法存根
ExportFileDialog fileDlg = new ExportFileDialog();
if (fileDlg.show()) {
// 得到导出的EXCEL文件路径
String filepath = fileDlg.getFilePath();
if (filepath == null) {
ShowStatusBarMsgUtil.showErrorMsg("提示", "请选择导出的文件!", getModel()
.getContext());// 最下面红色显示
return;
}
try {
export(filepath);
} catch (Exception e) {
ExceptionUtils.wrappBusinessException("程序出错:" + e.getMessage());
}
}
ShowStatusBarMsgUtil.showStatusBarMsg("导出模板成功!", getModel()
.getContext());
}
public void export(String filepath) throws BusinessException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
SXSSFWorkbook wb = new SXSSFWorkbook(xssfWorkbook, 100);
// 创建工作表
SXSSFSheet wbSheet = (SXSSFSheet) wb.createSheet(getExcelSheetName());
// 设置默认行宽
wbSheet.setDefaultColumnWidth(15);
// 设置列头单元格格式
CellStyle headStyle = getHeadStyle(wb);
// 设置表内容单元格格式
CellStyle bodyStyle = getBodyStyle(wb);
// 设置有值的合计行单元格格式
CellStyle bodyStyleHjh = getBodyStyleHjh(wb);
// 在第1行创建列头
SXSSFRow row = (SXSSFRow) wbSheet.createRow((int) 0);
// 设置列头元素
SXSSFCell cellHead = null;
for (int i = 0; i < getExportFieldsName().length; i++) {
cellHead = (SXSSFCell) row.createCell(i);
cellHead.setCellValue(getExportFieldsName()[i]);
cellHead.setCellStyle(headStyle);
}
// 封装表内容数据
List<YhlypxhtfkdVO> list = new ArrayList<YhlypxhtfkdVO>();
Object[] selectedOperaDatas = getModel().getSelectedOperaDatas();
if(selectedOperaDatas == null){
throw new BusinessException(" 未选择导出数据! ");
}
for (int i = 0; i < selectedOperaDatas.length; i++) {
AggYhlypxhtfkdVO aggvo = (AggYhlypxhtfkdVO) selectedOperaDatas[i];
AggYhlypxhtfkdVO clone = (AggYhlypxhtfkdVO) aggvo.clone();
list.add(clone.getParentVO());
}
//写入表内容数据
int nowRow = 1;
if (list != null && list.size() > 0) {
//放款金额(原币)
UFDouble fkjeyb = UFDouble.ZERO_DBL;
//放款金额(本币)
UFDouble fkjebb = UFDouble.ZERO_DBL;
YhlypxhtfkdVO[] array = list.toArray(new YhlypxhtfkdVO[list.size()]);
YhlypxhtfkdVO[] exeArray = execFormulaWithVOs(array);
for (YhlypxhtfkdVO vo : exeArray) {
// 在第2行创建rows
SXSSFRow rows = (SXSSFRow) wbSheet.createRow(nowRow++);
// 设置表内容元素
SXSSFCell cellHeads = null;
// 写入数据内容
String[] fieldsValue = new String[] {
NullValueUtils.getNullStringValue(vo.getDbilldate().toStdString().substring(0, 10)),//单据日期
NullValueUtils.getNullStringValue(vo.getNqx()),//期限
NullValueUtils.getNullStringValue(vo.getDenddate()),//最后一次还款日期
NullValueUtils.getNullStringValue(vo.getPk_currtype()),//币种
NullValueUtils.getNullStringValue(vo.getNorifkmny()),//放款金额(原币)
NullValueUtils.getNullStringValue(vo.getCurrate()),//本币汇率
NullValueUtils.getNullStringValue(vo.getNfkmny()),//放款金额(本币)
NullValueUtils.getNullStringValue(vo.getReratedays()),//利率天数
NullValueUtils.getNullStringValue(vo.getJxzq()),// 结息周期
NullValueUtils.getNullStringValue(vo.getQxunit()),//期限单位
NullValueUtils.getNullStringValue(vo.getHth()),//合同号
NullValueUtils.getNullStringValue(vo.getSumnum())//累计放款单数量
};
//处理最后一次还款日期
if(!fieldsValue[2].equals("")){
fieldsValue[2] = fieldsValue[2].substring(0, 10);
}
// 开始写入数据,j为列值
for (int j = 0; j < fieldsValue.length; j++) {
cellHeads = (SXSSFCell) rows.createCell(j);
cellHeads.setCellValue(fieldsValue[j]);
cellHeads.setCellStyle(bodyStyle);
if(vo.getAttributeValue(getExportFieldsCode()[j]) instanceof UFDouble && j==4){
fkjeyb = fkjeyb.add(NullValueUtils.getNullUFdoubleValue(vo.getAttributeValue(getExportFieldsCode()[j])));
}else if(vo.getAttributeValue(getExportFieldsCode()[j]) instanceof UFDouble && j==6){
fkjebb = fkjebb.add(NullValueUtils.getNullUFdoubleValue(vo.getAttributeValue(getExportFieldsCode()[j])));
}
}
//创建合计行
wbSheet.createRow(nowRow);
for(int i=0; i<fieldsValue.length; i++){
wbSheet.getRow(nowRow).createCell(i);
if(i == 0){
wbSheet.getRow(nowRow).getCell(i).setCellStyle(bodyStyle);
wbSheet.getRow(nowRow).getCell(i).setCellValue("合计行");
}else if(i == 4){
wbSheet.getRow(nowRow).getCell(i).setCellValue(getStringByUFDouble(fkjeyb));
wbSheet.getRow(nowRow).getCell(i).setCellStyle(bodyStyleHjh);
}else if(i == 6){
wbSheet.getRow(nowRow).getCell(i).setCellValue(getStringByUFDouble(fkjebb));
wbSheet.getRow(nowRow).getCell(i).setCellStyle(bodyStyleHjh);
}else{
wbSheet.getRow(nowRow).getCell(i).setCellStyle(bodyStyle);
}
}
}
}
//设置excel表名,并生成excel
try {
FileOutputStream fout = new FileOutputStream(filepath
+ "\\放款单(仅表头)"
+ new UFDate().toStdString().substring(0, 10) + ".xlsx");
wb.write(fout);
fout.close();
} catch (Exception e) {
// TODO 自动生成的 catch 块
throw new BusinessException("生成excel出错:" + e.getMessage());
}
}
/**
* @desc:档案翻译(币种和合同号一起翻译)
* @author:hh
* @date :2023年3月29日
*/
private YhlypxhtfkdVO[] execFormulaWithVOs(YhlypxhtfkdVO[] hvos) {
YhlypxhtfkdVO[] clonevos = hvos.clone();
SuperVOUtil.execFormulaWithVOs(clonevos, new String[] {
//币种 bd_curritype
"pk_currtype->getColValue(bd_currtype,name,pk_currtype,pk_currtype)",
//合同 bd_curritype
"hth->getColValue(yhly_yhlypxdkht,vbillno,pk_dkht,hth)"
});
return clonevos;
}
//列名名称数组
private String[] getExportFieldsName() {
return new String[] { "单据日期", "期限", "最后一次还款日期", "币种", "放款金额(原币)",
"本币汇率", "放款金额(本币)", "利率天数", "结息周期", "期限单位",
"合同号", "累计放款单数量" };
}
//列名编码数组
private String[] getExportFieldsCode() {
return new String[] { "dbilldate", "nqx", "denddate", "pk_currtype", "norifkmny",
"currate", "nfkmny", "reratedays", "jxzq", "qxunit", "hth", "sumnum" };
}
private String getExcelSheetName() {
String date = new UFDate().toString().substring(0, 10);
return "放款单" + date;
}
public BillManageModel getModel() {
return model;
}
public void setModel(BillManageModel model) {
this.model = model;
this.model.addAppEventListener(this);
}
public ShowUpableBillForm getEditor() {
return editor;
}
public void setEditor(ShowUpableBillForm editor) {
this.editor = editor;
}
// 设置表头的单元格样式
public CellStyle getHeadStyle(SXSSFWorkbook wb) {
// 创建单元格样式
CellStyle cellStyle = wb.createCellStyle();
// 设置单元格的背景颜色为淡灰色
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 设置填充字体的样式
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(false);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) wb.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体的样式
font.setFontHeight(14);// 设置字体的大小
cellStyle.setFont(font);// 将字体填充到表格中去
// 设置单元格边框为细线条(上下左右)
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
// 设置表体的单元格样式
public CellStyle getBodyStyle(SXSSFWorkbook wb) {
// 创建单元格样式
CellStyle cellStyle = wb.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容不显示自动换行
cellStyle.setWrapText(false);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) wb.createFont();
// font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体
font.setFontHeight(12);// 设置字体的大小
cellStyle.setFont(font);// 将字体添加到表格中去
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
public CellStyle getBodyStyleHjh (SXSSFWorkbook wb){
// 创建单元格样式
CellStyle cellStyle = wb.createCellStyle();
// 设置单元格的背景颜色为橙色
cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
// 设置填充字体的样式
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容不显示自动换行
cellStyle.setWrapText(false);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) wb.createFont();
// font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体
font.setFontHeight(12);// 设置字体的大小
cellStyle.setFont(font);// 将字体添加到表格中去
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
public static UFDouble getUFDoubleNullValue(Object obj) {
return obj == null?UFDouble.ZERO_DBL:new UFDouble(obj.toString());
}
private Double getStringByUFDouble(UFDouble value){
if(value==null){
value = UFDouble.ZERO_DBL;
}else{
value = new UFDouble(value.toString());
}
return value.toDouble();
}
}
2.导出功能(表头加表体)
package nc.ui.yhlypx.yhlypxfkd.ace.action;
import java.awt.event.ActionEvent;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import nc.ui.pubapp.uif2app.model.BillManageModel;
import nc.ui.pubapp.uif2app.view.ShowUpableBillForm;
import nc.ui.uif2.NCAction;
import nc.ui.uif2.ShowStatusBarMsgUtil;
import nc.ui.yhlypx.util.NullValueUtils;
import nc.ui.yhlypx.yhlypxfkd.ace.dialog.ExportFileDialog;
import nc.vo.pub.BusinessException;
import nc.vo.pub.SuperVOUtil;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDouble;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nc.vo.yhlypx.yhlypxfkd.AggYhlypxhtfkdVO;
import nc.vo.yhlypx.yhlypxfkd.YhlypxhtfkBVO;
import nc.vo.yhlypx.yhlypxfkd.YhlypxhtfkdVO;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @desc:放款单导出按钮(导出表头和表体)
* @author hh
* @date:2023-03-24
*/
public class ExportActionHeadAndBody extends NCAction {
private static final long serialVersionUID = -2288549251908687419L;
private BillManageModel model;
protected ShowUpableBillForm editor;
public ExportActionHeadAndBody() {
setBtnName("导出表头和表体");
setCode("exportAction");
}
@Override
public void doAction(ActionEvent arg0) throws Exception {
// TODO 自动生成的方法存根
ExportFileDialog fileDlg = new ExportFileDialog();
if (fileDlg.show()) {
// 得到导出的EXCEL文件路径
String filepath = fileDlg.getFilePath();
if (filepath == null) {
ShowStatusBarMsgUtil.showErrorMsg("提示", "请选择导出的文件!", getModel()
.getContext());// 最下面红色显示
return;
}
try {
export(filepath);
} catch (Exception e) {
ExceptionUtils.wrappBusinessException("程序出错:" + e.getMessage());
}
}
ShowStatusBarMsgUtil.showStatusBarMsg("导出模板成功!", getModel()
.getContext());
}
public void export(String filepath) throws BusinessException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
List<YhlypxhtfkdVO> headvolist = new ArrayList<YhlypxhtfkdVO>();
List<YhlypxhtfkBVO> bodyvolist = new ArrayList<YhlypxhtfkBVO>();
Object[] selectedOperaDatas = getModel().getSelectedOperaDatas();
if(selectedOperaDatas == null){
throw new BusinessException(" 未选择导出数据! ");
}
for (int i = 0; i < selectedOperaDatas.length; i++) {
AggYhlypxhtfkdVO aggvo = (AggYhlypxhtfkdVO) selectedOperaDatas[i];
AggYhlypxhtfkdVO clone = (AggYhlypxhtfkdVO) aggvo.clone();
YhlypxhtfkBVO[] bodyvos = (YhlypxhtfkBVO[]) clone.getChildrenVO();
headvolist.add((YhlypxhtfkdVO) clone.getParentVO());
for (int j = 0; j < bodyvos.length; j++) {
bodyvolist.add(bodyvos[j]);
}
}
if(headvolist.size() > 0){
//放款单导出表头数据
exportwzxqsqd(xssfWorkbook,headvolist);
//放款单导出表体数据
exportwzxqsqdbvo(xssfWorkbook,bodyvolist);
}
try {
FileOutputStream fout = new FileOutputStream(filepath
+ "\\放款单(表头加表体)"
+ new UFDate().toStdString().substring(0, 10) + ".xlsx");
xssfWorkbook.write(fout);
fout.close();
} catch (Exception e) {
// TODO 自动生成的 catch 块
throw new BusinessException("生成excel出错:" + e.getMessage());
}
}
//放款单导出表头数据
private void exportwzxqsqd(XSSFWorkbook xb, List<YhlypxhtfkdVO> headvolist) {
//表头翻译
YhlypxhtfkdVO[] hvoArray = headvolist.toArray(new YhlypxhtfkdVO[headvolist.size()]);
YhlypxhtfkdVO[] exeHvoArray = execFormulaWithHVOs(hvoArray);
//创建页签
xb.createSheet(getExcelSheetHeadName());
//获取页签1
XSSFSheet xssfSheet = xb.getSheetAt(0);
// 设置默认行宽
xssfSheet.setDefaultColumnWidth(15);
// 设置列头单元格格式
CellStyle headStyle = getHeadStyle(xb);
// 设置表内容单元格格式
CellStyle bodyStyle = getBodyStyle(xb);
xssfSheet.setRowBreak(headvolist.size());
// 在第1行创建rows
XSSFRow row = (XSSFRow) xssfSheet.createRow((int) 0);
// 设置列头元素
XSSFCell cellHead = null;
for (int i = 0; i < getExportFieldsHeadName().length; i++) {
cellHead = (XSSFCell) row.createCell(i);
cellHead.setCellValue(getExportFieldsHeadName()[i]);
cellHead.setCellStyle(headStyle);
}
//写入表内容数据
int nowRow = 1;
//放款金额(原币)
UFDouble fkjeyb = UFDouble.ZERO_DBL;
//放款金额(本币)
UFDouble fkjebb = UFDouble.ZERO_DBL;
for (int i = 0; i < exeHvoArray.length; i++) {
YhlypxhtfkdVO hvo = (YhlypxhtfkdVO) exeHvoArray[i];
// 在第2行创建rows
XSSFRow rows = (XSSFRow) xssfSheet.createRow(nowRow++);
// 设置表内容元素
XSSFCell cellHeads = null;
String[] fieldsValue = new String[]{
NullValueUtils.getNullStringValue(hvo.getDbilldate().toStdString().substring(0, 10)),//单据日期
NullValueUtils.getNullStringValue(hvo.getNqx()),//期限
NullValueUtils.getNullStringValue(hvo.getDenddate()),//最后一次还款日期
NullValueUtils.getNullStringValue(hvo.getPk_currtype()),//币种
NullValueUtils.getNullStringValue(hvo.getNorifkmny()),//放款金额(原币)
NullValueUtils.getNullStringValue(hvo.getCurrate()),//本币汇率
NullValueUtils.getNullStringValue(hvo.getNfkmny()),//放款金额(本币)
NullValueUtils.getNullStringValue(hvo.getReratedays()),//利率天数
NullValueUtils.getNullStringValue(hvo.getJxzq()),// 结息周期
NullValueUtils.getNullStringValue(hvo.getQxunit()),//期限单位
NullValueUtils.getNullStringValue(hvo.getHth()),//合同号
NullValueUtils.getNullStringValue(hvo.getSumnum())//累计放款单数量
};
//处理最后一次还款日期
if(!fieldsValue[2].equals("")){
fieldsValue[2] = fieldsValue[2].substring(0, 10);
}
// 开始写入数据,j为列值
for (int j = 0; j < fieldsValue.length; j++) {
cellHeads = (XSSFCell) rows.createCell(j);
cellHeads.setCellValue(fieldsValue[j]);
cellHeads.setCellStyle(bodyStyle);
if(hvo.getAttributeValue(getExportFieldsCode()[j]) instanceof UFDouble && j==4){
fkjeyb = fkjeyb.add(NullValueUtils.getNullUFdoubleValue(hvo.getAttributeValue(getExportFieldsCode()[j])));
}else if(hvo.getAttributeValue(getExportFieldsCode()[j]) instanceof UFDouble && j==6){
fkjebb = fkjebb.add(NullValueUtils.getNullUFdoubleValue(hvo.getAttributeValue(getExportFieldsCode()[j])));
}
}
//创建合计行
xssfSheet.createRow(nowRow);
for(int j=0; j<fieldsValue.length; j++){
xssfSheet.getRow(nowRow).createCell(j);
xssfSheet.getRow(nowRow).getCell(j).setCellStyle(bodyStyle);
if(j == 0){
xssfSheet.getRow(nowRow).getCell(j).setCellValue("合计行");
}else if(j == 4){
xssfSheet.getRow(nowRow).getCell(j).setCellValue(getStringByUFDouble(fkjeyb));
}else if(j == 6){
xssfSheet.getRow(nowRow).getCell(j).setCellValue(getStringByUFDouble(fkjebb));
}
}
}
}
//放款单导出表体数据
private void exportwzxqsqdbvo(XSSFWorkbook xb, List<YhlypxhtfkBVO> bodyvolist) {
//表体翻译
YhlypxhtfkBVO[] bvoArray = bodyvolist.toArray(new YhlypxhtfkBVO[bodyvolist.size()]);
YhlypxhtfkBVO[] exeBvoArray = execFormulaWithBVOs(bvoArray);
//创建页签
xb.createSheet(getExcelSheetBodyName());
//获取页签2
XSSFSheet xssfSheet = xb.getSheetAt(1);
// 设置默认行宽
xssfSheet.setDefaultColumnWidth(15);
// 设置列头单元格格式
CellStyle headStyle = getHeadStyle(xb);
// 设置表内容单元格格式
CellStyle bodyStyle = getBodyStyle(xb);
// 在第1行创建rows
XSSFRow row = (XSSFRow) xssfSheet.createRow((int) 0);
// 设置列头元素
XSSFCell cellHead = null;
for (int i = 0; i < getExportFieldsBodyName().length; i++) {
cellHead = (XSSFCell) row.createCell(i);
cellHead.setCellValue(getExportFieldsBodyName()[i]);
cellHead.setCellStyle(headStyle);
}
//写入表内容数据
int nowRow = 1;
int rowno = 0;
//遍历获取表体数据
for (int i = 0; i < exeBvoArray.length; i++) {
YhlypxhtfkBVO bvo = exeBvoArray[i];
// 在第2行创建rows
XSSFRow rows = (XSSFRow) xssfSheet.createRow(nowRow++);
// 设置表内容元素
XSSFCell cellHeads = null;
String[] fieldsValue = new String[]{
++rowno+"",//行号
NullValueUtils.getNullStringValue(bvo.getHkno()),//还款编号
NullValueUtils.getNullStringValue(getUFDate(bvo.getDstartdate())),//开始计息日期
NullValueUtils.getNullStringValue(getUFDate(bvo.getDhkdate())),//还款日期
NullValueUtils.getNullStringValue(bvo.getNprice()),//单价
NullValueUtils.getNullStringValue(bvo.getNnum()),//数量
NullValueUtils.getNullStringValue(bvo.getNmny()==null ? 0.00 : bvo.getNmny().setScale(2, UFDouble.ROUND_HALF_UP)),//金额
NullValueUtils.getNullStringValue(bvo.getSumlxd())//累计利息单数量
};
// 开始写入数据,j为列值
for (int j = 0; j < fieldsValue.length; j++) {
cellHeads = (XSSFCell) rows.createCell(j);
cellHeads.setCellValue(fieldsValue[j]);
cellHeads.setCellStyle(bodyStyle);
}
}
}
/**
* @desc:表头档案翻译
* @author:hh
* @date :2023年3月29日
*/
private YhlypxhtfkdVO[] execFormulaWithHVOs(YhlypxhtfkdVO[] hvos) {
YhlypxhtfkdVO[] clonevos = hvos.clone();
SuperVOUtil.execFormulaWithVOs(clonevos, new String[] {
//币种 bd_curritype
"pk_currtype->getColValue(bd_currtype,name,pk_currtype,pk_currtype)",
//合同 bd_curritype
"hth->getColValue(yhly_yhlypxdkht,vbillno,pk_dkht,hth)"
});
return clonevos;
}
/**
* @desc:表体档案翻译
* @author:hh
* @date :2023年3月29日
*/
private YhlypxhtfkBVO[] execFormulaWithBVOs(YhlypxhtfkBVO[] bvos) {
YhlypxhtfkBVO[] clonevos = bvos.clone();
SuperVOUtil.execFormulaWithVOs(clonevos, new String[] {
});
return clonevos;
}
//表头列名名称数组
private String[] getExportFieldsHeadName() {
return new String[] { "单据日期", "期限", "最后一次还款日期", "币种", "放款金额(原币)",
"本币汇率", "放款金额(本币)", "利率天数", "结息周期", "期限单位",
"合同号", "累计放款单数量" };
}
//表头列名编码数组
private String[] getExportFieldsCode() {
return new String[] { "dbilldate", "nqx", "denddate", "pk_currtype", "norifkmny",
"currate", "nfkmny", "reratedays", "jxzq", "qxunit", "hth", "sumnum" };
}
//表体列名名称数组
private String[] getExportFieldsBodyName() {
return new String[] { "行号", "还款编号", "开始计息日期", "还款日期", "单价",
"数量", "金额", "累计利息单数量" };
}
//表体列名编码数组
// private String[] getExportFieldsBodyCode() {
// return new String[] { "rowno", "hkno", "dstartdate", "dhkdate", "nprice",
// "nnum", "nmny", "sumlxd" };
// }
//表头页签名
private String getExcelSheetHeadName() {
String date = new UFDate().toString().substring(0, 10);
return "放款单表头" + date;
}
//表体页签名
private String getExcelSheetBodyName() {
String date = new UFDate().toString().substring(0, 10);
return "放款单表体" + date;
}
public BillManageModel getModel() {
return model;
}
public void setModel(BillManageModel model) {
this.model = model;
this.model.addAppEventListener(this);
}
public ShowUpableBillForm getEditor() {
return editor;
}
public void setEditor(ShowUpableBillForm editor) {
this.editor = editor;
}
private String getUFDate(Object value) {
if(value == null){
return "";
}else{
return value.toString().substring(0, 10);
}
}
// 设置表头的单元格样式
public CellStyle getHeadStyle(XSSFWorkbook xb) {
// 创建单元格样式
CellStyle cellStyle = xb.createCellStyle();
// 设置单元格的背景颜色为淡灰色
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 设置填充字体的样式
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(false);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) xb.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体的样式
font.setFontHeight(14);// 设置字体的大小
cellStyle.setFont(font);// 将字体填充到表格中去
// 设置单元格边框为细线条(上下左右)
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
// 设置表体的单元格样式
public CellStyle getBodyStyle(XSSFWorkbook xb) {
// 创建单元格样式
CellStyle cellStyle = xb.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容不显示自动换行
cellStyle.setWrapText(false);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) xb.createFont();
// font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体
font.setFontHeight(12);// 设置字体的大小
cellStyle.setFont(font);// 将字体添加到表格中去
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
private Double getStringByUFDouble(UFDouble value){
if(value==null){
value = UFDouble.ZERO_DBL;
}else{
value = new UFDouble(value.toString());
}
return value.toDouble();
}
}