java如何读取、写入Excel文件(将内容转成任意的bean对象)
将StudentInfo.xlsx里的内容读取出来并封装到StudentInfo类的对象中
利用反射可以很好地实现对任意类的数据封装。实现如下
依赖:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
public class ExcelUtil {
public static <T> List<T> parseFromExcel(String path, Class<T> aimClass) {
return parseFromExcel(path, 0, aimClass);
}
@SuppressWarnings("deprecation")
public static <T> List<T> parseFromExcel(String path, int firstIndex, Class<T> aimClass) {
List<T> result = new ArrayList<T>();
try {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
//对excel文档的第一页,即sheet1进行操作
Sheet sheet = workbook.getSheetAt(0);
int lastRaw = sheet.getLastRowNum();
for (int i = firstIndex; i < lastRaw; i++) {
//第i行
Row row = sheet.getRow(i);
T parseObject = aimClass.newInstance();
Field[] fields = aimClass.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
field.setAccessible(true);
Class<?> type = field.getType();
//第j列
Cell cell = row.getCell(j);
if (cell == null)
continue;
//很重要的一行代码,如果不加,像12345这样的数字是不会给你转成String的,只会给你转成double,而且会导致cell.getStringCellValue()报错
cell.setCellType(Cell.CELL_TYPE_STRING);
String cellContent = cell.getStringCellValue();
cellContent = "".equals(cellContent) ? "0" : cellContent;
if (type.equals(String.class)) {
field.set(parseObject, cellContent);
} else if (type.equals(char.class) || type.equals(Character.class)) {
field.set(parseObject, cellContent.charAt(0));
} else if (type.equals(int.class) || type.equals(Integer.class)) {
field.set(parseObject, Integer.parseInt(cellContent));
} else if (type.equals(long.class) || type.equals(Long.class)) {
field.set(parseObject, Long.parseLong(cellContent));
} else if (type.equals(float.class) || type.equals(Float.class)) {
field.set(parseObject, Float.parseFloat(cellContent));
} else if (type.equals(double.class) || type.equals(Double.class)) {
field.set(parseObject, Double.parseDouble(cellContent));
} else if (type.equals(short.class) || type.equals(Short.class)) {
field.set(parseObject, Short.parseShort(cellContent));
} else if (type.equals(byte.class) || type.equals(Byte.class)) {
field.set(parseObject, Byte.parseByte(cellContent));
} else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
field.set(parseObject, Boolean.parseBoolean(cellContent));
}
}
result.add(parseObject);
}
fis.close();
return result;
} catch (
Exception e) {
e.printStackTrace();
System.err.println("An error occured when parsing object from Excel. at " + this.getClass());
}
return result;
}
public static void main(String[] args) throws Exception {
//参数里的5表示有效行数从第5行开始
List<StudentInfo> studentInfos = ExcelUtil.parseFromExcel("C:\\Users\\unive\\Desktop\\StudentInfo.xlsx", 5,
StudentInfo.class);
for (int i = 0; i < studentInfos.size(); i++) {
System.err.println(studentInfos.get(i).toString());
}
}
}
优化:
public class ExcelUtil {
// 带标题写入Excel
public static <T> void writeExcelWithTitle(List<T> beans, String path) {
writeExcel(beans,path,true);
}
// 仅把数据写入Excel
public static <T> void writeExcel(List<T> beans, String path) {
writeExcel(beans,path,false);
}
private static <T> void writeExcel(List<T> beans, String path, boolean writeTitle) {
if(beans == null || beans.size() == 0) return;
Workbook workbook = new HSSFWorkbook();
FileOutputStream fos = null;
int offset = writeTitle ? 1 : 0;
try {
Sheet sheet = workbook.createSheet();
for (int i = 0; i < beans.size() + offset; ++i) {
if(writeTitle && i == 0) {createTitle(beans, sheet);continue;}
Row row = sheet.createRow(i);
T bean = beans.get(i - offset);
Field[] fields = bean.getClass().getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
field.setAccessible(true);
Cell cell = row.createCell(j);
//Date,Calender都可以 使用 +"" 操作转成字符串
cell.setCellValue(field.get(bean)+"");
}
}
fos = new FileOutputStream(path);
workbook.write(fos);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
fos.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
private static <T> void createTitle(List<T> beans,Sheet sheet){
Row row = sheet.createRow(0);
T bean = beans.get(0);
Field[] fields = bean.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
field.setAccessible(true);
Cell cell = row.createCell(i);
cell.setCellValue(field.getName());
}
}
public static void main(String[] args) {
// scenics这里是自定义景点集合, 不用管
// 带标题写入excel
ExcelUtil.writeExcelWithTitle(scenics,
"C:\\Users\\unive\\Documents\\景点信息\\scenics.xlsx");
// 不带标题写入excel
ExcelUtil.writeExcel(scenics,
"C:\\Users\\unive\\Documents\\景点信息\\scenicsWithoutTitle.xlsx");
}
}
带标题写入excel的结果:
不带标题写入的结果:
注意:
直接将生成的excel通过navicat导入数据库会报错,因为生成的excel似乎没有官方的那么标准,所以解决办法是新建一个excel文件,把数据复制过来,再让navicat把这个新建的excel导入到数据库即可。