public class CsvToListObjectTest {
/**
* 写入csv文件
* @param list
* @param fileName
* @return
*/
public static File getCsvFile(List<User> list, String fileName){
File file = new File(fileName);
FileOutputStream fos = null;
OutputStreamWriter osw = null;
BufferedWriter bfw = null;
try {
fos = new FileOutputStream(file);
osw = new OutputStreamWriter(fos);
bfw = new BufferedWriter(osw);
bfw.write(list.get(0).getKeyString()+"\r");
for (int i = 0; i < list.size(); i++) {
bfw.write(list.get(i).getValueString()+"\r");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if (bfw != null){
bfw.close();
}
if (osw != null){
osw.close();
}
if (fos != null){
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return file;
}
public static void main(String[] agrs){
User user = null;
ArrayList<User> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
user = new User();
user.setUser_id(10000+i);
user.setUser_name("user"+i);
user.setUser_age(20+i);
user.setUser_sex("男");
user.setUser_pwd("123456"+i);
user.setUser_phone("123456789"+i);
user.setUser_type(""+i);
list.add(user);
}
File file = getCsvFile(list,"user.csv");
}
/**
* Servlet导出list集合的csv文件
* @param response
* @param list
* @param fileName
*/
public static void getCsvFile(HttpServletResponse response, List<User> list, String fileName){
//设置文件名
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String fname = fileName+sdf.format(new Date())+".csv";
//设置response
response.reset();
response.setHeader("Content-disposition", "attachment; filename="+fname);
response.setContentType("application/ms-txt.numberformat:@");
//获得输出流
OutputStream os = null;
OutputStreamWriter osw = null;
BufferedWriter bfw = null;
//写入数据
try {
os = response.getOutputStream();
osw = new OutputStreamWriter(os);
bfw = new BufferedWriter(osw);
bfw.write(list.get(0).getKeyString()+"\r");
for (int i = 0; i < list.size(); i++) {
bfw.write(list.get(i).getValueString()+"\r");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if (bfw != null){
bfw.flush();
bfw.close();
}
if (osw != null){
osw.close();
}
if (os != null){
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public class User {
private Integer user_id;
private String user_name;
private String user_pwd;
private Integer user_age;
private String user_sex;
private String user_phone;
private String user_type;
public String getKeyString() {
return "user_id,"+"user_name,"+"user_pwd,"+"user_age,"+"user_sex,"+"user_phone,"+"user_type";
}
public String getValueString(){
return user_id+","+user_name+","+user_pwd+","+user_age+","+user_sex+","+user_phone+","+user_type;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getUser_pwd() {
return user_pwd;
}
public void setUser_pwd(String user_pwd) {
this.user_pwd = user_pwd;
}
public Integer getUser_age() {
return user_age;
}
public void setUser_age(Integer user_age) {
this.user_age = user_age;
}
public String getUser_sex() {
return user_sex;
}
public void setUser_sex(String user_sex) {
this.user_sex = user_sex;
}
public String getUser_phone() {
return user_phone;
}
public void setUser_phone(String user_phone) {
this.user_phone = user_phone;
}
public String getUser_type() {
return user_type;
}
public void setUser_type(String user_type) {
this.user_type = user_type;
}
}
User类中以及实现了csv头部和值的连接。
使用workbook导出excel:
/**
* 导出list集合的excel文件
*/
public static void excelFile(HttpServletResponse response, List<UserInfo> list, String fileName, String fileType){
//设置文件名
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String fname = fileName+sdf.format(new Date())+fileType;
//设置response
response.reset();
response.setHeader("Content-disposition", "attachment; filename="+fname);
response.setContentType("application/ms-excel.numberformat:@");
//获得输出流
OutputStream os = null;
try {
os = response.getOutputStream();
Workbook workbook = null;
if (fileType.equals(".xlsx")){
//.xlsx
workbook = new XSSFWorkbook();
}else {
//.xls
workbook = new HSSFWorkbook();
}
Sheet sheet = workbook.createSheet();
Row rowh = sheet.createRow(0);
rowh.createCell(0).setCellValue("user_id");
rowh.createCell(1).setCellValue("user_name");
rowh.createCell(2).setCellValue("user_age");
rowh.createCell(3).setCellValue("user_sex");
rowh.createCell(4).setCellValue("user_phone");
rowh.createCell(5).setCellValue("user_type");
for (int i = 1; i < list.size(); i++) {
Row row = sheet.createRow(i);
row.createCell(0).setCellValue(list.get(i).getUserId());
row.createCell(1).setCellValue(list.get(i).getUserName());
row.createCell(2).setCellValue(list.get(i).getUserAge());
row.createCell(3).setCellValue(list.get(i).getUserSex());
row.createCell(4).setCellValue(list.get(i).getUserPhone());
row.createCell(5).setCellValue(list.get(i).getUserType());
}
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}finally {
if (os != null){
try {
os.flush();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}