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

Java
339
0
0
2023-06-06
Java如何支持函数式编程？
上一篇
Java基础——Java多线程（什么是线程安全？）
下一篇
目录
  • 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常用类

类名

说明

HSSFWorkbook

Excel的文件对象

HSSFSheet

Excel的表单

HSSFRow

Excel的行

HSSFCell

Excel的格子单元

HSSFFont

Excel字体

HSSFDataFormat

格子单元的日期格式

HSSFHeader

Excel文档Sheet的页眉

HSSFFooter

Excel文档Sheet的页脚

样式：

 

HSSFCellStyle

cell样式

辅助操作包括：

 

HSSFDateUtil

日期

HSSFPrintSetup

打印

HSSFErrorConstants

错误信息表

数据导出

首先，理解一下一个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;
	}
}

导入的数据：

Java如何支持函数式编程？
上一篇
Java基础——Java多线程（什么是线程安全？）
下一篇