Rectangle 27 4

To read a file just instanciate a new XSSFWorkbook by passing a new FileInputStream with the path of the Excel file:

XSSFWorkbook workbook = new XSSFWorkbook(OPCPackage.open(new File("foo.xslx")));

Or with an input stream (takes a little more memory than a file):

XSSFWorkbook workbook = new XSSFWorkbook(myInputStream);

After having a XSSFWorkbook, use it to iterate through all the cell (example).

the Apache poi 3.9 is a library and nor one jar file.how do i use it?

Do you know why it takes a little more memory than a file ?

apache - reading excel file --> xlsx format with java - Stack Overflow

java apache excel apache-poi xlsx
Rectangle 27 2

Your classpath is missing one of the dependencies for Apache POI. Specifically, you are missing the XMLBeans jar. Are you using Maven?

java - apache poi 3.6: Reading an xlsx file - Stack Overflow

java
Rectangle 27 2

Using POI 3.8 and poi-ooxml-3.8, I've had success with something like this (i've not tried older versions):

InputStream is = //Open file, and get inputstream
Workbook workBook = WorkbookFactory.create(is);
int totalSheets = workBook.getNumberOfSheets();
for (int i = 0; i <= totalSheets - 1; i++) {
  Sheet sheet = workBook.getSheetAt(i);
  // Do something with the sheet
}

WorkbookFactory will automatically determine whether the file is the old XLS, or newer XLSX and return the correct version of Workbook. The rest of the code is just a sample of iterating through the sheets contained within.

what to write in the inputstream line,is=?

You need to supply an InputStream that points to the file you are looking to read, and which one you use depends on how you are looking to get at the file. I would probably start with this: docs.oracle.com/javase/6/docs/api/java/io/FileInputStream.html

apache - reading excel file --> xlsx format with java - Stack Overflow

java apache excel apache-poi xlsx
Rectangle 27 4

I posted a comment to vikiiii's answer. I thought I'd expand on it a bit more. His answer is specific to HSSF (.xls) but both the HSSF and XSSF classes descend from the same interface so the code is the same, you just use XSSF instead of HSSF. Seeing as you want to reuse the color I'd recommend using:

XSSFColor bgColor = xssfCell.getCellStyle().getFillBackgroundColorColor();

See here for the Javadoc. Now to set a new cell to that color you can use this.

secondCell.getCellStyle().setFillBackgroundColor(bgColor);

I'd recommend looking at the interfaces that the XSSF and HSSF classes descend from and have a look at making your code be able to handle both xls and xlsx files. As far as I'm aware the only difference is the way you set up the workbook, using WorkbookFactory.

java - Apche POI get cell color when reading xlsx file - Stack Overflo...

java apache-poi
Rectangle 27 12

Here is an example to read a large xls file using sax parser.

