導(dǎo)入Jar包：poi 和 mysql-connector-java
poi
mysql-connector-java
poi下載地址：
http://mirrors.hust.edu.cn/apache/poi/dev/bin/poi-bin-3.16-beta2-20170202.zip
mysql-connector-java下載地址：
https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.41.zip
轉(zhuǎn)換均采用以下兩圖的內(nèi)容
數(shù)據(jù)庫(kù)表：
數(shù)據(jù)庫(kù)表
Excel（workbook.xls）：
workbook.xls
數(shù)據(jù)庫(kù)轉(zhuǎn)Excel：
- 核心代碼：
//初始化excel文件
HSSFWorkbook wb = new HSSFWorkbook();//excel文件對(duì)象
HSSFSheet sheet = wb.createSheet("sheet");//表
sheet.setDefaultColumnWidth(20);//設(shè)置默認(rèn)列寬為20
HSSFRow row0 = sheet.createRow(0);//創(chuàng)建第一行
row0.createCell(0).setCellValue("id");//第一行第一列
FileOutputStream outputStream = new FileOutputStream("workbook.xls");
wb.write(outputStream);
outputStream.flush();
- 全部代碼：
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {
//數(shù)據(jù)庫(kù)變量
String driver = "com.mysql.jdbc.Driver";
String dvUrl = "jdbc:mysql://localhost:3306/";
String user = "root";
String password = "root";
String database = "test";
Connection conn;
PreparedStatement pre;
String sql;
//初始化數(shù)據(jù)庫(kù)
Class.forName(driver);
conn = DriverManager.getConnection(dvUrl + database, user, password);
//初始化excel文件
HSSFWorkbook wb = new HSSFWorkbook();//excel文件對(duì)象
HSSFSheet sheet = wb.createSheet("sheet");//表
sheet.setDefaultColumnWidth(20);//設(shè)置默認(rèn)列寬為20
HSSFRow row0 = sheet.createRow(0);//創(chuàng)建第一行
row0.createCell(0).setCellValue("id");//第一行第一列
row0.createCell(1).setCellValue("title");第一行第二列
row0.createCell(2).setCellValue("href");第一行第三列
row0.createCell(3).setCellValue("author");第一行第四列
row0.createCell(4).setCellValue("time");第一行第五列
sql = "select * from blog";//查詢blog表
pre = conn.prepareStatement(sql);
ResultSet rs = pre.executeQuery();//
int row_count = 1;
while (rs.next()) {//遍歷表內(nèi)容
String id = rs.getString("id");
String title = rs.getString("title");
String href = rs.getString("href");
String author = rs.getString("author");
String time = rs.getString("time");
HSSFRow row = sheet.createRow(row_count);
row.createCell(0).setCellValue(id);
row.createCell(1).setCellValue(title);
row.createCell(2).setCellValue(href);
row.createCell(3).setCellValue(author);
row.createCell(4).setCellValue(time);
row_count++;
}
FileOutputStream outputStream = new FileOutputStream("workbook.xls");
wb.write(outputStream);
outputStream.flush();
}
Excel轉(zhuǎn)數(shù)據(jù)庫(kù)：
- 核心代碼：
File file = new File("workbook.xls");
FileInputStream fileInputStream = new FileInputStream(file);
//根據(jù)指定的文件輸入流導(dǎo)入Excel從而產(chǎn)生Workbook對(duì)象
Workbook wb = new HSSFWorkbook(fileInputStream);
//獲取Excel文檔中的第一個(gè)表單
Sheet sheet = wb.getSheetAt(0);
//對(duì)Sheet中的每一行進(jìn)行遍歷
for (Row row : sheet) {
//跳過(guò)第一行標(biāo)題
if (row.getRowNum() == 0) {
continue;
}
ExcelBean excelBean = new ExcelBean();
excelBean.setId(row.getCell(0).getStringCellValue());//第一行第一列的值
}
- 全部代碼：
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
//數(shù)據(jù)庫(kù)變量
String driver = "com.mysql.jdbc.Driver";
String dvUrl = "jdbc:mysql://localhost:3306/";
String user = "root";
String password = "root";
String database = "test";
Connection conn;
PreparedStatement pre;
String sql;
ArrayList<ExcelBean> excelBeans = new ArrayList<>();
//初始化數(shù)據(jù)庫(kù)
Class.forName(driver);
conn = DriverManager.getConnection(dvUrl + database, user, password);
pre = conn.prepareStatement("SHOW TABLES LIKE 'excel'");
ResultSet rs = pre.executeQuery();
if (rs.next()) {
//表存在
sql = "truncate table excel";
} else {
//表不存在
sql = "CREATE TABLE excel(" +
"id varchar(255)," +
"title varchar(255)," +
"href varchar(255)," +
"author varchar(255)," +
"time varchar(255)" +
") ENGINE=MyISAM DEFAULT CHARSET=utf8";
}
pre = conn.prepareStatement(sql);
pre.execute();
File file = new File("workbook.xls");
FileInputStream fileInputStream = new FileInputStream(file);
//根據(jù)指定的文件輸入流導(dǎo)入Excel從而產(chǎn)生Workbook對(duì)象
Workbook wb = new HSSFWorkbook(fileInputStream);
//獲取Excel文檔中的第一個(gè)表單
Sheet sheet = wb.getSheetAt(0);
//對(duì)Sheet中的每一行進(jìn)行遍歷
for (Row row : sheet) {
//跳過(guò)第一行標(biāo)題
if (row.getRowNum() == 0) {
continue;
}
ExcelBean excelBean = new ExcelBean();
excelBean.setId(row.getCell(0).getStringCellValue());
excelBean.setTitle(row.getCell(1).getStringCellValue());
excelBean.setHref(row.getCell(2).getStringCellValue());
excelBean.setAuthor(row.getCell(3).getStringCellValue());
excelBean.setTime(row.getCell(4).getStringCellValue());
excelBeans.add(excelBean);
}
//遍歷集合將數(shù)據(jù)插入到數(shù)據(jù)庫(kù)
for (ExcelBean eb : excelBeans) {
sql = "INSERT INTO excel(id,title,href,author,time) " +
"VALUES ('" +
eb.getId() + "','" +
eb.getTitle() + "','" +
eb.getHref() + "','" +
eb.getAuthor() + "','" +
eb.getTime() + "')";
pre = conn.prepareStatement(sql);
pre.execute();
}
pre.close();
conn.close();
}
public class ExcelBean {
private String id;
private String title;
private String href;
private String author;
private String time;
}
