Create a xlsx file in java

You need the jdbc library of the database you want to read from java.

https://www.journaldev.com/2471/jdbc-example-mysql-oracle

To access Excel files you can use the POI library. Here is a link that has examples::

https://www.java67.com/2014/09/how-to-read-write-xlsx-file-in-java-apache-poi-example.html?m=1

Here is a library quite old though... https://sourceforge.net/projects/jexcelapi/

And a more recent example: https://medium.com/@ssaurel/generating-microsoft-excel-xlsx-files-in-java-9508d1b521d9

Havn't used this though.

Hi when i am running my method via main class xlsx file is generating but when i use same method in my web apps its saying its corrupted please let me know the solution.

Hi,

I would go with Apache POI as Juan mentions.

What error are you getting in your web app when you create the xlsx file ? POI should work on both standalone and web. :)

Regards,
    Tomas Helgi

i gave a link to download xslx file from internet explorer when i am clicking on that link it will download a zip file containing many xml's but actual xlsx file not please let me the solution.

is correct, the .xlsx file is a compressed file. You can compare the xlsx file generated on the web against the one generated in standalone as    zip file. Perhaps it is generating well on the web and is only a encoding  problem.
What is the error message?

not any error message its downloaded the zip file but when i am unzipping it xlsx file are missing other then that 4-5 xml's file are there .

I understand that your problem is that the same java code to create an excel file does it well when standalone is executed and badly when it is executed in a web application. I don't understand when you talk about a zip file.

The .xlsx files, unlike the .xls, are really zip files that contain directories and xml files that the excel interprets. The set of these files is an .xlsx.

When you talk about a zip file are you talking about the xlsx file?

When you open the xlsx file with excel, what error message does it give you?
On the other hand, are java library versions the same in both environments? What version of poi do you use and what application server?

Hi,

Make sure that your servlet that serves the excel file has in the response.contenttype is set to "application/vnd.ms-excel"


Regards,
  Tomas Helgi

when i am opening file from the location its opening perfectly but when i am opening that file from link which i have given in jsp page it giving zip file which containing no xlsx file . pleas help me out on this .

nothing happen same zip file coming when clicking on href .please let me know the solution or provide me some sample code.

Use a link like:

<a href="blahblah.xlsx" type="application/vnd.ms-excel">

Select allOpen in new window

i m using the link like below

<td align=center><label><a href="<%=pathName + url_string%>"><%=reportname%></a></label></td>

where
pathName  coming from context file  - like http://localhost:8080/Project
url_string coming from database - like /Report/filename.xlsx

please let me know if i am doing something wrong

Add this to a  mix...    type="application/vnd.ms-excel" like in:

<td align=center><label><a href="<%=pathName + url_string%>" type="application/vnd.ms-excel"><%=reportname%></a></label></td>

its not working please please let me some solution

if the filename ends in .xlsx ... and/or the type (as send with the download, derived from the type in the <a ... > link).
The browser should interpret either of those.
Can you show a HTTP header of a download?

curl -v LINK
Here link is one of your generated links...
The content can be clipped.

can you please explore lil bit more i don't  understand what you want from my side.

I explained how  a browser interprets the data it gets from the HTTP Header,  (filte extension & Content-Type field) and How YOU can set a a value from that in a a href=... type=...
entry.
A browser can determine the type  primarily from the Content-Type field, and if that is not present (or ignored like IE does) it can use the extension of the name (.xlsx).
So you need to convince your browser by sending valid headers & data "to do the right thing" (tm).

YOU can verify what is sent through various means most often the easiest is using curl -v URL   with the right URL.
it will show your the complete HTTP query and answeer in it full glory, as seen by a browser.

Hi,

I would move the code (the process of serving the xlsx files) from the JSP page to a servlet where you set the  response.contenttype to "application/vnd.ms-excel". This makes that process separate from the actual JSP page process and thus minimizes conflict of what type of data is served.

A simple servlet whould look like this

package mypkg;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
 
public class XLSXServlet extends HttpServlet {
   @Override
   public void doGet(HttpServletRequest request, HttpServletResponse response)
               throws IOException, ServletException {
      // Set the response message's MIME type
      // note that you could change this to "application/octet-stream" to make it more  file-type independent
      response.setContentType("application/vnd.ms-excel"); 
      PrintWriter out = response.getWriter();
 
      try {
         // Here you set the code that retrieves the files for output.         
         // see below examples for more info

      } finally {
         out.close();  // Always close the output writer
      }      

     
  }
}

Select allOpen in new window

In your webapp.xml file you need to put this code

<servlet>
      <servlet-name>XLSXServlet</servlet-name>
      <servlet-class>mypkg.XLSXServlet</servlet-class>
   </servlet>
<servlet-mapping>
      <servlet-name>XLSXServlet</servlet-name>
      <url-pattern>/retrieveXLSX</url-pattern>
   </servlet-mapping>

Select allOpen in new window

And in the JSP page an url that simply says
<td align=center><label><a href="<%=/retrieveXLSX%>" ><%=reportname%></a></label></td>

See here for more examples
https://www.baeldung.com/servlet-download-file
https://www.javatpoint.com/example-of-downloading-file-from-the-server-in-servlet
https://www.programmergate.com/download-file-from-web-application/

Regards,
    Tomas Helgi

which formate it gives result in xls or xlsx .

Hi,

For xls use "application/vnd.ms-excel".

For xlsx use "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

and if you vant to make it file independent use "application/octet-stream" where the file is transfered in binary.

Regards,
  Tomas Helgi

Hi Tomas ,

My file path is in database . in jsp page just fetching the filepath and give it to the <a href tag . i m not writing any thing to that file that process completed before downloading the file . if not clear then let me know. i am creating xls file save in the folder and path of that file is store in database . in jsp page call that path of jsp from database for download and view.

Hi,

The link should include the  type="application/octet-stream" (or any of the above types mentioned.
As noci pointed out in earlier comment you could change it to

<td align=center><label><a href="<%=pathName + url_string%>" type="application/octet-stream"><%=reportname%></a></label></td> 

Select allOpen in new window

However you should use the method I suggested as it is a recommended practice and the web-server can divide the JSP and servlet into different separate processes and thus the content types in the JSP and servlet does not collide which otherwise could arguably corrupt files/data in the transfer.

Regards,
    Tomas Helgi

Hi,

Do you need more help on this ?

Regards,
   Tomas Helgi