Convert List to Array with datatype in Java

If we have list and we have to convert into array with datatype. Java provides method for this.

For ex: List<Long> var = new ArrayList<Long>();

We have convert this into Long array. 

Conversion code: 

            Long[] varArray =  var.toArray(new Long[var.size()]);

Convert TWList object to xls

Do you want to convert BPM TWLIst object to excel file , here the code


import com.lombardisoftware.client.persistence.TWClass;
import com.lombardisoftware.data.twclass.TWClassDefinitionData;
import com.lombardisoftware.data.twclass.TWClassProperty;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import teamworks.TWList;
import teamworks.TWObject;

public class XlsxWriter {
    private SXSSFWorkbook workbook = new SXSSFWorkbook(100);

    public void createMultipleExcelFile(String path, TWObject object, TWList sheetNameList) throws ExcelWriterException {
        try {
            List<String> varList = this.getPropertyList(object);
            for (int i = 0; i < varList.size(); ++i) {
                TWList proertyValue = (TWList)object.getPropertyValue(varList.get(i));
                List<String> colModelList = this.getPropertyList((TWObject)proertyValue.getArrayData(0));
                this.addSheet(proertyValue, colModelList, sheetNameList.getArrayData(i).toString());
            }
            FileOutputStream output = new FileOutputStream(path);
            this.write(output);
            output.close();
        }
        catch (Exception e) {
            throw new ExcelWriterException(0, e.getMessage());
        }
    }

    public void createExcelFile(String path, TWList object, String sheetName) throws ExcelWriterException {
        try {
            ArrayList<String> colModelList = new ArrayList();
            TWObject report = (TWObject)object.getArrayData(0);
            colModelList = this.getPropertyList(report);
            this.addSheet(object, colModelList, sheetName);
            FileOutputStream output = new FileOutputStream(path);
            this.write(output);
            output.close();
        }
        catch (ParseException e) {
            throw new ExcelWriterException(0, e.getMessage());
        }
        catch (FileNotFoundException e) {
            throw new ExcelWriterException(0, e.getMessage());
        }
        catch (Exception e) {
            throw new ExcelWriterException(0, e.getMessage());
        }
    }

    private void addSheet(TWList data, List<String> colModel, String sheetName) throws ExcelWriterException {
        Sheet sheet = this.workbook.createSheet(sheetName);
        int numCols = colModel.size();
        int currentRow = 0;
        try {
            int i;
            Row row = sheet.createRow(currentRow);
            for (i = 0; i < numCols; ++i) {
                Cell cell = row.createCell(i);
                Font fn = this.workbook.createFont();
                fn.setBoldweight(700);
                CellStyle cs = this.workbook.createCellStyle();
                cs.setFont(fn);
                cell.setCellStyle(cs);
                cell.setCellValue(colModel.get(i).toString());
            }
            ++currentRow;
            for (i = 0; i < data.getArraySize(); ++i) {
                row = sheet.createRow(currentRow++);
                TWObject bean = (TWObject)data.getArrayData(i);
                for (int y = 0; y < numCols; ++y) {
                    Object proertyValue = bean.getPropertyValue(colModel.get(y));
                    String value = proertyValue != null ? proertyValue.toString() : "";
                    Cell cell = row.createCell(y);
                    cell.setCellValue(value);
                }
            }
            for (i = 0; i < numCols; ++i) {
                sheet.autoSizeColumn((int)((short)i));
            }
        }
        catch (Exception e) {
            throw new ExcelWriterException(0, e.getMessage());
        }
    }

    private void write(OutputStream outputStream) throws Exception {
        this.workbook.write(outputStream);
    }

    private List<String> getPropertyList(TWObject twObj) throws ExcelWriterException {
        ArrayList<String> variables = null;
        try {
            variables = new ArrayList<String>();
            List defnPropList = ((com.lombardisoftware.core.TWObject)twObj).getTWClass().getDefinition().getProperties();
            for (TWClassProperty prop : defnPropList) {
                variables.add(prop.getName());
            }
        }
        catch (Exception e) {
            throw new Exception(0, e.getMessage());
        }
        return variables;
    }
}

Reversing the digits of an Integer Algorithm

Design an algorithm that accepts a positive integer and reverses the order of its digits.

Algorithm development

Digit reversal is a technique that is sometimes used in computing to remove bias from a set of numbers. It is important in some fast information'-retrieval algorithms. A specific example clearly defines the relationship of the input to the desired output. For example,

                                                            Input: 27953

                                                            Output: 35972

