本人需要对每天发送的定时邮件取近30天的数据导入数据库,因此excel文件名有一定的特点,比较好删选。写这博客主要做个笔记,因为java还是入门阶段
package com.yiwugou.analysis.words;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.yiwugou.analysis.util.DatabaseHelper;
import com.yiwugou.analysis.util.DateTimeUtil;
import com.yiwugou.analysis.util.SqlHelper;
import com.yiwugou.analysis.words.dao.ExcelToDBDao;
public class ExcelToDB extends SqlHelper {
Connection conn;
public static void main(String[] args) throws IOException {
String srcFile="D:\\Users\\Administrator\\Desktop\\excel";
ExcelToDB e=new ExcelToDB();
e.read(srcFile);
}
public List<String> file(String srcFile){
File file=new File(srcFile);
if(!file.isDirectory()){
System.out.println("这不是一个目录");
}
File[] listfile=file.listFiles();
List<String> fileFullName=new ArrayList<String>();
//对于日期和文件的获取,是我的文件目录下文件名的要求,其他人可自行处理
//excel文件特点:xxxxx_xxxxxx_xxxxxxx_20150620_20150621.xlsx
Calendar cal = Calendar.getInstance();//获取当前日期
Calendar cal31=DateTimeUtil.getNdayBeforeToday(31);//获取31天前的日期
String dayNow= getDateStrFromTS(cal);//将Calendar日期格式转为字符串,如:"20150621"
String d31= getDateStrFromTS(cal31);
for (int i = 0; i < listfile.length; i++) {
if(listfile[i].isFile()){
String filename=listfile[i].getName(); //获取excel文件名
String dayStr=filename.substring(21,29); //截取第一个日期表示文件的当天
if(dayStr.compareTo(d31)>=0&dayStr.compareTo(dayNow)<0){//要求获取近30天的文件
fileFullName.add(filename);
}
}
}
return fileFullName;
}
/*读取excel数据*/
public void read(String srcFile) throws IOException
{
List<String> fileFullName=file(srcFile);
for(String filename:fileFullName){
String dayStr=filename.substring(21,29);
InputStream stream = new FileInputStream(srcFile+File.separator+filename);
Workbook wb = null;
if (filename.endsWith(".xls")) {
wb = new HSSFWorkbook(stream);
}
else if (filename.endsWith(".xlsx")) {
wb = new XSSFWorkbook(stream);
}
else {
System.out.println("您输入的excel格式不正确");
}
Sheet sheet1 = wb.getSheetAt(0);//读取第一张sheet表
for(int i=1;i<sheet1.getLastRowNum();i++){//从第二行开始,因为第一行是表头
int searchNum=(int) sheet1.getRow(i).getCell(0).getNumericCellValue();//读取第1列,是int型
String keyword=sheet1.getRow(i).getCell(4).getStringCellValue();//读取第5列,是String型
insert(dayStr,keyword,searchNum);//数据库进行插入处理
}
}
}
public void insert(String dayStr,String keyword,int searchNum){
Connection con = getConnection();
String sql="insert into xxxxxxx_30days(dayStr,keyword,searchNum) values(?,?,?)";
String[] paras={dayStr,keyword,String.valueOf(searchNum)};
SqlHelper.executeUpdate(sql,paras,con);//<span style="font-family: Arial, Helvetica, sans-serif;">SqlHelper是韩顺平老师jdbc课程中的</span>
}
private Connection getConnection() {
// TODO Auto-generated method stub
try {
if(conn==null||conn.isClosed())
{
String user = "数据库用户名";
String password = "数据库密码";
String url = "数据库的url";
conn = getConnectionByParams(url,user,password);
}
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public String getDateStrFromTS(Calendar cal) {
<span style="white-space:pre"> </span>// 返回用于数据库查询的日期时间字符串
<span style="white-space:pre"> </span> String monthStr = cal.get(Calendar.MONTH)+1<10?"0"+(cal.get(Calendar.MONTH)+1):(cal.get(Calendar.MONTH)+1)+"";
<span style="white-space:pre"> </span> String dateStr = cal.get(Calendar.DAY_OF_MONTH)<10?"0"+cal.get(Calendar.DAY_OF_MONTH):cal.get(Calendar.DAY_OF_MONTH)+"";
<span style="white-space:pre"> </span>return <span style="white-space:pre"> </span>cal.get(Calendar.YEAR)+monthStr+dateStr;
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
}