csv作为一种excel的一种文本格式,数据格式简单,以","分割,常用来作为数据的导入导出操作。
元数据以数组的形式传输,数组内部常见的数据格式:Java bean
一:CSV输出
通过字符拼装得到getXXX,以反射的形式过去实体的get方法,从而取值
package com.iaspec.atsign.sysmgrt.util;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import com.iaspec.atsign.certificate.client.operation.model.ApplicationForm;
/**
* 文件操作
*
* @author Edwin Hu
*/
public class CSVUtils {
private static final String fileName = "ApplicationForm.csv";// 文件名
// 读取字符编码
private static final String csvEncoding = "UTF-8";
/** CSV文件列分隔符 */
private static final String CSV_COLUMN_SEPARATOR = ",";
/** CSV文件列分隔符 */
private static final String CSV_RN = "\r\n";
private final static Logger logger = Logger.getLogger(CSVUtils.class);
/**
* 数据初始化
*
* @param data 数据库查出来的数据
* @param headerName csv表头
* @param fileds data中的key(实体类属性)
* @return
*/
public static String formatCsvData(List<ApplicationForm> data, String[] fileds, String[] headerName) {
StringBuffer buf = new StringBuffer();
// 输出列头
for (int i = 0; i < headerName.length; i++) {
buf.append(headerName[i]).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_RN);
if (null != data) {
// 输出数据
for (int i = 0; i < data.size(); i++) {
ApplicationForm t = (ApplicationForm) data.get(i);
for (int j = 0; j < fileds.length; j++) {
String value = getPropertyValue(t, fileds[j]);
buf.append(value).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_RN);
}
}
logger.info("csv file Initialize successfully");
return buf.toString();
}
/**
* 导出
*
* @param fileName 文件名
* @param content 内容
* @param request
* @param response
* @throws IOException
*/
public static void exportCsv(String fileName, String content, HttpServletRequest request,
HttpServletResponse response) {
// 设置响应(下载弹出框)
response.setCharacterEncoding(csvEncoding);
response.setContentType("text/csv; charset=" + csvEncoding);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
final String userAgent = request.getHeader("USER-AGENT");
// 写出响应数据
OutputStream os = null;
try {
if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
fileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
// 写出响应数据
os = response.getOutputStream();
os.write(content.getBytes("GBK"));
os.flush();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (null != os) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
logger.info("csv file download completed");
}
/**
* 将第一个字母转换为大写字母并和get拼合成方法
*
* @param origin
* @return
*/
private static String toUpperCaseFirstOne(String origin) {
StringBuffer sb = new StringBuffer(origin);
sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
sb.insert(0, "get");
return sb.toString();
}
/**
* 通过并和get拼合成方法获取属性值
*
* @param property getxxx
* @param t 实体类
* @return
*/
@SuppressWarnings("unused")
private static String getPropertyValue(ApplicationForm t, String property) {
Class clazz = t.getClass();
String filedName = toUpperCaseFirstOne(property);
Object obj = null;
try {
Method method = clazz.getDeclaredMethod(filedName);
method.setAccessible(true);
obj = method.invoke(t);
} catch (Exception e) {
e.printStackTrace();
}
return obj.toString();
}
}
controller层调用
@RequestMapping("createCsv")
public void createCsv(HttpServletResponse response, HttpServletRequest request)
throws UnsupportedEncodingException {
/**
* struts2中:手动获取response request
* import javax.servlet.http.HttpServletRequest;
* import javax.servlet.http.HttpServletResponse;
* import org.apache.struts2.ServletActionContext;
* HttpServletResponse response = ServletActionContext.getResponse();
* HttpServletRequest request = ServletActionContext.getRequest();
* 不然会报异常:java.lang.NoSuchMethodException
*/
// 生成实体类的get方法取值(实体类的属性名)
String fileds[] = new String[] { "userId", "userName", "userPass" };
// csv 文件的的列名字
String headerName[] = new String[] { "第一列", "第三列", "第二列" };
//设置csv文件名
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String fileName = sdf.format(new Date()).toString() + "-表名.csv";
//数据源
List<UserLogin> exportData = userService.getUserlogin();
String formatCsvData = EdwinCsv.formatCsvData(exportData, fileds, headerName);
CSVUtils.exportCsv(fileNames, formatCsvData, request, response);
}
二:CSV输入
文件导入,首先上传文件,然后读取文件数据,写入数据库
文件导入:需要form表单提交 method="post" enctype="multipart/form-data"
<input type="file" name="uploadImage" id="fileInput"> 中的name必须与后台形参名字相同
<form action="upload.action"method="post" enctype="multipart/form-data" id="form">
<table>
<tbody >
<tr >
<td>上传文件:</td>
<td style="padding-left: 10px;">
<input type="file" name="uploadImage" id="fileInput">
</td>
<td style="padding-left: 80px;">
<button type="submit" class="btn btn-primary btn-q btn-outline fa fa-upload"class="easyui-validatebox" data-options="required:true">上传</button>
</td>
</tr>
<tr>
<td colspan="2">
<span style="color:red">*上传文件格式为csv文件!</span>
</td>
</tr>
</tbody>
</table>
</form>
一:struts接收数据
在action中定义三个属性值,并给出get/set方法
private File uploadImage; // 得到上传的文件
private String uploadImageContentType; // 得到文件的类型
private String uploadImageFileName; // 得到文件的名称
package com.web.action.view;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.struts2.ServletActionContext;
public class ApplicationFormAction<T> extends BaseAction {
private static final long serialVersionUID = -1190864107983796417L;
private File uploadImage; // 得到上传的文件
private String uploadImageContentType; // 得到文件的类型
private String uploadImageFileName; // 得到文件的名称
/**
* 导入csv
*/
public String upload() {
System.out.println("=====================upload======================");
System.out.println("uploadImageFileName:" + this.getUploadImageFileName());
System.out.println("uploadImageContentType:" + this.getUploadImageContentType());
System.out.println("uploadImage:" + this.getUploadImage());
// 获取要保存文件夹的物理路径(绝对路径 /WEB-INF下外部无法访问)
String realPath = ServletActionContext.getServletContext().getRealPath("/WEB-INF");
File file = new File(realPath);
//文件名字前缀
String prefixName = this.getUploadImageFileName().substring(0, this.getUploadImageFileName().indexOf("."));
//文件名字后缀
String suffixName = this.getUploadImageFileName().substring(this.getUploadImageFileName().lastIndexOf(".") + 1);
String fileName = prefixName + System.currentTimeMillis() + "." + suffixName;
// 测试此抽象路径名表示的文件或目录是否存在。若不存在,创建此抽象路径名指定的目录,包括所有必需但不存在的父目录。
if (!file.exists()) {
file.mkdirs();
}
try {
// 保存文件
FileUtils.copyFile(uploadImage, new File(file, fileName));
String uploadPath = realPath + File.separator + fileName;
WebLogger.debug("uploadPath:" + uploadPath);
// 解析文件得到数据源
List<ApplicationForm> readCsvFile = ReadCsvFile.readCsvFile(uploadPath, realPath, fileName);
if (null != readCsvFile && readCsvFile.size() > 0) {
// 插入数据库
applicationFormDelegate.bulkInsert(readCsvFile);
}
} catch (IOException e) {
e.printStackTrace();
}
return "uploadSuccess";//执行完成后跳转页面,或者返回提示信息
}
public File getUploadImage() {
return uploadImage;
}
public void setUploadImage(File uploadImage) {
this.uploadImage = uploadImage;
}
public String getUploadImageContentType() {
return uploadImageContentType;
}
public void setUploadImageContentType(String uploadImageContentType) {
this.uploadImageContentType = uploadImageContentType;
}
public String getUploadImageFileName() {
return uploadImageFileName;
}
public void setUploadImageFileName(String uploadImageFileName) {
this.uploadImageFileName = uploadImageFileName;
}
}
二:springmvc接受数据
package com.Learn.dome.excel.controller;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.util.List;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadBase;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.ProgressListener;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import com.Learn.dome.excel.util.ReadCsvFile;
@Controller
@RequestMapping("upload")
public class upload {
@RequestMapping("/upLoadFile")
public String upload(@RequestParam(value = "file", required = false) MultipartFile file, ModelMap model, HttpServletRequest request)
throws Exception {
//设置相对路径 /WEB-INF下外部无法访问
String realPath = request.getSession().getServletContext().getRealPath("/WEB-INF");
//获取文件名称的后缀
String suffixName= file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")+1);
//获取文件名称的前缀
String suffixName= file.getOriginalFilename().substring(0,file.getOriginalFilename().indexOf("."));
//对格式进行筛选
if(suffixName.equalsIgnoreCase("csv")) {
//在路径下创建文件夹
File f = new File(realPath);
String fileName = prefixName + System.currentTimeMillis() + "." + suffixName;
String uploadPath = realPath + File.separator + fileName;
if(!f.exists()) {
f.mkdirs();
}
//文件的传输
file.transferTo(new File(uploadPath));
// 解析文件得到数据源
List<ApplicationForm> readCsvFile = ReadCsvFile.readCsvFile(uploadPath, realPath, fileName);
if (null != readCsvFile && readCsvFile.size() > 0) {
// 插入数据库
applicationFormDelegate.bulkInsert(readCsvFile);
}
request.setAttribute("info","文件上传成功!");
} else {
request.setAttribute("info","文件类型不正确,请上传格式为xls,xlsx,txt,csv文件!");
}
return null;
}
}
工具类ReadCsvFile
package com.iaspec.atsign.sysmgrt.web.util;
import java.io.File;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import com.csvreader.CsvReader;
import com.iaspec.atsign.certificate.client.operation.model.ApplicationForm;
/**
* 读取csv文件
*
* @author Edwin Hu
*
*/
public class ReadCsvFile {
public static List<ApplicationForm> readCsvFile(String uploadPath, String realPath, String fileName) {
List<ApplicationForm> listCsvFile = null;
CsvReader reader = null;
try {
ArrayList<String[]> csvList = new ArrayList<String[]>();
WebLogger.debug("--------csvList----------" + csvList.toString());
reader = new CsvReader(uploadPath, ',', Charset.forName("GBK"));
// reader.readHeaders(); // 跳过表头,不跳可以注释掉
while (reader.readRecord()) {
csvList.add(reader.getValues()); // 按行读取,并把每一行的数据添加到list集合
}
WebLogger.debug("读取的行数:" + csvList.size());
int size = csvList.size();
if (size > 0 && null != csvList) {
listCsvFile = new ArrayList<ApplicationForm>();
for (int row = 1; row < size; row++) {
WebLogger.debug("----------------row---------------" + row);
WebLogger.debug("-------------------------------");
WebLogger.debug(csvList.get(row) + ",");
// 每一行的数据
ApplicationForm app = new ApplicationForm();
if (csvList.get(0)[0].equals("cluster")) {
app.setCluster(csvList.get(row)[0]);
}
if (csvList.get(0)[1].equals("hospital")) {
app.setHospital(csvList.get(row)[1]);
}
if (csvList.get(0)[2].equals("post")) {
app.setPost(csvList.get(row)[2]);
}
if (csvList.get(0)[3].equals("department")) {
app.setDepartment(csvList.get(row)[3]);
}
if (csvList.get(0)[4].equals("title")) {
app.setTitle(csvList.get(row)[4]);
}
if (csvList.get(0)[5].equals("surname")) {
app.setSurname(csvList.get(row)[5]);
}
if (csvList.get(0)[6].equals("givenname")) {
app.setGivenname(csvList.get(row)[6]);
}
if (csvList.get(0)[7].equals("cuid")) {
app.setCuid(csvList.get(row)[7]);
}
if (csvList.get(0)[8].equals("hkid")) {
app.setHkid(csvList.get(row)[8]);
}
if (csvList.get(0)[9].equals("passport")) {
app.setPassport(csvList.get(row)[9]);
}
if (csvList.get(0)[10].equals("roleName")) {
app.setRoleName(csvList.get(row)[10]);
}
if (csvList.get(0)[11].equals("validity")) {
app.setValidity(Integer.valueOf(csvList.get(row)[11]));
}
if (csvList.get(0)[12].equals("email")) {
app.setEmail(csvList.get(row)[12]);
}
if (csvList.get(0)[13].equals("telephone")) {
app.setTelephone(csvList.get(row)[13]);
}
if (csvList.get(0)[14].equals("status")) {
app.setStatus(Integer.valueOf(csvList.get(row)[14]));
}
listCsvFile.add(app);
WebLogger.debug("----listCsvFile----" + listCsvFile.toString());
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != reader) {
reader.close();
}
deleteFile(realPath, fileName);
}
return listCsvFile;
}
/**
* 删除该目录filePath下的所有文件
*
* @param filePath 文件目录路径
*/
public static void deleteFiles(String filePath) {
WebLogger.debug("----deleteFiles---删除文件-");
File file = new File(filePath);
if (file.exists()) {
File[] files = file.listFiles();
for (int i = 0; i < files.length; i++) {
if (files[i].isFile()) {
files[i].delete();
}
}
}
}
/**
* 删除单个文件
*
* @param filePath 文件目录路径
* @param fileName 文件名称
*/
public static void deleteFile(String filePath, String fileName) {
File file = new File(filePath);
if (file.exists()) {
File[] files = file.listFiles();
for (int i = 0; i < files.length; i++) {
if (files[i].isFile()) {
if (files[i].getName().equals(fileName)) {
files[i].delete();
return;
}
}
}
}
}
}