本章使用jxl读取Execl表格,当然也可以用poi读取Execl
jxl 与poi 操作Excel区别
JXL: 只想生成一些大数据量可以考虑使用
POI: 功能比较复杂的情况下可以考虑使用,
一、导入依赖
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.6</version>
</dependency>
二、代码
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import java.io.*;
import java.nio.charset.StandardCharsets;
/**
* @Author: JackHuan
* @Date: 2020/12/26 14:34
* @Description: 读取Execl表,生成对应的JavaBean(注意:仅支持.xls)
* 读取后缀为.xlsx前需将文件另存为.xls
**/
public class ExeclUtil {
public static void main(String[] args) {
String inFile = "C:\\Users\\c-wuj7\\Documents\\我接收到的文件\\PV_20201226.xls";
String sheetName = "V00068";
//dto
String outFileDto = "C:\\Users\\c-wuj7\\Desktop\\aa\\dto.java";
ExeclRange dto = new ExeclRange(5, 12, 0, 1, 5);
//do
String outFileDo = "C:\\Users\\c-wuj7\\Desktop\\aa\\do.java";
ExeclRange domain = new ExeclRange(5, 12, 5);
//esbDto
String outFileEsbDto = "C:\\Users\\c-wuj7\\Desktop\\aa\\esbDto.java";
ExeclRange esbDto = new ExeclRange(5, 12, 5);
//调用方法依次为,dto,do,esbDto
readExeclToJava(inFile,sheetName,outFileDto,dto,false);
readExeclToJava(inFile,sheetName,outFileDo,domain,false);
readExeclToJava(inFile,sheetName,outFileEsbDto,esbDto,true);
}
/**
*
* @param inFile 读取的execl表格
* @param sheetName 工作簿名称
* @param outFile 输出的java文件
* @param execlRange 所读取Execl的区域
* @param flag true 为 esbDto, false为 dto、do
*/
private static void readExeclToJava(String inFile,String sheetName,String outFile,ExeclRange execlRange,boolean flag) {
Integer startRow = execlRange.startRow;
Integer endRow = execlRange.endRow;
Integer jsonProperty = execlRange.jsonProperty;
Integer apiModelProperty = execlRange.apiModelProperty;
Integer entity = execlRange.entity;
Workbook wb = null;
BufferedWriter out = null;
try {
wb = Workbook.getWorkbook(new FileInputStream(inFile));
out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), StandardCharsets.UTF_8));
//获取工作表
Sheet sheet = wb.getSheet(sheetName);
//一个表的总行数
int rowsTotal = sheet.getRows();
for (int i = 0 ; i < rowsTotal ; i++){
//自定义行
if ( i <= endRow && i >= startRow ){
Cell[] cells = sheet.getRow(i);
if (jsonProperty != null && apiModelProperty != null ) {
//生成dto
String first = cells[jsonProperty].getContents();
String second = cells[apiModelProperty].getContents();
String third = cells[entity].getContents();
if (third != null && !"".equals(third) && second != null && !"".equals(second) && first != null && !"".equals(first)){
String lowerEntity = third.substring(0, 1).toLowerCase() + third.substring(1);
String a = "@JsonProperty(\""+first+"\")";
String b = "@ApiModelProperty(\""+second+"\")";
String c = "private String "+lowerEntity + ";";
out.write(a.toCharArray());
out.newLine();
out.write(b.toCharArray());
out.newLine();
out.write(c.toCharArray());
out.newLine();
out.newLine();
out.flush();
}
}else if(jsonProperty == null && apiModelProperty == null && flag) {
//生成esbDto
String third = cells[entity].getContents();
if (third != null && !"".equals(third)){
String lowerEntity = third.substring(0, 1).toLowerCase() + third.substring(1);
String upperEntity = third.substring(0, 1).toUpperCase() + third.substring(1);
String b = "@JSONField(name = \""+ upperEntity +"\")";
String c = "private String "+lowerEntity + ";";
out.write(b.toCharArray());
out.newLine();
out.write(c.toCharArray());
out.newLine();
out.newLine();
out.flush();
}
}else {
//生成do
String third = cells[entity].getContents();
if (third != null && !"".equals(third)){
String lowerEntity = third.substring(0, 1).toLowerCase() + third.substring(1);
String c = "private String "+lowerEntity + ";";
out.write(c.toCharArray());
out.newLine();
out.newLine();
out.flush();
}
}
}
}
System.out.println("successful...");
} catch (IOException | BiffException e) {
System.out.println("fail...");
e.printStackTrace();
} finally {
if (out != null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (wb != null){
wb.close();
}
}
}
}
/**
* 所读取Execl的区域
*/
class ExeclRange{
/**
* startRow 起始行数
* endRow 终止行数
* jsonProperty 列位置1
* apiModelProperty 列位置2
* entity 列位置3
*/
Integer startRow;
Integer endRow;
Integer jsonProperty;
Integer apiModelProperty;
Integer entity;
public ExeclRange(int startRow, int endRow, int entity) {
this.startRow = startRow;
this.endRow = endRow;
this.entity = entity;
}
public ExeclRange(int startRow, int endRow, int jsonProperty, int apiModelProperty, int entity) {
this.startRow = startRow;
this.endRow = endRow;
this.jsonProperty = jsonProperty;
this.apiModelProperty = apiModelProperty;
this.entity = entity;
}
}
参考文章
Java文件写入换行方法