Poi读写Excel文件

来源：互联网发布：python机器人编程 ros 编辑：程序博客网时间：2024/05/17 21:44

Apache的Poi读写Excel比较强大，对xlsx和xls都能够支持，当然也支持更多的设置，下面是Poi的demo。

详细查看poi的api：http://poi.apache.org/spreadsheet/quick-guide.html

一、使用的包

maven的相关依赖坐标

  <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.10-FINAL</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.10-FINAL</version></dependency>

二、Poi的demo

package org.andy.excel;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.util.Calendar;import java.util.Date;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.DateUtil;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 org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.junit.Test;public class PoiExcelTest {public static void readExcel(String filePath) {if (null != filePath && !"".equals(filePath.trim())) {Workbook workbook = null;InputStream inputStream = null;try {inputStream = new FileInputStream(filePath);workbook = WorkbookFactory.create(inputStream); //使用WorkbookFactory创建，会安装xls或xlsxSheet sheet = workbook.getSheetAt(0);if (null != sheet) {for (Row row : sheet) {for (Cell cell : row) {switch (cell.getCellType()) {// 判断内容类型case Cell.CELL_TYPE_STRING:// string类型System.out.print(cell.getRichStringCellValue().getString());break;case Cell.CELL_TYPE_NUMERIC: //数字类型if (DateUtil.isCellDateFormatted(cell)) {//判断是否是日期System.out.print(cell.getDateCellValue());} else {System.out.print(cell.getNumericCellValue());}break;case Cell.CELL_TYPE_BOOLEAN: //布尔类型System.out.print(cell.getBooleanCellValue());break;case Cell.CELL_TYPE_FORMULA: //公式System.out.print(cell.getCellFormula());break;default:System.out.print("");}System.out.print("  ");}System.out.println();}}} catch (Exception e) {e.printStackTrace();} finally {if (null != inputStream) {try {inputStream.close();inputStream = null;} catch (Exception e) {e.printStackTrace();}}}}}public void writeExcel(String filePath) {if (null != filePath && !"".equals(filePath.trim())) {Workbook workbook = null;//根据不同的excel格式创建workbookif (filePath.trim().toLowerCase().endsWith(".xls")) {workbook = new HSSFWorkbook();} else if (filePath.trim().toLowerCase().endsWith(".xlsx")) {workbook = new XSSFWorkbook();} else {return;}OutputStream outputStream = null;try {outputStream = new FileOutputStream(filePath);CreationHelper createHelper = workbook.getCreationHelper();Sheet sheet = workbook.createSheet();Row row = sheet.createRow(0);row.createCell(0).setCellValue("is string");//string类型//设置cell的样式CellStyle cellStyle = workbook.createCellStyle();cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); Cell cell = row.createCell(1);cell.setCellStyle(cellStyle);cell.setCellValue(new Date());//Calendar row.createCell(2).setCellValue(Calendar.getInstance());//布尔类row.createCell(3).setCellValue(true);//数字类型row.createCell(4).setCellValue(200);workbook.write(outputStream);} catch (Exception e) {e.printStackTrace();} finally {if (null != outputStream) {try {outputStream.close();} catch (Exception e) {e.printStackTrace();}}}}}@Testpublic void read() {    readExcel("c:\\andy.xlsx");}@Testpublic void write() {writeExcel("c:\\andy.xlsx");}}

0 0