POI技术实现EXCEL对ORCLE的导入导出
下面是导入的架包
我在orcle中用的表是Company
先是连接数据库驱动
public class Driver {
private final String OrcleDrivar="oracle.jdbc.driver.OracleDriver";
private final String OrcleUrl="jdbc:oracle:thin:@localhost:1521:orcl";
private final String name="scott";
private final String pwd="tiger";
private Connection conn=null;
//加载驱动
public Driver(){
try {
Class.forName(OrcleDrivar);
this.conn=DriverManager.getConnection(OrcleUrl,name,pwd);
} catch (Exception e) {
// TODO Auto-generated catch block
System.out.println("数据库连接异常");
e.printStackTrace();
}
}
//去的是数据库连jie
public Connection getcon(){
return conn;
}
//关闭数据库链接
public void close(){
try {
this.conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("数据库关闭失败");
e.printStackTrace();
}
}
}
JavaBean层
public class Company {
private int c_id;
private String c_name;
private String c_carname;
private String c_linkman;
private String c_tel;
private String c_address;
public Company(){
}
public Company(int c_id,String c_name,String c_carname,String c_linkman,String c_tel, String c_address){
this.c_id=c_id;
this.c_name=c_name;
this.c_carname=c_carname;
this.c_linkman=c_linkman;
this.c_tel=c_tel;
this.c_address=c_address;
}
public int getC_id() {
return c_id;
}
public void setC_id(int c_id) {
this.c_id = c_id;
}
public String getC_name() {
return c_name;
}
public void setC_name(String c_name) {
this.c_name = c_name;
}
public String getC_carname() {
return c_carname;
}
public void setC_carname(String c_carname) {
this.c_carname = c_carname;
}
public String getC_linkman() {
return c_linkman;
}
public void setC_linkman(String c_linkman) {
this.c_linkman = c_linkman;
}
public String getC_tel() {
return c_tel;
}
public void setC_tel(String c_tel) {
this.c_tel = c_tel;
}
public String getC_address() {
return c_address;
}
public void setC_address(String c_address) {
this.c_address = c_address;
}
}
数据库方法实现层
public class actionDao implements Dao {
@Override
public List<Company> queryAll() {
// TODO Auto-generated method stub
Driver driver=new Driver();
List<Company> list=new ArrayList<Company>();
String sql="select * from company";
Company Dao=null;
PreparedStatement per=null;
try{
per=driver.getcon().prepareStatement(sql);
ResultSet re=per.executeQuery();
System.out.println(re.next());
while(re.next()){
Dao=new Company();
Dao.setC_id(re.getInt(1));
Dao.setC_name(re.getString(2));
Dao.setC_carname(re.getString(3));
Dao.setC_linkman(re.getString(4));
Dao.setC_tel(re.getString(5));
Dao.setC_address(re.getString(6));
list.add(Dao);
}
System.out.println(list);
per.close();
driver.close();
}catch(Exception e){
System.out.println("查询全部异常");
}
return list;
}
@Override
public boolean insert(Company dao) {
// TODO Auto-generated method stub
Driver driver=new Driver();
boolean flag=false;
PreparedStatement per=null;
String sql="insert into company values(?,?,?,?,?,?)";
try{
per=driver.getcon().prepareStatement(sql);
per.setInt(1, dao.getC_id());
per.setString(2, dao.getC_name());
per.setString(3, dao.getC_carname());
per.setString(4, dao.getC_linkman());
per.setString(5, dao.getC_tel());
per.setString(6, dao.getC_address());
if(per.executeUpdate()!=0){
flag=true;
}
driver.getcon().commit();
per.close();
driver.close();
}catch(Exception e){
System.out.println("增加异常");
}
return flag;
}
@Override
public boolean update1(Company dao) {
// TODO Auto-generated method stub
Driver driver=new Driver();
boolean flag=false;
PreparedStatement per=null;
String sql="update company c_name=?,c_carname=?,c_linkman=?,c_tel=?,c_address=? where c_id=?";
try{
per=driver.getcon().prepareStatement(sql);
per.setString(1, dao.getC_name());
per.setString(2, dao.getC_carname());
per.setString(3, dao.getC_linkman());
per.setString(4, dao.getC_tel());
per.setString(5, dao.getC_address());
per.setInt(6, dao.getC_id());
if(per.executeUpdate()!=0){
flag=true;
}
driver.getcon().commit();
per.close();
driver.close();
}catch(Exception e){
System.out.println("修改异常");
}
return flag;
}
@Override
public boolean delete(int id) {
// TODO Auto-generated method stub
Driver driver=new Driver();
// TODO Auto-generated method stub
boolean flag=false;
PreparedStatement per=null;
String sql="delete from company where c_id=?";
try {
per=driver.getcon().prepareStatement(sql);
per.setInt(1, id);
if(per.executeUpdate()!=0){
flag=true;
}
driver.getcon().commit();
per.close();
driver.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("删除异常");
e.printStackTrace();
}
return flag;
}
@Override
public Company update(int id) {
// TODO Auto-generated method stub
Driver driver=new Driver();
//impDao imp=new impDao();
String sql="select * from company where c_id=?";
Company Dao=null;
PreparedStatement per=null;
try {
per=driver.getcon().prepareStatement(sql);
per.setInt(1, id);
ResultSet re=per.executeQuery();
while(re.next()){
Dao=new Company();
Dao.setC_id(re.getInt(1));
Dao.setC_name(re.getString(2));
Dao.setC_carname(re.getString(3));
Dao.setC_linkman(re.getString(4));
Dao.setC_tel(re.getString(5));
Dao.setC_address(re.getString(6));
}
driver.getcon().commit();
per.close();
driver.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return Dao;
}
@Override
public Company mixquery(String name) {
// TODO Auto-generated method stub
Driver driver=new Driver();
//impDao imp=new impDao();
String sql="select * from company where c_name like ?";
Company Dao=null;
PreparedStatement per=null;
try {
per=driver.getcon().prepareStatement(sql);
per.setString(1, name);
ResultSet re=per.executeQuery();
System.out.println(re);
while(re.next()){
Dao=new Company();
Dao.setC_id(re.getInt(1));
Dao.setC_name(re.getString(2));
Dao.setC_carname(re.getString(3));
Dao.setC_linkman(re.getString(4));
Dao.setC_tel(re.getString(5));
Dao.setC_address(re.getString(6));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return Dao;
}
}
读入EXCEL
public class Readxlsx {
@SuppressWarnings("resource")
public List<Company> readExcel() throws Exception{
/*由于HSSFWorkbook只能操作excel2003一下版本,XSSFWorkbook只能操作excel2007以上版本,
所以利用Workbook接口创建对应的对象操作excel来处理兼容性
比如说 :
我先创建一个文件
String file="C:\Users\Administrator\Desktop\华安公司.xlsx";
boolean isExecl2003=file.toLowerCase().endsWith("xls")?true:false;
Workbook woorbook=null;
if(isExecl2003){
woorbook=new HSSFWorkbook(new FileInputStream(new File(file)));
}else{
workbook = new XSSFWorkbook(new FileInputStream(new File(file)));
}
*/
File file=new File("华安公司.xlsx");
//判断文件存在不
if(file.isFile()&&file.exists()){
System.out.println("文件存在");
}else{
System.out.println("文件不存在");
}
FileInputStream input=new FileInputStream(file);//括号中放入需要导入的Excel文件
HSSFWorkbook workbook=new HSSFWorkbook(input);
//XSSFworkbook workbook =new XSSFworkbook(input);//创建一个excel表格:
Company com=null;//实例化公司对象
List <Company> list=new ArrayList<Company>();
//循环工作表sheet
for(int sheet=0;sheet<workbook.getNumberOfSheets();sheet++){
HSSFSheet sheet1=workbook.getSheetAt(sheet);/*XSSFSheet用来创建一个工作表格
然后在把读入文件中的表格行数赋给当前的表格
*/
//判断的到的sheet1是不是空值
if(sheet1==null){
continue;
}
//循环行rownum
for(int rownum=1;rownum<=sheet1.getLastRowNum();rownum++){
//创建行
HSSFRow row=sheet1.getRow(rownum);
if(row!=null){
com=new Company();
//创建单元格
HSSFCell c_id=row.getCell(0);
HSSFCell c_name=row.getCell(1);
HSSFCell c_carname=row.getCell(2);
HSSFCell c_linkman=row.getCell(3);
HSSFCell c_tel=row.getCell(4);
HSSFCell c_address=row.getCell(5);
com.setC_id((int)(Float.parseFloat(getcellValue(c_id))));
com.setC_name(getcellValue(c_name));
com.setC_carname(getcellValue(c_carname));
com.setC_linkman(getcellValue(c_linkman));
com.setC_tel(getcellValue(c_tel));
com.setC_address(getcellValue(c_address));
list.add(com);
}
}
}
return list;
}
private String getcellValue(HSSFCell cell) {
// TODO Auto-generated method stub
if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){
//返回数值型
return String.valueOf(cell.getNumericCellValue());
}else if(cell.getCellType()==cell.CELL_TYPE_STRING){
//返回字符串类型
return String.valueOf(cell.getStringCellValue());
}
return null;
}
}
public class SaveSql {
public void Save() throws Exception {
Readxlsx read=new Readxlsx();
Company com=new Company();
actionDao Dao=new actionDao();
List <Company> list=read.readExcel();
for(int i=0;i<list.size();i++){
com=list.get(i);
Company number=Dao.mixquery("%"+com.getC_name()+"%");
if(number!=null){
System.out.println(number.getC_name()+"该对象已存在无法插入");
}else{
Dao.insert(com);
}
}
}
}
EXCEL从数据库导出
public class Writexslx {
public void CreateWork(List <Company> list) throws Exception{
//获得总行数
int columnnum=list.size();
//Excel创建文本
HSSFWorkbook workbook=new HSSFWorkbook();
Company com=null;
//创建EXCEL的表
HSSFSheet sheet=workbook.createSheet("华安公司");
//设置单元格宽度
sheet.setColumnWidth((short)3, 20*256);
//设置单元格高度
sheet.setDefaultRowHeight((short)300);
//
//创建列
HSSFRow row=sheet.createRow(0);//下标从0开始
//创建单元格
HSSFCell[] cell=new HSSFCell[columnnum];
//设置第一行的字段名
String [] names=new String[columnnum];
names[0]="编号";
names[1]="姓名";
names[2]="司机名";
names[3]="相关联的人";
names[4]="电话";
names[5]="地址";
for(int i=0;i<columnnum;i++){
cell[i]=row.createCell(i);
cell[i].setCellValue(new HSSFRichTextString(names[i]));
}
for(int j=0;j<list.size();j++){
//创建一行
HSSFRow row1=sheet.createRow(j+1);
//得到要插入你的每一条记录
com=list.get(j);
for(int col=0;col<5;col++){
//给每个单元格赋值
HSSFCell c_id=row1.createCell(0);
HSSFCell c_name=row1.createCell(1);
HSSFCell c_carname=row1.createCell(2);
HSSFCell c_linkman=row1.createCell(3);
HSSFCell c_tel=row1.createCell(4);
HSSFCell c_address=row1.createCell(5);
c_id.setCellValue(com.getC_id());
c_name.setCellValue(com.getC_name());
c_carname.setCellValue(com.getC_carname());
c_linkman.setCellValue(com.getC_linkman());
c_tel.setCellValue(com.getC_tel());
c_address.setCellValue(com.getC_address());
}
}
//创建输出流,导出到桌面 ,公司名为乱写的,如有相同,纯属无意
FileOutputStream out=new FileOutputStream("C:/Users/Administrator/Desktop/华安公司.xlsx");
workbook.write(out);
out.close();
System.out.println("数据库导出成功");
}
}
测试类
public class text {
public static void main(String[] args) {
// TODO Auto-generated method stub
//注掉的部分为导入的测试方法
/*SaveSql sa=new SaveSql();
try {
sa.Save();
System.out.println("进去了,哈哈哈");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
Writexslx wri=new Writexslx();
actionDao dao=new actionDao();
List<Company> list=dao.queryAll();
try {
wri.CreateWork(list);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("成功");
}
}