- Excel
- Excel说明
- Excel的两种版本
- 常见的Excel操作工具
- JXL
- POI
在企业级应用开发中,Excel报表是一种最常见的报表需求。Excel报表开发一般分为两种形式:
- 为了方便操作,基于Excel的报表批量上传数据,也就是把Excel中的数据导入到系统中。
- 通过java代码生成Excel报表。也就是把系统中的数据导出到Excel中,方便查阅。
- Excel2003 是一个特有的二进制格式,其核心结构是复合文档类型的结构,存储数据量较小;
- Excel2007 的核心结构是 XML 类型的结构,采用的是基于 XML 的压缩方式,使其占用的空间更小,操作效率更高。
JXL只能对Excel进行操作,属于比较老的框架,它只支持到Excel 95-2000的版本。现在已经停止更新和维护,所以本课程中只时简单地演示一下jxl的代码,不会把它作为重点。
主要语法:
1、 创建可写入的Excel工作薄
WritableWorkbook workbook= Workbook.createWorkbook(输出流);
2、创建工作表
WritableSheet sheet= workbook.createSheet(工作表的名称, 工作表的索引值);
3、创建单元格
添加文本类单元格
Label labelC = new Label(列索引值, 行索引值, "单元格中的内容"); sheet.addCell(labelC);
4、写入到文件
workbook.write();// 写入数据
5、释放资源:
workbook.close();// 关闭文件
使用jxl导出一些简单的excel(很少使用)
public void downLoadXlsByJxl(HttpServletResponse response){ try { //创建一个工作薄 ServletOutputStream outputStream = response.getOutputStream(); WritableWorkbook workbook = Workbook.createWorkbook(outputStream); //创建一个工作表 WritableSheet sheet = workbook.createSheet("一个JXL入门", 0); //设置列宽 sheet.setColumnView(0,5); sheet.setColumnView(1,8); sheet.setColumnView(2,15); sheet.setColumnView(3,15); sheet.setColumnView(4,30); //处理标题 String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"}; Label label = null; for (int i = 0; i < titles.length; i++) { label = new Label(i,0,titles[i]); sheet.addCell(label); } //处理导出的内容 ListuserList = this.findAll(); int rowIndex = 1; for (User user : userList) { label = new Label(0,rowIndex,user.getId().toString()); sheet.addCell(label); label = new Label(1,rowIndex,user.getUserName()); sheet.addCell(label); label = new Label(2,rowIndex,user.getPhone()); sheet.addCell(label); label = new Label(3,rowIndex,simpleDateFormat.format(user.getHireDate())); sheet.addCell(label); label = new Label(4,rowIndex,user.getAddress()); sheet.addCell(label); rowIndex++; } //导出的文件名称 String filename="一个JXL入门.xls"; //设置文件的打开方式和mime类型 response.setHeader( "Content-Disposition", "attachment;filename=" + new String(filename.getBytes(),"ISO8859-1")); response.setContentType("application/vnd.ms-excel"); //导出 workbook.write(); //关闭资源 workbook.close(); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } }
结果如图:
- POI是apache的项目,可对微软的Word,Excel,PPT进行操作,包括office2003和2007,Excle2003和2007。POI现在一直有更新。所以现在主流使用POI。
- Apache POI是Apache软件基金会的开源项目,由Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java语言操作Microsoft Office的功能。
API对象介绍:
工作簿:WorkBook | HSSFWordBook :2003版本,XSSFWorkBook :2007级以上 |
---|---|
工作表 : Sheet | HSSFSheet :2003版本,XSSFSheet :2007级以上 |
行 :Row | HSSFRow :2003版本,XSSFRow :2007级以上 |
单元格: Cell | HSSFCell :2003版本,XSSFCell :2007级以上 |
POI操作Excel高低版本区别
对应excel名称 | 低版本中的类名 | 高版本中的类名 |
---|---|---|
工作簿 | HSSFWorkbook | XSSFWorkbook |
工作表 | HSSFSheet | XSSFSheet |
行 | HSSFRow | XSSFRow |
单元格 | HSSFCell | XSSFCell |
单元格样式 | HSSFCellStyle | XSSFCellStyle |
1.添加依赖
org.apache.poi poi4.0.1 org.apache.poi poi-ooxml4.0.1 org.apache.poi poi-ooxml-schemas4.0.1
2.代码测试
操作低版本
public class POIDemo01 { public static void main(String[] args) throws Exception{ Workbook workbook = new HSSFWorkbook(); //创建了一个全新(里面什么都没有)的工作薄 Sheet sheet = workbook.createSheet("demo测试"); //创建了一个全新(里面什么都没有)的工作表 Row row = sheet.createRow(0); //创建了第一行(空的) Cell cell = row.createCell(0);//创建的是第一行的第一个单元格 cell.setCellValue("这是我第一次玩POI"); //把工作薄输出到本地磁盘 workbook.write(new FileOutputStream("d://test.xls")); } }
操作高版本
public class POIDemo02 { public static void main(String[] args) throws Exception{ Workbook workbook = new XSSFWorkbook(); //创建了一个全新(里面什么都没有)的工作薄 Sheet sheet = workbook.createSheet("demo测试"); //创建了一个全新(里面什么都没有)的工作表 Row row = sheet.createRow(0); //创建了第一行(空的) Cell cell = row.createCell(0);//创建的是第一行的第一个单元格 cell.setCellValue("这是我第一次玩POI"); //把工作薄输出到本地磁盘 workbook.write(new FileOutputStream("d://test.xlsx")); } }
数据导入
数据的导入就是读取excel中的内容,转成对象插入到数据库中
实现步骤:
1、根据上传的文件创建Workbook
2、获取到第一个sheet工作表
3、从第二行开始读取数据
4、读取每一个单元格,把内容放入到用户对象的相关的属性中
public void uploadExcel(MultipartFile file) throws Exception { Workbook workbook = new XSSFWorkbook(file.getInputStream()); //根据上传的输入流创建workbook Sheet sheet = workbook.getSheetAt(0); //获取工作薄中的第一个工作表 int lastRowIndex = sheet.getLastRowNum(); //获取这个sheet中最后一行数据,为了循环遍历 //以下三个为了节省栈内存,所以提到循环的外面 User user = null; Row row = null; Cell cell = null; //开始循环每行,获取每行的单元格中的值,放入到user属性中 for (int i = 1; i <= lastRowIndex; i++) { row = sheet.getRow(i); user = new User(); //因为第一个列单元格中是字符串,可以直接使用getStringCellValue方法 String userName = row.getCell(0).getStringCellValue(); //用户名 user.setUserName(userName); String phone = null; //手机号 try { phone = row.getCell(1).getStringCellValue(); } catch (IllegalStateException e) { phone = row.getCell(1).getNumericCellValue()+""; } user.setPhone(phone); String province = row.getCell(2).getStringCellValue(); //省份 user.setProvince(province); String city = row.getCell(3).getStringCellValue(); //城市 user.setCity(city); //因为在填写excel中的数据时就可以约定这个列只能填写数值,所以可以直接用getNumericCellValue方法 Integer salary = ((Double)row.getCell(4).getNumericCellValue()).intValue(); //工资 user.setSalary(salary); String hireDateStr = row.getCell(5).getStringCellValue(); //入职日期 Date hireDate = simpleDateFormat.parse(hireDateStr); user.setHireDate(hireDate); String birthdayStr = row.getCell(6).getStringCellValue(); //出生日期 Date birthday = simpleDateFormat.parse(birthdayStr); user.setBirthday(birthday); String address = row.getCell(7).getStringCellValue(); //现住地址 user.setAddress(address); userMapper.insert(user); } }
数据导出
1、创建一个全新的工作薄
2、在新的工作薄中创建一个新的工作表
3、在工作表创建第一行作为标题行,标题固定
4、从第二行循环遍历创建,有多少条用户数据就应该创建多少行
5、把每一个user对象的属性放入到相应的单元格中
public void downLoadXlsx(HttpServletResponse response) throws Exception { //创建一个空的工作薄 Workbook workbook = new XSSFWorkbook(); //在工作薄中创建一个工作表 Sheet sheet = workbook.createSheet("测试"); //设置列宽 sheet.setColumnWidth(0,5*256); sheet.setColumnWidth(1,8*256); sheet.setColumnWidth(2,15*256); sheet.setColumnWidth(3,15*256); sheet.setColumnWidth(4,30*256); //处理标题 String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"}; //创建标题行 Row titleRow = sheet.createRow(0); Cell cell = null; for (int i = 0; i < titles.length; i++) { cell = titleRow.createCell(i); cell.setCellValue(titles[i]); } //处理内容 ListuserList = this.findAll(); int rowIndex = 1; Row row = null; for (User user : userList) { row = sheet.createRow(rowIndex); cell = row.createCell(0); cell.setCellValue(user.getId()); cell = row.createCell(1); cell.setCellValue(user.getUserName()); cell = row.createCell(2); cell.setCellValue(user.getPhone()); cell = row.createCell(3); cell.setCellValue(simpleDateFormat.format(user.getHireDate())); cell = row.createCell(4); cell.setCellValue(user.getAddress()); rowIndex++; } //导出的文件名称 String filename="员工数据.xlsx"; //设置文件的打开方式和mime类型 ServletOutputStream outputStream = response.getOutputStream(); response.setHeader( "Content-Disposition", "attachment;filename=" + new String(filename.getBytes(),"ISO8859-1")); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); workbook.write(outputStream); }
导出带样式的数据
public void downLoadXlsxByPoiWithCellStyle(HttpServletResponse response) throws Exception { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); Row bigTitleRow = sheet.createRow(0); //1.边框线(全边框),行高(42),合并单元格(第一行第一个到第五个单元格),对齐方式(水平垂直居中),字体:黑体18号 CellStyle bigTitleRowCellStyle = workbook.createCellStyle(); //上下左右边框 bigTitleRowCellStyle.setBorderTop(BorderStyle.THIN);//BorderStyle.THIN细线 bigTitleRowCellStyle.setBorderBottom(BorderStyle.THIN); bigTitleRowCellStyle.setBorderLeft(BorderStyle.THIN); bigTitleRowCellStyle.setBorderRight(BorderStyle.THIN); //对齐方式,水平居中对齐,垂直居中对齐 bigTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER); bigTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置行高 bigTitleRow.setHeightInPoints(42); //设置列宽 sheet.setColumnWidth(0,5*256); sheet.setColumnWidth(1,10*256); sheet.setColumnWidth(2,10*256); sheet.setColumnWidth(3,10*256); sheet.setColumnWidth(4,30*256); //字体 //1.创建字体 Font font = workbook.createFont(); //2.设置字体样式 font.setFontName("黑体"); font.setFontHeightInPoints((short) 18); //3.字体放入样式中 bigTitleRowCellStyle.setFont(font); //--5个单元格合并 for (int i = 0; i < 5; i++) { Cell cell = bigTitleRow.createCell(i); cell.setCellStyle(bigTitleRowCellStyle); } //起始行,结束行,开始列,结束列 sheet.addMergedRegion(new CellRangeAddress(0,0,0,4)); //向单元格中添加句子 bigTitleRow.getCell(0).setCellValue("用户信息数据"); Row littleTitleRow = sheet.createRow(1); //1.边框线(全边框),行高(42),合并单元格(第一行第一个到第五个单元格),对齐方式(水平垂直居中),字体:黑体18号 CellStyle littleTitleRowCellStyle = workbook.createCellStyle(); //克隆大标题的样式 //上下左右边框 //对齐方式,水平居中对齐,垂直居中对齐 littleTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle); //设置列宽 littleTitleRow.setHeightInPoints(32); //字体 //1.创建字体 Font littleFont = workbook.createFont(); //2.设置字体样式 littleFont.setFontName("宋体"); littleFont.setFontHeightInPoints((short) 12); littleFont.setBold(true); //3.字体放入样式中 littleTitleRowCellStyle.setFont(littleFont); //添加小标题内容 String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"}; for (int i = 0; i < 5; i++) { Cell cell = littleTitleRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(littleTitleRowCellStyle); } CellStyle contentRowCellStyle = workbook.createCellStyle(); contentRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle); contentRowCellStyle.setAlignment(HorizontalAlignment.LEFT); Font contentFont = workbook.createFont(); contentFont.setFontName("宋体"); contentFont.setFontHeightInPoints((short) 11); contentFont.setBold(false); contentRowCellStyle.setFont(contentFont); Row contentRow = null; Cell cell = null; int contentRowIndex = 2; //添加用户数据 Listusers = userMapper.selectAll(); for (User user : users) { contentRow = sheet.createRow(contentRowIndex); cell = contentRow.createCell(0); cell.setCellValue(user.getId()); cell = contentRow.createCell(1); cell.setCellValue(user.getUserName()); cell = contentRow.createCell(2); cell.setCellValue(user.getPhone()); cell = contentRow.createCell(3); cell.setCellValue(simpleDateFormat.format(user.getHireDate())); cell = contentRow.createCell(4); cell.setCellValue(user.getAddress()); cell.setCellStyle(contentRowCellStyle); contentRowIndex++; } String fileName = "员工样式数据.xlsx"; response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1")); response.setContentType("application/vnd.ms-excel"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); workbook.write(response.getOutputStream()); }
根据模板导出数据
public void downLoadXlsxByPoiWithTemplate(HttpServletResponse response) throws Exception { //1.获取到模板 //获取项目的根目录,创建目录 File rootFile = new File(ResourceUtils.getURL("classpath:").getPath()); File templateFile = new File(rootFile, "/excel_template/userList.xlsx"); //创建有模板的工作簿 Workbook workbook = new XSSFWorkbook(templateFile); Sheet sheet = workbook.getSheetAt(0); //2.查询所有的用户数据 Listlist = userMapper.selectAll(); //3.放入到模板中 int rowIndex = 2; Row row = null; Cell cell = null; //获取准备好的内容单元格样式,第二个sheet的第一个单元格的样式 CellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle(); for (User user : list) { row = sheet.createRow(rowIndex); row.setHeightInPoints(15); cell = row.createCell(0); cell.setCellValue(user.getId()); cell = row.createCell(1); cell.setCellValue(user.getUserName()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(user.getPhone()); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellValue(simpleDateFormat.format(user.getHireDate())); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(user.getAddress()); cell.setCellStyle(cellStyle); rowIndex++; } //删除第二个sheet workbook.removeSheetAt(1); //4.导出文件 //一个流两个头 String fileName = "员工模板数据.xlsx"; response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1")); response.setContentType("application/vnd.ms-excel"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //执行 workbook.write(response.getOutputStream()); }
模板样式:sheet1
sheet2
代码:https://gitee.com/suisui9857/user_management