Issue
I have created one servlet code. The main goal is to write data from a database to a csv file. In my code, the csv file gets downloaded successfully, but the file remains empty. The project contains javacsv.jar file. I don't have any clue why that is. Please shed some light on how I can achieve this. For a summary please read the points below:
- Access data from database table.
- Then write that data into the csv file with table format like < td >data here< / td> . [I want this format because I can format that data as per my requirements.]
- Please shed some light guys peace :) Refer this Servlet code below:
public class excelServletFile extends HttpServlet {
String name = "";
String email = "";
String eid = "Username";
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
try {
String fileName = eid + "Data.csv";
ServletContext context = getServletContext();
String mimeType = context.getMimeType(fileName);
if (mimeType == null) {
mimeType = "application/octet-stream";
}
response.setContentType(mimeType);
String headerKey = "Content-Disposition";
String headerValue = String.format("attachment; filename=\"%s\"", fileName);
response.setHeader(headerKey, headerValue);
OutputStream outStream = response.getOutputStream();
ConnectionClass cn = new ConnectionClass();
Connection con = cn.connectDb();
PreparedStatement ps;
ResultSet rs;
Charset cs = Charset.forName("UTF-8");
Writer writer = new PrintWriter(System.out);
writer.flush();
writer.append("NAME");
writer.append("EMAIL");
ps = con.prepareStatement("select name,email from user");
rs = ps.executeQuery();
while (rs.next()) {
name = rs.getString("name");
email = rs.getString("email");
PrintWriter out = response.getWriter();
out.println(name);
out.println(email);
writer.append(name);
writer.append(email);
}
writer.close();
} catch (Exception e) {
System.out.println(e);
}
}
Solution
package servletProject;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Arrays;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/govinds")
public class CSVServlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
processRequest(req, resp);
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
FileWriter writer = null;
ResultSet rs = null;
Connection con = null;
PreparedStatement ps = null;
try {
String fileName = "D:/Data.csv";
System.out.println(fileName);
ConnectionClass cn = new ConnectionClass();
con = cn.connectDb();
System.out.println("fileName" + fileName);
writer = new FileWriter(fileName);
// Write the CSV file header
CSVUtils.writeLine(writer, Arrays.asList("NAME", "email"));
ps = con.prepareStatement("select firstName,email from employees");
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("firstName"));
CSVUtils.writeLine(writer, Arrays.asList(rs.getString("firstName"), rs.getString("email")));
}
writer.flush();
writer.close();
download(request, response);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
con.close();
} catch (Exception e) {
}
}
}
public void download(HttpServletRequest request, HttpServletResponse response) throws IOException {
String filePath = "D:/Data.csv";
File downloadFile = new File(filePath);
FileInputStream inStream = new FileInputStream(downloadFile);
// if you want to use a relative path to context root:
String relativePath = getServletContext().getRealPath("");
System.out.println("relativePath = " + relativePath);
// obtains ServletContext
ServletContext context = getServletContext();
// gets MIME type of the file
String mimeType = context.getMimeType(filePath);
if (mimeType == null) {
// set to binary type if MIME mapping not found
mimeType = "application/octet-stream";
}
System.out.println("MIME type: " + mimeType);
// modifies response
response.setContentType(mimeType);
response.setContentLength((int) downloadFile.length());
// forces download
String headerKey = "Content-Disposition";
String headerValue = String.format("attachment; filename=\"%s\"", downloadFile.getName() + ".csv");
response.setHeader(headerKey, headerValue);
// obtains response's output stream
OutputStream outStream = response.getOutputStream();
byte[] buffer = new byte[4096];
int bytesRead = -1;
while ((bytesRead = inStream.read(buffer)) != -1) {
outStream.write(buffer, 0, bytesRead);
}
inStream.close();
outStream.close();
}
}
// DB connection File
public class ConnectionClass {
public Connection connectDb() {
Connection con=null;
try {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels","root","root");
}catch (Exception e) {
con=null;
}
if(con!=null)
System.out.println("connected");
else
System.out.println("not connected");
return con;
}
}
//CSVUtil Files
package servletProject;
import java.io.IOException;
import java.io.Writer;
import java.util.List;
public class CSVUtils {
private static final char DEFAULT_SEPARATOR = ',';
public static void writeLine(Writer w, List<String> values) throws IOException {
writeLine(w, values, DEFAULT_SEPARATOR, ' ');
}
public static void writeLine(Writer w, List<String> values, char separators) throws IOException {
writeLine(w, values, separators, ' ');
}
// https://tools.ietf.org/html/rfc4180
private static String followCVSformat(String value) {
String result = value;
if (result.contains("\"")) {
result = result.replace("\"", "\"\"");
}
return result;
}
public static void writeLine(Writer w, List<String> values, char separators, char customQuote) throws IOException {
boolean first = true;
// default customQuote is empty
if (separators == ' ') {
separators = DEFAULT_SEPARATOR;
}
StringBuilder sb = new StringBuilder();
for (String value : values) {
if (!first) {
sb.append(separators);
}
if (customQuote == ' ') {
sb.append(followCVSformat(value));
} else {
sb.append(customQuote).append(followCVSformat(value)).append(customQuote);
}
first = false;
}
sb.append("\n");
w.append(sb.toString());
}
}
Answered By - Govind Sharma
