Java operation excel file

Personal blog navigation page (click the link on the right to open personal blog): Daniel takes you to the technology stack 

1. The template file is under resources:

2. Operate Excel and download

 private static final String STUDENT_ANSWER_EXCEL_FIEL = "template/templateStudentAnswerOfExamTable.xlsx";
    

@Override
    public void exportStudentAnswerToExcelByExamId(HttpServletResponse response, long examId) {

     

        // 1. Get Excel template file
        // 2. get the data list set to be inserted
        // 3. Insert data into Excel template
        // 4. Export to Excel file
        String fileName = "studentAnswerOfExam";

        File newFile = createNewFile(STUDENT_ANSWER_EXCEL_FIEL);

        // New files write data and download*****************************************************
        InputStream is = null;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        try {
            is = new FileInputStream(newFile);// Converting excel files to input streams
            workbook = new XSSFWorkbook(is);// Create workbook s,
            // Get the first sheet
            sheet = workbook.getSheetAt(0);

        } catch (Exception e1) {
            logger.error("export budget.details.table error" + e1);
        }

        if (sheet != null) {
            try {
                // Writing data
                FileOutputStream fos = new FileOutputStream(newFile);
                StudentExamResultBO studentExamResult = examService.getStudentExamResult(examId);
                setData(sheet, studentExamResult);
                // ===
                workbook.write(fos);
                fos.flush();
                fos.close();
                // download
                InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                response.reset();
                response.setContentType("text/html;charset=UTF-8");
                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
                response.setContentType("application/x-msdownload");
                String newName = URLEncoder.encode(fileName + System.currentTimeMillis() + ".xlsx", "UTF-8");
                response.addHeader("Content-Disposition", "attachment;filename=\"" + newName + "\"");
                response.addHeader("Content-Length", "" + newFile.length());
                toClient.write(buffer);
                toClient.flush();
            } catch (Exception e) {
                logger.error("error:", e);
            } finally {
                try {
                    if (null != is) {
                        is.close();
                    }
                } catch (Exception e) {
                    logger.error("error:", e);
                }
            }
        }

        // Delete new files created
        this.deleteFile(newFile);

    }

    /**
     * Read the excel template and copy it to a new file for writing and downloading
     * 
     * @return
     */
    public File createNewFile(String templatePath) {
        // Read template and assign to new file************************************************************
        // File template path
        String path = (getSispPath() + templatePath);
        File file = new File(path);
        // Path to save file
        String realPath = (getSispPath() + "template");
        // New filename
        String newFileName = System.currentTimeMillis() + ".xlsx";
        // Determine whether the path exists
        File dir = new File(realPath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        // Write to new excel
        File newFile = new File(realPath, newFileName);
        try {
            newFile.createNewFile();
            // Copy template to new file
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            logger.error("error:", e);
        }
        return newFile;
    }

    /**
     * Delete after download
     * 
     * @param files
     */
    private void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }

    private String getSispPath() {
        String classPaths = this.getClass().getResource("/").getPath();
        return classPaths;
    }

    /**
     * Copy file
     * 
     * @param s source file
     * @param t New files copied to
     */
    public void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s), 1024);
                out = new BufferedOutputStream(new FileOutputStream(t), 1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = in.read(buffer)) != -1) {
                    out.write(buffer, 0, len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            logger.error("error:", e);
        }
    }


    private void setData(XSSFSheet sheet, StudentExamResultBO studentExamResult) {

        XSSFRow row0 = sheet.getRow(0);
        if (null == row0) {
            row0 = sheet.createRow(0);
        }
        XSSFCell examName = row0.getCell(1);
        if (null == examName) {
            examName = row0.createCell(1);
        }
        examName.setCellValue(studentExamResult.getName());
        }

2. Download the template directly

/**
 * Download import template
 * @param request
 * @param response
 */
@GetMapping("downloadTemplate")
public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
	InputStream in = null;
	ServletOutputStream out = null;
	String fileName = "Import template.xlsx";
	
	try {
		in = this.getClass().getClassLoader().getResourceAsStream("import_template/template.xlsx");
		response.setContentType("application/octet-stream");
		fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
		response.setHeader("Content-disposition", "attachment; filename=" + fileName);
		out = response.getOutputStream();
		
		int b = 0;
		byte[] buffer = new byte[1024];
		while ((b = in.read(buffer)) != -1) {
			out.write(buffer, 0, b);
		}
		
		out.flush();
	} catch (IOException e) {
		e.printStackTrace();
	} finally {
		IOUtils.closeQuietly(out);
		IOUtils.closeQuietly(in);
	}
}

Attached are Java/C/C + + / machine learning / algorithm and data structure / front end / Android / Python / programmer's required reading / book list:

(click the right side to open the personal blog with dry goods): Technical dry goods warehouse
=====>>① [Java bull takes you to the road to advanced level]<<====
=====>>② [algorithmic data structure + acm Daniel takes you to the road to advanced level]<<===
=====>>③ [Database bull leads you to the road to advanced level]<<=====
=====>>④ [the Web front-end bull takes you to the road to an advanced level]<<====
=====>>⑤ [machine learning and python Daniel take you to the road to advanced level]<<====
=====>>⑥ [Architecture division takes you to the road to advanced level]<<=====
=====>>⑦ [C + + Daniel takes you to the road to advanced level]<<====
=====>>⑧ [ios takes you to the advanced level]<<====
=====>>⑨ [Web security bull leads you to the road to advanced level]<<=====
=====>>⑩ [Linux and operating system Daniel take you to the advanced road]<<=====

There is no fruit in the world that can be obtained without any effort. I hope that all young friends and those who want to learn technology will overcome difficulties and cut through thorns on the way to the technology road, understand the book, knock on the code, understand the principle, and practice, which will bring you life, your work and a dream in the future.

Published 145 original articles, won praise 17, visited 8577
Private letter follow

Tags: Excel Java Python Android

Posted on Mon, 16 Mar 2020 07:30:42 -0400 by qads