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;

}

}