java获取apache版本_java通过apache poi框架读取2007版Excel文件

packagecom.reus;importjava.awt.BorderLayout;importjava.awt.event.ActionEvent;importjava.awt.event.ActionListener;importjava.io.File;importjava.io.FileOutputStream;importjava.io.IOException;importjava.math.BigDecimal;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.TimeZone;importjavax.swing.JButton;importjavax.swing.JFrame;importjavax.swing.JPanel;importjavax.swing.JScrollPane;importjavax.swing.JTable;importjavax.swing.table.DefaultTableModel;importjavax.swing.table.TableModel;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellType;importorg.apache.poi.ss.usermodel.DateUtil;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;public classExcelOperate {public static voidmain(String[] args) {

String[][] data= { { "中文", "$1275.00" }, { "Pets", "$125.00" }, { "Electronics", "$2533.00" }, { "Mensware", "$497.00"} };

String[] headers= { "Department", "Daily Revenue"};

JFrame frame= new JFrame("JTable to Excel Hack");

DefaultTableModel model= newDefaultTableModel(data, headers);final JTable table = newJTable(model);

JScrollPane scroll= newJScrollPane(table);

String outFullPath= "out.xlsx";//默认输出到工程本地目录下

JButton exportButton= new JButton("输出");//输出为Excel文件

exportButton.addActionListener(newActionListener() {public voidactionPerformed(ActionEvent evt) {try{

createExcel(model,outFullPath);//输出并创建Excel

} catch(Exception ex) {

System.out.println(ex.getMessage());

ex.printStackTrace();

}

}

});

JButton readButton= new JButton("读取");//读取Excel文件，在控制台窗口打印显示

readButton.addActionListener(newActionListener() {public voidactionPerformed(ActionEvent evt) {try{

SimpleDateFormat sdf= new SimpleDateFormat("HH:mm:ss:SS");

TimeZone t=sdf.getTimeZone();

t.setRawOffset(0);

sdf.setTimeZone(t);

Long startTime= System.currentTimeMillis();//用于计算从excel文件中读取数据耗时

String fileName= "in.xlsx";

readExcel(fileName);//读取Excel，xlsx后缀名的文件

Long endTime=System.currentTimeMillis();

System.out.println("用时：" + sdf.format(new Date(endTime -startTime)));

}catch(Exception ex) {

System.out.println(ex.getMessage());

ex.printStackTrace();

}

}

});

JPanel bottomPanel=newJPanel();

bottomPanel.setLayout(newBorderLayout());

bottomPanel.add(exportButton, BorderLayout.NORTH);

bottomPanel.add(readButton, BorderLayout.SOUTH);

frame.getContentPane().add("Center", scroll);

frame.getContentPane().add("South", bottomPanel);

frame.pack();

frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

frame.setVisible(true);

}

@SuppressWarnings({"resource"})public static void readExcel(String strPath) throwsIOException {//构造 XSSFWorkbook 对象，strPath 传入文件路径

XSSFWorkbook xwb = newXSSFWorkbook(strPath);//读取第一个表格内容

XSSFSheet sheet = xwb.getSheetAt(0);//0表示第一个表格//定义 row、cell

XSSFRow row;

XSSFCell cell;//循环输出表格中的内容

for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {

row=sheet.getRow(i);for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {

String ret= "";

cell=row.getCell(j);

ret=getCellValueByCell(cell);

System.out.print(ret+ "\t");

}

System.out.println("");

}

}//获取单元格各类型值，返回字符串类型

public staticString getCellValueByCell(Cell cell) {//判断是否为null或空串

if (cell == null || cell.toString().trim().equals("")) {return "";

}

String cellValue= "";

CellType cellType=cell.getCellType();switch(cellType) {case NUMERIC://把枚举常量前的冗余类信息去掉编译即可通过

short format =cell.getCellStyle().getDataFormat();if (DateUtil.isCellDateFormatted(cell)) {//注意：DateUtil.isCellDateFormatted()方法对“2019年1月18日"这种格式的日期，判断会出现问题，需要另行处理

SimpleDateFormat sdf = null;//System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());

if (format == 20 || format == 32) {

sdf= new SimpleDateFormat("HH:mm");

}else if (format == 14 || format == 31 || format == 57 || format == 58) {//处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)

sdf = new SimpleDateFormat("yyyy-MM-dd");double value =cell.getNumericCellValue();

Date date=org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);

cellValue=sdf.format(date);

}else {//日期

sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

}try{

cellValue= sdf.format(cell.getDateCellValue());//日期

} catch(Exception e) {try{throw new Exception("exception on get date data !".concat(e.toString()));

}catch(Exception e1) {

e1.printStackTrace();

}

}finally{

sdf= null;

}

}else{

BigDecimal bd= newBigDecimal(cell.getNumericCellValue());

cellValue= bd.toPlainString();//数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值

}break;case STRING: //字符串

cellValue =cell.getStringCellValue();break;case BOOLEAN: //Boolean

cellValue = cell.getBooleanCellValue() + "";break;case FORMULA: //公式

{//cellValue = cell.getCellFormula();//读取单元格中的公式

cellValue = String.valueOf(cell.getNumericCellValue());//读取单元格中的数值

}break;case BLANK: //空值

cellValue = "";break;case ERROR: //故障

cellValue = "ERROR VALUE";break;default:

cellValue= "UNKNOW VALUE";break;

}returncellValue;

}/*** 用户列表导出，生成Excel*/

private static voidcreateExcel(TableModel model, String outFileFullPath) {

XSSFWorkbook userListExcel=createUserListExcel(model);try{//输出成文件

FileOutputStream outputStream = new FileOutputStream(newFile(outFileFullPath));

userListExcel.write(outputStream);

outputStream.close();

}catch(Exception e) {

e.printStackTrace();

}

}/*** 创建excel*/

private staticXSSFWorkbook createUserListExcel(TableModel model) {//1.创建HSSFWorkbook，一个HSSFWorkbook对应一个Excel文件

XSSFWorkbook wb = newXSSFWorkbook();//2.在workbook中添加一个sheet,对应Excel文件中的sheet

XSSFSheet sheet = wb.createSheet("sheet1");//3.设置表头，即每个列的列名

XSSFRow row = sheet.createRow(0);//创建第一行

for (int i = 0; i < model.getColumnCount(); i++) {

row.createCell(i).setCellValue(model.getColumnName(i));//给列写入数据,创建单元格，将列名写入

}//写入正式数据

for (int i = 1; i <= model.getRowCount(); i++) {//先行索引。由于第一行已经被表格的标题占据了，所以昌数据索引从1开始

row = sheet.createRow(i);//创建真正的行用于存放数据

for (int j = 0; j < model.getColumnCount(); j++) {//后列索引

row.createCell(j).setCellValue(model.getValueAt(i-1, j).toString());

sheet.autoSizeColumn(1, true);

}

}returnwb;

}

}