这周公司要用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-schemasor ooxml-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 | commons-logging, commons-codec, log4j | poi-version-yyyymmdd.jar |
| poi-scratchpad | poi | poi-scratchpad-version-yyyymmdd.jar |
| poi-ooxml | poi, poi-ooxml-schemas | poi-ooxml-version-yyyymmdd.jar |
| poi-ooxml-schemas | xmlbeans | poi-ooxml-schemas-version-yyyymmdd.jar |
| poi-examples | poi, poi-scratchpad, poi-ooxml | poi-examples-version-yyyymmdd.jar |
| ooxml-schemas | xmlbeans | ooxml-schemas-1.3.jar |
| ooxml-security |
xmlbeans For signing: bcpkix-jdk15on, bcprov-jdk15on, xmlsec, slf4j-api |
ooxml-security-1.1.jar |
综上可知：我们操作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<Integer> notReadList = new ArrayList<Integer>();
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<Object[]> readListObject(String sheetName, int startRowNum,
int endRowNum, int startCellNum, int endCellNum, int... notReadNum) {
List<Object[]> oList = new ArrayList<Object[]>();
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<Object[]> readListObject(int sheetNum, int startRowNum,
int endRowNum, int startCellNum, int endCellNum, int... notReadNum) {
List<Object[]> oList = new ArrayList<Object[]>();
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<String, List<Object[]>> readAllExcel() {
Map<String, List<Object[]>> map = new HashMap<String, List<Object[]>>();
int sheetNum = wb.getNumberOfSheets();
for (int i = 0; i < sheetNum; i++) {
String sheetName = wb.getSheetName(i);
List<Object[]> 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<Object[]> 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<Object[]> 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<String, List> readAllExcel(Class clazz) {
Map<String, List<Object[]>> oMap = readAllExcel();
Map<String, List> map = new HashMap<String, List>();
for (Entry<String, List<Object[]>> entry : oMap.entrySet()) {
String key = entry.getKey();
List<Object[]> 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!"); }
}