tags: 纳税服务系统项目
前言
再次回到我们的用户模块上，我们发现还有两个功能没有完成：
对于将网页中的数据导入或导出到excel文件中，我们是完全没有学习过的。但是呢，在Java中操作excel是相对常用的，因此也有组件供我们使用
JAVA中操作Excel的有两种比较主流的工具包
- JXL
- POI
这次我们主要学习的是POI操作excel。
JXL有一个缺陷，只能操作03或以前版本的excel，而POI可以操作97-07版本的。
POI基础
面向对象看excel
首先，要用Java来操作excel的话，肯定用对象来指定excel相关的内容的。我们来看看excel由什么组成：
POI是这样看的：
- 整个excel称作为工作薄
- 工作薄下可以创建很多张表，称作为工作表
- 工作表有非常多的行
- 行又可细分单元格【指定行的列就可以定位到工作表任意的位置了】
给我们一顿分析以后，我们发现它们之间是有从属关系的：
- 工作表从属于工作薄
- 行从属于工作表
- 单元格从属于行
操作Excel步骤
导入POI开发包：
- poi-ooxml-3.10.1-20140818.jar，
- poi-ooxml-schemas-3.10.1-20140818.jar，
- 以及复制在ooxml-lib目录下的xmlbeans-2.6.0.jar，dom4j-1.6.1.jar【dom4j一般在项目导入的时候已经存在了】。
- poi-ooxml-3.11-20141221.jar
/** * 使用POI1无非操作Excel无非就4个步骤： * * 创建/读取工作薄 * 创建/读取工作表 * 创建/读取行 * 创建/读取单元格 * * * */复制代码
创建Excel并写入数据
@Test public void testWrite() throws IOException { //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //创建工作表 HSSFSheet sheet = workbook.createSheet("我是新的工作表"); //创建行,坐标从0开始，我创建的是第三行 HSSFRow row = sheet.createRow(2); //创建单元格，坐标也是从0开始，于是就是第三行第三列 HSSFCell cell = row.createCell(2); //往单元格写数据 cell.setCellValue("helloWorld"); //把工作薄写到硬盘中 FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls"); workbook.write(outputStream); //关闭流 workbook.close(); outputStream.close(); }复制代码 读取Excel的数据
@Test public void testRead() throws IOException { //获取输入流，读取Excel数据 FileInputStream inputStream = new FileInputStream("C:\\工作薄.xls"); //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(inputStream); //得到工作表 HSSFSheet sheet = workbook.getSheetAt(0); //得到行 HSSFRow row = sheet.getRow(2); //得到单元格 HSSFCell cell = row.getCell(2); //得到单元格的数据 String cellValue = cell.getStringCellValue(); System.out.println(cellValue); }复制代码 03与07版本
- 03版本使用的是HSSFWorkbook这么一个类来操作03的Excel数据
- 07版本使用的是XSSFWorkbook这么一个类来操作07的Excel数据
其实他们的方法都是一样的，仅仅是类的不同。而使用哪个对象，我们可以根据后缀名来判断创建哪个对象【是03还是07】
@Test public void testRead03And07Excel() throws Exception { String fileName = "D:\\itcast\\测试.xlsx"; if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){ //判断是否excel文档 boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$"); FileInputStream inputStream = new FileInputStream(fileName); //1、读取工作簿 Workbook workbook = is03Excel ?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream); //2、读取第一个工作表 Sheet sheet = workbook.getSheetAt(0); //3、读取行；读取第3行 Row row = sheet.getRow(2); //4、读取单元格；读取第3行第3列 Cell cell = row.getCell(2); System.out.println("第3行第3列单元格的内容为：" + cell.getStringCellValue()); workbook.close(); inputStream.close(); } }复制代码 Excel样式
回到我们的需求中，当我们使用POI导出数据的时候，Excel应该要有样式才好看的。类似下面的模板：
在POI中可以利用格式化对象来格式化excel文档；也即设置excel内容的样式。
POI中主要的格式化对象常用的有：
- 合并单元格
- 设置单元格样式
- 设置单元格字体
- 居中
- 背景颜色等
POI的样式对象明显是属性工作薄的。应用于工作表
合并单元格
属于工作薄，应用于工作表
创建合并单元格对象的时候要给出4个参数，它们分别表示：
- 行的起始位置
- 行的结束位置
- 列的起始位置
- 列的结束位置
@Test public void testCellRange() throws IOException { //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //创建合并单元格对象,从第六行开始到第十行，从第六列开始，到第十列 CellRangeAddress cellRangeAddress = new CellRangeAddress(5, 9, 5, 9); //创建工作表 HSSFSheet sheet = workbook.createSheet("我是新的工作表"); //应用于工作表 sheet.addMergedRegion(cellRangeAddress); //创建行,坐标从0开始，我创建的是第六行 HSSFRow row = sheet.createRow(5); //创建单元格，坐标也是从0开始，于是就是第六行第六列 HSSFCell cell = row.createCell(5); //往单元格写数据 cell.setCellValue("helloWorld"); //把工作薄写到硬盘中 FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls"); workbook.write(outputStream); //关闭流 workbook.close(); outputStream.close(); }复制代码 设置单元格样式
上面的图我们可以发现，我们已经实现了合并单元格，但是一般我们都是将字体设置成居中、字体大小等等。POI也提供了相对应的对象给我们实现：
设置居中
样式属于工作薄，应用于单元格：
@Test public void test() throws IOException { //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //创建样式对象 HSSFCellStyle style = workbook.createCellStyle(); //创建合并单元格对象,从第六行开始到第十行，从第六列开始，到第十列 CellRangeAddress cellRangeAddress = new CellRangeAddress(5, 9, 5, 9); //设置水平居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //创建工作表 HSSFSheet sheet = workbook.createSheet("我是新的工作表"); sheet.addMergedRegion(cellRangeAddress); //创建行,坐标从0开始，我创建的是第六行 HSSFRow row = sheet.createRow(5); //创建单元格，坐标也是从0开始，于是就是第六行第六列 HSSFCell cell = row.createCell(5); //往单元格写数据 cell.setCellValue("helloWorld"); //设置单元格的样式 cell.setCellStyle(style); //把工作薄写到硬盘中 FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls"); workbook.write(outputStream); //关闭流 workbook.close(); outputStream.close(); }复制代码 设置字体
字体属于工作薄，应用于样式【和css是类似的】
@Test public void test() throws IOException { //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //创建样式对象 HSSFCellStyle style = workbook.createCellStyle(); //创建合并单元格对象,从第六行开始到第十行，从第六列开始，到第十列 CellRangeAddress cellRangeAddress = new CellRangeAddress(5, 9, 5, 9); //设置水平居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //创建font对象 HSSFFont font = workbook.createFont(); //设置字体为粗体 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体为23字号 font.setFontHeightInPoints((short) 23); //设置字体的颜色 font.setColor(HSSFFont.COLOR_RED); //字体应用于样式 style.setFont(font); //创建工作表 HSSFSheet sheet = workbook.createSheet("我是新的工作表"); sheet.addMergedRegion(cellRangeAddress); //创建行,坐标从0开始，我创建的是第六行 HSSFRow row = sheet.createRow(5); //创建单元格，坐标也是从0开始，于是就是第六行第六列 HSSFCell cell = row.createCell(5); //往单元格写数据 cell.setCellValue("helloWorld"); //设置单元格的样式 cell.setCellStyle(style); //把工作薄写到硬盘中 FileOutputStream outputStream = new FileOutputStream("C:\\工作薄.xls"); workbook.write(outputStream); //关闭流 workbook.close(); outputStream.close(); }复制代码 实现导出功能#
绑定按钮事件，请求Action处理导出，打开一个输入框给用户下载
function doExportExcel() { window.open("${basePath}user/user_exportExcel.action"); }复制代码 Action处理
/************导出Excel*************************/ public void exportExcel() throws IOException { //查找出列表的全部数据 List list = userServiceImpl.findObjects(); //导出其实就是让用户下载该Excel文件 HttpServletResponse response = ServletActionContext.getResponse(); //设置头和指定名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("列表展示.xls", "UTF-8")); //指定返回的类容数据 response.setContentType("application/x-execl"); ServletOutputStream outputStream = response.getOutputStream(); //给Service层做导出Excel操作 userServiceImpl.exportExcel(list, outputStream); }复制代码 Service实现
/** * 第一行写死，字体大小11，居中，粗体,合并单元格 * 第二行写死，粗体 * 第三行开始，是数据库列表的数据 */ @Override public void exportExcel(Listlist, ServletOutputStream outputStream) { /***********创建工作薄---样式---字体--单元格*************/ HSSFWorkbook workbook = new HSSFWorkbook(); //第一行的合并单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4); //创建第一行样式【居中】 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建第二行样式【居中】 HSSFCellStyle cellStyle2 = workbook.createCellStyle(); cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建第一行字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 23); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //创建第二行字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体应用于样式 cellStyle.setFont(font); cellStyle2.setFont(font2); /***********创建工作表*************/ HSSFSheet sheet = workbook.createSheet("用户列表"); //第一行单元格应用于工作表 sheet.addMergedRegion(cellRangeAddress); //设置默认列宽 sheet.setDefaultColumnWidth(25); /***********创建行*************/ //第一行 HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue("用户列表"); //第二行数据也是写死的，我们用数组遍历即可 String[] data = { "用户名","帐号", "所属部门", "性别", "电子邮箱"}; HSSFRow row1 = sheet.createRow(1); for (int i = 0; i < data.length; i++) { HSSFCell cell1 = row1.createCell(i); cell1.setCellValue(data[i]); //加载第二行样式 cell1.setCellStyle(cellStyle2); } /***************行和列在循环的时候，不要重复了。不然会报错的！！！！*****************/ //第三行数据就是我们数据库保存的数据 if (list != null) { int i=2; for (User user : list) { //从第三行开始 HSSFRow row2 = sheet.createRow(i); HSSFCell row2Cel0 = row2.createCell(0); row2Cel0.setCellValue(user.getName()); HSSFCell row2Cell = row2.createCell(1); row2Cell.setCellValue(user.getAccount()); HSSFCell row2Cel2 = row2.createCell(2); row2Cel2.setCellValue(user.getDept()); HSSFCell row2Cel3 = row2.createCell(3); row2Cel3.setCellValue(user.isGender() ? "男" : "女"); HSSFCell row2Cel4 = row2.createCell(4); row2Cel4.setCellValue(user.getEmail()); i++; } } try { //写到outputSteam上 workbook.write(outputStream); workbook.close(); outputStream.close(); } catch (IOException e) { e.printStackTrace(); } }复制代码
效果
优化一
我们来看下面这段代码，他们都要设置居中，字体就除了大小不同。其他都相同。却占用了这么多代码！！！
//创建第一行样式【居中】 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建第二行样式【居中】 HSSFCellStyle cellStyle2 = workbook.createCellStyle(); cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建第一行字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 23); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //创建第二行字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体应用于样式 cellStyle.setFont(font); cellStyle2.setFont(font2);复制代码
于是我就抽取成一个方法来得到样式
- 需要工作薄来创建样式
- 只有字体的大小是变化的
/** * @param workbook 当前使用工作薄 * @param fontSize 字体大小 * * */ public HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize) { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints(fontSize); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); return cellStyle; }复制代码 当使用的时候，代码就变成了这样调用：
HSSFCellStyle cellStyle = createStyle(workbook, (short) 24); HSSFCellStyle cellStyle2 = createStyle(workbook, (short) 13); 复制代码
优化二
我们的Service业务层的代码看起来太多了。这样我们维护起来就不方便了。
我的做法是：把代码抽取成Utils的方法，Service层调用就好了。
实现导入功能
现在我有这么一个Excel文件，要把信息存储到数据库中，并且在浏览器显示出来
其实导入Excel就是文件上传，只不过不用把文件保存在服务器的硬盘数据中而是保存在数据库中，输出到浏览器就行了。
function doImportExcel() { document.forms[0].action = "${basePath}user/user_importExcel.action"; document.forms[0].submit(); }复制代码 Action封装文件上传
/*************上传Excel************************/ private File userExcel; private String userExcelFileName; private String userExcelContentType; public void setUserExcel(File userExcel) { this.userExcel = userExcel; } public void setUserExcelFileName(String userExcelFileName) { this.userExcelFileName = userExcelFileName; } public void setUserExcelContentType(String userExcelContentType) { this.userExcelContentType = userExcelContentType; }复制代码 Action处理
主要判断有没有上传文件。给Service层处理
/************导入Excel*************************/ public String importExcel() throws IOException { //1、获取excel文件 if(userExcel != null){ //是否是excel if(userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){ //2、导入 userServiceImpl.importExcel(userExcel, userExcelFileName); } } return "list"; }复制代码 Utils封装成集合返回
public static ListimportExcel(File userExcel, String userExcelFileName) { try { FileInputStream fileInputStream = new FileInputStream(userExcel); boolean is03Excel = userExcelFileName.matches("^.+\\.(?i)(xls)$"); //1、读取工作簿 Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream); //2、读取工作表 Sheet sheet = workbook.getSheetAt(0); //3、读取行 List users = new ArrayList<>(); if (sheet.getPhysicalNumberOfRows() > 2) { User user = null; for (int k = 2; k < sheet.getPhysicalNumberOfRows(); k++) { //4、读取单元格 Row row = sheet.getRow(k); user = new User(); //用户名 Cell cell0 = row.getCell(0); user.setName(cell0.getStringCellValue()); //帐号 Cell cell1 = row.getCell(1); user.setAccount(cell1.getStringCellValue()); //所属部门 Cell cell2 = row.getCell(2); user.setDept(cell2.getStringCellValue()); //性别 Cell cell3 = row.getCell(3); user.setGender(cell3.getStringCellValue().equals("男")); //手机号 String mobile = ""; Cell cell4 = row.getCell(4); try { mobile = cell4.getStringCellValue(); } catch (Exception e) { double dMobile = cell4.getNumericCellValue(); mobile = BigDecimal.valueOf(dMobile).toString(); } user.setMobile(mobile); //电子邮箱 Cell cell5 = row.getCell(5); user.setEmail(cell5.getStringCellValue()); //生日 Cell cell6 = row.getCell(6); if (cell6.getDateCellValue() != null) { user.setBirthday(cell6.getDateCellValue()); } //默认用户密码为 123456 user.setPassword("123456"); //默认用户状态为 有效 user.setState(User.USER_STATE_VALID); users.add(user); } } workbook.close(); fileInputStream.close(); return users; } catch (Exception e) { e.printStackTrace(); } return null; }复制代码
Service调用
public void importExcel(File userExcel, String userExcelFileName) { List users = ExcelUtils.importExcel(userExcel, userExcelFileName); for (User user : users) { save(user); } }复制代码 总结
- 导入就是用户上传一个Excel文件，我们读取Excel文件的数据，封装成对象，存进数据库中
- 导出就是将我们数据库的数据写到Excel文件中，让用户能够进行下载
- 指定我们的返回类型是excel
- 工作表从属于工作薄，行从属于工作表，单元格从属于行
- 在POI组件中，Excel文件我们又分为了两类，03版和07版的，他俩对应了不同的对象，但这两个对象的API都是一样的。因此我们可以通过文件的后缀来判断使用哪个对象
- 我们的Excel还是应该有样式才好看的。POI也提供了对应的API给我们修改样式
- 合并单元格，从属于工作薄，应用与工作表
- 设置居中、字体的大小都是属于样式的。从属于工作薄，应用与单元格。
如果您觉得这篇文章帮助到了您，可以给作者一点鼓励