Generation of the Fibonacci sequence

This blog explains about fibonacci sequence,

Generate and print the first~ terms of the Fibonacci sequence where n~ 1. The first few terms are

                                    0, 1, 2, 3, 5, 8, 13, ...

Each term beyond the first two is derived from the sum of its two nearest predecessors.

Algorithm development

From the definition: new term = preceding term- before preceding term. The last sentence of the problem statement suggests we may be able to use the definition to generate consecutive terms (apart from the first two) iteratively.

            Define:

                                    a as the term before the preceding term

                                    b as the preceding term

                                    c new term

            Then to start with:

                                    a:= 0    first fibonacci number

                                    b := 1  second Fibonacci number

                                    c := a+b  third Fibonacci number (from definition)

            When the new term c has been generated we have the third Fibonacci. To generate the fourth, or next Fibonacci number, we need to apply the same definition again. Before we can make this next computation we  need to make some adjustments. The fourth Fibonacci number is derived

From the sum of the second and third Fibonacci numbers. With regard to the definition the second Fibonacci number has the role of the term before the preceding term and the third Fibonacci number has the role of "the preceding term". Therefore ,before making the next (i.e. the fourth) computation we must ensure that:

            a) new term (i.e. the third) assumes the role of the preceding term,

            (b) and what is currently the preceding term must assume the role of the term before the   

                 preceding term.

            That is,

                        a:= 0                [1]        term before preceding term

                        b := 1              [2]        preceding term

                        c := a+b          [3]        new term

                        a:= b               [4]        term before preceding term becomes preceding term

                        b := c                [5]         preceding term becomes new term

After making step [5] we are in a position where we can use the definition to generate the next Fibonacci number. A way to do this is to loop back to step [3]. Further investigation of steps [3]->[5] indicates they can be placed in a loop to iteratively generate Fibonacci numbers (for n>2). The essential mechanism we could use is:

                                                a:= 0;

                                                b := 1;

                                                i:= 2;

                                                while i<n do

                                                begin

                                                            i:=i+l;

                                                            c := a+b;

                                                            a:= b;

                                                            b = c;

                                                end

As each new term c is Computed, the term before the preceding term, a, loses its relevance to the Calculation of the next Fibonacci number. To restore its relevance assignment is done. At all times only two numbers are relevant to the generation of the next Fibonacci number. In our computation, a third variable, c is introduced. Keep the two variables a and b always relevant. Because the first Fibonacci number becomes irrelevant as soon as the third Fibonacci is computed, start with:

                                                a:= 0;  [1]

                                                b := 1; [2]

                                                a := a+b [3]

Iterate on step [3] to generate successive Fibonacci numbers we run into trouble because we are not changing b. However, after step [3] we know the next (i.e. fourth) Fibonacci number can be generated using,

                                                next:=a+b;

Doing step [4], the old value of b, as defined by step [2], loses its relevance. To keep b  relevant at step [4] we can make the assignment:

                                                b := a+b

The first four steps then become

                                                a:= 0;              [1]

                                                b := 1;             [2]

                                                a := a+p;         [3]       

                                                b := a+b          [4]

After step [4]  find that a and b as defined are correct for generating the fifth Fibonacci number. At this point the old a value becomes irrelevant. Working through several more steps confirms that we can safely iterate on steps [3] and [4] to generate the required sequence. Because the algorithm generates Fibonacci numbers in pairs care must be taken to write out exactly n numbers. The easiest way to do this is to keep the output one step behind the generation phase. The complete algorithm description can now be given.

Algorithm description

1. Prompt and read n, the number of Fibonacci numbers to be generated.

2. Assign first two Fibonacci numbers a and b.

3. Initialize count of number generated.

4. While less than n Fibonacci numbers have been generated do

(a) write out next two Fibonacci numbers;

(b) generate next Fibonacci number keeping a relevant;

(c) generate next Fibonacci number from most recent pair keeping b relevant for next computation;

(d) update count of number of Fibonacci numbers generated, i.

5. If n even then write out last two Fibonacci numbers else write out second last Fibonacci number.

Factorial Computation

This blog will explain about factorial computation algorithm development step by step.

Algorithm development

Start the development of this algorithm by examining the definition of n!

                                    n!=1*2*3*….(n-1)*n for n>=1

                  

and by definition. Computer's arithmetic unit can only multiply two numbers at a time. Applying the factorial definition we get,

                                                           

                                                            0!= 1

                                                            1!=l x l

                                                            2!  1x 2

                                                            3!=l x 2 x 3

                                                            4! = l x 2 x 3 x 4

4! contains all the factors of 3L The only difference is the inclusion of the number 4. n! can always be obtained from (n-1)! by simply multiplying it by n (for n~ 1). That ~is, n! = nx(n-l)! for n.>=1

From step (2) onwards the same process is repeated. For the general (i+ 1)th step,

p:=p*i     (i+1)

This general step can be placed in a loop to iteratively generate n!. This allows to take advantage of the fact that the computer's arithmetic unit can only multiply two numbers at a time. In many ways this problem is very much like the problem of summing a set of n numbers. In the summation problem we performed a set of additions, whereas in this problem we need to generate a set of products. It follows from the general (i+ 1)th step that all factorials for n~ 1

