今天领导给安排了一个临时工作，让我将一个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中数据如图所示：
四：运行结果
