SXSSF for Writing huge data on exisitng excel

[sandy27 <ms...@gmail.com>]

Hi ALL,

I have been using apache poi for quit sometime . i have found that for
writing large excel files apache asks the developer to use SXSSF .
I have tried out this but the problem is that using SXSSF it creates a new
file entirly.

It allowing to read an existing xlsx and write data on that.

Since the data that needs to be appended in an existing sheet is huge
because of which i am not able to use XSSF (because it takes a lot of
memory).

Could anyone please help me out in this as what can be done here .

Thanks



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSF-for-Writing-huge-data-on-exisitng-excel-tp5712656.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: SXSSF for Writing huge data on exisitng excel

[Mark Beardsley <ma...@tiscali.co.uk>]

As far as I am aware - and do bear in mind that I have no experience with
SXSSF - it is not possible to read a workbook using SXSSF. This api was
created to be used as a tool for writing large files only and that is why I
suspect you are not getting the row numbers from the sheet when you ask for
them.

If you want to undertake read and then write operations on the same file,
the obvious answer is to use the 'normal' usermodel API in the XSSF stream.
The problem with that is, as I am sure you are aware, that it imposes quite
a large demand upon your systems memory. Having said that, you may have an
option. It is possible to use the SAX parser and a custom handler to parse
the xml markup with what is known as the event approach - it is detailed
here http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api - and it is
quite easy to see that you could use this approach to get at the number of
the final row on the sheet. The handler ought to be very simple as you are
only interested in the value of the r attribute of the row element (that
attribute records the number of the row as a String). Next, you could
re-open the workbook using the SXSSF approach and write data into the rows
that follow. Both of these are low memory approaches to reading the file on
the one hand and writing to it on the other.

Off of the top of my head, I cannot see another way around the problem you
are facing as I am pretty certain that SXSSF cannot be used to read data
from an existing file. If you wanted to make certain of this limitation, it
might be worthwhile posting a supplementary question to the list asking just
this, something like, 'is it possible to read data from or get information
about a sheet in an existing Excel workbook using the SXSSF API?'



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSF-for-Writing-huge-data-on-exisitng-excel-tp5712656p5712670.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: SXSSF for Writing huge data on exisitng excel

[sandy27 <ms...@gmail.com>]

Hi mark,

thanks for the reply.

Below is the code for reading i have used.

	    	FileInputStream myInput = new
FileInputStream("C:\\Testingforlargexlsx\\sxxf\\temp.xlsx");
        XSSFWorkbook	arbitory_ss = new XSSFWorkbook(myInput);
			 SXSSFWorkbook wb_ss = new SXSSFWorkbook(arbitory_ss,-1);
			  SXSSFSheet sheet = null;
				sheet = (SXSSFSheet) wb_ss.getSheet("sheet1");
				System.err.println(" LOG sheet Merged Regions 
"+sheet.getNumMergedRegions());
				SXSSFRow row1 = (SXSSFRow) sheet.getRow(1);
						System.out.println("SXSSFRow  "+ sheet.getFirstRowNum());
						System.out.println("SXSSFRow  "+ row1);
						Row row12 = (SXSSFRow) sheet.getRow(0);
						System.out.println("Row  "+ sheet.getFirstRowNum());
						System.out.println("Row  "+ row1);


I have run this above mentioned code to get the 1st row in two ways SXSSFRow
and Row but for both the values its showing null.

Is there any separate api for reading rows????



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSF-for-Writing-huge-data-on-exisitng-excel-tp5712656p5712669.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: SXSSF for Writing huge data on exisitng excel

[Mark Beardsley <ma...@tiscali.co.uk>]

Can I ask what you have tried with SXXSF? I have absolutely no experience
with this API but the javadoc does seem to suggest that it is possible to
work with an existing file, for example;

"public SXSSFWorkbook(XSSFWorkbook workbook,
                     int rowAccessWindowSize)

    Constructs an workbook from an existing workbook. "

and there is a method to obtain a sheet from the workbook using either it's
name or index number and I would assume - even though that is VERY dangerous
- that it is possible to get at the index of the last row on the sheet and
then append further rows to it. If that is how you can then proceed, I do
not know but it might be better if you could create a very simple test case
- some code that atempts to op0en an existing workbook using SXSSF and then
append data to a sheet - that illustrates your problem and then post it here
so that others with far more knowledge of this strand of the API than I can
comment.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/SXSSF-for-Writing-huge-data-on-exisitng-excel-tp5712656p5712660.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org