Java：实现文件批量导入导出实践（兼容xls,xlsx）

[[315627]]

1、介绍

java实现文件的导入导出数据库，目前在大部分系统中是比较常见的功能了，今天写个小demo来理解其原理，没接触过的同学也可以看看参考下。

目前我所接触过的导入导出技术主要有POI和iReport，poi主要作为一些数据批量导入数据库，iReport做报表导出。另外还有jxl类似poi的方式，不过貌似很久没跟新了，2007之后的office好像也不支持，这里就不说了。

2、POI使用详解

2.1 什么是Apache POI？

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2.2 POI的jar包导入

本次讲解使用maven工程，jar包版本使用poi-3.14和poi-ooxml-3.14。目前最新的版本是3.16。因为3.15以后相关api有更新，部分操作可能不一样，大家注意下。 

<!-- poi的包 3.15版本后单元格类型获取方式有调整 -->  
        <dependency>  
            <groupId>org.apache.poi</groupId>  
            <artifactId>poi</artifactId>  
            <version>3.14</version>  
        </dependency>  
        <dependency>  
            <groupId>org.apache.poi</groupId>  
            <artifactId>poi-ooxml</artifactId>  
            <version>3.14</version>  
        </dependency> 

2.3 POI的API讲解

2.3.1 结构

HSSF － 提供读写Microsoft Excel格式档案的功能。

XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

HWPF － 提供读写Microsoft Word格式档案的功能。

HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

HDGF － 提供读写Microsoft Visio格式档案的功能。

2.3.2 对象

本文主要介绍HSSF和XSSF两种组件，简单的讲HSSF用来操作Office 2007版本前excel.xls文件，XSSF用来操作Office 2007版本后的excel.xlsx文件，注意二者的后缀是不一样的。

HSSF在org.apache.poi.hssf.usermodel包中。它实现了Workbook 接口，用于Excel文件中的.xls格式

常用组件：

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 : 日期格式

和HSSF类似;

2.3.3 两个组件共同的字段类型描述

其实两个组件就是针对excel的两种格式，大部分的操作都是相同的。

2.3.4 操作步骤

以HSSF为例，XSSF操作相同。

首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。

1、用HSSFWorkbook打开或者创建“Excel文件对象”

2、用HSSFWorkbook对象返回或者创建Sheet对象

3、用Sheet对象返回行对象，用行对象得到Cell对象

4、对Cell对象读写。

3、代码操作

3.1 效果图

惯例，贴代码前先看效果图

Excel文件两种格式各一个：

代码结构：

导入后：（我导入了两遍，没做校验）

导出效果：

3.2 代码详解

这里我以Spring+SpringMVC+Mybatis为基础，扩展：SpringBoot+Mybatis多模块（module）项目搭建教程

Controller: 

package com.allan.controller;  
import java.util.List;  
import javax.servlet.http.HttpServletResponse;  
import org.apache.poi.ss.formula.functions.Mode;  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.stereotype.Controller;  
import org.springframework.ui.Model;  
import org.springframework.web.bind.annotation.RequestMapping;  
import org.springframework.web.bind.annotation.RequestMethod;  
import org.springframework.web.bind.annotation.RequestParam;  
import org.springframework.web.bind.annotation.ResponseBody;  
import org.springframework.web.multipart.MultipartFile;  
import org.springframework.web.servlet.ModelAndView;  
import com.allan.pojo.Student;  
import com.allan.service.StudentService;  
/**  
 *   
 * @author 小卖铺的老爷爷  
 *  
 */  
