// returns the data from a cell
public String getCellData(String sheetName, int colNum, int rowNum) {
try {
if (rowNum <= 0) return "";
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(rowNum - 1);
if (row == null) return "";
cell = row.getCell(colNum);
if (cell == null) return "";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue();
else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String cellText = String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of M/D/YY
double d = cell.getNumericCellValue();
Calendar cal = Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
cellText =
cal.get(Calendar.MONTH) + 1 + "/" + cal.get(Calendar.DAY_OF_MONTH) + "/" + cellText;
// System.out.println(cellText);
}
return cellText;
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
else return String.valueOf(cell.getBooleanCellValue());
} catch (Exception e) {
e.printStackTrace();
return "row " + rowNum + " or column " + colNum + " does not exist in xls";
}
}
public static void copyCell(XSSFCell oldCell, XSSFCell newCell) {
try {
switch (oldCell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
} catch (Exception ex) {
System.out.println("Error in writing Cell in reject excel file :: " + ex.getMessage());
ex.printStackTrace();
}
}
private void processCell(Element tr, XSSFCell cell) {
int num = cell.getSheet().getNumMergedRegions();
// System.out.println(cell.getCTCell());
for (int i = 0; i < num; i++) {
CellRangeAddress c = cell.getSheet().getMergedRegion(i);
System.out.println(c.getFirstColumn());
;
System.out.println(c.getLastColumn());
System.out.println(c.getFirstRow());
System.out.println(c.getLastRow());
System.out.println();
System.out.println(cell.getRowIndex());
System.out.println(cell.getColumnIndex());
System.out.println("\n\n\n");
// System.out.println(cra);
}
// System.exit(0);
Element td = htmlDocumentFacade.createTableCell();
Object value;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
value = "\u00a0";
break;
case Cell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getNumericCellValue();
break;
default:
value = cell.getRichStringCellValue();
break;
}
if (value instanceof XSSFRichTextString) {
processCellStyle(td, cell.getCellStyle(), (XSSFRichTextString) value);
td.setTextContent(value.toString());
} else {
processCellStyle(td, cell.getCellStyle(), null);
td.setTextContent(value.toString());
}
// System.err.println(value);
tr.appendChild(td);
}
/**
* 获取每个cell的数据
*
* @param cell
* @return object
*/
public Object getXssCellData(Object cell) {
Object cellData = "";
if (cell != null) {
XSSFCell xssCell = (XSSFCell) cell;
if (xssCell != null) {
switch (xssCell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
cellData = xssCell.getStringCellValue();
if (cellData != null) {
cellData = String.valueOf(cellData).trim();
}
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(xssCell)) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
cellData = df.format(xssCell.getDateCellValue());
df = null;
} else {
DecimalFormat df = new DecimalFormat("0");
cellData = df.format(xssCell.getNumericCellValue());
df = null;
}
break;
case XSSFCell.CELL_TYPE_BLANK:
cellData = "";
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
cellData = String.valueOf(xssCell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
xssCell = null;
}
return cellData;
}
/**
* @param oldCell
* @param newCell
* @param styleMap
*/
public static void copyCell(
XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
if (styleMap != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
int stHashCode = oldCell.getCellStyle().hashCode();
XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch (oldCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}