java swing 中使用JFileChooser读取Excel并进行解析

package com.robert.Excel; import javax.swing.*; public class ExcelFrame extends JFrame { private static final int DEFAULT_WIDTH = 500; private static final int DEFAULT_HEIGHT = 400; private static final int DEFAUTL_POSITION_X = 200; private static final int DEFAULT_POSITION_Y = 200; public ExcelFrame() { setSize(DEFAULT_WIDTH, DEFAULT_HEIGHT); setLocation(DEFAUTL_POSITION_X, DEFAULT_POSITION_Y); setTitle("解析Excel"); setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); } public String readFile() { String fileName = ""; JFileChooser chooser = new JFileChooser(); int returnVal = chooser.showOpenDialog(this); if(returnVal == JFileChooser.APPROVE_OPTION) { fileName = chooser.getSelectedFile().getName(); } return fileName; } }
package com.robert.Excel; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; import java.util.ArrayList; public class ExcelUtils { public static final String PATH = "E:\\write.sql"; private ExcelFrame excelFrame = new ExcelFrame() ; private static final String INSERT_PREFIX = "insert into table values("; public static void main(String[] args) { ExcelUtils excelUtils = new ExcelUtils(); excelUtils.resolvingExcelToSQLScript(); } public void resolvingExcelToSQLScript() { String fileName = excelFrame.readFile(); XSSFSheet sheet = readExcel("E:\\"+fileName); ArrayList<ArrayList> table = resolvingExcelSheet(sheet); String sql = mergerStr(table, INSERT_PREFIX); write(sql,PATH); } public XSSFSheet readExcel(String fileName) { XSSFWorkbook workbook = null; XSSFSheet sheet = null; try { workbook = new XSSFWorkbook(new FileInputStream(fileName)); sheet = workbook.getSheetAt(0); } catch (IOException e) { e.printStackTrace(); } return sheet; } public ArrayList<ArrayList> resolvingExcelSheet(XSSFSheet sheet) { int numberOfRows = sheet.getPhysicalNumberOfRows(); XSSFRow row = null; ArrayList<ArrayList> values = new ArrayList<ArrayList>(); ArrayList<String> rowValue = new ArrayList<String>(); if (sheet != null) { for (int i = 0; i < numberOfRows; i++) { row = sheet.getRow(i); rowValue = resolvingExcelRow(row); values.add(rowValue); } } return values; } public ArrayList<String> resolvingExcelRow(XSSFRow row) { int numberOfColumns = row.getPhysicalNumberOfCells(); XSSFCell cell = null; String cellValue = ""; ArrayList<String> cellValues = new ArrayList<String>(); if (row !=null) { for (int i = 0; i < numberOfColumns; i++) { cell = row.getCell(i); if(cell!=null) { if((cell.getCellType())==XSSFCell.CELL_TYPE_NUMERIC) { cellValue = cell.getNumericCellValue()+""; } else if(cell.getCellType()==XSSFCell.CELL_TYPE_STRING) { cellValue = cell.getStringCellValue(); cellValue = "\'"+cellValue+"\'"; } cellValues.add(cellValue); } } } return cellValues; } public String mergerStr(ArrayList<ArrayList> table,String insertStr) { String scriptSQL = ""; for(ArrayList arrayList : table) { String insertSQL = insertStr; ArrayList<String> list = (ArrayList<String>)arrayList; for(int i=0;i<list.size()-1;i++) { insertSQL += list.get(i); insertSQL += ", "; } insertSQL += list.get(list.size()-1); insertSQL += ");"; insertSQL += "\n"; scriptSQL += insertSQL; } return scriptSQL; } public void write(String sql,String path) { PrintWriter printWriter = null; BufferedWriter bufferedWriter = null; try { printWriter = new PrintWriter(path); bufferedWriter = new BufferedWriter(printWriter); bufferedWriter.write(sql); bufferedWriter.flush(); bufferedWriter.close(); printWriter.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { bufferedWriter.close(); printWriter.close(); } catch (IOException e) { e.printStackTrace(); } } } }

注意：

使用JFileChooser读取的文件路径格式为：E：\test.xlsx，当使用这种格式的文件路径去查找文件时，会出现找不到文件的异常。