excel 合并单元格测试导入

最新推荐文章于 2024-09-30 16:03:32 发布

fusu_ai

最新推荐文章于 2024-09-30 16:03:32 发布

阅读量72

点赞数

文章标签： excel java

本文链接：https://blog.csdn.net/fusu_ai/article/details/132321676

版权

import com.skytech.egov.questions.model.TAnswers;
import com.skytech.egov.questions.model.TQuestions;
import com.skytech.egov.questions.service.QuestionsService;
import com.skytech.egov.questions.util.AnalysisDocumentUtil;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import javax.annotation.Resource;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

@Component
public class AnalysisDocumentUtil {
  @Resource
  private QuestionsService questionsService;
  
  private static String firstRowValue = "";
  
  private static String uuid;
  
  private static List<TQuestions> questionsList = new ArrayList<>();
  
  private static List<TAnswers> answerList = new ArrayList<>();
  
  public void documentUtil(int questionsId) throws Exception {
    FileInputStream fis = new FileInputStream("C:/Users/OFFICE/Desktop/test.xls");
    XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(fis);
    Sheet sheet = xSSFWorkbook.getSheetAt(0);
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
      String rowStr = "";
      Row row = sheet.getRow(i);
      TAnswers answers = new TAnswers();
      for (Cell cell : row) {
        String value = getValue(sheet, i, cell.getColumnIndex());
        if ("".equals(value)) {
          value = cell.toString();
          if (cell.getColumnIndex() == 1) {
            answers.setOptionDesc(value);
          } else if (cell.getColumnIndex() == 2) {
            int i1 = (new Double(Double.parseDouble(value))).intValue();
            answers.setScore((new Long(i1)).longValue());
            answers.setSubjectId(uuid);
          } else if (cell.getColumnIndex() == 3) {
            answerList.add(answers);
          } 
        } 
        rowStr = rowStr + "--" + value;
      } 
      System.out.println(rowStr);
    } 
    this.questionsService.saveQuestionsAndAnswers(questionsList, answerList, questionsId);
    System.out.println("success");
  }
  
  private static String getValue(Sheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
      CellRangeAddress range = sheet.getMergedRegion(i);
      int firstColumn = range.getFirstColumn();
      int lastColumn = range.getLastColumn();
      int firstRow = range.getFirstRow();
      int lastRow = range.getLastRow();
      if (row >= firstRow && row <= lastRow && column >= firstColumn && column <= lastColumn) {
        Row fRow = sheet.getRow(firstRow);
        Cell fCell = fRow.getCell(firstColumn);
        if (!fCell.getStringCellValue().equals(firstRowValue)) {
          uuid = UUID.randomUUID().toString();
          String stringCellValue = fCell.getStringCellValue();
          TQuestions questions = new TQuestions();
          questions.setSubjectDesc(stringCellValue);
          questions.setGuid(uuid);
          questionsList.add(questions);
          firstRowValue = fCell.getStringCellValue();
        } 
        return fCell.getStringCellValue();
      } 
    } 
    return "";
  }
}