use of org.apache.poi.hssf.usermodel.HSSFFont in project financial by greatkendy123.
the class ExportTGExcel method getStyle.
/*
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
// font.setFontHeightInPoints((short)10);
// 字体加粗
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
// font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
use of org.apache.poi.hssf.usermodel.HSSFFont in project financial by greatkendy123.
the class ExportExcel method getColumnTopStyle.
/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
// font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
use of org.apache.poi.hssf.usermodel.HSSFFont in project sinsim by WilsonHu.
the class TaskRecordController method export.
/**
* 导出计划到excel表格
* @param orderNum
* @param machineStrId
* @param taskName
* @param nameplate
* @param installStatus
* @param machineType
* @param query_start_time
* @param query_finish_time
* @param is_fuzzy
* @return
*/
@PostMapping("/export")
public Result export(String orderNum, String machineStrId, String taskName, String nameplate, Integer installStatus, Integer machineType, String query_start_time, String query_finish_time, @RequestParam(defaultValue = "true") Boolean is_fuzzy) {
List<TaskRecordDetail> list = taskRecordService.selectPlanedTaskRecords(orderNum, machineStrId, taskName, nameplate, installStatus, machineType, query_start_time, query_finish_time, is_fuzzy);
HSSFWorkbook wb = null;
FileOutputStream out = null;
String downloadPath = "";
/*
返回给docker外部下载
*/
String downloadPathForNginx = "";
SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
SimpleDateFormat formatter2 = new SimpleDateFormat("yyyy/MM/dd HH:mm");
String dateString;
try {
// 生成一个空的Excel文件
wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
// 设置标题行格式
HSSFCellStyle headcellstyle = wb.createCellStyle();
HSSFFont headfont = wb.createFont();
headfont.setFontHeightInPoints((short) 10);
// 粗体显示
headfont.setBold(true);
headcellstyle.setFont(headfont);
Row row;
// 创建行和列
for (int r = 0; r < list.size() + 1; r++) {
// 新创建一行,行号为row+1
row = sheet1.createRow(r);
// 计划完成时间,合同交货日期,计划交货日期
for (int c = 0; c < 13; c++) {
// 创建一个单元格,列号为col+1
row.createCell(c);
sheet1.getRow(0).getCell(c).setCellStyle(headcellstyle);
}
}
for (int k = 1; k < 13; k++) {
sheet1.setColumnWidth(k, 4500);
}
// 第一行为标题
sheet1.getRow(0).getCell(0).setCellValue("序号");
sheet1.getRow(0).getCell(1).setCellValue("需求单号");
sheet1.getRow(0).getCell(2).setCellValue("机器编号");
sheet1.getRow(0).getCell(3).setCellValue("机型");
sheet1.getRow(0).getCell(4).setCellValue("工序");
sheet1.getRow(0).getCell(5).setCellValue("状态");
sheet1.getRow(0).getCell(6).setCellValue("安装的开始时间");
sheet1.getRow(0).getCell(7).setCellValue("安装的结束时间");
sheet1.getRow(0).getCell(8).setCellValue("质检的开始时间");
sheet1.getRow(0).getCell(9).setCellValue("质检的结束时间");
sheet1.getRow(0).getCell(10).setCellValue("计划完成时间");
sheet1.getRow(0).getCell(11).setCellValue("合同交货日期");
sheet1.getRow(0).getCell(12).setCellValue("计划交货日期");
// 第二行开始,填入值
Machine machine = null;
MachineOrder machineOrder = null;
MachineType machineType1 = null;
Byte taskStatus = 0;
for (int r = 0; r < list.size(); r++) {
row = sheet1.getRow(r + 1);
row.getCell(0).setCellValue(r + 1);
// 需求单号
if (list.get(r).getMachineOrder().getOrderNum() != null) {
row.getCell(1).setCellValue(list.get(r).getMachineOrder().getOrderNum());
}
// 机器编号
if (list.get(r).getMachine().getNameplate() != null) {
row.getCell(2).setCellValue(list.get(r).getMachine().getNameplate());
}
int machineTypeID = list.get(r).getMachine().getMachineType();
machine = machineService.selectMachinesByNameplate(list.get(r).getMachine().getNameplate());
/**
* 获取机型类型的名称 machine_type.name
*/
machineType1 = machineTypeService.findById(machineTypeID);
if (machineType1 != null) {
// 机型
row.getCell(3).setCellValue(machineType1.getName());
}
// 工序
if (list.get(r).getTaskName() != null) {
row.getCell(4).setCellValue(list.get(r).getTaskName());
}
// 状态
taskStatus = list.get(r).getStatus();
if (taskStatus == Constant.TASK_INITIAL) {
row.getCell(5).setCellValue(Constant.STR_TASK_INITIAL);
} else if (taskStatus == Constant.TASK_PLANED) {
row.getCell(5).setCellValue(Constant.STR_TASK_PLANED);
} else if (taskStatus == Constant.TASK_INSTALL_WAITING) {
row.getCell(5).setCellValue(Constant.STR_TASK_INSTALL_WAITING);
} else if (taskStatus == Constant.TASK_INSTALLING) {
row.getCell(5).setCellValue(Constant.STR_TASK_INSTALLING);
} else if (taskStatus == Constant.TASK_INSTALLED) {
row.getCell(5).setCellValue(Constant.STR_TASK_INSTALLED);
} else if (taskStatus == Constant.TASK_QUALITY_DOING) {
row.getCell(5).setCellValue(Constant.STR_TASK_QUALITY_DOING);
} else if (taskStatus == Constant.TASK_QUALITY_DONE) {
row.getCell(5).setCellValue(Constant.STR_TASK_QUALITY_DONE);
} else if (taskStatus == Constant.TASK_INSTALL_ABNORMAL) {
row.getCell(5).setCellValue(Constant.STR_TASK_INSTALL_ABNORMAL);
} else if (taskStatus == Constant.TASK_QUALITY_ABNORMAL) {
row.getCell(5).setCellValue(Constant.STR_TASK_QUALITY_ABNORMAL);
} else if (taskStatus == Constant.TASK_SKIP) {
row.getCell(5).setCellValue(Constant.STR_TASK_SKIP);
}
// 安装的开始时间
if (list.get(r).getInstallBeginTime() != null) {
dateString = formatter2.format(list.get(r).getInstallBeginTime());
row.getCell(6).setCellValue(dateString);
}
// 安装的结束时间
if (list.get(r).getInstallEndTime() != null) {
dateString = formatter2.format(list.get(r).getInstallEndTime());
row.getCell(7).setCellValue(dateString);
}
// 质检的开始时间
if (list.get(r).getQualityBeginTime() != null) {
dateString = formatter2.format(list.get(r).getQualityBeginTime());
row.getCell(8).setCellValue(dateString);
}
// 质检的结束时间
if (list.get(r).getQualityEndTime() != null) {
dateString = formatter2.format(list.get(r).getQualityEndTime());
row.getCell(9).setCellValue(dateString);
}
// 计划完成时间
if (list.get(r).getTaskPlan().getPlanTime() != null) {
dateString = formatter.format(list.get(r).getTaskPlan().getPlanTime());
row.getCell(10).setCellValue(dateString);
}
// 合同交货日期
if (list.get(r).getMachineOrder().getContractShipDate() != null) {
dateString = formatter.format(list.get(r).getMachineOrder().getContractShipDate());
row.getCell(11).setCellValue(dateString);
}
// 计划交货日期
if (list.get(r).getMachineOrder().getPlanShipDate() != null) {
dateString = formatter.format(list.get(r).getMachineOrder().getPlanShipDate());
row.getCell(12).setCellValue(dateString);
}
}
downloadPath = abnoramlExcelOutputDir + "导出计划" + ".xls";
downloadPathForNginx = "/excel/" + "导出计划" + ".xls";
out = new FileOutputStream(downloadPath);
wb.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
if ("".equals(downloadPath)) {
return ResultGenerator.genFailResult("异常导出失败!");
} else {
return ResultGenerator.genSuccessResult(downloadPathForNginx);
}
}
use of org.apache.poi.hssf.usermodel.HSSFFont in project sinsim by WilsonHu.
the class MachineOrderController method exportToFinaceExcel.
@PostMapping("/exportToFinaceExcel")
public Result exportToFinaceExcel(Integer id, Integer contract_id, String order_num, String contract_num, String status, String sellman, String customer, String marketGroupName, // 这个是查询创建日期
String query_start_time, // 这个是查询创建日期
String query_finish_time, // 这个是查询审核日期
String queryStartTimeSign, // 这个是查询审核日期
String queryFinishTimeSign, String machine_name, // 订单签核的当前步骤
String oderSignCurrentStep, String searchDepartment, @RequestParam(defaultValue = "true") Boolean is_fuzzy) {
List<MachineOrderDetail> list = machineOrderService.selectOrder(id, contract_id, order_num, contract_num, status, sellman, customer, marketGroupName, query_start_time, query_finish_time, queryStartTimeSign, queryFinishTimeSign, machine_name, oderSignCurrentStep, searchDepartment, is_fuzzy);
HSSFWorkbook wb = null;
FileOutputStream out = null;
String downloadPath = "";
/*
返回给docker外部下载
*/
String downloadPathForNginx = "";
SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
String dateString;
try {
// 生成一个空的Excel文件
wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("账务报表");
// 设置标题行格式
HSSFCellStyle headcellstyle = wb.createCellStyle();
HSSFFont headfont = wb.createFont();
headfont.setFontHeightInPoints((short) 10);
// 粗体显示
headfont.setBold(true);
headcellstyle.setFont(headfont);
Row row;
// 新创建一行,行号为row+1
row = sheet1.createRow(0);
int columnSum = 35;
for (int c = 0; c < columnSum; c++) {
// 列头
// 创建一个单元格,列号为col+1
row.createCell(c);
sheet1.setColumnWidth(c, 4500);
sheet1.getRow(0).getCell(c).setCellStyle(headcellstyle);
}
// 第一行为标题
sheet1.getRow(0).getCell(0).setCellValue("客户");
sheet1.getRow(0).getCell(1).setCellValue("合同号");
sheet1.getRow(0).getCell(2).setCellValue("订单号");
// sheet1.getRow(0).getCell(3).setCellValue("铭牌号");
sheet1.getRow(0).getCell(3).setCellValue("机器信息");
sheet1.getRow(0).getCell(4).setCellValue("台数");
sheet1.getRow(0).getCell(5).setCellValue("单价");
sheet1.getRow(0).getCell(6).setCellValue("装置");
sheet1.getRow(0).getCell(7).setCellValue("装置总价");
sheet1.getRow(0).getCell(8).setCellValue("优惠金额");
sheet1.getRow(0).getCell(9).setCellValue("订单总价");
sheet1.getRow(0).getCell(10).setCellValue("币种");
sheet1.getRow(0).getCell(11).setCellValue("销售员");
// 销售费
sheet1.getRow(0).getCell(12).setCellValue("业务费");
sheet1.getRow(0).getCell(13).setCellValue("保修费");
sheet1.getRow(0).getCell(14).setCellValue("保修人员");
sheet1.getRow(0).getCell(15).setCellValue("付款方式");
sheet1.getRow(0).getCell(16).setCellValue("定金率");
sheet1.getRow(0).getCell(17).setCellValue("毛利");
sheet1.getRow(0).getCell(18).setCellValue("包装方式");
sheet1.getRow(0).getCell(19).setCellValue("机架长度");
sheet1.getRow(0).getCell(20).setCellValue("针数");
sheet1.getRow(0).getCell(21).setCellValue("头数");
sheet1.getRow(0).getCell(22).setCellValue("头距");
sheet1.getRow(0).getCell(23).setCellValue("X行程");
sheet1.getRow(0).getCell(24).setCellValue("Y行程");
sheet1.getRow(0).getCell(25).setCellValue("电脑");
sheet1.getRow(0).getCell(26).setCellValue("剪线方式");
sheet1.getRow(0).getCell(27).setCellValue("换色方式");
sheet1.getRow(0).getCell(28).setCellValue("加油系统");
sheet1.getRow(0).getCell(29).setCellValue("夹线器");
sheet1.getRow(0).getCell(30).setCellValue("跳跃方式");
sheet1.getRow(0).getCell(31).setCellValue("旋梭");
sheet1.getRow(0).getCell(32).setCellValue("面线夹持");
sheet1.getRow(0).getCell(33).setCellValue("订单类型");
sheet1.getRow(0).getCell(34).setCellValue("填表日期");
DataFormat dataFormat = wb.createDataFormat();
CellStyle cellStyle;
HSSFCellStyle wrapStyle = wb.createCellStyle();
wrapStyle.setWrapText(true);
// 第二行开始,填入值
for (int i = 0; i < list.size(); i++) {
int r = i + 1;
MachineOrderDetail mod = list.get(i);
OrderDetail od = mod.getOrderDetail();
// 新创建一行
row = sheet1.createRow(r);
for (int c = 0; c < columnSum; c++) {
// 创建列单元格
row.createCell(c);
}
// 客户
sheet1.getRow(r).getCell(0).setCellValue(mod.getCustomer());
// 合同号
sheet1.getRow(r).getCell(1).setCellValue(mod.getContractNum());
//
sheet1.getRow(r).getCell(2).setCellValue(mod.getOrderNum());
sheet1.getRow(r).getCell(0).setCellStyle(wrapStyle);
sheet1.getRow(r).getCell(1).setCellStyle(wrapStyle);
sheet1.getRow(r).getCell(2).setCellStyle(wrapStyle);
// sheet1.getRow(r).getCell(3).setCellValue(mod.getNameplate());//
MachineType mt = mod.getMachineType();
if (mt != null) {
// 机器信息
String machineInfo = mt.getName() + "/" + mod.getNeedleNum() + "/" + mod.getHeadNum() + "/" + mod.getHeadDistance() + "/" + mod.getxDistance() + "/" + mod.getyDistance() + "/" + mod.getElectricTrim() + "/" + mod.getElectricPc();
//
sheet1.getRow(r).getCell(3).setCellValue(machineInfo);
}
//
sheet1.getRow(r).getCell(4).setCellValue(mod.getMachineNum());
//
sheet1.getRow(r).getCell(5).setCellValue(mod.getMachinePrice());
cellStyle = wb.createCellStyle();
// 金额格式
cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
sheet1.getRow(r).getCell(5).setCellStyle(cellStyle);
List<Equipment> epArray = JSON.parseArray(mod.getEquipment(), Equipment.class);
String strEp = "";
int epAmount = 0;
for (Equipment itemEquipment : epArray) {
strEp += itemEquipment.getName() + ":" + itemEquipment.getNumber() + "个" + "\r\n";
epAmount += itemEquipment.getPrice() * itemEquipment.getNumber();
}
sheet1.getRow(r).getCell(6).setCellStyle(wrapStyle);
// 装置
sheet1.getRow(r).getCell(6).setCellValue(new HSSFRichTextString(strEp));
// 装置金额
sheet1.getRow(r).getCell(7).setCellValue(epAmount);
sheet1.getRow(r).getCell(7).setCellStyle(cellStyle);
// 优惠金额
sheet1.getRow(r).getCell(8).setCellValue(mod.getDiscounts());
Double totalAmount = Double.parseDouble(mod.getMachinePrice()) * mod.getMachineNum() + epAmount - Double.parseDouble(mod.getDiscounts());
sheet1.getRow(r).getCell(8).setCellStyle(cellStyle);
// 总金额
sheet1.getRow(r).getCell(9).setCellValue(totalAmount);
sheet1.getRow(r).getCell(9).setCellStyle(cellStyle);
// 币种
sheet1.getRow(r).getCell(10).setCellValue(mod.getCurrencyType());
// 销售员
sheet1.getRow(r).getCell(11).setCellValue(mod.getSellman());
// 销售费
sheet1.getRow(r).getCell(12).setCellValue(mod.getBusinessExpense());
// 保修费
sheet1.getRow(r).getCell(13).setCellValue(mod.getWarrantyFee());
// 保修人员
sheet1.getRow(r).getCell(14).setCellValue(mod.getMaintainPerson());
// 付款方式
sheet1.getRow(r).getCell(15).setCellValue(mod.getPayMethod());
// sheet1.getRow(r).getCell(16).setCellValue(0);//定金率 先空着
// 改为要从成本核算员的意见中抽取 毛利率
// 毛利率
sheet1.getRow(r).getCell(17).setCellValue(mod.getGrossProfit());
// 包装方式
sheet1.getRow(r).getCell(18).setCellValue(mod.getPackageMethod());
// 订单中关于技术部的意见里长度:***,显示在财务报表中,显示方式为【机架长度】一栏
// 机架长度
sheet1.getRow(r).getCell(19).setCellValue(mod.getMachineFrameLength());
// 针数
sheet1.getRow(r).getCell(20).setCellValue(mod.getNeedleNum());
// 头数
sheet1.getRow(r).getCell(21).setCellValue(mod.getHeadNum());
// 头距
sheet1.getRow(r).getCell(22).setCellValue(mod.getHeadDistance());
// X行程
sheet1.getRow(r).getCell(23).setCellValue(mod.getxDistance());
// Y行程
sheet1.getRow(r).getCell(24).setCellValue(mod.getyDistance());
// 电脑
sheet1.getRow(r).getCell(25).setCellValue(od.getElectricPc());
// 剪线方式
sheet1.getRow(r).getCell(26).setCellValue(od.getElectricTrim());
// 换色方式
sheet1.getRow(r).getCell(27).setCellValue(od.getColorChangeMode());
// 加油系统
sheet1.getRow(r).getCell(28).setCellValue(od.getElectricOil());
// 夹线器
sheet1.getRow(r).getCell(29).setCellValue(od.getAxleSplit());
// 跳跃方式
sheet1.getRow(r).getCell(30).setCellValue(od.getAxleJump());
// 旋梭
sheet1.getRow(r).getCell(31).setCellValue(od.getAxleHook());
// 面线夹持
sheet1.getRow(r).getCell(32).setCellValue(od.getAxleUpperThread());
// 订单类型
sheet1.getRow(r).getCell(33).setCellValue(mod.getOrderType());
dateString = formatter.format(mod.getCreateTime());
// 填表日期
sheet1.getRow(r).getCell(34).setCellValue(dateString);
}
downloadPath = reportOutputPath + "账务报表" + ".xls";
downloadPathForNginx = "/report/" + "账务报表" + ".xls";
out = new FileOutputStream(downloadPath);
wb.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
if ("".equals(downloadPath)) {
return ResultGenerator.genFailResult("异常导出失败!");
} else {
return ResultGenerator.genSuccessResult(downloadPathForNginx);
}
}
Aggregations