先通过POI将数据从Excel中读出来
然后拼SQL语句
最后通过JDBC执行SQL语句
然后拼SQL语句
最后通过JDBC执行SQL语句
解决方案 »
- mvn -install error: Could not resolve dependencies for project
- 页面跳转问题
- 关于struts,hidden标签保存form的一个Character类型的property
- 如何做一个移动的短信管理程序!!!请高手帮忙策划下!!!!
- Tomcat中JSTL的问题
- 奇怪的 String index out of range
- 软件用例文裆是什么?
- 求大神帮忙
- 发表新帖子之后的3秒等待页面怎么做啊?
- Tomcat异常???
- 如何判断 鼠标 悬浮对象 是否是滚动条 在线等。。。
- Java Axis2调用C# webService 传递参数问题,就没有一个人能解决问题这问题?这到底有多难。
拼SQL语句,字符串连接而已,还用问吗?
JDBC怎么用,问google
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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;
import org.cupd.spdb.report.importexcel.dao.CardBinDao;import com.websurf.spdb.bo.DBConnBOImpl;public class CardBinDaoImpl implements CardBinDao {
private static final Log log=LogFactory.getLog(CardBinDaoImpl.class);
private static DBConnBOImpl dbConn = new DBConnBOImpl();
PreparedStatement ps = null;
Connection conn = null;
ResultSet rs = null;
private String filePath="E:\\Microsoft Excel.xlsx";
public boolean insertDB(){
boolean flag=true;
try {
//文件流指向excel文件
// FileInputStream fin=new FileInputStream(filePath);
XSSFWorkbook workbook=new XSSFWorkbook(filePath);//创建工作薄
XSSFSheet sheet=workbook.getSheetAt(0);//得到工作表
XSSFRow row=null;//对应excel的行
XSSFCell cell=null;//对应excel的列
int totalRow=sheet.getLastRowNum();//得到excel的总记录条数
log.info("得到excel的总记录数"+totalRow);
//以下的字段一一对应数据库表的字段
String cardName="";
String cardType="";
String cardBin="";
String cardLength="";
String activityCode="";
String note="";
String sql ="insert into BizCardBin(cardName,cardType,cardBin,cardLength,activityCode,note) values(?,?,?,?,?,?)";
for(int i=2;i<=totalRow;i++){
row=sheet.getRow(i);
cell=row.getCell((short) 0);
cardName=cell.getStringCellValue().toString();
cell=row.getCell((short) 1);
cardType=cell.getStringCellValue().toString();
cell=row.getCell((short) 2);
cardBin=cell.getStringCellValue().toString();
cell=row.getCell((short) 3);
cardLength=cell.getStringCellValue().toString();
cell=row.getCell((short) 4);
activityCode=cell.getStringCellValue().toString();
cell=row.getCell((short) 5);
note=cell.getStringCellValue().toString(); ps=conn.prepareStatement(sql);
ps.setString(1,cardName);
ps.setString(2,cardType);
ps.setString(3,cardBin);
ps.setString(4,cardLength);
ps.setString(5,activityCode);
ps.setString(6,note);
ps.execute();
System.out.println("preparestatement successful");
}
dbConnClose(conn, dbConn);
psClose(ps, rs);
} catch (FileNotFoundException e) {
flag=false;
e.printStackTrace();
} catch(IOException ex){
flag=false;
ex.printStackTrace();
} catch(SQLException exx){
flag=false;
exx.printStackTrace();
}
return flag;
}
public static void main(String[] args) {
CardBinDaoImpl e=new CardBinDaoImpl();
e.insertDB();
}
/**
* 插入数据 只需要传入插入sql即可
* 插入sql的样例:insert into t_department values('D004','金融部');
* @param insert 插入语句
* @return
* @throws SQLException
*/
/*public int insertCardBin(BizCardBin cardBin){
int result=0;
PreparedStatement ps = null;
Connection conn = null;
String sql ="insert into BizCardBin(cardName,cardTyoe,cardBin,cardLength,cardLength,activityCode,note) values(?,?,?,?,?,?,?)";
try {
conn=dbConn.getConnection();
log.info("获取JDBC连接完成");
ps = conn.prepareStatement(sql);
ps.setString(1, cardBin.getCardName());
ps.setString(2, cardBin.getCardType());
ps.setString(3, cardBin.getCardBin());
ps.setString(4, cardBin.getCardLength());
ps.setString(5, cardBin.getActivityCode());
ps.setString(6, cardBin.getNote());
result = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
log.error("SQL Exception "+ e);
e.printStackTrace();
return 0;
}finally{
dbConnClose(conn, dbConn);
psClose(ps, rs);
}
return result;
}
*/
public void dbConnClose(Connection conn,DBConnBOImpl dbConn){
if(conn!=null){
dbConn.close(conn);
}
}
private static void psClose(final PreparedStatement preparedStatement,
final ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
log.error("error", e);
}
}
}
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class CardBinImportExcel {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row; /**
* 读取Excel表格表头的内容
* @param InputStream
* @return String 表头内容的数组
*/
@SuppressWarnings("deprecation")
public String[] readExcelTitle(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
//title[i] = getStringCellValue(row.getCell((short) i));
title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
} /**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
@SuppressWarnings("deprecation")
public Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String excelStr = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
// 每个单元格的数据内容用"-"分割开,以后需要时用String类的replace()方法还原数据
// 也可以将每个单元格的数据设置到一个javabean的属性中,此时需要新建一个javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
excelStr += getStringCellValue
(row.getCell((short) j)).trim() + "&";
j++;
}
content.put(i, excelStr);
excelStr = "";
}
return content;
} /**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
@SuppressWarnings("unused")
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
} /**
* 获取单元格数据内容为日期类型的数据
*
* @param cell
* Excel单元格
* @return String 单元格数据内容
*/
@SuppressWarnings({ "unused", "deprecation" })
private String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}
return result;
} /**
* 根据HSSFCell类型设置数据
* @param cell
* @return
*/
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则,转化为Data格式
//方法1:这样子的data格式是带时分秒的:2011-10-12 0:00:00
//cellvalue = cell.getDateCellValue().toLocaleString();
//方法2:这样子的data格式是不带带时分秒的:2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue; } public static void main(String[] args) {
try {
// 对读取Excel表格标题测试
InputStream is = new FileInputStream("E:\\Microsoft Excel.xlsx");
CardBinImportExcel excelReader = new CardBinImportExcel();
String[] title = excelReader.readExcelTitle(is);
System.out.println("获得Excel表格的标题:");
for (String s : title) {
System.out.print(s + " ");
} // 对读取Excel表格内容测试
InputStream is2 = new FileInputStream("E:\\Microsoft Excel.xlsx");
Map<Integer, String> map = excelReader.readExcelContent(is2);
System.out.println("获得Excel表格的内容:");
for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
} } catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}
}
}
希望能帮到你
http://download.csdn.net/detail/holdon521/6876403