Java使用Apache POI操作excel文件

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();

}

// 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");

}

}

}

}

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]);

}

}

}

}

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;

}