今天公司要做导入导出功能，所以就在SSM下研究了下 发现还挺简单的做了个工具类以便自己以后使用
文章目录
- 导出
- 导出工具代码如下
- 导入
- Controller:
- service
- serviceimpl
- dao
- mapping
- POI中文API
- 使用的第三方架包是POI,使用的框架是SSM
- 第三方架包下载地址: POI官网
- 如果是使用的maven创建的项目，直接导入依赖就好
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.2</version>
</dependency>导出
导出工具代码如下
首先是获得SXSSFWorkbook类的工具类
工具类代码如下
public SXSSFWorkbook export_Flight (List<Flight> flights) {
SXSSFWorkbook workbook = new SXSSFWorkbook(100);//内存中保留100条数据，以免内存溢出
Sheet sheet = workbook.createSheet();//获取该工作区间的第一个sheet
int flightsSize = flights.size();
Row row = sheet.createRow(0);//标题行
Cell title1 = row.createCell(0);
Cell title2 = row.createCell(1);
Cell title3 = row.createCell(2);
Cell title4 = row.createCell(3);
Cell title5 = row.createCell(4);
Cell title6 = row.createCell(5);
title1.setCellValue("航空编号");
title2.setCellValue("航空公司");
title3.setCellValue("起飞日期");
title4.setCellValue("起飞时间");
title5.setCellValue("起飞城市");
title6.setCellValue("到达城市");
for (int i = 0; i < flightsSize; i++) {
Flight flight = flights.get(i);
Row row1 = sheet.createRow(i + 1);
Cell cell1 = row1.createCell(0);
Cell cell2 = row1.createCell(1);
Cell cell3 = row1.createCell(2);
Cell cell4 = row1.createCell(3);
Cell cell5 = row1.createCell(4);
Cell cell6 = row1.createCell(5);
cell1.setCellValue(flight.getFlightid());
cell2.setCellValue(flight.getFlighname());
cell3.setCellValue(flight.getStartDate());
cell4.setCellValue(flight.getArrivalTime());
cell5.setCellValue(flight.getStartCity());
cell6.setCellValue(flight.getArrivalCity());
}
return workbook;
}我这里是导出一个飞机类的实例，所以命名是这么命名的
然后在控制层写这么一个方法就可以了，代码如下
//导出航班信息
@RequestMapping("/export")
public void export(HttpServletResponse response) {
// 创建新的Excel工作簿
SXSSFWorkbook workbook = null;
UtilPoi export = new UtilPoi();
try {
String excelName = "flight";
OutputStream out = response.getOutputStream();
excelName = new String(excelName.getBytes("GBK"), "ISO8859_1");
response.setHeader("Content-Disposition", "attachment;filename=" + excelName + ".xlsx");
//这里flightService.selectFlightPage(1) 只是得到一个flight的集合 不用太关心
workbook = export.export_Flight(flightService.selectFlightPage(1));
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}然后我们看效果:
至此导出完成了,因为第一次写导出所以写的比价简单，如果大家想要更详细的介绍这里推荐一篇博客感觉写的听详细的，有导入和导出
导入
spring-mvc加入上传文件配置
<!--上传配置-->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- set the max upload size100MB -->
<property name="maxUploadSize">
<value>104857600</value>
</property>
<property name="maxInMemorySize">
<value>4096</value>
</property>
<property name="defaultEncoding">
<value>utf-8</value>
</property>
</bean>页面代码：login.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>登录界面</title>
</head>
<script type="text/javascript" src="../js/jquery-3.3.1.min.js"></script>
<script type="text/javascript" src="../js/jquery.form.js"></script>
<script type="text/javascript">
//ajax 方式上传文件操作
$(document).ready(function(){
$('#btn').click(function(){
if(checkData()){
$('#form1').ajaxSubmit({
url:'../import',
dataType: 'text',
success: resutlMsg,
error: errorMsg
});
function resutlMsg(msg){
alert(msg);
$("#upfile").val("");
}
function errorMsg(){
alert("导入excel出错！");
}
}
});
});
//JS校验form表单信息
function checkData(){
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
alert("选择Excel格式的文件导入！");
return false;
}
return true;
}
</script>
<body>
<div>1.通过简单的form表单提交方式，进行文件的上</br> 2.通过jquery.form.js插件提供的form表单一步提交功能 </div></br>
<form method="POST" enctype="multipart/form-data" id="form1" action="../import">
<table>
<tr>
<td>上传文件: </td>
<td> <input id="upfile" type="file" name="upfile"></td>
</tr>
<tr>
<td><input type="submit" value="提交" onclick="checkData()"></td>
<td><input type="button" value="ajax方式提交" id="btn" name="btn" ></td>
</tr>
</table>
</form>
</body>
</html>Controller:
UserController
@RequestMapping(value = "/import", method = RequestMethod.POST,produces = "application/json;charset=utf-8")
@ResponseBody
public String impotr(HttpServletRequest request, Model model) throws Exception {
//获取上传的文件
MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request;
MultipartFile file = multipart.getFile("upfile");
InputStream in = file.getInputStream();
//数据导入
userService.importExcelInfo(in, file);
in.close();
return "成功！";
}service
UserService.java
List<User> getUserByUserName(String username);
void importExcelInfo(InputStream in, MultipartFile file) throws Exception;serviceimpl
UserServiceImpl.java
public void importExcelInfo(InputStream in, MultipartFile file) throws Exception{
//获取文件名
String filename = file.getName();
XSSFWorkbook workbook = new XSSFWorkbook(in);
//得到Excel工作表对象
XSSFSheet sheets = workbook.getSheetAt(0);
//i 从1开始，去掉表头
for (int i = 1; i < sheets.getLastRowNum(); i++) {
User user = new User();
//获得第一行
XSSFRow row = sheets.getRow(i);
if (row == null) {
break;
}
//获得第一个单元格
XSSFCell cell = row.getCell(0);
if (cell != null) {
//把数据格式化为String 账号
cell.setCellType(Cell.CELL_TYPE_STRING);
user.setUsername(cell.toString());
}
cell = row.getCell(1);
if (cell != null) {
//密码
cell.setCellType(Cell.CELL_TYPE_STRING);
user.setPassword(cell.toString());
}
//去重
if (0 == userMapper.getUserByUserName(user.getUsername()).size()) {
userMapper.insertUser(user);
}
}
}dao
UserMapper.java
void insertUser(User user);
List<User> getUserByUserName(String username);mapping
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.baidu.shiro.dao.UserMapper">
<select id="getUserByUserName" parameterType="String" resultType="User">
select
password
from
users
where username = #{username}
</select>
<insert id="insertUser" parameterType="User">
insert into users
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username">username,</if>
<if test="password">password</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username != null">
#{username},
</if>
<if test="password != null">
#{password}
</if>
</trim>
</insert>
</mapper>ps: 导入可以能遇到科学计数法问题
解决方法：
- 更改excel 的输入格式，把excel的单元格改为文本格式，就可以了
- 也可以在代码层面改，代码如下：
BigDecimal one = new BigDecimal("1.44335632343E21");
System.out.println(one.toPlainString());这是效果图：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
