飞天班第21节：企业项目研发（五）POI实际应用

@Test
public void testWriter07() throws IOException {
  // easyexcel重写了poi对07版Excel的解析(原生的API太慢了)
  // 新建一个 excel 工作簿 xlsx 07 ，对象的变化！
  Workbook xssfSheets = new XSSFWorkbook();

  // 创建一个默认的工作表  sheet0
  Sheet sheet = xssfSheets.createSheet("会员统计");

  // 创建一行记录 ， 下标从0 开始，就代表 row 1
  Row row1 = sheet.createRow(0);
  // 填写每一个格子的数据 (1-1)
  Cell cell11 = row1.createCell(0);
  cell11.setCellValue("今日注册");
  //  (1-2)
  Cell cell12 = row1.createCell(1);
  cell12.setCellValue("999");

  // 创建一行记录 ， 下标从0 开始，就代表 row 2
  Row row2 = sheet.createRow(1);
  // 填写每一个格子的数据 (2-1)
  Cell cell21 = row2.createCell(0);
  cell21.setCellValue("统计时间");
  //  (2-2)
  Cell cell22 = row2.createCell(1);
  String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
  cell22.setCellValue(dateTime);

  // 文件编写完毕，然后需要数据到处成为 xls
  FileOutputStream fileOutputStream = new FileOutputStream("/xjw/st-excel-poi/testWrite07.xlsx");
  xssfSheets.write(fileOutputStream);

  // 关闭流
  fileOutputStream.close();
  System.out.println("文件生成完毕");
}

// 03数据量限制65536行，07数据量几乎无限， 可以试试100万条数据（但十分消耗内存，可能产生内存溢出）
// 20~30w数据很适合 （相对比较慢）
@Test
public void testWriter07BigData() throws IOException {
  long begin = System.currentTimeMillis();
  // 创建工作簿
  XSSFWorkbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet();
  // 填写数据
  for (int rowNum = 0; rowNum < 100000 ; rowNum++) {
    // 创建内容
    Row row = sheet.createRow(rowNum);
    // 填写列的数据
    for (int cellNum = 0; cellNum < 10 ; cellNum++) {
      Cell cell = row.createCell(cellNum);
      cell.setCellValue(cellNum);
    }
  }
  System.out.println("写入完毕");
  // 文件编写完毕，然后需要数据到处成为 xls
  FileOutputStream fileOutputStream = new FileOutputStream("/xjw/testWrite07-bigobject.xls");
  workbook.write(fileOutputStream);
  // 关闭流
  fileOutputStream.close();

  long end = System.currentTimeMillis();
  System.out.println((double) (end-begin)/1000);
}

// 一般使用这个来处理大的对象！加速 SXSSF
@Test
public void testWriter07BigDataFast() throws IOException {
  long begin = System.currentTimeMillis();
  // 创建工作簿。 会产生临时文件，
  // 100条记录保存在内存中，超过这个数据，前面的内容就写入到 excel
  SXSSFWorkbook workbook = new SXSSFWorkbook();
  Sheet sheet = workbook.createSheet();
  // 填写数据
  for (int rowNum = 0; rowNum < 100000 ; rowNum++) {
    // 创建内容
    Row row = sheet.createRow(rowNum);
    // 填写列的数据
    for (int cellNum = 0; cellNum < 10 ; cellNum++) {
      Cell cell = row.createCell(cellNum);
      cell.setCellValue(cellNum);
    }
  }
  System.out.println("写入完毕");
  // 文件编写完毕，然后需要数据到处成为 xls
  FileOutputStream fileOutputStream = new FileOutputStream("/xjw/test-excel-poi/testWrite07-bigobject-fast.xls");
  workbook.write(fileOutputStream);
  // 关闭流
  fileOutputStream.close();

  // 建议：清除临时文件
  workbook.dispose();
  long end = System.currentTimeMillis();
  System.out.println((double) (end-begin)/1000);
}

