Excel表格导入导出

package certification.Utlie;

import java.io.*;
import java.text.Format;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtils {

  /**
   * 读取excel文件，按照sheet的顺序将表格保存在Map中
   *
   * @param file
   * @return Map<String,List<Object>> 第一个表格读取为 map.get("0")
   * @throws Exception 读文件出错
   */
  public static Map<String, List<Object[]>> readExcelFile(MultipartFile file) throws Exception {
    //检查文件
    checkFile(file);
    //获得Workbook工作薄对象
    Workbook workbook = getWorkBook(file);
    Map<String, List<Object[]>> result = new HashMap<String, List<Object[]>>();
    for (int sn = 0; sn < workbook.getNumberOfSheets(); sn++) {
      Sheet sheet = workbook.getSheetAt(sn);

      List<Object[]> rows = new LinkedList<>();
      int colnum = 0;
      for (Row row : sheet) {
        if (row.getLastCellNum() <= 0) {
          break;
        }
        if (colnum == 0) {
					colnum = row.getLastCellNum();
				}
        Object[] data = new Object[colnum];
        for (Cell cell : row) {
          data[cell.getColumnIndex()] = getCellValue(cell);
        }
        rows.add(data);
      }
      result.put(sn + "", rows);
    }

    workbook.close();
    return result;
  }

  private static Object getCellValue(Cell cell) {

    switch (cell.getCellType()) {
      case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
      case Cell.CELL_TYPE_BLANK:
        return "";
      case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
      case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
          return cell.getDateCellValue();
        } else {
          DataFormatter dataFormatter = new DataFormatter();
          Format format = dataFormatter.createFormat(cell);
          return format.format(cell.getNumericCellValue());
        }

      default:
        return "";
    }
  }

  public static List<Date> findFriday(String y, int m) {
    int year = Integer.parseInt(y);
    int month = m - 1;
    Calendar calendar = Calendar.getInstance();
    calendar.set(Calendar.YEAR, year);
    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DATE, 1);
    List<Date> result = new ArrayList<>();
    while (calendar.get(Calendar.MONTH) == month) {
      if (calendar.get(Calendar.DAY_OF_WEEK) == 6) {
        result.add(calendar.getTime());
        calendar.add(Calendar.DATE, 7);
      } else {
        calendar.add(Calendar.DATE, 1);
      }
    }
    return result.subList(0, 4);
  }

  //校验文件是否合法
  public static void checkFile(MultipartFile file) throws IOException{
    //判断文件是否存在
    if(null == file){
      throw new FileNotFoundException("文件不存在！");
    }
    //获得文件名
    String fileName = file.getOriginalFilename();
    //判断文件是否是excel文件
    if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
      throw new RuntimeException(fileName + "不是excel文件");
    }
  }

  public static Workbook getWorkBook(MultipartFile file) {
    //获得文件名
    String fileName = file.getOriginalFilename();
    //创建Workbook工作薄对象，表示整个excel
    Workbook workbook = null;
    try {
      //获取excel文件的io流
      InputStream is = file.getInputStream();
      //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
      if(fileName.endsWith("xls")){
        //2003
        workbook = new HSSFWorkbook(is);
      }else if(fileName.endsWith("xlsx")){
        //2007
        workbook = new XSSFWorkbook(is);
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
    return workbook;
  }

  //读取
  public List<Object> readExcel(MultipartFile file){
    List<Object> list = new ArrayList<>();
    try {
      Map<String, List<Object[]>> data = readExcelFile(file);
      List<Object[]> sheet = data.get("0");
      for (int i = 1; i < sheet.size(); i++) {
        Object[] objects = sheet.get(i);
          for (Object o : objects) {
           list.add(o);
          }
          System.out.println();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }


  /**
   *
   * @param filePath 文件名称
   * @param name sheet名称
   * @param objects 第一行标题名称
   * @param objectList 第二行到第N列数据
   */
  public void writeExcel(String filePath,String name,List<Object> objects,List<Object> objectList){
  //判断文件是否已经存在
    if(sheetExist(filePath,name)){
    //存在,直接添加
      writeToExcel(filePath,name,objectList);
    }else{
    //不存在
    //先创建文件
    createExcel(filePath,name,objects);
    //然后添加数据
    writeToExcel(filePath,name,objectList);
  }




  //判断文件和sheet是否存在
  public boolean sheetExist(String filePath,String name){
    boolean flag = false;
    File file = new File(filePath);
    if(file.exists()){
      //文件存在
      //创建workbook
      try {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
        //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
        XSSFSheet sheet = workbook.getSheet(name);
        if(sheet != null){    //sheet存在
          flag = true;
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }else{    //文件不存在
      flag = false;
    }
    return flag;
  }

  //写入数据到Excel
  public void writeToExcel(String filePath,String name,List<Object> objectList){
    //创建workbook
    File file = new File(filePath);
    XSSFWorkbook workbook = null;
    try {
      workbook = new XSSFWorkbook(new FileInputStream(file));
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
    FileOutputStream out = null;
    //sheet名称
    XSSFSheet sheet = workbook.getSheet(name);
    try {
     	//获取表格的总行数
     	//需要加一
        int rowCount = sheet.getLastRowNum() + 1; 
        //添加数据
        Row row = sheet.createRow(rowCount);     //最新要添加的一行
      for (int i = 0;objectList.size() > i;i++){
        row.createCell(i).setCellValue(objectList.get(i).toString());
      }
      out = new FileOutputStream(filePath);
      workbook.write(out);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        out.close();
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
  }

  //创建Excel文件
  public void createExcel(String filePath,String name,List<Object> objects){
    XSSFWorkbook workbook = new XSSFWorkbook();
    //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
    XSSFSheet sheet = workbook.createSheet(name);
    //新建文件
    FileOutputStream fos = null;
    try {
      XSSFRow row = sheet.createRow(1);    //创建第一行
      for (int i = 0;objects.size() > i;i++){
        //添加表头
        row.createCell(i).setCellValue(objects.get(i).toString());
      }
      fos = new FileOutputStream(filePath);
      workbook.write(fos);
      fos.close();
    } catch (Exception e){
      e.printStackTrace();
    }
  }

  }