demo · 归档

package cn.study.excel;

import jdk.nashorn.internal.runtime.regexp.joni.exception.ValueException; 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.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.annotation.processing.FilerException; import javax.swing.; import java.awt.; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.io.*;

public class ExcelIOTest_2 { public static void main(String[] args) { JFrame jFrame = new Study(); }

}

class Study extends JFrame implements ActionListener { Study() { setVisible(true); setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE); setSize(500, 300); setTitle("测试ExcelIO程序"); Container container = getContentPane(); JButton start = new JButton("启动!"); container.add(start, BorderLayout.SOUTH); start.addActionListener(this);

  JButton read = new JButton("读取!");
  container.add(read, BorderLayout.NORTH);
  read.addActionListener(this);

}

@Override public void actionPerformed(ActionEvent e) { File file = new File("F://TestExcelIO//sushe11.xlsx"); switch (e.getActionCommand()) { case "启动!": System.out.println("启动按钮按下!"); ExcelOutput excelOutput = new ExcelOutput(file); break; case "读取!": System.out.println("读取按钮被按下!"); ExcelInput excelInput = new ExcelInput(file); } } }

class ExcelOutput { ExcelOutput(File file) { int Row, Component; System.out.println("================成功调用ExcelOutput类!================"); HSSFWorkbook newWorkbook = new HSSFWorkbook(); HSSFSheet newSheet = newWorkbook.createSheet(); HSSFRow newRow = newSheet.createRow(Row = (Integer.valueOf(JOptionPane.showInputDialog(null, "Row")))); HSSFCell newCell = newRow.createCell(Component = (Integer.valueOf(JOptionPane.showInputDialog(null, "Cell")))); newCell.setCellValue("R:" + Row + " C:" + Component); System.out.println(newCell.getStringCellValue()); JOptionPane.showMessageDialog(null, "Row: " + newCell.getRowIndex() + "\nColumn: " + newCell.getColumnIndex()); try {//写入文件 // File file = new File("F://TestExcelIO//MyFirstExcel.xls"); if (file.exists()) {//如果已存在则询问是否覆盖 if (ExcelTool.really("覆盖" + file)) { file.delete(); file.createNewFile(); } } else { file.createNewFile(); } OutputStream newfile = new FileOutputStream(file); newWorkbook.write(newfile);

      newWorkbook.close();
      newfile.close();

  } catch (Exception e) {
      System.out.println(e.getMessage());
  }

}

class ExcelInput { ExcelInput(File file) { System.out.println("================方法ExcelInput调用成功!================");

