上传、下载excel

/*****************************下载excel模板****************************************/
@Override
    public void downloadExcel(HttpServletResponse response) {
        String[] title = {"车站编号", "车站IP", "监视器编号", "电视墙IP", "客户端IP", "备注"};
        String[][] data = new String[1][];
        data[0] = new String[6];
        data[0][0] = "1";
        data[0][1] = "127.0.0.0";
        data[0][2] = "100";
        data[0][3] = "128.0.0.0";
        data[0][4] = "129.0.0.0";
        data[0][5] = "植物园站";
        ServletOutputStream out = null;
        Workbook hssfWorkbook = ExcelUtil.getHSSFWorkbook("modbus映射表导入模板", title, data, null);
        try {
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("modbus映射表导入模板" + ""
                    + ".xls", "UTF-8"));
            out = response.getOutputStream();
            hssfWorkbook.write(out);
        } catch (Exception e) {
            throw new BusinessException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), ErrorCode
                    .SYSTEM_CREATE_EXCEL_ERROR.getMessage(), e);
        }finally {
            if(null != out){
                try {
                    out.flush();
                    out.close();
                }catch (IOException e){
                    throw new BusinessException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), ErrorCode
                            .SYSTEM_CREATE_EXCEL_ERROR.getMessage(), e);
                }

            }
        }
    }
/*****************************导出excel信息****************************************/
    @Override
    public void exportStationExcel(HttpServletResponse response) {
        String[] title = {"车站编号", "车站IP", "监视器编号", "电视墙IP", "客户端IP", "备注"};
        List<StationInfo> stationExcelDTOS= stationInfoMapper.findAll();
        String[][] data = null;
        if(!CollectionUtils.isEmpty(stationExcelDTOS)){
            int size = stationExcelDTOS.size();
            data = new String[size][];
            StationInfo stationExcelDTO;
            for(int j=0;j<size;j++){
                stationExcelDTO= stationExcelDTOS.get(j);
                data[j] = new String[6];
                data[j][0] = Integer.toString(stationExcelDTO.getStationId());
                data[j][1] = stationExcelDTO.getStationIp();
                data[j][2] = Integer.toString(stationExcelDTO.getMonitorId());
                data[j][3] = stationExcelDTO.getTvmsIp();
                data[j][4] = stationExcelDTO.getModbusClientIp();
                data[j][5] = stationExcelDTO.getDescription();
            }
        }
        
/*****************************导入excel信息****************************************/
 @Override
    @Transactional
    public void importExcel(MultipartFile file) {
        try {
            List<StationInfo> stationInfos = new ArrayList<>();
            InputStream in = file.getInputStream();
            List<List<Object>> lists = ExcelUtil.getInfoListByExcel(in, file.getOriginalFilename());
            if (!CollectionUtils.isEmpty(lists)) {
                stationInfos = convertPoliceStation(lists);
            }
            stationInfoMapper.deleteAllInBatch();
            stationInfoMapper.saveStationInfoList(stationInfos);
        } catch (BusinessException bEx) {
            throw bEx;
        } catch (Exception e) {
            throw new BusinessException(ErrorCode.SYSTEM_IMPORT_EXCEL_ERROR.getCode(), ErrorCode
                    .SYSTEM_IMPORT_EXCEL_ERROR.getMessage(), e);
        }

    }

excel工具类

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.MessageSource;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


@Component
public class ExcelUtil {

    /**
     * 2003- 版本的excel
     */
    private final static String EXCEL_2003_L = ".xls";

    /**
     * 2007- 版本的excel
     */
    private final static String EXCEL_2007_U = ".xlsx";

    private static ExcelUtil excelUtil;

    @Autowired
    private MessageSource messageSource;

    @PostConstruct
    public void init() {
        excelUtil = this;
        excelUtil.messageSource = this.messageSource;
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        //声明列对象
        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        if ((values == null || values.length == 0) || (values.length == 1 && values[0].length == 0)) {
            return wb;
        }

        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }


    /**
     * 解析excel内容
     *
     * @param in, fileName
     * @return java.util.List<java.util.List<java.lang.Object>>
     * @throws Exception
     */
    public static List<List<Object>> getInfoListByExcel(InputStream in, String fileName) throws Exception {
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            String msg = excelUtil.messageSource.getMessage(
                    ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getMessage(), null,
                    LocaleContextHolder.getLocale());
            throw new BusinessBranchException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), msg);
        }
        try {
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;

            list = new ArrayList<List<Object>>();
            //遍历Excel中所有的sheet
            for (int i = 0; i < work.getNumberOfSheets(); i++) {
                sheet = work.getSheetAt(i);
                int rowNum = 0;
                if (sheet == null) {
                    continue;
                }
                //读取第一行，确保所有列被读到
                Row row0 = sheet.getRow(0);
                //总共列数
                int firstCellNum = row0.getFirstCellNum();//0
                int lastCellNum = row0.getLastCellNum();//6
                //遍历当前sheet中的所有行。sheet.getLastRowNum()返回最后一行的索引，即比行总数小1
                for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                    //读取一行
                    row = sheet.getRow(j);
                    List<Object> li = new ArrayList<>();
                    if (row == null) {
                        continue;
                    }
                    //遍历所有的列。row.getLastCellNum()返回的是最后一列的列数，即等于总列数
                    for (int y = firstCellNum; y < lastCellNum; y++) {
                        cell = row.getCell(y);
                        if (null != cell) {
                            li.add(getCellValue(cell));
                        } else {
                            li.add(null);
                        }
                    }
                    list.add(li);
                }
            }
        } catch (Exception e) {
            String msg = excelUtil.messageSource.getMessage(
                    ErrorCode.SYSTEM_CREATE_PARSE_LINE_ERROR.getMessage(),
                    null, LocaleContextHolder.getLocale());
            throw new BusinessException(ErrorCode.SYSTEM_CREATE_PARSE_LINE_ERROR.getCode(), msg, e);
        }
        return list;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (EXCEL_2003_L.equals(fileType)) {
            //2003-
            wb = new HSSFWorkbook(new POIFSFileSystem(inStr));
        } else if (EXCEL_2007_U.equals(fileType)) {
            //2007+
            wb = new XSSFWorkbook(inStr);
        } else {
            String msg = excelUtil.messageSource.getMessage(
                    ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getMessage(), null,
                    LocaleContextHolder.getLocale());
            throw new BusinessBranchException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), msg);
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = "";
        //格式化number String字符
        DecimalFormat df = new DecimalFormat("0");
        //日期格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
        //格式化数字
        DecimalFormat df2 = new DecimalFormat("0.00");

        switch (cell.getCellType()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if (CommonConstant.CELL_DECIMAL_FORMAT.equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if (CommonConstant.CELL_SIMPLE_DATE.equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            default:
                break;
        }
        if(null != value){
            return value.toString().replaceAll("：", ":");
        }else {
            return value;
        }

    }

    /**
      * 设置生成的Excel的具体样式
      *
      * @param workbook
      * @return org.apache.poi.ss.usermodel.CellStyle
     */
    public static CellStyle getColumnTopStyle(Workbook workbook) {
        CellStyle cellStyle=workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        //设置自动换行
        cellStyle.setWrapText(false);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }
}