Java读写Excel之POI入门

JavaPOI读取excel支持xls、xlsxJava POI读取excel 支持xls、xlsximport java.io.File;import java.io.FileInputStream;import java .util.List;import ermodel.HSSFPicture;import o ermodel.HSSFPictureData;import org.apach ermodel.HSSFShape;import er model.HSSFSheet;import ermodel.HSSFW orkbook;import ermodel.Cell;import org.ap ermodel.DateUtil;import ermo del.Row;public final class T estImportExcel {public static void main(String[] args) throws Exception {File excelFile = new File("test.xls"); HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile)); HSS FSheet sheet = wb.getSheetAt(0);for (Row row : sheet) { for (Cell cell : row) { s witch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getRichStringCellValue().getString());break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) {System.out.print(String.valueOf(cell.getDateCellValue()));} else {System.out.print(cell.getNumericCellValue());} break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); bre ak; default:}}System.out.println();}//读取图片List<HSSFPictureData> pictures = wb.getAllPictures();for (HSSFShape shape : sheet.getDrawingPatriarch().getChil dren()) {if (shape instanceof HSSFPicture) { HSSFPicture pic = (HSSFPicture) shape;int pictureIndex = pic.getPictureIndex()-1;HSSFPictureData picData = pictures.get(pictureIndex);System.out.println("image-size:" + picData.getData().length);}}System.out.println(wb.getSheetName(0));}}。

java使⽤POI读取excel数据⼀、定义 Apache POI是Apache软件基⾦会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

