POI操作Excel
一、POI概述
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
结构：
-
- HSSF － 提供读写Microsoft Excel格式档案的功能。
- XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
- HWPF － 提供读写Microsoft Word格式档案的功能。
- HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
- HDGF － 提供读写Microsoft Visio格式档案的功能。
使用必须引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
注：3.17版本是支持jdk6的最后版本
二、HSSF概况
HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。
三、 POI EXCEL文档结构类
- HSSFWorkbook excel文档对象
- HSSFSheet excel的sheet
- HSSFRow excel的行
- HSSFCell excel的单元格
- HSSFFont excel字体
- HSSFName 名称
- HSSFDataFormat 日期格式
- HSSFHeader sheet头
- HSSFFooter sheet尾
- HSSFCellStyle cell样式
- HSSFDateUtil 日期
- HSSFPrintSetup 打印
- HSSFErrorConstants 错误信息表
四、EXCEL的读写操作
1、读取“区域数据.xls”并储存于list集合中，“区域数据.xls”如下图
1 public List<Area> importXLS(){ 2 3 ArrayList<Area> list = new ArrayList<>(); 4 try { 5 //1、获取文件输入流 6 InputStream inputStream = new FileInputStream("/Users/Shared/区域数据.xls"); 7 //2、获取Excel工作簿对象 8 HSSFWorkbook workbook = new HSSFWorkbook(inputStream); 9 //3、得到Excel工作表对象 10 HSSFSheet sheetAt = workbook.getSheetAt(0); 11 //4、循环读取表格数据 12 for (Row row : sheetAt) { 13 //首行（即表头）不读取 14 if (row.getRowNum() == 0) { 15 continue; 16 } 17 //读取当前行中单元格数据，索引从0开始 18 String areaNum = row.getCell(0).getStringCellValue(); 19 String province = row.getCell(1).getStringCellValue(); 20 String city = row.getCell(2).getStringCellValue(); 21 String district = row.getCell(3).getStringCellValue(); 22 String postcode = row.getCell(4).getStringCellValue(); 23 24 Area area = new Area(); 25 area.setCity(city); 26 area.setDistrict(district); 27 area.setProvince(province); 28 area.setPostCode(postcode); 29 list.add(area); 30 } 31 //5、关闭流 32 workbook.close(); 33 } catch (IOException e) { 34 e.printStackTrace(); 35 } 36 return list; 37 }
2、导出数据到“区域数据.xls”文件中，页面数据如下图：
1 public void exportExcel() throws IOException { 2 3 Page<Area> page = areaService.pageQuery(null); 4 List<Area> list = page.getContent(); 5 6 //1.在内存中创建一个excel文件 7 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); 8 //2.创建工作簿 9 HSSFSheet sheet = hssfWorkbook.createSheet(); 10 //3.创建标题行 11 HSSFRow titlerRow = sheet.createRow(0); 12 titlerRow.createCell(0).setCellValue("省"); 13 titlerRow.createCell(1).setCellValue("市"); 14 titlerRow.createCell(2).setCellValue("区"); 15 titlerRow.createCell(3).setCellValue("邮编"); 16 titlerRow.createCell(4).setCellValue("简码"); 17 titlerRow.createCell(5).setCellValue("城市编码"); 18 19 //4.遍历数据,创建数据行 20 for (Area area : list) { 21 //获取最后一行的行号 22 int lastRowNum = sheet.getLastRowNum(); 23 HSSFRow dataRow = sheet.createRow(lastRowNum + 1); 24 dataRow.createCell(0).setCellValue(area.getProvince()); 25 dataRow.createCell(1).setCellValue(area.getCity()); 26 dataRow.createCell(2).setCellValue(area.getDistrict()); 27 dataRow.createCell(3).setCellValue(area.getPostcode()); 28 dataRow.createCell(4).setCellValue(area.getShortcode()); 29 dataRow.createCell(5).setCellValue(area.getCitycode()); 30 } 31 //5.创建文件名 32 String fileName = "区域数据统计.xls"; 33 //6.获取输出流对象 34 HttpServletResponse response = ServletActionContext.getResponse(); 35 ServletOutputStream outputStream = response.getOutputStream(); 36 37 //7.获取mimeType 38 ServletContext servletContext = ServletActionContext.getServletContext(); 39 String mimeType = servletContext.getMimeType(fileName); 40 //8.获取浏览器信息,对文件名进行重新编码 41 HttpServletRequest request = ServletActionContext.getRequest(); 42 fileName = FileUtils.filenameEncoding(fileName, request); 43 44 //9.设置信息头 45 response.setContentType(mimeType); 46 response.setHeader("Content-Disposition","attachment;filename="+fileName); 47 //10.写出文件,关闭流 48 hssfWorkbook.write(outputStream); 49 hssfWorkbook.close(); 50 }
工具类
1 public class FileUtils { 2 3 public static String filenameEncoding(String filename, HttpServletRequest request) throws IOException { 4 String agent = request.getHeader("User-Agent"); //获取浏览器 5 if (agent.contains("Firefox")) { 6 BASE64Encoder base64Encoder = new BASE64Encoder(); 7 filename = "=?utf-8?B?" 8 + base64Encoder.encode(filename.getBytes("utf-8")) 9 + "?="; 10 } else if(agent.contains("MSIE")) { 11 filename = URLEncoder.encode(filename, "utf-8"); 12 } else if(agent.contains ("Safari")) { 13 filename = new String (filename.getBytes ("utf-8"),"ISO8859-1"); 14 } else { 15 filename = URLEncoder.encode(filename, "utf-8"); 16 } 17 return filename; 18 } 19 }
写出xls文件：
五、 EXCEL常用操作方法
1、 得到Excel常用对象
1 POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls")); 2 //得到Excel工作簿对象 3 HSSFWorkbook wb = new HSSFWorkbook(fs); 4 //得到Excel工作表对象 5 HSSFSheet sheet = wb.getSheetAt(0); 6 //得到Excel工作表的行 7 HSSFRow row = sheet.getRow(i); 8 //得到Excel工作表指定行的单元格 9 HSSFCell cell = row.getCell((short) j); 10 cellStyle = cell.getCellStyle();//得到单元格样式
2、建立Excel常用对象
1 HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象 2 HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象 3 HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行 4 cellStyle = wb.createCellStyle();//创建单元格样式 5 row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格 6 row.createCell((short)0).setCellValue(1); //设置Excel工作表的值
3、设置sheet名称和单元格内容
1 wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16); 2 cell.setEncoding((short) 1); 3 cell.setCellValue("单元格内容");
4、取得sheet的数目
1 wb.getNumberOfSheets()
5、 根据index取得sheet对象
1 HSSFSheet sheet = wb.getSheetAt(0);
6、取得有效的行数
1 int rowcount = sheet.getLastRowNum();
7、取得一行的有效单元格个数
1 row.getLastCellNum();
8、单元格值类型读写
1 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型 2 cell.getNumericCellValue();//读取为数值类型的单元格内容
9、设置列宽、行高
1 sheet.setColumnWidth((short)column,(short)width); 2 row.setHeight((short)height);
10、添加区域，合并单元格
1 Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo 2 ,(short)columnTo);//合并从第rowFrom行columnFrom列 3 sheet.addMergedRegion(region);// 到rowTo行columnTo的区域 4 //得到所有区域 5 sheet.getNumMergedRegions()
11、保存Excel文件
1 FileOutputStream fileOut = new FileOutputStream(path); 2 wb.write(fileOut);
12、根据单元格不同属性返回字符串数值
1 public String getCellStringValue(HSSFCell cell) { 2 String cellValue = ""; 3 switch (cell.getCellType()) { 4 case HSSFCell.CELL_TYPE_STRING://字符串类型 5 cellValue = cell.getStringCellValue(); 6 if(cellValue.trim().equals("")||cellValue.trim().length()<=0) 7 cellValue=" "; 8 break; 9 case HSSFCell.CELL_TYPE_NUMERIC: //数值类型 10 cellValue = String.valueOf(cell.getNumericCellValue()); 11 break; 12 case HSSFCell.CELL_TYPE_FORMULA: //公式 13 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); 14 cellValue = String.valueOf(cell.getNumericCellValue()); 15 break; 16 case HSSFCell.CELL_TYPE_BLANK: 17 cellValue=" "; 18 break; 19 case HSSFCell.CELL_TYPE_BOOLEAN: 20 break; 21 case HSSFCell.CELL_TYPE_ERROR: 22 break; 23 default: 24 break; 25 } 26 return cellValue; 27 }
13、常用单元格边框格式
1 HSSFCellStyle style = wb.createCellStyle(); 2 style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框 3 style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框 4 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 5 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
14、设置字体和内容位置
1 HSSFFont f = wb.createFont(); 2 f.setFontHeightInPoints((short) 11);//字号 3 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 4 style.setFont(f); 5 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中 6 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 7 style.setRotation(short rotation);//单元格内容的旋转的角度 8 HSSFDataFormat df = wb.createDataFormat(); 9 style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式 10 cell.setCellFormula(string);//给单元格设公式 11 style.setRotation(short rotation);//单元格内容的旋转的角度
15、插入图片
1 //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray 2 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 3 BufferedImage bufferImg = ImageIO.read(new File("ok.jpg")); 4 ImageIO.write(bufferImg,"jpg",byteArrayOut); 5 //读进一个excel模版 6 FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt"); 7 fs = new POIFSFileSystem(fos); 8 //创建一个工作薄 9 HSSFWorkbook wb = new HSSFWorkbook(fs); 10 HSSFSheet sheet = wb.getSheetAt(0); 11 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 12 HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10); 13 patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16、调整工作表位置
1 HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("format sheet"); 3 HSSFPrintSetup ps = sheet.getPrintSetup(); 4 sheet.setAutobreaks(true); 5 ps.setFitHeight((short)1); 6 ps.setFitWidth((short)1);
