Excel模板
需求
将模板中的数据读取出来，当数据库中存在表时，用id更新或插入数据，当表不存在时，建表并插入数据。
进度
目前只是将数据包装好，并未实现sql语句的拼接。封装过程详见demo。
Demo
package excelImport;
import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
public static void main(String[] args) {
XSSFWorkbook workbook = ReadExcel.getWorkbook(
"D:/test.xlsx");
Map<String, XSSFSheet> sheetMap = ReadExcel.getSheetMap(workbook);
XSSFSheet currentSheet = sheetMap.get(
"Sheet1");
Map<String, Object> rowMap = getRowMap(currentSheet);
List<XSSFRow> list = getTableInfoList(rowMap,
"hdmx(Level=test)");
Map<String, Object> m = getTableInfoMap(list);
System.out.println(m.get(
"tableName"));
System.out.println(((String[]) m.get(
"colName"))[
2]);
System.out.println(((String[]) m.get(
"colTypeArray"))[
1]);
Object[][] oArr = (Object[][]) m.get(
"dataInfoArray");
System.out.println(oArr[
2][
2]);
}
/**
* 根据路径获取workbook
*
* @param excelPath
* @return
*/
private static XSSFWorkbook
getWorkbook(String excelPath) {
XSSFWorkbook wb =
null;
try {
File file =
new File(excelPath);
FileInputStream fis =
new FileInputStream(file);
wb =
new XSSFWorkbook(fis);
fis.close();
}
catch (Exception e) {
}
return wb;
}
/**
* 根据workbook获取sheet
*
* @param workbook
* @return
*/
private static Map<String, XSSFSheet>
getSheetMap(XSSFWorkbook workbook) {
int sheetNum = workbook.getNumberOfSheets();
Map<String, XSSFSheet> sheetMap =
new HashMap<String, XSSFSheet>();
for (
int i =
0; i < sheetNum; i++) {
XSSFSheet sheet = workbook.getSheetAt(i);
sheetMap.put(sheet.getSheetName(), sheet);
}
return sheetMap;
}
/**
* 根据sheet获取map
*
* @param sheet
* @return
*/
private static Map<String, Object>
getRowMap(XSSFSheet sheet) {
Map<String, Object> resMap =
new HashMap<String, Object>();
List<XSSFRow> rowList =
new ArrayList<XSSFRow>();
int totalRowNum = sheet.getLastRowNum();
String currentTable =
"";
for (
int i =
0; i < totalRowNum; i++) {
XSSFRow currentRow = sheet.getRow(i);
if (currentRow !=
null) {
String tableName = sheet.getRow(i).getCell(
0).toString();
if (tableName.contains(
"Level")) {
currentTable = tableName;
}
if (!currentTable.equals(
"")) {
rowList.add(currentRow);
resMap.put(currentTable, rowList);
}
}
else {
currentTable =
"";
rowList =
new ArrayList<XSSFRow>();
}
}
return resMap;
}
/**
* 根据封装好的map和key获取某一个表的list集合
*
* @param rowMap
* @param rowMapKey
* @return
*/
private static List<XSSFRow>
getTableInfoList(Map<String, Object> rowMap,
String rowMapKey) {
@SuppressWarnings(
"unchecked")
List<XSSFRow> tableInfoList = (List<XSSFRow>) rowMap.get(rowMapKey);
return tableInfoList;
}
/**
* 根据list集合封装为map数据，其中包含： tableName 表名 String colName 列名 String[]
* dataInfoArray 待处理数据 Object[][] colTypeArray 列类型 String[]
*
* @param list
* @return
*/
private static Map<String, Object>
getTableInfoMap(List<XSSFRow> list) {
Map<String, Object> tableInfoMap =
new HashMap<String, Object>();
String tableName = list.get(
0).getCell(
0).getRichStringCellValue()
.toString();
tableInfoMap.put(
"tableName", tableName);
XSSFRow colNameRow = list.get(
1);
String[] colNameArr = getColNameArray(colNameRow);
tableInfoMap.put(
"colName", colNameArr);
int dataNum = list.size() -
3;
if (dataNum <=
0) {
return null;
}
else {
int dataLen = colNameRow.getLastCellNum();
Object[][] dataArr = getDataArrays(list, dataNum, dataLen);
tableInfoMap.put(
"dataInfoArray", dataArr);
String[] colTypeArray = getColTypeOfSqlServerDatabaseByDataArray(dataArr[dataArr.length -
1]);
tableInfoMap.put(
"colTypeArray", colTypeArray);
}
return tableInfoMap;
}
/**
* 根据Java类型获取对应数据库类型
*
* @param objects
* @return
*/
private static String[]
getColTypeOfSqlServerDatabaseByDataArray(
Object[] objects) {
int len = objects.length;
String[] colTypeArr =
new String[len];
for (
int i =
0; i < len; i++) {
Object obj = objects[i];
if (obj
instanceof Number) {
obj =
"number";
}
else if (obj
instanceof String) {
obj =
"varchar2";
}
else if (obj
instanceof Date) {
obj =
"date";
}
else {
obj =
"";
}
colTypeArr[i] = (String) obj;
}
return colTypeArr;
}
/**
* 根据列名行获取列名数组
*
* @param colNameRow
* @return
*/
private static String[]
getColNameArray(XSSFRow colNameRow) {
int colNum = colNameRow.getLastCellNum();
String[] colNameArray =
new String[colNum];
for (
int i =
0; i < colNum; i++) {
String colName = colNameRow.getCell(i).getRichStringCellValue()
.getString();
colNameArray[i] = colName;
}
return colNameArray;
}
/**
* 根据模板特点，传入某表所有行数据，将待处理数据转换成二维数组形式
*
* @param list
* @param dataNum
* @param dataLen
* @return
*/
private static Object[][]
getDataArrays(List<XSSFRow> list,
int dataNum,
int dataLen) {
Object[] dataArrays[] =
new Object[dataNum][dataLen];
for (
int i =
0; i < dataNum; i++) {
XSSFRow row = list.get(i +
3);
Object[] cellArray = getCellArray(row);
dataArrays[i] = cellArray;
}
return dataArrays;
}
/**
* 根据row获取cell的数组数据
*
* @param row
* @return
*/
private static Object[]
getCellArray(XSSFRow row) {
Object[] os =
new Object[row.getLastCellNum()];
for (
int i =
0; i < row.getLastCellNum(); i++) {
XSSFCell cell = row.getCell(i);
Object obj = getJavaTypeValueOfCell(cell);
os[i] = obj;
}
return os;
}
/**
* 根据cell类型获取相应java类型数据
*
* @param cell
* @return
*/
private static Object
getJavaTypeValueOfCell(XSSFCell cell) {
int cellType = cell.getCellType();
Object obj =
new Object();
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
Double cellDoubleValue = cell.getNumericCellValue();
if (DateUtil.isCellDateFormatted(cell)) {
obj = cell.getDateCellValue();
}
else if (cellDoubleValue >
0 && cellDoubleValue <
1) {
obj = setScale(cellDoubleValue,
2);
}
else {
obj = Math.round(cellDoubleValue);
}
break;
case Cell.CELL_TYPE_STRING:
obj = cell.getStringCellValue();
break;
default:
obj =
"";
break;
}
return obj;
}
/**
* 设置double数据保留小数点位置
*
* @param d
* @param scale
* @return
*/
public static Double
setScale(Double d,
int scale) {
BigDecimal bigDecimal =
new BigDecimal(d);
double bdfScale = bigDecimal.setScale(scale, BigDecimal.ROUND_HALF_UP)
.doubleValue();
return bdfScale;
}
}
运行结果