The number 27953 is actually

2 * 10^4 + 7 * 10^3 + 9 * 10^2 + 5 * 10^1 + 3 * 10^0

To access the individual digits it is probably going to be easiest to start at one end of the number and work through to the other end. Because other than visually it is not easy to tell how many digits there are in the input number it will be best to try to establish the identity of the least significant digit (i.e. the rightmost digit). To do this we need to effectively "chop off" the least significant digit in the number. In other words we want to end up with 2795 with the 3 removed and identified.

The number 2795 can be obtained by integer division of the original number by 10 i.e. 27953 div 10->2795 This chops off the 3 but does not save it. However, 3 is the remainder that results from dividing 27953 by 10. To get this remainder use the mod function. That is,

                                                27953 mod 10->3

Applying the following two steps

            r := n mod 10  (l)=>(r = 3)

            n := n div 10    (2)=>(n = 2795)

the digit 3 is obtained and the new number 2795. Applying the same two steps to the new value of n we can obtain the 5 digit. Iteratively access the individual digits of the input number. Next major concern is to carry out the digit reversal. Applying digit extraction procedure to the first two digits  3 and then 5 are obtained. In the final output they appear as:

3 followed by 5

If the original number was 53 then we could obtain its reverse by first extracting the 3, multiplying it by 10, and then adding 5 to give 35. That is

                                                                        3x 10+5->35

The last three digits of the input number are 953. They appear in the "reversed" number as 359. Therefore at the stage when we have the 35 and then extract the 9 we can obtain the sequence 359 by multiplying 35 by 10 and adding 9. That is,

35 * 10 +9 -> 359

359 * 10 + 7 -> 3597

3597 * 10 + 2  ->  35972

The last number obtained from the multiplication and addition process Is the "digit-reversed" integer we have been seeking. On closely examining the digit extraction, and the reversal process, it is evident that they both involve a set of steps that can be performed iteratively.

            We must now find a mechanism for building up the "reversed" integer digit by digit. Let us assume that the variable dreverse is to be used to build the reversed integer. At each stage in building the reversed integer its previous value is used in conjunction with the most recently extracted digit. Rewriting the multiplication and addition process we have just described in terms of the variable dreverse,

Therefore to build the reversed integer we can use the construct:

            dreverse := (previous value of dreverse)* 10 + (most recently extracted rightmost digit)

The variable dreverse can be used on both sides of this expression. For the value of dreverse to be correct (i.e. dreverse = 3) after the first iteration it must initially be zero. This initialization step for dreverse is also needed to ensure that the algorithm functions correctly when the input number to be reversed is zero. Under what conditions should the iterative process terminate is to be found. The termination condition must in some way be related to the number of digits in the input integer. In fact as soon as all digits have been extracted and processed termination should apply. With each iteration the number of digits in the number being reversed is reduced by one, yielding the sequence shown in Table 2.1. Accumulative integer division of the "number being reversed" by 10 produces the sequence 27953, 95,279, ... . In our example, when the integer division process is applied for 5thtime a zero results since 2 is less than 10. Since at this point in the computation the "reversed" number has been fully constructed we can use the zero result to terminate the iterative process.

The central steps in our digit reversal algorithm are: 1. While there are still digits in the number being reversed do (a) extract the right most digit from the number being reversed and append this digit to the right-hand end of the current reversed number representation; (b) remove the rightmost digit from the number being reversed.

Algorithm description

1. Establish n, the positive integer to be reversed.

2. Set the initial condition for the reversed integer dreverse.

3. While the integer being reversed is greater than zero do

(a) use the remainder function to extract the rightmost digit of the 11 number being reversed; .

(b) increase the previous reversed integer representation dreverse by a factor of 10and add to it the most recently extracted digit to give the current dreverse value; .

(c) use integer division by 10 to remove the rightmost digit from the number being reversed.

This algorithm is most suitably implemented as a function which accepts as input the integer to be reversed and returns as output the integer with its digits reversed.

Convert Excel to XML or JSON in Java using POI jars

Excel reader using Java:

Required Jars:

• xmlbeans-2.5.0
• dom4j-1.6.1
• asm-3.1
• poi-3.9
• poi-ooxml-3.9
• poi-ooxml-schemas-3.9
• java-json

Description:

