Java操作excel文件插入数据

读取一个表数据根据自己的需求将数据插入到另外一个表中，下载excel及jar包即可

注意：读取表数据时，表格中的格子没有数据时会读取报错

excel文件及相关jar包：

连接：https://pan.baidu.com/s/1BpLGxBz2dPRvq_eCxFofzA，提取码：aczc

excel表格：（一下插入数据是从表格内随机获取插入数据）

package kf.ats.gvnsvr.client.taizhou.test;import org.apache.commons.io.FileUtils;
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.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;public class TestEditExcel {//第一行每一列的titleprivate static String[] title = new String[] {"部门名称","事项编码","事项名称","创建时间","状态"};//生成文件名private static String newfilepath = "e:/testNew.xls";public static void main(String[] args) throws IOException{//读取excel表数据List listMaps = getExcelOne();//封装需要写入excel表的数据List> BodyMap = excelDataList(listMaps);//写入数据exportXlsgl(BodyMap);}/*** 读取excel表数据* @return，list对象集合*/public static List getExcelOne(){List listMaps = new ArrayList();File file = new File("D:\\2020CodeFile\\yhs.xlsx");/IP地址FileInputStream in;XSSFWorkbook wb;try {in = new FileInputStream(file);wb = new XSSFWorkbook(in);Sheet sheet = wb.getSheetAt(0); //取得“测试.xlsx”中的第一个表单int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); Row row = null; for (int i = firstRowNum+1; i <= lastRowNum; i++) { try { BumenEnity map = new BumenEnity();row = sheet.getRow(i); //取得第i行 （从第二行开始取，因为第一行是表头）map.setDepartment(row.getCell(1).getStringCellValue());map.setName(row.getCell(2).getStringCellValue());map.setService(row.getCell(3).getStringCellValue());listMaps.add(map);} catch (Exception e) { e.printStackTrace(); } }} catch (Exception e1) {// TODO Auto-generated catch blocke1.printStackTrace();}return listMaps;}public static List> excelDataList(List map){List> BodyMap = new ArrayList>();Random r = new Random(1);for (int i=0;i<5;i++){int ret = r.nextInt(100);Date date = randomDate("2020-09-01","2020-11-20");System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date) + " " + ret + " " + map.get(ret).getDepartment() + " " + map.get(ret).getName() + " " + map.get(ret).getService());int max=17,min=8;long randomNum = System.currentTimeMillis();int ran3 = (int) (randomNum%(max-min)+min);DecimalFormat g1=new DecimalFormat("00");String startZeroStr = g1.format(Integer.valueOf(ran3));Map mapdata = new HashMap();mapdata.put("name1", map.get(ret).getName());mapdata.put("name2", map.get(ret).getDepartment());mapdata.put("name3", map.get(ret).getService());mapdata.put("name4", new SimpleDateFormat("yyyy-MM-dd "+startZeroStr+":mm:ss").format(date));mapdata.put("name5", "SUCCESS");BodyMap.add(mapdata);}return BodyMap;}/*** 生成随机时间* @param beginDate开始时间* @param endDate结束时间* @return*/private static Date randomDate(String beginDate,String endDate){try {SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");Date start = format.parse(beginDate);Date end = format.parse(endDate);if(start.getTime() >= end.getTime()){return null;}long date = random(start.getTime(),end.getTime());return new Date(date);} catch (Exception e) {e.printStackTrace();}return null;}/*** 生成随机数* @param begin* @param end* @return*/private static long random(long begin,long end){long rtn = begin + (long)(Math.random() * (end - begin));if(rtn == begin || rtn == end){return random(begin,end);}return rtn;}/*** 创建excel文件并插入数据* @param BodyMap*/public static void exportXlsgl(List> BodyMap){//1.创建Excel工作簿HSSFWorkbook workbook = new HSSFWorkbook();//2.创建一个工作表HSSFSheet sheet = workbook.createSheet("sheet2");//3.创建第一行HSSFRow row = sheet.createRow(0);HSSFCell cell = null;//4.插入第一行数据for (int i = 0; i m : BodyMap){ HSSFRow row2=sheet.createRow(i);HSSFCell cell2=row2.createCell(0);cell2.setCellValue(m.get("name1"));cell2=row2.createCell(1);cell2.setCellValue(m.get("name2"));cell2=row2.createCell(2);cell2.setCellValue(m.get("name3"));cell2=row2.createCell(3);cell2.setCellValue(m.get("name4"));cell2=row2.createCell(4);cell2.setCellValue(m.get("name5"));cell2=row2.createCell(5);i++;}//创建一个文件,将Excel内容存盘File file=new File(newfilepath);try {file.createNewFile();FileOutputStream stream=FileUtils.openOutputStream(file);workbook.write(stream);stream.close();} catch (IOException e) {e.printStackTrace();}}
}


实体类：BumenEnity

插入表数据输出结果：