Excel上传下载

1定义接口

package com.util;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

public interface IExcelFile {
	//用于时间转换
	public static SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
	
	//解析Excel文件
	public List<List<Map<String,Object>>> parse(HSSFWorkbook hssfWorkbook,String fileName)throws IOException;
	
	//创建Excel文件
	public HSSFWorkbook createExcel(List<Map<String,Object>> excel,String excelType);
	
	//类型转换
    default public String getValue(HSSFCell hssfCell) {
    	//判断是否是boolean类型
        if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
          //判断是否是数字类型
        } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        	  //判断是否是时间
        	  if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(hssfCell)) { 
                    //获取时间对象
        		    Date theDate = hssfCell.getDateCellValue(); 
                    //是时间转换为时间格式的字符串
                    return  dff.format(theDate);  
              }	
        	  //是数字类型转换为String类型
    	      hssfCell.setCellType(Cell.CELL_TYPE_STRING);
    	      //返回String
	          return String.valueOf(hssfCell.getStringCellValue());
	    } else {
	    	//else是String类型
	        return String.valueOf(hssfCell.getStringCellValue());
	    }
    }	

}

2.实现接口

package com.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;

@Component
public class ExcelFileImpl implements IExcelFile {
	
	
	
	
	
	

	@Override
	public HSSFWorkbook createExcel(List<Map<String, Object>> excel, String excelType) {
		if(excelType.equals("xls")){
			return extracted(excel);
		}else if(excelType.equals("xlsx")){
			return null;
		}else{
			return null;
		}
	}

	private HSSFWorkbook extracted(List<Map<String, Object>> excel) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFRow head = sheet.createRow(0);
		//设置第一行
		createTableHead(head);
		for (int i = 0; i < excel.size(); i++) {
			HSSFRow row = sheet.createRow(i + 1);
			createTableValue(row, excel.get(i));
		}
		return wb;
	}
	
	//通过键获得值
	private void createTableValue(HSSFRow row1, Map<String, Object> map) {
		HSSFCell cell1 = row1.createCell(0);
		setValue(map, cell1, "id");
		HSSFCell cell2 = row1.createCell(1);
		setValue(map, cell2, "name");
		HSSFCell cell3 = row1.createCell(2);
		setValue(map, cell3, "menu_en_name");
		HSSFCell cell4 = row1.createCell(3);
		setValue(map, cell4, "menu_desc");
		HSSFCell cell5 = row1.createCell(4);
		setValue(map, cell5, "user_id");
		HSSFCell cell6 = row1.createCell(5);
		setValue(map, cell6, "menu_value");
		HSSFCell cell7 = row1.createCell(6);
		setValue(map, cell7, "parent_id");
		HSSFCell cell8 = row1.createCell(7);
		setValue(map, cell8, "create_date");
	}

	//通过键设置值
	private void setValue(Map<String, Object> map, HSSFCell cell4, String key) {
		if (map.get(key) != null) {
			cell4.setCellValue(map.get(key).toString());
		}
	}
	
    //设置头部
	private void createTableHead(HSSFRow row1) {
		HSSFCell cell1 = row1.createCell(0);
		cell1.setCellValue("ID");
		HSSFCell cell2 = row1.createCell(1);
		cell2.setCellValue("名字");
		HSSFCell cell3 = row1.createCell(2);
		cell3.setCellValue("菜单中文名");
		HSSFCell cell4 = row1.createCell(3);
		cell4.setCellValue("菜单描述");
		HSSFCell cell5 = row1.createCell(4);
		cell5.setCellValue("用户ID");
		HSSFCell cell6 = row1.createCell(5);
		cell6.setCellValue("菜单值");
		HSSFCell cell7 = row1.createCell(6);
		cell7.setCellValue("菜单父ID");
		HSSFCell cell8 = row1.createCell(7);
		cell8.setCellValue("创建时间");
	}

	@Override
	public List<List<Map<String, Object>>> parse(HSSFWorkbook hssfWorkbook, String fileName) {
		// 判断是否为空
		if (fileName == null) {
			return null;
		}
		// 判断是否是解析的文件格式
		if (fileName.endsWith(".xls") == false && fileName.endsWith(".xlss") == false) {
			return null;
		}
		//用来放页和行	
		List<List<Map<String, Object>>> book = new ArrayList<List<Map<String, Object>>>();
		//遍历Excel文件的页数
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                       //每页创建一个集合对象
			List<Map<String, Object>> page = new ArrayList<Map<String, Object>>();
			//获取一页数据元素
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			//遍历Excel文件每页的行数
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				//获取一行元素
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				//这一行为空结束本次开始下一次循环
				if (hssfRow == null) {
					continue;
				}
				//获取这一行的第一个下标
				int min = hssfRow.getFirstCellNum();
				//获取这一行的最后一个下标
				int max = hssfRow.getLastCellNum();
				//用来放一行
				Map<String, Object> map = new HashMap<String, Object>();
				//遍历每一行的每一列的元素
				for (int i = min; i < max; i++) {
					//获取每一列的元素
					HSSFCell one = hssfRow.getCell(i);
					if (one == null ) {
						continue;
					}
					//每一行的列
					map.put(String.valueOf(i), getValue(one));
				}
				page.add(map);//页的每一行	
			}
			book.add(page);//每一页
		}
		return book;
	}

}

jar包