public void parseExcel(File file) throws IOException {

        OPCPackage container;
        try {
            container = OPCPackage.open(file.getAbsolutePath());
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(container);
            XSSFReader xssfReader = new XSSFReader(container);
            StylesTable styles = xssfReader.getStylesTable();
            XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
            while (iter.hasNext()) {
                InputStream stream = iter.next();

                processSheet(styles, strings, stream);
                stream.close();
            }
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (OpenXML4JException e) {
            e.printStackTrace();
        }

}

protected void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, SAXException {

        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        try {
            SAXParser saxParser = saxFactory.newSAXParser();
            XMLReader sheetParser = saxParser.getXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, new SheetContentsHandler() {

            @Override
                public void startRow(int rowNum) {
                }
                @Override
                public void endRow() {
                }
                @Override
                public void cell(String cellReference, String formattedValue) {
                }
                @Override
                public void headerFooter(String text, boolean isHeader, String tagName) {

                }

            }, 
            false//means result instead of formula
            );
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
        } catch (ParserConfigurationException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());

Thanks O.C exactly what I was looking for processing over 250k rows. Perfectly works.

Many thanks for the code snippet up there. Apache POI should post in their documentation an example as the one above to advertise those APIs more readily.

@O.C Thanks a ton!! Could you please tell how to consider blank cells in excel using the above code?

Is there a way using an iterator-based / row-based approach? I would like to wrap an iterator around it with hasNext() and next() methods so that the caller has more influence. In this event-based approach I have no control over the progress, because I have to fetch all events until no events are there.

but this is an xlsx parser not xls parser :(

java - Error While Reading Large Excel Files (xlsx) Via Apache POI - S...

java apache-poi out-of-memory xlsx
Rectangle 27 12

Here is an example to read a large xls file using sax parser.

public void parseExcel(File file) throws IOException {

        OPCPackage container;
        try {
            container = OPCPackage.open(file.getAbsolutePath());
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(container);
            XSSFReader xssfReader = new XSSFReader(container);
            StylesTable styles = xssfReader.getStylesTable();
            XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
            while (iter.hasNext()) {
                InputStream stream = iter.next();

                processSheet(styles, strings, stream);
                stream.close();
            }
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (OpenXML4JException e) {
            e.printStackTrace();
        }

}

protected void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, SAXException {

        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        try {
            SAXParser saxParser = saxFactory.newSAXParser();
            XMLReader sheetParser = saxParser.getXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, new SheetContentsHandler() {

            @Override
                public void startRow(int rowNum) {
                }
                @Override
                public void endRow() {
                }
                @Override
                public void cell(String cellReference, String formattedValue) {
                }
                @Override
                public void headerFooter(String text, boolean isHeader, String tagName) {

                }

            }, 
            false//means result instead of formula
            );
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
        } catch (ParserConfigurationException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());

Thanks O.C exactly what I was looking for processing over 250k rows. Perfectly works.

Many thanks for the code snippet up there. Apache POI should post in their documentation an example as the one above to advertise those APIs more readily.

@O.C Thanks a ton!! Could you please tell how to consider blank cells in excel using the above code?

Is there a way using an iterator-based / row-based approach? I would like to wrap an iterator around it with hasNext() and next() methods so that the caller has more influence. In this event-based approach I have no control over the progress, because I have to fetch all events until no events are there.

but this is an xlsx parser not xls parser :(

java - Error While Reading Large Excel Files (xlsx) Via Apache POI - S...

java apache-poi out-of-memory xlsx
Rectangle 27 2

You can use this code to get the cell color.

cell.getCellStyle().getFillBackgroundColor();
HSSFColor.getIndexHash().get(myCell.getCellStyle().getFillBackgroundColor())

thank you but i have all ready used it in my code that you can see. but it is not working.

i think it will be returning you a number,right?

how can i apply that number on new cell?

i am processing xlsx and using XSSF so how can i use HSSFColor

java - Apche POI get cell color when reading xlsx file - Stack Overflo...

java apache-poi
Rectangle 27 2

Since there is a limitation in 16 mb per document in mongodb (http://docs.mongodb.org/master/reference/limits/) you might want to use GridFS to write raw data. Here is an example how to do it:

[UPDATE] If you have actually many small documents in XLSX file (like document per row) then you can use batch-like way inserting documents by blocks (see How to insert multiple documents at once in MongoDB through Java as an example).

Thanks rsutormin. i tried this one ,but i want to read greater than 100mb file in every two seconds in within two seconds i have to parse it and save to mongoDb gridfs I used gridfs i want to do it in minimum Timespan like two seconds. My whole data is in petabyte..but i take this sheets from URl in rest.

Another question is if the speed is so critical then why don't you deal with CSV-files instead which could be imported into MongoDB with help of standard tools (stackoverflow.com/questions/4686500/)?

Read the 100Mb xlsx file parse it in java and store it into mongodb? F...

java mongodb
Rectangle 27 2

Since there is a limitation in 16 mb per document in mongodb (http://docs.mongodb.org/master/reference/limits/) you might want to use GridFS to write raw data. Here is an example how to do it:

[UPDATE] If you have actually many small documents in XLSX file (like document per row) then you can use batch-like way inserting documents by blocks (see How to insert multiple documents at once in MongoDB through Java as an example).

Thanks rsutormin. i tried this one ,but i want to read greater than 100mb file in every two seconds in within two seconds i have to parse it and save to mongoDb gridfs I used gridfs i want to do it in minimum Timespan like two seconds. My whole data is in petabyte..but i take this sheets from URl in rest.

Another question is if the speed is so critical then why don't you deal with CSV-files instead which could be imported into MongoDB with help of standard tools (stackoverflow.com/questions/4686500/)?

Read the 100Mb xlsx file parse it in java and store it into mongodb? F...

java mongodb
Rectangle 27 11

(sigh..) you don't want to know how many hours i've spent trying to figure this out and it was literally as simple as that..

Java servlet trying to output a xlsx file, but keep getting octet-stre...

java servlets
Rectangle 27 1

Try running without stax-api. Using stax-api.jar is not helpful in poi > 3.9 and may cause conflict if you are using Java 6 or above. As per Apache poi components page

The OOXML jars require a stax implementation, but now that Apache POI requires Java 6, that dependency is provided by the JRE and no additional stax jars are required. The OOXML jars used to require DOM4J, but the code has now been changed to use JAXP and no additional dom4j jars are required.

i tried the same but not working getting same error

As far as I see there seems no problem with the jars. you should probably check your build configuration and try. Also use xmlbeans-2.6.0.jar for this version.

How to read xlsx file in Apache POI in Android/Java - Stack Overflow

java android android-studio apache-poi xlsx
Rectangle 27 1

@Gus, I had the same problem. I had to read a 13MB XLSX and ran out of heap with conventional POI. I had to implement XSSF+SAX API to read the file. Although very difficult to understand at first, I'm able to read my XLSX file easily with it (and very quickly, too).

The Apache guys give an example of it's usage in the link. In my case I copied the code and adapted to my needs.

excel - Are there any languages that can be integrated with java that ...

java excel apache-poi xlsx
Rectangle 27 3

If you're using Java 7, there's a really easy way to do this: Files.copy.

cross platform - How to create an exact copy of .xls or .xlsx file usi...

java cross-platform xls xlsx
Rectangle 27 1

When you create a new file you will not need a file first, you can just start with a new Workbook:

Workbook wb = new XSSFWorkbook();

and then use the API to populate it. At the end you can write it to a new file via

try (OutputStream stream = new FileOutputStream(file)) {
    wb.write(stream);
}

excel - using Java temporary file for XLSX Apache poi - Stack Overflow

java excel apache-poi xlsx temporary-files
Rectangle 27 1

Apache POI 3.5 have added support to all the OOXML (docx, xlsx, etc.)

See the XSSF sub project

That's mean it is possible to only extract the XSSF/HSSF part of Apache POI?

java - How to read xlsx file in an android application? - Stack Overfl...

java android excel apache-poi xlsx
Rectangle 27 1

Try Google Doc online to browse if u app connected with internet ...

Sorry, I think my question is not clear enough. What I am going to do is extracting the content of the xlsx file, not xlsx editing.

Google Doc Support Show too and easy and Quick Done it ;)

nope... I am going to extract the data inside the xlsx files and process them. Not for showing them out or editing.

java - How to read xlsx file in an android application? - Stack Overfl...

java android excel apache-poi xlsx
Rectangle 27 17

I'm using this code to export excel (xlsx) file ( Apache Poi ) in jersey as an attachement.

@GET
@Path("/{id}/contributions/excel")
@Produces("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
public Response exportExcel(@PathParam("id") Long id)  throws Exception  {

    Resource resource = new ClassPathResource("/xls/template.xlsx");

    final InputStream inp = resource.getInputStream();
    final Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);

    Row row = CellUtil.getRow(7, sheet);
    Cell cell = CellUtil.getCell(row, 0);
    cell.setCellValue("TITRE TEST");

    [...]

    StreamingOutput stream = new StreamingOutput() {
        public void write(OutputStream output) throws IOException, WebApplicationException {
            try {
                wb.write(output);
            } catch (Exception e) {
                throw new WebApplicationException(e);
            }
        }
    };


    return Response.ok(stream).header("content-disposition","attachment; filename = export.xlsx").build();

}

java - Input and Output binary streams using JERSEY? - Stack Overflow

java jersey jax-rs
Rectangle 27 4

You're missing the stax API jar

If you look at the POI Components page you'll see that the ooxml-schemas jar depends on a STAX API jar (most typically provided by stax-api-1.0.1.jar)

Look in your POI binary download, and in the ooxml-lib directory you'll see the jar you need. Alternately, if you use Maven, it'll download the dependency for you

Thanks a ton man..it's working now.:)

java - Write a file in xlsx format using Apache POI - Stack Overflow

java excel apache-poi xlsx
Rectangle 27 6

com.displaytag.export.views
myExcel2007ExportView.java

Step 3 : Copy and paste following code in that file

package com.displaytag.export.views;


import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import javax.servlet.jsp.JspException;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.displaytag.export.BinaryExportView;
import org.displaytag.model.Column;
import org.displaytag.model.ColumnIterator;
import org.displaytag.model.HeaderCell;
import org.displaytag.model.RowIterator;
import org.displaytag.model.TableModel;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.lowagie.text.BadElementException;

public class myExcel2007ExportView implements BinaryExportView{

 //private static Log log = LogFactory.getLog(myExcel2007ExportView.class);
private TableModel model;
private boolean exportFull;
private boolean header;
private boolean decorated;
private XSSFWorkbook workbook;
private XSSFSheet sheet;
private int rowCount=0;
private CellStyle normalstyle=null;
private CellStyle headerstyle=null;
private CellStyle captionstyle=null;

private XSSFFont defaultFont;
private XSSFFont headerFont;
private XSSFFont captionFont;

public String getMimeType() {
     return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}

public void setParameters(TableModel tableModel, boolean exportFullList, boolean includeHeader,
       boolean decorateValues)
{
   this.model = tableModel;
   this.exportFull = true;
   this.header = includeHeader;
   this.decorated = decorateValues;
}

public boolean outputPage() {
    return false;
}

protected void initTable() throws BadElementException
{
    //Blank workbook
    workbook = new XSSFWorkbook();

    //Create a blank sheet
    sheet = workbook.createSheet("Server Management System");


    defaultFont= workbook.createFont();
    defaultFont.setFontHeightInPoints((short)10);
    defaultFont.setFontName("Arial");
    defaultFont.setColor(IndexedColors.BLACK.getIndex());
    defaultFont.setBold(false);
    defaultFont.setItalic(false);

    headerFont= workbook.createFont();
    headerFont.setFontHeightInPoints((short)10);
    headerFont.setFontName("Arial");
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    headerFont.setBold(true);
    headerFont.setItalic(false);

    captionFont= workbook.createFont();
    captionFont.setFontHeightInPoints((short)15);
    captionFont.setFontName("Arial");
    captionFont.setColor(IndexedColors.BLACK.getIndex());
    captionFont.setBold(true);
    captionFont.setItalic(false);


    normalstyle= workbook.createCellStyle(); 
    normalstyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
    normalstyle.setFillPattern(CellStyle.BIG_SPOTS);
    normalstyle.setAlignment(CellStyle.ALIGN_CENTER);
    normalstyle.setFont(defaultFont);

    headerstyle= workbook.createCellStyle(); 
    headerstyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerstyle.setFillPattern(CellStyle.BIG_SPOTS);
    headerstyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerstyle.setFont(headerFont);

    captionstyle= workbook.createCellStyle(); 
    captionstyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    captionstyle.setFillPattern(CellStyle.BIG_SPOTS);
    captionstyle.setAlignment(CellStyle.ALIGN_CENTER);
    captionstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    captionstyle.setFont(captionFont);

}

protected void generateXLSXTable() throws JspException, BadElementException
{
    //adding caption
    if(this.model.getCaption()!=null && this.model.getCaption()!="")
    {
     Row blankrow = sheet.createRow(rowCount++);
     Row row = sheet.createRow(rowCount++);

     row.setHeight((short)5000);
     int columnCount=0;
     Cell captionCell = row.createCell(columnCount++);
     captionCell.setCellValue(this.model.getCaption());
     captionCell.setCellStyle(captionstyle);
     sheet.addMergedRegion(new CellRangeAddress(
             rowCount-1, //first row (0-based)
             rowCount-1, //last row  (0-based)
             columnCount-1, //first column (0-based)
             columnCount+5  //last column  (0-based)
     ));
     Row blankrow2 = sheet.createRow(rowCount++);
    }

    if (this.header)
    {
        generateHeaders();
    }

    generateRows();

    //adding footer
    if(this.model.getFooter()!=null && this.model.getFooter()!="")
    {
     Row blankrow = sheet.createRow(rowCount++);
     Row blankrow2 = sheet.createRow(rowCount++);
     Row row = sheet.createRow(rowCount++);

     row.setHeight((short)1000);
     int columnCount=0;
     Cell captionCell = row.createCell(columnCount++);
     captionCell.setCellValue(this.model.getFooter());
     captionCell.setCellStyle(captionstyle);
     sheet.addMergedRegion(new CellRangeAddress(
             rowCount-1, //first row (0-based)
             rowCount-1, //last row  (0-based)
             columnCount-1, //first column (0-based)
             columnCount+5  //last column  (0-based)
     ));
    }
}

protected void generateHeaders() throws BadElementException
{
    Iterator<HeaderCell> iterator = this.model.getHeaderCellList().iterator();
    Row row = sheet.createRow(rowCount++);

    int columnCount=0;
    while (iterator.hasNext())
    {
        sheet.autoSizeColumn(columnCount);
        HeaderCell headerCell = iterator.next();

        String columnHeader = headerCell.getTitle();

        if (columnHeader == null)
        {
            columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
        }

        Cell hdrCell = row.createCell(columnCount++);
        hdrCell.setCellValue(columnHeader);
        hdrCell.setCellStyle(headerstyle);
    }
}

protected void generateRows() throws JspException, BadElementException
{
    // get the correct iterator (full or partial list according to the exportFull field)
    RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
    // iterator on rows
    while (rowIterator.hasNext())
    {
        Row newrow = sheet.createRow(rowCount++);
        org.displaytag.model.Row row = rowIterator.next();
        // iterator on columns
        ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
        int columnCount=0;
        while (columnIterator.hasNext())
        {
            Column column = columnIterator.nextColumn();

            // Get the value to be displayed for the column
            Object value = column.getValue(this.decorated);

            Cell Cell = newrow.createCell(columnCount++);
            Cell.setCellValue(ObjectUtils.toString(value));
        }
    }

}

@Override
public void doExport(OutputStream out) throws IOException, JspException {

    try {
        // Initialize the table with the appropriate number of columns
        initTable();

        generateXLSXTable();

    } catch (BadElementException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    workbook.write(out);
    out.close();
}
}

Note: You can modify this class as per your need.

displaytag.properties

Step 5 : Copy and Paste following code in it

export.amount=list
sort.amount=list
export.decorated=true

export.types=csv excel xml pdf rtf [mymedia]

export.[mymedia]=true
export.[mymedia].class=com.displaytag.export.views.myExcel2007ExportView

export.excel=true
export.csv=true
export.xml=true
export.pdf=true


export.pdf.include_header=true
export.excel.include_header=true

export.rtf=true
export.rtf.label=<span class="export rtf">RTF </span>
export.rtf.include_header=true
export.rtf.filename=

export.[mymedia].label=<span class="export excel">Excel 2007 </span>
export.[mymedia].include_header=true
export.[mymedia].filename=

Notice: Make sure you do not change the order of properties

jsp
<display:table id="l" name="yourList" pagesize="10" requestURI="/yourAction.action" style="width:100%" export="true">


<display:setProperty name="export.csv.filename" value="YourReport.csv" />
<display:setProperty name="export.excel.filename" value="YourReport.xls" />
<display:setProperty name="export.xml.filename" value="YourReport.xml" />
<display:setProperty name="export.rtf.filename" value="YourReport.rtf" />
<display:setProperty name="export.pdf.filename" value="YourReport.pdf" />
<display:setProperty name="export.[mymedia].filename" value="YourReport.xlsx" />


<display:caption media="csv xml excel pdf rtf [mymedia]"> yourcaption</display:caption>

<display:column property="id" titleKey="selectlogdetails.logid"  sortable="false" />
//other columns

<display:footer media="csv xml excel pdf rtf [mymedia]"> yourfooter
</display:footer>
</display:table>

Notice : Do not forget to add [mymedia] in caption, footer and also do not forget to set file name as below.

<display:setProperty name="export.[mymedia].filename" value="YourReport.xlsx" />

Step 7: Copy and paste following jar files in your WEB-INF/lib folder

commons-lang-2.3 or above

commons-beanutils-1.7.0 or above

commons-collections-3.1 or above

itext-1.3 or above

slf4j-api-1.4.2 or above

slf4j-log4j12-1.4.2 or above

Notice : If I forgot any dependency jar then please check it with maven.

Step 8: Run the project and Enjoy :) Also let me know if any error is there. Also if you are using any decorator in your displaytag then you have to write code for it.

To make a resource folder If you are using eclipse right click on java resources as below

Give name res and make a properties file in it

SEVERE: Servlet.service() for servlet [default] in context with path [/reportnguccGraph] threw exception java.lang.IllegalStateException: Cannot call sendError() after the response has been committed

see my answer for creating a resorce folder. I gave name res to the source folder created and I have put displaytag.properties file in it. In src folder there are java files and in res we put resource files.

i'm using netbeans and my displaytag.properties file is in default package with other xml files just like yours but it gives me an exception when the response is coming to jsp

java - How to export excel file with xlsx extension in displaytag - St...

java excel export-to-excel displaytag