public class ImportInitDataUtils {
public static List<Map<String, String>> readExcel(){
String filePath = "D:/sudoku.xlsx";
ArrayList<Map<String, String>> insertParams = new ArrayList<Map<String, String>>();
String[] dbFields ;
Map<String, String> errorMap = new HashMap<String, String>();//错误行详细 --- 行数:错误详细
int total_record = 0;//记录总数
int parse_record = 0;//解析成功数
Workbook workBook = null;
try {
try {
//操作Excel2007的版本,扩展名是.xlsx
workBook = new XSSFWorkbook(new FileInputStream(filePath));
} catch (Exception ex) {
ex.printStackTrace();
//操作Excel2003以前(包括2003)的版本,扩展名是.xls
workBook = new HSSFWorkbook(new FileInputStream(filePath));
}
//只读第一个工作薄
Sheet sheet = workBook.getSheetAt(0);
//sheet.setDefaultColumnWidth(20);
// 循环行Row
for (int rowNum = 1; rowNum <= sheet.getLastRowNum()+1; rowNum++) {// 前一行为表头不读
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
if ("end".equalsIgnoreCase(getValue(row.getCell(0))) || StringUtils.isBlank(getValue(row.getCell(0)))) {
total_record = rowNum - 1;
System.out.println("##################### LOG #######################");
System.out.println("读取了" + (rowNum - 1) + "条数据");
System.out.println("##################### LOG #######################");
break;
}
dbFields = new String[] { "user_name", "prize_name"};
Map<String, String> cellDatas = new HashMap<String, String>();
StringBuilder errorString = new StringBuilder();
total_record = sheet.getLastRowNum();
// 循环列Cell
boolean temp = true; //本行数据是否有错误
for (int cellNum = 0; cellNum < dbFields.length; cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
String cellValue = getValue(cell);
if("user_name".equals(dbFields[cellNum])){//用户名
if(StringUtils.isBlank(cellValue)){
errorString.append("用户名(user_name)为空\n");
temp = false;
}
}
if("prize_name".equals(dbFields[cellNum])){//奖品
if(StringUtils.isBlank(cellValue)){
errorString.append("奖品(prizes)为空\n");
temp = false;
}
}
cellDatas.put(dbFields[cellNum], cellValue);
if(temp){
insertParams.add(cellDatas);
parse_record ++;
}else{
errorMap.put(rowNum+"", errorString.toString());
}
}
}
}catch(Exception e){
e.printStackTrace();
}
// 处理结果文件路径
String resultFilepath = "D:/sudoku_.xlsx";
FileUtils.copyFile(new File(filePath), new File(resultFilepath));
FileOutputStream out = null;
Workbook resultWorkBook = null;
try {
try {
resultWorkBook = new XSSFWorkbook(new FileInputStream(resultFilepath));
} catch (Exception ex) {
ex.printStackTrace();
resultWorkBook = new HSSFWorkbook(new FileInputStream(resultFilepath));
}
// 只读第一个工作薄
Sheet resultSheet = resultWorkBook.getSheetAt(0);
CellStyle style = resultWorkBook.createCellStyle();
Font font = resultWorkBook.createFont();
font.setColor(HSSFColor.RED.index);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setFont(font);
for (int rowNum = 0; rowNum <= resultSheet.getLastRowNum(); rowNum++) {//
Row row = resultSheet.getRow(rowNum);
if (row == null) {
continue;
}
if ("end".equalsIgnoreCase(getValue(row.getCell(0))) || getValue(row.getCell(0)) == null) {
break;
}
String resultString = errorMap.get(rowNum);
// 新加一列 -- 处理结果
Cell createCell1 = row.createCell(row.getLastCellNum());
// 新加一列 -- 错误说明
Cell createCell2 = row.createCell(row.getLastCellNum());
if(rowNum == 0){
createCell1.setCellValue("处理结果");
createCell2.setCellValue("错误说明");
continue;
}
createCell1.setCellType(HSSFCell.CELL_TYPE_STRING);
createCell2.setCellType(HSSFCell.CELL_TYPE_STRING);
createCell1.setCellStyle(style);
createCell2.setCellStyle(style);
if (StringUtils.isBlank(resultString)) {
createCell1.setCellValue("导入成功");
createCell2.setCellValue("");
} else {
createCell1.setCellValue("导入失败");
createCell2.setCellValue(resultString);
}
}
out = new FileOutputStream(resultFilepath);
resultWorkBook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
return insertParams;
}
/**
* 读取单元格字符串
* @param cell
* @return
*/
private static String getValue(Cell cell) {
if(cell == null){
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return String.valueOf(cell.getNumericCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
public static void main(String[] args) {
List<Map<String, String>> list = readExcel();
for(Map<String, String> map : list){
System.out.println(map.get("user_name")+",,,"+map.get("prize_name"));
}
}
}