java - 我想为spring boot代码编写一个junit，其中包含将excel转换为json的代码，我在这里发布了我尝试过的内容

Question

package com.example.demo;

import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.core.JsonProcessingException;
@Service
public class UploadService {
    
    public String creteJSONFromExcel(MultipartFile file)
    {
        String jsonString = "";
        
        try {
                Path tempDir = Files.createTempDirectory("");
                File tempFile = tempDir.resolve(file.getOriginalFilename()).toFile();
                
                file.transferTo(tempFile);
                Workbook excelWorkBook = WorkbookFactory.create(tempFile);
            int totalSheetNumber = excelWorkBook.getNumberOfSheets();
            for(int i=0;i<totalSheetNumber;i++)
            {
                Sheet sheet = excelWorkBook.getSheetAt(i);
                if(totalSheetNumber ==1 && sheet.getLastRowNum() == 0 && sheet.getRow(0)==null) {
                    throw new Exception("It is an empty file");
                }
                String sheetName = sheet.getSheetName();
                if(sheetName != null && sheetName.length() > 0)
                {
                    List<List<String>> sheetDataTable = getSheetDataList(sheet);
                    jsonString = getJSONStringFromList(sheetDataTable);
                }
            }
        }
        
        catch(Exception e) {
            System.out.println(e.getMessage());
        }
            return jsonString ;
        }
    private static List<List<String>> getSheetDataList(Sheet sheet)
    {
        List<List<String>> ret = new ArrayList<List<String>>();
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        if(lastRowNum > 0)
        {
            for(int i=firstRowNum; i<lastRowNum + 1; i++)
            {
                Row row = sheet.getRow(i);
                int firstCellNum = row.getFirstCellNum();
                int lastCellNum = row.getLastCellNum();
                List<String> rowDataList = new ArrayList<String>();
                for(int j = firstCellNum; j < lastCellNum; j++)
                {
                    Cell cell = row.getCell(j,org.apache.poi.ss.usermodel.Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    CellType cellType = cell.getCellType();
                    if(cellType == CellType.NUMERIC)
                    {
                        double numberValue = cell.getNumericCellValue();
                        String stringCellValue = BigDecimal.valueOf(numberValue).toPlainString();
                        rowDataList.add(stringCellValue);
                    }else if(cellType == CellType.STRING)
                    {
                        String cellValue = cell.getStringCellValue();
                        rowDataList.add(cellValue);
                    }else if(cellType == CellType.BOOLEAN)
                    {
                        boolean numberValue = cell.getBooleanCellValue();
                        String stringCellValue = String.valueOf(numberValue);
                        rowDataList.add(stringCellValue);
                    }else if(cellType == CellType.BLANK)
                    {
                        rowDataList.add("");
                    }
                }
                ret.add(rowDataList);
            }
        }
        return ret;
    }
    /* Return a JSON string from the string list. */
    private static String getJSONStringFromList(List<List<String>> dataTable)
    {
        String ret = "";
        if(dataTable != null)
        {
            int rowCount = dataTable.size();
            if(rowCount > 1)
            {
                JSONArray tableJSONArray = new JSONArray();
                List<String> headerRow = dataTable.get(0);
                int columnCount = headerRow.size();
                // Loop in the row data list.
                for(int i=1; i<rowCount; i++)
                {
                    List<String> dataRow = dataTable.get(i);
                    JSONObject rowJsonObject = new JSONObject();
                    for(int j=0;j<columnCount;j++)
                    {
                        String columnName = headerRow.get(j);
                        String columnValue = dataRow.get(j);
                        String s =  columnValue.replaceAll("\\W+", " ");
                        rowJsonObject.put(columnName,s.substring(0, Math.min(s.length(), 100)));
                    }
                    rowJsonObject.put("id", ""+i);
                    tableJSONArray.put(rowJsonObject);
                }
                ret = tableJSONArray.toString();
            }
        }
        return ret;
    }
}

这是我尝试过的

import static org.junit.Assert.*;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.mockito.Mockito.doNothing;
import static org.mockito.Mockito.when;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.Rule;
import org.junit.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.junit.rules.ExpectedException;
import org.junit.runner.RunWith;
import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.springframework.web.multipart.MultipartFile;


public class UploadServiceTest {
    
    @Mock
    MultipartFile file;
    
    @Mock
    Path tempDir;
    
    @Mock
    File tempfile;
    
    @Mock
    Workbook excelWorkBook;
    
    @Mock
    org.apache.poi.ss.usermodel.Sheet sheet;
    
    
    
    @InjectMocks
    UploadService uploadservice;
    @Rule
    public ExpectedException exceptionRule = ExpectedException.none();
    
    
    
        
    @Test
    public void creteJSONFromExcelTest() throws Exception {
        when(Files.createTempDirectory("")).thenReturn(tempDir);
        when(tempDir.resolve(file.getOriginalFilename()).toFile()).thenReturn(tempfile);
        doNothing().when(file).transferTo(tempfile);
        when(WorkbookFactory.create(tempfile)).thenReturn(excelWorkBook);
        when(excelWorkBook.getNumberOfSheets()).thenReturn(1);
        when(excelWorkBook.getSheetAt(1)).thenReturn(sheet);
        when(sheet.getSheetName()).thenReturn("customer");
        List<List<String>> mockedSheetDataTable = new ArrayList<List<String>>();
        List<String> singleList = new ArrayList<String>();
        singleList.add("Address");
        singleList.add("Name");
        mockedSheetDataTable.add(singleList);
        ArrayList<String> anotherList = new ArrayList<String>();
        anotherList.add("Bangalore");
        anotherList.add("teja");
        mockedSheetDataTable.add(anotherList);

        when(uploadservice.getSheetDataList(sheet)).thenReturn(mockedSheetDataTable);
        when(uploadservice.getJSONStringFromList(mockedSheetDataTable)).thenReturn("{\"Address\":\"Bangalore\"\"Name\":\"teja\"}");
        assertEquals(uploadservice.creteJSONFromExcel(file), "{\\\"Address\\\":\\\"Bangalore\\\"\\\"Name\\\":\\\"teja\\\"}");
        
        
        
    }
    @Test
    public void shouldFailWhenGivenBlankFile() throws Exception {
        
        exceptionRule.expect(Exception.class);
        when(Files.createTempDirectory("")).thenReturn(tempDir);
        when(tempDir.resolve(file.getOriginalFilename()).toFile()).thenReturn(tempfile);
        doNothing().when(file).transferTo(tempfile);
        when(WorkbookFactory.create(tempfile)).thenReturn(excelWorkBook);
        when(excelWorkBook.getNumberOfSheets()).thenReturn(1);
        when(excelWorkBook.getSheetAt(1)).thenReturn(sheet);
        when(sheet.getLastRowNum() == 0 && sheet.getRow(0)==null).thenThrow(new Exception());
    
     
    
   

}
}

在这里，我已将 excel 转换为 json，并尝试为其编写服务类测试，我有一个仅调用服务类方法的控制器类，有人告诉我只需使用已知的 excel 文件调用该方法并检查是否返回值符合您的预期，但不知道如何在代码中实现

标签： javaexcelspringjunit