If you want to convert your excel file sheet to xml element or json object below code will be useful.

The main method is,

/**

* This method called by external system.It will return XMLElement string or

* json string to calling system.

* 

* @param ios

*            - Input stream of file which need to convert

* @param fileName

*            - Absolute file pathk

* @param sheetNames

*            - Sheet names to parse.If its null system will parse all

*            sheets.

* @param emptyRowCount

*            - Number of consecutive empty rows to stop reading the file.

* @param headerRow

*            - Header row number in the work sheet to find the total number

*            of column.

* @param maxLimit

*            - Maximum number of rows to read

* @param outputType

*            - return output type - possible values(json, xml) Default is

*            XML.

* @return response -String based on output type.

*/

              getXMLElementsFromExcel(fileStream,

"filename.xlsx", "sheet name", emptyRowCount,

headerRowNumber, MaxLinesTORead,"outputType(json or xml)");

Implementation Code:

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import java.util.StringTokenizer;

import java.util.concurrent.TimeUnit;

import javax.xml.parsers.ParserConfigurationException;

import javax.xml.parsers.SAXParser;

import javax.xml.parsers.SAXParserFactory;

import org.apache.commons.io.FilenameUtils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;

import org.apache.poi.openxml4j.opc.OPCPackage;

import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;

import org.apache.poi.xssf.eventusermodel.XSSFReader;

import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;

import org.apache.poi.xssf.model.StylesTable;

import org.json.JSONException;

import org.json.JSONObject;

import org.json.XML;

import org.xml.sax.ContentHandler;

import org.xml.sax.InputSource;

import org.xml.sax.SAXException;

import org.xml.sax.XMLReader;

/**

 * 

 * This class is used to convert the xlsx and xlsm file format contents into a

 * XML string.

 */

