导入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; }
注意插入的数据格式要一致,否则会报异常。