excel poi 的xml配置_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格式档案的功能。

使用必须引入依赖

org.apache.poi

poi

3.17

注：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 ListimportXLS(){2

3 ArrayList list = new ArrayList<>();4 try{5 　　　　　//1、获取文件输入流

6 　　　　　InputStream inputStream = new FileInputStream("/Users/Shared/区域数据.xls");7 　　　　　//2、获取Excel工作簿对象

8 HSSFWorkbook workbook = newHSSFWorkbook(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 = newArea();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 　　returnlist;37 }

2、导出数据到“区域数据.xls”文件中，页面数据如下图：

1 public void exportExcel() throwsIOException {2

3 Page page = areaService.pageQuery(null);4 List list =page.getContent();5

6 //1.在内存中创建一个excel文件

7 HSSFWorkbook hssfWorkbook = newHSSFWorkbook();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 classFileUtils {2

3 public static String filenameEncoding(String filename, HttpServletRequest request) throwsIOException {4 String agent = request.getHeader("User-Agent"); //获取浏览器

5 if (agent.contains("Firefox")) {6 BASE64Encoder base64Encoder = newBASE64Encoder();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 returnfilename;18 }19 }

写出xls文件：

五、 EXCEL常用操作方法

1、 得到Excel常用对象

1 POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));2 //得到Excel工作簿对象

3 HSSFWorkbook wb = newHSSFWorkbook(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)rowTo2 ,(short)columnTo);//合并从第rowFrom行columnFrom列

3 sheet.addMergedRegion(region);//到rowTo行columnTo的区域4 //得到所有区域

5 sheet.getNumMergedRegions()

11、保存Excel文件

1 FileOutputStream fileOut = newFileOutputStream(path);2 wb.write(fileOut);

12、根据单元格不同属性返回字符串数值

1 publicString 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 　　　　caseHSSFCell.CELL_TYPE_BLANK:17 　　　　　　　　cellValue=" ";18 　　　　　　　　break;19 　　　　caseHSSFCell.CELL_TYPE_BOOLEAN:20 　　　　　　　　break;21 　　　　caseHSSFCell.CELL_TYPE_ERROR:22 　　　　　　　　break;23 　　　　default:24 　　　　　　　　break;25 }26 　　returncellValue;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 = newByteArrayOutputStream();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 = newPOIFSFileSystem(fos);8 //创建一个工作薄

9 HSSFWorkbook wb = newHSSFWorkbook(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 = newHSSFWorkbook();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);