2.java操作Excel,并且赋上封装好的操作Excel的工具类

java操作Excel,并且赋上封装好的操作Excel的工具类

下载文件

 @Autowired
 private ExcelUtil excelUtil;
  
 /**  文件下载（参数根据需要的来)
     * @param file  上传的文件
     * @param data  附带的参数
     * @param response
     */
 @RequestMapping("/loadExcel")
    public void loadExcel( HttpServletResponse response) {
        HSSFWorkbook wb= null;
        OutputStream os=null;
        try {
            //sheet名
            String sheetName = "Sheet1";
            //文件的内容
            List<HashMap> contentList = new ArrayList<>();
            //文件的表头
            List<HashMap> titles =new ArrayList<>();
            wb = excelUtil.exportExcel(sheetName, titles, contentList);
            String fileName = new String("测试excel".getBytes(), "UTF-8");
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            os = response.getOutputStream();
            wb.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

封装的操作Excel工具类
分为单独获取表头，单独获取除去表头的内容，导出Excel文件流

package org.mohrss.si.biz.utils;
import org.springframework.stereotype.Component;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class ExcelUtil {

    private static Workbook wb;
    private static Sheet sheet;
    private static Row row;

    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    /**
     * 获取表头
     * @param inputStream 文件流
     * @param suffix      文件后缀
     * @return
     */
    public Map<Integer, String> readExcelTitle(InputStream inputStream, String suffix) {
        getWorkbook(inputStream, suffix);
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        Map<Integer, String> map = new HashMap<>();
        for (int i = 0; i < colNum; i++) {
            map.put(i, row.getCell(i).getStringCellValue());
        }
        return map;
    }

    private void getWorkbook(InputStream inputStream, String suffix) {
        try {
               //判断后缀符合excel文件
                if (EXCEL_XLS.equals(suffix.toLowerCase())) {
                    wb = new HSSFWorkbook(inputStream);
                } else if (EXCEL_XLSX.equals(suffix.toLowerCase())) {
                    wb = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 读取excel内容
     * @param inputStream  文件流
     * @param suffix       文件后缀
     * @return
     */
    public List<HashMap> readExcelContent(InputStream inputStream, String suffix) {
        //获取标题
        Map<Integer, String> title = readExcelTitle(inputStream, suffix);
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();


        //返回的内容
        List<HashMap> contentList = new ArrayList<>();

        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            HashMap cellValue = new HashMap();
            while (j < colNum) {
                String obj = getCellFormatValue(row.getCell(j));
                cellValue.put(title.get(j), obj);
                j++;
            }
            contentList.add(cellValue);
        }
        return contentList;
    }


    /**
     * 导出excel
     *
     * @param sheetName
     * @param title  
     * @param values
     * @return
     *
     *  替换之前表头名 beforeReplacing
     *  替换之后表头名 afterReplacing
     */
    public HSSFWorkbook exportExcel(String sheetName, List<HashMap> title, List<HashMap> values) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook 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);
        //设置字体样式
        HSSFFont font = wb.createFont();
        //字体颜色
        font.setColor(HSSFFont.COLOR_RED);
        //字体加粗
        font.setBold(true);
        style.setFont(font);
        //声明列对象
        HSSFCell cell = null;

        for (int i = 0; i < title.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(title.get(i).get("afterReplacing").toString());
            cell.setCellStyle(style);
        }

        for (int i = 0; i <values.size(); i++) {
             row=sheet.createRow(i+1);
            for (int j = 0; j < title.size(); j++) {
                HSSFCell cell1 = row.createCell(j);

                String titleValue = title.get(j).get("beforeReplacing").toString();
                if(titleValue==null ||  "".equals(titleValue)  ){
                    cell1.setCellValue("");
                }else {
                    cell1.setCellValue( values.get(i).get(titleValue).toString());
                }
                //设置单元格样式
                HSSFCellStyle rowStyle = wb.createCellStyle();
                //设置单元格垂直居中
                rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置单元格水平居中
                rowStyle.setAlignment(HorizontalAlignment.CENTER);
                cell1.setCellStyle(rowStyle);
            }
        }
        return wb;
    }


    /**
     * 判断数据类型
     *
     * @param cell cell
     * @return String
     */
    private static String getCellFormatValue(Cell cell) {
        String cellValue = null;
        if (cell != null) {
            // 判断当前Cell的Type
            CellType cellTypeEnum = cell.getCellType();
            switch (cellTypeEnum) {
                // str
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                // num
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm:ss");
                        Instant instant = cell.getDateCellValue().toInstant();
                        ZoneId zoneId = ZoneId.systemDefault();
                        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, zoneId);
                        cellValue = dateTimeFormatter.format(localDateTime);
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                default:
                    throw new IllegalStateException("Unexpected value: " + cellTypeEnum);
            }
        }
        return cellValue;
    }


}