public class ExcelToXMLOrJson implements Constants {

static List<String> header = null;

/**

* This method called by external system.It will return XMLElement string or

* json string to calling system.

* 

* @param ios

*            - Input stream of file which need to convert

* @param fileName

*            - Absolute file pathk

* @param sheetNames

*            - Sheet names to parse.If its null system will parse all

*            sheets.

* @param emptyRowCount

*            - Number of consecutive empty rows to stop reading the file.

* @param headerRow

*            - Header row number in the work sheet to find the total number

*            of column.

* @param maxLimit

*            - Maximum number of rows to read

* @param outputType

*            - return output type - possible values(json, xml) Default is

*            XML.

* @return response -String based on output type.

*/

public static String getXMLElementsFromExcel(InputStream ios,

String fileName, String sheetNames, int emptyRowCount,

int headerRow, int maxLimit, String outputType) {

ExcelReaderResponseBuilder resBuilder = new ExcelReaderResponseBuilder();

StringBuilder response = new StringBuilder();

resBuilder.setStatus(STATUS_SUCCESS);

resBuilder.setErrorCode(null);

resBuilder.setErrorMessage(null);

try {

File fileObject = new File(fileName);

String ext = FilenameUtils.getExtension(fileName);

// Call the method to parse the excel file

if (fileObject != null) {

if (ext.equalsIgnoreCase("xls")) {

if (outputType.equalsIgnoreCase("json")) {

return convertToJson(XlsReader.readExcel(ios,

sheetNames, resBuilder));

} else {

XlsReader.readExcel(ios, sheetNames, resBuilder);

}

} else {

parseExcel(ios, sheetNames, (headerRow - 1), emptyRowCount,

maxLimit, resBuilder);

}

}

} catch (ExcelReaderException e) {

resBuilder.setStatus(e.getStatus());

resBuilder.setErrorCode(e.getErrorCode());

resBuilder.setErrorMessage(e.getErrormessage());

resBuilder.setLastPage(e.isLastPage());

} catch (FileNotFoundException e) {

resBuilder.setStatus(STATUS_FAILURE);

resBuilder.setErrorCode(FILE_NOT_FOUND_EC);

resBuilder.setErrorMessage(FILE_NOT_FOUND);

resBuilder.setLastPage(true);

} catch (Exception e) {

resBuilder.setStatus(STATUS_FAILURE);

resBuilder.setErrorCode(EXCEPTION_EC);

resBuilder.setErrorMessage(e.getMessage());

resBuilder.setLastPage(true);

}

if (resBuilder.getResponse() == null || resBuilder.getStatus() == 0) {

response.append(

"<?xml version=\"1.0\" encoding=\"us-ascii\"?> <response><data>")

.append("</data><status>").append(resBuilder.getStatus())

.append("</status><errorcode>")

.append(resBuilder.getErrorCode())

.append("</errorcode><errormessage>")

.append(resBuilder.getErrorMessage())

.append("</errormessage></response>");

} else {

response.append(

"<?xml version=\"1.0\" encoding=\"us-ascii\"?><response><data>")

.append(resBuilder.getResponse().toString())

.append("</data><status>").append(resBuilder.getStatus())

.append("</status><errorcode>")

.append(resBuilder.getErrorCode())

.append("</errorcode><errormessage>")

.append(resBuilder.getErrorMessage())

.append("</errormessage><headers>");

for (int i = 0; i < header.size(); i++) {

response.append("<header>" + header.get(i) + "</header>");

}

response.append("</headers></response>");

}

resBuilder = null;

if (outputType.equalsIgnoreCase("json")) {

return convertToJson(response.toString());

}

return response.toString();

}

/**

* Start to process and creates XMLElement string.

* 

* @param file

*            - This is file object for the given excel file

* @param workSheetNames

*            - List of work sheet names to read

* @param headerRow

*            - Header row number in the work sheet to find the total number

*            of column.

* @param emptyRowCount

*            - Number of consecutive empty rows to stop reading the file

* @param maxLimit

*            - Maximum number of rows to read

* @param resBuilder

*            - Builder class object which holds output xml element values

* 

* @throws IOException

* @throws ExcelReaderException

*/

private static void parseExcel(InputStream ios, String workSheetNames,

int headerRow, int emptyRowCount, int maxLimit,

ExcelReaderResponseBuilder resBuilder) throws IOException,

ExcelReaderException {

OPCPackage container = null;

InputStream stream = null;

ArrayList<String> sheetNameList = new ArrayList<String>();

try {

// split the input list names

if (workSheetNames != null && !workSheetNames.equalsIgnoreCase("")) {

StringTokenizer strTokenizer = new StringTokenizer(

workSheetNames, ",");

while (strTokenizer.hasMoreElements()) {

sheetNameList.add(strTokenizer.nextElement().toString()

.trim().toLowerCase());

}

}

container = OPCPackage.open(ios);

ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(

container);

XSSFReader xssfReader = new XSSFReader(container);

StylesTable styles = xssfReader.getStylesTable();

XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader

.getSheetsData();

// If sheet name has given this part will get execute.

if (!sheetNameList.isEmpty()) {

while (iter.hasNext()) {

stream = iter.next();

String sheetName = iter.getSheetName();

if (resBuilder.getStatus() == 0) {

throw new ExcelReaderException(resBuilder.getStatus(),

resBuilder.getErrorCode(),

resBuilder.getErrorMessage(), true);

}

if (sheetNameList.contains(sheetName.toLowerCase())) {

processSheet(styles, strings, stream, sheetName,

headerRow, emptyRowCount, maxLimit, resBuilder);

resBuilder.getResponse().append("</worksheet>");

}

sheetNameList.remove(sheetName.toLowerCase());

stream.close();

}

// If sheet name has not given this part will process all

// worksheet

} else {

while (iter.hasNext()) {

stream = iter.next();

String sheetName = iter.getSheetName();

if (resBuilder.getStatus() == 0) {

throw new ExcelReaderException(resBuilder.getStatus(),

resBuilder.getErrorCode(),

resBuilder.getErrorMessage(), true);

}

processSheet(styles, strings, stream, sheetName, headerRow,

emptyRowCount, maxLimit, resBuilder);

resBuilder.getResponse().append("</worksheet>");

stream.close();

}

}

// Identify the unavailable sheets

if (!sheetNameList.isEmpty()) {

throw new ExcelReaderException(STATUS_FAILURE,

SHEET_NOT_AVAILABLE_EC, SHEET_NOT_AVAILABLE

+ sheetNameList.toString(), false);

}

} catch (InvalidFormatException e) {

resBuilder.setResponse(null);

throw new ExcelReaderException(STATUS_FAILURE, EXCEPTION_EC,

e.getMessage(), false);

} catch (SAXException e) {

resBuilder.setResponse(null);

throw new ExcelReaderException(STATUS_FAILURE, EXCEPTION_EC,

e.getMessage(), false);

} catch (OpenXML4JException e) {

resBuilder.setResponse(null);

throw new ExcelReaderException(STATUS_FAILURE, EXCEPTION_EC,

e.getMessage(), false);

} finally {

if (stream != null) {

stream.close();

}

if (container != null) {

container.close();

}

}

}

/**

* Start to process and creates XMLElement string.

* 

* @param file

*            - This is file object for the given excel file

* @param workSheetNames

*            - List of work sheet names to read

* @param headerRow

*            - Header row number in the work sheet to find the total number

*            of column.

* @param emptyRowCount

*            - Number of consecutive empty rows to stop reading the file

* @param maxLimit

*            - Maximum number of rows to read

* @param resBuilder

*            - Builder class object which holds output xml element values

* 

* @throws IOException

* @throws ExcelReaderException

*/

public static String getSheetNames(InputStream ios, String fileName) {

OPCPackage container = null;

InputStream stream = null;

StringBuilder sheetNameXML = null;

String result = null;

try {

File fileObject = new File(fileName);

String ext = FilenameUtils.getExtension(fileName);

if (fileObject != null) {

if (ext.equalsIgnoreCase("xls")) {

return convertToJson(XlsReader.getSheetNames(ios));

} else {

container = OPCPackage.open(ios);

XSSFReader xssfReader = new XSSFReader(container);

XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader

.getSheetsData();

sheetNameXML = new StringBuilder();

sheetNameXML.append("<response>");

while (iter.hasNext()) {

stream = iter.next();

String sheetName = iter.getSheetName();

sheetNameXML.append("<worksheet>" + sheetName

+ "</worksheet>");

stream.close();

}

sheetNameXML.append("</response>");

}

}

} catch (InvalidFormatException e) {

result = "<response><errorMessage>" + e.getMessage()

+ "</errorMessage></response>";

} catch (OpenXML4JException e) {

result = "<response><errorMessage>" + e.getMessage()

+ "</errorMessage></response>";

} catch (IOException e) {

result = "<response><errorMessage>" + e.getMessage()

+ "</errorMessage></response>";

} catch (Exception e) {

result = "<response><errorMessage>" + e.getMessage()

+ "</errorMessage></response>";

} finally {

if (stream != null) {

try {

stream.close();

} catch (IOException e) {

result = "<response><errorMessage>" + e.getMessage()

+ "</errorMessage></response>";

}

}

if (container != null) {

try {

container.close();

} catch (IOException e) {

result = "<response><errorMessage>" + e.getMessage()

+ "</errorMessage></response>";

}

}

}

if (sheetNameXML != null) {

result = sheetNameXML.toString();

}

return convertToJson(result);

}

/**

* This method parse the work sheet.

* 

* @param styles

*            - Styles on work sheet

* @param strings

*            - String table object

* @param sheetInputStream

*            - Input stream for work sheet

* @param sheetName

*            - Sheet name to read

* @param headerRow

*            - Header row number in the worksheet to find the total number

*            of column.

* @param finalEmptyRowCount

*            - Number of consecutive empty rows to stop reading the file

* @param maxLimit

*            - Maximum number of rows to read

* @param resBuilder

*            - Builder class object which holds output xml element values

* @return resBuilder - Worksheet data wrapped into object

* @throws IOException

* @throws SAXException

*/

private static void processSheet(StylesTable styles,

ReadOnlySharedStringsTable strings, InputStream sheetInputStream,

String sheetName, int headerRow, int finalEmptyRowCount,

int maxLimit, ExcelReaderResponseBuilder resBuilder)

throws IOException, SAXException {

ContentHandler handler = null;

InputSource sheetSource = new InputSource(sheetInputStream);

SAXParserFactory saxFactory = SAXParserFactory.newInstance();

ExcelReaderSheetHandlerV1 sheetHandler = null;

try {

SAXParser saxParser = saxFactory.newSAXParser();

XMLReader sheetParser = saxParser.getXMLReader();

resBuilder.getResponse().append(

"<worksheet sheetName=\"" + sheetName + "\">");

// Receive notification of the logical content of a document.

sheetHandler = new ExcelReaderSheetHandlerV1(finalEmptyRowCount,

headerRow, maxLimit, sheetName);

handler = new XSSFSheetXMLHandler(styles, strings, sheetHandler,

false);

sheetParser.setContentHandler(handler);

sheetParser.parse(sheetSource);

resBuilder.setStatus(STATUS_SUCCESS);

resBuilder.setErrorCode(null);

resBuilder.setErrorMessage(null);

} catch (ExcelReaderException e) {

resBuilder.setStatus(e.getStatus());

resBuilder.setErrorCode(e.getErrorCode());

resBuilder.setErrorMessage(e.getErrormessage());

} catch (ParserConfigurationException e) {

throw new ExcelReaderException(STATUS_FAILURE, EXCEPTION_EC,

e.getMessage(), true);

} finally {

sheetInputStream.close();

}

if (sheetHandler.getResponse() != null)

resBuilder.getResponse().append(

sheetHandler.getResponse().toString());

header = sheetHandler.getHeader();

}

private static String convertToJson(String xmlString) {

JSONObject xmlJSONObj;

try {

xmlJSONObj = XML.toJSONObject(xmlString);

return xmlJSONObj.toString(4);

} catch (JSONException e) {

e.printStackTrace();

}

return null;

}

public static void main(String args[]) {

FileInputStream io = new FileInputStream(new File("your file"));

String objList = getXMLElementsFromExcel(io,

"filename.xlsx", "sheet name", 5,

1, 10000,"fileType(json or xml)");

System.out.println(objList);

}

}


