目录
- 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; } }
导入的数据:
以上为个人经验,希望能给大家一个参考,也希望大家多多支持自由互联。