java后台生成excel文件

最新推荐文章于 2025-01-25 17:04:20 发布

原创最新推荐文章于 2025-01-25 17:04:20 发布 · 646 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java后台生成excel

java开发类专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了一种使用Java进行财务报表数据导出至Excel的方法。通过读取模板，自动填充资产负债表、利润表和现金流表等数据，实现了报表的自动化生成。文章深入探讨了单元格赋值、模板读取及保护机制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public void ExportExcel(Nsxx nsxx,ZcfzList zcfzList,LrxxList lrxxList,XjllList xjllList,String djxh) throws Exception{

	System.out.println(nsxx);
	System.out.println(""+nsxx.getZsfs());
	String fileName = "modelteble.xls";//excel模板路径 
	//String realPath = request.getSession().getServletContext().getRealPath(fileUrl);
	String realPath = modelexcelurl;//模板文件所在文件夹
	File fi = new File(realPath, fileName);
	System.out.println(realPath);
	POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi)); 

	//获取项目的webroot路径
	//String pathStr = request.getSession().getServletContext().getRealPath(fileUrl);
	String pathStr =excelUrl;//生成的excel存储地址
	FileOutputStream fos = null;
	try { 
		fos = new FileOutputStream(pathStr+djxh+".xls");
	} catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}  

	//读取excel模板    
	HSSFWorkbook wb = new HSSFWorkbook(fs); 

	//读取了模板内所有sheet内容    
	HSSFSheet sheet = wb.getSheetAt(0);   //读取报告模板中的第一个sheet页 
	//在相应的单元格进行赋值    
	setCellValue(sheet,2,1,nsxx.getNsrmc()); 

	setCellValue(sheet,3,1,nsxx.getFddbrxm());
	setCellValue(sheet,3,3,nsxx.getFddbryddh());
	setCellValue(sheet,3,5,nsxx.getCwfzrxm());
	setCellValue(sheet,3,7,nsxx.getCwfzryddh());

	//设置单元格为只读  这个是解锁的时候所设置的密码
	//sheet.protectSheet("abcd2019");
	protectSheet(sheet);//将sheet页设置为只读
	
	//资产负债
	//读取了模板内所有sheet内容    
	HSSFSheet sheetzcfz = wb.getSheetAt(1);  
	//在相应的单元格进行赋值    
	setCellValue(sheetzcfz,1,3,zcfzList.getSsrq()); 

	setCellValue(sheetzcfz,2,1,zcfzList.getNsrsbh());
	setCellValue(sheetzcfz,2,5,zcfzList.getNsrmc());

	List<Zcfz> List = zcfzList.getZcfzList();
	if(null!=List&&List.size()>0){
		for (int j=0,i=5;j<List.size()&&i<List.size()+5;j++,i++) {
			// 表格数据
			setCellValue(sheetzcfz,i,0,List.get(j).getZcxmmc());

			setCellValue(sheetzcfz,i,1,List.get(j).getEwbhxh()); 

			setCellValue(sheetzcfz,i,2,List.get(j).getQmsZc());

			setCellValue(sheetzcfz,i,3,List.get(j).getNcsZc());

			setCellValue(sheetzcfz,i,4,List.get(j).getQyxmmc());

			setCellValue(sheetzcfz,i,5,List.get(j).getEwbhxh());

			setCellValue(sheetzcfz,i,6,List.get(j).getQmsQy());

			setCellValue(sheetzcfz,i,7,List.get(j).getNcsQy());
		}
	}
	protectSheet(sheetzcfz);//设置为只读

	//利润信息
	//读取了模板内所有sheet内容    
	HSSFSheet sheetLrxx = wb.getSheetAt(2);  
	//在相应的单元格进行赋值    
	if(lrxxList.getSsrq()!= null){
		setCellValue(sheetLrxx,1,1,lrxxList.getSsrq());
	}
	setCellValue(sheetLrxx,2,0,"纳税人识别号：  "+lrxxList.getNsrsbh());
	setCellValue(sheetLrxx,2,3,lrxxList.getNsrmc());

	List<Lrxx> ListLr = lrxxList.getLrxxList();
	if(null!=ListLr&&ListLr.size()>0){
		for (int j=0,i=5;j<ListLr.size()&&i<ListLr.size()+5;j++,i++) {

			// 表格数据
			setCellValue(sheetLrxx,i,0,ListLr.get(j).getHmc());

			setCellValue(sheetLrxx,i,1,ListLr.get(j).getEwbhxh());  

			setCellValue(sheetLrxx,i,2,ListLr.get(j).getBnljje());

			setCellValue(sheetLrxx,i,3,ListLr.get(j).getByje()); 
		}
	}
	protectSheet(sheetLrxx);//设置为只读
	
	//现金流信息
	//读取了模板内所有sheet内容    
	HSSFSheet sheetXjl = wb.getSheetAt(3);  
	//在相应的单元格进行赋值    
	if(xjllList.getSsrq()!= null){
		setCellValue(sheetXjl,1,1,xjllList.getSsrq()); 
	}
	setCellValue(sheetXjl,2,0,"纳税人识别号：  "+xjllList.getNsrsbh()); 
	setCellValue(sheetXjl,2,3,xjllList.getNsrmc());  


	List<Xjll> ListXjl = xjllList.getXjllList();
	if(null!=ListXjl&&ListXjl.size()>0){
		for (int j=0,i=5;j<ListXjl.size()&&i<ListXjl.size()+5;j++,i++) {

			// 表格数据
			setCellValue(sheetXjl,i,0,ListXjl.get(j).getHmc());

			setCellValue(sheetXjl,i,1,ListXjl.get(j).getEwbhxh());  

			setCellValue(sheetXjl,i,2,ListXjl.get(j).getBnljje()); 

			setCellValue(sheetXjl,i,3,ListXjl.get(j).getByje()); 
		}
	}
	protectSheet(sheetXjl);//设置为只读
	
	try {
		wb.write(fos);  
		fos.flush();  
		fos.close();
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} 
}

//为每个单元格赋值
public void  setCellValue(HSSFSheet sheet,int row,int cell,String cellValue){
	HSSFCell HCell = sheet.getRow(row).getCell(cell); 
	if(null==cellValue||"".equals(cellValue)){
		cellValue="--";
	}
	HCell.setCellValue(cellValue); 
}

public void protectSheet(HSSFSheet sheet){
	sheet.protectSheet("abcd2019");
}