java 文件导入导出

导出

public static void excelDownload(List<SpcCardfaceOrderDetail> detailList,String dirName,String proOrderCode) throws IOException {
        try{
            //表头数据
            String[] header = {"序号","条形码","姓名","学号","身份证号","性别","学校类型","学校名称","年级","班级","毕业年份","联系电话","手机号码","1寸照片名称","2寸照片名称"};
            //声明一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();

            //生成一个表格，设置表格名称为"数据表"
            HSSFSheet sheet = workbook.createSheet("在线订单信息模版");

            //设置表格列宽度为10个字节
            sheet.setDefaultColumnWidth(10);
            //创建标题的显示样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            //headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //创建第一行表头
            HSSFRow headrow = sheet.createRow(0);

            //遍历添加表头(下面模拟遍历数据，也是同样的操作过程)
            for (int i = 0; i < header.length; i++) {
                //创建一个单元格
                HSSFCell cell = headrow.createCell(i);

                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(header[i]);

                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);
                cell.setCellStyle(headerStyle);
            }

            //
            for(int i=0;i<detailList.size();i++){
                if(oConvertUtils.isEmpty(detailList.get(i).getBarCode())  ){
                    continue;
                }
                HSSFRow row1 = sheet.createRow(i+1);
                //给序号和条形码赋值
                row1.createCell(0).setCellValue(new HSSFRichTextString(String.format("%06d",i+1)));
                row1.createCell(1).setCellValue(new HSSFRichTextString(detailList.get(i).getBarCode()));
                row1.createCell(2).setCellValue(new HSSFRichTextString(detailList.get(i).getName()));
                row1.createCell(3).setCellValue(new HSSFRichTextString(detailList.get(i).getStuId()));

                row1.createCell(4).setCellValue(new HSSFRichTextString(detailList.get(i).getCertNo()));
                String sex = "";
                if("1".equals(detailList.get(i).getSex())){
                    sex = "男" ;
                }else if("2".equals(detailList.get(i).getSex())){
                    sex = "女" ;
                }else if("3".equals(detailList.get(i).getSex())){
                    sex = "未知" ;
                }

                row1.createCell(5).setCellValue(new HSSFRichTextString(sex));
                String school = "";
                if("1".equals(detailList.get(i).getSchoolType())){
                    school = "小学" ;
                }else if("2".equals(detailList.get(i).getSchoolType())){
                    school = "初中" ;
                }else if("3".equals(detailList.get(i).getSchoolType())){
                    school = "高中" ;
                }else if("4".equals(detailList.get(i).getSchoolType())){
                    school = "中职" ;
                }else if("5".equals(detailList.get(i).getSchoolType())){
                    school = "特殊学校" ;
                }

                row1.createCell(6).setCellValue(new HSSFRichTextString(school));

                row1.createCell(7).setCellValue(new HSSFRichTextString(detailList.get(i).getSchool()));
                row1.createCell(8).setCellValue(new HSSFRichTextString(detailList.get(i).getGrade()));
                row1.createCell(9).setCellValue(new HSSFRichTextString(detailList.get(i).getClasses()));

                row1.createCell(10).setCellValue(new HSSFRichTextString(detailList.get(i).getGraduateYear()));

                row1.createCell(11).setCellValue(new HSSFRichTextString(detailList.get(i).getTelphone()));
                row1.createCell(12).setCellValue(new HSSFRichTextString(detailList.get(i).getMobilePhone()));
                if(!oConvertUtils.isEmpty(detailList.get(i).getPic1())){
                    row1.createCell(13).setCellValue(new HSSFRichTextString(detailList.get(i).getBarCode()+"_1.jpg"));
                }
                if(!oConvertUtils.isEmpty(detailList.get(i).getPic2())){
                    row1.createCell(14).setCellValue(new HSSFRichTextString(detailList.get(i).getBarCode()+"_2.jpg"));
                }

            }
            //创建一行

            //第二列创建并赋值

            //准备将Excel的输出流通过response输出到页面下载
            //八进制输出流
            ByteArrayOutputStream ops = new ByteArrayOutputStream();

            //workbook将Excel写入到response的输出流中，供页面下载
            //workbook.write(ops);

            byte[] b = ops.toByteArray();
            ByteArrayInputStream in = new ByteArrayInputStream(b);
            FileOutputStream os = new FileOutputStream(dirName+"//在线订单印刷数据"+proOrderCode+".xls");
            workbook.write(os);

            os.close();
            in.close();
            ops.close();
            //FtpFileUtil.upload(this.FTP_ADDRESS,this.FTP_PORT,this.FTP_USERNAME,this.FTP_PASSWORD,in,this.FTP_BASEPATH+"123.xlsx");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

导入

// An highlighted block
var foo = 'bar';
```public static void exportExcel(HttpServletResponse response, Map<String,String> fieldsMap,Map<String,List> dictMap) throws Exception{
        //创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet = workbook.createSheet();
        //设置第一行为冻结行
        sheet.createFreezePane(0,1,0,1);
        //创建单元格样式
        XSSFCellStyle allCellStyle = workbook.createCellStyle();
        //创建数据格式化
        XSSFDataFormat dataFormat = workbook.createDataFormat();
        //设置单元格为字符串格式
        allCellStyle.setDataFormat(dataFormat.getFormat("@"));
        //创建数据dataValidationHelper用于下拉选项创建
        XSSFDataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
        //动态列list
        List<String> fieldsList = new ArrayList<String>(fieldsMap.keySet());
        int columnSize = fieldsList.size();
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        //设置行高
        row.setHeightInPoints(25);
        for(int i=0;i<columnSize;i++){
            //设置各列默认样式
            sheet.setDefaultColumnStyle(i,allCellStyle);
            //创建单元格样式
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            //创建列
            XSSFCell cell = row.createCell(i);
            //动态设置列值
            cell.setCellValue(fieldsMap.get(fieldsList.get(i)));
            //设置列宽
            sheet.setColumnWidth(i,12*256);
            //设置列样式为水平居中、垂直居中、绿色的细下边框，
            cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBottomBorderColor(new XSSFColor(Color.GREEN));
            cell.setCellStyle(cellStyle);
            //针对特定列添加以数据字典为依赖的下拉选项
            if(fieldsList.get(i).equals("sex") || fieldsList.get(i).equals("schoolType") || fieldsList.get(i).equals("graduateYear") || fieldsList.get(i).equals("school") || fieldsList.get(i).equals("grade") || fieldsList.get(i).equals("classes")){
                CellRangeAddressList rangeAddressList = new CellRangeAddressList(1,65535,i,i);
                XSSFDataValidationConstraint validationConstraint = (XSSFDataValidationConstraint)validationHelper.createExplicitListConstraint((String[])dictMap.get(fieldsList.get(i)).stream().toArray(String[]::new));
                XSSFDataValidation dataValidation = (XSSFDataValidation) validationHelper.createValidation(validationConstraint,rangeAddressList);
                sheet.addValidationData(dataValidation);
            }
        }
        response.setHeader("content-disposition", "attachment;filename=template.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
        out.flush();
    }