private List<Map<Integer, String>> readExcelValue(InputStream is, int index)
  throws IOException, InvalidFormatException {
  List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
  Workbook wb = null;
  //poi自动判断
  wb = WorkbookFactory.create(is);

  // 获取第一个表格
  Sheet sheet = wb.getSheetAt(index);
  // Excel行数
  int totalRows = sheet.getPhysicalNumberOfRows();// 获取的是物理行数，也就是不包括那些空行（隔行）的情况

  // 列数
  int totalCells = 0;
  // 第一行为标题，以标题的cell个数为准
  if (totalRows >= 2 && sheet.getRow(0) != null) {
    totalCells = sheet.getRow(0).getLastCellNum();// 获取最后一个不为空的列是第几个
  }

  // 从标题行开始读起
  for (int r = 0; r < totalRows; r++) {
    Row row = sheet.getRow(r);
    if (row == null) { // 第"+(r+1)+"行数据有问题，请仔细检查！
      continue;
    }
    Map<Integer, String> map = new HashMap<Integer, String>();
    for (int c = 0; c < totalCells; c++) {
      Cell cell = row.getCell(c);
      if (cell == null) { // "第"+(c+1)+"列数据有问题，请仔细检查；";
        map.put(c, "");
      } else {
        map.put(c, getCellValueString(cell));
      }
    }
    list.add(map);
  }

  return list;
}

private String getCellValueString(Cell cell) {
  String returnVal = null;
  if (cell != null) {
    System.out.println();
    switch (cell.getCellType()) {
      case Cell.CELL_TYPE_NUMERIC:    
        if (DateUtil.isCellDateFormatted(cell)) {// 日期
          returnVal = new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd");
        } else { // 数字
          returnVal = NumberFormat.getInstance().format(cell.getNumericCellValue()).replaceAll(",", "");
        }
        break;
      case Cell.CELL_TYPE_STRING: // 字符串
        returnVal = cell.getStringCellValue();
        break;
      case Cell.CELL_TYPE_BLANK:  // 空
        returnVal = "";
        break;
      case Cell.CELL_TYPE_FORMULA:    // 公式
        returnVal=String.valueOf(cell.getNumericCellValue());
        break;
      default:
        returnVal = cell.toString();
        break;
    }
  }
  return returnVal == null ? "" : returnVal;
}

@Service
public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {

	// 一个事务
	@Transactional
	@Override
	public List<String> batchImport(MultipartFile file) throws Exception {
		// 处理失败消息封装
		List<String> errorMsg = new ArrayList<>();

		// 获取Excel工作表
		ExcelImportUtil excelHSSFUtil = new ExcelImportUtil(file.getInputStream());
		HSSFSheet sheet = excelHSSFUtil.getSheet();

		// 获取行
		int rowCount = sheet.getPhysicalNumberOfRows();
		if (rowCount <= 1){
			errorMsg.add("请填写数据");
			return errorMsg;
		}

		// 遍历获取数据 (父-子，写入数据库！)
		for (int rowNum = 0; rowNum < rowCount ; rowNum++) {
			Row rowData = sheet.getRow(rowNum);
			if (rowData!=null) { // 行中存在内容
				// 获取一级分类
				String levelOneValue = "";
				Cell levelOneCell = rowData.getCell(0);
				if (levelOneCell!=null){
					levelOneValue = excelHSSFUtil.getCellValue(levelOneCell).trim();
					if (StringUtils.isEmpty(levelOneValue)){
						errorMsg.add("第"+ rowNum + "行一级分类为空");
						continue;
					}
				}

				// 判断一级分类是否重复
				Subject subject = this.getOne(new QueryWrapper<Subject>().eq("parent_id",0).eq("title",levelOneValue));
				String parentId = null;
				if(null == subject){
					// 将一级分类插入到数据库
					Subject subjectLevelOne = new Subject();
					subjectLevelOne.setTitle(levelOneValue);
					subjectLevelOne.setSort(rowNum);
					this.save(subjectLevelOne);
					parentId = subjectLevelOne.getId();
				}else{
					parentId = subject.getId();
				}

				// 获取二级分类
				String levelTwoValue = "";
				Cell levelTwoCell = rowData.getCell(1);
				if (levelTwoCell!=null){
					levelTwoValue = excelHSSFUtil.getCellValue(levelTwoCell).trim();
					if (StringUtils.isEmpty(levelTwoValue)){
						errorMsg.add("第"+ rowNum + "行二级分类为空");
						continue;
					}
				}

				// 判断二级分类是否重复
				Subject subjectSub = this.getOne(new QueryWrapper<Subject>().eq("parent_id",parentId).eq("title",levelTwoValue));
				if(null == subjectSub){
					// 将二级分类插入到数据库
					Subject subjectLevelTwo = new Subject();
					subjectLevelTwo.setTitle(levelTwoValue);
					subjectLevelTwo.setParentId(parentId);
					subjectLevelTwo.setSort(rowNum);
					this.save(subjectLevelTwo);
				}
			}
		}
		return errorMsg;
	}