convert excel

[phani kon]

I have a very large excel file(3,245kb), so I can't open from java. It is giving out of memory error. So how would I do it. Do I have to convert into another format. If I convert to another format. Is java can read that format?

Please let me know the solution?

Thanks a lot

[David O'Meara]

It depends on what you're trying to do. Do you need to understand the data?
You could always convert to CSV, as that is just text, but again we come back to what you need to accomplish

[Ulf Dittmer]

(Continued from here.)

Some links regarding the OpenOffice Java API can be found in the http://faq.javaranch.com/java/AccessingFileFormats page.

How is it possible if I open same excel into (because of the memory issue) htm and data should be added from the database on the htm ? Later I convert into excel inside the code to generate corresponding graphs or charts and open again as a htm page.

I'm not sure what you're proposing to do. Excel files are not HTML, and can't meaningfully be converted into HTML without losing a lot of features. As David says, it depends on what exactly you need to do, and I don't have a good idea what that is.

[wonder qi]

java -Xmx xxxm -Xms xxxm adjust memory

[Ben Souther]

"wonder qi",
Please check your private messages.
-Ben

[phani kon]

Here is my requirment. I am trying to open and read the excel file(3.245mb) from the browser, but I couldn't open because of some memory issue.The excel file contains charts and graphs also.

What are the other options to open and read the excel file?

I need to generate charts and graphs also. It is really very complicated issue, I don't understand, how to resolve this isssue.

I need some to help me please

thanks

[Ulf Dittmer]

It seems we're going around in circles. Have you tried jExcelApi? Have you tried the OpenOffice java API? Have you tried increasing the available memory? All these might solve the issue.

[phani kon]

The file is 3.245m.After adding the data it can be more than this 3.245m.I have increased the jvm like below. But It didn't help.

1.
-showsplash
com.genuitec.myeclipse.product
--launcher.XXMaxPermSize
256m
-vmargs
-Xms6m//modified
-Xmx80m//modified
-Dosgi.splashLocation=C:\eclipse\eclipse\MyEclipseSplash.bmp
-Duser.language=en
-XX p ermSize=128M
-XX MaxPermSize=256M

2.Suppose If I remove charts and graphs form my existing excel file inorder to decrease the memory size. How can reproduce using java code on excel again. Also I don't see any thing to build charts and graphs uisng JEXCEL.

3.Is open office is software tool to convert xsl to anyother format.I need to dowonload the software or any jar will be there. Suppose If I convert Using OpenOffice to.sxc format. How would I read from java.Do you have any examples. The site you specified does not have any examples using java.

Please let me know. If I need to try anything else.

Any ideas or suggestions would be really helpful

thanks
[ March 19, 2008: Message edited by: lakshmi manepally ]

[Ulf Dittmer]

80MB for the max heap size isn't much, especially if these are the options for Eclipse (and it looks to me like they are), because in that case a big chunk of the memory will be used up by Eclipse. Try running the code as a standalone program -in other words, outside of Eclipse- and allocate a max of 128MB to it. I'd be surprised if that wasn't enough memory to work with.

[phani kon]

What about open office? Could please anyone post some examples of open office

[Ulf Dittmer]

The page I mentioned before contains relevant links, e.g. one called "this article" that contains example Java code. Now, that article talks about word processing documents, but the general approach should be similar for spreadsheets. (That article also shows that the API has a steep learning curve; I wouldn't use it unless I had exhaustively researched other approaches.)

The page also links to the OO developer site, and to the JODConverter project which may be helpful to you.

[phani kon]

Thanks for your information. I have one question,Using Jexcel how do we update the columns in the excel from the database.

I mean If I want to update say column name "2008 q1" values.That value should directly come from database (resultset).

Can you post me one similar example

Thanks
[ March 19, 2008: Message edited by: lakshmi manepally ]

[Ulf Dittmer]

Didn't we have this discussion already recently? The tutorial shows how to read, modify and write files - what else are you looking for?

[phani kon]

It doesn't show anything like update data from the ResultSet. I am looking like below.

How do I connect using Jexcel to the resultset.

rs resultset = st.excutequery("select * form XXXX);

I hope you understand my question.

Thanks a lot.

[David O'Meara]

What does your code look like? (it's clear you didn't copy and paste that line)
Maybe there is an error, and you're gobbling it up.

[phani kon]

I want to read the file and update values through resultset in the excel using JExcel. Can you tell me,How can I do it.here the sample code

    
public void performTask(
javax.servlet.http.HttpServletRequest request,
javax.servlet.http.HttpServletResponse response) {
 
File fp = new File("D:\\excel\\ATREstatsQuarterly2007.sxc");
  try {
      Workbook wb = Workbook.getWorkbook(fp);
      WritableWorkbook copy = Workbook.createWorkbook(new File("d:\\excel\\output.xls"), wb); 
                 ht = rs.getdataquater(yr,month,catgory)//Here I am 
getting the data form the database
              enumeration e = ht.element();
               while(e.hasmoreelements)
               { int fieldCnt;
for (fieldCnt = 1; fieldCnt <= colCnt; fieldCnt++) {
if (fieldCnt > 1) {
out.print("\t");
}
String colName = rsm.getColumnName(fieldCnt);

Object obj = rs.getObject(fieldCnt);
if (obj != null) {
if(colName.equalsIgnoreCase("OFF_HWY_PCT")){
double val = rs.getDouble(fieldCnt);
out.print(ndf.format(val/100.0));
}else{
out.print(obj.toString());
}
} else {
out.print("");
}
}
out.print("\n");
}                       }
 
      WritableSheet sheet2 = copy.getSheet(1); 
      WritableCell cell = sheet2.getWritableCell(1, 2); 
 
      if (cell.getType() == CellType.LABEL) 
      { 
        Label l = (Label) cell; 
        l.setString("modified cell"); 
        
         
        System.out.println(" ssss"  ); 
      } 
 

             int columns = sheet.getColumns();
      int rows = sheet.getRows();
 
      String data;
      for(int col = 0;col < columns;col++) {
          for(int row = 0;row < rows;row++) {
              data = sheet.getCell(col, row).getContents();
              int c = sheet.getCell(col, row).getColumn();
              int r = sheet.getCell(col, row).getRow();
              System.out.println("col:" +c);
              System.out.println("row:" +r);
              System.out.println("contents:"+data);
              // Your code here
          }
      }
      Label label = new Label(0, 2, "New label record"); 
      sheet2.addCell(label); 
      copy.write(); 
      copy.close(); 
}
cath(exception e)
{
e.printstacktrace();
}

[Ulf Dittmer]

I'm not following. This code doesn't contain anything JDBC-related, so how are you using a database? Do you intend to get values from a database, and store those in an XLS file, or the other way around?

(Just in case you're unclear about that: Excel files are not databases, so you can't use JDBC to read from them or write to them. That's why there are APIs like POI and jExcelAPI.)

[phani kon]

I know that, here is the more clear code

    
 

 
public void performTask(
javax.servlet.http.HttpServletRequest request,
javax.servlet.http.HttpServletResponse response) {
Hashtable ht = null;
String category  = "";
  int  year=0;
   int quarter=0;
  String quaterly ;
try
{
 category = request.getParameter("category");
year  = Integer.parseInt(request.getParameter("year"));
quarter = Integer.parseInt(request.getParameter("quaterly"));  
quaterly= request.getParameter("quaterly");
  

    
    }
    catch(Exception e)
    {
    System.out.println("<B> Not Valid Parameters");
    }
    
    
    int months [] = null;
        int months1 [] = {1,2,3};
        int months2 [] = {4,5,6};
        int months3 [] = {7,8,9};
        int months4 [] = {10,11,12};
    
    
try
{


    DataAccess wda = new DataAccess(); //(Here I am writing all sql related connections and result and statements)    
    
    if (quarter == 1)
{
months = months1;  
ht = wda.getTheQuater(category,months,year); //gettheQuater method in wda is used to get data from the database)
 }
if (quarter == 2)
 {
 ht = wda.getTheQuater(category,months,year); 
  }
if (quarter == 3)
 {
 months = months3;
 ht = ht = wda.getTheQuater(category,months,year);  } 
 if (quarter == 4)
{
 months = months4;       
 ht = wda.getTheQuater(category,months,year); 
 }
  
 
      response.setContentType("application/vnd.ms-excel");
PrintWriter out = new PrintWriter(response.getWriter());
response.setHeader("Content-disposition","inline; filename=" +"exportfilename.xls"+ "");
 

File fp = new File("c:\\excel\\AtrestatsQuaterly.xls");
Workbook wb = Workbook.getWorkbook(fp);
WritableWorkbook copy = Workbook.createWorkbook(new File("c:\\excel\\output.xls"), wb); 
WritableSheet sheet2 = copy.getSheet(1); 
Sheet wb1 = wb.getSheet(1);
System.out.println("name" +wb1.getCell(23, 1).getContents());
String data;        
 
int columns = wb1.getRows();
int rows  = wb1.getColumns();
      for(int col = 0;col < columns;col++) {
        for(int row = 0;row < rows;row++) {
            data = sheet2.getCell(col, row).getContents();
            
            
           // System.out.println("Reading values:   " +data);
            System.out.println("values are [" + col + "," + row
+ "] = " + data);
         
            
            // Your code here
        } }
 
// Specify default name for MS Excel document to save as 
/*String expFileName = "exportfilename.xls";
response.reset();
response.setHeader("Content-type","application/xls");
response.setHeader("Content-disposition","inline; filename=" +expFileName+ "");*/
 
///here I can export the data form the  database to excel but I want to modify the excel
     
   Enumeration en = ht.elements(); 
   Enumeration e = ht.keys();
  
  
   int colCnt = ht.size();   
   int hdrCnt;
   copy.write(); 
   copy.close();
   while (en.hasMoreElements()&& e.hasMoreElements()) {
for (hdrCnt = 1; hdrCnt <= colCnt; hdrCnt++) {

String key = (String) (e.nextElement());
//out.print(key);
Object obj = en.nextElement();
if (obj != null) {
out.print("\t");
out.print(obj.toString());
out.print("\n");
System.out.println("OBJ:" + obj.toString());
} else {
out.print("");
}

}
 
}
   
   
}

 
catch(Exception e)
{
e.printStackTrace();

}



 
}
}