利用dorado实现excel数据上传到mysql数据库
实现过程中遇到巨多的bug, 初步估计至少20个异常,但是最后还是实现了. 具体的过程不细说了, 把大概需要注意的几点说一下
1 基本步骤是: java代码读取excel表中的数据, dorado中配置mysql的连接, 设计对应excel的表, 设计映射表,
其中我遇到的一些坑: mysql数据库不支持序列化, 而联合主键又得有主键, 至今不知道怎么处理联合主键, 我是直接添加了一个id作为主键
保存的时候用了hibernate的save接口, 但是无法保存到数据库中, 原因有几点: 配置文件没有<prop key="hibernate.connection.autocommit">true</prop>
或者是没有写@Transaction注解, 就没提交
配置mysql的时候,一定要仔细, 还要导入相应的jar包, 一般classNotFound就是jar包没有导入的问题
数据库设计的时候, 字段不能有关键字 如 desc
导入mysql方言的时候hibernate.dialect=org.hibernate.dialect.MySQL5Dialect其中的y是小写, 写成大写就凉了
2 我的基本代码如下
实体类, excel表格中除了没有id, 别的字段都有, 我开始以name和datee作为主键, 走了很多弯路
@Entity
@Table(name="EXDATA")
public class ExcelEntity {
//private static final long serialVersionUID = 6076304611179489259L;
@Id
@Column(name="id")
private int id;
@Column(name="name")
private String name;
@Column(name = "dept")
private String dept;
@Column(name="datee")
private String datee;
@Column(name = "result")
private String result;
@Column(name = "lateTime")
private String lateTime;
@Column(name = "beforeTime")
private String beforeTime;
@Column(name = "goWork")
private String goWork;
@Column(name = "afterWork")
private String afterWork;
@Column(name = "descc")
private String descc;
@Column(name = "sign")
private String sign;
@Column(name = "deptLeader")
private String deptLeader;
@Column(name = "govLeader")
private String govLeader;
继承自hibernateD的dao类
@Repository
public class ExcelDao extends HibernateDao<ExcelEntity,Integer>{
}
实现类
@Component
public class ExcelDemo {
// 注入dao对象
@Resource
public ExcelDao excelDao;
@Transactional
@Expose
public void getAllByExcel(){
List<ExcelEntity> list=new ArrayList<ExcelEntity>();
try{
// 获取excel表格对象
Workbook rwb=Workbook.getWorkbook(new File("E:\\创新产品事业部12月未正常打卡记录.xls"));
Sheet rs=rwb.getSheet(0);
int clos=rs.getColumns();//得到所有的列
int rows=rs.getRows();//得到所有的行
int index=0;
for (int i =2; i < rows; i++) {//除去表头,应该从第三行开始
for (int j = 0; j < clos; j++) {
//第一个是列数，第二个是行数
String name=rs.getCell(j++, i).getContents();
String dept=rs.getCell(j++, i).getContents();
String datee=rs.getCell(j++, i).getContents();
String result=rs.getCell(j++, i).getContents();
String lateTime=rs.getCell(j++, i).getContents();
String beforeTime=rs.getCell(j++, i).getContents();
String goWork=rs.getCell(j++, i).getContents();
String afterWork=rs.getCell(j++, i).getContents();
String descc=rs.getCell(j++, i).getContents();
String sign=rs.getCell(j++, i).getContents();
String deptLeader=rs.getCell(j++, i).getContents();
String govLeader=rs.getCell(j++, i).getContents();
// 将一行数据封装到实体中
ExcelEntity entity=new ExcelEntity();
entity.setAfterWork(afterWork);
entity.setBeforeTime(beforeTime);
entity.setDept(dept);
entity.setDeptLeader(deptLeader);
entity.setdescc(descc);
entity.setGovLeader(govLeader);
entity.setGoWork(goWork);
entity.setLateTime(lateTime);
entity.setResult(result);
entity.setSign(sign);
entity.setName(name);
entity.setDatee(datee);
entity.setId(index++);
list.add(entity);
}
}
}catch(Exception e){
e.printStackTrace();
}
//保存操作
for (ExcelEntity excelEntity : list) {
System.out.println(excelEntity);
excelDao.save(excelEntity);
System.out.println("save执行完毕");
}
}
}
最后是一些配置文件, 虽然用的少,但是真的重要 app-context.xml, 还有一个配置文件, 是configure.propertiesl就是存放一些属性供app-context的变量使用,比较简单
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/jee
http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<context:component-scan base-package="sample" />
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url"
value="jdbc:mysql:${jdbc.url}?useUnicode=true&characterEncoding=utf8" />
<property name="username" value="${jdbc.userName}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="entityInterceptor" ref="dorado.unByteCodeProxyInterceptor" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.showSql}</prop>
<prop key="hibernate.format_sql">${hibernate.formatSql}</prop>
<prop key="hibernate.connection.autocommit">true</prop>
</props>
</property>
<property name="packagesToScan">
<list>
<value>com/bstek/dorado/sample/entity</value>
</list>
</property>
</bean>
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />
<context:component-scan base-package="com.bstek.dorado.sample" />
</beans>
随便附上excel导出的代码
@Expose
public void getAllBySql(){
/**
* 从数据库读取数据
*/
List<ExcelEntity> list=new ArrayList<ExcelEntity>();
list=excelDao.getAll();
// 将数据保存到excel中
//开始写入excel,创建模型文件头
String[] titleA = {"id","姓名","部门","日期","考勤结果","迟到时间","早退时间","上班","下班","说明","签字","领导","老板"};
//导入的excel的位置
File file = new File("e:/TestFile.xls");
if(file.exists()){
//如果文件存在就删除
file.delete();
}
try{
file.createNewFile();
//创建工作簿
WritableWorkbook workbookA = Workbook.createWorkbook(file);
WritableSheet sheetA = workbookA.createSheet("sheet1", 0);
Label labelA = null;
//设置列名
for (int i = 0; i < titleA.length; i++) {
labelA = new Label(i,0,titleA[i]);//第0行
sheetA.addCell(labelA);
}
// 设置单元格属性
WritableCellFormat wc = new WritableCellFormat();
// 设置居中
wc.setAlignment(Alignment.CENTRE);
// 设置边框线
wc.setBorder(Border.ALL, BorderLineStyle.THIN);
//插入数据
int i=1;//从第二行开始
for (ExcelEntity excelEntity : list) {
int j=0;//从第1列开始
Integer id=excelEntity.getId();
//向特定格子写入数据
Label label = new Label(j++, i,id.toString(),wc);
sheetA.addCell(label);
Label label01 = new Label(j++, i,excelEntity.getName(),wc);
sheetA.addCell(label01);
Label label02 = new Label(j++, i,excelEntity.getDept(),wc);
sheetA.addCell(label02);
Label label03 = new Label(j++, i,excelEntity.getDatee(),wc);
sheetA.addCell(label03);
Label label04 = new Label(j++, i,excelEntity.getResult(),wc);
sheetA.addCell(label04);
Label label05 = new Label(j++, i,excelEntity.getLateTime(),wc);
sheetA.addCell(label05);
Label label06 = new Label(j++, i,excelEntity.getBeforeTime(),wc);
sheetA.addCell(label06);
Label label07 = new Label(j++, i,excelEntity.getGoWork(),wc);
sheetA.addCell(label07);
Label label09 = new Label(j++, i,excelEntity.getAfterWork(),wc);
sheetA.addCell(label09);
Label label08 = new Label(j++, i,excelEntity.getdescc(),wc);
sheetA.addCell(label08);
i++;
}
workbookA.write();
workbookA.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("文件写入失败，报异常...");
}