Constatns.java
---------------------

/**
 * This interface has created to maintain the constant variable for excel
 * reader.
 */
public interface Constants {

// Error codes for Excel Reader

String EXCEPTION_EC = "100"; // EC - Error Code
String SHEET_NOT_AVAILABLE_EC = "101";
String CROSSED_LIMIT_EC = "102";
String FILE_NOT_FOUND_EC = "103";

// Error Messages
String SHEET_NOT_AVAILABLE = " The following sheet is not available, ";
String FILE_NOT_FOUND = "The given file path is invalid.";
String CROSSED_LIMIT = "The following sheet name crossed the maximum limit, ";

// Status codes
int STATUS_SUCCESS = 1;
int STATUS_FAILURE = 0;

//Output type
String JSON = "json";
}

ExcelReaderSheetHandlerV1

--------------------------------------

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;

/**

 * Receive notification of the logical content of a document and construct the

 * XML String from document

 * 

 */

public class ExcelReaderSheetHandlerV1 implements SheetContentsHandler,

Constants {

int initialRow;

int lastColumn;

int maxColumns;

int emptyRowCount;

int totalColumn;

boolean hasCell;

boolean addRow;

int finalEmptyRowCount;

StringBuilder response;

int maxLimit;

int headerRow;

int colCount;

String sheetName;

List<String> header;

int temp;

public ExcelReaderSheetHandlerV1(int finalEmptyRowCount, int headerRow,

int maxLimit, String sheetName) {

super();

this.finalEmptyRowCount = finalEmptyRowCount;

this.headerRow = headerRow;

this.maxLimit = maxLimit;

this.sheetName = sheetName;

this.response = new StringBuilder();

this.header = new ArrayList<String>();

}

public List<String> getHeader() {

return header;

}

public void setHeader(List<String> header) {

this.header = header;

}

@Override

public void startRow(int rowNum) {

initialRow = rowNum;

lastColumn = -1;

maxColumns = 0;

hasCell = false;

colCount = 0;

// If 5 consecutive empty rows occur , it will force

// to stop the reading of that sheet.

if (emptyRowCount > finalEmptyRowCount) {

throw new ExcelReaderException(STATUS_SUCCESS, null, null, true);

}

// If sheet contain more than 70000 records , it

// will force to stop the reading of that sheet and

// returns error.

if (rowNum <= maxLimit) {

if(initialRow != headerRow)

response.append("<row rowNumber=\"" + rowNum + "\">");

} 

//You can enable this else part if you want to stop the reading file if the sheet contains data ore than mentioned limit

/*else {

throw new ExcelReaderException(STATUS_FAILURE, CROSSED_LIMIT_EC,

CROSSED_LIMIT + sheetName, true);// Forcing to

// throw an

// exception to

// stop reading.

}*/

}

/* (non-Javadoc)

* @see org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler#endRow()

*/

@Override

public void endRow() {

if (maxColumns > totalColumn) {

for (int i = maxColumns; i < totalColumn + 1; i++) {

temp = colCount++;

response.append("<" + header.get(temp) + "></"

+ header.get(temp) + ">");

}

}

if (initialRow != headerRow) {

response.append("</row>");

}

if (!hasCell)

emptyRowCount++;

}

/* (non-Javadoc)

* @see org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler#cell(java.lang.String, java.lang.String)

*/

@Override

public void cell(String cellReference, String formattedValue) {

// Get the current cell number.(For example,

// A1,B1,C1..)

String r = cellReference;

int firstDigit = -1;

// Find the first digit location to split alphabets

// and digits.

for (int c = 0; c < r.length(); ++c) {

if (Character.isDigit(r.charAt(c))) {

firstDigit = c;

break;

}

}

// Get the current cell column number

int thisColumn = nameToColumn(r.substring(0, firstDigit));

if (lastColumn == -1) {

if (thisColumn == 0)

lastColumn = thisColumn;

else {

lastColumn = -1;

}

}

// Find the column difference to find empty cell

// count

int colDiff = thisColumn - lastColumn;

lastColumn = thisColumn;

// Add empty cell tag to XML string to indicate

// empty cell.

for (; colDiff > 1; colDiff--) {

temp = colCount++;

response.append("<" + header.get(temp) + "></" + header.get(temp)

+ ">");

maxColumns++;

}

// If the header row is processing get the column name

if (initialRow == headerRow) {

totalColumn++;

if (formattedValue.trim().indexOf(' ') == -1

&& formattedValue.trim().indexOf('/') == -1

&& formattedValue.indexOf("#") == -1) {

header.add(formattedValue.trim());

} else {

String tempValue = formattedValue.trim().replaceAll(" ", "_")

.replaceAll("/", "_Or_").replaceAll("#", "_No");

header.add(tempValue.trim());

}

} else {

// Adding cell value to XML string

temp = colCount++;

String result = "<" + header.get(temp) + "><![CDATA["

+ formattedValue.trim() + "]]></" + header.get(temp) + ">";

response.append(result);

}

maxColumns++;

hasCell = true;

}

/* (non-Javadoc)

* @see org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler#headerFooter(java.lang.String, boolean, java.lang.String)

*/

@Override

public void headerFooter(String arg0, boolean arg1, String arg2) {

}

/**

* Converts an Excel column name like "C" to a zero-based index.

* 

* @param name

* @return Index corresponding to the specified name

*/

private int nameToColumn(String name) {

int column = -1;

for (int i = 0; i < name.length(); ++i) {

int c = name.charAt(i);

column = (column + 1) * 26 + c - 'A';

}

return column;

}

public int getInitialRow() {

return initialRow;

}

public void setInitialRow(int initialRow) {

this.initialRow = initialRow;

}

public int getLastColumn() {

return lastColumn;

}

public void setLastColumn(int lastColumn) {

this.lastColumn = lastColumn;

}

public int getMaxColumns() {

return maxColumns;

}

public void setMaxColumns(int maxColumns) {

this.maxColumns = maxColumns;

}

public int getEmptyRowCount() {

return emptyRowCount;

}

public void setEmptyRowCount(int emptyRowCount) {

this.emptyRowCount = emptyRowCount;

}

public int getTotalColumn() {

return totalColumn;

}

public void setTotalColumn(int totalColumn) {

this.totalColumn = totalColumn;

}

public boolean isHasCell() {

return hasCell;

}

public void setHasCell(boolean hasCell) {

this.hasCell = hasCell;

}

public boolean isAddRow() {

return addRow;

}

public void setAddRow(boolean addRow) {

this.addRow = addRow;

}

public int getFinalEmptyRowCount() {

return finalEmptyRowCount;

}

public void setFinalEmptyRowCount(int finalEmptyRowCount) {

this.finalEmptyRowCount = finalEmptyRowCount;

}

public StringBuilder getResponse() {

return response;

}

public void setResponse(StringBuilder response) {

this.response = response;

}

public int getHeaderRow() {

return headerRow;

}

public void setHeaderRow(int headerRow) {

this.headerRow = headerRow;

}

}

