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.
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:
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 java.io.*;
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 org.apache.poi.ss.usermodel.*;
import au.com.bytecode.opencsv.CSVReader; //used to read user uploaded CSV file.
import org.apache.commons.io.FilenameUtils; //read uploaded file
import java.io.InputStreamReader;
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 */
response.setContentType("application/vnd.ms-excel");
}
if (outputtype.equals("2")) {
/* Set response type for XLSX format */
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
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) {
lnNum++;
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)
cell.setCellValue((Double)obj);
else
cell.setCellValue((String)obj);
}
}
/* Write response to output stream */
if (outputtype.equals("1")) {
new_workbook.write(out);
}
else {
new_workbook_xlsx.write(out);
}
}
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 Csv2XlsServlet.java
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
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