Java 到出导入 excel

/**
* 导入
* @param myfile
* @param req
* @return
*/
@RequestMapping(value = “uplodimport”)
public String upload(MultipartFile myfile, HttpServletRequest req) {

	System.out.println(myfile.getOriginalFilename());
	ServletContext context = RequestContextUtils.getWebApplicationContext(req).getServletContext();
	String path = context.getRealPath("/upload");
	File dspath = new File(path, myfile.getOriginalFilename());// 构造一个文件

	try {
		File file = new File(path); // 目录
		if (!file.exists())
			file.mkdirs();

		if (!file.exists())
			file.createNewFile();// 文件是否存在

		myfile.transferTo(dspath); // 保存数据

		// 读取excel文件
		Workbook workbook = null;

		// 判断excel是新版或者旧版 xls(2003) xlsx(2007)
		if (myfile.getOriginalFilename().endsWith("xls")) {
			workbook = new HSSFWorkbook(new FileInputStream(dspath));
		} else if (myfile.getOriginalFilename().endsWith("xlsx")) {
			workbook = new XSSFWorkbook(new FileInputStream("xlsx"));
		}
		Sheet sheet = workbook.getSheetAt(0);
		List<SubArea> list = new ArrayList<SubArea>();
		// 遍历表格
		for (Row row : sheet) {

			System.out.println("sheet" + sheet.getLastRowNum());
			int num = row.getRowNum();
			if (num == 0) {
				continue;
			}
			SubArea sub = new SubArea();
			list.add(sub);

			for (Cell cell : row) {
				String cellValue = "";
				// 判断数据类型
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC: // 数字
					cellValue = cell.getNumericCellValue() + "";
					break;

				case Cell.CELL_TYPE_STRING: // 字符串
					cellValue = cell.getStringCellValue();
					break;

				case Cell.CELL_TYPE_BOOLEAN: // Boolean
					cellValue = String.valueOf(cell.getBooleanCellValue());
					break;

				case Cell.CELL_TYPE_FORMULA: // 公式
					cellValue = String.valueOf(cell.getCellFormula());
					break;

				case Cell.CELL_TYPE_BLANK: // 空值
					cellValue = "";
					break;

				case Cell.CELL_TYPE_ERROR: // 故障
					cellValue = "非法字符";

				default:
					cellValue = "未知类型";
					break;
				}

				if (cell.getColumnIndex() == 0) {
					sub.setId(cellValue);
				} else if (cell.getColumnIndex() == 1) {
					Region region = new Region();
					region.setId(cellValue);
					sub.setReg(region);
				} else if (cell.getColumnIndex() == 2) {
					sub.setAddresskey(cellValue);
				} else if (cell.getColumnIndex() == 3) {
					sub.setStartnum(cellValue);
				} else if (cell.getColumnIndex() == 4) {
					sub.setEndnum(cellValue);
				} else if (cell.getColumnIndex() == 5) {
					sub.setSingle(cellValue.charAt(0));
				} else if (cell.getColumnIndex() == 6) {
					sub.setPosition(cellValue);
				}
			}

		}
		System.out.println(list.toString());
		boolean result = service.addRegion(list);
	} catch (IllegalStateException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}

	return "redirect:subarea";

}

/**
* 导出
* @param req
* @param resp
* @param sub1
* @param reg
*/
@RequestMapping(value = “export”)
public void uplodexport(HttpServletRequest req, HttpServletResponse resp, SubArea sub1, Region reg) {

	sub1.setReg(reg);
	// 把所有分区的数据查出来
	List<SubArea> list = service.allSubArea(sub1);

	// 使用poi将数据写到excel文件里面
	// 先创建一个excel文件
	HSSFWorkbook workbook = new HSSFWorkbook();
	// 创建标签页
	HSSFSheet sheet = workbook.createSheet("分区1");
	// 创建一个标题行
	HSSFRow row = sheet.createRow(0);
	row.createCell(0).setCellValue("分区编号");
	row.createCell(1).setCellValue("省/市/区");
	row.createCell(2).setCellValue("关键字");
	row.createCell(3).setCellValue("起始号");
	row.createCell(4).setCellValue("终止号");
	row.createCell(5).setCellValue("单双");
	row.createCell(6).setCellValue("位置信息");

	// 遍历集合 进行填格

	for (SubArea sub : list) {
		// 创建行 索引值为最后一行上加一 表示新建一行
		HSSFRow row2 = sheet.createRow(sheet.getLastRowNum() + 1);
		// 创建格 并且赋值
		row2.createCell(0).setCellValue(sub.getId());
		row2.createCell(1).setCellValue(sub.getReg().getName());
		row2.createCell(2).setCellValue(sub.getAddresskey());
		row2.createCell(3).setCellValue(sub.getStartnum());
		row2.createCell(4).setCellValue(sub.getEndnum());
		row2.createCell(5).setCellValue(sub.getSingle());
		row2.createCell(6).setCellValue(sub.getPosition());
	}

	// 第三步：使用输出流进行文件下载（一个流、两个头）
	String filename = "分区管理.xls";
	resp.setContentType("application/vnd.ms-excel");

	try {
		String agent = req.getHeader("User-Agent");
		String filename2 = FileUtils.encodeDownloadFilename(filename, agent);

		resp.setHeader("content-disposition", "attachment;filename=" + filename2);
		ServletOutputStream outputStream = resp.getOutputStream();
		workbook.write(outputStream);
	} catch (IOException e) {
		e.printStackTrace();
	}
}

用到的jar包有：

	<dependency>
		<groupId>commons-fileupload</groupId>
		<artifactId>commons-fileupload</artifactId>
		<version>1.4</version>
	</dependency>
	
	<!-- poi -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.17</version>
	</dependency>
	
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.17</version>
	</dependency>