public String getCellContent(Cell cell) {
DecimalFormat df = new DecimalFormat("0");
if (cell == null) {
return "";
}
int type = cell.getCellType();
String value = "";
switch (type) {
case Cell.CELL_TYPE_FORMULA:
try {
value = String.valueOf(df.format(cell.getNumericCellValue())).toString().trim();
} catch (Exception e) {
log.info("处理公式单元格失败！");
}
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString().trim();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue()).toString().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
value = String.valueOf(df.format(cell.getNumericCellValue())).toString().trim();
if (value.endsWith(".0")) {
value = value.substring(0, value.length() - 2).trim();
}
break;
default:
value = cell.toString().trim();
break;
}
return value;
}
private String text(Row row, int pos) {
Cell cell = row.getCell(pos);
if (cell != null) {
String s = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (pos == INNLAND_POSTNUMMER) {
s = String.format("%04.0f", cell.getNumericCellValue());
} else if (pos == INNLAND_KONTONR) {
s = String.format("%011.0f", cell.getNumericCellValue());
} else {
s = String.format("%1.0f", cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA:
s = this.formatter.formatCellValue(cell, this.evaluator);
break;
default:
s = cell.getStringCellValue();
}
return s.trim();
} else {
return "";
}
}
public static String getValue(Cell cell) {
String content = null;
if (cell != null) {
switch (cell.getCellType()) {
case 0:
double d = cell.getNumericCellValue();
if (d == (long) d) content = String.valueOf((long) d);
else {
content = String.valueOf(d);
}
break;
case 1:
content = cell.getStringCellValue();
break;
case 2:
double dd = cell.getNumericCellValue();
if (dd == (long) dd) content = String.valueOf((long) dd);
else {
content = String.valueOf(dd);
}
break;
case 3:
content = "";
break;
case 4:
content = String.valueOf(cell.getBooleanCellValue());
}
}
return content;
}
private void generateServicesRegion(Workbook wb, StringBuilder sb) {
// Get the Services Sheet
Sheet sheet = wb.getSheet("Services");
Iterator<Row> rowIt = sheet.rowIterator();
// Ignore the Header row
rowIt.next();
while (rowIt.hasNext()) {
Row row = rowIt.next();
Cell cellId = row.getCell(0);
if (cellId != null) {
int id = (int) cellId.getNumericCellValue();
Cell cellName = row.getCell(1);
String name = cellName.getStringCellValue().replaceAll("\"", "'");
Cell cellDescription = row.getCell(2);
String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
Cell cellPrivateData = row.getCell(3);
Cell cellPartOf = row.getCell(4);
sb.append("Service S" + id + " {");
sb.append("\n");
sb.append("\tName \"" + name + "\"");
sb.append("\n");
sb.append("\tDescription \"" + description + "\"");
sb.append("\n");
if (cellPrivateData.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int privateData = (int) cellPrivateData.getNumericCellValue();
sb.append("\tRefersTo PrivateData PD" + privateData);
sb.append("\n");
} else if (cellPrivateData.getCellType() == Cell.CELL_TYPE_STRING) {
String privateData = cellPrivateData.getStringCellValue();
if (privateData.equals("All")) {
sb.append("\tRefersTo PrivateData All");
} else {
sb.append("\tRefersTo PrivateData ");
for (String s : privateData.split(", ")) {
sb.append("PD" + s + ",");
}
// Delete last ','
sb.deleteCharAt(sb.length() - 1);
}
sb.append("\n");
}
if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int partOf = (int) cellPartOf.getNumericCellValue();
sb.append("\tService_Part S" + partOf);
sb.append("\n");
}
sb.append("}");
sb.append("\n\n");
} else break;
}
}
private void generateRecipientsRegion(Workbook wb, StringBuilder sb) {
// Get the Recipients Sheet
Sheet sheet = wb.getSheet("Recipients");
Iterator<Row> rowIt = sheet.rowIterator();
// Ignore the Header row
rowIt.next();
while (rowIt.hasNext()) {
Row row = rowIt.next();
Cell cellId = row.getCell(0);
if (cellId != null) {
if (cellId.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int id = (int) cellId.getNumericCellValue();
Cell cellDescription = row.getCell(1);
String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
Cell cellScope = row.getCell(2);
String scope = cellScope.getStringCellValue();
if (scope.contains("/")) {
scope = "Internal/External";
} else {
scope = scope.substring(0, 1).toUpperCase() + scope.substring(1);
}
Cell cellType = row.getCell(3);
String type = cellType.getStringCellValue();
if (type.contains("/")) {
type = "Individual/Organization";
} else {
type = type.substring(0, 1).toUpperCase() + type.substring(1);
}
Cell cellPartOf = row.getCell(4);
sb.append("Recipient R" + id + " {");
sb.append("\n");
sb.append("\tName \"" + description + "\"");
sb.append("\n");
sb.append("\tDescription \"" + description + "\"");
sb.append("\n");
if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int partOf = (int) cellPartOf.getNumericCellValue();
sb.append("\tRecipient_Part R" + partOf);
sb.append("\n");
}
sb.append("\tScope " + scope);
sb.append("\n");
sb.append("\tType " + type);
sb.append("\n}");
sb.append("\n\n");
}
} else break;
}
}
private static List<Product> readProductFile(String filePath) {
List<Product> productList = new ArrayList<Product>();
try {
FileInputStream file = new FileInputStream(FILE_PATH);
// Use XSSF for xlsx format, for xls use HSSF
Workbook workbook = new HSSFWorkbook(file);
int numberOfSheets = workbook.getNumberOfSheets();
System.out.println("Number of sheets in the workbook : " + numberOfSheets);
String productType = null;
Sheet firstSheet = workbook.getSheetAt(0);
for (Row myRow : firstSheet) {
Product product = new Product();
Boolean skipProduct = Boolean.FALSE;
for (Cell myCell : myRow) {
switch (myCell.getColumnIndex()) {
case 0:
product.setProductName(myCell.getRichStringCellValue().getString().trim());
break;
case 1:
product.setSellingRate(myCell.getNumericCellValue());
break;
case 2:
product.setCostRate(myCell.getNumericCellValue());
break;
case 3:
if (myCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
product.setProductCode(String.valueOf((int) myCell.getNumericCellValue()));
else product.setProductCode(myCell.getStringCellValue().trim());
break;
case 4:
if (myCell.getRichStringCellValue().getString() != null) {
productType = myCell.getRichStringCellValue().getString().trim();
skipProduct = Boolean.TRUE;
} else skipProduct = Boolean.FALSE;
break;
}
}
if (!skipProduct) {
product.setProductType(productType);
productList.add(product);
}
}
file.close();
} catch (Exception ex) {
ex.printStackTrace();
}
return productList;
}
// ����Excel
public String ExcelInto() throws Exception {
String directory = "/file";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
File target = UploadFile.Upload(uploadFile, uploadFileFileName, targetDirectory);
List<Dise> sList = new ArrayList<Dise>();
excelFile = new FileInputStream(target);
Workbook wb = new HSSFWorkbook(excelFile);
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum() + 1;
for (int i = 1; i < rowNum; i++) {
Dise dise = new Dise();
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
for (int j = 0; j < cellNum; j++) {
Cell cell = row.getCell(j);
String cellValue = null;
switch (cell.getCellType()) { // �ж�excel��Ԫ�����ݵĸ�ʽ�����������ת�����Ա������ݿ�
case 0:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 3:
cellValue = cell.getStringCellValue();
break;
case 4:
cellValue = cell.getStringCellValue();
break;
}
switch (j) { // ͨ���������ж϶�Ӧ������ֶ�
case 1:
dise.setName(cellValue);
break;
case 2:
dise.setA(cellValue);
break;
case 3:
dise.setB(cellValue);
break;
case 4:
dise.setC(cellValue);
break;
}
}
sList.add(dise);
}
DiseService.add(sList);
return "listAll";
}
public static void ParseXLSFormat(String file_name, String output_file_name) throws IOException {
FileInputStream fileInputStream = new FileInputStream(file_name);
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
HSSFSheet worksheet = workbook.getSheet("T_Auto");
BufferedWriter out = new BufferedWriter(new FileWriter(output_file_name));
boolean first = true;
StringBuffer result = new StringBuffer();
Iterator<Row> rowIterator = worksheet.iterator();
rowIterator.next();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String automaton = row.getCell(AUTOMATA).getStringCellValue();
Cell from_cell = row.getCell(FROM);
int from;
if (from_cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
from = (int) from_cell.getNumericCellValue();
} else {
from = Integer.parseInt(from_cell.getStringCellValue());
}
Cell to_cell = row.getCell(TO);
int to;
if (to_cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
to = (int) to_cell.getNumericCellValue();
} else {
to = Integer.parseInt(to_cell.getStringCellValue());
}
String events = row.getCell(EVENTS).getStringCellValue();
String condition = row.getCell(CONDITION).getStringCellValue();
String actions = row.getCell(ACTIONS).getStringCellValue();
if (first) {
first = false;
} else {
result.append(NEWLINE);
}
result.append(
automaton + NEWLINE + from + NEWLINE + to + NEWLINE + events + NEWLINE + condition
+ NEWLINE + actions);
}
fileInputStream.close();
out.write(result.toString());
out.close();
}
/** 从Excel导入到TableBean */
public void doImport() {
int rowNum = sheet.getLastRowNum() + 1;
int columnNum = 0;
for (int i = 0; i < rowNum; i++) {
if (sheet.getRow(i) != null) {
int last = sheet.getRow(i).getLastCellNum();
columnNum = last > columnNum ? last : columnNum;
}
}
tableBean = new TableBean(rowNum, columnNum);
Collection<CellBean> cellBeans = new ArrayList<CellBean>();
for (int r = startRow; r < rowNum; r++) {
Row row = sheet.getRow(r);
if (row != null) {
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
Integer type = forceCellType.get(c);
if (type != null) {
cell.setCellType(type);
}
String cellValue = null;
if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
cellValue = cell.getBooleanCellValue() + "";
} else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
try {
cellValue = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getRichStringCellValue()).trim();
}
} else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (DateUtil.isCellDateFormatted(cell)) {
Date date2 = cell.getDateCellValue();
SimpleDateFormat dff = new SimpleDateFormat(dateFormat);
cellValue = dff.format(date2); // 日期转化
} else cellValue = String.valueOf(cell.getNumericCellValue());
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
cellValue = cell.getStringCellValue();
}
if (cellValue != null && cellValue instanceof String) {
cellValue = cellValue.toString().trim();
}
CellBean cellBean = new CellBean(cellValue, r, c);
cellBean.setCell(cell);
cellBeans.add(cellBean);
}
}
}
}
tableBean.setCellBeans(cellBeans);
}
public String parseExcelData(InputStream is) {
try {
System.out.println("--> open workbook");
workbook = new XSSFWorkbook(is);
System.out.println("start get sheet");
// Taking first sheet from the workbook
// XSSFSheet sheet = workbook.getSheetAt(0);
Sheet sheet = workbook.getSheetAt(0);
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
currentString = new StringBuilder();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
bytesRead++;
currentString.append(cell.getBooleanCellValue() + "\t");
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
bytesRead++;
currentString.append(cell.getNumericCellValue() + "\t");
System.out.print(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
bytesRead++;
currentString.append(cell.getStringCellValue() + "\t");
System.out.print(cell.getStringCellValue());
break;
}
}
currentString.append("\n");
}
is.close();
} catch (IOException e) {
LOG.error("IO Exception : File not found " + e);
}
return currentString.toString();
}
/* (non-Javadoc)
* @see com.his.common.excel.AbstractFileImporter#getInteger(java.lang.Integer, int)
*/
@Override
public Integer getInteger(Integer rowNums, int index) {
Cell cell = getCell(rowNums, index);
if (cell == null) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return Integer.parseInt(cell.getStringCellValue().trim());
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return (int) cell.getNumericCellValue();
}
return (int) cell.getNumericCellValue();
}
public static Object getCellValue(Cell cell) {
Object value = new Object();
// Prevent a NullPointerException
if (cell != null) {
if (cell.getHyperlink() != null) {
Workbook workbook = new XSSFWorkbook();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Hyperlink link = cell.getHyperlink();
String address = link.getAddress();
if (logger.isTraceEnabled()) {
logger.trace(
"Found a Hyperlink to "
+ cell.getHyperlink().getAddress()
+ " in cell "
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex());
}
cell = evaluator.evaluateInCell(cell);
}
// Depending on the cell type, the value is read using Apache POI methods
switch (cell.getCellType()) {
// String are easy to handle
case Cell.CELL_TYPE_STRING:
logger.trace("Found string " + cell.getStringCellValue());
value = cell.getStringCellValue();
break;
// Since date formatted cells are also of the numeric type, this needs to be processed
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = SimpleDateFormat.getDateInstance();
logger.trace("Found date " + df.format(date));
value = date;
} else {
logger.trace("Found general number " + cell.getNumericCellValue());
value = cell.getNumericCellValue();
}
break;
}
} else {
logger.trace("Found cell with NULL value");
}
return value;
}
protected boolean isCellEmpty(Cell cell) {
if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return true;
}
try {
String cellStringValue = cell.getStringCellValue();
if (!cellStringValue.isEmpty()) {
return false;
}
} catch (Exception e) {
// Do nothing. Its just not a String cell.
}
try {
Double cellNumericValue = cell.getNumericCellValue();
if (0 != cellNumericValue) {
// from the poi documentation 'For blank cells we return a 0.'. Groan! So since a zero is a
// valid value, how do I test for an empty numeric cell?
return false;
}
} catch (Exception e) {
// Do nothing. Its just not a Numeric cell.
}
// Its either empty or a not a string or numeric.
return true;
}
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(Cell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
private static void retrieveSheetInformation(Sheet sheet) {
// every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
Row row = rowIterator.next();
// Every row has columns, get the column iterator and
// iterate over them
Iterator<Cell> cellIterator = row.cellIterator();
StringBuffer sb = new StringBuffer();
while (cellIterator.hasNext()) {
// Get the Cell object
Cell cell = cellIterator.next();
// check the cell type and process accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
sb.append(cell.getStringCellValue()).append(" $ ");
break;
case Cell.CELL_TYPE_NUMERIC:
sb.append(cell.getNumericCellValue()).append(" ");
}
} // end of cell iterator
System.out.println(sb.toString());
} // end of rows iterator
}
public static Object getCellValue(Cell cell) {
if (cell == null) return null;
int celltype = cell.getCellType();
if (celltype == Cell.CELL_TYPE_NUMERIC) return cell.getNumericCellValue();
if (celltype == Cell.CELL_TYPE_STRING) {
String value = cell.getStringCellValue();
if ("null".equals(value)) {
value = "";
}
if (!StringUtil.isEmpty(value)) {
value = value.replaceAll(" ", "");
value = value.replaceAll(" ", "");
}
return value;
}
if (celltype == Cell.CELL_TYPE_FORMULA) {
String value = cell.getStringCellValue();
if ("null".equals(value)) {
value = "";
}
return value;
}
if (celltype == cell.CELL_TYPE_BLANK) return "";
if (celltype == cell.CELL_TYPE_ERROR) return "";
return "";
}
/** 把单元格内的类型转换至String类型 */
private String ConvertCellStr(Cell cell, String cellStr) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
// 读取String
cellStr = cell.getStringCellValue().toString();
break;
case Cell.CELL_TYPE_BOOLEAN:
// 得到Boolean对象的方法
cellStr = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 先看是否是日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 读取日期格式
cellStr = cell.getDateCellValue().toString();
} else {
// 读取数字
cellStr = String.valueOf(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA:
// 读取公式
cellStr = cell.getCellFormula().toString();
break;
}
return cellStr;
}
private void generateEnforcementsRegion(Workbook wb, StringBuilder sb) {
// Get the Enforcements Sheet
Sheet sheet = wb.getSheet("Enforcements");
Iterator<Row> rowIt = sheet.rowIterator();
// Ignore the Header row
rowIt.next();
while (rowIt.hasNext()) {
Row row = rowIt.next();
Cell cellId = row.getCell(0);
if (cellId != null) {
int id = (int) cellId.getNumericCellValue();
Cell cellName = row.getCell(1);
String name = cellName.getStringCellValue().replaceAll("\"", "'");
Cell cellDescription = row.getCell(2);
String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
Cell cellType = row.getCell(3);
String type = cellType.getStringCellValue();
sb.append("Enforcement En" + id + " {");
sb.append("\n");
sb.append("\tName \"" + name + "\"");
sb.append("\n");
sb.append("\tDescription \"" + description + "\"");
sb.append("\n");
sb.append("\tType " + type);
sb.append("\n}");
sb.append("\n\n");
} else break;
}
}
private Object[] readLineData(Row row) {
if (row == null) return null;
List<Object> dataList = new ArrayList<Object>();
for (Cell cell : row) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
// ???Boolean????????
dataList.add(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// ?????????
dataList.add(cell.getDateCellValue());
} else {
// ???????
dataList.add(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
// ???String
dataList.add(cell.getRichStringCellValue().toString());
break;
case Cell.CELL_TYPE_BLANK:
// ???
dataList.add("");
break;
}
}
return dataList.toArray();
}
/**
* * Transforms the contents of a given cell from the POI-model to a {@link Formula} and sets the
* formula as content of the given cell from the internal model.
*
* @param cell The given cell from the internal model.
* @param poiCell The given cell form the POI-model.
*/
protected void transformFormulaContent(Cell cell, org.apache.poi.ss.usermodel.Cell poiCell) {
// Create formula and add to cell.
Formula formula = new Formula();
cell.setFormula(formula);
formula.setCell(cell);
formula.setFormulaString(poiCell.getCellFormula());
// Set formula result type.
switch (poiCell.getCachedFormulaResultType()) {
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
formula.setResultType(CellContentType.NUMERIC);
cell.setNumericContent(poiCell.getNumericCellValue());
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
formula.setResultType(CellContentType.TEXT);
cell.setTextContent(poiCell.getStringCellValue());
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
formula.setResultType(CellContentType.BOOLEAN);
cell.setBooleanContent(poiCell.getBooleanCellValue());
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
formula.setResultType(CellContentType.ERROR);
cell.setErrorContent(FormulaError.forInt(poiCell.getErrorCellValue()).toString());
break;
default:
break;
}
// Set formula content.
Stack<Ptg> ptgs = getPtgStackFor(poiCell);
transform(cell, ptgs, formula, ptgs.size());
}
public static void main(String args[]) throws Exception {
File file = new File("d:/delete/createworkbook.xlsx");
FileInputStream fis = new FileInputStream(file);
if (file.isFile() && file.exists()) {
// Get the workbook instance for XLSX file
XSSFWorkbook workbook = new XSSFWorkbook(fis);
System.out.println("openworkbook.xlsx file open successfully.");
// get worksheet, 0 based
XSSFSheet sheet = workbook.getSheetAt(0);
// get rows
Iterator<Row> it = sheet.iterator();
while (it.hasNext()) {
Row row = (XSSFRow) it.next();
// get cells
Iterator<Cell> cellIt = row.cellIterator();
while (cellIt.hasNext()) {
Cell cell = cellIt.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + " \t\t ");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + " \t\t ");
break;
}
}
}
} else {
System.out.println("Error to open openworkbook.xlsx file.");
}
}
/**
* 由于Excel当中的单元格Cell存在类型,若获取类型错误就会产生异常, 所以通过此方法将Cell内容全部转换为String类型
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String str = null;
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
str = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
str = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
str = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
str = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue());
} else {
str = String.valueOf((long) cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
str = String.valueOf(cell.getStringCellValue());
break;
default:
str = null;
break;
}
}
return StringUtils.trim(str);
}
@Override
public String readValue(String valueCode) {
String retVal = null;
try {
if (headers.containsKey(valueCode)) {
Cell cell = dataRow.getCell(headers.get(valueCode), Row.RETURN_BLANK_AS_NULL);
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
retVal = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
retVal = String.valueOf(cell.getNumericCellValue());
break;
}
}
}
} catch (Exception e) {
logger.error(String.format("Failed to get column data: %s", valueCode));
}
return retVal;
}
/* (non-Javadoc)
* @see com.his.common.excel.AbstractFileImporter#getDouble(java.lang.Integer, int)
*/
@Override
public Double getDouble(Integer rowNums, int index) {
Cell cell = getCell(rowNums, index);
if (cell == null) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
if (StringUtils.isBlank(cell.getStringCellValue())) {
return 0.0;
} else {
return Double.parseDouble(cell.getStringCellValue().trim());
}
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return cell.getNumericCellValue();
}
return cell == null ? null : cell.getNumericCellValue();
}
private Object getNumericCellValue(final Cell cell) {
Object cellValue;
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = new Date(cell.getDateCellValue().getTime());
} else {
cellValue = cell.getNumericCellValue();
}
return cellValue;
}
@SuppressWarnings("static-access")
private static String getValue(Cell cell) {
if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
return NumberToTextConverter.toText(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
/**
* Get Numeric value from cell.
*
* @param cell
* @return
*/
private String getNumericValue(Cell cell) {
String ret = "";
double d = cell.getNumericCellValue();
if (d == (int) d) {
ret = ret + (int) d;
} else {
ret = ret + d;
}
return ret;
}
public Object getDataFromCell(final Cell cell) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return cell.getNumericCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
}
return null;
}
static String[] getStringArrVall(Cell val, String split) {
if (val != null) {
if (val.getCellType() == Cell.CELL_TYPE_STRING) {
if (val.getStringCellValue().equals(" ")) return null;
return val.getStringCellValue().split(split);
} else if (val.getCellType() == Cell.CELL_TYPE_NUMERIC)
return ("" + val.getNumericCellValue()).split(",");
}
return null;
}
private static InterviewInstance getInstance(Row row) throws ParseException {
// extract the cells needed from this row
Cell interview_id_cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell line_cell = row.getCell(1, Row.CREATE_NULL_AS_BLANK);
Cell speaker_cell = row.getCell(2, Row.CREATE_NULL_AS_BLANK);
Cell comment_cell = row.getCell(3, Row.CREATE_NULL_AS_BLANK);
ArrayList<Cell> category_cells = new ArrayList<>(5);
for (int i = 4; i <= 8; ++i) {
Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
if (cell.getCellType() != Cell.CELL_TYPE_BLANK) category_cells.add(cell);
}
Cell sentiment_cell = row.getCell(9, Row.CREATE_NULL_AS_BLANK);
// verify that all the required information is present
verifyNumericCell(interview_id_cell, "Interview ID field");
verifyNumericCell(line_cell, "Line field");
verifySpeakerCell(speaker_cell);
verifyCellNotEmpty(comment_cell, "Comment cell");
if (category_cells.isEmpty())
throw new ParseException("No category labels given for samples in row " + row.getRowNum(), 0);
Iterator<Cell> row_iter = category_cells.iterator();
ArrayList<Category> categories = new ArrayList<>();
while (row_iter.hasNext()) {
Cell category_cell = row_iter.next();
verifyCategoryCell(category_cell);
categories.add(Category.getCategory(category_cell.getStringCellValue()));
}
// convert the cell into an interviewInstance
InterviewInstance instance =
new InterviewInstance(
(int) interview_id_cell.getNumericCellValue(),
(int) line_cell.getNumericCellValue(),
Speaker.getSpeaker(speaker_cell.getStringCellValue()),
comment_cell.getStringCellValue(),
categories);
return instance;
}
