POI模版导出excel

最新推荐文章于 2025-08-08 22:01:10 发布

原创最新推荐文章于 2025-08-08 22:01:10 发布 · 162 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

本文介绍了一个使用Java和Apache POI库自动生成薪资详情Excel表格的示例代码。该程序可以将员工的基本信息及薪资详情填充到指定的Excel模板中，并保存为新的Excel文件。

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



package com.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.ORM.Report;
import com.ORM.TempBean;
import com.util.FirstEndOfMonth;
import com.util.WebUtil;

public class ExcelTemplate {

	public void genarateExcel(String realpath,File targetFile,Report report,TempBean bean) throws Exception {

		try {
			POIFSFileSystem fs  =new POIFSFileSystem(new FileInputStream(realpath));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);

			//Excel单元格行
			HSSFRow row10 = sheet.getRow(1);
			//Excel单元格列
			HSSFCell cell10 = row10.getCell(0);
			cell10.setCellValue(""+report.getYear()+"年"+report.getMonth()+"月薪资明细");

			//姓名
			HSSFRow row21 = sheet.getRow(2);
			HSSFCell cell21 = row21.getCell(1);
			cell21.setCellValue(report.getUsername());

			//计算周期
			HSSFRow row23 = sheet.getRow(2);
			HSSFCell cell23 = row23.getCell(3);
			String datatiem = " 薪资计算周期："+bean.getFirstDay()+"～"+bean.getEndDay();
			cell23.setCellValue(datatiem);

			//基本工资
			HSSFRow row41 = sheet.getRow(4);
			HSSFCell cell41 = row41.getCell(1);
			cell41.setCellValue(WebUtil.NotNull(report.getSalary()));
			//病事假
			HSSFRow row44 = sheet.getRow(4);
			HSSFCell cell44 = row44.getCell(4);
			cell44.setCellValue(WebUtil.NotNull(report.getSickleave()));

			//报销费用
			HSSFRow row51 = sheet.getRow(5);
			HSSFCell cell51 = row51.getCell(1);
			cell51.setCellValue(WebUtil.NotNull(report.getRemarks()));
			//扣  罚
			HSSFRow row54 = sheet.getRow(5);
			HSSFCell cell54 = row54.getCell(4);
			cell54.setCellValue(WebUtil.NotNull(report.getPunishto()));

			//其他
			HSSFRow row61 = sheet.getRow(6);
			HSSFCell cell61 = row61.getCell(1);
			cell61.setCellValue(WebUtil.NotNull(report.getProsubsidy()));
			//社  保
			HSSFRow row64 = sheet.getRow(6);
			HSSFCell cell64 = row64.getCell(4);
			cell64.setCellValue(WebUtil.NotNull(report.getSocial()));

			//公积金
			HSSFRow row74 = sheet.getRow(7);
			HSSFCell cell74 = row74.getCell(4);
			cell74.setCellValue(WebUtil.NotNull(report.getFundacc()));

			//个  税
			HSSFRow row84 = sheet.getRow(8);
			HSSFCell cell84 = row84.getCell(4);
			cell84.setCellValue(WebUtil.NotNull(report.getBonuses()));

			//补缴四金
			HSSFRow row94 = sheet.getRow(9);
			HSSFCell cell94 = row94.getCell(4);
			cell94.setCellValue(WebUtil.NotNull(report.getOvertime()));

			//其  他
			HSSFRow row104 = sheet.getRow(10);
			HSSFCell cell104 = row104.getCell(4);
			cell104.setCellValue(WebUtil.NotNull(report.getRevenue()));


			//收入合计
			HSSFRow row121 = sheet.getRow(12);
			HSSFCell cell121 = row121.getCell(1);
			cell121.setCellValue(WebUtil.NotNull(report.getBonus()));
			//应扣合计
			HSSFRow row124 = sheet.getRow(12);
			HSSFCell cell124 = row124.getCell(4);
			cell124.setCellValue(WebUtil.NotNull(report.getAftersub()));

			//工资合计
			HSSFRow row141 = sheet.getRow(14);
			HSSFCell cell141 = row141.getCell(1);
			cell141.setCellValue(WebUtil.NotNull(report.getSuntotle()));

			FileOutputStream fOut = new FileOutputStream(targetFile);
			// 把相应的Excel 工作簿存盘
			wb.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {

		ExcelTemplate excelTemplate = new ExcelTemplate();
		String realpath = "D:\\工资条N.xls";
		for(int c = 0; c < 1; c++){
			String filePathName = "D:\\a.xls";
			File filePath = new File(filePathName);

			Report report = new Report();
			report.setUsername("HAHAHA");
			report.setYear("2012");
			report.setMonth("1");
			TempBean bean = new TempBean();
			FirstEndOfMonth firstEnd = new FirstEndOfMonth();
			bean.setFirstDay(firstEnd.getFirstDayOfMonth(2012, 1));
			bean.setEndDay(firstEnd.getLastDayOfMonth(2012, 2));
			excelTemplate.genarateExcel(realpath, filePath, report, bean);
			System.out.println(filePath);
//
//			String namezip = "D:\\zip\\a"+c+".zip";
//			ZipCompressor zc = new ZipCompressor(namezip);
//			zc.compress(filePathName);
		}
//		DeleteFile t = new DeleteFile();
//		t.delAllFile("D:\\xls\\");

	}

}