![](https://i-blog.csdnimg.cn/blog_migrate/7e75b4f6d552a7276d4e9bc026a58c1d.png)
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));
}
}
}
}
}