导入部分
controller
/**
* Excel批量导入
*
* @param recordid 需要导入的Excel模板
* @param request 请求
* @return json
*/
@ResponseBody
@RequestMapping(value = "/batchInputAssets", method = RequestMethod.POST)
public JsonResponse batchInputAssets(@RequestBody(required = false) ZCGL record, HttpServletRequest request) {
String ewmUrl = HttpUtil.getUrl(request);
String url = HttpUtil.getUrl(request);
int flag = zcglService.batchInputAssets(record,url,ewmUrl);
if (flag == 0) {
return JsonResponse.getInstanceFailure(JsonResponse.ERROR_SAVE);
} else {
return JsonResponse.getInstanceSuccess();
}
}
service
@Override
public int batchInputAssets(ZCGL record, String url,String ewmUrl) {
String filePath = record.getImportFileList().get(0).getRealUrl();
int flag = 0;
try {
try {
List<ZCGL> list = getDataFromExcel(filePath);
for (ZCGL record1 : list) {
AssetsImagePojo assetsImage = new AssetsImagePojo();
if (record1.getImageList() != null) {
for (int i = 0; i < record1.getImageList().size(); i++) {
assetsImage.setRecordid(UuidUtil.generate());
assetsImage.setSszcid(record1.getRecordid());
assetsImage.setImage(record1.getImageList().get(i).getFileName());
assetsImage.setUrl(record1.getImageList().get(i).getFileUrl());
assetsImage.setCreateTime(new Date());
assetsImage.setModifyTime(new Date());
assetsImage.setCreateUser("test");
assetsImage.setModifyUser("test");
assetsImage.setDelFlag("0");
assetsImageMapper.insert(assetsImage);
}
}
record1.setZcztdm("2");
// record1.setXzl(5);
record1.setXzl(1);
record1.setCreateTime(new Date());
record1.setModifyTime(new Date());
record1.setCreateUser("test");
record1.setModifyUser("test");
record1.setDelFlag("0");
flag = zcglMapper.insert(record1);
if (flag == 0) {
record1 = null;
} else {
//对导入进来的数据直接进行验收
ZCYS zcys = new ZCYS();
zcys.setDelFlag("0");
zcys.setBz("无");
zcys.setCheckState("1");
zcys.setCreateTime(new Date());
zcys.setCreateUser("test");
zcys.setModifyTime(new Date());
zcys.setModifyUser("haiwei_admin");
zcys.setYsbmid("1BR5OTR2N0028E00A8C00000BB8AB41C");
zcys.setYssj(new Date());
zcys.setYsdh(record1.getYsdh());
zcys.setRecordid(UuidUtil.generate());
zcys.setSszcid(record1.getRecordid());
zcys.setCwbh(record1.getCwbh());
zcysMapper.insert(zcys);
//对导入进来的数据直接进行折旧
// ZCZJ zczj=new ZCZJ();
// zczj.setSszcid(record1.getRecordid());
// zczj.setXzl(record1.getXzl()+"");//残值率
// if(record1.getSynx() == null){
// record1.setSynx(30);
// }
// zczj.setZzjyf(record1.getSynx()*12+"");//总折旧月份=使用年限
// int zzjny = record1.getSynx()*12;//使用年限
// //月折旧，累计折旧，资产净值
// zczj.setZjny("1");//已折旧月份
// //资产残值=原值*残值率
// double zccz =Double.valueOf(record1.getZcyz()).doubleValue() * record1.getXzl()*0.01;
// //月折旧额 =(固定资产原值 - 预计净残值)/ 使用年限 / 12
// double yzj = (Double.valueOf(record1.getZcyz()).doubleValue()-zccz)/zzjny;
// BigDecimal temp = new BigDecimal(yzj);
// zczj.setYzj(temp);//月折旧
//
// //累计折旧=月折旧额*已提月份
// double ljzj = yzj* Double.valueOf(zczj.getZjny()).doubleValue();
// Integer.parseInt(zczj.getZjny());
// BigDecimal temp1 = new BigDecimal(ljzj);
// zczj.setLjzj(temp1);
//
// //资产净值=资产原值-累计折旧
// double zcjz = Double.valueOf(record1.getZcyz()).doubleValue() - ljzj;
// Integer.parseInt(record1.getZcyz())
// zczj.setZxjz(zcjz+"");
// zczj.setRecordid(UuidUtil.generate());
// zczj.setDelFlag("0");
// zczj.setCreateTime(new Date());
// zczj.setModifyTime(new Date());
// zczj.setCreateUser(record1.getCreateUser());
// zczj.setModifyUser(record1.getModifyUser());
// flag = zczjMapper.insert(zczj);
//新增完验收和折旧记录以后改变资产管理表的折旧状态和资产状态
ZCGL zcgl = new ZCGL();
zcgl.setRecordid(record1.getRecordid());
zcgl.setZcztdm("1");
// zcgl.setZjztdm("2");
zcglMapper.updateStateById(zcgl);
//将供应商名称录入到供应商管理表
if(record1.getGysmc() == null){
}else{
if(gysglMapper.selectByGysmc(record1.getGysmc()) == null){
GYSGL gys = new GYSGL();
gys.setDelFlag("0");
gys.setZt("0");
gys.setCreateTime(new Date());
gys.setCreateUser("haiwei_admin");
gys.setModifyTime(new Date());
gys.setModifyUser("haiwe_admin");
gys.setGysmc(record1.getGysmc());
gys.setRecordid(UuidUtil.generate());
gysglMapper.insert(gys);
}
}
// 生成二维码
// 获取保存的路径，
String path = Thread.currentThread().getContextClassLoader().getResource("").getPath()
+ "../../upload/";
// 创建文件夹
File file = new File(path);
if (!file.exists() && !file.isDirectory()) {
file.mkdir();
}
String text = ewmUrl+"/initOaZcxqList.do?recordid="+record1.getRecordid();// 二维码内容
// String text = record1.getZcbh() + record1.getZcmc();// 二维码内容
int width = 150; // 二维码图片宽度
int height = 150; // 二维码图片高度
String format = "jpg";// 二维码的图片格式
Hashtable<EncodeHintType, String> hints = new Hashtable<EncodeHintType, String>();
hints.put(EncodeHintType.CHARACTER_SET, "utf-8"); // 内容所使用字符集编码
BitMatrix bitMatrix;
try {
bitMatrix = new MultiFormatWriter().encode(text, BarcodeFormat.QR_CODE, width, height,
hints);
// 生成二维码
File outputFile = new File(file + File.separator + record1.getRecordid() + ".jpg");
writeToFile(bitMatrix, format, outputFile);
// String url = record.getUrl();
String ewmlj = url + "/upload/" + record1.getRecordid() + ".jpg";
String ewmwjm = record1.getRecordid() + ".jpg";
// 将二维码路径和名称放到资产二维码得实体类里保存
ZCEWM zcwem = new ZCEWM();
zcwem.setRecordid(UuidUtil.generate());
zcwem.setEwmlj(ewmlj);
zcwem.setEwmwjm(ewmwjm);
zcwem.setSszcid(record1.getRecordid());
zcwem.setDelFlag("0");
zcwem.setCreateTime(new Date());
zcwem.setModifyTime(new Date());
zcwem.setCreateUser("test");
zcwem.setModifyUser("test");
zcewmMapper.insert(zcwem);
} catch (WriterException | IOException e) {
e.printStackTrace();
}
}
}
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
public List<ZCGL> getDataFromExcel(String filePath) throws IOException, InvalidFormatException {
int j = 0;
// 判断是否为excel类型文件
if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
System.out.println("文件不是excel类型");
}
InputStream inp = new FileInputStream(filePath);
Workbook wookbook = WorkbookFactory.create(inp);
// 得到一个工作表
Sheet sheet = wookbook.getSheetAt(0);
// 获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
List<ZCGL> list = new ArrayList<ZCGL>();
// 获得所有数据
for (int i = 2; i <= totalRowNum; i++) {
ZCGL dbAssets = new ZCGL();
// 获得第i行对象
Row row = sheet.getRow(i);
// 获得获得第i行第0列的 String类型对象
Cell cell = row.getCell((short) 2);
dbAssets.setCwbh(cell.getStringCellValue());
cell = row.getCell((short) 3);
ZCLX zclx = zclxMapper.selectAssetTypeByZclxmc(cell.getStringCellValue());
if(zclx != null){
dbAssets.setSszclx1(zclx.getRecordid());
}else{
dbAssets.setSszclx1("");
}
cell = row.getCell((short) 4);
ZCLX zclx1 = zclxMapper.selectAssetTypeByZclxmc(cell.getStringCellValue());
if(zclx1 != null){
dbAssets.setSszclx2(zclx1.getRecordid());
}else{
dbAssets.setSszclx2("");
}
cell = row.getCell((short) 5);
dbAssets.setZcmc(cell.toString());
//品牌
cell = row.getCell((short) 6);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setBrandId(null);
} else {
String brandName = cell.getStringCellValue();
AssetsBrand brand = brandMapper.selectByBrandName(brandName);
if(brand != null){
dbAssets.setBrandId(brand.getRecordid());
}else{
dbAssets.setBrandId("");
}
}
cell = row.getCell((short) 7);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setGrfsdm(null);
} else {
DmCodeTable dmcode = new DmCodeTable();
dmcode.setDmfl("GZFS");
dmcode.setDmnr(cell.getStringCellValue());
DmCodeTable dmcodetable = dmcodetableMapper.selectByDmnr(dmcode);
if(dmcodetable != null){
dbAssets.setGrfsdm(dmcodetable.getDmbh());
}else{
dbAssets.setGrfsdm("");
}
}
//购入日期
cell = row.getCell((short) 8);
try {
Date date = sdf.parse(cell.toString());
dbAssets.setGrrq(date);
} catch (ParseException e) {
dbAssets.setGrrq(null);
}
cell = row.getCell((short) 9);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setCqdwid("");
} else {
DWGL dwgl = dwglMapper.selectByDwmc(cell.toString());
if(dwgl != null){
dbAssets.setCqdwid(dwgl.getDwbh());
}else{
dbAssets.setCqdwid("");
}
}
//使用单位 oa拉取
cell = row.getCell((short) 10);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setSydwid("");
} else {
//获取使用单位
ZcOaDepartmentMember zcOaDepartmentMember = new ZcOaDepartmentMember();
zcOaDepartmentMember.setOaUserName(cell.toString());
List<ZcOaDepartmentMember> zcMemberList = zcOaDepartmentMemberMapper.selectByZcOaDepartmentMemberList(zcOaDepartmentMember);
if(zcMemberList.size()>0){
dbAssets.setSydwid(zcMemberList.get(0).getOtherId());
dbAssets.setSydwOrgid(zcMemberList.get(0).getOaOrgId());
}else{
dbAssets.setSydwid("");
}
}
//使用部门
cell = row.getCell((short) 11);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setSybmName("");
} else {
BMGL bg = new BMGL();
bg.setBmmc(cell.toString());
BMGL bmgl = bmglMapper.selectByPrimaryKey(bg);
if(bmgl != null){
dbAssets.setSybmid(bmgl.getBmbh());
}else{
dbAssets.setSybmid("");
}
}
//存放地点
cell = row.getCell((short) 12);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setCfdd1("");
} else {
dbAssets.setCfdd1(cell.toString());
}
//使用人
cell = row.getCell((short) 13);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setSyr("");
} else {
dbAssets.setSyr(cell.toString());
}
//管理人员oa拉取
cell = row.getCell((short) 14);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setGlryid("");
} else {
// 获取管理员
ZcOaDepartmentMember zcOaDepartmentMember = new ZcOaDepartmentMember();
zcOaDepartmentMember.setOaUserName(cell.toString());
List<ZcOaDepartmentMember> zcMemberList = zcOaDepartmentMemberMapper.selectByZcOaDepartmentMemberList(zcOaDepartmentMember);
if(zcMemberList.size()>0){
dbAssets.setGlryid(zcMemberList.get(0).getOtherId());
}else{
dbAssets.setGlryid("");
}
}
cell = row.getCell((short) 15);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setCwbh("");
} else {
dbAssets.setZcbh(cell.getStringCellValue());
}
cell = row.getCell((short) 16);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setYsdh("");
} else {
dbAssets.setYsdh(cell.getStringCellValue());
}
cell = row.getCell((short) 17);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setGgxh("");
} else {
dbAssets.setGgxh(cell.getStringCellValue());
}
cell = row.getCell((short) 18);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setZcyz("");
} else {
dbAssets.setZcyz(cell.toString());
}
//生产日期
cell = row.getCell((short) 19);
try {
Date date = sdf.parse(cell.toString());
dbAssets.setScrq(date);
} catch (ParseException e) {
dbAssets.setScrq(null);
}
cell = row.getCell((short) 20);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setSynx(null);
} else {
dbAssets.setSynx((int)Double.valueOf(cell.toString()).doubleValue());
}
cell = row.getCell((short) 21);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setBxts(null);
} else {
dbAssets.setBxts((int) cell.getNumericCellValue());
}
cell = row.getCell((short) 22);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setGysmc(null);
} else {
GYSGL gys = gysglMapper.selectByGysmc(cell.toString());
if(gys != null){
dbAssets.setGys(gys.getRecordid());
}else{
dbAssets.setGysmc(cell.toString());
}
}
cell = row.getCell((short) 23);
if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
dbAssets.setBz(null);
} else {
dbAssets.setBz(cell.getStringCellValue());
}
dbAssets.setRecordid(UuidUtil.generate());
list.add(dbAssets);
}
return list;
}导出部分
controller
/**
* 导出Excel
*
* @param recordid 记录ID
* @param request 请求
* @return json
*/
@ResponseBody
@RequestMapping(value = "/assetsExportExcel", method = RequestMethod.POST)
public JsonResponse assetsExportExcel(@RequestBody(required = false) ZCGL record, HttpServletRequest request) {
String url = "http://" + request.getServerName() + ":" + request.getServerPort() + request.getContextPath()+"/exportTemplate/台帐.xls";
record = zcglService.assetsExportExcel(record,url);
return JsonResponse.getInstanceSuccess(url);
}service
/**
* 资产管理Excel导出
*
**/
public ZCGL assetsExportExcel(ZCGL record, String url) {
try {
try {
print(record);
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private void print(ZCGL record) throws IOException, InvalidFormatException {
// excel模板路径
srcXlsPath = Thread.currentThread().getContextClassLoader().getResource("").getPath() + "../../exportTemplates/Assets.xlsx";
// excel导出路径
String path = Thread.currentThread().getContextClassLoader().getResource("").getPath() + "../../exportTemplate/";
File file = new File(path);
if (!file.exists() && !file.isDirectory()) {
file.mkdir();
}
desXlsPath = path+"台帐.xls";
List<ZCGL> aa = zcglMapper.selectZCGLList(record);
InputStream inp = new FileInputStream(srcXlsPath);
Workbook wookbook = WorkbookFactory.create(inp);
FileOutputStream out = new FileOutputStream(desXlsPath);
for (int i = 0; i < 15; i++) {
ZCGL zcgl = aa.get(i);
ZCGL assets = zcglMapper.selectByPrimaryKey(zcgl.getRecordid());
ZCYS zcys = new ZCYS();
zcys.setRecordid(zcgl.getRecordid());
List<ZCYS> zcysList = zcysMapper.getWeChatCheckList(zcys);
ZCLX zclxBean = zclxMapper.selectByPrimaryKey(zcgl.getSszclx2());
// 获取模板
// FileInputStream fis = new FileInputStream(srcXlsPath);
// 输出模板
Sheet sheet = wookbook.getSheetAt(0);
// 行号下标，从0开始。
int rowIndex = i + 5;
// 创建行
Row row = sheet.getRow(rowIndex);
Cell borderCell = row.createCell(1);
// 设置样式
XSSFCellStyle borderStyle = (XSSFCellStyle) wookbook.createCellStyle();
List<String> list = new ArrayList<String>();
list.add(zcysList.get(0).getCwbh()); // 财务编号
list.add(zclxBean.getCzlxbh()); // 机械分类编号
list.add(assets.getZcmc());// 资产名称
list.add(assets.getZcyz());// 资产原值
list.add(assets.getGgxh());// 规格型号
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String modifyTime = formatter.format(assets.getModifyTime());
list.add(modifyTime);// 调入时间
list.add("");// 调出时间、部门
list.add("");// 使用人签字
list.add("");// 负责人签字
list.add(assets.getBz());// 备注
sb(wookbook, sheet, rowIndex, list);
}
wookbook.write(out);
out.flush();
out.close();
}
/**
* 重复项共同方法
*
* @param wookbook
* @param sheet
* @param rowIndex
* @param list
*/
private static void sb(Workbook wookbook, Sheet sheet, int rowIndex, List<String> list) {
// 创建行
Row row = sheet.getRow(rowIndex);
Cell borderCell = row.createCell(1);
// 设置样式
XSSFCellStyle borderStyle = (XSSFCellStyle) wookbook.createCellStyle();
row = sheet.getRow(rowIndex);
borderCell = row.createCell(1);
borderStyle = (XSSFCellStyle) wookbook.createCellStyle();
// 设置右边框
borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
// 设置上边框
borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
borderCell = row.createCell(1);
borderCell.setCellValue(list.get(0));
// 设置单元格内容水平对其方式 居中对齐
borderStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 设置单元格内容垂直对其方式 中对齐
borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
borderCell.setCellStyle(borderStyle);
borderCell = row.createCell(2);
borderCell.setCellValue(list.get(1));
// 设置单元格内容水平对其方式 居中对齐
borderStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 设置单元格内容垂直对其方式 中对齐
borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
borderCell.setCellStyle(borderStyle);
borderCell = row.createCell(3);
borderCell.setCellValue(list.get(2));
// 设置单元格内容水平对其方式 居中对齐
borderStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 设置单元格内容垂直对其方式 中对齐
borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
borderCell.setCellStyle(borderStyle);
borderCell = row.createCell(4);
borderCell.setCellValue(list.get(3));
// 设置单元格内容水平对其方式 居中对齐
borderStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 设置单元格内容垂直对其方式 中对齐
borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
borderCell.setCellStyle(borderStyle);
borderCell = row.createCell(5);
borderCell.setCellValue(list.get(4));
// 设置单元格内容水平对其方式 居中对齐
borderStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 设置单元格内容垂直对其方式 中对齐
borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
borderCell.setCellStyle(borderStyle);
borderCell = row.createCell(6);
borderCell.setCellValue(list.get(5));
// 设置单元格内容水平对其方式 居中对齐
borderStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 设置单元格内容垂直对其方式 中对齐
borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 设置右边框
borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
borderCell.setCellStyle(borderStyle);
borderCell = row.createCell(7);
borderCell.setCellValue(list.get(6));
// 设置单元格内容水平对其方式 居中对齐
borderStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 设置单元格内容垂直对其方式 中对齐
borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 设置右边框
borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
borderCell.setCellStyle(borderStyle);
}导入模板
导出模板
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
