- 官方介绍
- 需要的jar包
其中dom4j-1.6.1.jar和xbean.jar、并不包含在POI提供的jar包中，需要单独下载，否则程序会抛出异常：java.lang.ClassNotFoundException:org.apache.xmlbeans.XmlOptions。
- 具体代码
public static void createWorkbook() throws IOException {
Workbook wb = new HSSFWorkbook();
String safeName1 = WorkbookUtil.createSafeSheetName("[O'sheet1]");
Sheet sheet1 = wb.createSheet(safeName1);
CreationHelper createHelper = wb.getCreationHelper();
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet1.createRow((short) 0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1234);
// Or do it on one line.
row.createCell(2).setCellValue(
createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
// we style the second cell as a date (and time). It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but
// other cells.
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(
"m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
// you can also set date as java.util.Calendar
CellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setDataFormat(createHelper.createDataFormat().getFormat(
"yyyyMMdd HH:mm:ss"));
cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle1.setLeftBorderColor(IndexedColors.GREEN.getIndex());
cellStyle1.setBorderRight(CellStyle.BORDER_THIN);
cellStyle1.setRightBorderColor(IndexedColors.BLUE.getIndex());
cellStyle1.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
cellStyle1.setTopBorderColor(IndexedColors.BLACK.getIndex());
cell = row.createCell(4);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle1);
FileOutputStream fileOut = new FileOutputStream("e:/test/workbook.xls");
wb.write(fileOut);
fileOut.close();
}
读取excel文件的内容：
public static void readExcel() throws InvalidFormatException, IOException {
// Use a file
Workbook wb1 = WorkbookFactory.create(new File("e:/test/userinfo.xls"));
Sheet sheet = wb1.getSheetAt(0);
// Decide which rows to process
// int rowStart = Math.min(10, sheet.getFirstRowNum());
// int rowEnd = Math.max(40, sheet.getLastRowNum());
int rowStart = sheet.getLastRowNum();
int rowEnd = sheet.getLastRowNum() + 1;
logger.info(sheet.getFirstRowNum());
logger.info(sheet.getLastRowNum());
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row r = sheet.getRow(rowNum);
int lastColumn = Math.max(r.getLastCellNum(), 10);
logger.info(lastColumn);
// To get the contents of a cell, you first need to know what kind
// of cell it is (asking a string cell for its numeric contents will
// get you a NumberFormatException for example). So, you will want
// to switch on the cell's type, and then call the appropriate
// getter for that cell.
for (int cn = 0; cn < lastColumn; cn++) {
// Cell cell = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
Cell cell = r.getCell(cn);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
logger.info(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
logger.info(cell.getDateCellValue());
} else {
logger.info(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
logger.info(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
logger.info(cell.getCellFormula());
break;
default:
logger.info("empty");
}
}
}
}
下面给出一个具体的例子，实例中的excel文件内容如下：
public static void readUserInfo() throws InvalidFormatException,
IOException {
String[] titles = { "收费编号", "收费性质", "姓名", "家庭住址", "工作单位", "电话", "手机",
"小区楼号", "单元号", "楼层", "房间号", "建筑面积（㎡）", "面积依据", "A面积", "A超",
"A轻体", "B面积", "B超", "B轻体", "用户编号", "所属楼前表表号" };
//用来存储标题和顺序的map，key为标题，value为顺序号
Map<String, Integer> titleMap = new HashMap<String, Integer>();
//将既定顺序写入map
for (int i=0 ; i<titles.length; i++) {
titleMap.put(titles[i], i);
}
Workbook wb = WorkbookFactory.create(new File("e:/test/userinfo.xls"));
for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
Sheet xSheet = wb.getSheetAt(numSheet);
if (xSheet == null) {
continue;
}
// 获取第一行的标题内容
Row tRow = xSheet.getRow(0);
//存储标题顺序的数组
Integer[] titleSort = new Integer[tRow.getLastCellNum()];
//循环标题
for (int titleNum = 0; titleNum < tRow.getLastCellNum(); titleNum++) {
Cell tCell = tRow.getCell(titleNum);
String title = "";
if (tCell == null || "".equals(tCell)) {
} else if (tCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {// 布尔类型处理
// logger.info(xCell.getBooleanCellValue());
} else if (tCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {// 数值类型处理
title = doubleToString(tCell.getNumericCellValue());
} else {// 其他类型处理
title = tCell.getStringCellValue();
}
//通过获取的标题，从map中读取顺讯号，写入保存标题顺序号的数组
Integer ts = titleMap.get(title);
if (ts != null) {
titleSort[titleNum] = ts;
}
}
// 循环行Row
for (int rowNum = 1; rowNum < xSheet.getLastRowNum() + 1; rowNum++) {
Row xRow = xSheet.getRow(rowNum);
if (xRow == null) {
continue;
}
// 循环列Cell
String[] v = new String[titleSort.length];
for (int cellNum = 0; cellNum < titleSort.length; cellNum++) {
Cell xCell = xRow.getCell(cellNum);
String value = "";
if (xCell == null || "".equals(xCell)) {
} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {// 布尔类型处理
logger.info(xCell.getBooleanCellValue());
} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {// 数值类型处理
value = doubleToString(xCell.getNumericCellValue());
} else {// 其他类型处理
value = xCell.getStringCellValue();
}
//按照标题顺序的编号来存储每一行记录
v[titleSort[cellNum]] = value;
// logger.info("v[" + titleSort[cellNum] + "] = " + v[titleSort[cellNum]]);
}
//循环结果数组，获取的与既定顺序相同
for (int i = 0; i < v.length; i++) {
logger.info(v[i]);
}
}
}
}
上段程序中用到的工具类doubleToString（将excel中的double类型转为String类型，处理了科学计数法形式的数）：
private static String doubleToString(double d) {
String str = Double.valueOf(d).toString();
// System.out.println(str);
String result = "";
if (str.indexOf("E") > 2) {
int index = str.indexOf("E");
int power = Integer.parseInt(str.substring(index + 1));
BigDecimal value = new BigDecimal(str.substring(0, index));
value = value.movePointRight(power);
result = value.toString();
} else {
if (str.indexOf(".0") > 0)
result = str.substring(0, str.indexOf(".0"));
else
result = str;
}
return result;
}
目前对于POI的应用只限于此，并没有再深入，以后写了新的相关内容会继续补充，请大大们批评指正！