⼆、所需jar包：三、简单的⼀个读取excel的demo1、读取⽂件⽅法/*** 读取出filePath中的所有数据信息* @param filePath excel⽂件的绝对路径**/public static void getDataFromExcel(String filePath){//String filePath = "E:\\123.xlsx";//判断是否为excel类型⽂件if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")){System.out.println("⽂件不是excel类型");}FileInputStream fis =null;Workbook wookbook = null;try{//获取⼀个绝对地址的流fis = new FileInputStream(filePath);}catch(Exception e){e.printStackTrace();}try{//2003版本的excel，⽤.xls结尾wookbook = new HSSFWorkbook(fis);//得到⼯作簿}catch (Exception ex){//ex.printStackTrace();try{//2007版本的excel，⽤.xlsx结尾wookbook = new XSSFWorkbook(fis);//得到⼯作簿} catch (IOException e){// TODO Auto-generated catch blocke.printStackTrace();}}//得到⼀个⼯作表Sheet sheet = wookbook.getSheetAt(0);//获得表头Row rowHead = sheet.getRow(0);//判断表头是否正确if(rowHead.getPhysicalNumberOfCells() != 3){System.out.println("表头的数量不对!");}//获得数据的总⾏数int totalRowNum = sheet.getLastRowNum();//要获得属性String name = "";int latitude = 0;//获得所有数据for(int i = 1 ; i <= totalRowNum ; i++){//获得第i⾏对象Row row = sheet.getRow(i);//获得获得第i⾏第0列的 String类型对象Cell cell = row.getCell((short)0);name = cell.getStringCellValue().toString();//获得⼀个数字类型的数据cell = row.getCell((short)1);latitude = (int) cell.getNumericCellValue();System.out.println("名字："+name+",经纬度："+latitude); }}2、测试public static void main(String[] args){getDataFromExcel("E:"+ File.separator +"123.xlsx");}3、原始数据4、结果名字：A1,经纬度：1名字：A2,经纬度：2名字：A3,经纬度：3名字：A4,经纬度：4名字：A5,经纬度：5名字：A6,经纬度：6名字：A7,经纬度：7名字：A8,经纬度：8名字：A9,经纬度：9名字：A10,经纬度：10名字：A11,经纬度：11。

java的POI操作Excel文件（2）现在我要做的东西基本完成啦，我把这段时间使用POI的一些心得总结出来，希望能对和我遇到相同问题的朋友有所帮助，至于POI基本的使用方法，自己去看文档吧。

1、设置分页符的bugPOI里的HSSFSheet类提供了setRowBreak方法可以设置Sheet的分页符。

Bug：如果你要设置分页符的Sheet是本来就有的，并且你没有在里面插入过分页符，那么调用setRowBreak时POI会抛出空指针的异常。

解决方法：在Excel里给这个sheet插入一个分页符，用POI打开后再把它删掉，然后你就可以随意插入分页符了。

如果sheet是由POI生成的则没有这个问题。

我跟踪了setRowBreak的源代码，发现是Sheet.Java下的PageBreakRecord rowBreaks这个变量在搞鬼，如果Sheet里原来没有分页符，开发这个模块的那位兄台忘了为这个对象new实例，所以只能我们先手工给Excel插入一个分页符来触发POI为rowBreaks创建实例。

2、如何拷贝行我在的POI用户论坛翻遍了每个相关的帖子，找遍了api，也没看到一个拷贝行的方法，没办法，只能自己写：//注：this.fWorkbook是一个HSSHWorkbook，请自行在外部newpublic void copyRows(String pSourceSheetName,String pTargetSheetName,int pStartRow, int pEndRow,int pPosition){HSSFRow sourceRow = null;HSSFRow targetRow = null;HSSFCell sourceCell = null;HSSFCell targetCell = null;HSSFSheet sourceSheet = null;HSSFSheet targetSheet = null;Region region = null;int cType;int i;short j;int targetRowFrom;int targetRowTo;if ((pStartRow == -1) || (pEndRow == -1)){return;}sourceSheet = this.fWorkbook.getSheet(pSourceSheetName);targetSheet = this.fWorkbook.getSheet(pTargetSheetName);//拷贝合并的单元格for (i = 0; i < sourceSheet.getNumMergedRegions(); i++){region = sourceSheet.getMergedRegionAt(i);if ((region.getRowFrom() >= pStartRow) && (region.getRowTo() <= pEndRow)) {targetRowFrom = region.getRowFrom() - pStartRow + pPosition; targetRowTo = region.getRowTo() - pStartRow + pPosition;region.setRowFrom(targetRowFrom);region.setRowTo(targetRowTo);targetSheet.addMergedRegion(region);}}//设置列宽for (i = pStartRow; i <= pEndRow; i++){sourceRow = sourceSheet.getRow(i);if (sourceRow != null){for (j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++) {targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));}break;}}//拷贝行并填充数据for (;i <= pEndRow; i++){sourceRow = sourceSheet.getRow(i);if (sourceRow == null){continue;}targetRow = targetSheet.createRow(i - pStartRow + pPosition);targetRow.setHeight(sourceRow.getHeight());for (j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++)sourceCell = sourceRow.getCell(j);if (sourceCell == null){continue;}targetCell = targetRow.createCell(j);targetCell.setEncoding(sourceCell.getEncoding());targetCell.setCellStyle(sourceCell.getCellStyle());cType = sourceCell.getCellType();targetCell.setCellType(cType);switch (cType){case HSSFCell.CELL_TYPE_BOOLEAN:targetCell.setCellValue(sourceCell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_ERROR:targetCell.setCellErrorV alue(sourceCell.getErrorCellValue());break;case HSSFCell.CELL_TYPE_FORMULA://parseFormula这个函数的用途在后面说明targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));break;case HSSFCell.CELL_TYPE_NUMERIC:targetCell.setCellValue(sourceCell.getNumericCellV alue());break;case HSSFCell.CELL_TYPE_STRING:targetCell.setCellValue(sourceCell.getStringCellValue());break;}}}}这个函数有两个问题暂时无法解决：a、只能在同一个Workbook里面使用，跨Workbook总是拷不过去，不知道为什么？b、由于在拷贝行时也把行高也拷过去了，如果往这些单元格里写入的数据长度超过单元格长度，那么他们不会自动调整行高！3、公式的问题POI对Excel公式的支持是相当好的，但是我发现一个问题，如果公式里面的函数不带参数，比如now()或today()，那么你通过getCellFormula()取出来的值就是now(ATTR(semiV olatile))和today(ATTR(semiV olatile))，这样的值写入Excel是会出错的，这也是我上面copyRow的函数在写入公式前要调用parseFormula的原因，parseFormula这个函数的功能很简单，就是把ATTR(semiVolatile)删掉，我把它的代码贴出来：private String parseFormula(String pPOIFormula){final String cstReplaceString = "ATTR(semiV olatile)"; //$NON-NLS-1$StringBuffer result = null;int index;result = new StringBuffer();index = pPOIFormula.indexOf(cstReplaceString);if (index >= 0){result.append(pPOIFormula.substring(0, index));result.append(pPOIFormula.substring(index + cstReplaceString.length()));}else{result.append(pPOIFormula);}return result.toString();}至于为什么会出现ATTR(semiVolatile)，还需要大家的探索精神！4、向Excel写入图片的问题。

一、介绍在开发过程中，我们经常会遇到需要读取Excel文件内容的情况。

而Java作为一种广泛应用的编程语言，具有丰富的库和功能，使得它成为一种很好的选择。

本文将介绍如何使用Java来获取Excel文件的内容，并将分为以下几个部分来进行讲解。

二、使用POI库进行Excel文件操作POI（Poor Obfuscation Implementation）是Apache基金会的一个开源项目，它为Java程序提供了读取和写入Microsoft Office格式文件的功能。

具体来说，POI库中的HSSF模块可以用于读取和操作Excel文件。

以下是使用POI库进行Excel文件操作的步骤。

1. 引入POI库首先需要引入POI库的相关依赖。

可以通过Maven来引入POI库，添加以下依赖到项目的pom.xml文件中即可：```xml<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>```2. 读取Excel文件接下来，我们可以通过POI库的相关类来读取Excel文件。

首先需要创建一个文件输入流来读取Excel文件，然后通过HSSFWorkbook类来加载文件内容，最后可以通过遍历的方式获取Excel文件的内容。

以下是一个简单的示例：```javaFileInputStream file = new FileInputStream("example.xls"); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0);for (Row row : sheet) {for (Cell cell : row) {// 处理单元格的内容}}```3. 处理Excel文件内容在读取Excel文件内容后，我们可以通过POI库提供的类来处理Excel 文件的内容，比如获取单元格的值、设置单元格的值等操作。

Java使⽤POI读取和写⼊Excel指南（转）做项⽬时经常有通过程序读取Excel数据，或是创建新的Excel并写⼊数据的需求；⽹上很多经验教程⾥使⽤的POI版本都⽐较⽼了，⼀些API在新版⾥已经废弃，这⾥基于最新的Apache POI 4.0.1版本来总结⼀下整个读取和写⼊Excel的过程，希望能帮助到需要的⼈ ^_^1. 准备⼯作1.1 在项⽬中引⼊Apache POI相关类库引⼊ Apache POI 和 Apache POI-OOXML 这两个类库，Maven坐标如下：<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version></dependency>2. 读取或写⼊Excel数据2.1 ⽰例程序结构说明简单说明⼀下⽰例程序的整体结构：ExcelReader.java是实现读取Excel数据功能的类；ExcelWriter.java是创建新的Excel并向其中写⼊数据的类；ExcelDataVO.java封装了读取或写⼊时每⼀“⾏”的数据；MainTest.java是⽰例程序的⼊⼝类，其中演⽰了读取和写⼊Excel数据的整个过程；2.2 读取数据⽰例程序需要从桌⾯读取 readExample.xlsx 内的数据，readExample.xlsx 的内容如下：读取Excel时主要调⽤ExcelReader.java类来读取和解析Excel的具体内容，这⾥以读取系统⽂件的形式演⽰读取过程：（兼容 xls 和 xlsx）2.2.1 主程序⼊⼝类代码：/*** Author: Dreamer-1* Date: 2019-03-01* Time: 10:13* Description: ⽰例程序⼊⼝类*/public class MainTest {public static void main(String[] args) {// 设定Excel⽂件所在路径String excelFileName = "/Users/Dreamer-1/Desktop/myBlog/java解析Excel/readExample.xlsx";// 读取Excel⽂件内容List<ExcelDataVO> readResult = ExcelReader.readExcel(excelFileName);// todo 进⾏业务操作}}读取和写⼊时封装每⼀“⾏”数据的ExcelDataVO.java代码如下：/*** Author: Dreamer-1* Date: 2019-03-01* Time: 11:33* Description: 读取Excel时，封装读取的每⼀⾏的数据*/public class ExcelDataVO {/*** 姓名*/private String name;/*** 年龄*/private Integer age;/*** 居住地*/private String location;/*** 职业*/private String job;public String getName() {return name;}public void setName(String name) { = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public String getLocation() {return location;}public void setLocation(String location) {this.location = location;}public String getJob() {return job;}public void setJob(String job) {this.job = job;}}2.2.2 Excel解析类的代码：/*** Author: Dreamer-1* Date: 2019-03-01* Time: 10:21* Description: 读取Excel内容*/public class ExcelReader {private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // ⽇志打印类private static final String XLS = "xls";private static final String XLSX = "xlsx";/*** 根据⽂件后缀名类型获取对应的⼯作簿对象* @param inputStream 读取⽂件的输⼊流* @param fileType ⽂件后缀名类型（xls或xlsx）* @return 包含⽂件数据的⼯作簿对象* @throws IOException*/public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException { Workbook workbook = null;if (fileType.equalsIgnoreCase(XLS)) {workbook = new HSSFWorkbook(inputStream);} else if (fileType.equalsIgnoreCase(XLSX)) {workbook = new XSSFWorkbook(inputStream);}return workbook;}/*** 读取Excel⽂件内容* @param fileName 要读取的Excel⽂件所在路径* @return 读取结果列表，读取失败时返回null*/public static List<ExcelDataVO> readExcel(String fileName) {Workbook workbook = null;FileInputStream inputStream = null;try {// 获取Excel后缀名String fileType = fileName.substring(stIndexOf(".") + 1, fileName.length());// 获取Excel⽂件File excelFile = new File(fileName);if (!excelFile.exists()) {logger.warning("指定的Excel⽂件不存在！");return null;}// 获取Excel⼯作簿inputStream = new FileInputStream(excelFile);workbook = getWorkbook(inputStream, fileType);// 读取excel中的数据List<ExcelDataVO> resultDataList = parseExcel(workbook);return resultDataList;} catch (Exception e) {logger.warning("解析Excel失败，⽂件名：" + fileName + " 错误信息：" + e.getMessage());return null;} finally {try {if (null != workbook) {workbook.close();}if (null != inputStream) {inputStream.close();}} catch (Exception e) {logger.warning("关闭数据流出错！错误信息：" + e.getMessage());return null;}}}/*** 解析Excel数据* @param workbook Excel⼯作簿对象* @return 解析结果*/private static List<ExcelDataVO> parseExcel(Workbook workbook) {List<ExcelDataVO> resultDataList = new ArrayList<>();// 解析sheetfor (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {Sheet sheet = workbook.getSheetAt(sheetNum);// 校验sheet是否合法if (sheet == null) {continue;}// 获取第⼀⾏数据int firstRowNum = sheet.getFirstRowNum();Row firstRow = sheet.getRow(firstRowNum);if (null == firstRow) {logger.warning("解析Excel失败，在第⼀⾏没有读取到任何数据！");}// 解析每⼀⾏的数据，构造数据对象int rowStart = firstRowNum + 1;int rowEnd = sheet.getPhysicalNumberOfRows();for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {Row row = sheet.getRow(rowNum);if (null == row) {continue;}ExcelDataVO resultData = convertRowToData(row);if (null == resultData) {logger.warning("第 " + row.getRowNum() + "⾏数据不合法，已忽略！");continue;}resultDataList.add(resultData);}}return resultDataList;}/*** 将单元格内容转换为字符串* @param cell* @return*/private static String convertCellValueToString(Cell cell) {if(cell==null){return null;}String returnValue = null;switch (cell.getCellType()) {case NUMERIC: //数字Double doubleValue = cell.getNumericCellValue();// 格式化科学计数法，取⼀位整数DecimalFormat df = new DecimalFormat("0");returnValue = df.format(doubleValue);break;case STRING: //字符串returnValue = cell.getStringCellValue();break;case BOOLEAN: //布尔Boolean booleanValue = cell.getBooleanCellValue();returnValue = booleanValue.toString();break;case BLANK: // 空值break;case FORMULA: // 公式returnValue = cell.getCellFormula();break;case ERROR: // 故障break;default:break;}return returnValue;}/*** 提取每⼀⾏中需要的数据，构造成为⼀个结果数据对象** 当该⾏中有单元格的数据为空或不合法时，忽略该⾏的数据** @param row ⾏数据* @return 解析后的⾏数据对象，⾏数据错误时返回null*/private static ExcelDataVO convertRowToData(Row row) {ExcelDataVO resultData = new ExcelDataVO();Cell cell;int cellNum = 0;// 获取姓名cell = row.getCell(cellNum++);String name = convertCellValueToString(cell);resultData.setName(name);// 获取年龄cell = row.getCell(cellNum++);String ageStr = convertCellValueToString(cell);if (null == ageStr || "".equals(ageStr)) {// 年龄为空resultData.setAge(null);} else {resultData.setAge(Integer.parseInt(ageStr));}// 获取居住地cell = row.getCell(cellNum++);String location = convertCellValueToString(cell);resultData.setLocation(location);// 获取职业cell = row.getCell(cellNum++);String job = convertCellValueToString(cell);resultData.setJob(job);return resultData;}}2.2.3 应⽤场景补充⼀般我们会有这样的应⽤场景，即：在前台页⾯的⽂件上传⼊⼝上传本地的Excel⽂件到后台，后台收到Excel⽂件后进⾏解析并做对应的业务操作；这⾥假设前台已经有了上传⽂件的⼊⼝，再简单贴⼀下后台的解析代码；后台接收前台数据的Controller层代码⽰例：@PostMapping("/uploadExcel")public ResponseEntity<?> uploadImage(MultipartFile file) {// 检查前台数据合法性if (null == file || file.isEmpty()) {logger.warning("上传的Excel商品数据⽂件为空！上传时间：" + new Date());return new ResponseEntity<>(HttpStatus.BAD_REQUEST);}try {// 解析ExcelList<ExcelDataVO> parsedResult = ExcelReader.readExcel(file);// todo 进⾏业务操作return new ResponseEntity<>(HttpStatus.OK);} catch (Exception e) {logger.warning("上传的Excel商品数据⽂件为空！上传时间：" + new Date());return new ResponseEntity<>(HttpStatus.BAD_REQUEST);}}ExcelReader.java中的readExcel()⽅法需要做⼀定的修改，代码如下：/*** 读取Excel⽂件内容* @param file 上传的Excel⽂件* @return 读取结果列表，读取失败时返回null*/public static List<ExcelDataVO> readExcel(MultipartFile file) {Workbook workbook = null;try {// 获取Excel后缀名String fileName = file.getOriginalFilename();if (fileName == null || fileName.isEmpty() || stIndexOf(".") < 0) {logger.warning("解析Excel失败，因为获取到的Excel⽂件名⾮法！");return null;}String fileType = fileName.substring(stIndexOf(".") + 1, fileName.length());// 获取Excel⼯作簿workbook = getWorkbook(file.getInputStream(), fileType);// 读取excel中的数据List<ExcelDataVO> resultDataList = parseExcel(workbook);return resultDataList;} catch (Exception e) {logger.warning("解析Excel失败，⽂件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage());return null;} finally {try {if (null != workbook) {workbook.close();}} catch (Exception e) {logger.warning("关闭数据流出错！错误信息：" + e.getMessage());return null;}}}2.3 写⼊数据写⼊数据时主要调⽤ExcelWriter.java来创建Excel⼯作簿对象并写⼊数据，这⾥以写⼊系统⽂件数据为例演⽰写⼊的过程：2.3.1 主程序⼊⼝类代码/*** Author: Dreamer-1* Date: 2019-03-01* Time: 10:13* Description: ⽰例程序⼊⼝类*/public class MainTest {private static Logger logger = Logger.getLogger(MainTest.class.getName());public static void main(String[] args) {// 创建需要写⼊的数据列表List<ExcelDataVO> dataVOList = new ArrayList<>(2);ExcelDataVO dataVO = new ExcelDataVO();dataVO.setName("⼩明");dataVO.setAge(18);dataVO.setLocation("⼴州");dataVO.setJob("⼤学⽣");ExcelDataVO dataVO2 = new ExcelDataVO();dataVO2.setName("⼩花");dataVO2.setAge(19);dataVO2.setLocation("深圳");dataVO2.setJob("⼤学⽣");dataVOList.add(dataVO);dataVOList.add(dataVO2);// 写⼊数据到⼯作簿对象内Workbook workbook = ExcelWriter.exportData(dataVOList);// 以⽂件的形式输出⼯作簿对象FileOutputStream fileOut = null;try {String exportFilePath = "/Users/Dreamer-1/Desktop/myBlog/java解析Excel/writeExample.xlsx";File exportFile = new File(exportFilePath);if (!exportFile.exists()) {exportFile.createNewFile();}fileOut = new FileOutputStream(exportFilePath);workbook.write(fileOut);fileOut.flush();} catch (Exception e) {logger.warning("输出Excel时发⽣错误，错误原因：" + e.getMessage());} finally {try {if (null != fileOut) {fileOut.close();}if (null != workbook) {workbook.close();}} catch (IOException e) {logger.warning("关闭输出流时发⽣错误，错误原因：" + e.getMessage());}}}}2.3.2 写⼊Excel类的代码ExcelWriter.java类中，你可以根据实际需要替换 CELL_HEADS 列头的信息，然后重写convertDataToRow⽅法，转换你⾃⼰的⾏数据；/*** Author: Dreamer-1* Date: 2019-03-01* Time: 11:09* Description: ⽣成Excel并写⼊数据*/public class ExcelWriter {private static List<String> CELL_HEADS; //列头static{// 类装载时就载⼊指定好的列头信息，如有需要，可以考虑做成动态⽣成的列头CELL_HEADS = new ArrayList<>();CELL_HEADS.add("姓名");CELL_HEADS.add("年龄");CELL_HEADS.add("居住城市");CELL_HEADS.add("职业");}/*** ⽣成Excel并写⼊数据信息* @param dataList 数据列表* @return 写⼊数据后的⼯作簿对象*/public static Workbook exportData(List<ExcelDataVO> dataList){// ⽣成xlsx的ExcelWorkbook workbook = new SXSSFWorkbook();// 如需⽣成xls的Excel，请使⽤下⾯的⼯作簿对象，注意后续输出时⽂件后缀名也需更改为xls//Workbook workbook = new HSSFWorkbook();// ⽣成Sheet表，写⼊第⼀⾏的列头Sheet sheet = buildDataSheet(workbook);//构建每⾏的数据内容int rowNum = 1;for (Iterator<ExcelDataVO> it = dataList.iterator(); it.hasNext(); ) {ExcelDataVO data = it.next();if (data == null) {continue;}//输出⾏数据Row row = sheet.createRow(rowNum++);convertDataToRow(data, row);}return workbook;}/*** ⽣成sheet表，并写⼊第⼀⾏数据（列头）* @param workbook ⼯作簿对象* @return 已经写⼊列头的Sheet*/private static Sheet buildDataSheet(Workbook workbook) {Sheet sheet = workbook.createSheet();// 设置列头宽度for (int i=0; i<CELL_HEADS.size(); i++) {sheet.setColumnWidth(i, 4000);}// 设置默认⾏⾼sheet.setDefaultRowHeight((short) 400);// 构建头单元格样式CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());// 写⼊第⼀⾏各列的数据Row head = sheet.createRow(0);for (int i = 0; i < CELL_HEADS.size(); i++) {Cell cell = head.createCell(i);cell.setCellValue(CELL_HEADS.get(i));cell.setCellStyle(cellStyle);}return sheet;}/*** 设置第⼀⾏列头的样式* @param workbook ⼯作簿对象* @return 单元格样式对象*/private static CellStyle buildHeadCellStyle(Workbook workbook) {CellStyle style = workbook.createCellStyle();//对齐⽅式设置style.setAlignment(HorizontalAlignment.CENTER);//边框颜⾊和宽度设置style.setBorderBottom(BorderStyle.THIN);style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框style.setBorderLeft(BorderStyle.THIN);style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框style.setBorderRight(BorderStyle.THIN);style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框style.setBorderTop(BorderStyle.THIN);style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框//设置背景颜⾊style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//粗体字设置Font font = workbook.createFont();font.setBold(true);style.setFont(font);return style;}/*** 将数据转换成⾏* @param data 源数据* @param row ⾏对象* @return*/private static void convertDataToRow(ExcelDataVO data, Row row){int cellNum = 0;Cell cell;// 姓名cell = row.createCell(cellNum++);cell.setCellValue(null == data.getName() ? "" : data.getName());// 年龄cell = row.createCell(cellNum++);if (null != data.getAge()) {cell.setCellValue(data.getAge());} else {cell.setCellValue("");}// 所在城市cell = row.createCell(cellNum++);cell.setCellValue(null == data.getLocation() ? "" : data.getLocation());// 职业cell = row.createCell(cellNum++);cell.setCellValue(null == data.getJob() ? "" : data.getJob());}}⽰例程序运⾏后将会在指定的系统路径下⽣成 writeExample.xlsx⽂件，其内容如下：2.3.3 应⽤场景补充⼀般写⼊Excel时会有这样的场景：前台页⾯上有⼀个导出按钮，点击后将后台某张表⾥的数据以Excel的形式导出，导出的Excel⽂件通过浏览器下载到⽤户系统中；这⾥默认前台页⾯已经有相应的按钮功能，给出对应的Controller层代码供参考：@GetMapping("/exportExcel")public void exportExcel(HttpServletRequest request, HttpServletResponse response) {Workbook workbook = null;OutputStream out = null;try {// todo 根据业务需求获取需要写⼊Excel的数据列表 dataList// ⽣成Excel⼯作簿对象并写⼊数据workbook = ExcelWriter.exportData(dataList);// 写⼊Excel⽂件到前端if(null != workbook){String excelName = "⽰例Excel导出";String fileName = excelName + DateUtil.format(new Date(), DateUtil.SHORT_DATE) + ".xlsx";fileName = new String(fileName.getBytes("UTF-8"),"iso8859-1");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);response.setContentType("application/x-download");response.setCharacterEncoding("UTF-8");response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");response.flushBuffer();out = response.getOutputStream();workbook.write(out);out.flush();}} catch (Exception e) {logger.WARNING("写⼊Excel过程出错！错误原因：" + e.getMessage());} finally {try {if (null != workbook) {workbook.close();}if (null != out) {out.close();}} catch (IOException e) {logger.WARNING("关闭workbook或outputStream出错！");}}}// 前台页⾯发送请求到后台Controller时的JS代码可参考：var url = "/exportExcel";window.location=url;原⽂链接：https:///Dreamer-1/p/10469430.html。

java使⽤poi包将数据写⼊Excel表格1、Excel相关操作代码1import java.io.File;2import java.io.FileInputStream;3import java.io.FileNotFoundException;4import java.io.FileOutputStream;5import java.io.IOException;6import ng.reflect.Field;7import ng.reflect.Method;8import java.util.ArrayList;9import java.util.List;1011import ermodel.HSSFRow;12import ermodel.HSSFSheet;13import ermodel.HSSFWorkbook;14import ermodel.Cell;15import ermodel.Row;16import ponent;1718/**19 * @Description:20 * @author 21 * @date 创建时间：2016年12⽉8⽇下午2:38:4722 * @version 1.023*/24 @Component25public class ExcelManage {26private HSSFWorkbook workbook = null;2728/**29 * 判断⽂件是否存在30 * @param filePath ⽂件路径31 * @return32*/33public boolean fileExist(String filePath){34boolean flag = false;35 File file = new File(filePath);36 flag = file.exists();37return flag;38 }3940/**41 * 判断⽂件的sheet是否存在42 * @param filePath ⽂件路径43 * @param sheetName 表格索引名44 * @return45*/46public boolean sheetExist(String filePath,String sheetName){47boolean flag = false;48 File file = new File(filePath);49if(file.exists()){ //⽂件存在50//创建workbook51try {52 workbook = new HSSFWorkbook(new FileInputStream(file));53//添加Worksheet（不添加sheet时⽣成的xls⽂件打开时会报错)54 HSSFSheet sheet = workbook.getSheet(sheetName);55if(sheet!=null)56 flag = true;57 } catch (Exception e) {58 e.printStackTrace();59 }60 }else{ //⽂件不存在61 flag = false;62 }63return flag;64 }65/**66 * 创建新Sheet并写⼊第⼀⾏数据67 * @param filePath excel的路径68 * @param sheetName 要创建的表格索引69 * @param titleRow excel的第⼀⾏即表格头70 * @throws IOException71 * @throws FileNotFoundException72*/73public void createSheet(String filePath,String sheetName,String titleRow[]) throws FileNotFoundException, IOException{74 FileOutputStream out = null;75 File excel = new File(filePath); // 读取⽂件76 FileInputStream in = new FileInputStream(excel); // 转换为流77 workbook = new HSSFWorkbook(in); // 加载excel的⼯作⽬录7879 workbook.createSheet(sheetName); // 添加⼀个新的sheet80//添加表头81 Row row = workbook.getSheet(sheetName).createRow(0); //创建第⼀⾏82try {83for(int i = 0;i < titleRow.length;i++){84 Cell cell = row.createCell(i);85 cell.setCellValue(titleRow[i]);86 }87 out = new FileOutputStream(filePath);88 workbook.write(out);89 }catch (Exception e) {90 e.printStackTrace();91 }finally {92try {93 out.close();94 } catch (IOException e) {95 e.printStackTrace();96 }97 }98 }99 /**100 * 创建新excel.101 * @param filePath excel的路径102 * @param sheetName 要创建的表格索引103 * @param titleRow excel的第⼀⾏即表格头104*/105public void createExcel(String filePath,String sheetName,String titleRow[]){106//创建workbook107 workbook = new HSSFWorkbook();108//添加Worksheet（不添加sheet时⽣成的xls⽂件打开时会报错)109 workbook.createSheet(sheetName);110//新建⽂件111 FileOutputStream out = null;112try {113//添加表头114 Row row = workbook.getSheet(sheetName).createRow(0); //创建第⼀⾏115for(int i = 0;i < titleRow.length;i++){116 Cell cell = row.createCell(i);117 cell.setCellValue(titleRow[i]);118 }119 out = new FileOutputStream(filePath);120 workbook.write(out);121 } catch (Exception e) {122 e.printStackTrace();123 } finally {124try {125 out.close();126 } catch (IOException e) {127 e.printStackTrace();128 }129 }130 }131/**132 * 删除⽂件.133 * @param filePath ⽂件路径134*/135public boolean deleteExcel(String filePath){136boolean flag = false;137 File file = new File(filePath);138// 判断⽬录或⽂件是否存在139if (!file.exists()) {140return flag;141 } else {142// 判断是否为⽂件143if (file.isFile()) { // 为⽂件时调⽤删除⽂件⽅法144 file.delete();145 flag = true;146 }147 }148return flag;149 }150/**151 * 往excel中写⼊.152 * @param filePath ⽂件路径153 * @param sheetName 表格索引154 * @param object155*/156public void writeToExcel(String filePath,String sheetName, Object object,String titleRow[]){ 157//创建workbook158 File file = new File(filePath);159try {160 workbook = new HSSFWorkbook(new FileInputStream(file));161 } catch (FileNotFoundException e) {162 e.printStackTrace();163 } catch (IOException e) {164 e.printStackTrace();165 }166 FileOutputStream out = null;167 HSSFSheet sheet = workbook.getSheet(sheetName);168// 获取表格的总⾏数169int rowCount = sheet.getLastRowNum() + 1; // 需要加⼀170try {171 Row row = sheet.createRow(rowCount); //最新要添加的⼀⾏172//通过反射获得object的字段,对应表头插⼊173// 获取该对象的class对象174 Class<? extends Object> class_ = object.getClass();175176for(int i = 0;i < titleRow.length;i++){177 String title = titleRow[i];178 String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其⾸字母⼤写; 179 String methodName = "get"+UTitle;180 Method method = class_.getDeclaredMethod(methodName); // 设置要执⾏的⽅法181 String data = method.invoke(object).toString(); // 执⾏该get⽅法,即要插⼊的数据182 Cell cell = row.createCell(i);183 cell.setCellValue(data);184 }185 out = new FileOutputStream(filePath);186 workbook.write(out);187 } catch (Exception e) {188 e.printStackTrace();189 } finally {190try {191 out.close();192 } catch (IOException e) {193 e.printStackTrace();194 }195 }196 }197 }2、main⽅法调⽤⽅式1public static void main(String[] args) {2 String filePath = "result/数据汇总.xls";3 String sheetName = "测试";4//Excel⽂件易车sheet页的第⼀⾏5 String title[] = {"⽇期", "城市","新发布车源数"};6//Excel⽂件易车每⼀列对应的数据7 String titleDate[] = {"date", "city","newPublish"};89 ExcelManage em = new ExcelManage();10//判断该名称的⽂件是否存在11boolean fileFlag = em.fileExist(filePath);12if(!fileFlag){13 em.createExcel(filePath,sheetName,title);14 }15//判断该名称的Sheet是否存在16boolean sheetFlag = em.sheetExist(filePath,sheetName);17//如果该名称的Sheet不存在，则新建⼀个新的Sheet18if(!sheetFlag){19try {20 em.createSheet(filePath,sheetName,title);21 } catch (FileNotFoundException e) {22 e.printStackTrace();23 } catch (IOException e) {24 e.printStackTrace();25 }26 }28 YiCheData user = new YiCheData();29 user.setDate("206-12-21");30 user.setCity("北京");31 user.setNewPublish("5");32//写⼊到excel33 em.writeToExcel(filePath,sheetName,user,titleDate);34 }3、⽤于测试的bean类1public class YiCheData {2private String date;3private String city;4private String newPublish;56public String getDate() {7return date;8 }9public void setDate(String date) {10this.date = date;11 }12public String getCity() {13 return city;14 }15public void setCity(String city) {16 this.city = city;17 }18public String getNewPublish() {19 eturn newPublish;20 }21public void setNewPublish(String newPublish) { 22 this.newPublish = newPublish;23 }24 }。

java的poi技术读取Excel数据这篇blog主要是讲述java中poi读取excel，⽽excel的版本包括：2003-2007和2010两个版本，即excel的后缀名为：xls和xlsx。

读取excel和MySQL相关：你也可以在：了解到写⼊Excel的⽅法信息使⽤JXL技术：下⾯是本⽂的项⽬结构：项⽬中所需要的jar⽂件：所⽤的Excel数据（2003-2007,2010都是⼀样的数据）运⾏效果：=================================================源码部分：================================================= /Excel2010/src/com/b510/common/Common.java1 /**2 *3 */4 package mon;56 /**7 * @author Hongten8 * @created 2014-5-219 */10 public class Common {1112 public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";13 public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";1415 public static final String EMPTY = "";16 public static final String POINT = ".";17 public static final String LIB_PATH = "lib";18 public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;19 public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;20 public static final String NOT_EXCEL_FILE = " : Not the Excel file!";21 public static final String PROCESSING = "Processing...";2223 }/Excel2010/src/com/b510/excel/ReadExcel.java1 /**2 *3 */4 package com.b510.excel;56 import java.io.FileInputStream;7 import java.io.IOException;8 import java.io.InputStream;9 import java.util.ArrayList;10 import java.util.List;1112 import ermodel.HSSFCell;13 import ermodel.HSSFRow;14 import ermodel.HSSFSheet;15 import ermodel.HSSFWorkbook;16 import ermodel.XSSFCell;17 import ermodel.XSSFRow;18 import ermodel.XSSFSheet;19 import ermodel.XSSFWorkbook;2021 import mon;22 import com.b510.excel.util.Util;23 import com.b510.excel.vo.Student;2425 /**26 * @author Hongten27 * @created 2014-5-2028 */29 public class ReadExcel {3031 /**32 * read the Excel file33 * @param path the path of the Excel file34 * @return35 * @throws IOException36 */37 public List<Student> readExcel(String path) throws IOException {38 if (path == null || Common.EMPTY.equals(path)) {39 return null;40 } else {41 String postfix = Util.getPostfix(path);42 if (!Common.EMPTY.equals(postfix)) {43 if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {44 return readXls(path);45 } else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {46 return readXlsx(path);47 }48 } else {49 System.out.println(path + Common.NOT_EXCEL_FILE);50 }51 }52 return null;53 }5455 /**56 * Read the Excel 201057 * @param path the path of the excel file58 * @return59 * @throws IOException60 */61 public List<Student> readXlsx(String path) throws IOException {62 System.out.println(Common.PROCESSING + path);63 InputStream is = new FileInputStream(path);64 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);65 Student student = null;66 List<Student> list = new ArrayList<Student>();67 // Read the Sheet68 for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {69 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);70 if (xssfSheet == null) {71 continue;72 }73 // Read the Row74 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {75 XSSFRow xssfRow = xssfSheet.getRow(rowNum);76 if (xssfRow != null) {77 student = new Student();78 XSSFCell no = xssfRow.getCell(0);79 XSSFCell name = xssfRow.getCell(1);80 XSSFCell age = xssfRow.getCell(2);81 XSSFCell score = xssfRow.getCell(3);82 student.setNo(getValue(no));83 student.setName(getValue(name));84 student.setAge(getValue(age));85 student.setScore(Float.valueOf(getValue(score)));86 list.add(student);87 }88 }89 }90 return list;91 }9293 /**94 * Read the Excel 2003-200795 * @param path the path of the Excel96 * @return97 * @throws IOException98 */99 public List<Student> readXls(String path) throws IOException {100 System.out.println(Common.PROCESSING + path);101 InputStream is = new FileInputStream(path);102 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);103 Student student = null;104 List<Student> list = new ArrayList<Student>();105 // Read the Sheet106 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 107 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);108 if (hssfSheet == null) {109 continue;110 }111 // Read the Row112 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {113 HSSFRow hssfRow = hssfSheet.getRow(rowNum);114 if (hssfRow != null) {115 student = new Student();116 HSSFCell no = hssfRow.getCell(0);117 HSSFCell name = hssfRow.getCell(1);118 HSSFCell age = hssfRow.getCell(2);119 HSSFCell score = hssfRow.getCell(3);120 student.setNo(getValue(no));121 student.setName(getValue(name));122 student.setAge(getValue(age));123 student.setScore(Float.valueOf(getValue(score)));124 list.add(student);125 }126 }127 }128 return list;129 }130131 @SuppressWarnings("static-access")132 private String getValue(XSSFCell xssfRow) {133 if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {134 return String.valueOf(xssfRow.getBooleanCellValue());135 } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {136 return String.valueOf(xssfRow.getNumericCellValue());137 } else {138 return String.valueOf(xssfRow.getStringCellValue());139 }140 }141142 @SuppressWarnings("static-access")143 private String getValue(HSSFCell hssfCell) {144 if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {145 return String.valueOf(hssfCell.getBooleanCellValue());146 } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {147 return String.valueOf(hssfCell.getNumericCellValue());148 } else {149 return String.valueOf(hssfCell.getStringCellValue());150 }151 }152 }/Excel2010/src/com/b510/excel/client/Client.java1 /**2 *3 */4 package com.b510.excel.client;56 import java.io.IOException;7 import java.util.List;89 import mon;10 import com.b510.excel.ReadExcel;11 import com.b510.excel.vo.Student;1213 /**14 * @author Hongten15 * @created 2014-5-2116 */17 public class Client {1819 public static void main(String[] args) throws IOException {20 String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH;21 String excel2010 = Common.STUDENT_INFO_XLSX_PATH;22 // read the 2003-2007 excel23 List<Student> list = new ReadExcel().readExcel(excel2003_2007);24 if (list != null) {25 for (Student student : list) {26 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());27 }28 }29 System.out.println("======================================");30 // read the 2010 excel31 List<Student> list1 = new ReadExcel().readExcel(excel2010);32 if (list1 != null) {33 for (Student student : list1) {34 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());35 }36 }37 }38 }/Excel2010/src/com/b510/excel/util/Util.java1 /**2 *3 */4 package com.b510.excel.util;56 import mon;78 /**9 * @author Hongten10 * @created 2014-5-2111 */12 public class Util {1314 /**15 * get postfix of the path16 * @param path17 * @return18 */19 public static String getPostfix(String path) {20 if (path == null || Common.EMPTY.equals(path.trim())) {21 return Common.EMPTY;22 }23 if (path.contains(Common.POINT)) {24 return path.substring(stIndexOf(Common.POINT) + 1, path.length());25 }26 return Common.EMPTY;27 }28 }/Excel2010/src/com/b510/excel/vo/Student.java1 /**2 *3 */4 package com.b510.excel.vo;56 /**7 * Student8 *9 * @author Hongten10 * @created 2014-5-1811 */12 public class Student {13 /**14 * id15 */16 private Integer id;17 /**18 * 学号19 */20 private String no;21 /**22 * 姓名23 */24 private String name;25 /**26 * 学院27 */28 private String age;29 /**30 * 成绩31 */32 private float score;3334 public Integer getId() {35 return id;36 }3738 public void setId(Integer id) {39 this.id = id;40 }4142 public String getNo() {43 return no;44 }4546 public void setNo(String no) {47 this.no = no;48 }4950 public String getName() {51 return name;52 }5354 public void setName(String name) {55 = name;56 }5758 public String getAge() {59 return age;60 }6162 public void setAge(String age) {63 this.age = age;64 }6566 public float getScore() {67 return score;68 }6970 public void setScore(float score) {71 this.score = score;72 }7374 }。

java读写excel⽂件实现POI解析Excel的⽅法在⽇常⼯作中，我们常常会进⾏⽂件读写操作，除去我们最常⽤的纯⽂本⽂件读写，更多时候我们需要对Excel中的数据进⾏读取操作，本⽂将介绍Excel读写的常⽤⽅法，希望对⼤家学习Java读写Excel 会有帮助。

package com.zhx.base.utils;import ermodel.HSSFWorkbook;import ermodel.*;import ermodel.XSSFWorkbook;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;/*** POI解析Excel*/public class ExcelReaderUtil {/*** 根据fileType不同读取excel⽂件** @param path* @param path* @throws IOException*/public static List<List<String>> readExcel(String path) {String fileType = path.substring(stIndexOf(".") + 1);// return a list contains many listList<List<String>> lists = new ArrayList<List<String>>();//读取excel⽂件InputStream is = null;try {is = new FileInputStream(path);//获取⼯作薄Workbook wb = null;if (fileType.equals("xls")) {wb = new HSSFWorkbook(is);} else if (fileType.equals("xlsx")) {wb = new XSSFWorkbook(is);} else {return null;}//读取第⼀个⼯作页sheetSheet sheet = wb.getSheetAt(0);//第⼀⾏为标题for (Row row : sheet) {ArrayList<String> list = new ArrayList<String>();for (Cell cell : row) {//根据不同类型转化成字符串cell.setCellType(Cell.CELL_TYPE_STRING);list.add(cell.getStringCellValue());}lists.add(list);}} catch (IOException e) {e.printStackTrace();} finally {try {if (is != null) is.close();} catch (IOException e) {e.printStackTrace();}}return lists;}/*** 创建Excel.xls* @param lists 需要写⼊xls的数据* @param titles 列标题* @param name ⽂件名* @return* @throws IOException*/public static Workbook creatExcel(List<List<String>> lists, String[] titles, String name) throws IOException {System.out.println(lists);//创建新的⼯作薄Workbook wb = new HSSFWorkbook();// 创建第⼀个sheet（页），并命名Sheet sheet = wb.createSheet(name);// ⼿动设置列宽。

POI操作Excel文档－基础篇（转）关键字: poi一．POI简介Jakarta POI 是apache的子项目，目标是处理ole2对象。

它提供了一组操纵Windows文档的Ja目前比较成熟的是HSSF接口，处理MS Excel（97-2002）对象。

它不象我们仅仅是用csv生成的没式的可以由Excel转换的东西，而是真正的Excel对象，你可以控制一些属性如sheet,cell等等二．HSSF概况HSSF 是Horrible SpreadSheet Format的缩写，也即“讨厌的电子表格格式”。

也许HSSF的名字滑稽，就本质而言它是一个非常严肃、正规的API。

通过HSSF，你可以用纯Java代码来读取、写入改Excel文件。

HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模前者很好理解，后者比较抽象，但操作效率要高得多。

三．开始编码1 ．准备工作要求:JDK 1.4+POI开发包可以到 /dyn/closer.cgi/jakarta/poi/ 最新的POI工具包2 ． EXCEL 结构HSSFWorkbook excell 文档对象介绍HSSFSheet excell的表单HSSFRow excell的行HSSFCell excell的格子单元HSSFFont excell字体HSSFName 名称HSSFDataFormat 日期格式在poi1.7中才有以下2项：HSSFHeader sheet头HSSFFooter sheet尾和这个样式HSSFCellStyle cell样式辅助操作包括HSSFDateUtil 日期HSSFPrintSetup 打印HSSFErrorConstants 错误信息表3 ．具体用法实例（采用 usermodel ）如何读Excel读取Excel文件时，首先生成一个POIFSFileSystem对象，由POIFSFileSystem对象构造一个HSSFWorkbook，该HSSFWorkbook对象就代表了Excel文档。

Java使⽤POI操作Excel Java中常见的⽤来操作 Excel 的⽅式有2种：JXL和POI。

JXL只能对 Excel进⾏操作，且只⽀持到 Excel 95-2000的版本。

⽽POI是Apache 的开源项⽬，由Java编写的跨平台 Java API，可操作 Microsoft Office。

借助POI，可以⽅便的⽣成数据报表，数据批量上传，数据备份等⼯作。

⼀.简单使⽤1.创建Maven⼯程导⼊POI坐标<!-- poi 相关 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.0.1</version></dependency>2.使⽤APIHSSF : 读写 Microsoft Excel XLS 格式⽂档XSSF : 读写 Microsoft Excel OOXML XLSX 格式⽂档SXSSF : 读写 Microsoft Excel OOXML XLSX 格式⽂档HWPF : 读写 Microsoft Word DOC 格式⽂档HSLF : 读写 Microsoft PowerPoint 格式⽂档HDGF : 读 Microsoft Visio 格式⽂档HPBF : 读 Microsoft Publisher 格式⽂档HSMF : 读 Microsoft Outlook 格式⽂档3.操作⽰例//1.创建Excel对象XSSFWorkbook wb = new XSSFWorkbook();//2.创建Sheet对象Sheet sheet = wb.createSheet();//3.创建⾏对象(索引从0开始)Row nRow = sheet.createRow(0);//4.设置⾏⾼和列宽nRow.setHeightInPoints(26.25f);sheet.setColumnWidth(1,26*256); //(列的索引,列宽*256(理解为固定写法))//5.创建单元格对象(索引从0开始)Cell nCell = nRow.createCell(0);//6.设置单元格内容nCell.setCellValue("dinTalk");//==============================//7.创建单元格样式对象CellStyle style = wb.createCellStyle();//8.创建字体对象Font font = wb.createFont();//9.设置字体和其⼤⼩及效果font.setFontName("⿊体");font.setFontHeightInPoints((short)12);font.setBold(true); //加粗//10.设置样式style.setFont(font);style.setAlignment(HorizontalAlignment.CENTER); //横向居中style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中style.setBorderTop(BorderStyle.THIN); //上细线style.setBorderBottom(BorderStyle.THIN); //下细线style.setBorderLeft(BorderStyle.THIN); //左细线style.setBorderRight(BorderStyle.THIN); //右细线//11.为单元格应⽤样式nCell.setCellStyle(style);⼆.使⽤模板1.模板打印(下载)我们通过⾃定义⽣成 Excel 报表⽂件很是⿇烦，特别是字体、样式⽐较复杂的时候。

java poi excel 读写摘要：1.Java POI 简介2.Java POI Excel 读写功能3.使用Java POI 读取Excel 文件4.使用Java POI 写入Excel 文件5.总结与展望正文：Java POI 是一个开源的Java 库，它允许Java 开发人员操作Microsoft Office 文档，如Word、Excel、PowerPoint 等。

其中，Java POI Excel 提供了读取和写入Excel 文件的功能。

1.Java POI 简介Java POI 是一个基于Apache License 2.0 协议的开源项目，它为Java 开发人员提供了一种操作Microsoft Office 文档的方法。

Java POI 支持读取和写入多种Office 文档格式，包括Excel、Word、PowerPoint 等。

通过使用Java POI，开发人员可以在不安装Microsoft Office 的情况下，对Office 文档进行操作。

2.Java POI Excel 读写功能Java POI Excel 提供了丰富的API，允许Java 开发人员对Excel 文件进行读取和写入操作。

Excel 文件主要包括两种类型：XLS 和XLSX。

Java POI 支持这两种文件格式，同时还支持读取和写入XML 格式的Excel 文件。

3.使用Java POI 读取Excel 文件使用Java POI 读取Excel 文件主要包括以下几个步骤：(1) 导入相关的Java POI 库：```javaimport ermodel.*;import ermodel.XSSFWorkbook;import java.io.FileInputStream;import java.io.IOException;```(2) 创建一个Workbook 对象，表示Excel 文件：```javaWorkbook workbook = new XSSFWorkbook(new FileInputStream("example.xlsx"));```(3) 获取工作表：```javaSheet sheet = workbook.getSheetAt(0);```(4) 遍历工作表中的行和列，获取单元格数据：```javafor (Row row : sheet) {for (Cell cell : row) {String cellValue = getCellValue(cell);System.out.print(cellValue + "t");}System.out.println();}```4.使用Java POI 写入Excel 文件使用Java POI 写入Excel 文件主要包括以下几个步骤：(1) 创建一个新的Excel 文件：```javaWorkbook workbook = new XSSFWorkbook();```(2) 添加一个新的工作表：```javaSheet sheet = workbook.createSheet("Sheet1");```(3) 创建行：```javaRow row = sheet.createRow(0);```(4) 创建单元格，并设置单元格数据：```javaCell cell = row.createCell(0);cell.setCellValue("Hello, World!");```(5) 将数据写入Excel 文件：```javatry (FileOutputStream fileOut = newFileOutputStream("output.xlsx")) {workbook.write(fileOut);} catch (IOException e) {e.printStackTrace();}```5.总结与展望Java POI 是一个功能强大的Java 库，它允许Java 开发人员轻松地操作Microsoft Office 文档，如Excel、Word、PowerPoint 等。

JAVA使⽤POI（XSSFWORKBOOK）读取EXCEL⽂件过程解析经过⼀番搜索发现，java操纵excel⽂件常⽤的有jxl和poi两种⽅式，孰好孰坏看⾃⼰需求⽽定。

其中最主要的区别在于jxl不⽀持.xlsx，⽽poi⽀持.xlsx这⾥介绍的使⽤poi⽅式(XSSFWorkbook)，实际上poi提供了HSSFWorkbook和XSSFWorkbook两个实现类。

区别在于HSSFWorkbook是针对.xls⽂件，XSSFWorkbook是针对.xslx⽂件。

⾸先明确⼀下基本概念： 先创建⼀个⼯作簿，⼀个⼯作簿可以有多个⼯作表，⼀个⼯作表可以有多个⾏，⼀个⾏可以有多个单元格 ⼯作簿 >>>>>>>>XSSFWorkbook ⼯作表 >>>>>>>>XSSFSheet ⾏ >>>>>>>>XSSFRow 单元格 >>>>>>>>XSSFCell下图为创建的student.xlsx的内容：读取student.xlsx⽂件代码：package com.zjk.testexcel;import ermodel.*;import java.io.FileInputStream;import java.io.IOException;/*** @Auther: zjk* @Date: 2019/8/30* @Description:*/public class TestExcel1 {public static void main(String[] args) {try {//创建⼯作簿XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\test-excel\\student.xlsx"));System.out.println("xssfWorkbook对象：" + xssfWorkbook);//读取第⼀个⼯作表(这⾥的下标与list⼀样的，从0开始取，之后的也是如此)XSSFSheet sheet = xssfWorkbook.getSheetAt(0);System.out.println("sheet对象：" + sheet);//获取第⼀⾏的数据XSSFRow row = sheet.getRow(0);System.out.println("row对象：" + row);//获取该⾏第⼀个单元格的数据XSSFCell cell0 = row.getCell(0);System.out.println("cello对象：" + cell0);} catch (IOException e) {e.printStackTrace();}}}控制台输出结果：可以发现具体到⾏对象时，就解析成xml⽂件了xssfWorkbook对象： Name: /xl/workbook.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xmlsheet对象： Name: /xl/worksheets/sheet1.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xmlrow对象： <xml-fragment r="1" spans="1:4" xmlns:r="/officeDocument/2006/relationships" xmlns:xdr="/drawingml/2006/spreadsheetDrawing" xmlns:x14="/office/spre <main:c r="A1" t="s"> <main:v>0</main:v> </main:c> <main:c r="B1" t="s"> <main:v>1</main:v> </main:c> <main:c r="C1" t="s"> <main:v>2</main:v> </main:c> <main:c r="D1" t="s"><main:v>3</main:v></main:c></xml-fragment>cello对象：姓名以上可以实现了读取某⾏某单元格的数据，那么接下来就该读取整个表的所有数据了：package com.zjk.testexcel;import ermodel.XSSFCell;import ermodel.XSSFRow;import ermodel.XSSFSheet;import ermodel.XSSFWorkbook;import java.io.FileInputStream;import java.io.IOException;/*** @Auther: zjk* @Date: 2019/8/30* @Description:*/public class TestExcel2 {public static void main(String[] args) {try {//创建⼯作簿XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\test-excel\\student.xlsx"));System.out.println("xssfWorkbook对象：" + xssfWorkbook);//读取第⼀个⼯作表XSSFSheet sheet = xssfWorkbook.getSheetAt(0);System.out.println("sheet对象：" + sheet); //获取最后⼀⾏的num，即总⾏数。

java 的POI 操作Excel 文件（1）微软在桌面系统上的成功，令我们不得不大量使用它的办公产品，如：Word ，Excel 。

时至今日，它的源代码仍然不公开已封锁了我们的进一步应用和开发。

然而在要求更高的服务器领域，微软本身的产品移植性不好，领域，微软本身的产品移植性不好，性能不佳。

在我们实际的开发中，表现层的解决方案虽然有多样，但是Ie 浏览器已成为最多人使用的浏览器，因为大家都用Windows 。

在企业办公系统中，常常有客户这样子要求：你要把我们的报表直接用Excel 打开。

或者是：我们已经习惯用Excel 打印。

这样子如果用.net 开发是没有问题的，开发是没有问题的，但是有但是有j2ee 这个比.net 更有前途的开放式的开发环境，更有前途的开放式的开发环境，难道我为了解决难道我为了解决打印的要求去另写客户端的控件？或者在服务器端使用本地代码？第一种方案的问题是关键数据的处理有时候不能在客户端做，第2种方案的问题是牺牲了代码的可移植性和稳定性。

如果让客户端只负责处理生成好的报表，那将是一种诱人的选择。

性。

如果让客户端只负责处理生成好的报表，那将是一种诱人的选择。

Apache 的Jakata 项目的POI 子项目，目标是处理ole2对象。

目前比较成熟的是HSSF 接口，处理MS MS ExcelExcel （97-2002）对象。

它不象我们仅仅是用csv 生成的没有格式的可以由Excel 转换的东西，转换的东西，而是真正的而是真正的Excel 对象，你可以控制一些属性如sheet,cell 等等。

这是一个年轻的项目，所以象HDF 这样直接支持W ord 对象的好东西仍然在设计中。

其它支持word 格式的纯java 方案还有itext ，不过也是仍在奋斗中。

但是HSSF 已经成熟到能够和足够我们使用了。

另外，无锡永中Office 的实现方案也是纯java 的解决方案，不过那也是完全商业的产品，并不是公开代码项目。

java通过POI技术操作Excel（2）----模板读取，录⼊数据 先来回顾下通常把java对Excel的操作分为以下功能：1、⽣成模板，导出模板；2、填充模板，录⼊数据；3；读取数据库数据，导出数据；在上⼀篇博⽂中，我简单记录了模板⽣成和导出，在这篇博⽂中，主要来记录--Excel⽂件导⼊，数据录⼊(仍然是以jsp+servlet为例) 既然要解决这个问题，那⾸先来分析下我们需要⾯对的有哪些需求需要实现： 1、Excel⽂件导⼊(这是最基础的，巧妇难为⽆⽶之炊，导⼊环节也是查了好久才完成的); 2、Excel⽂件中数据的格式判定，你要读取⽂件，如果⽂件中其实没有数据怎么办，程序会报错么; 3、Excel⽂件中数据的数值判定，“编号”啊，“⾝份证号”啊之类是不是需要有⼀定的规范才说明数值是正确的，否则录⼊毫⽆意义，数据库中可能还会报错; 4、Excel⽂件中数据判定完成后，判定全部正确的，全部录⼊数据库，录⼊成功显⽰录⼊成功，录⼊不成功则修改录⼊环节； 判定如果存在错误，将所有出现错误的列汇总，返回界⾯提⽰所有出错的列;⾸先，我们来完成导⼊功能，实现如下效果：相信这个上传效果，很多⼈都能实现，<input type="file">如是⽽已嘛，但是实现它后我们如何进⾏excel数据操作呢？通常我们想到的有如下两种⽅法：1、将excel⽂件传上去，然后对传上的⽂件进⾏操作，因为我们传到哪了我们知道，可以直接获取路径；2、我们可以直接获取想要上传的⽂件在电脑上的路径，然后我们通过路径直接对⽂件进⾏操作；这⾥我主要来介绍下我实现的第⼆种⽅法，（以ie浏览器为例，其它浏览器暂不讨论）1function upLoad(){2var myFile=document.getElementById("myFile");3 myFile.select();4var realPath=document.selection.createRange().text;5var len=realPath.length;6var path=realPath.substr(len-4);7if(path==".xls"){ document.getElementById("myForm").action="upLoad?path="+realPath;8 document.getElementById("myForm").submit();9 }else{10 alert("请输⼊excel格式的⽂件");11 }12 }通常情况下，在ie7之前我们可以通过document.getElementById('file_upl').value 直接获取⽂本的本地路径，但是在ie8之后，处于安全性考虑，上传时获取以上value 值则会以“C:\fakepath\”来代替了，这个时候我们就需要上⽂中出现的var myFile=document.getElementById("myFile"); myFile.select();varrealPath=document.selection.createRange().text;这三步来进⾏完成了。

javapoi读取excel操作⽰例（2个代码）项⽬中要求读取excel⽂件内容，并将其转化为xml格式。

常见读取excel⽂档⼀般使⽤POI和JExcelAPI这两个⼯具。

这⾥我们介绍使⽤POI实现读取excel⽂档。

复制代码代码如下:/** 使⽤POI读取EXCEL⽂件*/import java.io.File;import java.io.FileInputStream;import java.util.ArrayList;import ermodel.HSSFCell;import ermodel.HSSFRow;import ermodel.HSSFSheet;import ermodel.HSSFWorkbook;/**** @author Hanbin*/public class ReadExcel {/*** @param args the command line arguments*/public static void main(String[] args)throws Exception {read("d:\\demo.xls");}public static ArrayList read(String fileName){ArrayList list = new ArrayList();String sql = "";try{File f = new File(fileName);FileInputStream fis = new FileInputStream(f);HSSFWorkbook wbs = new HSSFWorkbook(fis);HSSFSheet childSheet = wbs.getSheetAt(0);System.out.println("⾏数:" + childSheet.getLastRowNum());for(int i = 4;i<childSheet.getLastRowNum();i++){HSSFRow row = childSheet.getRow(i);System.out.println("列数:" + row.getPhysicalNumberOfCells());if(null != row){for(int k=1;k<row.getPhysicalNumberOfCells();k++){HSSFCell cell;cell = row.getCell((short)k);// System.out.print(getStringCellValue(cell) + "\t");list.add(getStringCellValue(cell) + "\t");}}}}catch(Exception e){e.printStackTrace();}return list;}* @return String 单元格数据内容*/private static 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;}}再来⼀个例⼦复制代码代码如下:package edu.sjtu.erplab.poi;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;import ermodel.HSSFCell;import ermodel.HSSFDateUtil; import ermodel.HSSFRow;import ermodel.HSSFSheet; import ermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; /*** 操作Excel表格的功能类*/public class ExcelReader {private POIFSFileSystem fs;private HSSFWorkbook wb;private HSSFSheet sheet;* 读取Excel表格表头的内容* @param InputStream* @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 = sheet.getRow(0);// 标题总列数int colNum = row.getPhysicalNumberOfCells();System.out.println("colNum:" + colNum);String[] title = new String[colNum];for (int i = 0; i < colNum; i++) {//title[i] = getStringCellValue(row.getCell((short) i));title[i] = getCellFormatValue(row.getCell((short) i));}return title;}/*** 读取Excel数据内容* @param InputStream* @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();row = sheet.getRow(0);int colNum = row.getPhysicalNumberOfCells();// 正⽂内容应该从第⼆⾏开始,第⼀⾏为表头的标题for (int i = 1; i <= rowNum; i++) {row = sheet.getRow(i);int j = 0;while (j < colNum) {// 每个单元格的数据内容⽤"-"分割开，以后需要时⽤String类的replace()⽅法还原数据// 也可以将每个单元格的数据设置到⼀个javabean的属性中，此时需要新建⼀个javabean // str += getStringCellValue(row.getCell((short) j)).trim() +// "-";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的Typeswitch (cell.getCellType()) {// 如果当前Cell的Type为NUMERICcase HSSFCell.CELL_TYPE_NUMERIC:case HSSFCell.CELL_TYPE_FORMULA: {// 判断当前的cell是否为Dateif (HSSFDateUtil.isCellDateFormatted(cell)) {// 如果是Date类型则，转化为Data格式//⽅法1：这样⼦的data格式是带时分秒的：2011-10-12 0:00:00 //cellvalue = cell.getDateCellValue().toLocaleString();//⽅法2：这样⼦的data格式是不带带时分秒的：2011-10-12Date date = cell.getDateCellValue();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); cellvalue = sdf.format(date);}// 如果是纯数字else {// 取得当前Cell的数值cellvalue = String.valueOf(cell.getNumericCellValue());}break;}// 如果当前Cell的Type为STRINcase HSSFCell.CELL_TYPE_STRING:// 取得当前的Cell字符串cellvalue = cell.getRichStringCellValue().getString();break;// 默认的Cell值default:cellvalue = " ";}} else {cellvalue = "";}return cellvalue;}public static void main(String[] args) {try {// 对读取Excel表格标题测试InputStream is = new FileInputStream("d:\\test2.xls");ExcelReader excelReader = new ExcelReader();String[] title = excelReader.readExcelTitle(is);System.out.println("获得Excel表格的标题:");for (String s : title) {System.out.print(s + " ");}for (int i = 1; i <= map.size(); i++) {System.out.println(map.get(i));}} catch (FileNotFoundException e) {System.out.println("未找到指定路径的⽂件!");e.printStackTrace();}}}。

Java读写Excel之POI超入门博客分类：∙JavajavaexcelpoiApache POI是用Java编写的免费开源的跨平台的Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。

Apache POI是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE2复合文档格式（OLE2）的Java API。

用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint 文件。

Apache POI提供Java操作Excel解决方案（适用于Excel97-2008）。

∙HSSF－提供读写Microsoft Excel XLS格式档案的功能。

∙XSSF－提供读写Microsoft Excel OOXML XLSX格式档案的功能。

∙HWPF－提供读写Microsoft Word DOC格式档案的功能。

∙HSLF－提供读写Microsoft PowerPoint格式档案的功能。

∙HDGF－提供读Microsoft Visio格式档案的功能。

∙HPBF－提供读Microsoft Publisher格式档案的功能。

∙HSMF－提供读Microsoft Outlook格式档案的功能。

/Busy Developers'Guide to HSSF and XSSF Features版本：poi-3.8-20120326.jar1、生成WorkbookJava代码1.//生成Workbook2.HSSFWorkbook wb=new HSSFWorkbook();3.4.//添加Worksheet（不添加sheet时生成的xls文件打开时会报错）5.@SuppressWarnings("unused")6.Sheet sheet1=wb.createSheet();7.@SuppressWarnings("unused")8.Sheet sheet2=wb.createSheet();9.@SuppressWarnings("unused")10.Sheet sheet3=wb.createSheet("new sheet");11.@SuppressWarnings("unused")12.Sheet sheet4=wb.createSheet("rensanning");13.14.//保存为Excel文件15.FileOutputStream out=null;16.17.try{18.out=new FileOutputStream("c:\\text.xls");19.wb.write(out);20.}catch(IOException e){21.System.out.println(e.toString());22.}finally{23.try{24.out.close();25.}catch(IOException e){26.System.out.println(e.toString());27.}28.}2、生成Workbook OOXML形式(.xlsx)Java代码1.2.//生成Workbook3.XSSFWorkbook wb=new XSSFWorkbook();4.5.//......3、打开WorkbookJava代码1.2.//方法一：使用WorkbookFactory3.FileInputStream in=null;4.Workbook wb=null;5.6.try{7.in=new FileInputStream(TEST_WORKBOOK_NAME);8.wb=WorkbookFactory.create(in);9.}catch(IOException e){10.System.out.println(e.toString());11.}catch(InvalidFormatException e){12.System.out.println(e.toString());13.}finally{14.try{15.in.close();16.}catch(IOException e){17.System.out.println(e.toString());18.}19.}20.21.System.out.println("====================Workbook====================");22.System.out.println("Number of Sheets:"+wb.getNumberOfSheets());23.System.out.println("Sheet3's name:"+wb.getSheetName(3));24.System.out.println();25.26.//方法二：使用POIFSFileSystem27.try{28.in=new FileInputStream(TEST_WORKBOOK_NAME);29.POIFSFileSystem fs=new POIFSFileSystem(in);30.wb=new HSSFWorkbook(fs);31.}catch(IOException e){32.System.out.println(e.toString());33.}finally{34.try{35.in.close();36.}catch(IOException e){37.System.out.println(e.toString());38.}39.}40.41.System.out.println("====================Workbook====================");42.System.out.println("Number of Sheets:"+wb.getNumberOfSheets());43.System.out.println("Sheet3's name:"+wb.getSheetName(3));44.System.out.println();4、打开加密的Workbook(读加密)Java代码1.FileInputStream input=new FileInputStream(TEST_WORKBOOK_NAME_ENCRYPTED);2.BufferedInputStream binput=new BufferedInputStream(input);3.POIFSFileSystem poifs=new POIFSFileSystem(binput);4.5.Biff8EncryptionKey.setCurrentUserPassword(TEST_WORKBOOK_PASSWORD);6.7.HSSFWorkbook wb=new HSSFWorkbook(poifs);8.9.System.out.println("====================EncryptedWorkbook====================");10.System.out.println("Number of Sheets:"+wb.getNumberOfSheets());11.System.out.println("Sheet0's name:"+wb.getSheetName(0));12.System.out.println();5、追加SheetJava代码1.Sheet sheet=wb.createSheet("append sheet");6、复制SheetJava代码1.wb.cloneSheet(1);7、修改Sheet名称Java代码1.wb.setSheetName(i,"SheetName new");8、删除SheetJava代码1.wb.removeSheetAt(1);9、设置下部Sheet名的Tab的第一个可见TabJava代码1.//设置下部Sheet名的Tab的第一个可见Tab（以左的Sheet看不见）2.wb.setFirstVisibleTab(2);10、调整Sheet顺序Java代码1.wb.setSheetOrder("SheetName3",1);2.wb.setSheetOrder(wb.getSheetName(4),0);11、设置当前Sheett.setActiveSheet();Java代码1.//设置当前Sheet2.wb.setActiveSheet(wb.getNumberOfSheets()-1);3.//（Excel的当前Sheet被设置，需要结合setSelected使用，不然下部Sheet名的Tab还是默认为第一个）4.//（需要选择多个Sheet的话，每个Sheet调用setSelected(true)即可）5.wb.getSheetAt(wb.getNumberOfSheets()-1).setSelected(true);12、固定窗口Java代码1.wb.getSheet("SheetName4").createFreezePane(2,2);13、分割窗口Java代码1.wb.getSheet("SheetName5").createSplitPane(2000,2000,0,0,HSSFSheet.PANE_LOWER_LEFT);14、Sheet缩放Java代码1.//setZoom(int numerator,int denominator)2.//"numerator"÷"denominator"例如：3÷1＝3那就是设置为300%3.4.//扩大(200%)5.wb.getSheet("sheetname1").setZoom(2,1);6.//缩小(50%)7.wb.getSheet("sheetname2").setZoom(1,2);15、行列分组Java代码1.wb.getSheet("sheetname3").groupColumn(4,7);2.wb.getSheet("sheetname3").groupColumn(9,12);3.wb.getSheet("sheetname3").groupColumn(10,11);4.5.wb.getSheet("sheetname3").groupRow(5,14);6.wb.getSheet("sheetname3").groupRow(7,13);7.wb.getSheet("sheetname3").groupRow(16,19);16、关闭分组Java代码1.wb.getSheet("sheetname3").setColumnGroupCollapsed(10,true);2.wb.getSheet("sheetname3").setRowGroupCollapsed(7,true); 17、插入行Java代码1.Row row1=wb.getSheet("sheetname4").createRow(1);2.Cell cell1_1=row1.createCell(1);3.cell1_1.setCellValue(123);4.5.Row row4=wb.getSheet("sheetname4").createRow(4);6.Cell cell4_3=row4.createCell(3);7.cell4_3.setCellValue("中国");18、删除行Java代码1.Row row=wb.getSheet("sheetname4").getRow(1);2.wb.getSheet("sheetname4").removeRow(row);19、移动行Java代码1.//******移动行只移动内容，不牵扯行的删除和插入2.3.//移动行（把第1行和第2行移到第5行之后）4.wb.getSheet("sheetname5").shiftRows(0,1,5);5.6.//移动行（把第3行和第4行往上移动1行）7.wb.getSheet("sheetname5").shiftRows(2,3,-1);20、修改行高Java代码1.//设置默认行高2.wb.getSheet("sheetname6").setDefaultRowHeight((short)100);3.4.//设置行高5.wb.getSheet("sheetname6").getRow(2).setHeight((short)(100*20));21、修改列宽Java代码1.//设置默认列宽2.wb.getSheet("sheetname7").setDefaultColumnWidth(12);3.4.//设置列宽5.wb.getSheet("sheetname7").setColumnWidth(0,5*256);22、不显示网格线Java代码1.//不显示网格线2.wb.getSheet("sheetname8").setDisplayGridlines(false);23、设置分页Java代码1.//设置第一页：3行2列（可以多次设置）2.wb.getSheet("sheetname9").setRowBreak(2);3.wb.getSheet("sheetname9").setColumnBreak(1);24、添加，删除，合并单元格Java代码1.//追加行2.for(int i=0;i<10;i++){3.Row row=wb.getSheet("sheetname10").createRow(i);4.for(int j=0;j<10;j++){5.//添加单元格6.Cell cell=row.createCell(j);7.cell.setCellValue(i+1);8.}9.10.//删除单元格11.row.removeCell(row.getCell(5));12.}13.14.//合并单元格15.//CellRangeAddress(int firstRow,int lastRow,int firstCol,int lastCol)16.wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1,4,2,3));25、设置Header，FooterJava代码1.//Header2.Header header=wb.getSheet("sheetname11").getHeader();3.header.setLeft(HSSFHeader.startUnderline()+4.HSSFHeader.font("宋体","Italic")+5."文字文字"+6.HSSFHeader.endUnderline());7.header.setCenter(HSSFHeader.fontSize((short)16)+8.HSSFHeader.startDoubleUnderline()+9.HSSFHeader.startBold()+10."汉字汉字"+11.HSSFHeader.endBold()+12.HSSFHeader.endDoubleUnderline());13.header.setRight("打印时间:"+HSSFHeader.date()+""+HSSFHeader.time());14.15.//Footer16.Footer footer=wb.getSheet("sheetname11").getFooter();17.footer.setLeft("Copyright@rensanning");18.footer.setCenter("Page:"+HSSFFooter.page()+"/"+HSSFFooter.numPages());19.footer.setRight("File:"+HSSFFooter.file());26、设置单元格值Java代码1.//boolean2.Cell cell00=rows[0].createCell(0);3.boolean val00=true;4.cell00.setCellValue(val00);5.6.//Calendar格式化7.CellStyle styleCalendar=wb.createCellStyle();8.DataFormat formatCalendar=wb.createDataFormat();9.styleCalendar.setDataFormat(formatCalendar.getFormat("yyyy/mm/dd"));10.Cell cell11=rows[1].createCell(0);11.Calendar val11=Calendar.getInstance();12.cell11.setCellStyle(styleCalendar);13.cell11.setCellValue(val11);14.15.//Date格式化16.CellStyle styleDate=wb.createCellStyle();17.DataFormat formatDate=wb.createDataFormat();18.styleDate.setDataFormat(formatDate.getFormat("yyyy/mm/dd hh:mm"));19.Cell cell21=rows[2].createCell(0);20.Date val21=new Date();21.cell21.setCellStyle(styleDate);22.cell21.setCellValue(val21);23.24.//double25.Cell cell30=rows[3].createCell(0);26.double val30=1234.56;27.cell30.setCellValue(val30);28.29.//double格式化30.CellStyle styleDouble=wb.createCellStyle();31.DataFormat formatDouble=wb.createDataFormat();32.styleDouble.setDataFormat(formatDouble.getFormat("#,##0.00"));33.Cell cell31=rows[3].createCell(1);34.double val31=1234.56;35.cell31.setCellStyle(styleDouble);36.cell31.setCellValue(val31);37.38.//String39.Cell cell40=rows[4].createCell(0);40.HSSFRichTextString val40=new HSSFRichTextString("Test汉字");41.cell40.setCellValue(val40);27、设置单元格边线Java代码1.wb.getSheet("sheetname2").setColumnWidth(1,4096);2.3.Row row1=wb.getSheet("sheetname2").createRow(1);4.row1.setHeightInPoints(70);5.6.Cell cell1_1=row1.createCell(1);7.cell1_1.setCellValue("Sample");8.9.CellStyle style=wb.createCellStyle();10.11.style.setBorderTop(CellStyle.BORDER_DASHED);12.style.setBorderBottom(CellStyle.BORDER_DOUBLE);13.style.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT);14.style.setBorderRight(CellStyle.BORDER_MEDIUM);15.16.style.setTopBorderColor(IndexedColors.MAROON.getIndex());17.style.setBottomBorderColor(IndexedColors.SKY_BLUE.getIndex());18.style.setLeftBorderColor(IndexedColors.ORANGE.getIndex());19.style.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex());20.21.cell1_1.setCellStyle(style);28、设置单元格背景填充Java代码1.wb.getSheet("sheetname3").setColumnWidth(0,4096);2.wb.getSheet("sheetname3").setColumnWidth(1,4096);3.wb.getSheet("sheetname3").setColumnWidth(2,4096);4.5.Row row1=wb.getSheet("sheetname3").createRow(1);6.row1.setHeightInPoints(70);7.8.Cell cell1_0=row1.createCell(0);9.Cell cell1_1=row1.createCell(1);10.Cell cell1_2=row1.createCell(2);11.12.cell1_0.setCellValue("THIN_VERT_BANDS");13.cell1_1.setCellValue("BIG_SPOTS");14.cell1_2.setCellValue("THICK_HORZ_BANDS");15.16.CellStyle style1=wb.createCellStyle();17.style1.setFillPattern(CellStyle.THIN_VERT_BANDS);18.style1.setFillForegroundColor(IndexedColors.WHITE.getIndex());19.style1.setFillBackgroundColor(IndexedColors.BLUE.getIndex());20.21.CellStyle style2=wb.createCellStyle();22.style2.setFillPattern(CellStyle.BIG_SPOTS);23.style2.setFillForegroundColor(IndexedColors.RED.getIndex());24.style2.setFillBackgroundColor(IndexedColors.WHITE.getIndex());25.26.CellStyle style3=wb.createCellStyle();27.style3.setFillPattern(CellStyle.THICK_HORZ_BANDS);28.style3.setFillForegroundColor(IndexedColors.PINK.getIndex());29.style3.setFillBackgroundColor(IndexedColors.BROWN.getIndex());30.31.cell1_0.setCellStyle(style1);32.cell1_1.setCellStyle(style2);33.cell1_2.setCellStyle(style3);29、设置单元格注释Java代码1.HSSFCreationHelper createHelper=2.(HSSFCreationHelper)wb.getCreationHelper();3.Drawing patriarch=wb.getSheet("sheetname4").createDrawingPatriarch();4.5.//注释6.Row row=wb.getSheet("sheetname4").createRow(1);7.Cell cell=row.createCell(1);8.9.HSSFClientAnchor clientAnchor=new HSSFClientAnchor(0,0,0,0,10.(short)4,2,(short)6,5);11.ment comment=patriarch.createCellComment(clientAnchor);ment.setString(createHelper.createRichTextString("注释注释111"));ment.setAuthor("rensanning");15.16.cell.setCellComment(comment);17.18.//带字体的注释19.Row row2=wb.getSheet("sheetname4").createRow(2);20.Cell cell2=row2.createCell(1);21.22.Font font=wb.createFont();23.font.setFontName("宋体");24.font.setFontHeightInPoints((short)10);25.font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);26.font.setColor(HSSFColor.RED.index);27.ment comment2=patriarch.createCellComment(clientAnchor);29.HSSFRichTextString text=new HSSFRichTextString("注释注释222");30.text.applyFont(font);ment2.setString(text);ment2.setAuthor("rensanning");33.34.cell2.setCellComment(comment2);30、设置单元格字体（斜体，粗体，下线，取消线，字体，大小，背景色）Java代码1.Font font=null;2.CellStyle style=null;3.4.//斜体5.font=wb.createFont();6.font.setItalic(true);7.style=wb.createCellStyle();8.style.setFont(font);9.10.wb.getSheet("sheetname5").getRow(1).getCell(1).setCellStyle(style);11.12.//粗体13.font=wb.createFont();14.font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);15.style=wb.createCellStyle();16.style.setFont(font);17.18.wb.getSheet("sheetname5").getRow(2).getCell(1).setCellStyle(style);19.20.//字体名21.font=wb.createFont();22.font.setFontName("Courier New");23.style=wb.createCellStyle();24.style.setFont(font);25.26.wb.getSheet("sheetname5").getRow(3).getCell(1).setCellStyle(style);27.28.//字体大小29.font=wb.createFont();30.font.setFontHeightInPoints((short)20);31.style=wb.createCellStyle();32.style.setFont(font);33.34.wb.getSheet("sheetname5").getRow(4).getCell(1).setCellStyle(style);35.36.//文字颜色37.font=wb.createFont();38.font.setColor(HSSFColor.YELLOW.index);39.style=wb.createCellStyle();40.style.setFont(font);41.42.wb.getSheet("sheetname5").getRow(5).getCell(1).setCellStyle(style);43.44.//上标45.font=wb.createFont();46.font.setTypeOffset(HSSFFont.SS_SUPER);47.style=wb.createCellStyle();48.style.setFont(font);49.50.wb.getSheet("sheetname5").getRow(6).getCell(1).setCellStyle(style);51.52.//下标53.font=wb.createFont();54.font.setTypeOffset(HSSFFont.SS_SUB);55.style=wb.createCellStyle();56.style.setFont(font);57.58.wb.getSheet("sheetname5").getRow(7).getCell(1).setCellStyle(style);59.60.//删除线61.font=wb.createFont();62.font.setStrikeout(true);63.style=wb.createCellStyle();64.style.setFont(font);65.66.wb.getSheet("sheetname5").getRow(8).getCell(1).setCellStyle(style);67.68.//下划线69.font=wb.createFont();70.font.setUnderline(HSSFFont.U_SINGLE);71.style=wb.createCellStyle();72.style.setFont(font);73.74.wb.getSheet("sheetname5").getRow(9).getCell(1).setCellStyle(style);75.76.//背景色77.style=wb.createCellStyle();78.style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);79.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);80.81.style.setFont(font);82.83.wb.getSheet("sheetname5").getRow(10).getCell(1).setCellStyle(style);31、设置超链接Java代码1.HSSFCreationHelper createHelper=2.(HSSFCreationHelper)wb.getCreationHelper();3.4.CellStyle style=wb.createCellStyle();5.Font font=wb.createFont();6.font.setUnderline(HSSFFont.U_SINGLE);7.font.setColor(HSSFColor.BLUE.index);8.style.setFont(font);9.10.//追加行11.Row[]rows=new Row[10];12.for(int i=0;i<10;i++){13.rows[i]=wb.getSheet("sheetname6").createRow(i);14.}15.16.//URL17.rows[0].createCell(0).setCellValue("URL Link");18.19.HSSFHyperlink link1=createHelper.createHyperlink(HSSFHyperlink.LINK_URL);20.link1.setAddress("/");21.rows[0].getCell(0).setHyperlink(link1);22.rows[0].getCell(0).setCellStyle(style);23.24.//Mail25.rows[1].createCell(0).setCellValue("Email Link");26.27.HSSFHyperlink link2=createHelper.createHyperlink(HSSFHyperlink.LINK_EMAIL);28.link2.setAddress("mailto:poi@?subject=Hyperlinks");29.rows[1].getCell(0).setHyperlink(link2);30.rows[1].getCell(0).setCellStyle(style);31.32.//File33.rows[2].createCell(0).setCellValue("File Link");34.35.HSSFHyperlink link3=createHelper.createHyperlink(HSSFHyperlink.LINK_FILE);36.link3.setAddress("link.xls");37.rows[2].getCell(0).setHyperlink(link3);38.rows[2].getCell(0).setCellStyle(style);39.40.//Workbook内41.rows[3].createCell(0).setCellValue("Worksheet Link");42.43.HSSFHyperlink link4=createHelper.createHyperlink(HSSFHyperlink.LINK_DOCUMENT);44.link4.setAddress("sheetname1!A1");45.rows[3].getCell(0).setHyperlink(link4);46.rows[3].getCell(0).setCellStyle(style);32、设置单元格横向对齐，纵向对齐Java代码1.//横向对齐2.wb.getSheet("sheetname7").setColumnWidth(2,3072);3.4.Row[]row=new Row[7];5.Cell[]cell=new Cell[7];6.7.for(int i=0;i<7;i++){8.row[i]=wb.getSheet("sheetname7").createRow(i+1);9.cell[i]=row[i].createCell(2);10.cell[i].setCellValue("Please give me a receipt");11.}12.13.CellStyle style0=wb.createCellStyle();14.style0.setAlignment(CellStyle.ALIGN_GENERAL);15.cell[0].setCellStyle(style0);16.17.CellStyle style1=wb.createCellStyle();18.style1.setAlignment(CellStyle.ALIGN_LEFT);19.cell[1].setCellStyle(style1);20.21.CellStyle style2=wb.createCellStyle();22.style2.setAlignment(CellStyle.ALIGN_CENTER);23.cell[2].setCellStyle(style2);24.25.CellStyle style3=wb.createCellStyle();26.style3.setAlignment(CellStyle.ALIGN_RIGHT);27.cell[3].setCellStyle(style3);28.29.CellStyle style4=wb.createCellStyle();30.style4.setAlignment(CellStyle.ALIGN_FILL);31.cell[4].setCellStyle(style4);32.33.CellStyle style5=wb.createCellStyle();34.style5.setAlignment(CellStyle.ALIGN_JUSTIFY);35.cell[5].setCellStyle(style5);36.37.CellStyle style6=wb.createCellStyle();38.style6.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);39.cell[6].setCellStyle(style6);40.41.//纵向对齐42.Row row2=wb.getSheet("sheetname8").createRow(1);43.row2.setHeightInPoints(70);44.Cell[]cell2=new Cell[4];45.46.for(int i=0;i<4;i++){47.cell2[i]=row2.createCell(i+1);48.cell2[i].setCellValue("Please give me a receipt");49.}50.51.CellStyle style02=wb.createCellStyle();52.style02.setVerticalAlignment(CellStyle.VERTICAL_TOP);53.cell2[0].setCellStyle(style02);54.55.CellStyle style12=wb.createCellStyle();56.style12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);57.cell2[1].setCellStyle(style12);58.59.CellStyle style22=wb.createCellStyle();60.style22.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);61.cell2[2].setCellStyle(style22);62.63.CellStyle style32=wb.createCellStyle();64.style32.setVerticalAlignment(CellStyle.VERTICAL_JUSTIFY);65.cell2[3].setCellStyle(style32);33、设置单元格旋转角度Java代码1.Row[]row=new Row[4];2.Cell[]cell=new Cell[4];3.4.for(int i=0;i<4;i++){5.row[i]=wb.getSheet("sheetname9").createRow(i+1);6.cell[i]=row[i].createCell(2);7.cell[i].setCellValue("Coffee");8.}9.10.CellStyle style0=wb.createCellStyle();11.style0.setRotation((short)45);12.cell[0].setCellStyle(style0);13.14.CellStyle style1=wb.createCellStyle();15.style1.setRotation((short)0);16.cell[1].setCellStyle(style1);17.18.CellStyle style2=wb.createCellStyle();19.style2.setRotation((short)-45);20.cell[2].setCellStyle(style2);21.22.CellStyle style3=wb.createCellStyle();23.style3.setRotation((short)-90);24.cell[3].setCellStyle(style3);34、设置单元格自动折行Java代码1.Row[]row=new Row[2];2.Cell[]cell=new Cell[2];3.4.for(int i=0;i<2;i++){5.row[i]=wb.getSheet("sheetname10").createRow(i+1);6.cell[i]=row[i].createCell(2);7.cell[i].setCellValue("Thank you very much.");8.}9.10.CellStyle style0=wb.createCellStyle();11.style0.setWrapText(true);12.cell[0].setCellStyle(style0);13.14.CellStyle style1=wb.createCellStyle();15.style1.setWrapText(false);16.cell[1].setCellStyle(style1);35、设置单元格文字缩进Java代码1.Row[]row=new Row[4];2.Cell[]cell=new Cell[4];3.4.for(int i=0;i<4;i++){5.row[i]=wb.getSheet("sheetname11").createRow(i+1);6.cell[i]=row[i].createCell(2);7.cell[i].setCellValue("Coffee");8.}9.10.CellStyle style1=wb.createCellStyle();11.style1.setIndention((short)1);12.style1.setAlignment(CellStyle.ALIGN_LEFT);13.cell[1].setCellStyle(style1);14.15.CellStyle style2=wb.createCellStyle();16.style2.setIndention((short)2);17.style2.setAlignment(CellStyle.ALIGN_LEFT);18.cell[2].setCellStyle(style2);19.20.CellStyle style3=wb.createCellStyle();21.style3.setIndention((short)3);22.style3.setAlignment(CellStyle.ALIGN_LEFT);23.cell[3].setCellStyle(style3);36、自定义格式Java代码1.Row[]rows=new Row[2];2.for(int i=0;i<rows.length;i++){3.rows[i]=wb.getSheet("sheetname12").createRow(i+1);4.}5.DataFormat format=wb.createDataFormat();6.7.CellStyle[]styles=new CellStyle[2];8.for(int i=0;i<styles.length;i++){9.styles[i]=wb.createCellStyle();10.}11.styles[0].setDataFormat(format.getFormat("0.0"));12.styles[1].setDataFormat(format.getFormat("#,##0.000"));13.14.Cell[]cells=new Cell[2];15.for(int i=0;i<cells.length;i++){16.cells[i]=rows[i].createCell(1);17.cells[i].setCellValue(1111.25);18.19.cells[i].setCellStyle(styles[i]);20.}37、设置公式Java代码1.Row row1=wb.getSheet("sheetname13").createRow(1);2.Row row2=wb.getSheet("sheetname13").createRow(2);3.4.Cell cell1_1=row1.createCell(1);5.Cell cell1_2=row1.createCell(2);6.Cell cell1_3=row1.createCell(3);7.Cell cell2_3=row2.createCell(3);8.9.cell1_1.setCellValue(30);10.cell1_2.setCellValue(25);11.cell1_3.setCellFormula("B2+C2");12.cell2_3.setCellFormula("MOD(B2,C2)");38、画直线，圆圈（椭圆），正方形（长方形）,TextboxJava代码1.HSSFPatriarch patriarch=((HSSFSheet)wb.getSheet("sheetname14")).createDrawingPatriarch();2.3.//直线4.HSSFClientAnchor clientAnchor1=new HSSFClientAnchor(0,0,0,0,5.(short)4,2,(short)6,5);6.HSSFSimpleShape shape1=patriarch.createSimpleShape(clientAnchor1);7.shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);8.9.//圆圈（椭圆）10.HSSFClientAnchor clientAnchor2=new HSSFClientAnchor(0,0,0,0,11.(short)8,4,(short)6,5);12.HSSFSimpleShape shape2=patriarch.createSimpleShape(clientAnchor2);13.shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);14.15.//正方形（长方形）16.HSSFClientAnchor clientAnchor3=new HSSFClientAnchor(0,0,0,0,17.(short)12,6,(short)6,5);18.HSSFSimpleShape shape3=patriarch.createSimpleShape(clientAnchor3);19.shape3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);20.21.//Textbox22.HSSFClientAnchor clientAnchor4=new HSSFClientAnchor(0,0,0,0,23.(short)14,8,(short)6,5);24.HSSFTextbox textbox=patriarch.createTextbox(clientAnchor4);25.textbox.setString(new HSSFRichTextString("This is a test"));39、插入图片Java代码1.//需要commons-codec-1.6.jar2.FileInputStream jpeg=new FileInputStream("resource/test.jpg");3.byte[]bytes=IOUtils.toByteArray(jpeg);4.int pictureIndex=wb.addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_JPEG);5.jpeg.close();6.7.HSSFCreationHelper helper=(HSSFCreationHelper)wb.getCreationHelper();8.9.HSSFPatriarch patriarch=((HSSFSheet)wb.getSheet("sheetname15")).createDrawingPatriarch();10.11.HSSFClientAnchor clientAnchor=helper.createClientAnchor();12.13.clientAnchor.setCol1(3);14.clientAnchor.setRow1(2);15.16.HSSFPicture picture=patriarch.createPicture(clientAnchor,pictureIndex);17.picture.resize();40、设置可输入ListJava代码1.CellRangeAddressList addressList=new CellRangeAddressList(2.0,3.0,4.0,5.0);6.7.final String[]DATA_LIST=new String[]{8."10",9."20",10."30",11.};12.DVConstraint dvConstraint=13.DVConstraint.createExplicitListConstraint(DATA_LIST);14.15.HSSFDataValidation dataValidation=new HSSFDataValidation(addressList,dvConstraint);16.dataValidation.setSuppressDropDownArrow(false);17.18.wb.getSheet("sheetname16").addValidationData(dataValidation);41、设置输入提示信息Java代码1.CellRangeAddressList addressList=new CellRangeAddressList(2.0,3.0,4.0,5.0);6.7.final String[]DATA_LIST=new String[]{8."10",9."20",10."30",11.};12.DVConstraint dvConstraint=DVConstraint.createExplicitListConstraint(DATA_LIST);13.14.HSSFDataValidation dataValidation=15.new HSSFDataValidation(addressList,dvConstraint);16.dataValidation.setSuppressDropDownArrow(false);17.dataValidation.createPromptBox("输入提示","请从下拉列表中选择！");18.dataValidation.setShowPromptBox(true);19.20.wb.getSheet("sheetname17").addValidationData(dataValidation);。

JavaPOI常⽤⽅法，读取单元格的值，设置单元格格式，合并单元格，获取已合并的单元格，导。

⼀、设置单元格格式。

设置单元格边框、单元格背景颜⾊、单元格对齐⽅式、单元格字体，设置⾃动换⾏。

/** Description: 设置单元格格式.* @author : ys.* @date : 20-3-31 上午11:09* @param workbook :* @return: ermodel.CellStyle**/public CellStyle setCellStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();//设置边框cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);//设置背景颜⾊cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置对齐⽅式cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//字体设置Font font = workbook.createFont();font.setFontName("⿊体");font.setFontHeightInPoints((short) 13);cellStyle.setFont(font);//内容⾃动换⾏cellStyle.setWrapText(true);return cellStyle;}⼆、获取单元格中的值。

JavaPOI操作Excel（读取写⼊）pom.xml依赖:<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.17</version></dependency><dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>23.0</version></dependency>Java类ExcelUtils代码:package com.dzpykj.files.excel;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.Calendar;import java.util.Date;import java.util.Iterator;import java.util.List;import java.util.Map;import ng3.StringUtils;import org.apache.poi.EncryptedDocumentException;import ermodel.HSSFWorkbook;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import ermodel.BorderStyle;import ermodel.Cell;import ermodel.CellStyle;import ermodel.CellType;import ermodel.FillPatternType;import ermodel.Font;import ermodel.HorizontalAlignment;import ermodel.IndexedColors;import ermodel.Row;import ermodel.Sheet;import ermodel.VerticalAlignment;import ermodel.Workbook;import ermodel.WorkbookFactory;import ermodel.XSSFCellStyle;import ermodel.XSSFWorkbook;import mon.collect.Maps;/*** Excel操作⼯具类* @author ChaiXY*/public class ExcelUtils {// @Value("${file_base_path}")// private static String fileBasePath;//⽂件的基础路径// private static String fileBasePath = System.getProperty("user.dir") + File.separator + "excel" + File.separator;;//⽂件的基础路径public static final String OFFICE_EXCEL_XLS = "xls";public static final String OFFICE_EXCEL_XLSX = "xlsx";/*** 读取指定Sheet也的内容* @param filepath filepath ⽂件全路径* @param sheetNo sheet序号,从0开始,如果读取全⽂sheetNo设置null*/public static String readExcel(String filepath, Integer sheetNo)throws EncryptedDocumentException, InvalidFormatException, IOException {StringBuilder sb = new StringBuilder();Workbook workbook = getWorkbook(filepath);if (workbook != null) {if (sheetNo == null) {int numberOfSheets = workbook.getNumberOfSheets();for (int i = 0; i < numberOfSheets; i++) {Sheet sheet = workbook.getSheetAt(i);if (sheet == null) {continue;}sb.append(readExcelSheet(sheet));}} else {Sheet sheet = workbook.getSheetAt(sheetNo);if (sheet != null) {sb.append(readExcelSheet(sheet));}}}return sb.toString();}/*** 根据⽂件路径获取Workbook对象* @param filepath ⽂件全路径*/public static Workbook getWorkbook(String filepath)throws EncryptedDocumentException, InvalidFormatException, IOException {InputStream is = null;Workbook wb = null;if (StringUtils.isBlank(filepath)) {throw new IllegalArgumentException("⽂件路径不能为空");} else {String suffiex = getSuffiex(filepath);if (StringUtils.isBlank(suffiex)) {throw new IllegalArgumentException("⽂件后缀不能为空");}if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) {try {is = new FileInputStream(filepath);wb = WorkbookFactory.create(is);} finally {if (is != null) {is.close();}if (wb != null) {wb.close();}}} else {throw new IllegalArgumentException("该⽂件⾮Excel⽂件");}}return wb;}/*** 获取后缀* @param filepath filepath ⽂件全路径*/private static String getSuffiex(String filepath) {if (StringUtils.isBlank(filepath)) {return "";}int index = stIndexOf(".");if (index == -1) {return "";}return filepath.substring(index + 1, filepath.length());}private static String readExcelSheet(Sheet sheet) {StringBuilder sb = new StringBuilder();if(sheet != null){int rowNos = sheet.getLastRowNum();// 得到excel的总记录条数for (int i = 0; i <= rowNos; i++) {// 遍历⾏Row row = sheet.getRow(i);if(row != null){int columNos = row.getLastCellNum();// 表头总共的列数for (int j = 0; j < columNos; j++) {Cell cell = row.getCell(j);if(cell != null){cell.setCellType(CellType.STRING);sb.append(cell.getStringCellValue() + " ");// System.out.print(cell.getStringCellValue() + " ");}}// System.out.println();}}}return sb.toString();}/*** 读取指定Sheet页的表头* @param filepath filepath ⽂件全路径* @param sheetNo sheet序号,从0开始,必填*/public static Row readTitle(String filepath, int sheetNo)throws IOException, EncryptedDocumentException, InvalidFormatException { Row returnRow = null;Workbook workbook = getWorkbook(filepath);if (workbook != null) {Sheet sheet = workbook.getSheetAt(sheetNo);returnRow = readTitle(sheet);}return returnRow;}/*** 读取指定Sheet页的表头*/public static Row readTitle(Sheet sheet) throws IOException {Row returnRow = null;int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数for (int i = 0; i < totalRow; i++) {// 遍历⾏Row row = sheet.getRow(i);if (row == null) {continue;}returnRow = sheet.getRow(0);break;}return returnRow;}/*** 创建Excel⽂件* @param filepath filepath ⽂件全路径* @param sheetName 新Sheet页的名字* @param titles 表头* @param values 每⾏的单元格*/public static boolean writeExcel(String filepath, String sheetName, List<String> titles, List<Map<String, Object>> values) throws IOException {boolean success = false;OutputStream outputStream = null;if (StringUtils.isBlank(filepath)) {throw new IllegalArgumentException("⽂件路径不能为空");} else {String suffiex = getSuffiex(filepath);if (StringUtils.isBlank(suffiex)) {throw new IllegalArgumentException("⽂件后缀不能为空");}Workbook workbook;if ("xls".equals(suffiex.toLowerCase())) {workbook = new HSSFWorkbook();} else {workbook = new XSSFWorkbook();}// ⽣成⼀个表格Sheet sheet;if (StringUtils.isBlank(sheetName)) {// name 为空则使⽤默认值sheet = workbook.createSheet();} else {sheet = workbook.createSheet(sheetName);}// 设置表格默认列宽度为15个字节sheet.setDefaultColumnWidth((short) 15);// ⽣成样式Map<String, CellStyle> styles = createStyles(workbook);// 创建标题⾏Row row = sheet.createRow(0);// 存储标题在Excel⽂件中的序号Map<String, Integer> titleOrder = Maps.newHashMap();for (int i = 0; i < titles.size(); i++) {Cell cell = row.createCell(i);cell.setCellStyle(styles.get("header"));String title = titles.get(i);cell.setCellValue(title);titleOrder.put(title, i);}// 写⼊正⽂Iterator<Map<String, Object>> iterator = values.iterator();// ⾏号int index = 1;while (iterator.hasNext()) {row = sheet.createRow(index);Map<String, Object> value = iterator.next();for (Map.Entry<String, Object> map : value.entrySet()) {// 获取列名String title = map.getKey();// 根据列名获取序号int i = titleOrder.get(title);// 在指定序号处创建cellCell cell = row.createCell(i);// 设置cell的样式if (index % 2 == 1) {cell.setCellStyle(styles.get("cellA"));} else {cell.setCellStyle(styles.get("cellB"));}// 获取列的值Object object = map.getValue();// 判断object的类型SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");if (object instanceof Double) {cell.setCellValue((Double) object);} else if (object instanceof Date) {String time = simpleDateFormat.format((Date) object);cell.setCellValue(time);} else if (object instanceof Calendar) {Calendar calendar = (Calendar) object;String time = simpleDateFormat.format(calendar.getTime());cell.setCellValue(time);} else if (object instanceof Boolean) {cell.setCellValue((Boolean) object);} else {if (object != null) {cell.setCellValue(object.toString());}}}index++;}try {outputStream = new FileOutputStream(filepath);workbook.write(outputStream);success = true;} finally {if (outputStream != null) {outputStream.close();}if (workbook != null) {workbook.close();}}return success;}}/*** 设置格式*/private static Map<String, CellStyle> createStyles(Workbook wb) {Map<String, CellStyle> styles = Maps.newHashMap();// 标题样式XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle();titleStyle.setAlignment(HorizontalAlignment.CENTER); // ⽔平对齐titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐titleStyle.setLocked(true); // 样式锁定titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());Font titleFont = wb.createFont();titleFont.setFontHeightInPoints((short) 16);titleFont.setBold(true);titleFont.setFontName("微软雅⿊");titleStyle.setFont(titleFont);styles.put("title", titleStyle);// ⽂件头样式XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle();headerStyle.setAlignment(HorizontalAlignment.CENTER);headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); // 前景⾊ headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 颜⾊填充⽅式 headerStyle.setWrapText(true);headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());headerStyle.setBorderLeft(BorderStyle.THIN);headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());headerStyle.setBorderTop(BorderStyle.THIN);headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());headerStyle.setBorderBottom(BorderStyle.THIN);headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());Font headerFont = wb.createFont();headerFont.setFontHeightInPoints((short) 12);headerFont.setColor(IndexedColors.WHITE.getIndex());titleFont.setFontName("微软雅⿊");headerStyle.setFont(headerFont);styles.put("header", headerStyle);Font cellStyleFont = wb.createFont();cellStyleFont.setFontHeightInPoints((short) 12);cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex());cellStyleFont.setFontName("微软雅⿊");// 正⽂样式AXSSFCellStyle cellStyleA = (XSSFCellStyle) wb.createCellStyle();cellStyleA.setAlignment(HorizontalAlignment.CENTER); // 居中设置cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER);cellStyleA.setWrapText(true);cellStyleA.setBorderRight(BorderStyle.THIN);cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex());cellStyleA.setBorderLeft(BorderStyle.THIN);cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex());cellStyleA.setBorderTop(BorderStyle.THIN);cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex());cellStyleA.setBorderBottom(BorderStyle.THIN);cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex());cellStyleA.setFont(cellStyleFont);styles.put("cellA", cellStyleA);// 正⽂样式B:添加前景⾊为浅黄⾊XSSFCellStyle cellStyleB = (XSSFCellStyle) wb.createCellStyle();cellStyleB.setAlignment(HorizontalAlignment.CENTER);cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER);cellStyleB.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());cellStyleB.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyleB.setWrapText(true);cellStyleB.setBorderRight(BorderStyle.THIN);cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex());cellStyleB.setBorderLeft(BorderStyle.THIN);cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex());cellStyleB.setBorderTop(BorderStyle.THIN);cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex());cellStyleB.setBorderBottom(BorderStyle.THIN);cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex());cellStyleB.setFont(cellStyleFont);styles.put("cellB", cellStyleB);return styles;}/*** 将源⽂件的内容复制到新Excel⽂件(可供理解Excel使⽤,使⽤价值不⼤)* @param srcFilepath 源⽂件全路径* @param desFilepath ⽬标⽂件全路径*/public static void writeExcel(String srcFilepath, String desFilepath)throws IOException, EncryptedDocumentException, InvalidFormatException {FileOutputStream outputStream = null;String suffiex = getSuffiex(desFilepath);if (StringUtils.isBlank(suffiex)) {throw new IllegalArgumentException("⽂件后缀不能为空");}Workbook workbook_des;if ("xls".equals(suffiex.toLowerCase())) {workbook_des = new HSSFWorkbook();} else {workbook_des = new XSSFWorkbook();}Workbook workbook = getWorkbook(srcFilepath);if (workbook != null) {int numberOfSheets = workbook.getNumberOfSheets();for (int k = 0; k < numberOfSheets; k++) {Sheet sheet = workbook.getSheetAt(k);Sheet sheet_des = workbook_des.createSheet(sheet.getSheetName());if (sheet != null) {int rowNos = sheet.getLastRowNum();for (int i = 0; i <= rowNos; i++) {Row row = sheet.getRow(i);Row row_des = sheet_des.createRow(i);if(row != null){int columNos = row.getLastCellNum();for (int j = 0; j < columNos; j++) {Cell cell = row.getCell(j);Cell cell_des = row_des.createCell(j);if(cell != null){cell.setCellType(CellType.STRING);cell_des.setCellType(CellType.STRING);cell_des.setCellValue(cell.getStringCellValue());}}}}}}}try {outputStream = new FileOutputStream(desFilepath);workbook_des.write(outputStream);} finally {if (outputStream != null) {outputStream.close();}if (workbook != null) {workbook_des.close();}}}public static void main(String[] args) {}}。

一、基于POI操作EXCEL文件1.<!-- POI依赖包-->2.<dependency>3.<groupId>org.apache.poi</groupId>4.<artifactId>poi</artifactId>5.<version>4.1.2</version>6.</dependency>7.<dependency>8.<groupId>org.apache.poi</groupId>9.<artifactId>poi-ooxml</artifactId>10.<version>4.1.2</version>11.</dependency>12.<dependency>13.<groupId>org.apache.poi</groupId>14.<artifactId>poi-ooxml-schemas</artifactId>15.<version>4.1.2</version>16.</dependency>17.<dependency>18.<groupId>xerces</groupId>19.<artifactId>xercesImpl</artifactId>20.<version>2.12.0</version>21.</dependency>EXCEL文件读操作示例如下：1.import ng.StringUtils;2.import ng.time.DateFormatUtils;3.import org.apache.poi.openxml4j.exceptions.OpenXML4JException;4.import org.apache.poi.openxml4j.opc.OPCPackage;5.import ermodel.*;6.import org.apache.poi.xssf.eventusermodel.XSSFReader;7.import org.apache.poi.xssf.model.SharedStringsTable;8.import org.apache.poi.xssf.model.StylesTable;9.import ermodel.XSSFCellStyle;10.import ermodel.XSSFRichTextString;11.import org.jdom2.Attribute;12.import org.jdom2.Document;13.import org.jdom2.Element;14.import org.jdom2.JDOMException;15.import org.jdom2.input.SAXBuilder;16.import org.platform.utils.date.DateFormatter;17.import org.slf4j.Logger;18.import org.slf4j.LoggerFactory;19.import org.xml.sax.*;20.import org.xml.sax.helpers.DefaultHandler;21.import org.xml.sax.helpers.XMLReaderFactory;22.import java.io.*;23.import java.text.DecimalFormat;24.import java.text.SimpleDateFormat;25.import java.util.ArrayList;26.import java.util.Date;27.import java.util.Iterator;28.import java.util.List;29.import java.util.function.Consumer;30.31.public class PoiExcelFileReader {32.private Logger LOG =LoggerFactory.getLogger(PoiExcelFileReader.class);33.enum XssfDataType {34.BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX,NUMBER, DATE, NULL35.}36.public static final SimpleDateFormat SDF = DateFormatter.TIME.get();37.public static final DecimalFormat DF = new DecimalFormat("0");38.public static final int ERROR = 0;39.public static final int BOOLEAN = 1;40.public static final int NUMBER = 2;41.public static final int STRING = 3;42.public static final int DATE = 4;43.public static final String DATE_FORMAT_STR1 = "yyyy-MM-ddHH:mm:ss";44.public static final String DATE_FORMAT_STR2 = "yyyy/MM/ddHH:mm:ss";45.private boolean isExcel2007 = true;46.private InputStream inputStream = null;47.private InputStream sheetInputStream = null;48.private InputSource sheetInputSource = null;49.private XMLReader xmlReader = null;50.private List<PoiExcelRow> rowDataList = newArrayList<PoiExcelRow>();51.// 批量处理52.private Consumer<List<PoiExcelRow>> consumer = null;53.// 批量处理的阀值54.private Integer threshold = null;55.// 跳过表头的行数56.private Integer skipHeadLineCount = 0;57.58.public PoiExcelFileReader(InputStream in, String suffix,Consumer<List<PoiExcelRow>> consumer, int threshold) {59.this(in, suffix, consumer, threshold, 0);60.}61.62.public PoiExcelFileReader(InputStream in, String suffix,Consumer<List<PoiExcelRow>> consumer, int threshold,63.int skipHeadLineCount) {64.this.inputStream = in;65.this.isExcel2007 = "xls".equals(suffix.toLowerCase()) ? false :true;66.this.consumer = consumer;67.this.threshold = threshold;68.this.skipHeadLineCount = skipHeadLineCount;69.}70.71.public void parse() {72.if (isExcel2007) {73.try {74.init(OPCPackage.open(inputStream));75.} catch (Exception e) {76.LOG.error(e.getMessage(), e);77.}78.parseExcel2007();79.} else {80.parseExcel2003(inputStream);81.}82.}83.84.public List<PoiExcelRow> getRowDataList() {85.return this.rowDataList;86.}87.88./** 初始化将Excel转换为XML */89.private void init(OPCPackage pkg) throws IOException,OpenXML4JException, SAXException{90.XSSFReader xssfReader = new XSSFReader(pkg);91.SharedStringsTable sharedStringsTable =xssfReader.getSharedStringsTable();92.StylesTable stylesTable = xssfReader.getStylesTable();93.sheetInputStream =xssfReader.getSheet(getSheetId(xssfReader.getWorkbookData()));94.sheetInputSource = new InputSource(sheetInputStream);95.xmlReader = getXmlReader(sharedStringsTable, stylesTable);96.}97.98.private XMLReader getXmlReader(SharedStringsTablesharedStringsTable, StylesTable stylesTable) throws SAXException {99.XMLReader xmlReader =XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");100.ContentHandler contentHandler = newCustomHandler(sharedStringsTable, stylesTable);101.xmlReader.setContentHandler(contentHandler);102.return xmlReader;103.}104.105.private void parseExcel2007(){106.try {107.xmlReader.parse(sheetInputSource);108.} catch (Exception e) {109.LOG.error(e.getMessage(), e);110.} finally{111.if(sheetInputStream != null){112.try {113.sheetInputStream.close(); 114.} catch (IOException e) {115.LOG.error(e.getMessage(), e); 116.}117.}118.}119.}120.121.private String getSheetId(InputStream workbookDataInputStream) { 122.String sheetId = "rId1";123.BufferedReader br = null;124.try {125.br = new BufferedReader(newInputStreamReader(workbookDataInputStream));126.String line = null;127.loop:128.while (null != (line = br.readLine())) {129.if (line.startsWith("<workbook")) {130.InputStream in = null;131.try {132.in = newByteArrayInputStream(line.getBytes("UTF-8"));133.Document document = new SAXBuilder().build(in);134.Element rootElement = document.getRootElement();135.Iterator<Element> iterator = rootElement.getChildren().iterator();136.while(iterator.hasNext()) {137.Element element = iterator.next();138.if(!"sheets".equals(element.getName())) continue;139.List<Element> children = element.getChildren();140.for (int i = 0, iLen = children.size(); i < iLen; i++) {141.Element subElement = children.get(i);142.for (Attribute attribute : subElement.getAttributes()) {143.if ("id".equa 144.s 145.b 146.}147.}148.}149.}150.} catch (JDOMException e) {151.LOG.error(e.getMessage(), e);152.} finally {153.try {154.if (null != in) in.close();155.} catch (Exception e) {156.LOG.error(e.getMessage(), e);157.}158.}159.}160.}161.} catch (IOException e) {162.LOG.error(e.getMessage(), e);163.} finally {164.try {165.if (null != workbookDataInputStream) workbookDataInputStream.close();166.if (null != br) br.close();167.} catch (Exception e) {168.LOG.error(e.getMessage(), e); 169.}170.}171.return sheetId;172.}173.174.private class CustomHandler extends DefaultHandler {175.// 映射字符串176.private SharedStringsTable sharedStringsTable = null; 177.// 单元格样式178.private StylesTable stylesTable = null;179.// 读取值180.private String readValue = null;181.// 单元格类型182.private XssfDataType dataType = null;183.// 一行中数据文本184.private String[] rowStrings = null;185.// 一行中数据类型186.private int[] rowTypes = null;187.// 当前行索引188.private int rowIndex = 0;189.// 当前列索引190.private int columnIndex;191.private short formatIndex = -1;192.193.private CustomHandler(SharedStringsTable sharedStringsTable,StylesTable stylesTable) {194.this.sharedStringsTable = sharedStringsTable;195.this.stylesTable = stylesTable;196.}197.198.@Override199.public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {200.// 单元格201.if (qName.equals("c")) {202.this.columnIndex =getColumnIndex(attributes);203.String cellType = attributes.getValue("t"); 204.String cellStyle = attributes.getValue("s"); 205.this.dataType = XssfDataType.NUMBER; 206.if ("b".equals(cellType)) {207.this.dataType = XssfDataType.BOOL;208.} else if ("e".equals(cellType)) {209.this.dataType = XssfDataType.ERROR;210.} else if ("inlineStr".equals(cellType)) {211.this.dataType = XssfDataType.INLINESTR; 212.} else if ("s".equals(cellType)) {213.this.dataType = XssfDataType.SSTINDEX;214.} else if ("str".equals(cellType)) {215.this.dataType = XssfDataType.FORMULA; 216.} else if (cellStyle != null) {217.int styleIndex = Integer.parseInt(cellStyle);218.XSSFCellStyle style =stylesTable.getStyleAt(styleIndex);219.this.formatIndex = style.getDataFormat(); 220.}221.} else if (qName.equals("row")) {222.rowIndex++;223.// 获取该行的单元格数初始化数组224.int columnNumber =getColumnNumber(attributes);225.this.rowStrings = newString[columnNumber];226.this.rowTypes = new int[columnNumber]; 227.}228.readValue = "";229.}230.231.@Override232.public void endElement(String uri, String localName, String qName) throws SAXException {233.// 单元格的值234.if (qName.equals("v")) {235.switch (this.dataType) {236.case BOOL: {237.rowStrings[columnIndex] = readValue.charAt(0) == '0' ? "FALSE" : "TRUE"; 238.rowTypes[columnIndex] = BOOLEAN;239.break;240.}241.case ERROR: {242.rowStrings[columnIndex] = "ERROR:" + readValue.toString();243.rowTypes[columnIndex] = ERROR;244.break;245.}246.case INLINESTR: {247.rowStrings[columnIndex] = new XSSFRichTextString(readValue).getString(); 248.rowTypes[columnIndex] = STRING;249.break;250.}251.case SSTINDEX: {252.rowStrings[columnIndex] =sharedStringsTable.getItemAt(Integer.parseInt(readValue)).getString();253.rowTypes[columnIndex] = STRING; 254.break;255.}256.case FORMULA: {257.rowStrings[columnIndex] = readValue;258.rowTypes[columnIndex] = STRING;259.break;260.}261.case NUMBER: {262.// 判断是否是日期格式263.if (formatIndex != -1 && DateUtil.isADateFormat(formatIndex, readValue)) {264.Date date =DateUtil.getJavaDate(Double.parseDouble(readValue));265.if (null != date) {266.rowStrings[columnIndex] = DateFormatUtils.format(date, DATE_FORMAT_STR1); 267.} else { 268.rowStrings[columnIndex] = readValue;269.}270.rowTypes[columnIndex] = DATE;271.formatIndex = -1;272.} else {273.rowStrings[columnIndex] = readValue;274.rowTypes[columnIndex] = NUMBER;275.}276.break;277.}278.default : break;279.}280.} else if (qName.equals("row")) {281.// 当解析的一行的末尾时输出数组中的数据282.if (rowIndex > skipHeadLineCount) { 283.// 过滤空行284.boolean isValid = false;285.for (int i = 0, len = rowStrings.length; i < len; i++) {286.if (StringUtils.isNotBlank(rowStrings[i])) {287.isValid = true;288.break; 289.}290.}291.if (isValid) {292.rowDataList.add(new PoiExcelRow(rowIndex, rowTypes, rowStrings));293.if (null != threshold && rowDataList.size() > threshold) {294.consumer.accept(rowDataList);295.rowDataList.clear();296.}297.}298.}299.}300.}301.302./**303.* 如果单元格类型是字符串、INLINESTR、数字、日期，readValue则是索引值304.* 如果单元格类型是布尔值、错误、公式，readValue则是内容值305.*/306.@Override307.public void characters(char[] ch, int start, int length) throws SAXException {308.readValue += new String(ch, start, length); 309.}310.311.@Override312.public void endDocument() throws SAXException {313.super.endDocument();314.if (rowDataList.size() > 0) {315.consumer.accept(rowDataList);316.rowDataList.clear();317.}318.}319.320.private int getColumnIndex(Attributes attributes) {321.String attributeValue = attributes.getValue("r"); 322.int columnIndex = -1;323.for (int i = 0, len = attributeValue.length(); i < len; ++i) { 324.if (Character.isDigit(attributeValue.charAt(i))) break;325.columnIndex = (columnIndex + 1) * 26 + attributeValue.charAt(i) - 'A';326.}327.return columnIndex;328.}329.330.private int getColumnNumber(Attributes attrubuts){331.String spans = attrubuts.getValue("spans");332.return StringUtils.isBlank(spans) ? -1 : Integer.parseInt(spans.substring(spans.indexOf(":") + 1));333.}334.335.}336.337.private void parseExcel2003(InputStream in) {338.Workbook workbook = null;339.try {340.workbook = WorkbookFactory.create(in);341.Sheet sheet = workbook.getSheetAt(0);342.int lastCellNum =sheet.getRow(0).getPhysicalNumberOfCells();343.for (int i = skipHeadLineCount, iLen = sheet.getLastRowNum();i <= iLen; i++) {344.Row row = sheet.getRow(i);345.String[] rowStrings = new String[lastCellNum]; 346.for (int j = 0, jLen = lastCellNum; j < jLen; j++) { 347.Cell cell = row.getCell(j);348.if (null == cell) continue;349.Object cellValue = null;350.switch (cell.getCellType()) {351.case BOOLEAN: cellValue = cell.getBooleanCellValue(); break;352.case ERROR: cellValue = cell.getErrorCellValue(); break;353.case FORMULA: cellValue = cell.getCellFormula(); break;354.case NUMERIC: cellValue = DF.format(cell.getNumericCellValue()); break;355.default: cellValue = cell.getStringCellValue(); break;356.}357.rowStrings[j] = String.valueOf(cellValue);358.}359.rowDataList.add(new PoiExcelRow(i, null, rowStrings));360.if (null != threshold && rowDataList.size() > threshold) {361.consumer.accept(rowDataList); 362.rowDataList.clear();363.}364.}365.if (rowDataList.size() > 0) {366.consumer.accept(rowDataList);367.rowDataList.clear();368.}369.} catch (Exception e) {370.LOG.error(e.getMessage(), e);371.} finally {372.try {373.if (null != in) in.close();374.if (null != workbook) workbook.close(); 375.} catch (IOException e) {376.LOG.error(e.getMessage(), e);377.}378.}379.}380.381.}EXCEL文件写操作示例如下：1.import org.apache.poi.xssf.streaming.SXSSFCell;2.import org.apache.poi.xssf.streaming.SXSSFRow;3.import org.apache.poi.xssf.streaming.SXSSFSheet;4.import org.apache.poi.xssf.streaming.SXSSFWorkbook;5.import ermodel.XSSFCell;6.import ermodel.XSSFRow;7.import ermodel.XSSFSheet;8.import ermodel.XSSFWorkbook;9.import org.slf4j.Logger;10.import org.slf4j.LoggerFactory;11.12.import java.io.IOException;13.import java.io.OutputStream;14.import java.math.BigDecimal;15.import java.util.List;16.17.public class PoiExcelFileWriter {18.19.private static Logger LOG =LoggerFactory.getLogger(PoiExcelFileWriter.class);20.21.public static void writeXSSFWorkbook(List<String> headerList,List<List<Object>> resultList, OutputStream outputStream) {22.XSSFWorkbook xssfWorkbook = new XSSFWorkbook();23.XSSFSheet xssfSheet = xssfWorkbook.createSheet("sheet1");24.XSSFRow xssfRow = xssfSheet.createRow(0);25.for (int i = 0, len = headerList.size(); i < len; i++) {26.XSSFCell xssfCell = xssfRow.createCell(i);27.xssfCell.setCellValue(headerList.get(i));28.}29.for (int i = 0, iLen = resultList.size(); i < iLen; i++) {30.xssfRow = xssfSheet.createRow(i + 1);31.List<Object> result = resultList.get(i);32.for (int j = 0, jLen = result.size(); j < jLen; j++) {33.XSSFCell xssfCell = xssfRow.createCell(j);34.Object valueObj = result.get(j);35.if (valueObj instanceof String) {36.xssfCell.setCellValue(null == valueObj ? "" :valueObj.toString());37.} else if (valueObj instanceof BigDecimal) {38.BigDecimal vDecimal = (BigDecimal) valueObj;39.xssfCell.setCellValue(vDecimal.doubleValue());40.} else if (valueObj instanceof Integer) {41.xssfCell.setCellValue((Integer) valueObj);42.} else if (valueObj instanceof Double) {43.xssfCell.setCellValue((Double) valueObj);44.} else {45.xssfCell.setCellValue(null == valueObj ? "" :valueObj.toString());46.}47.}48.}49.try {50.xssfWorkbook.write(outputStream);51.} catch (IOException e) {52.LOG.error(e.getMessage(), e);53.} finally {54.try {55.if (null != xssfWorkbook) xssfWorkbook.close();56.} catch (IOException e) {57.LOG.error(e.getMessage(), e);58.}59.}60.}61.62.public static void writeSXSSFWorkbook(List<String> headerList,List<List<Object>> resultList, OutputStream outputStream) {63.SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();64.SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet("sheet1");65.SXSSFRow sxssfRow = sxssfSheet.createRow(0);66.for (int i = 0, len = headerList.size(); i < len; i++) {67.SXSSFCell sxssfCell = sxssfRow.createCell(i);68.sxssfCell.setCellValue(headerList.get(i));69.}70.for (int i = 0, len = resultList.size(); i < len; i++) {71.sxssfRow = sxssfSheet.createRow(i + 1);72.List<Object> result = resultList.get(i);73.for(int j = 0, jLen = result.size(); j < jLen; j++){74.SXSSFCell sxssfCell = sxssfRow.createCell(j);75.Object valueObj = result.get(j);76.if (valueObj instanceof String) {77.sxssfCell.setCellValue(null == valueObj ? "" :valueObj.toString());78.} else if (valueObj instanceof BigDecimal) {79.BigDecimal vDecimal = (BigDecimal) valueObj;80.sxssfCell.setCellValue(vDecimal.doubleValue());81.} else if (valueObj instanceof Integer) {82.sxssfCell.setCellValue((Integer) valueObj);83.} else if (valueObj instanceof Double) {84.sxssfCell.setCellValue((Double) valueObj);85.} else {86.sxssfCell.setCellValue(null == valueObj ? "" :valueObj.toString());87.}88.}89.}90.try {91.sxssfWorkbook.write(outputStream);92.} catch (Exception e) {93.LOG.error(e.getMessage(), e);94.} finally {95.sxssfWorkbook.dispose();96.try {97.if (null != sxssfWorkbook) sxssfWorkbook.close();98.} catch (IOException e) {99.LOG.error(e.getMessage(), e);100.}101.}102.}103.104.}二、基于EASYEXCEL操作EXCEL文件1.<!-- EASYEXCEL依赖包-->2.<dependency>3.<groupId>com.alibaba</groupId>4.<artifactId>easyexcel</artifactId>5.<version>2.2.3</version>6.</dependency>EXCEL文件读写操作示例如下：1.import com.alibaba.excel.EasyExcelFactory;2.import com.alibaba.excel.ExcelWriter;3.import com.alibaba.excel.support.ExcelTypeEnum;4.import com.alibaba.excel.write.metadata.WriteSheet;5.import org.slf4j.Logger;6.import org.slf4j.LoggerFactory;7.8.import java.io.InputStream;9.import java.io.OutputStream;10.import java.util.HashMap;11.import java.util.List;12.import java.util.Map;13.14.public class EasyExcelFileUtils {15.16.private static Logger LOG = LoggerFactory.getLogger(EasyExcelFileUtils.class);17.18.public static void read(InputStream in, int threshold, List<String>headerFieldList, List<Map<String, Object>> dataList) {19.Map<Integer, String> indexFieldMap = new HashMap<Integer, String>();20.EasyExcelFactory.read(in, EventListenerUtils.getListener(21.head -> {22.Map<Integer, Object> header = (Map<Integer, Object>) head;23.for (Map.Entry<Integer, Object> entry : header.entrySet()) {24.String entryValue = String.valueOf(entry.getValue());25.headerFieldList.add(entryValue);26.indexFieldMap.put(entry.getKey(), entryValue);27.}28.},29.results -> {30.List<Object> resultList = (List<Object>) results;31.for (int i = 0, len = resultList.size(); i < len; i++) {32.Map<String, Object> data = new HashMap<String,Object>();33.Map<Integer, Object> result = (Map<Integer, Object>)resultList.get(i);34.for (Map.Entry<Integer, Object> entry : result.entrySet()) {35.data.put(indexFieldMap.get(entry.getKey()),entry.getValue());36.}37.dataList.add(data);38.}39.}, threshold)).sheet(0).headRowNumber(0).autoTrim(true).doRead();40.try {41.if (null != in) in.close();42.} catch (Exception e) {43.LOG.error(e.getMessage(), e);44.}45.}46.47.public static void write(List<List<String>> headerList, List<List<Object>>resultList, OutputStream outputStream) {48.ExcelWriter excelWriter =EasyExcelFactory.write(outputStream).excelType(ExcelTypeEnum.XLSX).useDefaultStyle(tr ue)49..needHead(true).head(headerList).autoTrim(true).build();50.WriteSheet writeSheet = new WriteSheet();51.writeSheet.setSheetNo(1);52.excelWriter.write(resultList, writeSheet);53.excelWriter.finish();54.}55.56.}1.import java.util.ArrayList;2.import java.util.List;3.import java.util.function.Consumer;4.5.import com.alibaba.excel.context.AnalysisContext;6.import com.alibaba.excel.event.AnalysisEventListener;7.8.public class EventListenerUtils {9.10./**11.* 批量监听12.* @param <T>13.* @param consumer 批量消费14.* @param threshold 批量阀值15.* @return16.*/17.public static <T> AnalysisEventListener<T>getListener(Consumer<List<T>> consumer, int threshold) {18.return new AnalysisEventListener<T>() {19.20.private List<T> ts = new ArrayList<T>();21.22.@Override23.public void invoke(T t, AnalysisContext context) {24.ts.add(t);25.if (ts.size() == threshold) {26.consumer.accept(ts);27.ts.clear();28.}29.}30.31.@Override32.public void doAfterAllAnalysed(AnalysisContextcontext) {33.if (ts.size() > 0) {34.consumer.accept(ts);35.ts.clear();36.}37.}38.39.};40.}41.42./**43.* 批量监听44.* @param <T>45.* @param headConsumer 表头消费46.* @param consumer 批量消费47.* @param threshold 批量阀值48.* @return49.*/50.public static <T> AnalysisEventListener<T> getListener(Consumer<T>headConsumer, Consumer<List<T>> consumer, int threshold) {51.return new AnalysisEventListener<T>() {52.53.private List<T> ts = new ArrayList<T>();54.55.@Override56.public void invoke(T t, AnalysisContext context) {57.if(context.readRowHolder().getRowIndex() == 0) {58.headConsumer.accept(t);59.} else {60.ts.add(t);61.if (ts.size() == threshold) {62.consumer.accept(ts);63.ts.clear();64.}65.}66.}67.68.@Override69.public void doAfterAllAnalysed(AnalysisContextcontext) {70.if (ts.size() > 0) {71.consumer.accept(ts);72.ts.clear();73.}74.}75.76.};77.}78.79./**80.* 限制数量的监听81.* @param <T>82.* @param headConsumer 表头消费83.* @param consumer 消费84.* @param threshold 限制阀值85.* @return86.*/87.public static <T> AnalysisEventListener<T>getLimitListener(Consumer<T> headConsumer, Consumer<List<T>> consumer, int threshold) {88.return new AnalysisEventListener<T>() {89.90.private List<T> ts = new ArrayList<T>();91.92.@Override93.public void invoke(T t, AnalysisContext context) {94.if(context.readRowHolder().getRowIndex() == 0) {95.headConsumer.accept(t);96.} else {97.if (ts.size() < threshold) {98.ts.add(t);99.}100.}101.}102.103.@Override104.public void doAfterAllAnalysed(AnalysisContext context) {105.if (ts.size() > 0) {106.consumer.accept(ts);107.ts.clear();108.}109.}110.111.};112.}113.114.}。

题目：探索Java中POI库的Excel读写功能1. 简介Java作为一种广泛应用的编程语言，拥有丰富的库和框架来支持各种应用场景。

其中，POI（Poor Obfuscation Implementation）库是Java操作Microsoft Office格式文件的重要工具之一。

在POI库中，Excel的读写功能一直备受开发者关注，因为Excel在商业和数据处理领域有着广泛的应用。

本文将深入探讨Java中POI库的Excel读写功能，介绍其基本用法和注意事项。

2. POI库的基本概念POI库是Apache软件基金会的一个开源项目，旨在提供Java程序对Microsoft Office格式文件的读写功能。

它支持对Excel、Word和PowerPoint等文件的操作，其中Excel的读写功能是开发者们最为关注的部分。

POI库主要由HSSF（Horrible Spreadsheet Format）、XSSF（XML Spreadsheet Format）和SXSSF（Streaming Usermodel API）三个子项目组成，在具体应用时，开发者可以根据自己的需求选择合适的子项目来实现对Excel文件的读写。

3. Excel的读操作在POI库中，读取Excel文件的操作主要通过HSSFWorkbook和XSSFWorkbook来实现。

HSSFWorkbook用于操作.xls格式的文件，而XSSFWorkbook用于操作.xlsx格式的文件。

使用POI库读取Excel文件的过程包括打开Excel文件、读取工作表、遍历行和列数据等步骤。

在具体的代码实现中，开发者可以根据需求选择不同的API来完成特定的读取任务，例如使用HSSF类库来从.xls文件中读取数据，或者使用XSSF类库来从.xlsx文件中读取数据。

在读取Excel文件时，开发者需要注意文件格式、数据类型和异常处理等问题，以确保读取的准确性和健壮性。