Excel格式:
代码:
package com.mingwen.imagetest;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 com.google.gson.Gson;
public class Excel {
static Gson gs = new Gson();
static Connection conn = null;
static String sql;
static Statement stmt;
static ResultSet rs = null;
public static void main(String[] args) {
try {
abc();
readExcel();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void abc() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://60.168.130.76:3306/gudi?characterEncoding=utf8&useSSL=true", "数据库账号",
"数据库密码");
stmt = conn.createStatement();
} catch (Exception e) {
}
}
public static void readExcel() throws SQLException {
Workbook wb = null;
Sheet sheet = null;
Row row = null;
String cellData = null;
String filePath = "C:\\Users\\Administrator\\Desktop\\12.xlsx";
wb = readExcel(filePath);
if (wb != null) {
// 获取第一个sheet
sheet = wb.getSheetAt(0);
// 获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
// 获取第一行
row = sheet.getRow(0);
// 获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
String a = (String) getCellFormatValue(row.getCell(j + 1));
j = j + 1;
// map.put(a, cellData);
String sql2 = "SELECT * FROM `express_code` where name =\"" + cellData + "\"";
rs = stmt.executeQuery(sql2);
List<Integer> levels = new ArrayList<Integer>();
while (rs.next()) {
levels.add(rs.getInt("id"));
}
if (levels.size() > 0) {
for (Integer integer : levels) {
sql = "update express_code set kdniao=\"" + a + "\" where id=\"" + integer + "\"";
stmt.execute(sql);
}
} else {
sql = "INSERT INTO express_code(kdniao,name) VALUES(\"" + a + "\",\"" + cellData + "\")";
stmt.execute(sql);
}
}
} else {
break;
}
}
}
}
// 读取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;
}
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
// 判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
// 数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
}