1.创建导入 导出工具类，写入其导入和导出方法
导出成外部文件：
public static ResponseEntity<byte[]> emp2Excel(List<Emp> list) {
//1.创建一个Excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建文档摘要
workbook.createInformationProperties();
//3.获取并配置文档信息
DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
//文档类别
docInfo.setCategory("员工信息");
//文档管理员
docInfo.setManager("javaboy");
//设置公司信息
docInfo.setCompany("");
//4.获取文档摘要信息
SummaryInformation summInfo = workbook.getSummaryInformation();
//文档标题
summInfo.setTitle("员工信息表");
//文档作者
summInfo.setAuthor("javaboy");
//文档备注
summInfo.setComments("本文档由 javaboy 提供");
//5.创建样式
//创建标题行的样式
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFCellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
HSSFSheet sheet = workbook.createSheet("员工信息表");
//设置列的宽度
sheet.setColumnWidth(0, 5 * 256);
sheet.setColumnWidth(1, 12 * 256);
sheet.setColumnWidth(2, 10 * 256);
sheet.setColumnWidth(3, 5 * 256);
//6.创建标题行
HSSFRow r0 = sheet.createRow(0);
HSSFCell c0 = r0.createCell(0);
c0.setCellValue("编号");
c0.setCellStyle(headerStyle);
HSSFCell c1 = r0.createCell(1);
c1.setCellStyle(headerStyle);
c1.setCellValue("姓名");
HSSFCell c2 = r0.createCell(2);
c2.setCellStyle(headerStyle);
c2.setCellValue("性别");
HSSFCell c3 = r0.createCell(3);
c3.setCellStyle(headerStyle);
c3.setCellValue("薪水");
for (int i = 0; i < list.size(); i++) {
Emp emp = list.get(i);
HSSFRow row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(emp.getEid());
row.createCell(1).setCellValue(emp.getEname());
row.createCell(2).setCellValue(emp.getSex());
row.createCell(3).setCellValue(emp.getSalary());
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
HttpHeaders headers = new HttpHeaders();
try {
headers.setContentDispositionFormData("attachment", new String("员工表2.xls".getBytes("UTF-8"), "ISO-8859-1"));
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
workbook.write(baos);
} catch (IOException e) {
e.printStackTrace();
}
return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
}
外部文件导入：
public static List<Emp> excel2Emp(MultipartFile file, List<Nation> allNations, List<Politicsstatus> allPoliticsstatus, List<Department> allDepartments, List<Position> allPositions, List<JobLevel> allJobLevels) {
List<Emp> list = new ArrayList<>();
Emp emp = null;
try {
//1.创建一个workbook对象
HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
//2.获取workbook中表单的数量
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
//3.获取表单
HSSFSheet sheet = workbook.getSheetAt(i);
//4.获取表单中的行数
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
for (int j = 0; j < physicalNumberOfRows; j++) {
//5.跳过标题行
if (j == 0) {
continue;//跳过标题行
}
//6.获取行
HSSFRow row = sheet.getRow(j);
if (row == null) {
continue;//防止数据中间有空行
}
//7.获取列数
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
emp = new Emp();
for (int k = 0; k < physicalNumberOfCells; k++) {
HSSFCell cell = row.getCell(k);
switch (cell.getCellType()) {
caseSTRING:
String cellValue = cell.getStringCellValue();
switch (k) {
case 0:
emp.setEid(Integer.valueOf(cellValue));
break;
case 1:
emp.setEname(cellValue);
break;
case 2:
emp.setSex(cellValue);
break;
case 3:
emp.setSalary(Double.valueOf(cellValue));
break;
}
break;
default:
break;
}
}
list.add(emp);
}
}
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
注意导出时，row.createCell(0).setCellValue();中只能为字符串，否则报空指针异常，加一个 +"" 就行，注意从数据库中查找的列要和表中的对应，不能少于表中的数据，不然表中数据为Null
注意：其中导入数据时，遇到整形或者日期Date类型的时候需要注意参数的类型设置，如下：
switch (cell.getCellType()) {
caseSTRING:
String cellValue = cell.getStringCellValue();
switch (k) {
case 0:
employeeec.setId(Integer.valueOf(cellValue));
break;
case 2:
employeeec.setEname(cellValue);
break;
case 3:
employeeec.setDepname(cellValue);
break;
case 4:
employeeec.setPosname(cellValue);
break;
case 6:
employeeec.setEcreason(cellValue);
break;
case 9:
employeeec.setRemark(cellValue);
break;
}
break;
default:
switch (k) {
case 1:
cell.setCellType(CellType.STRING);
employeeec.setEid(Integer.parseInt(cell.getStringCellValue()));
break;
case 7:
cell.setCellType(CellType.STRING);
employeeec.setEcpoint(Integer.parseInt(cell.getStringCellValue()));
break;
case 8:
//整形,先把表格类型转成String，然后在设置
cell.setCellType(CellType.STRING);
employeeec.setEctype(Integer.parseInt(cell.getStringCellValue()));
break;
case 5:
//日期
employeeec.setEcdate(cell.getDateCellValue());
break;
}
break;
}
注意值为 "null" 的情况
switch (cell.getCellType()) {
caseSTRING:
String cellValue = cell.getStringCellValue();
switch (k) {
case 0:
emp.setEid(Integer.valueOf(cellValue));
break;
case 1:
emp.setEname(cellValue);
break;
case 2:
emp.setSex(cellValue);
break;
case 3:
cell.setCellType(CellType.STRING);
if (cell.getStringCellValue().equals("null")){
emp.setSalary(0.0);
break;
}
emp.setSalary(Double.parseDouble(cell.getStringCellValue()));
break;
}
break;
default:
break;
}
下面直接看运行效果 ：
导入
