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;
}
}