poi Excel导入工具类

导入2003以上版本的Excel，即.xlsx格式的

工具类:

public class PoiExcelUtil {

    // 判断文件格式
    public static XSSFWorkbook getWorkbook(InputStream in, String fileName) {
        XSSFWorkbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        try {
            if (".xlsx".equals(fileType)) {
                workbook = new XSSFWorkbook(in);
            } else {
                throw new ServiceException("请上传xlsx类型的excle文件!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    // 生成表格
    public static XSSFWorkbook getXSSFWorkbook(String sheetName, String[] title, String[][] values, XSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new XSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
//        HSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFSheet sheet = workbook.createSheet();
        // 在sheet中添加表头第0行
        XSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        XSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
//            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, 256 * 15 + 184);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        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 workbook;
    }

    // 设置发送响应流
    public static void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {
        try {
            // 设置浏览器响应的格式
//            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName +".xls", "UTF-8") + "");
//            response.setContentType("application/vnd.ms-excel;charset=utf-8");
//            response.setCharacterEncoding("utf-8");

            //设置响应头
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
            response.setContentType("application/x-xls");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

调用：

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("file");
InputStream inputStream = file.getInputStream();
test.import(inputStream,file.getOriginalFilename());
inputStream.close();

public boolean import(InputStream in, String fileName) {

    //创建Excel工作薄
    XSSFWorkbook workbook = PoiExcelUtil.getWorkbook(in, fileName);
    if (org.springframework.util.StringUtils.isEmpty(workbook)) {
        throw new ServiceException("创建Excel工作薄为空！");
    }
    //读取默认第一个工作表sheet
    XSSFSheet sheet = workbook.getSheetAt(0);
    int firstRowNum = 1; //第二行开始
    int lastRowNum = sheet.getLastRowNum(); //最后一行
    for (int i = firstRowNum; i <= lastRowNum; i++) {
        XSSFRow row = sheet.getRow(i);
        Test test= new Test();
        test.setfMatCode(row.getCell(0).getStringCellValue());
        test.setProId(row.getCell(1).getStringCellValue());
        test.setProCode(row.getCell(2).getStringCellValue());
        test.setLineCode(row.getCell(3).getStringCellValue());
        test.setcMatCode(row.getCell(4).getStringCellValue());
        test.setPreProId(row.getCell(5).getStringCellValue());
        test.setManitLineId(row.getCell(6).getStringCellValue());
        testDao.insert(test);
    }
    return true;
}

注意插入的数据格式要一致，否则会报异常。