Rectangle 27 1

This answer is for .xls; the API for xlsx is very similar, though. Reading the Excel file is easy, open a FileInputstream for the file, then create a HSSFWorkbook

HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

To change to color of some cells, you first have to create a Style with this color (changing the style of a cell will also change some other properties, like the font etc., so things might get a bit messy here, but that's how excel works)

colorStyle = workbook.createCellStyle();
colorStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
colorStyle.setFillForegroundColor(HSSFColor.RED.index);

Now you have to loop through the rows in all workbooks, identify the duplicates, and change the style of those cells:

cell.setCellStyle(colorStyle);

Finally, you write the workbook to a new file, through a FileOutputStream

workbook.write(outputStream);

How to change color of a cell of an existing excel (xlsx or xls) in ja...

java excel apache-poi xls xlsx
Rectangle 27 10

According to the Apache POI Quick Guide, the POIFSFileSystem (or similarly, NPOIFSFileSystem) is only used with .xls (Excel versions through 2003) documents.

The equivalent for .xlsx documents (Excel 2007+) is OPCPackage.

OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
XSSFWorkbook
OPCPackage

Or you can just create it directly:

XSSFWorkbook wb = new XSSFWorkbook(new File("file.xlsx"));

Generally it's better to create the workbook using a File instead of an InputStream, to save memory.

Also, if you want code that doesn't care whether it's an .xls or an .xlsx:

// or "file.xlsx"
Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));

could you show the import section please

opcpackage.open
OPCPackage.open cannot be resolved to a type
import org.apache.poi.openxml4j.opc.OPCPackage;

that is what i am importing, and i am having the error

OPCPackage.open
static
fs = OPCPackage.open(new File(getFilePath()));

excel - Java POI the supplied data appears to be in the Office 2007+ X...

java excel maven apache-poi xssf
Rectangle 27 21

// For .xlsx
POIXMLDocument.hasOOXMLHeader(new BufferedInputStream( new FileInputStream(file) ));

// For .xls
POIFSFileSystem.hasPOIFSHeader(new BufferedInputStream( new FileInputStream(file) ));

These are essentially the methods that the WorkbookFactory#create(InputStream) uses for determining the type

Please note, that both method supports only streams supporting "mark" feature (or PushBackInputStream), so simple FileInputStream is not supported. Use BufferedInputStream as a wrapper. For this reason after the detection you can simply reuse the stream, since it will be reseted to the starting point.

Thank you. This is the only way to detect file type when you are using low level Event based read only access to process large excel files.

Added the information that only "mark" supporting streams can be used. Updated code sample.

hasOOXMLHeader
POIXMLDocument

java - Determine MS Excel file type with Apache POI - Stack Overflow

java excel apache-poi
Rectangle 27 6

Workbook wb = WorkbookFactory.create(myExcelFile);
if (wb instanceof HSSFWorkbook) {
    // do whatever
} else if (wb instanceof SXSSFWorkbook) {
    // do whatever
} else if (wb instanceof XSSFWorkbook) {
    // do whatever
}

But incase you are dealing with large files, there is a possibility that you will get File as ByteArayInputStream. In this case, the bellow approach shall work.

ByteArayInputStream bais = new ByteArrayInputStream(file.getData());

if(bais != null && POIFSFileSystem.hasPOIFSHeader(bais)) {
    System.out.println(".xls extention excel file");
    //do whatever
}
else if(bais != null && POIXMLDocument.hasOOXMLHeader(bais)) {
    System.out.println(".xlsx extention excel file");
    //do whatever
}
else {
    //wrong file format. throw exception.
}

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
Rectangle 27 6

Workbook wb = WorkbookFactory.create(myExcelFile);
if (wb instanceof HSSFWorkbook) {
    // do whatever
} else if (wb instanceof SXSSFWorkbook) {
    // do whatever
} else if (wb instanceof XSSFWorkbook) {
    // do whatever
}

But incase you are dealing with large files, there is a possibility that you will get File as ByteArayInputStream. In this case, the bellow approach shall work.

ByteArayInputStream bais = new ByteArrayInputStream(file.getData());

if(bais != null && POIFSFileSystem.hasPOIFSHeader(bais)) {
    System.out.println(".xls extention excel file");
    //do whatever
}
else if(bais != null && POIXMLDocument.hasOOXMLHeader(bais)) {
    System.out.println(".xlsx extention excel file");
    //do whatever
}
else {
    //wrong file format. throw exception.
}

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
Rectangle 27 12