can be generated iteratively. The instance where n = 0 is a special case which must be accounted for directly by the assignment

                                                p:= 1 (by definition of 0!)

The central part of the algorithm for computing n! therefore involves a special initial step followed by n iterative steps.

1. Treat 0! as a special case (p := 1).
2. Build each of the n remaining products p from its predecessor by an iterative process.
3. Write out the value of n factorial.

Algorithm description

1. Establish n, the factorial required where n≥0.

2. Set product p for 0! (special case). Also set product count to zero.

3. While less than n products have been calculated repeatedly do

            (a) increment product count,

            (b) compute the ith product p by multiplying i by the most recent product.

4. Return the result n!.

This algorithm is most usefully implemented as a function that accepts as input a number n and returns as output the value of n! In the Pascal implementation p has been replaced by the variable factor.

Sample code for Factorial:

The below code will compute the factorial for 25.

for (c = 1; c <= 25; c++)
    fact = fact * c;
 
  printf("Factorial of %d = %d\n", n, fact);
 

Happy coding!!!!

Excel writer in java

My previous blog explained how to convert excel file to XMLor Json. Now Do you want to convert your JSON object to excel file. here you go....

Required jar same as excel reader. I have explained in my previous blog . Please check it.

Code for Excel Writer:

import java.io.File;
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 java.util.StringTokenizer;

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 org.apache.poi.xssf.usermodel.XSSFFont;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 * This class is used to create the excel file for the given list of TWObject in
 * the given file path.
 */
public class XlsxWriter {
private SXSSFWorkbook workbook;

/**
* A no-constructor argument is needed. This allows BPM to create an
* instance of this object when a method is invoked from BPM.
*/

public XlsxWriter() {
super();
workbook = new SXSSFWorkbook(100);
}

/**
* This method will handle the input from the calling application and
* creates the excel file for the given input.
* 
* @param path
*            - Location where excel file should get create
* @param object
*            - List of TW object from BPM
* @param sheetName
*            - sheet name in the excel file.
* @throws ExcelWriterException
*/
public File createExcelFile(String path, String columns, String header,
String object) throws ExcelWriterException {

File newFile = null;
try {

List<String> colModelList = new ArrayList<String>();

JSONArray dataValue = new JSONArray(object);

// If the user didnt select any column, default table column order
// will be displayed
if (columns != null && !columns.equalsIgnoreCase("")) {
StringTokenizer colToken = new StringTokenizer(columns, ",");
while (colToken.hasMoreTokens()) {
colModelList.add(colToken.nextToken());
}
} else {
JSONArray columnList = new JSONArray(header);
for (int i = 0; i < columnList.length(); i++) {
colModelList.add(columnList.get(i).toString());
}
}

// Create a worksheet with given input values
addSheet(dataValue, colModelList, "WOAccrual_Report");

// Create an output stream to write the report to.
newFile = new File(path);
OutputStream output = new FileOutputStream(newFile);

// Write the report to the output stream
write(output);

// Finally, save the report
output.close();

} catch (ParseException e) {
System.out.println("");
} catch (FileNotFoundException e) {
System.out.println("");
} catch (Exception e) {
System.out.println("");
}
return newFile;
}

/**
* This method used to create a worksheet and add to workbook.
* 
* @param data
*            - data to be inserted in to worksheet
* @param colModel
*            - This is used to provide the column name.
* @param sheetName
*            - work sheet name.
* @throws ExcelWriterException
*/
private void addSheet(JSONArray data, List<String> colModel,
String sheetName) throws ExcelWriterException {

Sheet sheet = workbook.createSheet(sheetName);
int numCols = colModel.size();
int currentRow = 0;
Row row;

try {

// Create the report header at row 0
row = sheet.createRow(currentRow);
// Loop over all the column beans and populate the report headers
for (int i = 0; i < numCols; i++) {
// Get the header text from the bean and write it to the cell
Cell cell = row.createCell(i);
Font fn = workbook.createFont();
fn.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
CellStyle cs = workbook.createCellStyle();
cs.setFont(fn);
cell.setCellStyle(cs);
cell.setCellValue(colModel.get(i).toString());
}

currentRow++; // increment the spreadsheet row before we step into
// the data

// Write report rows
for (int i = 0; i < data.length(); i++) {
// create a row in the spreadsheet
row = sheet.createRow(currentRow++);
// get the bean for the current row
JSONObject bean = data.getJSONObject(i);

// For each column object, create a column on the current row based on the col model
for (int y = 0; y < numCols; y++) {
Object proertyValue = bean.getString(colModel.get(y));
String value;
if (proertyValue != null) {
value = proertyValue.toString();
} else {
value = "";
}
Cell cell = row.createCell(y);
cell.setCellValue(value);
}
}

// Autosize columns
for (int i = 0; i < numCols; i++) {
sheet.autoSizeColumn((short) i);
}

} catch (Exception e) {
throw new ExcelWriterException(0, e.getMessage());
}

}

/**
* This method will write the values into output stream.
* 
* @param outputStream
* @throws Exception
*/
private void write(OutputStream outputStream) throws Exception {
workbook.write(outputStream);
}


}

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;

}

}