poi 函数 读取

这一部分是写出excel

package com.sun.function;import java.io.FileOutputStream;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class TestWrite {public static void main(String[] args) {new TestWrite().funExcel();}public void funExcel() {// 创建HSSFWorkbook对象(excel的文档对象)HSSFWorkbook wkb = new HSSFWorkbook();// 建立新的sheet对象（excel的表单）HSSFSheet sheet = wkb.createSheet("成绩表");// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个//HSSFRow row1 = sheet.createRow(0);// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个//HSSFCell cell = row1.createCell(0);// 设置单元格内容//cell.setCellValue("学员考试成绩一览表");// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列//sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));for (int i = 0; i < 10; i++) {// 在sheet里创建第二行HSSFRow row2 = sheet.createRow(i);row2.createCell(0).setCellValue(i);row2.createCell(1).setCellValue("");row2.getCell(1).setCellFormula("if(A"+(i+1)+">5,\"A\",\"B\")");//row2.createCell(0).setCellValue(new Date());}FileOutputStream fos;try {fos = new FileOutputStream("e://fun.xls");wkb.write(fos);fos.close();} catch (Exception e) {e.printStackTrace();}}}

这一部分是读入excel

package com.sun.function;import java.io.FileInputStream;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellValue;import org.apache.poi.ss.usermodel.FormulaEvaluator;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;public class TestRead {public static void main(String[] args) {new TestRead().funRead();}private void funRead() {try {// 创建要读入的文件的输入流InputStream inp = new FileInputStream("E:\\fun.xls");// 根据上述创建的输入流 创建工作簿对象Workbook wb = WorkbookFactory.create(inp);// 得到第一页 sheet// 页Sheet是从0开始索引的Sheet sheet = wb.getSheetAt(0);int rowNum = sheet.getPhysicalNumberOfRows();for (int i = 0; i < rowNum; i++) {Row row = sheet.getRow(i);int colNum = row.getPhysicalNumberOfCells();List<Object> stuList = new ArrayList<Object>();for (int j = 0; j < colNum; j++) {Cell cell = row.getCell(j);System.out.print(getCellFormatValue(cell)+" ");}System.out.println("");}// 关闭输入流inp.close();} catch (Exception e) {e.printStackTrace();}}/** *  * @param cell Excel中单元格的数据 * @return 根据不同的类型 转换为 字符串 */private  String getCellFormatValue(Cell cell) {FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();String cellvalue = "";if (cell != null) {// 判断当前Cell的Typeswitch (cell.getCellType()) {case HSSFCell.CELL_TYPE_NUMERIC://数字类型cellvalue = String.valueOf(cell.getNumericCellValue());if (HSSFDateUtil.isCellDateFormatted(cell)) {//判断是否为日期类型SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());cellvalue = sdf.format(date).toString();}break;case HSSFCell.CELL_TYPE_BOOLEAN://boolean 类型cellvalue = String.valueOf(cell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_BLANK://空值cellvalue = "";break;        case Cell.CELL_TYPE_FORMULA: //公式            CellValue cellValue = evaluator.evaluate(cell);       switch (cellValue.getCellType()) {           case Cell.CELL_TYPE_BOOLEAN:               System.out.println(cellValue.getBooleanValue());               break;           case Cell.CELL_TYPE_NUMERIC:               System.out.println(cellValue.getNumberValue());               break;           case Cell.CELL_TYPE_STRING:               System.out.println(cellValue.getStringValue());               break;           case Cell.CELL_TYPE_BLANK:               break;           case Cell.CELL_TYPE_ERROR:               break;           // CELL_TYPE_FORMULA will never happen           case Cell.CELL_TYPE_FORMULA:                break;       }break;case HSSFCell.CELL_TYPE_STRING:// 如果当前Cell的Type为String// 取得当前的Cell字符串cellvalue = cell.getRichStringCellValue().getString();break;// 默认的Cell值default:cellvalue = " ";}}else{cellvalue = "";}return cellvalue;}}