Excel reader using Java:
Required Jars:
Implementation Code:
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)");
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";
}
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;
}
}
