Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package javacommon.excel; import java.text.NumberFormat; import java.util.ArrayList; import java.util.List; import java.util.Vector; import org.apache.commons.lang.ArrayUtils; import org.apache.commons.lang.time.DateFormatUtils; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; /** * Excel ? ?03?07Excel * * @author xc */ public class ExcelReader implements Reader { /** * Excel? */ private Workbook workbook; /** * */ private int sheets; public ExcelReader(Workbook workbook) { this.workbook = workbook; init(); } /** * ? * * @return */ public int getSheets() { return sheets; } /** * ?ExcelSheet * * @return */ public int getTotalRows() { int total = 0; for (int i = 0; i < sheets; i++) { total += getTotalRows(i); } return total; } /** * ?? * * @param sheetIndex 0 * @return */ public int getTotalRows(int sheetIndex) { if (sheetIndex < sheets) { return workbook.getSheetAt(sheetIndex).getLastRowNum() + 1; } return 0; } /** * * @param sheetIndex * @param row * @return */ public int getMaxColumn(int sheetIndex, int row) { int cols = 0; if (sheetIndex < getSheets() && row < getTotalRows(sheetIndex)) { return workbook.getSheetAt(sheetIndex).getRow(row).getLastCellNum() + 1; } return cols; } /** * ? * * @param sheetIndex * @return */ public int getMaxColumn(int sheetIndex) { if (sheetIndex >= sheets) { return 0; } int maxCol = 0; Sheet sheet = workbook.getSheetAt(sheetIndex); int maxRows = sheet.getLastRowNum(); int cols = 0; Row row = null; for (int i = 0; i < maxRows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getLastCellNum(); if (cols > maxCol) { maxCol = cols; } } } return maxCol + 1; } /** * ? * * @param sheetIndex * @param rowIndex * @return */ public boolean isBlankRow(int sheetIndex, int rowIndex) { Row row = getRow(sheetIndex, rowIndex); if (row == null) { return true; } int cols = row.getLastCellNum(); // boolean isBlank = true;//? String str = null; for (int i = 0; i <= cols; i++) { str = getCellStringValue(row.getCell(i)); if (str != null && str.trim().length() > 0) { isBlank = false; break; } } return isBlank; } /** * ? * @param sheetIndex * @return */ public List<Integer> getColumnWidths(int sheetIndex) { int rowCount = getTotalRows(sheetIndex); if (rowCount > 0) { Row row = workbook.getSheetAt(sheetIndex).getRow(0); int cols = row.getLastCellNum(); // List<Integer> result = new ArrayList<Integer>(cols); for (int i = 0; i < cols; i++) { result.add(getColumnWidth(sheetIndex, i)); } return result; } return null; } /** * ? * @param sheetIndex * @param columnIndex * @return */ public int getColumnWidth(int sheetIndex, int columnIndex) { return workbook.getSheetAt(sheetIndex).getColumnWidth(columnIndex) * 8 / 256; } /** * ?Excel? Null * * @param sheetIndex 0 * @param rowIndex 0 * @return ? */ public List<String> getRowStingData(int sheetIndex, int rowIndex) { List<String> result = new Vector<String>(); Row row = getRow(sheetIndex, rowIndex); if (row == null) { return null; } int cols = row.getLastCellNum(); // boolean isBlank = true;//? String str = null; for (int i = 0; i < cols; i++) { str = getCellStringValue(row.getCell(i)); if (str != null && str.trim().length() > 0) { isBlank = false; } result.add(str); } return isBlank ? null : result; } /** * ?Excel? * * @param sheetIndex 0 * @param rowIndex 0 * @param colBegin 0 * @param colEnd 0 * @return ? */ public List<String> getRowStingData(int sheetIndex, int rowIndex, int colBegin, int colEnd) { List<String> result = new Vector<String>(); Row row = getRow(sheetIndex, rowIndex); if (row == null) { return null; } int cols = row.getLastCellNum(); // if (colEnd > cols) colEnd = cols; String str = null; int i = colBegin; int emptyCount = 0; for (i = 0; i <= colEnd; i++) { Cell cell = row.getCell(i); if (cell != null) { str = getCellStringValue(cell); if ("".equals(str)) { emptyCount++; } } else { emptyCount++; str = ""; } result.add(str); } if (emptyCount == colEnd + 1) { return null; } return result; } /** * ?Excel? Null * * @param sheetIndex 0 * @param rowIndex 0 * @return ? */ public List<Object> getRowData(int sheetIndex, int rowIndex) { Row row = getRow(sheetIndex, rowIndex); if (row == null) { return null; } List<Object> result = new Vector<Object>(); int cols = row.getLastCellNum(); // boolean isBlank = true; //? Object obj = null; for (int i = 0; i <= cols; i++) { obj = getCellValue(row.getCell(i)); result.add(obj); if (obj != null) { isBlank = false; } } return isBlank ? null : result; } /** * ??? * * @param sheetIndex 0 * @param rowIndex 0 * @param columnIndex 0 * @return */ public Object getCellValue(int sheetIndex, int rowIndex, int columnIndex) { Row row = getRow(sheetIndex, rowIndex); if (row != null && columnIndex <= row.getLastCellNum()) { return getCellValue(row.getCell(columnIndex)); } return null; } /** * ??? * * @param sheetIndex 0 * @param rowIndex 0 * @param columnIndex 0 * @return */ public String getCellStringValue(int sheetIndex, int rowIndex, int columnIndex) { Row row = getRow(sheetIndex, rowIndex); if (row != null && columnIndex <= row.getLastCellNum()) { return getCellStringValue(row.getCell(columnIndex)); } return null; } /** * ?Excel * * @param in */ private void init() { sheets = this.workbook.getNumberOfSheets(); } /** * ??? * * @param c ? * @return */ private String getCellStringValue(Cell c) { if (c == null) { return ""; } String value = null; NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(12); switch (c.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = String.valueOf(c.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(c)) { return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue()); } else if ("@".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if ("General".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) { value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()), c.getCellStyle().getDataFormatString()); } else { value = nf.format(c.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = c.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: value = c.getCellFormula(); break; } return value == null ? "" : value.trim(); } /** * ?Excel? Null * * @param sheetIndex 0 * @param rowIndex 0 * @return ? */ public List<String> getRowStingFormatData(int sheetIndex, int rowIndex) { List<String> result = new Vector<String>(); Row row = getRow(sheetIndex, rowIndex); if (row == null) { return null; } int cols = row.getLastCellNum(); // boolean isBlank = true;//? String str = null; for (int i = 0; i < cols; i++) { str = getCellStringFormatValue(row.getCell(i)); if (str != null && str.trim().length() > 0) { isBlank = false; } result.add(str); } return isBlank ? null : result; } /** * ??? * * @param c ? * @return */ private String getCellStringFormatValue(Cell c) { if (c == null) { return ""; } String value = null; NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(12); switch (c.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = String.valueOf(c.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(c)) { return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue()); } else if ("@".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if ("General".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) { value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()), c.getCellStyle().getDataFormatString()); } else { value = nf.format(c.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = c.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: return c.getCellFormula(); } return value == null ? "" : value.trim(); } /** * ??? * * @param c ? * @return */ private Object getCellValue(Cell c) { if (c == null) { return null; } switch (c.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return c.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(c)) { return c.getDateCellValue(); } return c.getNumericCellValue(); case Cell.CELL_TYPE_STRING: return c.getStringCellValue(); case Cell.CELL_TYPE_FORMULA: return c.getCellFormula(); } return null; } /** * ?Excel * * @param sheetIndex 0 * @param rowIndex 0 * @return */ private Row getRow(int sheetIndex, int rowIndex) { if (sheetIndex < sheets) { Sheet sheet = workbook.getSheetAt(sheetIndex); if (rowIndex <= sheet.getLastRowNum()) { return sheet.getRow(rowIndex); } } return null; } }