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");
}
java后台生成excel文件
最新推荐文章于 2024-07-30 09:30:00 发布