  WorkbookTool workbookTool = new WorkbookTool();
  ExcelCellInputStream inputStream = new ExcelCellInputStream(workbookTool.getWorkbook(file));
  try {

      String date[][]=inputStream.readStringLine(1, 3, 11, 17, 11);
      for (int i=0;i< date.length;i++){
          for (int j =0;j<date[i].length;j++){
              System.out.print(date[i][j]+"\t\t");
          }
          System.out.println();
      }


  } catch (Exception e) {
      System.out.println("在ExcelInput["+e.getClass()+"]捕捉到错误:" + e.getCause() + e.getMessage());
  }
  finally{
      workbookTool.close();
  }

}

class ExcelCellInputStream{ Workbook workbook; Sheet sheet; Row row; Cell cell; int numberOfSheets; int numberOfRow; int numberOfCell; int sheetIndex,rowIndex,columnIndex; ExcelCellInputStream(Workbook workbook){ this.workbook=workbook; numberOfSheets = workbook.getNumberOfSheets(); }

String readString(int sheetIndex,int rowIndex,int columnIndex){ this.sheetIndex=sheetIndex;this.rowIndex=rowIndex;this.columnIndex=columnIndex; try { // System.out.println("numberOfSheets = " + numberOfSheets);//排错 if (sheetIndex<0||sheetIndex>numberOfSheets){ throw new ValueException("未找到您想要找的sheet编号:["+sheetIndex+"]"); } sheet = workbook.getSheetAt(sheetIndex-1); numberOfRow = sheet.getLastRowNum()+1;//修复了错误报到达Row边界的问题这里不知道怎么回事但是返回的数组确实比真实的行数少了一行 // System.out.println("numberOfRow = " + numberOfRow);//排错 if (rowIndex<0||rowIndex>numberOfRow){ throw new ValueException("未找到您想要找的row编号:["+rowIndex+"]"); } row = sheet.getRow(rowIndex-1); numberOfCell = row.getLastCellNum(); // System.out.println(numberOfCell);//排错 if (columnIndex<0||columnIndex>numberOfCell){ throw new ValueException("未找到您想要找的column编号:["+columnIndex+"]"); } cell = row.getCell(columnIndex-1); cell.setCellType(CellType.STRING);

      return cell.getStringCellValue();
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return null;
  }

} double readNumber(int sheetIndex,int rowIndex,int columnIndex){ this.sheetIndex=sheetIndex;this.rowIndex=rowIndex;this.columnIndex=columnIndex; try { if (sheetIndex<0||sheetIndex>numberOfSheets){ throw new ValueException("未找到您想要找的sheet编号:["+sheetIndex+"]"); } sheet = workbook.getSheetAt(sheetIndex-1); numberOfRow = sheet.getLastRowNum(); if (rowIndex<0||rowIndex>numberOfRow){ throw new ValueException("未找到您想要找的row编号:["+rowIndex+"]"); } row = sheet.getRow(rowIndex-1); numberOfCell = row.getLastCellNum(); if (columnIndex<0||columnIndex>numberOfCell){ throw new ValueException("未找到您想要找的column编号:["+columnIndex+"]"); } cell = row.getCell(columnIndex-1); cell.setCellType(CellType.NUMERIC);

      return cell.getNumericCellValue();
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return Integer.MIN_VALUE;
  }

}

String[][][] readStringLine(int startSheetIndex,int endSheetIndex,int startRow,int startColumn,int endRow,int endColumn){ String[][][] data= new String[endSheetIndex-startSheetIndex+1][endRow-startRow+1][endColumn-startColumn+1]; for (int i=0;i<endSheetIndex-startSheetIndex+1;i++){ for (int j=0;j<endRow-startRow+1;j++){ for (int k=0;k<endColumn-startColumn+1;k++){ data[i][j][k] = readString(startSheetIndex+i,startRow+j, startColumn+k); } } } return data; } String[][] readStringLine(int sheetIndex,int startRow,int startColumn,int endRow,int endColumn){ String[][][] data= readStringLine(sheetIndex,sheetIndex,startRow,startColumn,endRow,endColumn); String[][] cleanData = data[0]; return cleanData; } } class ExcelCellOutputStream{ Workbook workbook; Sheet sheet; Row row; Cell cell; int numberOfSheets; int numberOfRow; int numberOfCell; int sheetIndex,rowIndex,columnIndex;

ExcelCellOutputStream(Workbook workbook){ this.workbook=workbook; numberOfSheets = workbook.getNumberOfSheets(); }

boolean writeString(String string,int sheetIndex,int rowIndex,int columnIndex){ this.sheetIndex=sheetIndex;this.rowIndex=rowIndex;this.columnIndex=columnIndex; try { if (sheetIndex<0||sheetIndex>numberOfSheets){ throw new ValueException("未找到您想要找的sheet编号:["+sheetIndex+"]"); } sheet = workbook.getSheetAt(sheetIndex-1); numberOfRow = sheet.getLastRowNum(); if (rowIndex<0){ throw new ValueException("您输入的row编号为:["+rowIndex+"],"+"错误,应>0!"); } row = sheet.getRow(rowIndex-1); numberOfCell = row.getLastCellNum(); if (columnIndex<0){ throw new ValueException("您输入的column编号为:["+columnIndex+"],"+"错误,应>0!"); } cell = row.getCell(columnIndex-1); cell.setCellType(CellType.STRING); cell.setCellValue(string);

      return true;
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return false;
  }

} boolean writeNumber(double doubleValue,int sheetIndex,int rowIndex,int columnIndex){ this.sheetIndex=sheetIndex;this.rowIndex=rowIndex;this.columnIndex=columnIndex; try { if (sheetIndex<0||sheetIndex>numberOfSheets){ throw new ValueException("未找到您想要找的sheet编号:["+sheetIndex+"]"); } sheet = workbook.getSheetAt(sheetIndex-1); numberOfRow = sheet.getLastRowNum(); if (rowIndex<0){ throw new ValueException("您输入的row编号为:["+rowIndex+"],"+"错误,应>0!"); } row = sheet.getRow(rowIndex-1); numberOfCell = row.getLastCellNum(); if (columnIndex<0){ throw new ValueException("您输入的column编号为:["+columnIndex+"],"+"错误,应>0!"); } cell = row.getCell(columnIndex-1); cell.setCellType(CellType.NUMERIC); cell.setCellValue(doubleValue);

      return true;
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return false;
  }

}

class WorkbookTool { private Workbook workbook; private InputStream in; Workbook getWorkbook(File file){ try { if (!file.exists()) { JOptionPane.showMessageDialog(null, "文件" + file.getName() + "不存在!"); throw new FileNotFoundException("文件" + file.getName() + "不存在!"); } else if(!file.isFile()){ JOptionPane.showMessageDialog(null, file.getName()+"不是文件!"); throw new FilerException(file.getName()+"不是文件!"); } in = new FileInputStream(file); String fileName = file.getName(); String nameLast = fileName.substring(fileName.lastIndexOf(".")); if (nameLast.equals(".xls")){ workbook = new HSSFWorkbook(in); return workbook; } else if (nameLast.equals(".xlsx")){ workbook = new XSSFWorkbook(in); return workbook; } else { System.out.println("获取workbook时发生错误,未知的扩展名"); return null; } } catch (FilerException e){ System.out.println("获取文件流是发生错误:"+e.getClass()+e.getCause()+e.getMessage()); } catch (Exception e){ System.out.println("在GetWorkbook中捕获到其他错误:"+e.getClass()+e.getCause()+e.getMessage()); } return null; }

void close(){ try { workbook.close(); in.close(); } catch (Exception e){ System.out.println("关闭流时发生错误:"+e.getClass()+e.getCause()+e.getMessage()); } } }package cn.study.excel;

import javax.annotation.processing.FilerException; import javax.swing.; import java.awt.; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.io.*;

public class ExcelIOTest_2 { public static void main(String[] args) { JFrame jFrame = new Study(); }

}

  JButton read = new JButton("读取!");
  container.add(read, BorderLayout.NORTH);
  read.addActionListener(this);

}

