Java之使用POI教你玩转Excel导入与导出

目录

• POI 简介
• POI依赖文件
• POI常用类
• 数据导出
• 设置样式
• 数据导入

在平时开发项目时对数据的处理肯定是少不了的。对于数据处理也是很多程序员比较头疼的问题，就比如项目中的数据是如何添加进去呢？一条一条的录入？好像又有点不太友好，数据多了效率太低了，最关键的是甲方爸爸肯定不会满意的。

这时我们可以使用POI来操作Excel表格，可以通过POI来把Excel中的数据批量导入到数据库中，从而简化操作，提高效率。反之我们还可以通过POI把数据库中的数据导出到Excel表格中。

POI 简介

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

POI依赖文件

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

POI常用类

数据导出

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

所以对应的操作步骤如下：

• 用HSSFWorkbook打开或者创建Excel文件对象。
• 用HSSFWorkbook对象返回或者创建Sheet对象。
• 用Sheet对象返回行对象，用行对象得到Cell对象。
• 对Cell对象读写。

下面来看一下数据导出功能

前台页面只需要给出一个跳转到后台导出方法的链接即可。

<button class="layui-btn layui-btn-sm" onclick="window.location.href='staff/export'">导出</button>

后台对应导出的方法，代码如下：

@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
    //查询数据库中的数据
    List<Staff> staffList = staffService.findAll();
    //创建excel的文档对象
    HSSFWorkbook workbook = new HSSFWorkbook();
    //创建员工信息工作薄，表单
    HSSFSheet sheet = workbook.createSheet("员工信息");
    //在sheet里创建第一行，参数为行索引(excel的行)，从0开始
    HSSFRow row1 = sheet.createRow(0);
    //创建单元格excel的单元格，参数为列索引，从0开始
    HSSFCell cell = row1.createCell(0);
    //设置单元格内容，标题第一行（可以不设置，根据项目需求）
    cell.setCellValue("员工信息");
    //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列，一行标题合并单元格
    sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
    //设置列名（每列的小标题）
    String[] fieldnames = {"姓名", "部门", "年龄", "工号"};
    //在sheet里创建第二行，第一行设置了标题
    HSSFRow row2 = sheet.createRow(1);
    for(int i = 0; i < fieldnames.length; i++) {
        row2.createCell(i).setCellValue(fieldnames[i]);     //设置列名
    }
    //填充导出的数据到Excel中
    for(int i = 0; i < staffList.size(); i++){
        HSSFRow rows = sheet.createRow(i + 2);
        HSSFCell cellName = rows.createCell(0);
        cellName.setCellValue(staffList.get(i).getName());
        HSSFCell cell1Dept = rows.createCell(1);
        cell1Dept.setCellValue(staffList.get(i).getDept());
        HSSFCell cell1Age = rows.createCell(2);
        cell1Age.setCellValue(staffList.get(i).getAge());
        HSSFCell cell1Number = rows.createCell(3);
        cell1Number.setCellValue(staffList.get(i).getNumber());
    }
    //输出Excel文件
    OutputStream output=response.getOutputStream();
    response.reset();
    //filename可以设置Excel文件的名称
    response.setHeader("Content-disposition", "attachment; filename=staff.xls");
    response.setContentType("application/msexcel");
    workbook.write(output);
    output.close();
}

