利用POI实现简单的Excel读写操作
java 代码
- package test;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
- public class POIExcelService
- {
- private POIFSFileSystem fileSystem;
- private HSSFWorkbook workbook;
- private HSSFSheet sheet;
- private HSSFRow row;
- private HSSFCell cell;
- public POIExcelService(){}
- /**
- *
- * @param xlsPath
- */
- public POIExcelService(String xlsPath) throws Exception
- {
- this.fileSystem = new POIFSFileSystem(new FileInputStream(xlsPath));
- this.workbook = new HSSFWorkbook(fileSystem);
- }
- /**
- * 获取单元格数据,以String格式返回
- * @param sheet
- * @param row
- * @param cell
- * @return
- */
- public String getCellValue(int sheet,int row,int cell)
- {
- setSheet(sheet);
- setRow(row);
- setCell(cell);
- return this.getCell().getStringCellValue();
- }
- //导出文件
- public void Export(String xlsPath, int sheet, int row, int cell, String value) throws Exception
- {
- FileOutputStream fileOut = new FileOutputStream(xlsPath);
- this.setSheet(sheet);
- this.setRow(row);
- this.setCell(cell);
- this.getCell().setCellValue(value);
- this.workbook.write(fileOut);
- fileOut.close();
- }
- public HSSFCell getCell() {
- return cell;
- }
- public void setCell(int cell) {
- this.cell = this.row.getCell((short)(cell-1));
- }
- public HSSFRow getRow() {
- return row;
- }
- public void setRow(int row) {
- this.row = this.sheet.getRow(row-1);
- }
- public HSSFSheet getSheet() {
- return sheet;
- }
- public void setSheet(int sheet) {
- //默认是从0开始的,这里利用n-1转换下,用1开始,调用好理解点
- this.sheet = this.workbook.getSheetAt(sheet-1);
- }
- /*
- public String getCellStringValue(HSSFCell cell) {
- String cellValue = "";
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_STRING:
- cellValue = cell.getStringCellValue();
- if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
- cellValue=" ";
- break;
- case HSSFCell.CELL_TYPE_NUMERIC:
- cellValue = String.valueOf(cell.getNumericCellValue());
- break;
- case HSSFCell.CELL_TYPE_FORMULA:
- cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
- cellValue = String.valueOf(cell.getNumericCellValue());
- break;
- case HSSFCell.CELL_TYPE_BLANK:
- cellValue=" ";
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN:
- break;
- case HSSFCell.CELL_TYPE_ERROR:
- break;
- default:
- break;
- }
- return cellValue;
- }
- */
- public static void main(String[] args)
- {
- try {
- POIExcelService excelOper = new POIExcelService("产品项目导入模板.xls");
- String tmp = excelOper.getCellValue(1, 1, 1);
- System.out.println(tmp);
- excelOper.Export("产品项目导入模板_导出.xls",1,1,1,"ABCDE");
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }