list集合转csv文件以及servlet导出csv和excel文件

傷城~ 2022-05-14 07:04 160阅读 0赞

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();
                    }
                }
            }
    
        }