demo
目录：程序员成长之旅/Java学习/源码
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 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());
}
}
}