这周公司要用excel作为数据存储格式做一个文具申请的功能，感觉以前本来很简单的功能变复杂了不少，但是还是记录一下一些excel的基本用法。
写在最前面：这里只介绍一些excel的基本存储方式（读，写，数据和样式），高级用法并不会涉及。
首先是需要引入的jar包，如下表所示：（以下内容来自于Apache POI的官方文档）
Apache POI可以运用在许多文档文件的格式中。这些对文档操作的支持需要一些jar文件。不是所有的jar文件都被需要于每一个格式中。下面的表格列出了在POI部件中的关系，maven仓库的tags，和项目需要的jar文件。
| 组件 | 应用类型 | Maven artifactId | 注意 |
|---|---|---|---|
| POIFS | OLE2 Filesystem | poi | Required to work with OLE2 / POIFS based files |
| HPSF | OLE2 Property Sets | poi | |
| HSSF | Excel XLS | poi | For HSSF only, if common SS is needed see below |
| HSLF | PowerPoint PPT | poi-scratchpad | |
| HWPF | Word DOC | poi-scratchpad | |
| HDGF | Visio VSD | poi-scratchpad | |
| HPBF | Publisher PUB | poi-scratchpad | |
| HSMF | Outlook MSG | poi-scratchpad | |
| DDF | Escher common drawings | poi | |
| HWMF | WMF drawings | poi-scratchpad | |
| OpenXML4J | OOXML | poi-ooxml plus either poi-ooxml-schemasorooxml-schemas and ooxml-security | See notes below for differences between these options |
| XSSF | Excel XLSX | poi-ooxml | |
| XSLF | PowerPoint PPTX | poi-ooxml | |
| XWPF | Word DOCX | poi-ooxml | |
| Common SL | PowerPoint PPT and PPTX | poi-scratchpad and poi-ooxml | SL code is in the core POI jar, but implementations are in poi-scratchpad and poi-ooxml. |
| Common SS | Excel XLS and XLSX | poi-ooxml | WorkbookFactory and friends all require poi-ooxml, not just core poi |
下面是maven仓库所需要的jar包：
| Maven artifactId | 预先准备 | JAR |
|---|---|---|
| poi | , , | poi-version-yyyymmdd.jar |
| poi-scratchpad | poi-scratchpad-version-yyyymmdd.jar | |
| poi-ooxml | , | poi-ooxml-version-yyyymmdd.jar |
| poi-ooxml-schemas | poi-ooxml-schemas-version-yyyymmdd.jar | |
| poi-examples | , , | poi-examples-version-yyyymmdd.jar |
| ooxml-schemas | ||
| ooxml-security | For signing: , , , |
综上可知：我们操作excel所需要poi，poi-ooxml和poi-ooxml-shemas这三类，请自行配置
tips：excel分为xls和xlsx，第一类是基于binary的标准，第二种是基于xml的规范，所以用不同的类进行操作
- 新建一个excel文件：
/** * 新建一个excel 2003的文件，在项目的根目录下 */ public void createHSSFWorkBooksTest() { try { Workbook wb = new HSSFWorkbook(); //这里新建了一个exccel 2003的文件，所以 //Workbook wb = new XSSFWorkbook(); 这里是一个excel2007的文件，相应的输出流后缀应该是xlsx FileOutputStream fos = new FileOutputStream("workbook.xls"); wb.write(fos); fos.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } - 新建一个excel并且里面有2个sheet，一个叫做sheet1，一个叫做sheet2
/** * 新建一个excel 2003的文件，在项目的根目录下 */ public void createHSSFWorkBooksTest() { try { Workbook wb = new HSSFWorkbook(); //这里新建了一个exccel 2003的文件，所以 //Workbook wb = new XSSFWorkbook(); 这里是一个excel2007的文件，相应的输出流后缀应该是xlsx FileOutputStream fos = new FileOutputStream("workbook.xls"); wb.write(fos); fos.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } -
在单元格里面赋值，值为1
tips：在单元格里面可以添加所有基本类型，以及公式，但是公式操作这里不做介绍
/** * 在第二行的第一个单元格里面赋值，值为1 * @throws Exception */ public void testCreateCell() throws Exception { Workbook wb = new HSSFWorkbook(); CreationHelper helper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new Sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(0); cell.setCellValue(1); FileOutputStream fos = new FileOutputStream("workbook.xls"); wb.write(fos); fos.close(); } - 在单元格里面添加日期类型
/** * 在单元格里面添加日期类型的值 * @throws Exception */ public void testCreateDateCell() throws Exception { //新建一个excel2003的workbook Workbook wb = new HSSFWorkbook(); //新建一个单元格 Sheet sheet = wb.createSheet(); CreationHelper helper = wb.getCreationHelper(); //新建一行，且是第一行，java中的行是从0开始计算的 Row row = sheet.createRow(0); //在第一行新建一个单元格 Cell cell = row.createCell(0); //在这里赋一个没有转换格式的日期类型 cell.setCellValue(new Date()); //通过workbook获得一个cellstyle CellStyle style = wb.createCellStyle(); //进行日期类型的格式转换 style.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm")); //新建一个单元格，单元格的位置是第一行第二列 cell = row.createCell(1); //赋值，变量为日期类型 cell.setCellValue(new Date()); //该单元格的style为上面的那种 cell.setCellStyle(style); cell = row.createCell(2); //第二种获得日期的方法，通过调用java的canlendar cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(style); FileOutputStream fos = new FileOutputStream("workbook.xls"); wb.write(fos); fos.close(); }
- 设置单元格里面的字体颜色
/** * 设置不同的颜色显示 * @throws Exception */ public void testCreateDifferentCell() throws Exception { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow(2); CellStyle style = wb.createCellStyle(); //获取一个font Font font = wb.createFont(); //设置font的颜色为红色 font.setColor(Font.COLOR_RED); style.setFont(font); row.createCell(0).setCellValue(1.2); row.getCell(0).setCellStyle(style); row.createCell(1).setCellValue(1); row.createCell(2).setCellValue(true); FileOutputStream fos = new FileOutputStream("workbook.xls"); wb.write(fos); fos.close(); }
- 读取已经有的excel有两种方式：File和InputStream，但是在poi3.5之前不能用file的方式去读
/** * 读取已经存在excel的两种方式 * File && InputStream * * @throws Exception */ public void testCreateExcelByInputStreamAndFile() throws Exception { // Workbook wb = new HSSFWorkbook(); Workbook wb = WorkbookFactory.create(new File("workbook.xls")); //Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls")); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow(2); CellStyle style = wb.createCellStyle(); // style.setFont(wb.createFont().setColor(Font.COLOR_RED)); Font font = wb.createFont(); font.setColor(Font.COLOR_RED); style.setFont(font); row.createCell(0).setCellValue(1.2); row.getCell(0).setCellStyle(style); row.createCell(1).setCellValue(1); row.createCell(2).setCellValue(true); wb.close(); // FileOutputStream fos = new FileOutputStream("workbook.xls"); // wb.write(fos); // fos.close(); }
- 循环excel里面所有的元素，默认的迭代方式，不用迭代器，但是在poi3.5之前都不支持这样的读取方式
/** * 循环excel里面所有的元素，默认的迭代方式，不用迭代器，但是在poi3.5之前都不支持这样的读取方式 * @throws Exception */ public void testIterateAllExcel() throws Exception { Workbook wb = WorkbookFactory.create(new File("iterate.xls")); for (Sheet sheet : wb) { for (Row row : sheet) { for (Cell cell : row) { if (cell == null) { System.out.println("the cell is null!"); } else { System.out.println(cell.getStringCellValue()); } } } } wb.close(); } - 通过获取cell内容的类型来调用相应的方法获取cell里面的值
/** * 在excel的单元格内容有各式各样的形式，所以读取之前要先判断读取内容的类型，然后才能用相应的方式读取出来 * @throws Exception */ public void testGetExcelContent() throws Exception { Workbook wb = WorkbookFactory.create(new File("iterate.xls")); Sheet sheet = wb.getSheetAt(0); for (Row row : sheet) { for (int i = 0; i < row.getLastCellNum(); i++) { //单元格的内容如果是空则返回null Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL); if (cell == null) { System.out.println("the cell is null!"); continue; } //通过getCellType方法判断单元格里面的内容 switch (cell.getCellType()) { //获取的内容为string类型 case Cell.CELL_TYPE_STRING: System.out.println("the type is String:" + cell.getRichStringCellValue().getString()); break; //获取的内容为数字类型（包括整型，浮点...） case Cell.CELL_TYPE_NUMERIC: System.out.println("the type is numeric:" + cell.getNumericCellValue()); break; //获取的内容为布尔类型 case Cell.CELL_TYPE_BOOLEAN: System.out.println("the type is boolean:" + cell.getBooleanCellValue()); break; //获取的内容为公式类型 case Cell.CELL_TYPE_FORMULA: System.out.println("the type is formula:" + cell.getCellFormula()); break; //获取的内容为black case Cell.CELL_TYPE_BLANK: System.out.println("the type is null:" + cell); break; default: System.out.println("-"); break; } } } wb.close(); } - 读和重新写入
/** * 读和重新写入，就是输入流和输出流都对同一个文件做操作 * @throws Exception */ public void testExcelFont() throws Exception { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short) 1); Cell cell = row.createCell((short) 1); cell.setCellValue("This is testing merge"); Font font = wb.createFont(); font.setColor(IndexedColors.BLUE.getIndex()); CellStyle style = wb.createCellStyle(); style.setFont(font); cell.setCellStyle(style); FileOutputStream fos = new FileOutputStream("workbook.xls"); wb.write(fos); wb.close(); fos.close(); } ------------------------------不知疲倦的翻越每一个山丘，越过山丘，虽然已白了头，更新于2016/09/29-------------------------------------------
这里自己写了一个类，在构造函数时传入excel的地址，就可以通过里面的方法读取指定行，指定列，以及一个sheet中的全部内容，或者读取整个excel，并且在javabean的属性和excel对应列高度匹配时，可以调用方法直接转换成bean。
附代码：
package com.rms.apply.util;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Array;import java.lang.reflect.Field;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.HashMap;import java.util.List;import java.util.Locale;import java.util.Map;import java.util.Map.Entry;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import com.rms.entity.Server;public class POIExcelUtilHelper { private String excelPath; private Workbook wb = null; private InputStream inp = null; /** * the constructor method * * @param excelPath * the excel path like D://xxx.xlsx */ public POIExcelUtilHelper(String excelPath) { this.excelPath = excelPath; try { inp = new FileInputStream(excelPath); wb = WorkbookFactory.create(inp); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * read one line in excel * * @author mingzheng * @param sheet * the sheet * @param rowNum * the row line which you should read * @param startCellNum * the start column which you begin to read * @param endCellNum * the end of the column which you end to read(if the value is -1 * means read the line to the end of column) * @param notReadNum * the columns which you don't want to read * @return an array of object */ private Object[] readOneline(Sheet sheet, int rowNum, int startCellNum, int endCellNum, int... notReadNum) { List notReadList = new ArrayList (); for (int i = 0; i < notReadNum.length; i++) { notReadList.add(notReadNum[i]); } Row row = sheet.getRow(rowNum); if (row == null) { return null; } if (endCellNum == -1) { endCellNum = row.getLastCellNum(); } else { endCellNum++; } int objectNum = endCellNum - startCellNum - notReadNum.length; Object[] object = new Object[objectNum]; int j = 0; for (int i = startCellNum; i < endCellNum; i++) { if (notReadList.contains(i)) { System.out.println("the continue is:" + i); continue; } Cell cell = row.getCell(i); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy", Locale.ENGLISH); object[j] = sdf.format(HSSFDateUtil.getJavaDate(cell .getNumericCellValue())); } else { object[j] = cell.getNumericCellValue(); } } else if (cell.getCellType() == cell.CELL_TYPE_STRING) { object[j] = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { object[j] = cell.getNumericCellValue(); } catch (IllegalStateException e) { try { object[j] = cell.getStringCellValue(); } catch (IllegalStateException e1) { object[j] = "wrong!"; } } } else { object[j] = ""; } j++; } return object; } /** * enter the sheet num and read the row and column which you want to read. * * @author mingzheng * @param sheetNum * the sheet num (begin with 0) * @param rowNum * the row line which you should read * @param startCellNum * the start column which you begin to read * @param endCellNum * the end of the column which you end to read(if the value is -1 * means read the line to the end of column) * @param notReadNum * the columns which you don't want to read * @return an array of object */ public Object[] readOneObject(int sheetNum, int rowNum, int startCellNum, int endCellNum, int... notReadNum) { Sheet sheet = wb.getSheetAt(sheetNum); Object[] objects = readOneline(sheet, rowNum, startCellNum, endCellNum, notReadNum); return objects; } /** * enter the sheet name and read the row and column which you want to read. * * @author mingzheng * @param sheetNum * the sheet name * @param rowNum * the row line which you should read * @param startCellNum * the start column which you begin to read * @param endCellNum * the end of the column which you end to read(if the value is -1 * means read the line to the end of column) * @param notReadNum * the columns which you don't want to read * @return an array of object */ public Object[] readOneObject(String sheetName, int rowNum, int startCellNum, int endCellNum, int... notReadNum) { Sheet sheet = wb.getSheet(sheetName); Object[] objects = readOneline(sheet, rowNum, startCellNum, endCellNum, notReadNum); return objects; } /** * 读取excel里面指定行和指定列，并且返回一个list包装的object数组 * * @param sheetName * sheet的名字 * @param startRowNum * 开始的行号 * @param endRowNum * 结束的行号 * @param startCellNum * 开始的列号 * @param endCellNum * 结束的列号 * @param notReadNum * 在开始和结束列之间不用读的列号 * @return list包装的object数组 */ public List readListObject(String sheetName, int startRowNum, int endRowNum, int startCellNum, int endCellNum, int... notReadNum) { List oList = new ArrayList (); Sheet sheet = wb.getSheet(sheetName); if (endRowNum == -1) { endRowNum = sheet.getLastRowNum() + 1; } else { endRowNum++; } for (int i = startRowNum; i < endRowNum + 1; i++) { Object[] objects = readOneline(sheet, i, startCellNum, endCellNum, notReadNum); oList.add(objects); } return oList; } /** * 读一个excel的指定行和指定列，并且返回一个list包装的object数组 * * @param sheetNum * sheet的位置号 * @param startRowNum * 开始的行号 * @param endRowNum * 结束的行号 * @param startCellNum * 开始的列号 * @param endCellNum * 结束的列号 * @param notReadNum * 在开始行和结束行之间不用读的行号 * @return list包装的object数组 */ public List readListObject(int sheetNum, int startRowNum, int endRowNum, int startCellNum, int endCellNum, int... notReadNum) { List oList = new ArrayList (); Sheet sheet = wb.getSheetAt(sheetNum); if (endRowNum == -1) { endRowNum = sheet.getLastRowNum() + 1; } else { endRowNum++; } for (int i = startRowNum; i < endRowNum; i++) { Object[] objects = readOneline(sheet, i, startCellNum, endCellNum, notReadNum); oList.add(objects); } return oList; } /** * 读整个excel * * @return 返回一个map，key是sheet的名字，value是每一个sheet对应的list */ public Map > readAllExcel() { Map > map = new HashMap >(); int sheetNum = wb.getNumberOfSheets(); for (int i = 0; i < sheetNum; i++) { String sheetName = wb.getSheetName(i); List oList = readListObject(i, 0, -1, 0, -1); map.put(sheetName, oList); } return map; } public Object transferArrayToObject(Class clazz, Object[] objects) { try { Object object = clazz.newInstance(); Field[] fields = clazz.getDeclaredFields(); for (int i = 0; i < objects.length; i++) { Field field = fields[i]; field.setAccessible(true); if ((field.getType() == int.class || field.getType() == Integer.class) && (objects[i] instanceof Double)) { System.out.println(objects[i]); Double tempValue = (Double) objects[i]; objects[i] = tempValue.intValue(); } field.set(object, objects[i]); } return object; } catch (InstantiationException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } } public Object readOneObjectToBean(Class clazz, int sheetNum, int rowNum, int startCellNum, int endCellNum, int... notReadNum) { Object[] objectArray = readOneObject(sheetNum, rowNum, startCellNum, endCellNum, notReadNum); return transferArrayToObject(clazz, objectArray); } public Object readOneObjectToBean(Class clazz, String sheetName, int rowNum, int startCellNum, int endCellNum, int... notReadNum) { Object[] objectArray = readOneObject(sheetName, rowNum, startCellNum, endCellNum, notReadNum); return transferArrayToObject(clazz, objectArray); } public List readListObjectToBean(Class clazz, int sheetNum, int startRowNum, int endRowNum, int startCellNum, int endCellNum, int... notReadNum) { List oList = readListObject(sheetNum, startCellNum, endRowNum, startCellNum, endCellNum, notReadNum); List list = new ArrayList(); for (Object[] objectArray : oList) { Object object = transferArrayToObject(clazz, objectArray); list.add(object); } return list; } public List readListObjectToBean(Class clazz, String sheetName, int startRowNum, int endRowNum, int startCellNum, int endCellNum, int... notReadNum) { List oList = readListObject(sheetName, startCellNum, endRowNum, startCellNum, endCellNum, notReadNum); List list = new ArrayList(); for (Object[] objectArray : oList) { Object object = transferArrayToObject(clazz, objectArray); list.add(object); } return list; } public Map readAllExcel(Class clazz) { Map > oMap = readAllExcel(); Map map = new HashMap (); for (Entry > entry : oMap.entrySet()) { String key = entry.getKey(); List oList = entry.getValue(); List list = new ArrayList(); for (Object[] objectArray : oList) { Object o = transferArrayToObject(clazz, objectArray); list.add(o); } map.put(key, list); } return map; } public static void main(String[] args) throws InvalidFormatException, IOException { POIExcelUtilHelper helper = new POIExcelUtilHelper( "D://CR Tracking List_Order_SPEAR v4 final.xlsx"); Object[] list = helper.readOneObject("CR List", 2, 0, 41, 31, 32); System.out.println(Arrays.toString(list)); System.out.println(list.length); System.out.println("read the excel end!"); }}