时间：2021-07-01 10:21:17 帮助过：8人阅读
TSysDate g=oilsManager.getByDname(gys);
if(g==null){
g=new TSysDate();
g.setPid(Long.parseLong("51"));
g.setIsDel(Variable.UN_DEL);
g.setDname(gys);
oilsManager.doSave(g);
}
//添加加油港
String jyg=e.getJyg();
TSysDate ts=oilsManager.getByDname(jyg);
if(ts==null){
ts=new TSysDate();
ts.setPid(Long.parseLong("48"));
ts.setIsDel(Variable.UN_DEL);
ts.setDname(jyg);
oilsManager.doSave(ts);
}
Oils o=new Oils();
//e就是代表从list中循环出来的每个对象，从e对象中获取数据放到需//要放的实体类中
o.setAsh(e.getAsh()); //灰分
o.setDensity(e.getDensity()); //密度
o.setFlash(e.getFlash()); //闪点
o.setSulphur(e.getSulphur()); //硫
o.setViscosity(e.getViscosity()); //粘度
o.setSodium(e.getSodium()); //钠
o.setDensity(e.getDensity()); //密度
o.setBay(ts); //加油港
o.setSupplier(g); //供油商
o.setIsDel(Variable.UN_DEL);
oilsManager.doSave(o);
}
}
System.out.println("导入Excel文件[成功]");
}
详解：
第一步后台获取页面中上传的文件，并进行一一注解，从第一列开始起个别名。切记要在项目中创建一个新的实体类newModel，并将每个字段设置set、get方法。新的实体类newModel对应图片中的内容。
第二步进行解析，如下方法
public List<T> parse2(String filePath,int beginRow) throws Exception {
List<T> rets = new ArrayList<T>();
// 提取 column
Workbook wb = null;
fis = new FileInputStream(new File(filePath));
wb = create(fis);
if(wb==null){
return null;
}
// 获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
sheet = wb.getSheetAt(this.sheetNum);
// System.out.println("row-size="+sheet.getLastRowNum());
for(int i=beginRow;i<=sheet.getLastRowNum();i++){
Row row=sheet.getRow(i);
// System.out.println("row="+i);
Object b = beanClass.newInstance();
for (Integer idx : fieldDesc.keySet()) {
// System.out.println("idx="+idx);
row.getCell(idx).setCellType(Cell.CELL_TYPE_STRING);
Cell cell=row.getCell(idx);
// System.out.println(fieldDesc.get(idx)+"---;"+cell.getStringCellValue());
if (resolver.containsKey(fieldDesc.get(idx))) {
if(cell.getStringCellValue()!=null&&!"".equals(cell.getStringCellValue())){
Date ret = resolver.get(fieldDesc.get(idx)).parse(cell.getStringCellValue());
BeanUtils.setProperty(b, fieldDesc.get(idx),ret);
}else{
BeanUtils.setProperty(b, fieldDesc.get(idx),new Date());
}
}else{
if(cell.getStringCellValue()==null){
BeanUtils.setProperty(b, fieldDesc.get(idx),null);
}else{
BeanUtils.setProperty(b, fieldDesc.get(idx),cell.getStringCellValue());
}
}
}
rets.add((T) b);
}
return rets;
}
解析完成后，会获得一个list,将list循环并插入到实体类中对应的数据中。
以下是我这次导入时所建的所有类，以上内容如果看不太懂的话，可以直接复制之后自己研究
以下为上文中所谓的newModel，为导入Excel表格所建的实体类，有些字段是多余的，可以不要。
public class Oils1 implements Serializable{
private String oilsname; //油品名称
private String ypzl; //油品种类
private String yycd; //原油产地
private String jyg; //加油港
private String gys; //供油商
private Double sulphur; //含硫量
private Double viscosity; //粘度
private Double ash; //灰分
private Double sodium; //钠含量
private Double vanadium; //钒含量
private Double density; //密度
private Double heat; //热值
private Double flash; //闪点
private Double pour; //倾点
private Double pitch; //沥青分
private Double cetane; //十六烷值
private Double diesel; //柴油指数
private Double cloub; //浊点
public String getOilsname() {
return oilsname;
}
public String getYycd() {
return yycd;
}
public void setYycd(String yycd) {
this.yycd = yycd;
}
public String getJyg() {
return jyg;
}
public void setJyg(String jyg) {
this.jyg = jyg;
}
public String getGys() {
return gys;
}
public void setGys(String gys) {
this.gys = gys;
}
public void setOilsname(String oilsname) {
this.oilsname = oilsname;
}
public Double getSulphur() {
return sulphur;
}
public String getYpzl() {
return ypzl;
}
public void setYpzl(String ypzl) {
this.ypzl = ypzl;
}
public void setSulphur(Double sulphur) {
this.sulphur = sulphur;
}
public Double getViscosity() {
return viscosity;
}
public void setViscosity(Double viscosity) {
this.viscosity = viscosity;
}
public Double getAsh() {
return ash;
}
public void setAsh(Double ash) {
this.ash = ash;
}
public Double getSodium() {
return sodium;
}
public void setSodium(Double sodium) {
this.sodium = sodium;
}
public Double getVanadium() {
return vanadium;
}
public void setVanadium(Double vanadium) {
this.vanadium = vanadium;
}
public Double getDensity() {
return density;
}
public void setDensity(Double density) {
this.density = density;
}
public Double getHeat() {
return heat;
}
public void setHeat(Double heat) {
this.heat = heat;
}
public Double getFlash() {
return flash;
}
public void setFlash(Double flash) {
this.flash = flash;
}
public Double getPour() {
return pour;
}
public void setPour(Double pour) {
this.pour = pour;
}
public Double getPitch() {
return pitch;
}
public void setPitch(Double pitch) {
this.pitch = pitch;
}
public Double getCetane() {
return cetane;
}
public void setCetane(Double cetane) {
this.cetane = cetane;
}
public Double getDiesel() {
return diesel;
}
public void setDiesel(Double diesel) {
this.diesel = diesel;
}
public Double getCloub() {
return cloub;
}
public void setCloub(Double cloub) {
this.cloub = cloub;
}
}
以下为导入Excel表格所建的的一个类，建一个ImportExcel类，然后直接将以下方法复制粘贴进去即可。
public class ImportExcel<T> {
private Map<Integer, String> fieldDesc = new HashMap<Integer, String>();
private Class<T> beanClass;
private InputStream fis = null;
private Sheet sheet = null;
private int sheetNum=0;
//解析器
private Map<String, ExcelDateResolver> resolver = new LinkedHashMap<String, ExcelDateResolver>();
public ImportExcel(Class<T> c) {
beanClass = c;
}
public List<T> parse(String filePath,int beginRow) throws Exception {
List<T> rets = new ArrayList<T>();
// 提取 column
Workbook wb = null;
fis = new FileInputStream(new File(filePath));
wb = create(fis);
if(wb==null){
return null;
}
// 获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
sheet = wb.getSheetAt(this.sheetNum);
for(int i=beginRow;i<=sheet.getLastRowNum();i++){
Row row=sheet.getRow(i);
Object b = beanClass.newInstance();
for (Integer idx : fieldDesc.keySet()) {
row.getCell(idx).setCellType(Cell.CELL_TYPE_STRING);
Cell cell=row.getCell(idx);
if (resolver.containsKey(fieldDesc.get(idx))) {
if(cell.getStringCellValue()!=null&&!"".equals(cell.getStringCellValue())){
Date ret = resolver.get(fieldDesc.get(idx)).parse(cell.getStringCellValue());
BeanUtils.setProperty(b, fieldDesc.get(idx),ret);
}else{
BeanUtils.setProperty(b, fieldDesc.get(idx),new Date());
}
}else{
BeanUtils.setProperty(b, fieldDesc.get(idx),cell.getStringCellValue());
}
}
rets.add((T) b);
}
return rets;
}
public List<T> parse2(String filePath,int beginRow) throws Exception {
List<T> rets = new ArrayList<T>();
// 提取 column
Workbook wb = null;
fis = new FileInputStream(new File(filePath));
wb = create(fis);
if(wb==null){
return null;
}
// 获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
sheet = wb.getSheetAt(this.sheetNum);
// System.out.println("row-size="+sheet.getLastRowNum());
for(int i=beginRow;i<=sheet.getLastRowNum();i++){
Row row=sheet.getRow(i);
// System.out.println("row="+i);
Object b = beanClass.newInstance();
for (Integer idx : fieldDesc.keySet()) {
// System.out.println("idx="+idx);
row.getCell(idx).setCellType(Cell.CELL_TYPE_STRING);
Cell cell=row.getCell(idx);
// System.out.println(fieldDesc.get(idx)+"---;"+cell.getStringCellValue());
if (resolver.containsKey(fieldDesc.get(idx))) {
if(cell.getStringCellValue()!=null&&!"".equals(cell.getStringCellValue())){
Date ret = resolver.get(fieldDesc.get(idx)).parse(cell.getStringCellValue());
BeanUtils.setProperty(b, fieldDesc.get(idx),ret);
}else{
BeanUtils.setProperty(b, fieldDesc.get(idx),new Date());
}
}else{
if(cell.getStringCellValue()==null){
BeanUtils.setProperty(b, fieldDesc.get(idx),null);
}else{
BeanUtils.setProperty(b, fieldDesc.get(idx),cell.getStringCellValue());
}
}
}
rets.add((T) b);
}
return rets;
}
public Workbook create(InputStream inp) throws IOException {
if (!inp.markSupported()) {
inp = new PushbackInputStream(inp, 8);
}
if (POIFSFileSystem.hasPOIFSHeader(inp)) {
return new HSSFWorkbook(inp);
}
throw new IllegalArgumentException("你的excel版本目前poi解析不了");
}
public void setFieldDesc(Map<Integer, String> fieldDesc) {
this.fieldDesc = fieldDesc;
}
public void setResolver(Map<String, ExcelDateResolver> resolver) {
this.resolver = resolver;
}
将Excel上千条数据写入到数据库中
标签：别名 stream irf 不能 ant 粘贴 .com 标签 creat