Excel数据导入至数据库
来源：互联网 发布：淘宝售后工作职责 编辑：程序博客网 时间：2024/05/18 08:53
====抽象类
public abstract class BaseFileReader {
public abstract List<Map<String, String>> getFileContent(String fileName) throws Exception;public abstract List<String> getFileTitle(String fileName) throws Exception;
}
=======
private static final Log log = LogFactory.getLog(ExcelxReader.class);
public List<Map<String, String>> getFileContent(String fileName)
throws Exception {
List<Map<String, String>> list = null;
int startNum = 0;
FileInputStream in = new FileInputStream(new File(fileName)) ;
try {
HSSFWorkbook workbook = new HSSFWorkbook(in);
HSSFSheet sheet = workbook.getSheetAt(0);
int rows = CellValue.getRightRows(sheet);
HSSFRow row0 = sheet.getRow(0);
list = new ArrayList<Map<String, String>>();
if (row0 != null) {
Map<Integer, String> colsName = CellValue.getMapColNames(row0);
Object[] cols = colsName.keySet().toArray();
Map<String, String> line = null;
for (int i = startNum + 1; i < rows; i++) {
line = new HashMap<String, String>();
for (Object col : cols) {
HSSFRow row = sheet.getRow(i);
// if (null == row) {
// break;
// }
// 这里调用的方法改动了 原来参数为两个 现在改成了三个 多加了函数的处理
String data = CellValue.getStringValue(workbook,
row.getCell((Integer) col), null);
String colName = colsName.get(col);
if (colName != null && !"".equals(colName)) {
colName = colName.replaceAll(" ", "");
}
line.put(colName, data);
}
// if (line.size() == 0)
// continue;
list.add(line);
}
}
return list;
} catch (Exception e) {
log.error(e.getMessage(), e);
e.printStackTrace();
throw new Exception("解析2003excel错误");
}finally{
in.close();
}
}
public List<String> getFileTitle(String fileName) throws Exception {
log.info("start get file title: " + fileName);
FileInputStream in = new FileInputStream(new File(fileName)) ;
try {
HSSFWorkbook workbook = new HSSFWorkbook(in);
int sheetNum=workbook.getNumberOfSheets();
if(sheetNum > 1){
for (int i = 1; i < sheetNum; i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
int rowNum = sheet.getPhysicalNumberOfRows();
if(rowNum >= 1){
return null;
}
}
}
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row0 = sheet.getRow(0);
List<String> colsName = null;
if (row0 != null) {
colsName = CellValue.getColNames(row0);
}
return colsName;
} catch (Exception e) {
log.error(e.getMessage(), e);
e.printStackTrace();
throw new Exception("解析2003excel标题错误");
}finally{
in.close();
}
}
==========
/**
* Excel取值类
*
* @version 版本信息 创建时间 2010-6-25 下午16:01:50
*/
public class CellValue {
public static final String TXT_SEPARATOR = "\t";
public static final String XML_SEPARATOR = "root/rows/row";
/**
* 返回Excel2003去掉空行的记录数(xls)
*/
public static int getRightRows(HSSFSheet sheet) {
int rsRows = sheet.getPhysicalNumberOfRows(); // 行数
int nullCellNum;
int afterRows = rsRows;
// 定义 row、cell
HSSFRow row;
// HSSFCell cell;
String cell;
// 循环输出表格中的内容
for (int i = sheet.getFirstRowNum(); i < rsRows; i++) { // 统计行中为空的单元格数
row = sheet.getRow(i);
nullCellNum = 0;
if (row == null) {
afterRows--;
continue;
}
int rsCols = row.getPhysicalNumberOfCells();// 列数
for (int j = 0; j < rsCols; j++) {
cell = row.getCell(j) == null ? "" : row.getCell(j).toString();
if ("".equals(cell.trim())) {
nullCellNum++;
}
}
if (nullCellNum >= rsCols) { // 如果nullCellNum大于或等于总的列数
afterRows--; // 行数减一
}
}
return afterRows;
}
/**
* excel2003单元格内容读取
*
* @param cell
* @param defaultValue
* @return
*/
public static String getStringValue(HSSFCell cell, String defaultValue) {
String strReturn = defaultValue;
try {
if (cell == null)
return defaultValue;
int type = cell.getCellType();
switch (type) {
case HSSFCell.CELL_TYPE_STRING:
strReturn = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
if (isDate) {
double d = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
strReturn = DateUtil.dateToString(date,
"yyyy-M-d HH:mm:ss");
if (strReturn == null) {
strReturn = cell.getDateCellValue().toString();
}
} else {
double d = cell.getNumericCellValue();
strReturn = keepScore(d);
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strReturn = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
strReturn = defaultValue;
break;
default:
strReturn = defaultValue;
}
} catch (Exception e) {
e.printStackTrace();
strReturn = defaultValue;
}
return strReturn;
}
/**
* excel2003单元格内容读取
*
* @param cell
* @param defaultValue
* @return
*/
public static String getStringValue(HSSFWorkbook workbook,HSSFCell cell, String defaultValue) {
String strReturn = defaultValue;
try {
if (cell == null)
return defaultValue;
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
int type = cell.getCellType();
switch (type) {
case HSSFCell.CELL_TYPE_STRING:
strReturn = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
if (isDate) {
double d = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
strReturn = DateUtil.dateToString(date,
"yyyy-M-d HH:mm:ss");
if (strReturn == null) {
strReturn = cell.getDateCellValue().toLocaleString();
}
} else {
double d = cell.getNumericCellValue();
strReturn = keepScore(d);
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strReturn = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
strReturn = defaultValue;
break;
case HSSFCell.CELL_TYPE_FORMULA:
org.apache.poi.ss.usermodel.CellValue cellValue = evaluator.evaluate(cell);
//暂时只对数字函数处理 若要对其他函数 处理 得自行添加
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
strReturn=String.valueOf(cellValue.getNumberValue());
break;
default:
break;
}
break;
default:
strReturn = defaultValue;
}
} catch (Exception e) {
e.printStackTrace();
strReturn = defaultValue;
}
return strReturn;
}
/**
* 获取excel2003标题行列名组成的list
*
* @param row 标题行
* @return list
*/
public static List<String> getColNames(HSSFRow row) {
int cols = row.getPhysicalNumberOfCells();
List<String> colNames = new ArrayList<String>();
for (int i = 0; i < cols; i++) {
String colName = getStringValue(row.getCell(i), "");
if (colName != null && !colName.equals(""))
colNames.add(colName.replaceAll(" ",""));
}
return colNames;
}
/**
* 获取excel2003标题行列名组成的list
*
* @param row 标题行
* @return list
*/
public static Map<Integer, String> getMapColNames(HSSFRow row) {
int cols = row.getPhysicalNumberOfCells();
Map<Integer, String> colNames = new HashMap<Integer, String>();
for (int i = 0; i < cols; i++) {
String colName = getStringValue(row.getCell(i), "");
if (colName != null && !colName.equals(""))
colNames.put(i, colName.replaceAll(" ",""));
}
return colNames;
}
/**
* 获取txt标题行列名组成的list(默认第一个非空行为标题行)
*
* @param fieName 文件名
* @return list
*/
public static List<String> getColNames(String fileName, String separator) {
BufferedReader in = null;
try {
List<String> colNames = new ArrayList<String>();
in = new BufferedReader(new FileReader(fileName));
String line;
while ((line = in.readLine()) != null) {
if (!"".equals(line.trim())) {
for (String str : line.split(separator)) {
if(str!=null && !str.equals("")){
colNames.add(str.replaceAll(" ",""));
}
}
return colNames;
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (in != null){
in.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 获取txt标题行列名组成的list(默认第一个非空行为标题行)
*
* @param fieName 文件名
* @return list
*/
public static Map<Integer, String> getMapColNames(String fileName,
String separator) {
BufferedReader in = null;
try {
Map<Integer, String> colNames = new HashMap<Integer, String>();
in = new BufferedReader(new FileReader(fileName));
String line;
while ((line = in.readLine()) != null) {
if (!"".equals(line.trim())) {
for (int i = 0; i < line.split(separator).length; i++) {
String str = line.split(separator)[i];
if(str!=null && !str.equals("")){
colNames.put(i, str.replaceAll(" ",""));
}
}
return colNames;
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if (in != null){
in.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 获取txt标题行列名组成的list(默认第一个非空行为标题行)
*
* @param fieName 文件名
* @return list
*/
public static List<String> getColNames(InputStream is, boolean firstHeader,
String separator) {
BufferedReader in = null;
try {
List<String> colNames = new ArrayList<String>();
in = new BufferedReader(new InputStreamReader(is));
String line = null;
boolean isRun = true;
while (isRun) {
line = in.readLine();
if (line == null) {
continue;
}
isRun = false;
if (!firstHeader) {
line = in.readLine();
}
}
if (!"".equals(line.trim())) {
for (int i = 0; i < line.split(separator).length; i++) {
String str = line.split(separator)[i];
if(str!=null && !str.equals("")){
colNames.add(str.replaceAll(" ",""));
}
}
return colNames;
}
} catch (Exception e) {
e.printStackTrace();
}
finally{
try {
if (in != null){
in.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 获取txt标题行列名组成的list(默认第一个非空行为标题行)
*
* @param fieName 文件名
* @return list
*/
public static Map<Integer, String> getMapColNames(InputStream is,
boolean firstHeader, String separator) {
BufferedReader in = null;
try {
Map<Integer, String> colNames = new HashMap<Integer, String>();
in = new BufferedReader(new InputStreamReader(is));
String line = null;
boolean isRun = true;
while (isRun) {
line = in.readLine();
if (line == null) {
continue;
}
isRun = false;
if (!firstHeader) {
line = in.readLine();
}
}
if (!"".equals(line.trim())) {
for (int i = 0; i < line.split(separator).length; i++) {
String str = line.split(separator)[i];
if(str!=null && !str.equals("")){
colNames.put(i,str.replaceAll(" ",""));
}
// colNames.put(i, str.trim());
}
return colNames;
}
} catch (Exception e) {
e.printStackTrace();
}
finally{
try {
if (in != null){
in.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 获取txt内容组成的list(默认第一个非空行为标题行)
*
* @param fieName 文件名
* @return list
*/
public static List<String[]> getTxtContents(String fileName,
String separator) {
BufferedReader in = null;
try {
List<String[]> colNames = new ArrayList<String[]>();
in = new BufferedReader(new FileReader(fileName));
String line;
while ((line = in.readLine()) != null) {
if (!"".equals(line.trim())) {
colNames.add(line.split(separator));
}
}
colNames.remove(0);
return colNames;
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if (in != null){
in.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 返回xml文件对应的内容
*/
public static Document getXmlDoc(File file) {
SAXReader reader = new SAXReader();
Document document = null;
try {
document = reader.read(file);
} catch (Exception e) {
try {
InputStream in = new FileInputStream(file);
InputStreamReader strInStream = new InputStreamReader(in, "GBK");
document = reader.read(strInStream);
} catch (Exception ex) {
ex.printStackTrace();
}
}
return document;
}
/**
* 返回xml文件对应的内容
*/
public static Document getXmlDoc(InputStream is) {
Document document = null;
SAXReader reader = new SAXReader();
try {
BufferedReader input = null;
PushbackInputStream pis = new PushbackInputStream(is, 1024);
String bomEncoding = getBOMEncoding(pis);
if (bomEncoding == null) {
input = new BufferedReader(new InputStreamReader(pis, "UTF-8"));
} else {
input = new BufferedReader(new InputStreamReader(pis,
bomEncoding));
}
document = reader.read(input);
} catch (Exception e) {
try {
InputStreamReader strInStream = new InputStreamReader(is, "GBK");
document = reader.read(strInStream);
} catch (Exception ex) {
ex.printStackTrace();
}
}
return document;
}
/**
* 获取xml最后一级节点名组成的list
*
* @param element0 节点
* @return list
*/
public static List<String> getLastLevelNodeNames(Element element0) {
List<String> nodeNames = new ArrayList<String>();
List<Element> elements = element0.elements();
for (Element ele : elements) {
nodeNames.add(ele.getName());
}
return nodeNames;
}
protected static String getBOMEncoding(PushbackInputStream is)
throws IOException {
String encoding = null;
int[] bytes = new int[3];
bytes[0] = is.read();
bytes[1] = is.read();
bytes[2] = is.read();
if (bytes[0] == 0xFE && bytes[1] == 0xFF) {
encoding = "UTF-16BE";
is.unread(bytes[2]);
} else if (bytes[0] == 0xFF && bytes[1] == 0xFE) {
encoding = "UTF-16LE";
is.unread(bytes[2]);
} else if (bytes[0] == 0xEF && bytes[1] == 0xBB && bytes[2] == 0xBF) {
encoding = "UTF-8";
} else {
for (int i = bytes.length - 1; i >= 0; i--) {
is.unread(bytes[i]);
}
}
return encoding;
}
private static String keepScore(double d){
String result = "";
if(d > 0 ){
if (d - (int) d < Double.MIN_VALUE) { // 是否为int型
result = Integer.toString((int) d);
} else { // 是否为double型
DecimalFormat df = new DecimalFormat("#.###########");
result =df.format(d);
//strReturn = Double.toString(cell.getNumericCellValue());
}
}else{
if ((int) d - d < Double.MIN_VALUE) { // 是否为int型
result = Integer.toString((int) d);
} else { // 是否为double型
DecimalFormat df = new DecimalFormat("#.###########");
result =df.format(d);
}
}
return result;
}
- Excel数据导入至数据库
- EXCEL数据导入数据库
- EXCEL数据导入数据库
- EXCEL数据导入数据库
- EXCEL数据导入数据库
- Excel数据导入数据库
- 数据库导入excel数据
- 从excel导入数据至PostgreSQL数据库
- 将数据库数据导入Excel
- 将excel数据导入数据库
- 导入Excel数据到数据库
- excel数据快速导入数据库
- excel数据导入mysql数据库
- excel数据导入mysql数据库
- EXCEL数据导入SQL数据库
- Excel中的数据导入数据库
- Excel数据导入Mysql数据库
- 数据库中的数据导入excel
- 几个著名国外的java网站
- shell变量
- 【转】source insight 快捷键 汇总
- js鼠标点击展开收起特效(带缓动效果)
- C/S和B/S
- Excel数据导入至数据库
- Shell command 使用习惯和语句特点
- 3Sum Closest
- OpenStack对象存储-swift
- c语言版数据结构（奇迹冬瓜）-链表实战（2）合并两有序线性表
- 将代码以Eval方式加密、解密
- 常用的Double保留N位小数的转换
- RemoteViews的使用
- shell编程——循环语句
