excel文件存入mysql_解析excel文件并将数据导入到数据库中

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

一、需要导入的jar

mons-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依赖如下：

net.sourceforge.jexcelapi

jxl

2.6.12

mons

commons-collections4

4.1

org.apache.poi

poi

3.17-beta1

org.apache.poi

poi-ooxml-schemas

3.17-beta1

org.apache.xmlbeans

xmlbeans

2.6.0

org.apache.poi

poi-ooxml

3.17-beta1

二、主要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 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 getAllByExcel(String file) {

Workbook wb = null;

Sheet sheet = null;

Row row = null;

List 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中数据如图所示：

四：运行结果