注释怎么清楚，就不用多解释了吧，(#^ . ^#)。

导出结果如下：

设置样式

上面导出的内容大家也看到了，就是最原始的样式。其实在导出时我们也可以对其Excel设置相应的样式。

1、合并单元格

使用HSSFSheet类中的addMergedRegion(CellRangeAddress region)方法，上面导出也用到过。

参数CellRangeAddress表示合并的区域，方法如下：

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

其中参数依次表示起始行，截至行，起始列， 截至列。有兴趣的小伙伴可以点进封装类中看一下。

2、设置单元格的大小

HSSFSheet sheet = workbook.createSheet("员工信息");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue("张三");
sheet.setColumnWidth(1, 256 * 50);	//设置第一列的宽度，为50个字符宽度
row.setHeightInPoints(50);	//设置一行的高度

setColumnWidth方法和setHeightInPoints方法适合这是部分的样式，如果需要设置全部样式，可以使用HSSFSheet.setDefaultColumnWidth和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽和行高。

3、设置单元格样式

单元格样式是通过HSSFCellStyle类来设置的，所以我们需要先得到HSSFCellStyle 类

 HSSFCellStyle style = workbook.createCellStyle()

3.1、设置水平对齐方式

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

水平对齐相关参数

• 如果是左侧对齐就是 HSSFCellStyle.ALIGN_FILL。
• 如果是居中对齐就是 HSSFCellStyle.ALIGN_CENTER。
• 如果是右侧对齐就是 HSSFCellStyle.ALIGN_RIGHT。
• 如果是跨列举中就是 HSSFCellStyle.ALIGN_CENTER_SELECTION。
• 如果是两端对齐就是 HSSFCellStyle.ALIGN_JUSTIFY。

3.2、设置垂直对齐方式

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

垂直对齐相关参数

• 如果是靠上就是 HSSFCellStyle.VERTICAL_TOP。
• 如果是居中就是 HSSFCellStyle.VERTICAL_CENTER。
• 如果是靠下就是 HSSFCellStyle.VERTICAL_BOTTOM。
• 如果是两端对齐就是 HSSFCellStyle.VERTICAL_JUSTIFY。

3.3、设置边框

style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框
style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框
style.setTopBorderColor(HSSFColor.RED.index);//上边框颜色
style.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色
style.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色
style.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色

3.4、应用样式

//用在某一个单元格中
cell.setCellStyle(cellStyle);
//用在一行中，设置一行的样式
row.setRowStyle(cellStyle);

4、设置字体样式

字体样式是通过HSSFFont类来设置的，所以我们需要先得到HSSFFont类

HSSFFont font = workbook.createFont()

设置样式

font.setFontName("华文行楷");//设置字体名称
font.setFontHeightInPoints((short)28);//设置字号
font.setColor(HSSFColor.RED.index);//设置字体颜色
font.setUnderline(FontFormatting.U_SINGLE);//设置下划线
font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标
font.setStrikeout(true);//设置删除线

下划线选项值：

• 单下划线 FontFormatting.U_SINGLE
• 双下划线 FontFormatting.U_DOUBLE
• 会计用单下划线 FontFormatting.U_SINGLE_ACCOUNTING
• 会计用双下划线 FontFormatting.U_DOUBLE_ACCOUNTING
• 无下划线 FontFormatting.U_NONE

上标下标选项值：

• 上标 FontFormatting.SS_SUPER
• 下标 FontFormatting.SS_SUB
• 普通，默认值 FontFormatting.SS_NONE

应用样式

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

数据导入

导入数据时，页面需要给出一个选择文件的标签，页面就不多说了，只要有一个上传的标签即可。

这是用layui写的一个简单的页面，代码如下：

<style type="text/css">
	#updateFile{
		margin: 20px 0px 20px 50px;
	}
</style>
<form id="importForm" method="post"  class="layui-form" >
	<div class="layui-form-item" style="margin: 0px;">
		<button type="button" name="file" class="layui-btn layui-btn-normal" id="updateFile">选择文件</button>
   	</div>
   	<div class="layui-form-item">
		<div class="layui-input-block" style="margin-left: 50px;">
			<input class="layui-btn layui-btn-normal" id="upload" value="   导    入   " />
			<a href="staff/exportmoban" rel="external nofollow" >下载模板</a>
		</div>
    </div>
</form>

<script type="text/javascript">
	layui.use(['form', 'upload'], function(){
		var upload = layui.upload;
		upload.render({
	        elem: '#updateFile',
	        url: 'staff/excelimport',
	        auto: false,
	        accept: 'file',
	        exts: 'xls|excel|xlsx',
	        bindAction: '#upload',
	        done: function(res){	//导出成功后回调
	        }
	    });
	});
</script>

其中下载模板和导出数据基本一样，只需要创建一个Excel给出一条样式数据即可，代码如下：

@RequestMapping(value = "exportmoban")
@ResponseBody
public void exportmoban(HttpServletRequest request, HttpServletResponse response) throws Exception{
    //创建excel的文档对象
    HSSFWorkbook workbook = new HSSFWorkbook();
    //创建员工信息工作薄，表单
    HSSFSheet sheet = workbook.createSheet("员工信息");
    //在sheet里创建第一行，参数为行索引(excel的行)，从0开始
    HSSFRow row1 = sheet.createRow(0);
    //创建单元格excel的单元格，参数为列索引，从0开始
    HSSFCell cell = row1.createCell(0);
    //设置单元格内容，标题第一行（可以不设置，根据项目需求）
    cell.setCellValue("员工信息");
    //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列，一行标题合并单元格
    sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
    //设置列名（每列的小标题）
    String[] fieldnames = {"姓名", "部门", "年龄", "工号"};
    //在sheet里创建第二行，第一行设置了标题
    HSSFRow row2 = sheet.createRow(1);
    for(int i = 0; i < fieldnames.length; i++) {
        row2.createCell(i).setCellValue(fieldnames[i]);     //设置列名
    }
    //模板数据
    HSSFRow row3 = sheet.createRow(2);
    row3.createCell(0).setCellValue("王五");
    row3.createCell(1).setCellValue("软件部");
    row3.createCell(2).setCellValue(“18“);
    row3.createCell(3).setCellValue("003");
    //输出Excel文件
    OutputStream output=response.getOutputStream();
    response.reset();
    //filename可以设置Excel文件的名称
    response.setHeader("Content-disposition", "attachment; filename=staff.xls");
    response.setContentType("application/msexcel");
    workbook.write(output);
    output.close();
}

