• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • paul wheaton
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
  • Himai Minh
Bartenders:

Java Code to export data to Excel

 
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 1845
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sheriff
Posts: 28407
101
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 28407
101
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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



 
Sheriff
Posts: 67754
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic