Convert CSV to XLSX Java Servlet Example - Part 3

In Part 2 of this series, we showed how to get started with the server side code to convert CSV file to Excel format. We will continue the Server side code in this part, and provide the full Java servlet code in this part.

You may wish to read Part 1 and Part 2 of this series (links available at the bottom of the post) before going through this part.

Note that this example code works for CSV files with two columns only. But, there is always a lot of scope for expanding this to  include multiple columns that this is a straightforward task. Once we have obtained the uploaded file type and understood the nature of the output required [ We did this in Part 2 ], you can use Apache POI and Open CSV to generate the servlet code. The full servlet code that converts a CSV to XLS in Java is provided below:

Complete Servlet Code - CSV to XLS Conversion in Java

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; //Write in Office 2007, 2012 format
import org.apache.poi.xssf.usermodel.XSSFSheet; //Write in Office 2007, 2012 format
import org.apache.poi.hssf.usermodel.HSSFWorkbook; //XLS format
import org.apache.poi.hssf.usermodel.HSSFSheet;
import; //used to read user uploaded CSV file.
import; //read uploaded file
import java.util.*;
import org.apache.commons.fileupload.servlet.ServletFileUpload; //This class is required to handle the uploaded file in server
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.*;
public class Csv2XlsServlet extends HttpServlet {
public Csv2XlsServlet() {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
        OutputStream out = response.getOutputStream();  
        try {
                InputStream filecontent=null; //Initialize the input stream that accepts the user uploaded CSV file             
                List<FileItem> items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
                String outputtype="1"; //default output type
                for (FileItem item : items) {
                        if (item.isFormField()) {                               
                                String fieldname = item.getFieldName();
                                if (fieldname.equals("element_2")) {
                                outputtype = item.getString(); //identifies the type of conversion required
                                //the value can be 1 for XLS conversion, 2 for XLSX conversion
                        } else {
                        //The uploaded file is processed in this section
                        String fieldname = item.getFieldName();
                        String filename = FilenameUtils.getName(item.getName());
                        filecontent = item.getInputStream();
                        //Uploaded file is obtained into Inputstream object at this step                
         // We know the output type and have the file in the input stream now. We can now convert CSV to XLSX and return response back to server

         CSVReader reader = new CSVReader(new InputStreamReader(filecontent)); //reads the input CSV file in the servlet
         String [] nextLine;
         int lnNum = 0;
         /* Initialize both XLS and XLSX formats to start with. Only one will be used */
         HSSFWorkbook new_workbook=new HSSFWorkbook(); 
         HSSFSheet sheet=new_workbook.createSheet("CSV2XLS"); 
         XSSFWorkbook new_workbook_xlsx=new XSSFWorkbook(); 
         XSSFSheet sheet_xlsx=new_workbook_xlsx.createSheet("CSV2XLS"); 
         Row row;
         if (outputtype.equals("1")) {           
                 /* Set response type for XLS format */
         if (outputtype.equals("2")) {           
                 /* Set response type for XLSX format */
         Map<String, Object[]> excel_data = new HashMap<String, Object[]>(); //create a map and define data
         /* Populate data into logical Map */
         while ((nextLine = reader.readNext()) != null) {
                        excel_data.put(Integer.toString(lnNum), new Object[] {nextLine[0],nextLine[1]});                        
          /* Ready to convert CSV to logical Excel object */
          Set<String> keyset = excel_data.keySet();
          int rownum = 0;
                for (String key : keyset) { //loop through the data and add them to the cell
                        if (outputtype.equals("1")) {
                                row = sheet.createRow(rownum++); /* Create rows in the doucment using the right sheet object */
                        else {
                                row = sheet_xlsx.createRow(rownum++);
                        Object [] objArr = excel_data.get(key);
                        int cellnum = 0;
                        for (Object obj : objArr) {
                                Cell cell = row.createCell(cellnum++);
                                if(obj instanceof Double)
        /* Write response to output stream */
        if (outputtype.equals("1")) { 
        else {
        catch (Exception e) {
               System.err.println(e.toString()); /* Throw exceptions to log files */
         finally {
                 out.close();/* Close the output stream */
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException, IOException {
        doGet(request, response);

Once the code is ready, you need to compile the code and generate the class file. A sample command line compilation statement is provided below:

javac -Xlint:unchecked -classpath .;poi-3.8.jar;commons-io-2.4.jar;commons-fileupload-1.2.2.jar;opencsv-2.3.jar;servlet-api.jar;poi-ooxml-schemas-3.8.jar;poi-ooxml-3.8.jar

That completes the main part of this post. We now go to the final part of this post, where we will explain how to get this code working in Apache TomCat servlet, and grab all the supporting files for this tutorial for download.
This Series: How to Convert CSV to XLSX using Java Servlet API Code?

Keywords: Convert CSV to XLS, Convert CSV to XLSX, Java Servlet Code Example, POI, OpenCSV, Apache Common library, Apache IO

All Parts:
Part 1 - Create HTML Form Required to Support Servlet Code
Part 2-  Identify JAR files required - Write Servlet Side Code
Part 3-  Complete Servlet Code to convert CSV to XLS / XLSX in Java
Part 4-  Configure Server to support servlet - Download source files

No comments:

Post a Comment