Java - How to read xls format data via HTTP request not file

Question

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 think you supposed to return outByteStream.toByteArray() — Shiva
– Shiva, Commented Nov 21, 2018 at 7:44
See the accepted answer here: stackoverflow.com/questions/32641231/… .. Looks like it's what you need. — Garreth Golding
– Garreth Golding, Commented Nov 21, 2018 at 7:45
@secretsuperstar yep, i updated, API could send stream, while the test method could parse stream to WorkBook, will check Garreth's suggestion later — Rollsbean
– Rollsbean, Commented Nov 21, 2018 at 8:12

Shiva · Accepted Answer · 2018-11-21 11:52:57Z

2

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();
        }
    }
}

edited Nov 21, 2018 at 11:52

answered Nov 21, 2018 at 8:12

Shiva

2,0322 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Rollsbean Over a year ago

Hi @secret super star, what about the test method, the test was failed cause

NotOLE2FileException: Invalid header signature; read 0xC2A0C3118FC390C3, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document

when i use POI read it. Any suggestion?

Shiva Over a year ago

Similar question here : stackoverflow.com/questions/33879515/…

Rollsbean Over a year ago

what about use workbook as your stream content, just like that:

ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();         Workbook dailyReport = new HSSFWorkbook();         Sheet ddd = dailyReport.createSheet("ddd");         ddd.createRow(0).createCell(0).setCellValue("dddd");         dailyReport.write(outByteStream);

Shiva Over a year ago

You supposed to read as mvcResult.getResponse().getContentAsByteArray();

Shiva Over a year ago

Why do you want to create sheet ? are you doing in Api or test?

|

Collectives™ on Stack Overflow

Java - How to read xls format data via HTTP request not file

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related