XlsReader 

-------------

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.ss.usermodel.Cell;

public class XlsReader {

static List<String> header = null;

public static String readExcel(InputStream fileStream, String sheetName,

ExcelReaderResponseBuilder responseBuilder) {

StringBuilder response = null;

HSSFSheet sheet = null;

try {

// FileInputStream file = new FileInputStream(fileObject);

POIFSFileSystem fs = new POIFSFileSystem(fileStream);

HSSFWorkbook wb = new HSSFWorkbook(fs);

for (int i = 0; i < wb.getNumberOfSheets(); i++) {

sheet = wb.getSheetAt(i);

if (sheet.getSheetName().equals(sheetName)) {

HSSFRow row;

HSSFCell cell;

header = new ArrayList<String>();

response = new StringBuilder();

int rows; // No of rows

rows = sheet.getPhysicalNumberOfRows();

int cols = 0; // No of columns

int tmp = 0;

// This trick ensures that we get the data properly even if

// it

// doesn't start from first few rows

for (int j = 0; j < 10 || j < rows; j++) {

row = sheet.getRow(j);

if (row != null) {

tmp = sheet.getRow(j).getPhysicalNumberOfCells();

if (tmp > cols)

cols = tmp;

}

}

responseBuilder.getResponse().append(

"<worksheet sheetName=\"" + sheetName + "\">");

for (int r = 0; r < rows; r++) {

String formattedValue = "";

row = sheet.getRow(r);

if (row != null) {

if (r != 0) {

responseBuilder.getResponse().append("<row>");

}

for (int c = 0; c < cols; c++) {

cell = row.getCell((short) c);

if (cell != null) {

// If the header row is processing get the

// column

// name

if (r == 0) {

formattedValue = cell

.getStringCellValue();

if (formattedValue.trim().indexOf(' ') == -1

&& formattedValue.trim()

.indexOf('/') == -1

&& formattedValue.indexOf("#") == -1) {

header.add(formattedValue.trim());

} else {

String tempValue = formattedValue

.trim()

.replaceAll(" ", "_")

.replaceAll("/", "_Or_")

.replaceAll("#", "_No");

header.add(tempValue.trim());

}

} else {

// Adding cell value to XML string

switch (cell.getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

formattedValue = cell

.getNumericCellValue() + "";

break;

case Cell.CELL_TYPE_STRING:

formattedValue = cell

.getStringCellValue();

break;

case Cell.CELL_TYPE_BLANK:

formattedValue = "";

break;

default:

formattedValue = "";

break;

}

String result = "<" + header.get(c)

+ "><![CDATA["

+ formattedValue.trim()

+ "]]></" + header.get(c) + ">";

responseBuilder.getResponse().append(

result);

}

}

}

}

if (r != 0) {

responseBuilder.getResponse().append("</row>");

}

}

responseBuilder.getResponse().append("</worksheet>");

} else {

continue;

}

}

} catch (Exception ioe) {

responseBuilder.setStatus(0);

responseBuilder.setErrorCode("100");

responseBuilder.setErrorMessage(ioe.getMessage() + "\n"

+ ioe.getStackTrace());

}

if (responseBuilder.getResponse() == null

|| responseBuilder.getStatus() == 0) {

response.append(

"<?xml version=\"1.0\" encoding=\"us-ascii\"?> <response><data>")

.append("</data><status>")

.append(responseBuilder.getStatus())

.append("</status><errorcode>")

.append(responseBuilder.getErrorCode())

.append("</errorcode><errormessage>")

.append(responseBuilder.getErrorMessage())

.append("</errormessage></response>");

} else {

response.append(

"<?xml version=\"1.0\" encoding=\"us-ascii\"?><response><data>")

.append(responseBuilder.getResponse().toString())

.append("</data><status>")

.append(responseBuilder.getStatus())

.append("</status><errorcode>")

.append(responseBuilder.getErrorCode())

.append("</errorcode><errormessage>")

.append(responseBuilder.getErrorMessage())

.append("</errormessage><headers>");

for (int i = 0; i < header.size(); i++) {

response.append("<header>" + header.get(i) + "</header>");

}

response.append("</headers></response>");

}

responseBuilder = null;

return response.toString();

}

public static String getSheetNames(InputStream fileStream) throws Exception {

StringBuilder response = null;

HSSFSheet sheet = null;

POIFSFileSystem fs = new POIFSFileSystem(fileStream);

HSSFWorkbook wb = new HSSFWorkbook(fs);

response = new StringBuilder();

response.append("<response>");

for (int i = 0; i < wb.getNumberOfSheets(); i++) {

sheet = wb.getSheetAt(i);

response.append("<worksheet>" + sheet.getSheetName()

+ "</worksheet>");

}

response.append("</response>");

return response.toString();

}

public static List<String> getHeader() {

return header;

}

public static void setHeader(List<String> header) {

XlsReader.header = header;

}

}