java使用poi给excel文件插入数据

• excel模板文件

•  代码

package com.example.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;

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;

public class ExcelDemo {
	public static void main(String[] args) {
		File file = new File("C:\\Users\\user.SZCPKG3804\\Desktop\\test.xls");
		File targetFile = new File("C:\\Users\\user.SZCPKG3804\\Desktop\\targetFile.xls");
		try (FileInputStream fi = new FileInputStream(file);
				FileOutputStream fo = new FileOutputStream(targetFile);
				HSSFWorkbook hw = new HSSFWorkbook(fi);) {
			
			ArrayList<String[]> arrayList = new ArrayList<String[]>();
			//模拟要插入的数据
			for(int i=0;i<3;i++) {
				String[] strarr = new String[5];
				for(int j=0;j<strarr.length;j++) {
					strarr[j] = i+j+"";
				}
				arrayList.add(strarr);
			}
			// 在指定地方插入指定行数据
			insertRows(hw, 2, arrayList);
			
			//在指定单元格插入数据
			LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
			map.put("date", "2021-10-15");
			insertSpecialDate(hw, hw.getSheetAt(0), map);
			hw.write(fo);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 在指定地方插入指定行数据
	 * @param hw  EXCEL文件
	 * @param starRow 插入数据开始行
	 * @param arrayList  插入的数据
	 */
	private static void insertRows(HSSFWorkbook hw, int starRow, ArrayList<String[]> arrayList) {
		HSSFSheet sheet = hw.getSheetAt(0);
		sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), arrayList.size(), true, false);
		for (int i = 0; i < arrayList.size(); i++) {
			HSSFRow sourceRow = null;
			HSSFRow targetRow = null;
			HSSFCell sourceCell = null;
			HSSFCell targetCell = null;

			sourceRow = sheet.getRow(starRow + i);
			targetRow = sheet.createRow(starRow + 1 + i);
			targetRow.setHeight(sourceRow.getHeight());

			String[] strings = arrayList.get(i);
			for (int m = 0; m < strings.length; m++) {
				sourceCell = sourceRow.getCell(m);
				targetCell = targetRow.createCell(m);
				targetCell.setCellStyle(sourceCell.getCellStyle());
				targetCell.setCellValue(strings[m]);
			}
		}
	}
	
	/**
	 * 在excel指定地方插入数据
	 * @param workBook
	 * @param sheetAt
	 * @param map
	 */
	private static void insertSpecialDate(HSSFWorkbook workBook, HSSFSheet sheetAt, LinkedHashMap<String, String> map) {
		int firstRowNum = sheetAt.getFirstRowNum();
		int lastRowNum = sheetAt.getLastRowNum();
		for(int i=firstRowNum;i<=lastRowNum;i++) {
			HSSFRow row = sheetAt.getRow(i);
			if(row == null) return;
			for (int m = row.getFirstCellNum(); m < row.getLastCellNum(); m++) {
				HSSFCell cell = row.getCell(m);
				String scValue = cell.getStringCellValue();
				if(scValue != null && !"".equals(scValue) && scValue.startsWith("${") && scValue.endsWith("}")) {
					String name = scValue.substring(2,scValue.length()-1);
					cell.setCellValue(map.get(name));
				}
			}
		}
	}
}