@Controller  
public class StudentController {  
    @Autowired  
    private StudentService studentService;  
    /**  
     * 批量导入表单数据  
     *   
     * @param request  
     * @param myfile  
     * @return  
     */  
    @RequestMapping(value="/importExcel",method=RequestMethod.POST)  
    public String importExcel(@RequestParam("myfile") MultipartFile myFile) {  
        ModelAndView modelAndView = new ModelAndView();  
        try {  
            Integer num = studentService.importExcel(myFile);  
        } catch (Exception e) {  
            modelAndView.addObject("msg", e.getMessage());  
            return "index";  
        }  
        modelAndView.addObject("msg", "数据导入成功");  
        return "index";  
    }  
    @RequestMapping(value="/exportExcel",method=RequestMethod.GET)  
    public void exportExcel(HttpServletResponse response) {      
        try {  
            studentService.exportExcel(response);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
} 

Service 

package com.allan.service.impl;  
import java.io.OutputStream;  
import java.text.DecimalFormat;  
import java.text.SimpleDateFormat;  
import java.util.Date;  
import java.util.List;  
import javax.servlet.http.HttpServletResponse;  
import org.apache.commons.lang.StringUtils;  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.stereotype.Service;  
import org.springframework.web.multipart.MultipartFile;  
import com.allan.mapper.StudentMapper;  
import com.allan.pojo.Student;  
import com.allan.service.StudentService;  
/**  
 *   
 * @author 小卖铺的老爷爷  
 *  
 */  
@Service  
public class StudentServiceImpl implements StudentService{  
    private final static String XLS = "xls";    
    private final static String XLSX = "xlsx";   
    @Autowired  
    private StudentMapper studentMapper;  
    /**  
     * 导入Excel，兼容xls和xlsx  
     */  
    @SuppressWarnings("resource")  
    public Integer importExcel(MultipartFile myFile) throws Exception {  
        //        1、用HSSFWorkbook打开或者创建“Excel文件对象”  
        //  
        //        2、用HSSFWorkbook对象返回或者创建Sheet对象  
        //  
        //        3、用Sheet对象返回行对象，用行对象得到Cell对象  
        //  
        //        4、对Cell对象读写。  
        //获得文件名    
        Workbook workbook = null ;  
        String fileName = myFile.getOriginalFilename();   
        if(fileName.endsWith(XLS)){    
            //2003   
             workbook = new HSSFWorkbook(myFile.getInputStream());    
        }else if(fileName.endsWith(XLSX)){    
            //2007    
            workbook = new XSSFWorkbook(myFile.getInputStream());    
        }else{  
            throw new Exception("文件不是Excel文件");  
        }  
        Sheet sheet = workbook.getSheet("Sheet1");  
        int rows = sheet.getLastRowNum();// 指的行数，一共有多少行+  
        if(rows==0){  
            throw new Exception("请填写数据");  
        }  
        for (int i = 1; i <= rows+1; i++) {  
            // 读取左上端单元格  
            Row row = sheet.getRow(i);  
            // 行不为空  
            if (row != null) {  
                // **读取cell**  
                Student student = new Student();  
                //姓名  
                String name = getCellValue(row.getCell(0));  
                student.setName(name);  
                //班级  
                String classes = getCellValue(row.getCell(1));  
                student.setClasses(classes);  
                //分数  
                String scoreString = getCellValue(row.getCell(2));  
                if (!StringUtils.isEmpty(scoreString)) {  
                    Integer score = Integer.parseInt(scoreString);  
                    student.setScore(score);  
                }  
                //考试时间  
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//小写的mm表示的是分钟   
                String dateString = getCellValue(row.getCell(3));    
                if (!StringUtils.isEmpty(dateString)) {  
                    Date date=sdf.parse(dateString);    
                    student.setTime(date);  
                }  
                studentMapper.insert(student);  
            }  
        }  
        return rows-1;  
    } 
    /**  
     * 获得Cell内容  
     *   
     * @param cell  
     * @return  
     */  
    public String getCellValue(Cell cell) {  
        String value = "";  
        if (cell != null) {  
            // 以下是判断数据的类型  
            switch (cell.getCellType()) {  
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字  
                value = cell.getNumericCellValue() + "";  
                if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                    Date date = cell.getDateCellValue();  
                    if (date != null) {  
                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);  
                    } else {  
                        value = "";  
                    }  
                } else {  
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());  
                }  
                break;  
            case HSSFCell.CELL_TYPE_STRING: // 字符串  
                value = cell.getStringCellValue();  
                break;  
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean  
                value = cell.getBooleanCellValue() + "";  
                break;  
            case HSSFCell.CELL_TYPE_FORMULA: // 公式  
                value = cell.getCellFormula() + "";  
                break;  
            case HSSFCell.CELL_TYPE_BLANK: // 空值  
                value = "";  
                break;  
            case HSSFCell.CELL_TYPE_ERROR: // 故障  
                value = "非法字符";  
                break;  
            default:  
                value = "未知类型";  
                break;  
            }  
        }  
        return value.trim();  
    }  
    /**  
     * 导出excel文件  
     */  
    public void exportExcel(HttpServletResponse response) throws Exception {  
        // 第一步，创建一个webbook，对应一个Excel文件    
        HSSFWorkbook wb = new HSSFWorkbook();    
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet    
        HSSFSheet sheet = wb.createSheet("Sheet1");    
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short    
        HSSFRow row = sheet.createRow(0);    
        // 第四步，创建单元格，并设置值表头 设置表头居中    
        HSSFCellStyle style = wb.createCellStyle();    
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式    
        HSSFCell cell = row.createCell(0);  
        cell.setCellValue("姓名");    
        cell.setCellStyle(style);    
        cell = row.createCell(1);    
        cell.setCellValue("班级");    
        cell.setCellStyle(style);    
        cell = row.createCell(2);    
        cell.setCellValue("分数");    
        cell.setCellStyle(style);    
        cell = row.createCell(3);    
        cell.setCellValue("时间");    
        cell.setCellStyle(style);    
        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，   
        List<Student> list = studentMapper.selectAll();    
        for (int i = 0; i < list.size(); i++){    
            row = sheet.createRow(i + 1);    
            Student stu = list.get(i);    
            // 第四步，创建单元格，并设置值    
            row.createCell(0).setCellValue(stu.getName());    
            row.createCell(1).setCellValue(stu.getClasses());    
            row.createCell(2).setCellValue(stu.getScore());    
            cell = row.createCell(3);    
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));   
        }            
        //第六步,输出Excel文件  
        OutputStream output=response.getOutputStream();  
        response.reset();  
        long filename = System.currentTimeMillis();  
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式  
        String fileName = df.format(new Date());// new Date()为获取当前系统时间  
        response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");  
        response.setContentType("application/msexcel");          
        wb.write(output);  
        output.close();  
    }   
} 

