Java实现文件批量导入导出实例(兼容xls,xlsx)
1、介绍
Apache POI是Apache软件基金会的开放源码函式库。
2、POI使用详解
POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
本次讲解使用maven工程,jar包版本使用poi-3.14和poi-ooxml-3.14。目前最新的版本是3.16。因为3.15以后相关api有更新,部分操作可能不一样。
2.3 POI的API讲解
2.3.1 结构
HSSF提供读写XLS格式档案的功能。
XSSF提供读写XLSX格式档案的功能。
HWPF提供读写DOC格式档案的功能。
XWPF提供读写DOCX格式档案的功能。
2.3.2 对象
常用组件:
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾(只有打印的时候才能看到效果)
样式:
HSSFCellStyle cell样式
辅助操作包括:
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
XSSF在org.apache.xssf.usemodel包,并实现Workbook接口,用于Excel文件中的.xlsx格式
常用组件:
XSSFWorkbook excel的文档对象
XSSFSheet excel的表单
XSSFRow excel的行
XSSFCell excel的格子单元
XSSFFont excel字体
XSSFDataFormat 日期格式
2.3.3 操作步骤
以HSSF为例,XSSF操作相同。
首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(HSSFSheet)组成,一个sheet是由多个row(HSSFRow)组成,一个row是由多个cell(HSSFCell)组成。
3.1 POI导出
3.1.1 demo模拟导出
public static void main3(String[] args) throws Exception{
ArrayList<Emp> list = new ArrayList<>();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Emp emp = new Emp();
emp.setEmpId(1);
emp.setEmpName("张三");
emp.setEmpSex("男");
emp.setEmpBirth(format.parse("1998-10-10"));
list.add(emp);
emp = new Emp();
emp.setEmpId(2);
emp.setEmpName("李四");
emp.setEmpSex("女");
emp.setEmpBirth(format.parse("1991-10-10"));
list.add(emp);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("编号");
cell = row.createCell(1);
cell.setCellValue("姓名");
cell = row.createCell(2);
cell.setCellValue("性别");
cell = row.createCell(3);
cell.setCellValue("生日");
cell = row.createCell(4);
cell.setCellValue("年龄");
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i+1);
cell = row.createCell(0);
cell.setCellValue(list.get(i).getEmpId());
cell = row.createCell(1);
cell.setCellValue(list.get(i).getEmpName());
cell = row.createCell(2);
cell.setCellValue(list.get(i).getEmpSex());
cell = row.createCell(3);
cell.setCellValue(format.format(list.get(i).getEmpBirth()));
cell = row.createCell(4);
String format1 = format.format(list.get(i).getEmpBirth());
String substring = format1.substring(0, 4);
int i1 = Integer.parseInt(substring);
int i2=2021;
cell.setCellValue(i2-i1);
}
workbook.write(new File("E:\\emp1.xls"));
}
3.1.2 ssm项目实现导出功能
@RequestMapping("export")
public void export(HttpServletRequest request,HttpServletResponse response)throws Exception{
JSONObject result=new JSONObject();
List<EmpVo> list = empService.findAll();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
HSSFCell cell = row.createCell(0);
cell.setCellValue(list.get(i).getEmpId());
cell = row.createCell(1);
cell.setCellValue(list.get(i).getEmpName());
cell = row.createCell(2);
cell.setCellValue(list.get(i).getEmpAge());
cell = row.createCell(3);
cell.setCellValue(list.get(i).getEmpSex());
cell = row.createCell(4);
cell.setCellValue(list.get(i).getEmpSpecialHobby());
cell = row.createCell(5);
cell.setCellValue(list.get(i).getDname());
cell = row.createCell(6);
cell.setCellValue(list.get(i).getShengName()+"-"+list.get(i).getShiName()+"-"+list.get(i).getXianName());
cell = row.createCell(7);
cell.setCellValue(format.format(list.get(i).getEmpBirth()));
}
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode("设备列表.xls", "UTF-8"));
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/octet-stream");
workbook.write(response.getOutputStream());
workbook.close();
}
4.1 POI导入
4.1.1 demo模拟导入
public static void main7(String[] args) throws Exception{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("E:\\emp1.xls"));
HSSFSheet sheetAt = workbook.getSheetAt(0);
int lastRowNum = sheetAt.getLastRowNum();
ArrayList<Emp> list = new ArrayList<>();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 1; i <=lastRowNum; i++) {
Emp emp = new Emp();
HSSFRow row = sheetAt.getRow(i);
HSSFCell cell = row.getCell(0);
Double numericCellValue = cell.getNumericCellValue();
emp.setEmpId(numericCellValue.intValue());
cell = row.getCell(1);
emp.setEmpName(cell.getStringCellValue());
cell = row.getCell(2);
emp.setEmpSex(cell.getStringCellValue());
cell = row.getCell(3);
String stringCellValue = cell.getStringCellValue();
emp.setEmpBirth(format.parse(stringCellValue));
list.add(emp);
}
System.out.println(JSONObject.toJSONString(list));
workbook.close();
}
4.1.2 ssm项目实现导入功能
@RequestMapping("import")
public void importFile(HttpServletRequest request,HttpServletResponse response,MultipartFile file)throws Exception{
HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
HSSFSheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 1; i <=lastRowNum; i++) {
Emp emp = new Emp();
HSSFRow row = sheet.getRow(i);
HSSFCell cell = row.getCell(1);
emp.setEmpName(cell.getStringCellValue());
cell = row.getCell(2);
Double age = cell.getNumericCellValue();
emp.setEmpAge(age.intValue());
cell = row.getCell(3);
emp.setEmpSex(cell.getStringCellValue());
cell = row.getCell(4);
emp.setEmpSpecialHobby(cell.getStringCellValue());
cell = row.getCell(5);
String dname = cell.getStringCellValue();
Dept dept = new Dept();
dept.setName(dname);
dept.setState(1);
List<Dept> list = deptMapper.select(dept);
if (list!=null && list.size()>0){
emp.setEmpDeptId(list.get(0).getId());
}else{
deptMapper.insert(dept);
emp.setEmpDeptId(dept.getId());
}
cell = row.getCell(6);
String addresss = cell.getStringCellValue();
Address address = new Address();
address.setName(addresss.split("-")[0]);
List<Address> select = addressMapper.select(address);
emp.setEmpSheng(select.get(0).getId());
Address address2 = new Address();
address2.setName(addresss.split("-")[1]);
List<Address> select1 = addressMapper.select(address2);
emp.setEmpShi(select1.get(0).getId());
Address address3 = new Address();
address3.setName(addresss.split("-")[2]);
List<Address> select2 = addressMapper.select(address3);
emp.setEmpXian(select2.get(0).getId());
cell = row.getCell(7);
String stringCellValue = cell.getStringCellValue();
emp.setEmpBirth(format.parse(stringCellValue));
empService.addEmp(emp);
}
JSONObject result=new JSONObject();
workbook.close();
result.put("success", true);
WriterUtil.write(response, result.toString());
}
5.0 最后也希望上文对大家能有所帮助!有错误的地方请大神纠正。