POI结构:
HSSF:提供读写Microsoft Excel XLS格式档案的功能
XSSF:提供读写Microsoft Excel OOXML XLSX格式档案的功能
HWPF:提供读写Microsoft Word DOC格式档案的功能
HSLF:提供读写Microsoft PowerPoint格式档案的功能
HDGF:提供读写Microsoft Visio格式档案的功能
HPBF:提供读写Microsoft Publisher格式档案的功能
HSMF:提供读写Microsoft Outlook格式档案的功能
批量导入模板操作过程:
1.下载Excel模板
2.填入信息
3.上传文件到服务器
4.通过POI读物上传文件的数据并保存在数据库
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
文件读取通用类:
public class POIUtils {
private final static String xls = "xls";
private final static String xlsx = "xlsx";
private final static String DATE_FORMAT = "yyyy/MM/dd";
/**
* 读入excel文件,解析后返回
*/
public static List<String[]> readExcel(MultipartFile file) throws IOException {
//检查文件
checkFile(file);
//获得WorkBook工作簿
Workbook workBook = getWorkBook(file);
//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
List<String[]> list = new ArrayList<~>();
if(workBook != null){
for(int sheetNum = 0;sheetNum < workBook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workBook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了第一行的所有行
for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
//获取当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//获取当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获取当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
//循环当前行
for(int cellNum = firstCellNum;cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workBook.close();
}
return list;
}
//检查文件是否合法
public static void checkFile(MultipartFile file) throws IOException {
//判断文件是否存在
if(null == file){
throw new FileNotFoundException("file not exist!");
}
//获取文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
throw new IOException(fileName+"not a excel file");
}
}
public static Workbook getWorkBook(MultipartFile file){
//获取文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作波对象,表示整个excel
Workbook workbook = null;
try{
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件名后缀不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith(xls)){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith(xlsx)){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//如果当前单元格内容为日期类型,需要特殊处理
String dataFormatString = cell.getCellStyle().geDataFormatString();
if(dataFormatString.equals("m/d/yy")){
cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
return cellValue;
}
//把数字当成String来读,避免出现1都城1.0的情况
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
}
Controller:
@RestController
@RequestMapping
public class OrderSettingController {
@Reference
private OrderSettingService orderSettingService;
//文件上传,实现预约设置数据批量导入
@RequestMapping("/upload")
public Result upload(@RequestParam("excelFile") MultipartFile excelFile) {
try {
List<String[]> list = POIUtils.readExcel(excelFile); //使用POI解析表格数据
List<OrderSetting> data = new ArrayList<OrderSetting>();
for (String[] strings : list) {
String orderDate = strings[0];
String number = strings[1];
OrderSetting orderSetting = new OrderSetting(new Date(orderDate),Integer.parseInt(number));
data.add(orderSetting);
return new Result(true, MessageConstant.UPLOAD_SUCCESS);
}
//通过dubbo远程调用服务实现数据批量导入到数据库
orderSettingService.add(data);
}catch (IOException e){
e.printStackTrace();
return new Result(false, MessageConstant.UPLOAD_FAILD);
}
}
}
实现类Impl:
public class OrderSettingServiceImpl implements OrderSettingService {
@Autowired
private OrderSettingDao orderSettingDao;
public void add(List<OrderSetting> list){
if(list != null && list.size() > 0){
for(OrderSetting orderSetting : list){
//检查此数据是否存在
long count = orderSettingDao.findCountByOrderDate(orderSetting.getOrderDate());
if(count > 0){
//已经存在,执行更新操作
orderSettingDao.editNumberByOrderDate(orderSetting);
}else{
//不存在,执行添加操作
orderSettingDao.add(orderSetting);
}
}
}
}
}
Entity实体类举例:
public class OrderSetting implements Serializable {
private Integer id;
private Date orderDate;
private int number;
private int reservations;
public OrderSetting( Date orderDate, int number) {
this.orderDate = orderDate;
this.number = number;
}
}
批量导出模板操作过程:
public class ReportController{
@Autowired
private ReportService reportService;
//导出运营数据
@RequestMapping("/exportBusinessReport")
public Result exportBusinessReport(HttpServletRequest request,HttpServletResponse response) throws IOException {
Map<String,Object> data = reportService.getBusinessReportData();
String reportDate = (String) data.get("reportDate");
String todayNewMember = (String) data.get("todayNewMember");
List<Map> hotSermeal = (List<Map>) data.get("hotSermeal");
//动态获取模板文件在磁盘上的绝对位置(一般在resource目录下),从参数HttpServletRequest中获取
String filePath = request.getSession().getServletContext().getRealPath("template")+ File.separator+"report_template.xlsx";
//基于提供的Excel模板文件在内存中创建一个Excel表格对象
XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File(filePath)));
//读取第一个工作表
XSSFSheet sheet = excel.getSheetAt(0);
//获得第三行,第六格
XSSFRow row = sheet.getRow(2);
XSSFCell cell = row.getCell(5);
cell.setCellValue(reportDate);
//获取第五行,第六格
row = sheet.getRow(4);
cell = row.getCell(5);
cell.setCellValue(todayNewMember);
int rowNum = 12; //从13行开始遍历
for (Map map : hotSermeal) {
String name = (String) map.get("name");
Long setmeal_count = (Long) map.get("setmeal_count");
BigDecimal proportion = (BigDecimal) map.get("proportion");
row = sheet.getRow(rowNum++);
row.getCell(4).setCellValue(name);
row.getCell(5).setCellValue(name);
row.getCell(6).setCellValue(name);
}
//使用输出流进行表格下载,基于浏览器作为客户端下载,使用HttpServletResponse参数
OutputStream out = response.getOutputStream();
//设置响应头信息
response.setContentType("application/vnd.ms-excel"); //代表的是Excel文件类型
response.setHeader("content-Disposition","attachment;filename=report.xlsx"); //指定以附件形式进行下载
excel.write(out);
out.flush();
out.close();
excel.close();
return null;
}
}