java导入excel数据到数据库, 并生成sql文件
- 需求
- 使用的技术
- Jakarta POI
- 依赖
- 创建对接及生成类
- 实体类
- 用上的工具类
- 实现类
- 导入数据库
- Navicat Premium 连的数据库
需求
- 导入含父子关系的数据表格到数据库
- 生成SQL语句并生成SQL格式文件
- 需要导入的表格
使用的技术
Jakarta POI
- Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成,其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF,在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html,API文档http://poi.apache.org/apidocs/index.html;
- 环境配置
2.1 .1下载jar
官方下载:http://poi.apache.org/download.html 这里可以下载到它的最新版本和文档,目前最新版本是3.7。
2.1.2加入jar包
(这里指得是3.6版本) 将根目录下的poi-3.6-20091214.jar和Lib目录下三个通用包 commons-logging-1.1.jar junit-3.8.1.jar log4j-1.2.13.jar拷贝到项目的Lib下
依赖
//pom 文件导入jar包, 这里用3.7版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
创建对接及生成类
实体类
- 在数据库创建对应excel表格的各字段, 且写实体类
import javax.persistence.*;
@Entity
@Table(name = "bank_menu")
public class BankMenu {
@Id
@Column(name = "bank_id", nullable = false)
private Integer bankId;
private String bankName;
private Integer parentId;
private Integer sort;
private Integer status;
private Integer level;
....//GetterAndSetter
}
用上的工具类
public class StringUtil {
public static Integer parseInt(Object str) {
return str == null ? 0 : Integer.valueOf((isNumeric(str.toString())) ? Integer.parseInt(str.toString()) : 0);
}
}
实现类
- main方法运行
import com.linggo.util.StringUtil;
import com.user.service.dao.entity.BankMenu;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;
public class ImportExcel {
public static void executeInfo() throws IOException{
String filePath = "D:/Document/testList.xlsx"; //指定本地的数据目录
String slqPath = "D:/Document/TestSql.sql"; //指定生成文件目录
try {
InputStream in = new FileInputStream(filePath);
Workbook wb = new XSSFWorkbook(in);
in.close();
String sql = "";
Sheet sheetAt = wb.getSheetAt(0); // 获取excel中的第一张表格
int lastRowNum = sheetAt.getLastRowNum(); // 获取最后一行
Map map = new HashMap();
sql = "";
List<BankMenu> excelBean = getExcelBean(sql, sheetAt, lastRowNum); //调类中方法
Map<String,String> map1 =new HashMap<>();
for(BankMenu bean :excelBean){
map1.put("id",bean.getBankId().toString());
map1.put("bankName",bean.getBankName());
map1.put("parentId",bean.getParentId().toString());
map1.put("sort",bean.getSort().toString());
map1.put("banklevel",bean.getLevel().toString());
map1.put("bankStatus",bean.getStatus().toString());
Iterator<Map.Entry<String, String>> it = map1.entrySet().iterator();
Map.Entry<String, String> entry = it.next();
System.out.println("key= " + entry.getKey() + " and value= " + entry.getValue());
sql += String.format("INSERT IGNORE INTO `bank_menu` (`bank_id`, `bank_or_branch_name`, `bank_parent_id`, `bank_sort`,`bank_status`,`bank_level`) VALUES (" + map1.get("id")+",'" +map1.get("bankName")+ "'," +map1.get("parentId")+ ","+ map1.get("sort")+","+ map1.get("banklevel")+","+map1.get("bankStatus")+");\n");
System.out.println(map1);
System.out.println("======================");
}
File file = new File(slqPath);
if(!file.exists()){
file.createNewFile();
}
byte[] bytes = sql.getBytes();
OutputStream os = new FileOutputStream(slqPath);
os.write(bytes);
os.flush();
os.close();
System.out.println("sheets");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private static List<BankMenu> getExcelBean(String sql, Sheet sheetAt, int lastRowNum) {
List<BankMenu> list = new ArrayList<>();
DataFormatter formatter = new DataFormatter();
for (int rowNum=1; rowNum <= lastRowNum; rowNum++){ //从第二行开始拿
BankMenu bean = new BankMenu(); //创建一个单独Bean、
System.out.println("rowNum = "+rowNum);
Row row = sheetAt.getRow(rowNum);
Integer id = null;
String bankName = "";
Integer parentId = null;
Integer sort = null;
Integer bankLevel = null;
Integer bankStatus = null;
if (row != null){
Cell cell = row.getCell(0); // 获取对应的列的单元格, 这里是从第一列开始;
if (cell != null) {
String value1 = formatter.formatCellValue(cell);
id = StringUtil.parseInt(value1);
bean.setBankId(id);
}
cell = row.getCell(1); // 第二列, 以此类推, 有多少取多少
if (cell != null) {
bankName = cell.getStringCellValue();
bean.setBankName(bankName);
}
cell = row.getCell(2);
if (cell != null) {
parentId = StringUtil.parseInt(formatter.formatCellValue(cell));
bean.setParentId(parentId);
}
cell = row.getCell(3);
if (cell != null) {
sort = StringUtil.parseInt(formatter.formatCellValue(cell));
bean.setSort(sort);
}
cell = row.getCell(4);
if (cell != null) {
bankLevel = StringUtil.parseInt(formatter.formatCellValue(cell));
bean.setLevel(bankLevel);
}
cell = row.getCell(5);
if (cell != null) {
bankStatus = StringUtil.parseInt(formatter.formatCellValue(cell));
bean.setStatus(bankStatus);
}
/**
* 更多列在这里补充
*/
}
list.add(bean);
}
return list;
}
public static void main(String args[]) throws IOException {
long startTime = System.currentTimeMillis();
executeInfo();
long endTime = System.currentTimeMillis();
System.out.println("耗时为m:" + (endTime - startTime)/1000);
}
}
- 运行的结果:
由于我这数据比较多, 运行的时间就长一点;
2.1打印台
2.2 生成出来的文件
导入数据库
Navicat Premium 连的数据库
- 我用Navicat 直接运行生成的SQL文件, 最终实现导入excel数据到数据库
- 打开Navicat, 选择某个库里面的表, 右键 点运行SQL文件
- 进入这个界面, 选择生成的SQL文件, 然后点开始
我当时弄这个花了点时间, 特此记录下来, 以后可以参考; 欢迎交流 !