今天领导给安排了一个临时工作,让我将一个excel里面的数据解析后放入数据库中,经过一个下午的努力成功完成,现在将代码献上,希望对大家有所帮助

一、需要导入的jar

  1.commons-collections4-4.1.jar

  2.poi-3.17-beta1.jar

  3.poi-ooxml-3.17-beta1.jar

  4.poi-ooxml-schemas-3.17-beta1.jar

  5.xmlbeans-2.6.0.jar

对应的maven依赖如下:

<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>

 

二、主要API

  1.import org.apache.poi.ss.usermodel.Workbook,对应Excel文档;

  2.import org.apache.poi.hssf.usermodel.HSSFWorkbook,对应xls格式的Excel文档;

  3.import org.apache.poi.xssf.usermodel.XSSFWorkbook,对应xlsx格式的Excel文档;

  4.import org.apache.poi.ss.usermodel.Sheet,对应Excel文档中的一个sheet;

  5.import org.apache.poi.ss.usermodel.Row,对应一个sheet中的一行;

  6.import org.apache.poi.ss.usermodel.Cell,对应一个单元格。

三、对应带代码如下:

1.TestExcelToDb 类代码

package com.it.intelligent.list.dto;

import java.sql.*;
import java.util.List;

public class TestExcelToDb {

String url ="jdbc:postgresql://10.43.225.69:5432/it?currentSchema=it";
String user="11";
String pwd="11";
Connection conn=null;
ResultSet rs=null;

public void dataBase() {
try {
Class.forName("org.postgresql.Driver");
conn= DriverManager.getConnection(url, user, pwd);
} catch (ClassNotFoundException e) {
System.out.println("装载JDBC 驱动程序失败");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("无法连接数据库");
e.printStackTrace();
}
}

//增删修改

public int addU(String sql,String str[]) {
int a=0;
try {
PreparedStatement pst=conn.prepareStatement(sql);
if (str!=null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i+1, str[i]);
}
}
a=pst.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
}

return a;

}
public static void main(String[] args) {
//得到表格中所有的数据
List<TempData> listExcel=StuService.getAllByExcel("D:/l4/百货部分数据.xlsx");
TestExcelToDb testExcelToDb =new TestExcelToDb();
testExcelToDb.dataBase();
System.out.println(listExcel.size());
int sum=0;
for (TempData tempData : listExcel) {
String sql="insert into temp_data(l4_gds_grp_cd,l4_gds_grp_nm,gds_cd,vendor_cd,gds_nm)values(?,?,?,?,?)";
String[] str=new String[] {tempData.getL4GdsGrpCd(),tempData.getL4GdsGrpNm(),tempData.getGdsCd(),tempData.getVendorCd(),tempData.getGdsNm()+""};
testExcelToDb.addU(sql, str);
sum++;
}
System.out.println(sum);
}
}


2.StuService 代码
package com.it.intelligent.list.dto;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class StuService {
//查询指定目录中电子表格中所有的数据
public static List<TempData> getAllByExcel(String file) {
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List<TempData> list = null;
wb = readExcel(file);
if (wb != null) {
//用来存放表中数据
list =new ArrayList<>();
//获取第一个sheet
sheet = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
Cell cell = row.getCell(j);
String cellValue = cell.getRichStringCellValue().getString();
String[] strings = cellValue.split("#NLP#");
System.out.println(strings.length);
TempData tempData = new TempData();
tempData.setL4GdsGrpCd(strings[0]);
tempData.setL4GdsGrpNm(strings[1]);
tempData.setGdsCd(strings[2]);
tempData.setVendorCd(strings[4]);
tempData.setGdsNm(strings[3]);
list.add(tempData);
}
} else {
break;
}
}
}
return list;
}

//读取excel
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
} else {
return wb = null;
}

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
}

3.TempData 代码
package com.it.intelligent.list.dto;

public class TempData {
private String l4GdsGrpCd;
private String l4GdsGrpNm;
private String gdsCd;
private String vendorCd;
private String categoryCd;
private String categoryNm;
private String gdsNm;
private String listWords;
private String updateTime;
private String etlTime;

public String getL4GdsGrpCd() {
return l4GdsGrpCd;
}

public void setL4GdsGrpCd(String l4GdsGrpCd) {
this.l4GdsGrpCd = l4GdsGrpCd;
}

public String getL4GdsGrpNm() {
return l4GdsGrpNm;
}

public void setL4GdsGrpNm(String l4GdsGrpNm) {
this.l4GdsGrpNm = l4GdsGrpNm;
}

public String getGdsCd() {
return gdsCd;
}

public void setGdsCd(String gdsCd) {
this.gdsCd = gdsCd;
}

public String getVendorCd() {
return vendorCd;
}

public void setVendorCd(String vendorCd) {
this.vendorCd = vendorCd;
}

public String getCategoryCd() {
return categoryCd;
}

public void setCategoryCd(String categoryCd) {
this.categoryCd = categoryCd;
}

public String getCategoryNm() {
return categoryNm;
}

public void setCategoryNm(String categoryNm) {
this.categoryNm = categoryNm;
}

public String getGdsNm() {
return gdsNm;
}

public void setGdsNm(String gdsNm) {
this.gdsNm = gdsNm;
}

public String getListWords() {
return listWords;
}

public void setListWords(String listWords) {
this.listWords = listWords;
}

public String getUpdateTime() {
return updateTime;
}

public void setUpdateTime(String updateTime) {
this.updateTime = updateTime;
}

public String getEtlTime() {
return etlTime;
}

public void setEtlTime(String etlTime) {
this.etlTime = etlTime;
}
}

4.excel中数据如图所示:


四:运行结果