将Excel表格多个Sheet表的数据转换为SQL存储语句
如:将
转换为:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class Excel2Sql {
private static final String FILE_PATH = "E:\\data\\test.xls";
private static String WRITE_FILE_PATH = "E:\\data\\sql\\";
private static POIFSFileSystem fs;
//HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls
//XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx
private static HSSFWorkbook wb;
private static HSSFSheet sheet;
private static HSSFRow row;
public static void main(String[] args) throws FileNotFoundException {
InputStream is = new FileInputStream(FILE_PATH);
readExcel(is);
}
public static void readExcel(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}
System.out.println("--- start ---");
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
sheet = wb.getSheetAt(i);
String sheetName = sheet.getSheetName();
if (sheetName.equals("品牌")) {
String table = "mt_brand";
sheet = wb.getSheetAt(i);
connectSql(table);
} else if (sheetName.equals("系列")) {
String table = "mt_series";
sheet = wb.getSheetAt(i);
connectSql(table);
} else if (sheetName.equals("模型")) {
String table = "mt_model";
sheet = wb.getSheetAt(i);
connectSql(table);
} else if (sheetName.equals("省市")) {
String table = "mt_city";
sheet = wb.getSheetAt(i);
connectSql(table);
} else {
System.out.println("不需要对 \"" + sheetName + "\" 表进行处理!");
}
}
System.out.println("--- process end ---");
}
private static void connectSql(String table) {
//处理表头数据
String[] titleResult = readExcelTitle(sheet);
String titleSb = connectTitle(titleResult, table);
//生成sql
String createTableSql = connectCreatTable(titleResult, table);
createTableSql = createTableSql.replace(",\r\n)", "\r\n)");
//处理表内容数据
String contentResult = readExcelContent(sheet);
String[] contents = contentResult.split("---");
List<Map<Integer, String>> contentList = new ArrayList<>();
int count = contents.length;
for (int j = 0; j < count; j++) {
Map<Integer, String> map = new LinkedHashMap<>();
String values = "\r\nVALUES \r\n";
StringBuilder insertSb = new StringBuilder();
insertSb.append(titleSb);
insertSb.append(values);
insertSb.append(contents[j]);
String insert = insertSb.toString();
insert = insert.substring(0, insert.lastIndexOf(",")) + ";";
map.put(j, insert);
contentList.add(map);
}
writeStringToFile(table,createTableSql, contentList, count);
}
private static void writeStringToFile(String table, String createTableSql, List<Map<Integer, String>> contentList,
int count) {
try {
String filePath = WRITE_FILE_PATH + table + ".sql";
File file = new File(filePath);
PrintStream ps = new PrintStream(new FileOutputStream(file));
ps.println(createTableSql);// 往文件里写入字符串
String otherSql = null;
for (int i = 0; i < contentList.size(); i++) {
Map<Integer, String> map = contentList.get(i);
otherSql = map.get(i);
ps.append(otherSql + "\r\n\r\n");// 在已有的基础上添加字符串
}
System.out.println("Has been written to " + filePath);
ps.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
private static String connectCreatTable(String[] title, String table) {
String start = "CREATE TABLE " + table + "(\r\n";
String end = ") ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n";
StringBuilder sb = new StringBuilder();
sb.append(start);
for (String filed : title) {
if (filed.contains("id") || filed.contains("_year")) {
sb.append(filed);
sb.append(" int(11) NOT NULL,\r\n");
} else if (filed.contains("name")) {
sb.append(filed);
sb.append(" varchar(100) NOT NULL,\r\n");
} else {
sb.append(filed);
sb.append(" varchar(50) NOT NULL,\r\n");
}
}
sb.append(end);
return sb.toString();
}
private static String readExcelContent(HSSFSheet sheetData) {
Map<Integer, String> content = new HashMap<Integer, String>();
String data = "";
int rowNum = sheetData.getLastRowNum();// 得到总行数
row = sheetData.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
for (int i = 1; i <= rowNum; i++) {// 正文内容应该从第二行开始,第一行为表头的标题
row = sheetData.getRow(i);
data +="(";
int j = 0;
while (j < colNum) {
if (j != colNum - 1) {
String value = ExcelReaderUtil.getCellFormatValue(row.getCell(j)).trim();
String str = null;
if (value.endsWith(".0")) {
str = value.replace(".0", "");
} else {
str = value;
}
if (isInteger(str)) {
data += str;
data +=",";
} else {
data += "'" + str + "'";
data +=",";
}
} else {
data += "'" + ExcelReaderUtil.getCellFormatValue(row.getCell(j)).trim() + "'";
data +=")";
}
j++;
}
data +=",";
content.put(i, data);//str = ('1.0','奥迪','A'),('2.0','阿斯顿·马丁','A'),
data = "";
}
StringBuilder sb = new StringBuilder();
int mapCount = content.size();
for (int i = 1; i < mapCount + 1; i++) {
String value = content.get(i);
if (i % 500 != 0) {
sb.append(value + "\r\n");
} else {
sb.append(value + "\r\n");
sb.append("---");
}
}
return sb.toString();
}
private static String connectTitle(String[] title, String table) {
String insertSql = "INSERT INTO " + table + "(\r\n" ;
StringBuilder sb = new StringBuilder();
int titleCount = title.length;
int i = 1;
sb.append(insertSql);
for (String s : title) {
if (i != titleCount) {
sb.append(s);
sb.append(",\r\n");
} else {
sb.append(s);
sb.append(")");
}
i++;
}
return sb.toString();
}
private static String[] readExcelTitle(HSSFSheet sheetData) {
row = sheetData.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
String[] title = new String[colNum];
for (int k = 0; k < colNum; k++) {
title[k] = ExcelReaderUtil.getCellFormatValue(row.getCell(k));
}
return title;
}
/***
* 判断是否是 int
* @param input
*/
public static boolean isInteger(String input){
Matcher mer = Pattern.compile("^[+-]?[0-9]+$").matcher(input);
return mer.find();
}
}
ExcelReaderUtil:
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
public class ExcelReaderUtil {
/**
* 根据HSSFCell类型设置数据
* @param cell
* @return
*/
static String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
else {
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case HSSFCell.CELL_TYPE_STRING:
cellvalue = cell.getRichStringCellValue().getString();
break;
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
}