gendhe ranjith kumar

Greenhorn
+ Follow
since Mar 28, 2011
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by gendhe ranjith kumar

hi

i am getting the following exception when trying to open the generated xlsx file in browser.

how to handle this exception.

Thanks & Regards,

Ranjith.
11 years ago
JSP
hi

the provided solution did not solve problem.

it still showing the jsp name.

Thanks & regards

Ranjith.
11 years ago
JSP
hi

i am using the following code to download xlsx file.


<%
String resultXLS="EIS Modules Installed_1008404_4.xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8");
response.setHeader("content-disposition", "inline; filename=\"" + resultXLS + "\"");

File file = new File("/ed2/ora510/eld10comn/html/eisrs/EIS Modules Installed_1008404_4.xlsx");
FileInputStream fileIn = new FileInputStream(file);
OutputStream outExcel = response.getOutputStream();

byte[] outputByte = new byte[8192];
//copy binary contect to output stream
while(fileIn.read(outputByte, 0, 8192) != -1)
{
outExcel.write(outputByte, 0, 8192);
}

fileIn.close();
outExcel.flush();
outExcel.close();

%>

when i click open in IE. the xlsx file name will change to jsp page name.

this will work fine in mozilla and chorme.


Thanks & Regards

Ranjith.
11 years ago
JSP
hi

thanks for your response.

the above code is working fine in case of 2003 excel.

it is fails in excel 2007.

thanks & regards

Ranjith.
11 years ago
JSP
hi

i am using the following code to download xlsx file from server.

<%
String resultXLS="file downloading from jsp.xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8");
response.setHeader("content-disposition", "attachment; filename=\"" + resultXLS + "\"");

File file = new File(filepath);
FileInputStream fileIn = new FileInputStream(file);
OutputStream outExcel = response.getOutputStream();

byte[] outputByte = new byte[8192];
//copy binary contect to output stream
while(fileIn.read(outputByte, 0, 8192) != -1)
{
outExcel.write(outputByte, 0, 8192);
}

fileIn.close();
outExcel.flush();
outExcel.close();

%>



the file generated. but i am getting some error messages.

1."excel found unreadable content. do you want to recover from it".

2."the file cannot opened by Microsoft excel . do you want to search on online for converter to open the file".

any help on this will be appreciated.

Thanks & regards

Ranjith
11 years ago
JSP
hi

i am using jsp to generate excel

if the excel file is large iam getting OutOfMemoryError.

i do not want to increase the heap size.

i want to dump data in excel until i get OutOfMemoryError.

means

if i get error after dumping 30,000 rows. then i want save the file with 29,990 rows.

because of

heap size of jdks are different.


please give solution to this.




11 years ago
JSP
please check the link

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java

there are some reasons to avoid poi.

1. for jdk 1.4 poi supports only xls.

2. poi does not support to generate pivot table.

3.some servers take more memory to generate sheets

steps:


1) create Excel template file (without the data-rows) with aspose api, save to disk
2) open ZipFile, iterate over all file-entries
3) open new ZipOutputStream(servletRequest.getOutputStream())
4) copy all ZipEntries to ZipOutputStream except:
5) when the ZipEntry is for 'xl/worksheets/sheet1.xml', then create new XMLStreamWriter(zipOutputStream)
5a) Write Excel sheet header to XML stream
5b) for each row in the csv, populate XML row (using poi cellReference() class)
5c) write Excel sheet footer to XML stream
6) close xml stream, continue with copying the other ZIP entries


after read the excel file using asposeAPI

and create pivot table for sheet1.


by using this approach we can genarate xlsx file havaing 30,00,000 rows and 140 cols in less than 25 mins.

Thanks & Regards.
Ranjith.
11 years ago


we need to populate an Excel-2007 sheet with the results from a csv (e.g. 40,00,000 rows),

we can open the xlsx file in zip streams(Create xlsx file and rename it to zip and open with zip tool)

then we could use the following algorithm to send this Excel file to a browser:

1) create Excel template file (without the data-rows), save to disk
2) open ZipFile, iterate over all file-entries
3) open new ZipOutputStream(servletRequest.getOutputStream())
4) copy all ZipEntries to ZipOutputStream except:
5) when the ZipEntry is for 'xl/worksheets/sheet1.xml', then create new XMLStreamWriter(zipOutputStream)
5a) Write Excel sheet header to XML stream
5b) for each row in the resultset, populate XML row
5c) write Excel sheet footer to XML stream
6) close xml stream, continue with copying the other ZIP entries

if the csv file contains large data. then we create a temporary sheet.xml in server. later we copy content to the zip file.

by doing this one we are spending extra time to copy from xml to zipfile.


Thanks & Regards
Ranjith.



11 years ago
hi

thanks for response

our requirement is:

we are using the following code to generate excel from csv.

http://svn.apache.org/repos/asf/poi/trunk/src/exam...odel/examples/BigGridDemo.java

in that they are creating a temporary xml file. later they copy data from xml file to zip file.

it takes more time for 30,000,000 rows and 150 columns.

we read the csv file line by line.
to avoid creating of temporary xml file.
after reading every line from csv we call the substitute(...) method
it will overrides contents of zip file.

in case of files

try
{
String filename= "C:\\book1.txt";
boolean append = true;
FileWriter fw = new FileWriter(filename,append);

fw.write("add a line\n");//appends the string to the file
fw.close();
}

catch(IOException ioe)
{
System.err.println("IOException: " + ioe.getMessage());
}

this one is working.

is there any other way to write data to zip file without losing the content.

Thanks & Regards
Ranjith.
11 years ago
hi

i want to write the two string objects into the zip file.
i am using the following code.


this program runs

but it overrides contents of zip file and contains last String Object only .

how to append the data to the zip file?

Thanks & Regards
Ranjith.
11 years ago
thanks for your response

i am working on that.


Thanks & Regards

Ranjith.
11 years ago
thanks for giving response

could please give one simple example on this.

Thanks & Regards

Ranjith.
11 years ago
hi

we are using the following code to generate excel from csv.

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java

in that they are creating a temporary xml file. later they copy data from xml file to zip file.

how to write data to zip with out storing data in temporary xml file.


11 years ago
hi

the above example shows the writing data to zip file reading from dummy-1.txt

how to write data to zip file reading from input like(keyboard).
i saw some example in that they stored data in one temp file.

after that they will copy the data to zip file using copy stream logic.

is there any way to write data from input devices to zip file.

Thanks & Regards
Ranjith.

11 years ago
hi

i am trying to write data to zip file reading from another file

using this code.
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ZippingFileExample {
public static void main(String[] args) {
try {
String source = "dummy-1.txt";
String target = "data-1.zip";

ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(target));
FileInputStream fis = new FileInputStream(source);

// put a new ZipEntry in the ZipOutputStream
zos.putNextEntry(new ZipEntry(source));

int size = 0;
byte[] buffer = new byte[1024];

// read data to the end of the source file and write it to the zip
// output stream.
while ((size = fis.read(buffer, 0, buffer.length)) > 0) {
zos.write(buffer, 0, size);
}

zos.closeEntry();
fis.close();

// Finish zip process
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}


how to write data to zip file reading from input .
without storing data in temp file.

Thanks & regards
Ranjith
11 years ago