Updated: As of version 3.10 POI supports encryption as well as decryption for XLSX files. See the "Encryption Support" page on POI's website. The below is still relevant for XLS binary workbooks.

According to the "Encryption Support" page on POI's website POI supports reading encrypted XLS and XLSX files. Encrypting is not mentioned on that page, which implies that it's not supported. This is backed up by searching the POI site for "encrypt" which returns only a handful of results all of which are about decryption. I've also taken a look at the sources for their crypto implementation, which appears to only handle decryption. This isn't surprising; POI is designed for data extraction and search indexing, not for creating new spreadsheets.

As others have suggested, it's often possible to work around missing features in POI by creating a template in Excel and then using POI to populate it with data. Unfortunately that won't work for encryption because the file format of encrypted spreadsheets is radically different.

If you're willing to pay for commercial software, the latest version of ExtenXLS has full read and write support for all the encryption formats supported by Excel. Just construct an EncryptedWorkBookHandle instead of the normal WorkBookHandle. That will use the strongest possible cipher supported by an unmodified JRE, RC4 for XLS and 128-bit AES for XLSX. If you want to use 256-bit AES with OOXML and you've installed the JCE unlimited policy you can do so with the MSOfficeEncrypter class.

JExcelAPI, a popular open-source Java spreadsheet API, does not appear to support encryption at all. Aspose.Cells, a commercial offering, supports stong encryption. The documentation for Actuate's e.Spreadsheet seems to have disappeared from the 'net, so I can't tell whether it supports encryption or not.

Since none of the freely available Java spreadsheet APIs seems to support writing encrypted spreadsheets, if you're not willing to use commercial software you'll need to come up with a workaround. You could, for example, write the spreadsheet into an encrypted ZIP file. java.util.zip doesn't support encryption, but it looks like Zip4j does.

Full disclosure: I work for Extentech, the company behind ExtenXLS.

I've added the encryption support for xml-based files to POI recently, so it would be nice, if you update your answer, i.e. the first paragraph is out-dated. regarding the design for extraction: most POI-apis starting with reading support of a feature, but I'm not agreeing that POI is specifically designed for extraction (this was also valid back in Jan. 2012 in my point of view)

Agree with kiwiwings.was mislead by this answer and checking latest docs showed there is encryption support. poi.apache.org/encryption.html Please update the answer.

@kiwiwings I updated it, finally. FWIW you don't need to wait for the original author to update things. StackOverflow is wiki-ish, so you can always propose an edit.

java - Create a password protected excel file using apache poi? - Stac...

java excel apache-poi xls
Rectangle 27 14

I haven't had much exp with Apache POI, but as far as i know if you refer to a workbook by class "Workbook" then you can read and write both xls & xlsx.

Workbook wb = new HSSFWorkbook();
Workbook wb = new XSSFWorkbook();

you can pass a parameter for file type and create the WorkBook object accordingly using If statement.

Hello! I also have the said codes in mind and I was planning to use those if I cannot find any other way to detect the file extension.

java - Is there any way to read both .xls and .xlsx files using Apache...

java excel apache-poi
Rectangle 27 3

Workbook wb = WorkbookFactory.create(myExcelFile);

Then you can check the exact type created by the factory:

if (wb instanceof HSSFWorkbook) {
    // do whatever
} else if (wb instanceof SXSSFWorkbook) {
    // do whatever
} else if (wb instanceof XSSFWorkbook) {
    // do whatever
}

Anyway if you want to know the real type of the file before using it you can use Apache Tika.

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
Rectangle 27 3

You can let POI do the job for you, by using WorkbookFactory to automatically have the file extension detected opened for you.

Workbook w = WorkbookFactory.create(new File("hello.xls"));

Of course, you can use Apache Tika to detect the file format for you!

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
Rectangle 27 3

You can let POI do the job for you, by using WorkbookFactory to automatically have the file extension detected opened for you.

Workbook w = WorkbookFactory.create(new File("hello.xls"));

Of course, you can use Apache Tika to detect the file format for you!

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
Rectangle 27 3

Workbook wb = WorkbookFactory.create(myExcelFile);

Then you can check the exact type created by the factory:

if (wb instanceof HSSFWorkbook) {
    // do whatever
} else if (wb instanceof SXSSFWorkbook) {
    // do whatever
} else if (wb instanceof XSSFWorkbook) {
    // do whatever
}

Anyway if you want to know the real type of the file before using it you can use Apache Tika.

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
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 1

I woul recommend to use this FilenameUtils.getExtension from Apache Commons IO :

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
Rectangle 27 1

