wood burning stoves 2.0*
The moose likes Other Open Source Projects and the fly likes Java Code to export data to Excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Java Code to export data to Excel" Watch "Java Code to export data to Excel" New topic
Author

Java Code to export data to Excel

Rajesh Balu
Ranch Hand

Joined: Nov 07, 2011
Posts: 55
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

Joined: Dec 16, 2010
Posts: 835
    
  13

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.


"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do." -- Ted Nelson
Stefan Evans
Bartender

Joined: Jul 06, 2005
Posts: 1016
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

Joined: Nov 07, 2011
Posts: 55
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Nov 07, 2011
Posts: 55


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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Nov 07, 2011
Posts: 55


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



Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60782
    
  65

No. Rather than guessing, why not try a search?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Rajesh Balu
Ranch Hand

Joined: Nov 07, 2011
Posts: 55
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

Joined: Oct 04, 2011
Posts: 14
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.
 
Don't get me started about those stupid light bulbs.
 
subject: Java Code to export data to Excel
 
Similar Threads
merge first row of excel sheet by javascript
Oracle 9i to Excel Sheet
exporting to excel with specif format
Write Data to Excel
How to export data in excel