Ytukyg

Currently, i need to provide an API for another java application that send xls format response, while when i test in local use MockMVC to get response from my API and use HSSFWorkBook to parse it, while it failed, anyone can help me to show how implement this API in Java?
Very thanks!!!

framework: SpringMVC

my code:

    @RequestMapping(value = "/report", method = RequestMethod.GET)

    public ResponseEntity getReport() {

        LOGGER.info("begin to get report");

//        Workbook dailyReport = reportService.getDailyReport();

        Workbook dailyReport = new HSSFWorkbook();

        OutputStream outByteStream = new ByteArrayOutputStream();

        try {

            dailyReport.write(outByteStream);

            LOGGER.info("end to get report");

        } catch (IOException e) {

            LOGGER.error("IOException when write excel to stream, e: {}", e);

        }

        MultiValueMap<String, String> headers = new HttpHeaders();

        List<String> list = new ArrayList<>();

        list.add("application/vnd.ms-excel");

        headers.put(HttpHeaders.CONTENT_TYPE, list);

        return new ResponseEntity(outByteStream, headers, HttpStatus.OK);

    }

test method:

@Test

public void testGetMigrationDailyReport() {

    String url = "/report";

    try {

        MvcResult mvcResult = this.mockMvc.perform(MockMvcRequestBuilders.get(url)).andExpect(status().isOk()).andReturn();

        String response = mvcResult.getResponse().getContentAsString();

        byte bytes = response.getBytes();

        InputStream inputStream = new ByteArrayInputStream(bytes);



        Workbook workbook = new HSSFWorkbook(inputStream);

        Sheet sheet = workbook.getSheetAt(0);



    } catch (Exception e) {

        e.printStackTrace();

    }

}

full stacktrace:

    org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.IllegalArgumentException: No converter found for return value of type: class java.io.ByteArrayOutputStream

    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:979)

    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858)

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)

    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)

    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65)

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:167)

    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)

    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:155)

    at com.cisco.csit.wbxmig.web.IntegrationControllerTest.testGetMigrationDailyReport(IntegrationControllerTest.java:59)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:498)

    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)

    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)

    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)

    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)

    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)

    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)

    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)

    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)

    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)

    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)

    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)

    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

Caused by: java.lang.IllegalArgumentException: No converter found for return value of type: class java.io.ByteArrayOutputStream

    at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters(AbstractMessageConverterMethodProcessor.java:179)

    at org.springframework.web.servlet.mvc.method.annotation.HttpEntityMethodProcessor.handleReturnValue(HttpEntityMethodProcessor.java:183)

    at org.springframework.web.method.support.HandlerMethodReturnValueHandlerComposite.handleReturnValue(HandlerMethodReturnValueHandlerComposite.java:81)

    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:126)

    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832)

    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743)

    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)

    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961)

    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895)

    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)

    ... 38 more

I have tried the below:

It works well. The difference is I am doing ByteArrayOutputStream.getBytes().

@GetMapping("/down")

public ResponseEntity<byte> down() throws IOException {

    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();

        Workbook wb = new HSSFWorkbook();

        for(int sNo=1;sNo<=5;sNo++) {

            Sheet sheet = wb.createSheet("s"+sNo);

            for(int i=1;i<6;i++) {

                Row row = sheet.createRow(i);

                for(int j=1;j<=5;j++) {

                    Cell cell = row.createCell(j);

                    cell.setCellValue("test "+j);

                }



            }

        }

        wb.write(outByteStream);

        wb.close();



       MultiValueMap<String, String> headers = new HttpHeaders();

       List<String> list = new ArrayList<>();

       list.add("application/vnd.ms-excel");

       headers.put(HttpHeaders.CONTENT_TYPE, list);

    return new ResponseEntity<byte>(outByteStream.toByteArray(),headers, HttpStatus.OK);

}

Adding test.

The trick is create file from response and then read it again
create file from response

 FileOutputStream fos = new FileOutputStream("/Users/me/Desktop/output.xlsx");

 fos.write(bytes);

 fos.close();

read file

    FileInputStream fis = new FileInputStream("/Users/me/Desktop/output.xlsx");

    Workbook wb = WorkbookFactory.create(fis);

Whole test class

import java.io.ByteArrayOutputStream;



import java.io.FileInputStream;

import java.io.FileOutputStream;







import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;



import org.springframework.boot.test.autoconfigure.web.servlet.WebMvcTest;

import org.springframework.security.test.web.servlet.setup.SecurityMockMvcConfigurers;

import org.springframework.security.web.FilterChainProxy;

import org.springframework.test.context.ContextConfiguration;



import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.test.context.web.WebAppConfiguration;

import org.springframework.test.web.servlet.MockMvc;

import org.springframework.test.web.servlet.MvcResult;



import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;

import org.springframework.test.web.servlet.result.MockMvcResultMatchers;

import org.springframework.test.web.servlet.setup.MockMvcBuilders;

import org.springframework.web.context.WebApplicationContext;



import com.techdisqus.App;

import com.techdisqus.config.SpringSecurityConfig;

import com.techdisqus.controller.DefaultController;



@RunWith(SpringRunner.class)

/*@SpringBootTest

@ContextConfiguration*/

@WebMvcTest(DefaultController.class)

@ContextConfiguration(classes= {App.class,SpringSecurityConfig.class

        })

@WebAppConfiguration

public class MvcTest {





      @Autowired

        private  MockMvc mockMvc;



      @Autowired

        private FilterChainProxy springSecurityFilterChain;



      @Autowired

        private WebApplicationContext wac;



      @Before

        public void setup() {

            this.mockMvc = MockMvcBuilders.webAppContextSetup(this.wac)

                .addFilters(this.springSecurityFilterChain).apply(SecurityMockMvcConfigurers.springSecurity()).build();

        }





    @Test

    public void testDown() {

        String url = "/down";

        try {



            MvcResult mvcResult = this.mockMvc.perform(MockMvcRequestBuilders.get(url)).andExpect(MockMvcResultMatchers.status().isOk()).andReturn();

            byte bytes = mvcResult.getResponse().getContentAsByteArray();

            FileOutputStream fos = new FileOutputStream("/Users/me/Desktop/output.xlsx");

            fos.write(bytes);

            fos.close();



            FileInputStream fis = new FileInputStream("/Users/me/Desktop/output.xlsx");

            Workbook wb = WorkbookFactory.create(fis);

            int count = wb.getNumberOfSheets();  

            System.out.println("count "+count);

            for(int i=0;i<count;i++) {

              System.out.println(wb.getSheetAt(i).getSheetName());

              //perform asserts here if needed

            }

            System.out.println("done!");



        } catch (Exception e) {

            e.printStackTrace();

        }

    }

}