本文介绍了一种使用Java POI库从Excel读取数据并将其导入数据库的方法，同时提供了创建Excel模板的代码示例。文章详细解释了如何处理不同类型的单元格数据，包括字符串、整数和空白值，确保数据的正确转换。
导入excel读取里面内容存储,创建excel模板
POIUtil类
/*获取一行指定名的集合*/
/*读取一行存为list,性别数据库为int,所以判断;index=3为年龄,excel里是String类型,
数据库为int类型,若未填写,int存0*/
public static List<Object> getListByRow(Map<String, String> headDataMap, Row row, String[] attributes) {
List<Object> datas = new ArrayList<Object>();
for (int i = 0; i < attributes.length; i++) {
String index = headDataMap.get(attributes[i]);
if(index == null){
System.out.println("查询列："+attributes[i]+"没有！");
}
else{
Cell cell = row.getCell(Integer.parseInt(index));
Object cellValue = getCellValue(cell);
if(index.equals("2")){
if(cellValue.equals("女")){
cellValue = 1 ;
}else {
cellValue = 0 ;}
}
if(index.equals("3")){
if(cellValue.equals("")){
cellValue = 0;
}
cellValue = Integer.parseInt(cellValue.toString());
}
if (cellValue == null) {
cellValue = "";
}
datas.add(cellValue);
}
}
return datas;
}
/**
* 获取一行的内容,Map存储,存储方式由参数定义,获取表头
* @param row 行对象
* isValueKey 是否以单元格内容作为Key?key为单元格内容, value为下标索引
* @return 一行的内容,Map存储
*/
public static Map<String, String> getRowDataToMap(Row row, boolean isValueKey) {
Map<String, String> headDatas = new HashMap<String, String>();
short countCellNum = row.getLastCellNum();
if (isValueKey) {
for (int j = 0; j < countCellNum; j++) {
Cell cell = row.getCell(j);
if (isExist(cell)) {
// Key=单元格内容, Value=下标索引
headDatas.put(String.valueOf(getCellValue(cell)), String.valueOf(j));
}
}
} else {
for (int j = 0; j < countCellNum; j++) {
Cell cell = row.getCell(j);
if (isExist(cell)) {
// Key=下标索引, Value=单元格内容
headDatas.put( String.valueOf(j), String.valueOf(getCellValue(cell)));
}
}
}
return headDatas;
}
/*生成学生Excel表的模板*/
public boolean creatExcel(String path) throws IOException {
String[] title={ "学号", "姓名", "性别", "年龄",
"电话", "邮箱", "身份证"};
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFont font = wb.createFont();//创建字体样式
font.setFontName("宋体");//使用宋体
font.setFontHeightInPoints((short) 12);//字体大小
HSSFCellStyle style1 = wb.createCellStyle();
style1.setFont(font);//将字体注入
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
HSSFSheet sheet = wb.createSheet("学生信息表");
sheet.setColumnWidth(0, 30*170);
sheet.setColumnWidth(1, 30*170);
sheet.setColumnWidth(2, 30*128);
sheet.setColumnWidth(3, 30*128);
sheet.setColumnWidth(4, 30*160);
sheet.setColumnWidth(5, 30*170);
sheet.setColumnWidth(6, 30*250);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
//插入第一行数据的表头
for(int i=0;i<title.length;i++){
cell=row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(style1);
}
// 旁边的说明信息
sheet.addMergedRegion(new CellRangeAddress(0,(short)2,7,(short)12));
HSSFFont font1 = wb.createFont();//创建字体样式
font1.setFontName("宋体");//使用宋体
font1.setFontHeightInPoints((short) 14);//字体大小
font1.setColor(HSSFColor.RED.index);
HSSFCellStyle style2 = wb.createCellStyle();
style2.setFont(font1);
style2.setWrapText(true);// 自动换行
cell = row.createCell(7);
cell.setCellValue("性别填:男/女--年龄写数字,不加岁,如21,23");
cell.setCellStyle(style2);
//参数为(第一行，最后一行，第一列，最后一列)
FileOutputStream os = new FileOutputStream(path+"学生信息表.xls");
wb.write(os);
os.close();
return true;
}
/*获取单元格数据类型返回值*/
protected static Object getCellValue(Cell cell) {
Object cellVauue = "";
int cellType = 3;
if(cell!=null) {
cellType = cell.getCellType();
}
switch (cellType) {
case Cell.CELL_TYPE_BOOLEAN:
cellVauue = "";
break;
case Cell.CELL_TYPE_FORMULA:
cellVauue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if(String.valueOf(cell.getNumericCellValue()).indexOf("E")==-1){
cellVauue = (int)cell.getNumericCellValue();
}else {
cellVauue = new DecimalFormat("#").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
cellVauue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:
cellVauue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellVauue = "";
break;
default:
cellVauue = "";
}
if(cellVauue == null){
cellVauue = "";
}
return cellVauue;
}
Resource类
@POST
@Path("importExcel")
@ApiOperation("学生Excel导入")
@Produces(MediaType.APPLICATION_JSON)
public List<String> studentExcelImport(@FormDataParam("file") FormDataBodyPart file, @BeanParam Student student) throws IOException {
String mediaType = file.getMediaType().toString();
//xlsx,xls
if (!mediaType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") &&
!mediaType.equals("application/vnd.ms-excel")) {
Checkers.checkState(false, "文件不是excel类型");
}
File entity = file.getEntityAs(File.class);
return studentService.studentExcelImport(entity,student);
}
ServiceImpl实现类
@Override
public List<String> studentExcelImport(File file , Student student) throws IOException {
/*if (!xlsPath.endsWith(".xls") && !xlsPath.endsWith(".xlsx")) {
Checkers.checkState(false, "文件不是excel类型");
}*/
List<String> list = new ArrayList();
FileInputStream fileIn = new FileInputStream(file);
Workbook wb = new HSSFWorkbook(fileIn);
/* if(xlsPath.endsWith(".xls")){
wb = new HSSFWorkbook(fileIn);//03版
}else {
wb = new XSSFWorkbook(fileIn);//07版不行
}*/
Sheet sht0 = wb.getSheetAt(0); //获取Excel文档中的第一个表单
Map<String, String> headMap = poiUtil.getRowDataToMap(sht0.getRow(0),false);//获取表头
for (int i = 1; i <= sht0.getLastRowNum(); i++) {
Row row = sht0.getRow(i);
Student s = new Student();
s.setSchId(student.getSchId());
s.setFacId(student.getFacId());
s.setSpeId(student.getSpeId());
s.setClaId(student.getClaId());
for (int j=0;j<row.getLastCellNum();j++){
switch (headMap.get(String.valueOf(j))) {
case "学号":
if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
break;
}
s.setAccount(poiUtil.getCellValue(row.getCell(j)).toString());
break;
case "姓名":
if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
break;
}
s.setName(poiUtil.getCellValue(row.getCell(j)).toString());
break;
case "性别":
int sex;
if (poiUtil.getCellValue(row.getCell(j)).equals("女")) {
sex = 1;
} else {
sex = 0;
}
s.setSex(sex);
break;
case "年龄":
String ss = poiUtil.getCellValue(row.getCell(j)).toString();
if (ss.equals("")) {
s.setAge(0);
} else {
s.setAge(Integer.parseInt(ss));
}
break;
case "电话":
if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
break;
}
s.setPhone(poiUtil.getCellValue(row.getCell(j)).toString());
break;
case "邮箱":
if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
break;
}
s.setEmail(poiUtil.getCellValue(row.getCell(j)).toString());
break;
case "身份证":
if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
break;
}
s.setIdCard(poiUtil.getCellValue(row.getCell(j)).toString());
break;
}
}
if(isStudentExist(s.getAccount())){
list.add("第"+i+"行:"+s.getAccount()+"学生学号重复");
continue;//库中已有
}else if(s.getAccount()==null){
list.add("第"+i+"行:"+s.getName()+"学生没有学号");
continue;//库中已有
}else {
importSave(s);
}
}
fileIn.close();
return list;
}
poi读取电话号码一类大数字时会变成带E的数
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
