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,当使用这种格式的文件路径去查找文件时,会出现找不到文件的异常。