本文主要是介绍Java(119):ExcelUtil工具类(org.apache.poi读取和写入Excel)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
ExcelUtil工具类(XSSFWorkbook读取和写入Excel)，入参和出参都是：List<Map<String,Object>>
一、读取Excel
testdata.xlsx
1、new XSSFWorkbook对象
File file = new File(filePath);
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
2、new XSSFSheet对象
//获取sheet第一个
XSSFSheet xSheet = wb.getSheetAt(0);
if(sheetName!=null) {
//如果sheetName入参可用这个,通过名称获取
xSheet = wb.getSheet(sheetName);
}
3、遍历行读取数据写入List<Map<String,Object>>
for (int i = 0; i <= xSheet.getLastRowNum(); i++) { //遍历所有行
if(i==0){ //标题行
for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) {
headMap.put(j, xSheet.getRow(i).getCell(j).toString());
}
continue;
}
Map<String, Object> paramsMap = new HashMap<>();
for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) { //遍历当前行所有列
String key=headMap.get(j);
Cell cell=xSheet.getRow(i).getCell(j);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
paramsMap.put(key,cell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
paramsMap.put(key,new Double(cell.getNumericCellValue()).intValue());
break;
}
//paramsMap.put(key,xSheet.getRow(i).getCell(j));
}
resultList.add(paramsMap);
}
二、写入Excel
1、XSSFWorkbook 创建一个工作蒲
XSSFWorkbook xwb = new XSSFWorkbook();
XSSFSheet sheet = xwb.createSheet(sheetName);
2、XSSFSheet写入标题行
XSSFRow row = sheet.createRow(0);
for (int i=0;i<listHead.size();i++){
//根据需要给第一行每一列设置标题
XSSFCell cell = row.createCell(i);
cell.setCellValue(listHead.get(i));
}
3、数据赋值并设置列
//创建行
XSSFRow rows;
//创建列，即单元格Cell
XSSFCell cells;//把List里面的数据写到excel中(除标题行外)
for (int i=0;i<listA.size();i++) {
//从第一行开始写入
rows = sheet.createRow(i + 1);
//创建每个单元格Cell，即列的数据
Map<String, Object> testMap =listA.get(i);
for (int j=0;j<listHead.size();j++){
cells = rows.createCell(j);
String head=listHead.get(j);
String type=testMap.get(head).getClass().getTypeName();
switch (type) {
case "java.lang.Integer":
cells.setCellValue((int) testMap.get(head));
break;
case "java.lang.String":
cells.setCellValue((String) testMap.get(head));
break;
}
}
}
4、写入excel
File file = new File(filePath);
FileOutputStream outputStream = new FileOutputStream(file);
xwb.write(outputStream);
outputStream.flush();
outputStream.close();
result.xlsx
三、整体工具类(参考)
package com.example.utils;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;/*** Description :** @author : HMF* Date : Created in 10:40 2023/11/23* @version :*/
public class ExcelUtil {/*** 读取excel,放入List<Map<String, String>>* @param filePath 读取excel的文件路径+文件名称* @return datalist*/public static List<Map<String,Object>> readExcel(String filePath){return readExcel(filePath,null);}/*** 读取excel,放入List<Map<String, String>>* @param filePath 读取excel的文件路径+文件名称* @Param sheetName sheet名称* @return datalist*/public static List<Map<String,Object>> readExcel(String filePath,String sheetName){List<Map<String,Object>> resultList = new ArrayList<>();Map<Integer, String> headMap = new HashMap<>();try {File file = new File(filePath);FileInputStream fis = new FileInputStream(file);XSSFWorkbook wb = new XSSFWorkbook(fis);//获取sheet第一个XSSFSheet xSheet = wb.getSheetAt(0);if(sheetName!=null) {//如果sheetName入参可用这个,通过名称获取xSheet = wb.getSheet(sheetName);}for (int i = 0; i <= xSheet.getLastRowNum(); i++) { //遍历所有行if(i==0){ //标题行for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) {headMap.put(j, xSheet.getRow(i).getCell(j).toString());}continue;}Map<String, Object> paramsMap = new HashMap<>();for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) { //遍历当前行所有列String key=headMap.get(j);Cell cell=xSheet.getRow(i).getCell(j);switch (cell.getCellType()) {case Cell.CELL_TYPE_STRING:paramsMap.put(key,cell.getStringCellValue());break;case Cell.CELL_TYPE_NUMERIC:paramsMap.put(key,new Double(cell.getNumericCellValue()).intValue());break;}//paramsMap.put(key,xSheet.getRow(i).getCell(j));}resultList.add(paramsMap);}}catch (IOException e){System.out.println(e);}return resultList;}/*** 写入excel* @param filePath 读取excel的文件路径+文件名称* @Param sheetName sheet名称* @param listA 入参列表* @param listHead 标题头列表*/public static void writeExcel(String filePath, String sheetName, List<Map<String, Object>> listA,List<String> listHead){XSSFWorkbook xwb = new XSSFWorkbook();XSSFSheet sheet = xwb.createSheet(sheetName);//标题行XSSFRow row = sheet.createRow(0);for (int i=0;i<listHead.size();i++){//根据需要给第一行每一列设置标题XSSFCell cell = row.createCell(i);cell.setCellValue(listHead.get(i));}//创建行XSSFRow rows;//创建列，即单元格CellXSSFCell cells;//把List里面的数据写到excel中(除标题行外)for (int i=0;i<listA.size();i++) {//从第一行开始写入rows = sheet.createRow(i + 1);//创建每个单元格Cell，即列的数据Map<String, Object> testMap =listA.get(i);for (int j=0;j<listHead.size();j++){cells = rows.createCell(j);String head=listHead.get(j);String type=testMap.get(head).getClass().getTypeName();switch (type) {case "java.lang.Integer":cells.setCellValue((int) testMap.get(head));break;case "java.lang.String":cells.setCellValue((String) testMap.get(head));break;}}}//用输出流写到exceltry {File file = new File(filePath);FileOutputStream outputStream = new FileOutputStream(file);xwb.write(outputStream);outputStream.flush();outputStream.close();}catch (IOException e) {e.printStackTrace();}}public static void main(String args[]){String filePath="src/main/resources/testdata.xlsx";List<Map<String, Object>> resultList= readExcel(filePath,"Sheet1");for(Map<String, Object> testMap:resultList){System.out.println(testMap);
// for(String excKey :testMap.keySet()){
// System.out.println(excKey+": "+testMap.get(excKey));
// }}List<String> listHead =new ArrayList<>();listHead.add("id");listHead.add("name");listHead.add("age");listHead.add("grade");writeExcel("src/main/resources/result.xlsx","result",resultList,listHead);}}
参考：https://www.cnblogs.com/janson071/p/10119935.html
这篇关于Java(119):ExcelUtil工具类(org.apache.poi读取和写入Excel)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
