使用POI读写Excel文件
package com.jadyer.demo; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; 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; /** * POI下载地址为http://poi.apache.org/ * 这里要用到poi-3.9-20121203.jar和poi-ooxml-3.9-20121203.jar * @create Jul 9, 2013 7:54:34 PM * @author 玄玉<http://blog.csdn.net/jadyer> */ public class POIDemo { public static void writeExcel() throws IOException{ //创建一个Excel(or new XSSFWorkbook()) Workbook wb = new HSSFWorkbook(); //创建表格 Sheet sheet = wb.createSheet("测试Sheet_01"); //创建行 Row row = sheet.createRow(0); //设置行高 row.setHeightInPoints(30); //创建样式 CellStyle cs = wb.createCellStyle(); cs.setAlignment(CellStyle.ALIGN_CENTER); cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cs.setBorderBottom(CellStyle.BORDER_DOTTED); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); //创建单元格 Cell cell = row.createCell(0); //设置单元格样式 cell.setCellStyle(cs); //设置单元格的值 cell.setCellValue("序号"); cell = row.createCell(1); cell.setCellStyle(cs); cell.setCellValue("用户"); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue("1"); cell = row.createCell(1); cell.setCellValue("张起灵"); FileOutputStream fos = new FileOutputStream("D:/测试的Excel.xls"); wb.write(fos); if(null != fos){ fos.close(); } } public static void readExcel() throws InvalidFormatException, IOException{ //老版本POI是使用这种方式创建Workbook的,新版本中可以使用WorkbookFactory,它能自动根据文档的类型打开一个Excel //Workbook wb = new HSSFWorkbook(new FileInputStream("D:/5月业务定制对账文件汇总.xls")); Workbook wb = WorkbookFactory.create(new File("D:/5月业务定制对账文件汇总.xls")); //获取Excel中的某一个数据表..也可以通过Sheet名称来获取,即Workbook.getSheet("定制对账文件") Sheet sheet = wb.getSheetAt(0); Row row = null; Cell cell = null; //获取Excel的总行数:Sheet.getLastRowNum()+1(需要+1) for(/*int i=0*/ int i=sheet.getFirstRowNum(); i<sheet.getLastRowNum()+1; i++){ //获取数据表里面的某一行 row = sheet.getRow(i); //获取Excel的总列数:Row.getLastCellNum()(不用+1) for(/*int j=0*/ int j=row.getFirstCellNum(); j<row.getLastCellNum(); j++){ //获取一行中的一个单元格 cell = row.getCell(j); System.out.print(getCellValue(cell) + "----"); } //打印完一行的数据之后,再输入一个空行 System.out.println(); } } /** * 使用for-each循环来读取Excel */ public static void readExcelUseForeach() throws InvalidFormatException, IOException { for(Row row : WorkbookFactory.create(new File("D:/5月业务定制对账文件汇总.xls")).getSheetAt(0)){ for(Cell cell : row){ System.out.print(getCellValue(cell) + "----"); } System.out.println(); } } /** * 获取单元格内的数据值 */ private static String getCellValue(Cell cell){ String str = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: str = ""; break; case Cell.CELL_TYPE_BOOLEAN: str = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: str = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: str = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: str = cell.getStringCellValue(); break; default: str = null; break; } return str; } }
补充:软件开发 , Java ,