模板下载如图。

导入数据，我们只需要选择一个有数据的Excel表格（数据格式需要和模板中格式一样），点击导入即可。

后台对应导入数据的excelimport方法，代码如下：

@RequestMapping(value = "/excelimport")
@ResponseBody
public boolean excelimport(MultipartFile file) {
    // 实例化工具类
    ImportExcel excelReader = new ImportExcel();
    try {
        InputStream is = file.getInputStream();
        // 导入excel
        excelReader.readExcelContent(is);
        Map<Integer, String> map = excelReader.readExcelContent(is);
        //遍历数据保存
        //因为第一行和第二行是标题，所以从2开始
        for (int i = 2; i <= map.size() + 1; i++) {
            String[] scoreArray = map.get(i).split("-");
            if (scoreArray.length > 0) {
                String name = scoreArray[0];
                String dept = scoreArray[1];
                Integer age = Integer.parseInt(scoreArray[2]);
                String number = scoreArray[3];
                Staff staff = new Staff();
                staff.setName(name);
                staff.setDept(dept);
                staff.setAge(age);
                staff.setNumber(number);
                //保存
                staffService.save(staff);
            }
        }
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
}

注： 如果对数据需要做一些效验的话可以放在for循环中处理，这里只是写一个小案例，没有添加任何效验，根据项目需求自己添加。

其中ImportExcel类是一个处理导入的Excel数据的封装类。代码如下：

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class ImportExcel {
	private POIFSFileSystem fs;
	private HSSFWorkbook wb;
	private HSSFSheet sheet;
	private HSSFRow row;

	/**
	 * 读取Excel表格表头的内容
	 * @param is
	 * @return String 表头内容的数组
	 */
	public String[] readExcelTitle(InputStream is) {
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		//得到首行的row
		row = sheet.getRow(0);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title[i] = getCellFormatValue(row.getCell((short) i));
		}
		return title;
	}

	/**
	 * 读取Excel数据内容
	 * @param is
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public Map<Integer, String> readExcelContent(InputStream is) {
		Map<Integer, String> content = new HashMap<Integer, String>();
		String str = "";
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			//e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		//由于第0行和第一行已经合并了  在这里索引从2开始
		row = sheet.getRow(2);
		int colNum = row.getPhysicalNumberOfCells();
		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = 2; i <= rowNum; i++) {
			row = sheet.getRow(i);
			int j = 0;
			while (j < colNum) {
				str += getCellFormatValue(row.getCell((short) j)).trim() + "-";
				j++;
			}
			content.put(i, str);
			str = "";
		}
		return content;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 *
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getStringCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				strCell = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				strCell = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				strCell = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				strCell = "";
				break;
			default:
				strCell = "";
				break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}

	/**
	 * 获取单元格数据内容为日期类型的数据
	 *
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getDateCellValue(HSSFCell cell) {
		String result = "";
		try {
			int cellType = cell.getCellType();
			if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
				Date date = cell.getDateCellValue();
				result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
						+ "-" + date.getDate();
			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
				String date = getStringCellValue(cell);
				result = date.replaceAll("[年月]", "-").replace("日", "").trim();
			} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
				result = "";
			}
		} catch (Exception e) {
			System.out.println("日期格式不正确!");
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 根据HSSFCell类型设置数据
	 * @param cell
	 * @return
	 */
	private String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
				// 如果当前Cell的Type为NUMERIC
				case HSSFCell.CELL_TYPE_NUMERIC:
				case HSSFCell.CELL_TYPE_FORMULA: {
					// 判断当前的cell是否为Date
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						cellvalue = sdf.format(date);
					}
					// 如果是纯数字
					else {
						// 取得当前Cell的数值
						cellvalue = String.valueOf(cell.getNumericCellValue());
					}
					break;
				}
				// 如果当前Cell的Type为STRIN
				case HSSFCell.CELL_TYPE_STRING:
					// 取得当前的Cell字符串
					cellvalue = cell.getRichStringCellValue().getString();
					break;
				// 默认的Cell值
				default:
					cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
}

导入的数据：

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。