Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Java Code to export data to Excel

 
Rajesh Balu
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,


I want to export the HTML table data to Excel. I searched in the internet and got some sample codes. But those codes needs some external package to be imported.

Could someone please provide me the code to export the data to excel without importing any external packages?



Thanks,
Rajesh.B
 
J. Kevin Robbins
Bartender
Posts: 1744
19
Chrome Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want the exported file to be a true xls file, it can't be done without using something like Apache POI. However, you can export the table as a tab delimited file which Excel will easily import but you can't do anything fancy like resize the columns or apply formatting.
 
Stefan Evans
Bartender
Posts: 1691
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alternatively you can just produce an HTML table, and open it in Excel.
Excel will convert that into a spreadsheet quite easily.

 
Rajesh Balu
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,


Could you please tell me how to open the HTML table directly in Excel?

I think this would help me.


Thanks,
Rajesh.B
 
Paul Clapham
Sheriff
Pie
Posts: 20967
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rajesh Balu wrote:Could you please tell me how to open the HTML table directly in Excel?


Sure... open Excel, click on File, then Open, then navigate to the folder where you put the HTML document and select it.
 
Rajesh Balu
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Thanks for your prompt response.


But it is the dynamic output which will vary based on the user input. So every time do we need to save the HTML file and needs to open through Excel?



Thanks,
Rajesh.B
 
Paul Clapham
Sheriff
Pie
Posts: 20967
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could require the user to do that. Or you could be a little nicer to the user and send the response with a content type which indicates it's Excel, rather than using the standard "text/html" content type.
 
Rajesh Balu
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Is it correct to set the content type as "text/excel"?



 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64715
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No. Rather than guessing, why not try a search?
 
Rajesh Balu
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Below is my code using Struts display tag.

<display:table id="data" name="searchFormSR.results" pagesize="10" export="true">
<display:column property="results" title="ID" sortable="true" media="html" group="1" />
<display:setProperty name="export.excel.filename" value="RequisitionDetails.xls"/>
</display:table>

Here "results" is the list.

I am getting the below error.

Caused by: Exception: [.LookupUtil] Error looking up property "results" in object type "java.lang.String". Cause: Unknown property 'results'
at org.displaytag.util.LookupUtil.getBeanProperty(LookupUtil.java:141)
at org.displaytag.model.Column.getValue(Column.java:124)
at org.displaytag.model.Column.createChoppedAndLinkedValue(Column.java:201)
at org.displaytag.model.Column.initialize(Column.java:178)
at org.displaytag.render.TableWriterTemplate.writeTableBody(TableWriterTemplate.java:313)
at org.displaytag.render.TableWriterTemplate.writeTable(TableWriterTemplate.java:138)
at org.displaytag.render.HtmlTableWriter.writeTable(HtmlTableWriter.java:734)
at org.displaytag.tags.TableTag.writeHTMLData(TableTag.java:1647)
at org.displaytag.tags.TableTag.doEndTag(TableTag.java:1300)
at com.ibm._jsp._RCStatus._jspService(_RCStatus.java:277)
... 41 more
Caused by: java.lang.NoSuchMethodException: Unknown property 'results'
at org.apache.commons.beanutils.PropertyUtilsBean.getSimpleProperty(PropertyUtilsBean.java:1122)
at org.apache.commons.beanutils.PropertyUtils.getSimpleProperty(PropertyUtils.java:408)
at org.displaytag.util.LookupUtil.getProperty(LookupUtil.java:271)
at org.displaytag.util.LookupUtil.getBeanProperty(LookupUtil.java:129
 
Chandan Sarma
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ,
For your help i am attaching the program for read data from excel and store the data in database. i am using the ms-sql . try with this program.



import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.regex.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Insert {

public static void main(String[] args) {
try
{
String fileName = "C:\\Book2.xls";
Vector dataHolder = read(fileName);
saveToDatabase(dataHolder);
}
catch(Exception e)
{
System.out.println(e);
}
}

public static Vector read(String fileName) {
Vector cellVectorHolder = new Vector();
try {
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while (rowIter.hasNext()) {
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector = new Vector();
while (cellIter.hasNext()) {
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
} catch (Exception e) {
e.printStackTrace();
}
return cellVectorHolder;
}

private static void saveToDatabase(Vector dataHolder) {
String username = "";
String password = "";
for (int i = 0; i < dataHolder.size(); i++) {
Vector cellStoreVector = (Vector) dataHolder.elementAt(i);
System.out.println("the size of the vector"+cellStoreVector);
for (int j = 0; j < cellStoreVector.size(); j++) {
HSSFCell myCell = (HSSFCell) cellStoreVector.elementAt(j);
String st = myCell.toString();
System.out.println("the size in string"+st);
username = st.substring(0);
password = st.substring(0);
}
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
Statement stat = con.createStatement();
int k = stat.executeUpdate("insert into login(username,password) value('" + username + "','" + password + "')");
System.out.println("Data is inserted");
stat.close();
con.close();
} catch (Exception e) {
System.out.println("the Excerption"+e);
}
}
}
}

You need to add java POI jar in your library.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic