Java 实现xls表格上传下载功能

最新推荐文章于 2023-06-28 10:23:42 发布

王大力出奇迹

最新推荐文章于 2023-06-28 10:23:42 发布

阅读量493

点赞数

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/qq_41249513/article/details/101027359

版权

java 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

最近去了新公司，搞了个视频学习网站，正好有个考试功能，就搞了个表格的上传下载，后台是spring boot框架。

这是界面的代码。

上传表格的界面代码

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8"/>
    <title>上传文件</title>
</head>
<body>

 <form enctype="multipart/form-data" method="post" action="/importtest">    选择Excel表格：<input type="file" name="file"/><br/>    <input type="submit" value="上传"/></form>


</body>
</html>

后台的conterller层。

接收请求，并上传表格到服务器。

package guru.springframework.controllers;

import guru.springframework.dao.ImportDao;
import guru.springframework.dao.GeneralQueryDao;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import guru.springframework.domain.GeneralQueryBean;
import guru.springframework.domain.TestBean;
import guru.springframework.services.ImportService;
import publicclass.PublicIP;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;



@Controller
public class ImportController {


	private ImportDao ImportDao = new ImportDao();

	private ImportService importService;
	
	private GeneralQueryDao GeneralQueryDao = new GeneralQueryDao() ;

	@GetMapping("/importtest")
	public String index() {
		System.out.println("这里");
		return "importtest";
	}

	//导入文件表格
	@ResponseBody // 返回json数据
	@RequestMapping(value = "/importtest", method = RequestMethod.POST)
	public String uploadImg(@RequestParam("file") MultipartFile file, HttpServletRequest request) {

		String contentType = file.getContentType();
		String fileName = file.getOriginalFilename();
		if (file.isEmpty()) {
			return "文件为空！";
		}
		try {
			// 根据路径获取这个操作excel的实例
			HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream()); // 根据页面index 获取sheet页
			HSSFSheet sheet = wb.getSheetAt(0);
			// 实体类集合
			List<TestBean> importDatas = new ArrayList<>();
			HSSFRow row = null;
			// 循环sesheet页中数据从第二行开始，第一行是标题
			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
				// 获取每一行数据
				TestBean TestBean = new TestBean();
				row = sheet.getRow(i);

				String test = row.getCell(0).getStringCellValue();
				// 考试题
				TestBean.setItem(row.getCell(0).getStringCellValue());
				// option
				TestBean.setAnswer(row.getCell(1).getStringCellValue());
				// Answer
				TestBean.setChoose_answer(row.getCell(2).getStringCellValue());
				// 存入集合
				importDatas.add(TestBean);

				// System.out.println("1======="+test+"2==="+option+"3==="+Answer);

			}
			// 循环入库
			ImportDao.importtest(importDatas);
			// System.out.println("2======="+row.toString());
			return "导入成功!";

		} catch (Exception e) {
			e.printStackTrace();
		}
		return "ok";
	}

	//下载所有试题

	@RequestMapping(value = "UserExcelDownloads")
	public void downloadAllClassmate(HttpServletResponse response) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("信息表");

		List<TestBean> classmateList = new ArrayList<TestBean>();
		GeneralQueryBean GeneralQueryBean =new  GeneralQueryBean();
		GeneralQueryBean.setTable("test");
		System.out.println("1==="+GeneralQueryBean.toString());
		JSONArray jsonarrays = GeneralQueryDao.GetData(GeneralQueryBean);
		System.out.println(jsonarrays.toJSONString());
		
		String fileName = PublicIP.EXPORTPATH+"userinf" + ".xls";// 设置要导出的文件的名字
		// 新增数据行，并且设置单元格数据

		int rowNum = 1;

		String[] headers = { "试题",  "所有选项" , "正确答案"};
		// headers表示excel表中第一行的表头

		HSSFRow row = sheet.createRow(0);
		// 在excel表中添加表头

		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}

		// 在表中存放查询到的数据放入对应的列
//		for (TestBean teacher : classmateList) {
//			HSSFRow row1 = sheet.createRow(rowNum);
//			row1.createCell(0).setCellValue(teacher.getItem());
//			row1.createCell(1).setCellValue(teacher.getChoose_answer());
//			row1.createCell(2).setCellValue(teacher.getAnswer());
//			rowNum++;
//		}
		
		for(Object  jsonarray :jsonarrays) {
			JSONObject obj = (JSONObject) jsonarray;
			HSSFRow row1 = sheet.createRow(rowNum);
			row1.createCell(0).setCellValue(obj.getString("item"));
			row1.createCell(1).setCellValue(obj.getString("answer"));
			row1.createCell(2).setCellValue(obj.getString("choose_answer"));
			rowNum++;
		}
		

		response.setContentType("application/octet-stream");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName);
		response.flushBuffer();
		workbook.write(response.getOutputStream());
	}
	
	
}

实体类，考试类


package guru.springframework.domain;

public class TestBean {
	//试题
	private String item;
	//所有选项
	private String answer;
	//正确答案
	private String choose_answer;
	public String getItem() {
		return item;
	}
	public void setItem(String item) {
		this.item = item;
	}
	public String getAnswer() {
		return answer;
	}
	public void setAnswer(String answer) {
		this.answer = answer;
	}
	public String getChoose_answer() {
		return choose_answer;
	}
	public void setChoose_answer(String choose_answer) {
		this.choose_answer = choose_answer;
	}
	@Override
	public String toString() {
		return "TestBean [item=" + item + ", answer=" + answer + ", choose_answer=" + choose_answer + "]";
	}
	
	
}

maven依赖

<dependency>
           <groupId>org.apache.poi</groupId>
           <artifactId>poi</artifactId>
           <version>4.0.0</version>
       </dependency>
       <dependency>
           <groupId>org.apache.poi</groupId>
           <artifactId>poi-ooxml</artifactId>
           <version>4.0.0</version>
       </dependency>

程序开起来，游览器输入 http://127.0.0.1:8080/importtest 就可以上传文件了。

下载文件是 http://127.0.0.1:8080/UserExcelDownloads 从数据库中读出数据，遍历写出来。自动下载。

上传存储路径和下载路径自己控制。