概述
1. 创建解析主程序,代码如下:
package com.java.parseExcel;
import java.io.File;
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.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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.ss.usermodel.WorkbookFactory;
import com.java.bean.CallListInfoBean;
public class ExcelParse {
private Sheet sheet; // 表格类实例
@SuppressWarnings("rawtypes")
LinkedList[] result; // 保存每个单元格的数据 ，使用的是一种链表数组的结构
@SuppressWarnings("rawtypes")
public List<CallListInfoBean> parseExcelInfo(String filename,InputStream is,String caseID, int evId){
List<CallListInfoBean> list = null;
loadExcel(is);
init();
//判断是何种格式的Excel文件，主要是表头，数据格式不同，解析字段方式不一样
LinkedList reList = result[0];
String firstTile = (String)reList.get(0);
if(firstTile.contains("序号")){
list = getCallListBean4(filename,caseID,evId);
}
return list;
}
// 读取excel文件，创建表格实例
private void loadExcel(InputStream is) {
try {
Workbook workBook = WorkbookFactory.create(is);
sheet = workBook.getSheetAt(0);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
// 获取单元格的值
private String getCellValue(Cell cell) {
String cellValue = "";
@SuppressWarnings("unused")
DataFormatter formatter = new DataFormatter();
SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (cell != null) {
// 判断单元格数据的类型，不同类型调用不同的方法
switch (cell.getCellType()) {
// 数值类型
case Cell.CELL_TYPE_NUMERIC:
// 进一步判断 ，单元格格式是日期格式
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = simple.format(cell.getDateCellValue());
} else {
//数值
DecimalFormat df = new DecimalFormat("0");
double value = cell.getNumericCellValue();
cellValue = df.format(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
// 判断单元格是公式格式，需要做一种特殊处理来得到相应的值
case Cell.CELL_TYPE_FORMULA: {
try {
cellValue = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getRichStringCellValue());
}
}
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
// 初始化表格中的每一行，并得到每一个单元格的值
@SuppressWarnings({ "rawtypes", "unchecked" })
public void init() {
int rowNum = sheet.getLastRowNum() + 1;
result = new LinkedList[rowNum];
for (int i = 0; i < rowNum; i++) {
Row row = sheet.getRow(i);
// 每有新的一行，创建一个新的LinkedList对象
result[i] = new LinkedList();
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
// 获取单元格的值
String str = getCellValue(cell);
// 将得到的值放入链表中
result[i].add(str);
}
}
}
//话单Excel解析
public List<CallListInfoBean> getCallListBean4(String filename,String caseID, int evId) {
List<CallListInfoBean> list = new ArrayList<CallListInfoBean>();
for (int i = 0; i < result.length; i++) {
CallListInfoBean clb = new CallListInfoBean();
if (i >= 1) { //从导入话单的除去话单头,以后的第一行数据开始遍历
for (int j = 0; j < result[i].size(); j++) {
clb.setFile_download_url(filename);
clb.setCaseID(caseID);
clb.setEvID(evId);
if (j == 4) {
clb.setLocalNum((String) result[i].get(10));
} else if (j == 5) {//dateTrans()
clb.setStartTime((String) result[i].get(5)+" "+result[i].get(6));
} else if (j == 6) {
clb.setPosition((String) result[i].get(19)+","+(String) result[i].get(20));
} else if (j == 7) {
clb.setMethod((String) result[i].get(14));
} else if (j == 8) {
clb.setDialNumber((String) result[i].get(13));
} else if (j == 9) {
clb.setCallDuration((String) result[i].get(7));
} else if (j == 15) {
clb.setStarFlag(-1);
} else if (j == 19){
String a = ParserDateUtil.getCurrentWeekOfMonth((String) result[i].get(5));
clb.setWeek(a);
}else if (j == 20){
String time = (String) result[i].get(6); //根据startTime 截取时间的小时部分
String aa = time.split(":")[0].trim();
clb.setHours(aa);
}
}
list.add(clb);
}
}
return list;
}
/**
* 读取路径下的所有话单文件
* 函数的目的/功能
* @param filepath
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static void readfile(String excelPath,String caseId,int evId) throws FileNotFoundException, IOException {
try {
File file = new File(excelPath);
if (!file.isDirectory()) {
ExcelParse.JDBCInsertMySql(file.getPath(), caseId, evId);
System.out.println("文件");
System.out.println("path=" + file.getPath());
} else if (file.isDirectory()) {
System.out.println("文件夹");
String[] filelist = file.list();
for (int i = 0; i < filelist.length; i++) {
File readfile = new File(excelPath + "\" + filelist[i]);
if (!readfile.isDirectory()) {
ExcelParse.JDBCInsertMySql(readfile.getPath(),caseId, evId);
System.out.println("path=" + readfile.getPath());
} else if (readfile.isDirectory()) {
readfile(excelPath + "\" + filelist[i], caseId, evId);
}
}
}
} catch (FileNotFoundException e) {
System.out.println("readfile() Exception:" + e.getMessage());
}
}
public static void JDBCInsertMySql(String readExcelPath, String caseId,int evId){
FileInputStream is=null;
try {
is = new FileInputStream(new File(readExcelPath));
} catch (FileNotFoundException e) {
System.out.println(e.getMessage());
}
ExcelParse ep =new ExcelParse();
List<CallListInfoBean> list = ep.parseExcelInfo(readExcelPath, is,caseId,evId);
for (CallListInfoBean clb : list) {
// System.out.println(a.getCallDuration()+" | "+a.getLocalNum()+" | "+a.getStartTime()+" | "+a.getPosition()+" | "+a.getWeek()+" | "+a.getHours());
String driver="com.mysql.jdbc.Driver";
String url = "jdbc:mysql://172.16.102.180:3306/bdcloud";
String user = "root";
String password = "123456";
Connection conn = null;
PreparedStatement pstm =null;
try {
Class.forName(driver);
conn=DriverManager.getConnection(url,user,password);
String sql="insert into calllistbean_bak (caseID,evID,name,localNum,startTime,position,method,dialNumber,callDuration,file_download_url,day,time,callType,callHome,starFlag,favoriteTime,favoriteLabel,favoritePerson,week,hours) "
+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
pstm = conn.prepareStatement(sql);
pstm.setString(1,clb.getCaseID());
pstm.setInt(2,clb.getEvID());
pstm.setString(3,clb.getName());
pstm.setString(4,clb.getLocalNum());
pstm.setString(5,clb.getStartTime());
pstm.setString(6,clb.getPosition());
pstm.setString(7,clb.getMethod());
pstm.setString(8,clb.getDialNumber());
pstm.setString(9,clb.getCallDuration());
pstm.setString(10,clb.getFile_download_url());
pstm.setString(11,clb.getDay());
pstm.setString(12,clb.getTime());
pstm.setString(13,clb.getCallType());
pstm.setString(14,clb.getCallHome());
pstm.setInt(15,clb.getStarFlag());
pstm.setString(16,clb.getFavoriteTime());
pstm.setString(17,clb.getFavoriteLabel());
pstm.setString(18,clb.getFavoritePerson());
pstm.setString(19,clb.getWeek());
pstm.setString(20,clb.getHours());
// 执行sql语句
pstm.executeUpdate();
// 关闭数据库连接对象
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
//定义服务器对应话单excel路径,上线时候这个路径作为参数传进来
String excelPath = "F:\test\excel";
String caseId = "358";
int evId = 3279;
try {
//读取指定路径下的所有话单文件,并将所有话单对应的excel话单数据插入mysql
ExcelParse.readfile(excelPath,caseId, evId);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
2. 创建对应的mysql相关bean
package com.java.bean;
public class CallListBean {
/**用户姓名*/
private String name;
/**用户电话号码*/
private String localNum;
/**通话开始时间*/
private String startTime;
/**通信地点*/
private String position;
/**通信方式*/
private String method;
/**对方号码*/
private String dialNumber;
/**通信时长*/
private String callDuration;
private String file_download_url;
private String caseID;
/**通话日期*/
private String day;
/**通话时间*/
private String time;
public CallListBean(){
}
public CallListBean(String name, String localNum,String startTime, String position, String method,
String dialNumber,String callDuration,String file_download_url,String caseID,String day,String time) {
this.name = name;
this.localNum = localNum;
this.startTime = startTime;
this.position = position;
this.method = method;
this.dialNumber = dialNumber;
this.callDuration = callDuration;
this.file_download_url = file_download_url;
this.caseID = caseID;
this.day = day;
this.time = time;
}
public String getCaseID() {
return caseID;
}
public void setCaseID(String caseID) {
this.caseID = caseID;
}
public String getFile_download_url() {
return file_download_url;
}
public void setFile_download_url(String file_download_url) {
this.file_download_url = file_download_url;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLocalNum() {
return localNum;
}
public void setLocalNum(String localNum) {
this.localNum = localNum;
}
public String getStartTime() {
return startTime;
}
public void setStartTime(String startTime) {
this.startTime = startTime;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
public String getMethod() {
return method;
}
public void setMethod(String method) {
this.method = method;
}
public String getDialNumber() {
return dialNumber;
}
public void setDialNumber(String dialNumber) {
this.dialNumber = dialNumber;
}
public String getCallDuration() {
return callDuration;
}
public void setCallDuration(String callDuration) {
this.callDuration = callDuration;
}
public String getDay() {
return day;
}
public void setDay(String day) {
this.day = day;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
@Override
public String toString() {
return "CallListBean [name=" + name + ", localNum=" + localNum
+ ", startTime=" + startTime + ", position=" + position
+ ", method=" + method + ", dialNumber=" + dialNumber
+ ", callDuration=" + callDuration + ", file_download_url="
+ file_download_url + ", caseID=" + caseID + ", day=" + day
+ ", time=" + time + "]";
}
}
3. 创建日期解析类
package com.java.parseExcel;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
public class ParserDateUtil {
/**
* @param dateStr
* @return 获取当前是星期几
*/
public static String getCurrentWeekOfMonth(String dateStr) {
String weekStr = "";
int year = 0, month = 0, day = 0;
try {
String[] year_month_day = null;
if (dateStr.split("/").length>1) {
year_month_day = dateStr.split("/");
} else if (dateStr.split("-").length>1) {
year_month_day = dateStr.split("-");
}
if (!"".equals(year_month_day[0])) {
year = Integer.parseInt(year_month_day[0]);
}
if (!"".equals(year_month_day[1])) {
month = Integer.parseInt(year_month_day[1]);
}
if (!"".equals(year_month_day[2])) {
day = Integer.parseInt(year_month_day[2]);
}
Calendar calendar = new GregorianCalendar(year, month - 1, day);
weekStr = getCurrentWeekOfMonth(calendar);
} catch (Exception e) {
e.printStackTrace();
}
return weekStr;
}
/**
* @param calendar
* @return 获取当前是星期几;
*/
public static String getCurrentWeekOfMonth(Calendar calendar) {
String strWeek = "";
int dw = calendar.get(Calendar.DAY_OF_WEEK);
if (dw == 1) {
strWeek = "SUN";
} else if (dw == 2) {
strWeek = "MON";
} else if (dw == 3) {
strWeek = "TUE";
} else if (dw == 4) {
strWeek = "WED";
} else if (dw == 5) {
strWeek = "THU";
} else if (dw == 6) {
strWeek = "FRI";
} else if (dw == 7) {
strWeek = "SAT";
}
return strWeek;
}
/**
* @param calendar
* @return 返回当前是星期几;
*/
public static int getCurrentWeekOfMonthIndex(Calendar calendar) {
// System.out.println(calendar.get(Calendar.DAY_OF_WEEK));
return calendar.get(Calendar.DAY_OF_WEEK);
}
//字符串转换成日期格式
public static String[] getFormatDate(String dateString){
String[] s = new String[3];
String date = dateString.trim();
String year = date.substring(0, 4);
String month = date.substring(4, 6);
String day = date.substring(6, 8);
String hour = date.substring(8, 10);
String minute = date.substring(10, 12);
String second = date.substring(12, 14);
String strDate1 = year+"-"+month+"-"+day+" "+hour+":"+minute+":"+second;
String strDate2 = year+"-"+month+"-"+day;
String strDate3 = hour+":"+minute+":"+second;
s[0] = strDate1;
s[1] = strDate2;
s[2] = strDate3;
return s;
}
public static void main(String[] args) {
ParserDateUtil pd = new ParserDateUtil();
String a = pd.getCurrentWeekOfMonth("2016-11-11");
System.out.println(a);
}
}4. 最终mysql中输入如下
最后
以上就是欢呼小海豚为你收集整理的java 解析 excel数据并将数据插入mysql的全部内容，希望文章能够帮你解决java 解析 excel数据并将数据插入mysql所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复