使用的jar如下图：
具体代码:
package com.wll.excelAll;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.DateUtil;
public class readExcel {
public static void main(String[] args) {
// 需要读取的文件
readExcel.read("lib/test.xlsx");
}
public static void read(String path) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
// 同时支持Excel 2003、2007、2010
File excelFile = new File(path); // 创建文件对象
FileInputStream is = new FileInputStream(excelFile); // 文件流
Workbook workbook = WorkbookFactory.create(is); // 支持03 07 10格式
int sheetCount = workbook.getNumberOfSheets(); // 工作簿sheet 的数量 默认3张
// 遍历每个Sheet
for (int i = 0; i < 1; i++) {
Connection connection = null;
connection = readExcel.getCon();// 获取数据库链接
StringBuffer ssBuffer = new StringBuffer();
String string = "";
Sheet sheet = workbook.getSheetAt(i);
int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数 13
// 遍历每一行
for (int r = 0; r < rowCount; r++) {
StringBuffer sBuffer = new StringBuffer("insert into test values('");
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数 4
// 遍历每一列
for (int c = 0; c < cellCount; c++) {
Cell cell = row.getCell(c);
int cellType = cell.getCellType();
String cellValue = null;
switch (cellType) {
case Cell.CELL_TYPE_STRING: // 文本 字符串型 1
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字、日期 数值型 0
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); // 日期型
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔型 布尔型 4
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: // 空白 空值 3
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: // 错误 错误 5
cellValue = "错误";
break;
case Cell.CELL_TYPE_FORMULA: // 公式型 2
cellValue = "公式";
break;
default:
cellValue = "错误";
}
System.out.print(cellValue + " ");
sBuffer.append(cellValue + "','");
}
System.out.println();
// 拼接执行的SQL语句
string = sBuffer.toString().substring(0, sBuffer.toString().lastIndexOf(","));
string = string + ");";
System.out.println("---sql:" + string);
Statement st = connection.createStatement();
st.execute(string);
ssBuffer.append(string);
}
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getCon() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.err.println("---error:驱动加载失败");
e.printStackTrace();
}
try {
connection = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "root");
} catch (SQLException e) {
System.err.println("---error:获取链接失败");
e.printStackTrace();
}
System.out.println("-----获取链接成功！");
return connection;
}
}
如果你喜欢此类文章，可以订阅我，我还会继续发类似文章的。谢谢----vsalw