用了大半个月的若伊框架,给我的感受是它很强大,很实用。几乎涵盖了后台管理系统的所有模块。但是在一些细节方面需要我们自己去拓展。例如若伊给我们提供了导入导出Exel格式数据的demo,但是相对于Exel文件,csv文件能够存储的数据更多。
那什么是csv文件呢?
csv就相当于txt文档存储数据,单个数据之间用","号分割
例如:
那么如何使用若伊框架导入csv格式的数据。请看源码!
首先配置类
package com.ruoyi.common.utils.poi;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.common.utils.StringUtils;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
/*
* 解析csv文件的输入流工具类
* */
public class CSVUtils {
private final static String CSV_LOWER = "csv";
private final static String CSV = "CSV";
/**
* 功能说明:获取UTF-8编码文本文件开头的BOM签名。
* BOM(Byte Order Mark),是UTF编码方案里用于标识编码的标准标记。例:接收者收到以EF BB BF开头的字节流,就知道是UTF-8编码。
* @return UTF-8编码文本文件开头的BOM签名
*/
public static String getBom() {
byte[] b = {(byte)0xEF, (byte)0xBB, (byte)0xBF};
return new String(b);
}
/**
* 生成CVS文件
* @param exportData
* 源数据List
* @param map
* csv文件的列表头map
* @param outPutPath
* 文件路径
* @param fileName
* 文件名称
* @return
*/
@SuppressWarnings("rawtypes")
public static File createCsvFile(List exportData, LinkedHashMap map, String outPutPath,
String fileName) {
File csvFile = null;
BufferedWriter csvFileOutputStream = null;
try {
File file = new File(outPutPath);
if (!file.exists()) {
file.mkdirs();
}
//定义文件名格式并创建
String fileOpath = outPutPath + File.separator + fileName+".csv";
csvFile =new File(fileOpath);
file.createNewFile();
// UTF-8使正确读取分隔符","
//如果生产文件乱码,windows下用gbk,linux用UTF-8
csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
csvFile), "UTF-8"), 1024);
//写入前段字节流,防止乱码
csvFileOutputStream.write(getBom());
// 写入文件头部
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
csvFileOutputStream.write((String) propertyEntry.getValue() != null ? (String) propertyEntry.getValue() : "" );
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
csvFileOutputStream.newLine();
// 写入文件内容
for (Iterator iterator = exportData.iterator(); iterator.hasNext();) {
Object row = (Object) iterator.next();
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator
.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator
.next();
String str = "";
if(row !=null && !"".equals( propertyEntry.getKey()) && null != propertyEntry.getKey() && ((Map)row).get(propertyEntry.getKey()) != null){
str=((Map)row).get(propertyEntry.getKey()).toString();
}
if(StringUtils.isEmpty(str)){
str="";
}else{
if(str.indexOf(",")>=0){
str="\""+str+"\"";
}
}
csvFileOutputStream.write(str);
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
if (iterator.hasNext()) {
csvFileOutputStream.newLine();
}
}
csvFileOutputStream.flush();
} catch (Exception e) {
//log.error("error:{}", e);
} finally {
try {
csvFileOutputStream.close();
} catch (IOException e) {
// log.error("error:{}", e);
}
}
return csvFile;
}
/**
* 生成并下载csv文件
* @param response
* @param exportData
* @param map
* @param outPutPath
* @param fileName
* @throws IOException
*/
@SuppressWarnings("rawtypes")
public static void exportDataFile(HttpServletResponse response, List exportData, LinkedHashMap map, String outPutPath, String fileName) throws IOException{
File csvFile = null;
BufferedWriter csvFileOutputStream = null;
try {
File file = new File(outPutPath);
if (!file.exists()) {
file.mkdirs();
}
//定义文件名格式并创建
csvFile =new File(outPutPath+fileName+".csv");
if(csvFile.exists()){
csvFile.delete();
}
csvFile.createNewFile();
// UTF-8使正确读取分隔符","
//如果生产文件乱码,windows下用gbk,linux用UTF-8
csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"), 1024);
//写入前段字节流,防止乱码
csvFileOutputStream.write(getBom());
// 写入文件头部
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
csvFileOutputStream.write((String) propertyEntry.getValue() != null ? (String) propertyEntry.getValue() : "" );
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
csvFileOutputStream.newLine();
if(CollectionUtils.isNotEmpty(exportData)) {
// 写入文件内容
for (Iterator iterator = exportData.iterator(); iterator.hasNext(); ) {
Object row = (Object) iterator.next();
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator
.hasNext(); ) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator
.next();
String str = "";
if (row != null && !"".equals(propertyEntry.getKey()) && null != propertyEntry.getKey() && ((Map) row).get(propertyEntry.getKey()) != null) {
str = ((Map) row).get(propertyEntry.getKey()).toString();
}
if (StringUtils.isEmpty(str)) {
str = "";
} else {
if (str.indexOf(",") >= 0) {
str = "\"" + str + "\"";
}
}
csvFileOutputStream.write(str);
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
if (iterator.hasNext()) {
csvFileOutputStream.newLine();
}
}
}
csvFileOutputStream.flush();
} catch (Exception e) {
//log.error("error:{}", e);
} finally {
try {
csvFileOutputStream.close();
} catch (IOException e) {
//log.error("error:{}", e);
}
}
downFile(response,outPutPath,fileName);
csvFile.delete();
}
private static void downFile(HttpServletResponse response, String outPutPath, String fileName)throws IOException{
InputStream in = null;
try {
in = new FileInputStream(outPutPath+fileName+".csv");
int len = 0;
byte[] buffer = new byte[1024];
OutputStream out = response.getOutputStream();
response.setContentType("application/csv;charset=UTF-8");
response.setHeader("Content-Disposition","attachment; filename=" + URLEncoder.encode(fileName+".csv", "UTF-8"));
response.setCharacterEncoding("UTF-8");
while ((len = in.read(buffer)) > 0) {
out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });
out.write(buffer, 0, len);
}
out.close();
} catch (FileNotFoundException e) {
//log.error("error:{}", e);
} finally {
if (in != null) {
try {
in.close();
} catch (Exception e) {
//log.error("error:{}", e);
}
}
}
}
/**
* 生成并csv文件
* @param response
* @param exportData
* @param map
* @param outPutPath
* @param fileName
* @throws IOException
*/
@SuppressWarnings("rawtypes")
public static void exportFile(HttpServletResponse response, List exportData, LinkedHashMap map, String outPutPath, String fileName) throws IOException{
File csvFile = null;
BufferedWriter csvFileOutputStream = null;
try {
File file = new File(outPutPath);
if (!file.exists()) {
file.mkdirs();
}
//定义文件名格式并创建
csvFile =new File(outPutPath+fileName+".csv");
if(csvFile.exists()){
csvFile.delete();
}
csvFile.createNewFile();
// UTF-8使正确读取分隔符","
//如果生产文件乱码,windows下用gbk,linux用UTF-8
csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"), 1024);
//写入前段字节流,防止乱码
csvFileOutputStream.write(getBom());
// 写入文件头部
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
csvFileOutputStream.write((String) propertyEntry.getValue() != null ? (String) propertyEntry.getValue() : "" );
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
csvFileOutputStream.newLine();
if(CollectionUtils.isNotEmpty(exportData)) {
// 写入文件内容
for (Iterator iterator = exportData.iterator(); iterator.hasNext(); ) {
Object row = (Object) iterator.next();
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator
.hasNext(); ) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator
.next();
String str = "";
if (row != null && !"".equals(propertyEntry.getKey()) && null != propertyEntry.getKey() && ((Map) row).get(propertyEntry.getKey()) != null) {
str = ((Map) row).get(propertyEntry.getKey()).toString();
}
if (StringUtils.isEmpty(str)) {
str = "";
} else {
if (str.indexOf(",") >= 0) {
str = "\"" + str + "\"";
}
}
csvFileOutputStream.write(str);
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
if (iterator.hasNext()) {
csvFileOutputStream.newLine();
}
}
}
csvFileOutputStream.flush();
} catch (Exception e) {
//log.error("error:{}", e);
} finally {
try {
csvFileOutputStream.close();
} catch (IOException e) {
//log.error("error:{}", e);
}
}
}
/**
* 解析csv
* @param response
* @param exportData
* @param map
* @param outPutPath
* @param fileName
* @throws IOException
*/
@SuppressWarnings("rawtypes")
public static void analysisExportFile(HttpServletResponse response, List exportData, LinkedHashMap map, String outPutPath, String fileName) throws IOException {
InputStream in = null;
try {
in = new FileInputStream(outPutPath+fileName+".csv");
int len = 0;
byte[] buffer = new byte[1024];
OutputStream out = response.getOutputStream();
response.setContentType("application/csv;charset=UTF-8");
response.setHeader("Content-Disposition","attachment; filename=" + URLEncoder.encode(fileName+".csv", "UTF-8"));
response.setCharacterEncoding("UTF-8");
while ((len = in.read(buffer)) > 0) {
out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });
out.write(buffer, 0, len);
}
out.close();
} catch (FileNotFoundException e) {
//log.error("error:{}", e);
} finally {
if (in != null) {
try {
in.close();
} catch (Exception e) {
// log.error("error:{}", e);
}
}
}
File csvFile = null;
csvFile =new File(outPutPath+fileName+".csv");
if(csvFile.exists()){
csvFile.delete();
}
csvFile.delete();
}
/**
* 读取csv文件
* @param file
* @return
* @throws IOException
*/
public static List<String> readCsv(MultipartFile file) throws IOException {
checkFile(file);
List<String> list = new ArrayList<String>();
if (!file.isEmpty()){
InputStreamReader isr = null;
BufferedReader br = null;
try {
isr = new InputStreamReader(file.getInputStream());
br = new BufferedReader(isr);
String line = null;
List<List<String>> strs = new ArrayList<List<String>>();
while ((line = br.readLine()) != null){
strs.add(Arrays.asList(line.split(",",-1)));
}
JSONArray array = toJsonArray(strs);
list = array.toJavaList(String.class);
} catch (IOException e) {
//log.error("error:{}", e);
}finally {
try {
if (br != null){
br.close();
}
if (isr != null){
isr.close();
}
} catch (IOException e) {
//log.error("error:{}", e);
}
}
}else {
System.out.println("文件为空!");
}
return list;
}
/**
* 读取csv文件
* @param file
* @return
* @throws IOException
*/
public static List<String[]> readCsvFile(MultipartFile file ,String codeFormat) throws IOException {
checkFile(file);
List<String[]> strs = new ArrayList<String[]>();
if (!file.isEmpty()){
InputStreamReader isr = null;
BufferedReader br = null;
try {
isr = new InputStreamReader(file.getInputStream(),codeFormat);
br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null){
strs.add(line.split(",",-1));
}
} catch (IOException e) {
// log.error("error:{}", e);
}finally {
try {
if (br != null){
br.close();
}
if (isr != null){
isr.close();
}
} catch (IOException e) {
//log.error("error:{}", e);
}
}
}else {
System.out.println("文件为空!");
}
return strs;
}
private static JSONArray toJsonArray(List<List<String>> strs) {
JSONArray array = new JSONArray();
for (int i = 1; i < strs.size(); i++) {
JSONObject object = new JSONObject();
for (int j = 0; j < strs.get(0).size(); j++) {
object.put(strs.get(0).get(j), strs.get(i).get(j));
}
array.add(object);
}
return array;
}
/**
* 检查文件格式
* @param file
* @throws IOException
*/
public static void checkFile(MultipartFile file) throws IOException {
// 判断文件是否存在
if (null == file) {
throw new FileNotFoundException("判断文件不存在");
}
// 获得文件名
String fileName = file.getOriginalFilename();
// 判断文件是否是excel文件
if (!fileName.endsWith(CSV_LOWER) && !fileName.endsWith(CSV)) {
throw new IOException(fileName + "不是csv文件");
}
}
}
这个配置类主要用来解析前端传过来的csv格式的数据流!
然后controller类里面的方法
/**
* 学校信息导入
* MultipartFile file csv格式数据流
*updateSupport 前端传入的,如果数据和数据库里面的数据重复的话,是否覆盖{true 覆盖 false 不覆盖}
*/
@Log(title = "用户管理", businessType = BusinessType.IMPORT)
@RequiresPermissions("system:information:import")
@RequestMapping("/importData")
@ResponseBody
public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception
{
List<StudentInformation> studentInformations=new ArrayList<StudentInformation>();//创建一个学生类的数组
CSVUtils csvUtils=new CSVUtils();//创建配置类的对象
List<String> csv=csvUtils.readCsv(file);//把前端传过来的数据流解析成字符串数组
for(int a=0;a<csv.size();a++){ //循环此字符串数组
String stu=csvUtils.readCsv(file).get(a); //循环取出数组里的每个数据
Map maps = (Map)JSON.parse(stu); //取出的数据格式是map类型的,但数据类型是Sting类型的,所以转换成map类型
StudentInformation abc=new StudentInformation();//创建一个学生类的对象
abc.setName((String) maps.get("姓名"));//把刚才转换成功的每一条数据里面的单个数据再存到学生类里。
abc.setName((String) maps.get("性别"));
abc.setName((String) maps.get("年龄"));
abc.setName((String) maps.get("手机号"));
studentInformations.add(abc);//然后再把整条数据存入数组里
System.out.println(maps);
}
String operName = ShiroUtils.getSysUser().getLoginName();
//此方法是若伊框架中导入Exel格式文件的业务层方法。代码一模一样,不再做过多的赘述。
String message = studentInformationService.importSchool(studentInformations, updateSupport, operName);
return AjaxResult.success(message);
}
controller中主要注意的点是
再之后就是业务层的方法
正如刚才所说,业务成只是用于把数据新增到数据库里面。Exel格式或者csv格式,在控制类中已经解析好了,所以,用同一种业务层方法就可以了。
/**
* 导入用户数据
*
* @param studentInformation 用户数据列表
* @param isUpdateSupport 是否更新支持,如果已存在,则进行更新数据
* @param operName 操作用户
* @return 结果
*/
@Override
public String importSchool(List<StudentInformation> studentInformation, Boolean isUpdateSupport, String operName)
{
if (StringUtils.isNull(studentInformation) || studentInformation.size() == 0)
{
throw new BusinessException("导入用户数据不能为空!");
}
int successNum = 0;
int failureNum = 0;
StringBuilder successMsg = new StringBuilder();
StringBuilder failureMsg = new StringBuilder();
for (StudentInformation school : studentInformation)//循环取出刚才传入过来的数据数组,做出判断,数据表中没有的新增,有的修改
{
try
{
// 验证是否存在这个用户
StudentInformation u = studentInformationMapper.selectSchoolByLoginName(school.getName() );
if (StringUtils.isNull(u))
{
school.setName(school.getName());
this.insertStudentInformation(school);//此处调用的是新增数据的方法,
successNum++;
successMsg.append("<br/>" + successNum + "学校信息" + school.getName() + " 导入成功");
}
else if (isUpdateSupport)
{
school.setUpdateBy(operName);
this.updateStudentInformation(school);//此处调用的是修改数据的方法
successNum++;
successMsg.append("<br/>" + successNum + "学校信息 " + school.getName() + " 更新成功");
}
else
{
failureNum++;
failureMsg.append("<br/>" + failureNum + "学校信息" + school.getName() + " 已存在");
}
}
catch (Exception e)
{
failureNum++;
String msg = "<br/>" + failureNum + "学校信息" + school.getName() + " 导入失败:";
failureMsg.append(msg + e.getMessage());
//log.error(msg, e);
}
}
if (failureNum > 0)
{
failureMsg.insert(0, "很抱歉,导入失败!共 " + failureNum + " 条数据格式不正确,错误如下:");
throw new BusinessException(failureMsg.toString());
}
else
{
successMsg.insert(0, "恭喜您,数据已全部导入成功!共 " + successNum + " 条,数据如下:");
}
return successMsg.toString();
}
后端的新增语句和修改语句我就不贴上了。
最后就是前端的代码了。
其实前端代码,我几乎完全没动。还是若伊框架中给封装好的导入方法。导入exel或者csv都是一样的。
这边我把方法贴上供大家参考
导入按钮!
<a class="btn btn-info" onclick="$.table.importExcel()" shiro:hasPermission="system:information:import">
<i class="fa fa-upload"></i> 导入
</a>
点击导入按钮所调用的js方法!
// 导入数据
importExcel: function(formId, width, height) {
table.set();
var currentId = $.common.isEmpty(formId) ? 'importTpl' : formId;//此处是导入数据弹出页面
var _width = $.common.isEmpty(width) ? "400" : width;
var _height = $.common.isEmpty(height) ? "230" : height;
layer.open({
type: 1,
area: [_width + 'px', _height + 'px'],
fix: false,
//不固定
maxmin: true,
shade: 0.3,
title: '导入' + table.options.modalName + '数据',
content: $('#' + currentId).html(),
btn: ['<i class="fa fa-check"></i> 导入', '<i class="fa fa-remove"></i> 取消'],
// 弹层外区域关闭
shadeClose: true,
btn1: function(index, layero){
var file = layero.find('#file').val();
if (file == '' || (!$.common.endWith(file, '.xls') && !$.common.endWith(file, '.xlsx') && !$.common.endWith(file, '.csv'))){
$.modal.msgWarning("请选择后缀为 “xls”或“xlsx”以及“csv”的文件。"); //此处的导入条件我也把后缀名为.csv的加上了。
return false;
}
var index = layer.load(2, {shade: false});
$.modal.disable();
var formData = new FormData(layero.find('form')[0]);
console.log(formData);
$.ajax({
url: table.options.importUrl, //请求地址在绑定的数据表格中
data: formData,
cache: false,
contentType: false,
processData: false,
type: 'POST',
success: function (result) {
if (result.code == web_status.SUCCESS) {
$.modal.closeAll();
$.modal.alertSuccess(result.msg);
$.table.refresh();
} else if (result.code == web_status.WARNING) {
layer.close(index);
$.modal.enable();
$.modal.alertWarning(result.msg)
} else {
layer.close(index);
$.modal.enable();
$.modal.alertError(result.msg);
}
}
});
}
});
}
导入区域页面!
<!-- 导入区域 -->
<script id="importTpl" type="text/template">
<form enctype="multipart/form-data" class="mt20 mb10">
<div class="col-xs-offset-1">
<input type="file" id="file" name="file"/>
<div class="mt10 pt5">
<input type="checkbox" id="updateSupport" name="updateSupport" title="如果登录账户已经存在,更新这条数据。"> 是否更新已经存在的用户数据
<a onclick="$.table.importTemplate()" class="btn btn-default btn-xs"><i class="fa fa-file-excel-o"></i> 下载模板</a>
</div>
<font color="red" class="pull-left mt10">
提示:仅允许导入“xls”或“xlsx”格式文件!
</font>
</div>
</form>
</script>
好啦!整个流程到此结束了。
这一篇文章是我在csdn中的第一篇文章。也是我菜鸟生涯中的第一篇文章。在解决这个问题中我也是找了好多文章,问过很多位前辈,试过很多解决办法。最后一下午的时间终于成功了。
我也希望这一篇文章同样能够帮助到你。
如果有什么问题或者建议请给我留言哦~