/**
* read excel Xls and add the result into arraylist.
*
* @param sheetName excel sheet name
* @throws RuntimeException
*/
public List<Map<String, String>> excelToList(String sheetName) {
Row firstxlRow = null;
FileInputStream fso = null;
List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
return null;
}
firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
int firstCell = firstxlRow.getFirstCellNum();
int lastCell = firstxlRow.getLastCellNum();
List<String> keyList = new ArrayList<String>();
for (int cNum = firstCell; cNum < lastCell; cNum++) {
if (firstxlRow.getCell(cNum).toString() == null) {
break;
}
keyList.add(firstxlRow.getCell(cNum).toString());
}
for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
xlRow = xlSheet.getRow(i);
List<String> valueList = new ArrayList<String>();
if (xlRow == null) {
break;
}
for (int j = firstCell; j < lastCell; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
valueList.add(null);
continue;
} else {
valueList.add(xlCell.toString());
}
}
paraList.add(creatMap(keyList, valueList));
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
/**
* 获取销售记录
*
* @param productSheet
* @return
*/
private int getSaleRecordsFormXSheet(Sheet salesSheet) {
int count = 0;
if (salesSheet == null) return 0;
try {
int rowStart = salesSheet.getFirstRowNum();
int rowEnd = salesSheet.getLastRowNum();
for (int i = rowStart + 1; i <= rowEnd; i++) {
Row row = salesSheet.getRow(i);
SaleRecord sr = new SaleRecord();
// 日期
String datestr = String.valueOf(getXssCellData(row.getCell(0)));
if ("".equals(datestr)) continue;
Date date = null;
try {
date = new SimpleDateFormat("yyyy-MM-dd").parse(datestr);
} catch (Exception e) {
continue;
}
String struser = String.valueOf(getXssCellData(row.getCell(1)));
// if have not user info discard this record
if ("".equals(struser)) continue;
int user = Integer.parseInt(struser);
String strproduct = String.valueOf(getXssCellData(row.getCell(2)));
// if have not user info discard this record
if ("".equals(strproduct)) continue;
int product = Integer.parseInt(strproduct);
// 购买价格
String pricestr = (String) getXssCellData(row.getCell(3));
double price = 0d;
if (!"".equals(pricestr)) {
price = Double.parseDouble(pricestr);
}
// 购买量
String numstr = (String) getXssCellData(row.getCell(3));
double num = 0d;
if (!"".equals(numstr)) {
num = Double.parseDouble(numstr);
}
sr.setNum(num);
sr.setPrice(price);
sr.setProduct(product);
sr.setUser(user);
sr.setSaledate(date);
simpleDao.createEntity(sr);
count++;
}
} catch (Exception e) {
e.printStackTrace();
log.error("getSaleRecordsFormXSheet exception !");
}
return count;
}
@Override
public int getColumnCount() {
if (config != null) {
return config.getColumnLast() - config.getColumnOffset() + 1;
} else {
Row row = sheet.getRow(sheet.getFirstRowNum());
if (row == null) {
return 0;
} else {
return row.getLastCellNum();
}
}
}
private int getUserFormXSheet(Sheet userSheet) {
int count = 0;
if (userSheet == null) return 0;
try {
int rowStart = userSheet.getFirstRowNum();
int rowEnd = userSheet.getLastRowNum();
for (int i = rowStart + 1; i <= rowEnd; i++) {
Row row = userSheet.getRow(i);
User user = new User();
String strid = String.valueOf(getXssCellData(row.getCell(0)));
if ("".equals(strid)) continue;
int id = Integer.parseInt(strid);
String name = (String) getXssCellData(row.getCell(1));
String address = (String) getXssCellData(row.getCell(2));
String str_borthday = getXssCellData(row.getCell(3)).toString();
Date borthday = null;
if (!"".equals(str_borthday)) {
try {
borthday = new SimpleDateFormat("yyyy-MM-dd").parse(str_borthday);
} catch (Exception e) {
}
}
String gender = (String) getXssCellData(row.getCell(4));
user.setId(id);
user.setName(name);
user.setAddress(address);
user.setBirthday(borthday);
if ("男".equals(gender)) {
user.setGender("男");
}
simpleDao.createEntity(user);
count++;
}
} catch (Exception e) {
e.printStackTrace();
log.error("getUserFormXSheet exception !");
}
return count;
}
/**
* 从excel表格中获取产品数据
*
* @param productSheet
* @return
*/
private int getProductsFormXSheet(Sheet productSheet) {
int count = 0;
if (productSheet == null) return 0;
try {
int rowStart = productSheet.getFirstRowNum();
int rowEnd = productSheet.getLastRowNum();
for (int i = rowStart + 1; i <= rowEnd; i++) {
Row row = productSheet.getRow(i);
Product product = new Product();
String strid = String.valueOf(getXssCellData(row.getCell(0)));
if ("".equals(strid)) strid = "0";
int id = Integer.parseInt(strid);
String name = (String) getXssCellData(row.getCell(1));
String type = (String) getXssCellData(row.getCell(2));
String coststr = (String) getXssCellData(row.getCell(3));
double cost = 0d;
if (!"".equals(coststr)) {
cost = Double.parseDouble(coststr);
}
String unit = (String) getXssCellData(row.getCell(4));
product.setId(id);
product.setPname(name);
product.setType(type);
product.setCost(cost);
product.setUnit(unit);
simpleDao.createEntity(product);
count++;
}
} catch (Exception e) {
e.printStackTrace();
log.error("getProductsFormXSheet exception !");
}
return count;
}
@Override
public List<Entity> parse(File file) throws Exception {
List<Entity> retVal = new ArrayList<Entity>();
Workbook wb = null;
FileInputStream fileIn = new FileInputStream(file);
try {
wb = WorkbookFactory.create(fileIn);
Map<String, Integer> headers = new HashMap<String, Integer>();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
RowDataAccessor dataAccessor = new RowDataAccessor();
for (Row row : sheet) {
if (row.getRowNum() == sheet.getFirstRowNum()) {
for (Cell cell : row) {
headers.put(cell.getStringCellValue(), cell.getColumnIndex());
}
dataAccessor.setHeaders(headers);
} else {
dataAccessor.setDataRow(row);
Entity entity = Parser.EntityFiller.fillEntity(dataAccessor, dbHelper);
if (entity != null) {
retVal.add(entity);
}
}
}
}
} catch (Exception ex) {
throw ex;
} finally {
fileIn.close();
}
return retVal;
}
public static List<List<List<String>>> readExcel(File file, Rule rule) {
int start = rule.getStart();
int end = rule.getEnd();
List<List<List<String>>> result = Lists.newArrayList();
Workbook wb;
try {
wb = WorkbookFactory.create(file);
} catch (Exception e) {
throw new ExcelException(e);
}
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
List<List<String>> sheetList = Lists.newArrayList();
int rows = sheet.getLastRowNum();
if (start <= sheet.getFirstRowNum()) {
start = sheet.getFirstRowNum();
}
if (end >= rows) {
end = rows;
} else if (end <= 0) {
end = rows + end;
}
for (int rowIndex = start; rowIndex <= end; rowIndex++) {
Row row = sheet.getRow(rowIndex);
List<String> columns = Lists.newArrayList();
int cellNum = row.getLastCellNum();
System.out.println(row.getLastCellNum());
System.out.println(row.getPhysicalNumberOfCells());
for (int cellIndex = row.getFirstCellNum(); cellIndex < cellNum; cellIndex++) {
Cell cell = row.getCell(cellIndex);
int cellType = cell.getCellType();
String column = "";
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
// DecimalFormat format = new DecimalFormat();
// format.setGroupingUsed(false);
column = String.valueOf(cell.getDateCellValue());
break;
case Cell.CELL_TYPE_STRING:
column = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
column = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA:
column = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
case Cell.CELL_TYPE_BLANK:
column = " ";
break;
default:
}
columns.add(column.trim());
}
List<Boolean> rowFilterFlagList = Lists.newArrayList();
List<RowFilter> rowFilterList = Lists.newArrayList();
for (int k = 0; k < rowFilterList.size(); k++) {
RowFilter rowFilter = rowFilterList.get(k);
rowFilterFlagList.add(rowFilter.doFilter(rowIndex, columns));
}
if (!rowFilterFlagList.contains(false)) {
sheetList.add(columns);
}
}
result.add(sheetList);
}
return result;
}
public void read(Sheet sheet) {
final int LAST_ROW_COUNT = 5000;
final int POSTCODE_PREFIX_COLUMN_INDEX = 5;
final int COUNTY_COLUMN = 2;
int count = 0;
int ind = 0;
String value = null;
int rowStart = Math.min(1, sheet.getFirstRowNum());
int rowEnd = Math.max(LAST_ROW_COUNT, sheet.getLastRowNum());
String county = null;
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row r = sheet.getRow(rowNum);
if (r == null) {
continue;
}
int lastColumn = r.getLastCellNum();
/* check if there is a county in this row*/
// CellReference cellReference = new CellReference("C"+rowNum);
Cell countyCell = r.getCell(COUNTY_COLUMN, Row.RETURN_BLANK_AS_NULL);
if (countyCell != null && countyCell.getRichStringCellValue().length() > 0) {
county = countyCell.getStringCellValue();
}
ind = 0;
for (int cn = 0; cn < lastColumn; cn++) {
Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
if (c == null) {
// The spreadsheet is empty in this cell
value = null;
ind++;
} else {
// Do something useful with the cell's contents
switch (c.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
value = "" + c.getNumericCellValue();
ind++;
break;
case Cell.CELL_TYPE_STRING:
value = c.getStringCellValue().trim();
ind++;
break;
}
}
if (cn > POSTCODE_PREFIX_COLUMN_INDEX && value != null && value.length() > 0) {
if (county != null) {
char dc = value.charAt(0);
if (!(dc >= 'A' && dc <= 'Z')) {
// System.out.print(value+" not starts with letter");
value = value.substring(1);
// System.out.println(" "+value);
}
dc = value.charAt(value.length() - 1);
if (!(dc >= '0' && dc <= '9' || dc >= 'A' && dc <= 'Z')) {
value = value.substring(0, value.length() - 1);
}
mapping.put(value.trim(), county);
}
}
}
}
}