I woul recommend to use this FilenameUtils.getExtension from Apache Commons IO :

java - Open xls and xlsx with Apache POI - Stack Overflow

java excel apache-poi xls xlsx
Rectangle 27 1

I do not know any generic API to do this work. But, if u already have a "mapper" for your json, you could generate the xls using the apache POI API easily.

would like something already done though, of course.

java - JSON to xlsx converter - Stack Overflow

java linux json excel
Rectangle 27 3

you can use commons csv to convert into CSV format. or use POI to convert into xls. if you need helper to convert into xls, you can use jxls, it can convert java bean (or list) into excel with expression language.

Basically, the json doc maybe is a json array, right? so it will be same. the result will be list, and you just write the property that you want to display in excel format that will be read by jxls. See http://jxls.sourceforge.net/reference/collections.html

If the problem is the json can't be read in the jxls excel property, just serialize it into collection of java bean first.

{'foo': {'bar': [1, 2, 3]}}

That's why I said, if the json object is json single object, not json array, the result will be 1 row in the spreadsheet. If the json object is json array, the rows will be same with the json array size. And 1 again, you have to specify what property you want to write into that spreadsheet. If your example, if you want to display the bar (which the value is array), the column will be display maybe java.lang.Array@xxxxx, but it is an array, right. But if the object is [{name: 'a', 'bar' : [1,2,3]}, {name: 'b', 'bar' : [2,3,4]}], and you want to display the name only, it's feasible, right?

Converting JSON to XLS/CSV in Java - Stack Overflow

java json excel csv xls
Rectangle 27 3

you can use commons csv to convert into CSV format. or use POI to convert into xls. if you need helper to convert into xls, you can use jxls, it can convert java bean (or list) into excel with expression language.

Basically, the json doc maybe is a json array, right? so it will be same. the result will be list, and you just write the property that you want to display in excel format that will be read by jxls. See http://jxls.sourceforge.net/reference/collections.html

If the problem is the json can't be read in the jxls excel property, just serialize it into collection of java bean first.

{'foo': {'bar': [1, 2, 3]}}

That's why I said, if the json object is json single object, not json array, the result will be 1 row in the spreadsheet. If the json object is json array, the rows will be same with the json array size. And 1 again, you have to specify what property you want to write into that spreadsheet. If your example, if you want to display the bar (which the value is array), the column will be display maybe java.lang.Array@xxxxx, but it is an array, right. But if the object is [{name: 'a', 'bar' : [1,2,3]}, {name: 'b', 'bar' : [2,3,4]}], and you want to display the name only, it's feasible, right?

Converting JSON to XLS/CSV in Java - Stack Overflow

java json excel csv xls
Rectangle 27 1

one option would be to check the file name with lastIndexOf for . and see if it is .xls or xlsx and then use an if condition to switch accordingly. been a long time since i worked on poi but i think it the attributes are like HSSF for .xls and XSSF for .xlsx refer http://poi.apache.org/ site, last line under the topic Why should I use Apache POI?

java - Is there any way to read both .xls and .xlsx files using Apache...

java excel apache-poi
Rectangle 27 5

The method using the above code has a single parameter - FileInputStream. The first line in the code snippet is very much part of the code, but part of the invoking method. Since the method in question did not have knowledge of the Excel format or even a file extension to make an educated guess, I decided that I would first try to read the FileInputStream using HSSF API as below:

The problem with the above code is that the state of the inputFS object during the second attempt of opening it via the XSSF API is unknown. And this yielded a read error. I replaced the above with the following code, which works fine and the issue appears to be resolved:

Sheet sheet = null;
try {

    Workbook workbook = WorkbookFactory.create(inputFS);
    sheet = workbook.getSheetAt(0);
}
catch (Exception e) {
}

I tested this with both XLS (older, binary) and XLSX (newer, XML-based) formats and it works. Thanks for everyone's help and input!

Great . It solves many problems of my code.

java - Error reading Excel .XLSX with Apache POI - Stack Overflow

java apache-poi xlsx
Rectangle 27 5

You have to use XSSFWorkbook for XLSX.

HSSFWorkbook workbook = new HSSFWorkbook();
try {
    FileOutputStream output = new FileOutputStream("Test1.xls");
    workbook.createSheet("sheet1")
    workbook.write(output);
    output.close();
} catch (Exception e) {
    e.printStackTrace();
}

Thank you very much. Even I did not change to XSSF and just added createSheet and it worked.

java - Apache poi the file is corrupted - Stack Overflow

java excel apache-poi