3.3 导出文件api补充

大家可以看到上面service的代码只是最基本的导出。

在实际应用中导出的Excel文件往往需要阅读和打印的，这就需要对输出的Excel文档进行排版和样式的设置，主要操作有合并单元格、设置单元格样式、设置字体样式等。

3.3.1 单元格合并

使用HSSFSheet的addMergedRegion()方法 

public int addMergedRegion(CellRangeAddress region) 

参数CellRangeAddress 表示合并的区域，构造方法如下：依次表示起始行，截至行，起始列， 截至列 

CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 

3.3.2 设置单元格的行高和列宽 

HSSFSheet sheet=wb.createSheet();  
sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽  
//设置指定列的列宽，256 * 50这种写法是因为width参数单位是单个字符的256分之一  
sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50); 

3.3.3 设置单元格样式

1、创建HSSFCellStyle 

HSSFCellStyle cellStyle=wkb.createCellStyle() 

2、设置样式 

// 设置单元格的横向和纵向对齐方式，具体参数就不列了，参考HSSFCellStyle  
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);  
  cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
  /* 设置单元格的填充方式，以及前景颜色和背景颜色  
   三点注意：  
   1.如果需要前景颜色或背景颜色，一定要指定填充方式，两者顺序无所谓；  
   2.如果同时存在前景颜色和背景颜色，前景颜色的设置要写在前面；  
   3.前景颜色不是字体颜色。  
  */  
  //设置填充方式(填充图案)  
  cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);  
  //设置前景色  
  cellStyle.setFillForegroundColor(HSSFColor.RED.index);  
  //设置背景颜色  
  cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);  
  // 设置单元格底部的边框及其样式和颜色  
  // 这里仅设置了底边边框，左边框、右边框和顶边框同理可设  
  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);  
  cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);  
  //设置日期型数据的显示样式  
  cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); 

3、将样式应用于单元格 

cell.setCellStyle(cellStyle);  
  //将样式应用到行，但有些样式只对单元格起作用  
  row.setRowStyle(cellStyle); 

3.3.4设置字体样式

1、创建HSSFFont对象（调用HSSFWorkbook 的createFont方法） 

HSSFWorkbook wb=new HSSFWorkbook();  
HSSFFont  fontStyle=wb.createFont();  
HSSFWorkbook wb=new HSSFWorkbook (); 

2、设置字体各种样式 

//设置字体样式  
  fontStyle.setFontName("宋体");    
  //设置字体高度  
  fontStyle.setFontHeightInPoints((short)20);    
  //设置字体颜色  
  font.setColor(HSSFColor.BLUE.index);  
  //设置粗体  
  fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
  //设置斜体  
  font.setItalic(true);  
  //设置下划线  
  font.setUnderline(HSSFFont.U_SINGLE); 

3、将字体设置到单元格样式 

//字体也是单元格格式的一部分，所以从属于HSSFCellStyle  
// 将字体对象赋值给单元格样式对象  
cellStyle.setFont(font);  
// 将单元格样式应用于单元格  
cell.setCellStyle(cellStyle); 

大家可以看出用poi导出文件还是比较麻烦的，等下次在为大家介绍下irport的方法。

导出的api基本上就是这些，最后也希望上文对大家能有所帮助。