Java Poi操作Excel文件

最新推荐文章于 2024-03-04 21:47:10 发布

Godxv

最新推荐文章于 2024-03-04 21:47:10 发布

阅读量624

点赞数 2

分类专栏： Java 文章标签： java excel poi

本文链接：https://blog.youkuaiyun.com/qq_29591261/article/details/78481727

版权

Java 专栏收录该内容

9 篇文章

订阅专栏

本文提供了一个Java类用于操作Excel文件(.xlsx)的方法实现，包括文件创建、数据读取及写入等核心功能，并附带了一个简单的测试示例。

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

Excel操作类

package excelTest;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
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;
public class ExcelOperation {
private static XSSFWorkbook workbook = null;
/**
*判断文件是否存在.
*/
public static boolean fileExist(String fileDir) {
boolean flag = false;
File file = new File(fileDir);
flag = file.exists();
return flag;
}
/**
*判断文件的sheet是否存在.
*/
public static boolean sheetExist(String fileDir, String sheetName) throws Exception {
boolean flag = false;
File file = new File(fileDir);
if (file.exists()) {
try {
workbook = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = workbook.getSheet(sheetName);
if (sheet != null)
flag = true;
} catch (Exception e) {
throw e;
}
} else {
flag = false;
}
return flag;
}
/**
*创建新excel
*/
public static void createExcel(String fileDir, String sheetName, String titleRow[]) throws Exception {
workbook = new XSSFWorkbook();
FileOutputStream out = null;
try {
XSSFRow row = workbook.createSheet(sheetName).createRow(0);
for (int i = 0; i < titleRow.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
}
out = new FileOutputStream(fileDir);
workbook.write(out);
} catch (Exception e) {
throw e;
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
*往excel中写入mapList.size()行数据
*/
public static void writeToExcelRow(String fileDir, String sheetName, List<Map<String, String>> mapList, int rowId)
throws Exception {
File file = new File(fileDir);
try {
workbook = new XSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
FileOutputStream out = null;
XSSFSheet sheet = workbook.getSheet(sheetName);
// 获取表格的总行数
// introwCount = sheet.getLastRowNum() + 1; // 需要加一
// 获取表头的列数
int columnCount = sheet.getRow(0).getLastCellNum();
try {
XSSFRow titleRow = sheet.getRow(0);
if (titleRow != null) {
for (Map<String, String> map : mapList) {
XSSFRow newRow = sheet.createRow(rowId++);
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
String mapKey = titleRow.getCell(columnIndex).toString().trim();
XSSFCell cell = newRow.createCell(columnIndex);
cell.setCellValue(map.get(mapKey) == null ? null : map.get(mapKey).toString());
}
}
}
out = new FileOutputStream(fileDir);
workbook.write(out);
} catch (Exception e) {
throw e;
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
*往excel中写入一格数据
*/
public static void writeToExcelCell(String fileDir, String sheetName, String value, int rowId, int columnId)
throws Exception {
File file = new File(fileDir);
try {
workbook = new XSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
FileOutputStream out = null;
XSSFSheet sheet = workbook.getSheet(sheetName);
// 获取表格的总行数
// introwCount = sheet.getLastRowNum() + 1; // 需要加一
// 获取表头的列数
// intcolumnCount = sheet.getRow(0).getLastCellNum();
try {
XSSFRow row = sheet.getRow(rowId);
if (row != null) {
XSSFCell cell = row.getCell(columnId);
if (cell != null) {
cell.setCellValue(value == null ? null : value);
} else {
XSSFCell newCell = row.createCell(columnId);
newCell.setCellValue(value == null ? null : value);
}
}
out = new FileOutputStream(fileDir);
workbook.write(out);
} catch (Exception e) {
throw e;
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

测试

package excelTest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelTest {
public static void main(String[] args) throws Exception{
String excelFilePath="D:/test.xlsx";
String sheetName="sheet0";
String[] titleRow={"id", "name", "password"};
Map<String, String> map1=new HashMap<String, String>();
map1.put("id","1001");
map1.put("name", "anna");
map1.put("password", "ANNA");
Map<String, String> map2=new HashMap<String, String>();
map2.put("id","1002");
map2.put("name", "bob");
map2.put("password", "BOB");
List<Map<String, String>> mapList=new ArrayList<Map<String, String>>();
mapList.add(map1);
mapList.add(map2);
System.out.println(ExcelOperation.fileExist(excelFilePath));
ExcelOperation.createExcel(excelFilePath, sheetName, titleRow);
System.out.println(ExcelOperation.fileExist(excelFilePath));
ExcelOperation.writeToExcelRow(excelFilePath, sheetName, mapList, 1);//从表的第二行开始写
}
}