      newWorkbook.close();
      newfile.close();

  } catch (Exception e) {
      System.out.println(e.getMessage());
  }

}

class ExcelInput { ExcelInput(File file) { System.out.println("================方法ExcelInput调用成功!================");

  WorkbookTool workbookTool = new WorkbookTool();
  ExcelCellInputStream inputStream = new ExcelCellInputStream(workbookTool.getWorkbook(file));
  try {

      String date[][]=inputStream.readStringLine(1, 3, 11, 17, 11);
      for (int i=0;i< date.length;i++){
          for (int j =0;j<date[i].length;j++){
              System.out.print(date[i][j]+"\t\t");
          }
          System.out.println();
      }


  } catch (Exception e) {
      System.out.println("在ExcelInput["+e.getClass()+"]捕捉到错误:" + e.getCause() + e.getMessage());
  }
  finally{
      workbookTool.close();
  }

}

      return cell.getStringCellValue();
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return null;
  }

      return cell.getNumericCellValue();
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return Integer.MIN_VALUE;
  }

}

ExcelCellOutputStream(Workbook workbook){ this.workbook=workbook; numberOfSheets = workbook.getNumberOfSheets(); }

      return true;
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return false;
  }

      return true;
  }
  catch (Exception e){
      System.out.println("在read方法内发生错误:["+e.getClass()+e.getCause()+e.getMessage()+"]");
      return false;
  }

}

void close(){ try { workbook.close(); in.close(); } catch (Exception e){ System.out.println("关闭流时发生错误:"+e.getClass()+e.getCause()+e.getMessage());