年前在开发功能模块的时候用到了CSV文件导入导出,就此整理一下,便于大家参考。
导入导出功能很多时候用到的都是Excel文件,但是现在越来越多的使用了CSV文件进行此操作,它是一个纯文本文件,可以用记事本打开,也可以用Excel打开。CSV文件不像Excel那样有很多条条框框,它使用硬回车分割每条记录,用逗号分隔每条数据的字段。
CSV格式的文件就是用硬回车和文本都好实现的表格,用Excel一读就成了表格。文件名后缀就是 .csv。
直接上代码吧!
导入部分
导入的时候基于Ajax请求,js代码如下:
- function importIpMac(upload) {
- var importTextInfo = document.getElementById("importTextInfo");
- importTextInfo.value="";
- $.ajaxFileUpload({
- url: ctx + "/ipmac/importIpMac",
- type: 'post',
- secureuri: false, // 一般设置为false
- fileElementId: 'upload', // 上传文件的id、name属性名
- dataType: 'text', // 返回值类型,一般设置为json、application/json
- success: function(data, status){
- getIpMacBase();
- },
- error: function(data, status, e){
- alert('请求异常!');
- }
- });
- }
Java代码控制层:
- /**
- * 导入
- */
- @ResponseBody
- @RequestMapping(value = "/importIpMac", method = RequestMethod.POST, headers = { "content-type=multipart/form-data" })
- public int importIpMac(HttpServletRequest request,
- HttpServletResponse response,
- @RequestParam(value = "upload") MultipartFile[] buildInfo)
- throws ServletException, IOException {
- // 得到上传文件的保存目录,将上传的文件存放于WEB-INF目录下,不允许外界直接访问,保证上传文件的安全
- String savePath = request.getSession().getServletContext().getRealPath("/WEB-INF/upload");
- savePath = savePath.replace("file:", ""); // 去掉file:
- File file1 = new File(savePath);
- // 判断上传文件的保存目录是否存在
- if (!file1.exists() && !file1.isDirectory()) {
- log.info(savePath + "目录不存在,需要创建");
- file1.mkdir();
- }
- // 删除此路径下的所有文件以及文件夹
- delAllFile(savePath);
- try {
- InputStream is = buildInfo[0].getInputStream();// 多文件也适用,我这里就一个文件
- byte[] b = new byte[(int) buildInfo[0].getSize()];
- int read = 0;
- int i = 0;
- while ((read = is.read()) != -1) {
- b[i] = (byte) read;
- i++;
- }
- is.close();
- String filePath = savePath + "/" + "temp" + "_" + buildInfo[0].getOriginalFilename();
- log.info("临时文件保存路径:" + savePath + "/" + "temp" + "_" + buildInfo[0].getOriginalFilename());
- OutputStream os = new FileOutputStream(new File(savePath + "/" + "temp" + "_" + buildInfo[0].getOriginalFilename()));// 文件原名,如a.txt
- os.write(b);
- os.flush();
- os.close();
- topologyIpMacPortRealService.importIpMac(filePath);
- } catch (Exception e) {
- if (log.isDebugEnabled())
- log.debug("系统异常", e);
- }
- return 1;
- }
Java代码实现层:
- public int importIpMac(String filePath) throws Exception {
- // List<String> dataList=CSVUtils.importCsv(new File("/Users/wjm/Desktop/testexcel.csv"));
- List<String> dataList = CSVUtils.importCsv(new File(filePath));
- if (dataList != null && !dataList.isEmpty()) {
- for (int i = 1; i < dataList.size(); i++) {
- String data = dataList.get(i);
- SiTopologyIpMacPortBase base = new SiTopologyIpMacPortBase();
- String[] source = data.split(",");
- if (source[0] != "") {
- base.setId(source[0]);
- base.setMac(source[1]);
- base.setIp(source[2]);
- base.setUpIp(source[3]);
- base.setUpName(source[4]);
- base.setUpIndex(source[5]);
- base.setModifyTime(source[6]);
- siTopologyIpMacPortBaseDao.insert(base);
- }
- }
- }
- return 1;
- }
其中CSVUtils类:
- package com.one.si.toimpl.common.utils;
- import java.io.BufferedReader;
- import java.io.BufferedWriter;
- import java.io.File;
- import java.io.FileOutputStream;
- import java.io.FileReader;
- import java.io.IOException;
- import java.io.OutputStreamWriter;
- import java.util.ArrayList;
- import java.util.List;
- /**
- * CSV操作(导出和导入)
- *
- * @author wjm
- * @version 1.0 Nov 24, 2015 4:30:58 PM
- */
- public class CSVUtils {
- <span style="white-space:pre"> </span>/**
- * 导出
- *
- * @param file csv文件(路径+文件名),csv文件不存在会自动创建
- * @param dataList 数据
- * @return
- */
- public static boolean exportCsv(File file, List<String> dataList){
- boolean isSucess=false;
- FileOutputStream out=null;
- OutputStreamWriter osw=null;
- BufferedWriter bw=null;
- try {
- // OutputStreamWriter in_=new OutputStreamWriter(new FileOutputStream("文件名"), "gbk");
- out = new FileOutputStream(file);
- osw = new OutputStreamWriter(out, "gbk");
- bw =new BufferedWriter(osw);
- if(dataList!=null && !dataList.isEmpty()){
- for(String data : dataList){
- bw.append(data).append("\r");
- }
- }
- isSucess=true;
- } catch (Exception e) {
- isSucess=false;
- }finally{
- if(bw!=null){
- try {
- bw.close();
- bw=null;
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- if(osw!=null){
- try {
- osw.close();
- osw=null;
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- if(out!=null){
- try {
- out.close();
- out=null;
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- return isSucess;
- }
- /**
- * 导入
- *
- * @param file csv文件(路径+文件)
- * @return
- */
- public static List<String> importCsv(File file){
- List<String> dataList=new ArrayList<String>();
- BufferedReader br=null;
- try {
- br = new BufferedReader(new FileReader(file));
- String line = "";
- while ((line = br.readLine()) != null) {
- dataList.add(line);
- }
- }catch (Exception e) {
- }finally{
- if(br!=null){
- try {
- br.close();
- br=null;
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- return dataList;
- }
- }
导出部分
js部分:
- /*
- * 导出基准表中的数据
- */
- function exportIpMac() {
- window.open("exportIpMac.do");
- }
Java代码控制层:
- /**
- * 导出的基准表信息
- */
- @ResponseBody
- @RequestMapping("/exportIpMac")
- public String exportIpMac(HttpServletRequest request, HttpServletResponse response) throws Exception {
- List<String> dataList = topologyIpMacPortRealService.exportIpMac();
- response.setCharacterEncoding("GBK");
- SimpleDateFormat dfs = new SimpleDateFormat("yyyyMMddHHmmss");// 设置日期格式
- Date time = new Date();
- String tStamp = dfs.format(time);
- String filename = "IpMacPortExport"+tStamp + ".csv";
- response.setHeader("contentType", "text/html; charset=GBK");
- response.setContentType("application/octet-stream");
- response.addHeader("Content-Disposition", "attachment; filename="+filename);
- String cp=request.getSession().getServletContext().getRealPath("/");
- String path = cp+"download/"+filename;
- File file = new File(path);
- BufferedInputStream bis = null;
- BufferedOutputStream out = null;
- FileWriterWithEncoding fwwe =new FileWriterWithEncoding(file,"GBK");
- BufferedWriter bw = new BufferedWriter(fwwe);
- if(dataList!=null && !dataList.isEmpty()){
- for(String data : dataList){
- bw.write(data);
- bw.write("\n");
- }
- }
- bw.close();
- fwwe.close();
- try {
- bis = new BufferedInputStream(new FileInputStream(file));
- out = new BufferedOutputStream(response.getOutputStream());
- byte[] buff = new byte[2048];
- while (true) {
- int bytesRead;
- if (-1 == (bytesRead = bis.read(buff, 0, buff.length))){
- break;
- }
- out.write(buff, 0, bytesRead);
- }
- file.deleteOnExit();
- }
- catch (IOException e) {
- throw e;
- }
- finally{
- try {
- if(bis != null){
- bis.close();
- }
- if(out != null){
- out.flush();
- out.close();
- }
- }
- catch (IOException e) {
- throw e;
- }
- }
- delAllFile(cp+"download/");
- return null;
- }
- public List<String> exportIpMac() throws Exception {
- List<String> dataList = new ArrayList<String>();
- try {
- List<SiTopologyIpMacPortReal> list = siTopologyIpMacPortRealdao.selectAllData();
- dataList.add("ID,地址,IP地址,设备,设备名称,端口,更新时间");
- for (int i = 0; i < list.size(); i++) {
- dataList.add(list.get(i).getId() + "," + list.get(i).getMac()
- + "," + list.get(i).getIp() + ","
- + list.get(i).getUpIp() + ","
- + list.get(i).getUpName() + ","
- + list.get(i).getUpIfIndex() + ","
- + list.get(i).getModifyTime());
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- return dataList;
- }