项目场景:
项目场景:Excel的导入与导出
环境配置:
- 创建项目,导入jar包,创建包,创建类,创建接口(不知道操作的可以看之前的文章)
- 配置web.xml
<!-- 配置spring监听 -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- 配置springmvc -->
<servlet>
<servlet-name>spring-mvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring-mvc.xml</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>spring-mvc</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<!-- 配置编码 -->
<filter>
<filter-name>encoding-filter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encoding-filter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
- 配置spring-mvc.xml
<!-- 扫描具体的包路径 -->
<context:component-scan base-package="com.lwy.controller"></context:component-scan>
<!-- 开启springmvc注解扫描 自动开启(扫描)处理器适配器和处理器映射器 -->
<mvc:annotation-driven/>
<!-- 注入转换器 -->
<!-- <bean name="conversionService" class="org.springframework.context.support.ConversionServiceFactoryBean">
<property name="converters">
<set>
<bean class="com.lwy.converter.DateConvertor"></bean>
</set>
</property>
</bean> -->
<!-- 加载静态资源-->
<mvc:default-servlet-handler/>
<!-- 配置springmvc上传工具 -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- 通过属性来规范上传文件的信息 -->
<property name="defaultEncoding" value="UTF-8"></property>
<!-- 上傳文件的最大的字節數 -->
<property name="maxUploadSize" value="5000000"></property>
</bean>
<!-- 视图解析器 -->
<bean
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<!-- 前缀 -->
<property name="prefix" value="/WEB-INF/jsp/"></property>
<!-- 后缀 -->
<property name="suffix" value=".jsp"></property>
</bean>
- 配置applicationContext.xml
<!-- 开启spring扫描 -->
<context:component-scan base-package="com.lwy"></context:component-scan>
<!--引入jdbc-->
<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
<!-- 配置hibernate -->
<!-- 配置sessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<!-- 1、配置数据库链接 -->
<property name="dataSource" ref="myDataSource"></property>
<!-- 配置hibernate相关内容 -->
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
<!-- 加载映射 -->
<!-- private String[] packagesToScan; -->
<property name="packagesToScan">
<list>
<value>com.lwy.pojo</value>
</list>
</property>
</bean>
<!-- 2、配置数据库链接信息 -->
<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="driverClass" value="${jdbc.driver}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
</bean>
<!-- 注册事物管理器 -->
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory"></property>
</bean>
<!-- 注册事物通知 -->
<tx:advice id="myAdvice" transaction-manager="transactionManager">
<tx:attributes>
<!--
name : 方法名 可以使用匹配符
isolation : 隔离级别
propagation : 事物的传播的行为
read-only : 只读
-->
<tx:method name="save*" isolation="DEFAULT" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="insert*" isolation="DEFAULT" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="add*" isolation="DEFAULT" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="update*" isolation="DEFAULT" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="delete*" isolation="DEFAULT" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="*" isolation="DEFAULT" propagation="REQUIRED" read-only="true"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut expression="execution(* com.lwy.service.*.*(..))" id="pointcut"/>
<!-- 顾问 : 切面的另一种实现 -->
<aop:advisor advice-ref="myAdvice" pointcut-ref="pointcut"/>
</aop:config>
-
hibernate的相关配置,在上图
-
抽取的jdbc——jdbc.properties
jdbc.user=root
jdbc.password=11111
jdbc.url=jdbc:mysql://127.0.0.1:3306/day3_09_db?characterEncoding=utf8
jdbc.driver=com.mysql.jdbc.Driver
创建POI导入:
步骤:
代码示例:(1)判断当前excel版本(截取后缀), 创建对象(将当前文件封装进来)
(2)获取sheet页
(3)获取行(row)
(4)获取单元格(cell)
//引入自定义的数据类型
@Autowired
private BookService bookService;
//poi导入
@RequestMapping("uploadExcel")
public String uploadExcel(MultipartFile excelFile) throws IOException{
//获取文件大小,如果为0不解析内容不上传
if (excelFile.getSize() <= 0) {
return "list";
}
//判断当前Excel的版本
//获取文件的名称
String filename = excelFile.getOriginalFilename();
//截取后缀
String suffix = filename.substring(filename.lastIndexOf(".")+1);
//定义公共接收的父类的workbook
Workbook workbook = null;
//定义文件输入流
InputStream inputStream = excelFile.getInputStream();
//创建集合存放数据
List<Object[]> list = new ArrayList<Object[]>();
//判断如果后缀是xlsx则调用xlsx的方法
if (suffix.equals("xlsx")) {
//文件输入流
workbook = new XSSFWorkbook(inputStream); //07 最大行104000 最大列16000
}else if (suffix.equals("xls")) {
workbook = new HSSFWorkbook(inputStream); //03 60000 256
}
//获取sheet页
Sheet sheet = workbook.getSheetAt(0);
//如果为空,不需要上传
if (sheet == null) {
return "list";
}
//获取最后一行的下标
int lastnum = sheet.getLastRowNum();
//有行数了循环获取 如果略过表头,i不能从0开始
for (int i = 1; i < lastnum; i++) {
//获取行
Row row = sheet.getRow(i);
//通过行获取列
//获取开始单元格位置从0开始
short firstCellNum = row.getFirstCellNum();
//获取最后一个单元格配置
short lastCellNum = row.getLastCellNum();
//判断如果两者相等说明当前行没有数据,跳出继续下一次循环
if (firstCellNum == lastCellNum) {
continue;
}
//定义数组,存放单元格的值
Object[] o = new Object[lastCellNum];
//继续循环
for (int j = firstCellNum; j < lastCellNum; j++) {
//获取单元格
Cell cell = row.getCell(j);
//借助工具类,因为poi转换的时候,Excel中的为数字解析的时候不一样
Object object = ExcelUtil.parseExcel(cell);
//放到数组中
o[j] = object;
// System.out.println(object);
}
//将获取到的数据保存到list集合
list.add(o);
}
POI导出:
步骤:
(1)创建workBook, 对应一个Excel文件
(2)在workbook中创建一个sheet
(3)设置样式
(4)在表格中创建表头(row cell)
(5)将查询出的结果存放到表格中
(6)将workbook通过io写入到某个文件夹中
代码示例:
// poi导出
@RequestMapping("exportExcel")
public String exportExcel(){
// 1.创建WorkBook
Workbook workbook = new XSSFWorkbook();
// 2.在WorkBook里面创建一个sheet表
Sheet sheet = workbook.createSheet();
// 3.设置样式
CellStyle style = workbook.createCellStyle();
//设置居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置上下居中
style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
//创建字体
Font font = workbook.createFont();
//设置字体颜色
font.setColor(IndexedColors.RED.index);
//设置字号
font.setFontHeightInPoints((short)20);
//设置是否为斜体
font.setItalic(true);
//将设置的字体样式放入到样式中style
style.setFont(font);
// 第一个单元格
Row row = sheet.createRow(0);
// 4.创建表头cell
String[] title = {"作者","日期","书名"};
//每次循环创建行,每行给他一个value
for (int i = 0; i < title.length; i++) {
//创建单元格
Cell cell = row.createCell(i);
//每一行都给赋值
cell.setCellValue(title[i]);
//加上样式
cell.setCellStyle(style);
}
// 5.将查询的结果放到表格中
List<Book> blist = bookService.getInfo();
for (int i = 0; i < blist.size(); i++) {
Book book = blist.get(i);
//从第二行开始
Row row2 = sheet.createRow(i + 1);
Cell cell = row2.createCell(0);
cell.setCellValue(book.getBookAutor());
Cell cell2 = row2.createCell(1);
if (book.getBookDate() != null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
cell2.setCellValue(sdf.format(book.getBookDate()));
}
row2.createCell(2).setCellValue(book.getBookName());
}
// 6.将Workbook通过io写出到某个文件中
try {
FileOutputStream fis = new FileOutputStream("E:/upload/book/"+UUID.randomUUID() + ".xlsx");
workbook.write(fis);
fis.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "list";
}
目录结构
-
目录结构
-
创建dao
void addBookInfo(Book b);
List<Book> getInfo();
- 创建dao层实现类
@Repository
public class BookDaoImpl implements BookDao {
@Autowired
private SessionFactory sessionFactory;
//POI写入到实体类中
@Override
public void addBookInfo(Book b) {
// 创建session
Session session = sessionFactory.getCurrentSession();
session.save(b);
}
//写出
@Override
public List<Book> getInfo() {
// TODO Auto-generated method stub
Session session = sessionFactory.getCurrentSession();
Query query = session.createQuery("from Book");
return query.list();
}
}
- 创建service的实现类
@Autowired
private BookDao bookDao;
@Override
public void addBookInfo(Book b) {
// TODO Auto-generated method stub
bookDao.addBookInfo(b);
}
@Override
public List<Book> getInfo() {
// TODO Auto-generated method stub
return bookDao.getInfo();
}
- 创建service
void addBookInfo(Book b);
List<Book> getInfo();
- 上传界面
<form action="<%=path%>/poi/uploadExcel" method="post" enctype="multipart/form-data">
<input type="file" name="excelFile">
<input type="submit" value="提交">
</form>
- 下载界面
<form action="<%=path%>/poi/exportExcel">
<input type="submit" value="下载">
</form>
jar包提取码:
链接:https://pan.baidu.com/s/17amUuKr8Kzg3qLCf4oyGSw
提取码:ykom
复制这段内容后打开百度网盘手机App,操作更方便哦