import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
public class ExcelHandler {
private static final String EXCEL_VERSION_03_LOW_SUFFIX = "xls";
private static final String EXCEL_VERSION_07_UP_SUFFIX = "xlsx";
// 读取Excel 数据库字段 起始行
private static final Integer READ_DATABASE_COLUMN_START_ROW = 0;
// 读取Excel 数据库字段 起始列
private static final Integer READ_DATABASE_COLUMN_START_COLUMN = 2;
// 读取Excel 数据库数据记录内容 起始行
private static final Integer READ_DATABASE_CONTENT_START_ROW = 2;
// 读取Excel 数据库数据记录内容 起始列
private static final Integer READ_DATABASE_CONTENT_START_COLUMN = 2;
// 读取Excel 数据库删除查询操作 起始列
private static final Integer READ_DATABASE_DDL_SQL_START_COLUMN = 2;
private static final String EXCEL_FILE_PATH = "E://test1.xls";
private static final String DELETE_STR = "delete";
private static final String INSERT_STR = "insert";
@Autowired
private DataBaseDAO dataBaseDAO;
/**
* Excel To DataBase 入口
* @param filePath
*/
public void excelToDatabase(String filePath){
try {
readExcel(filePath);
} catch (IOException e) {
e.printStackTrace();
}
}
public StringBuffer insertSQL(String tableName,String tableColumns){
StringBuffer sql = new StringBuffer();
sql.append("insert into ").append(tableName).append(" (").append(tableColumns).append(") values(");
return sql;
}
public String getTableColumnFromDataBase(String tableName){
List<JSONObject> tableColumns = dataBaseDAO.getTableColumns(tableName);
StringBuffer columns = new StringBuffer();
for(JSONObject column:tableColumns){
columns.append(column.getString("column_name")).append(",");
}
return columns.toString().substring(0,columns.length()-1);
}
private String getTableColumn(Sheet sheet){
StringBuffer columns = new StringBuffer();
int endColNum = sheet.getRow(READ_DATABASE_COLUMN_START_ROW).getPhysicalNumberOfCells();
for(int index = READ_DATABASE_COLUMN_START_COLUMN; index <= endColNum;index++){
Row row = sheet.getRow(READ_DATABASE_COLUMN_START_ROW);
Cell cell = row.getCell(index);
String column_name = org.apache.commons.lang.StringUtils.trim(cell.getStringCellValue());
if(null != column_name && !"".equals(column_name)){
columns.append(cell.getStringCellValue()+",");
}
}
return columns.toString();
}
public List<Row> readExcel(String filePath) throws IOException {
if(null == filePath || "".equals(filePath)){
throw new IOException("Excel 文件路径不可以为空!");
}
List<Row> rowRecords = new ArrayList<Row>();
String excelSuffixName = filePath.substring(filePath.lastIndexOf(".")+1);
if(EXCEL_VERSION_03_LOW_SUFFIX.equals(excelSuffixName)){
rowRecords = readOfficeVersion2003LowExcel(filePath);
}
else if(EXCEL_VERSION_07_UP_SUFFIX.equals(excelSuffixName)){
rowRecords = readOfficeVersion2007UpExcel(filePath);
}
return rowRecords;
}
private List<Row> readOfficeVersion2003LowExcel(String filePath) throws IOException{
existExcelFile(filePath);
try {
InputStream inputStream = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
List<HSSFSheet> sheets = getWorkBookSheets(workbook);
// 遍历工作表
for(HSSFSheet sheet:sheets){
List<Row> rowRecords = new ArrayList<Row>();
int endRowNum = sheet.getLastRowNum();
rowRecords = getExcelRows(sheet, READ_DATABASE_CONTENT_START_ROW, endRowNum);
String tableName = getTableName(sheet);
String columnSql = getTableColumn(sheet);
Integer emptyColumnKeyPosition = getEmptyColumnKeyPosition(sheet);
columnSql = columnSql.substring(0,columnSql.length()-1);
// DDL操作语句打印
String executeDeleteSQL = getExecuteDeleteSql(sheet);
if(!"".equals(executeDeleteSQL) && null != executeDeleteSQL){
System.out.println(executeDeleteSQL);
System.out.println("\n");
}
for(Row row:rowRecords){
StringBuffer insetSql = insertSQL(tableName,columnSql);
int lastCellNum = row.getLastCellNum();
readExcelRowRecords(row,READ_DATABASE_CONTENT_START_COLUMN,lastCellNum,insetSql,emptyColumnKeyPosition);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private List<Row> readOfficeVersion2007UpExcel(String filePath) throws IOException{
existExcelFile(filePath);
try {
InputStream inputStream = new FileInputStream(filePath);
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
// 获取第一个工作表
List<XSSFSheet> sheets = getWorkBookSheets(workbook);
// 遍历工作表
for(XSSFSheet sheet:sheets){
List<Row> rowRecords = new ArrayList<Row>();
int endRowNum = sheet.getLastRowNum();
rowRecords = getExcelRows(sheet, READ_DATABASE_CONTENT_START_ROW, endRowNum);
// 获取表名
String tableName = getTableName(sheet);
String columnSql = getTableColumn(sheet);
//boolean isExistEmptyKey = isContainBlankEmptyColumnKey(sheet);
Integer emptyColumnKeyPosition = getEmptyColumnKeyPosition(sheet);
columnSql = columnSql.substring(0,columnSql.length()-1);
// DDL操作语句打印
String executeDeleteSQL = getExecuteDeleteSql(sheet);
if(!"".equals(executeDeleteSQL) && null != executeDeleteSQL){
System.out.println(executeDeleteSQL);
System.out.println("\n");
}
for(Row row:rowRecords){
StringBuffer insetSql = insertSQL(tableName,columnSql);
int lastCellNum = row.getLastCellNum();
if(null != row){
readExcelRowRecords(row,READ_DATABASE_CONTENT_START_COLUMN,lastCellNum,insetSql,emptyColumnKeyPosition);
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private List<HSSFSheet> getWorkBookSheets(HSSFWorkbook workbook){
Integer sheetNum = workbook.getNumberOfSheets();
List<HSSFSheet> sheets = new ArrayList<HSSFSheet>();
for(int i = 0; i < sheetNum; i++){
sheets.add(workbook.getSheetAt(i));
}
return sheets;
}
private List<XSSFSheet> getWorkBookSheets(XSSFWorkbook workbook){
Integer sheetNum = workbook.getNumberOfSheets();
List<XSSFSheet> sheets = new ArrayList<XSSFSheet>();
for(int i = 0; i < sheetNum; i++){
sheets.add(workbook.getSheetAt(i));
}
return sheets;
}
private String getTableName(Sheet sheet){
Row row = sheet.getRow(0);
if(null == row){
return "";
}
return row.getCell(0).getStringCellValue();
}
private Integer getEmptyColumnKeyPosition(Sheet sheet){
Integer result = -1;
int endColNum = sheet.getRow(READ_DATABASE_COLUMN_START_ROW).getPhysicalNumberOfCells();
for(Integer index = READ_DATABASE_COLUMN_START_COLUMN; index < endColNum;index++){
Row row = sheet.getRow(READ_DATABASE_COLUMN_START_ROW);
Cell cell = row.getCell(index);
String column_name = org.apache.commons.lang.StringUtils.trim(cell.getStringCellValue());
if( "".equals(column_name) || null == column_name){
result = index;
}
}
return result;
}
private String getExecuteDeleteSql(Sheet sheet){
//
StringBuffer executeSql = new StringBuffer();
Row row = sheet.getRow(READ_DATABASE_DDL_SQL_START_COLUMN);
Cell cell = row.getCell(0);
String ddlStr = org.apache.commons.lang.StringUtils.trim(cell.getStringCellValue());
String tableName = getTableName(sheet);
if(!"".equals(ddlStr) && null != ddlStr){
// 解析 ddl 操作字符
if(ddlStr.startsWith("delete")){
executeSql.append("delete from ").append(tableName);
if(ddlStr.contains("all")){
executeSql.append(";");
}else if(ddlStr.contains("and")){
String columnCode = ddlStr.substring(ddlStr.indexOf("(")+1,ddlStr.indexOf("="));
String whereSql = ddlStr.substring(ddlStr.indexOf("=")+1,ddlStr.indexOf("and"));
String whereSql2 = ddlStr.substring(ddlStr.indexOf("and")+4, ddlStr.indexOf("=", ddlStr.indexOf("and")));
executeSql.append(" where ").append(columnCode).append(" = ").append("'").
append(org.apache.commons.lang.StringUtils.trim(whereSql)).append("'").append(" or ").append(whereSql2).append(" = '").
append(org.apache.commons.lang.StringUtils.trim(ddlStr.substring(ddlStr.indexOf("=", ddlStr.indexOf("and"))+1, ddlStr.indexOf(")")))).append("';");
}else{
String columnCode = ddlStr.substring(ddlStr.indexOf("(")+1,ddlStr.indexOf("="));
String whereSql = ddlStr.substring(ddlStr.indexOf("=")+1,ddlStr.indexOf(")"));
executeSql.append(" where ").append(columnCode).append(" = ").append("'").append(whereSql).append("';");
}
}
}
return executeSql.toString();
}
private String getBuildingIdFromExcel(String filePath){
String buildingId = "";
try {
InputStream inputStream = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
// 获取第一个工作表
HSSFSheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
buildingId = cell.getStringCellValue();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return buildingId.substring(buildingId.indexOf("3"));
}
private void existExcelFile(String filePath) throws IOException{
File file = new File(filePath);
if(!file.exists()){
throw new IOException("文件名称为"+file.getName()+" Excel文件不存在!");
}
}
private List<Row> getExcelRows(Sheet sheet,Integer startRowNum,Integer endRowNum){
List<Row> result = new ArrayList<Row>();
for(int index = startRowNum; index <= endRowNum;index++){
Row row = sheet.getRow(index);
result.add(row);
}
return result;
}
private void readExcelRowRecords(Row row,Integer startCellNum,Integer endCellNum,StringBuffer sql,Integer emptyColumnKeyPosition){
for(int cellIndex = startCellNum;cellIndex < endCellNum;cellIndex++){
// 空 键值对,不做处理,直接跳出,继续执行
if(emptyColumnKeyPosition != -1 && cellIndex == emptyColumnKeyPosition){
continue;
}else {
Cell cell = row.getCell(cellIndex);
switch (cell.getCellType()){
case Cell.CELL_TYPE_STRING:
sql.append("'").append(cell.getStringCellValue()).append("',");
break;
case Cell.CELL_TYPE_BOOLEAN:
sql.append(cell.getBooleanCellValue()).append(",");
break;
case Cell.CELL_TYPE_NUMERIC:
// 科学计数法处理
DecimalFormat decimalFormat = new DecimalFormat("#");
sql.append(decimalFormat.format(cell.getNumericCellValue())).append(",");
break;
case Cell.CELL_TYPE_BLANK:
sql.append("'").append(cell.getStringCellValue()).append("',");
break;
default:
break;
}
}
}
sql.deleteCharAt(sql.length()-1);
System.out.println(sql+");\n");
}
}
Java poi 批量读取Excel
最新推荐文章于 2024-01-09 16:20:32 发布