没有标题-CSDN博客

本文链接：https://blog.csdn.net/aixuewo/article/details/98476569

package Entity;

import java.util.List;
import java.util.Map;

public class SheetCls {
private String sheetName;
private List<Map<String, String>> sheet;

public String getSheetName() {
return sheetName;
}

public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}

public List<Map<String, String>> getSheet() {
return sheet;
}

public void setSheet(List<Map<String, String>> sheet) {
this.sheet = sheet;
}
}

package excel;

public class ExcelFrame {

   public static void main(String[] args) {
       // TODO Auto-generated method stub
       Frame fram=new Frame();
       fram.Start();
   }

}

package excel;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import Entity.SheetCls;

public class ExecuteExcel {
   private static int file_num;
private static FormulaEvaluator evaluator;
public ExecuteExcel()
{
   file_num=0;
}
   public void Execute(String filepath,String outfiledir) throws Exception{
       // TODO Auto-generated method stub
   //filepath="D:\\wx\\1234.xls";
   try {
       List<SheetCls> temp=readExcelWithTitle(filepath);
       for (int i=0;i<temp.size();i++)
       {
           SheetCls tem_sheet=temp.get(i);
           file_num=file_num+10;
           File file = new File(outfiledir+"/"+(file_num)+"_"+tem_sheet.getSheetName()+".sql");//写入的sql文件
       if(!file.exists()){
       file.createNewFile();
       }
       FileWriter fw = new FileWriter(file,false);

               BufferedWriter bw = new BufferedWriter(fw);

           for (int j=0;j<tem_sheet.getSheet().size();j++)/*遍历行*/
           {
               Map<String, String> temp_row =tem_sheet.getSheet().get(j);
               String instr=" insert into "+tem_sheet.getSheetName()+"(";
               String values=" \r\n values(";
               for(Entry<String, String> vo : temp_row.entrySet()){
                   instr=instr+vo.getKey()+",";
                   if((vo.getValue()!=null&&vo.getValue().indexOf("to_date")!=-1)||null==vo.getValue())
                   {
                       values=values+vo.getValue()+",";
                   }else{
                       values=values+"'"+vo.getValue()+"'"+',';
                   }
                   /* System.out.println(vo.getKey()+" "+vo.getValue());*/
                   }
               instr=instr.substring(0,instr.length()-1)+")";
               values=values.substring(0,values.length()-1)+");";
               System.out.println(instr+values);
               bw.write(instr+values+"\r\n");//write to outputFile
               bw.flush();
           }
           bw.close();
           fw.close();
       }
   } catch (Exception e) {
       // TODO Auto-generated catch block
       throw e;
   }
}

public static List<SheetCls> readExcelWithTitle(String filepath) throws Exception{

String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());

InputStream is = null;

Workbook wb = null;

try {

is = new FileInputStream(filepath);

if (fileType.equals("xls")) {

wb = new HSSFWorkbook(is);

} else if (fileType.equals("xlsx")) {

wb = new XSSFWorkbook(is);

} else {

throw new Exception("Not excel File!");

   }

   List<SheetCls> result = new ArrayList<SheetCls>();//对应excel文件
   int sheetSize = wb.getNumberOfSheets();
   for (int i = 0; i < sheetSize; i++) {//遍历sheet页
       SheetCls sheetMap = new SheetCls ();
   Sheet sheet = wb.getSheetAt(i);
   String sheetnm=sheet.getSheetName();
   List<Map<String, String>> sheetList = new ArrayList<Map<String, String>>();//对应sheet页
   List<String> titles = new ArrayList<String>();//放置所有的标题

int rowSize = sheet.getLastRowNum() + 1;

for (int j = 0; j < rowSize; j++) {//遍历行

Row row = sheet.getRow(j);

if (row == null) {//略过空行

continue;

}

int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列

if (j == 0) {//第一行是标题行

for (int k = 0; k < cellSize; k++) {

Cell cell = row.getCell(k);

titles.add(cell.toString());

}

} else {//其他行是数据行

Map<String, String> rowMap = new LinkedHashMap<String, String> ();//对应一个数据行,采用LinkedHashMap可以按照加入的顺序输出

for (int k = 0; k < titles.size(); k++) {

Cell cell = row.getCell(k);

   String key = titles.get(k);

   String value = null;

   if (cell != null) {
   value = cell.toString();
   /* org.apache.poi.ss.usermodel.CellType typ=cell.getCellType();
   System.out.println(typ.toString());
   if (typ.toString()=="NUMERIC"){
       value=String.valueOf(cell.getNumericCellValue());
       System.out.println("zhuanhuanhou"+value);
   }*/
   value=getCellValueByCell(cell);

   }

rowMap.put(key, value);

   }

   sheetList.add(rowMap);

}

   }
   sheetMap.setSheetName(sheetnm);
   sheetMap.setSheet(sheetList);
   result.add(sheetMap);
   }
   return result;

} catch (FileNotFoundException e) {

throw e;

} finally {

   if (wb != null) {
   wb.close();
   }
   if (is != null) {
   is.close();
   }
   }
   }

private static String getCellValueByCell(Cell cell) {

//判断是否为null或空串

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

return "";

}

String cellValue = "";

CellType cellType=cell.getCellType();

if(cellType==CellType.FORMULA){ //表达式类型

cellType=evaluator.evaluate(cell).getCellType();

}

if (cellType==CellType.STRING)
{
cellValue= cell.getStringCellValue().trim();

cellValue=StringUtils.isEmpty(cellValue) ? "" : cellValue;
}
else if (cellType==CellType.BOOLEAN)
{
cellValue = String.valueOf(cell.getBooleanCellValue());

}

else if (cellType==CellType.NUMERIC)
{
if (DateUtil.isCellDateFormatted(cell)) {
   Date dateCellValue = cell.getDateCellValue();
   if (dateCellValue != null) {
       SimpleDateFormat sdf=new SimpleDateFormat("YYYY/MM/DD");
       cellValue="to_date('" +sdf.format(dateCellValue)+"','YYYY-MM-DD')";
   }

} else { //否

cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
}
}
else{
cellValue = "";
}

return cellValue;

}
}

package excel;

import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTabbedPane;
import javax.swing.JTextArea;

public class Frame extends JFrame implements ActionListener{

   /**
   *
   */
   private static final long serialVersionUID = 1L;
   List<String> file_list =new ArrayList<String>();
   //窗口
   JFrame jf = new JFrame("Excel Tool");
   //画板
   JPanel jp = new JPanel();
   //布局卡选项
   JTabbedPane tabPane=new JTabbedPane();
   //布局1
   Container con=new Container();

   //标签
   JLabel jl1 = new JLabel("Choose Excel");
   /*JLabel jl2 = new JLabel("选择模板");*/
   JLabel jl3 = new JLabel("Output Directory");
   //按钮
   JButton jb1 = new JButton("···");
   /*JButton jb2 = new JButton("···");*/
   JButton jb3 = new JButton("···");
   JButton jb4 = new JButton("Start");
   //文本
   JTextArea jt1 = new JTextArea();
   /*JTextArea jt2 = new JTextArea();*/
   JTextArea jt3 = new JTextArea();
   //文件选择器
   JFileChooser chooser = new JFileChooser();
   public void Start(){
       //设置窗口属性---------------------
       //窗口可见
       jf.setVisible(true);
       //窗口大小
       jf.setSize(800, 400);
       //关闭窗口时关闭程序
       jf.setDefaultCloseOperation(EXIT_ON_CLOSE);
       //设置窗口位置
       jf.setLocation(400, 150);
   }
   public Frame(){
   //将画板加到窗口上
   jf.add(jp);
   //布局，将布局卡加到面板上
   jf.setContentPane(tabPane);
   //设置标签，按钮，文本框的位置及大小，x,y,width,height
   jl1.setBounds(20,20, 100, 20);
   jb1.setBounds(640,20,50,20);
   jt1.setBounds(120,20,500,20);
   jt1.setEditable(false);
   //jl2.setBounds(20,50, 100, 20);
   //jb2.setBounds(640,50,50,20);
   //jt2.setBounds(120,50,500,20);
   jl3.setBounds(20,80, 300, 20);
   jb3.setBounds(640,80,50,20);
   jt3.setBounds(120,80,500,20);
   jt3.setEditable(false);
   jb4.setBounds(300,140,100,30);
   //设置按钮事件处理 this代表本身这个对象，意思是监听这个对象，所里该类必须实现ActionListener
   jb1.addActionListener(this);
   //jb2.addActionListener(this);
   jb3.addActionListener(this);
   jb4.addActionListener(this);
   //将按钮加到布局1上
   con.add(jl1);
   con.add(jt1);
   con.add(jb1);
   //con.add(jl2);
   //con.add(jt2);
   //con.add(jb2);
   con.add(jl3);
   con.add(jt3);
   con.add(jb3);
   con.add(jb4);
   //将布局加到布局卡内，并设置该布局的名称
   tabPane.add("Generate SQL",con);

}

   //事件处理，所有事件处理必须写在该方法，该方法重写自ActionListener
   @Override
   public void actionPerformed(ActionEvent e) {
       // TODO Auto-generated method stub
       //点击按钮后，判断是哪一个按钮
       if(e.getSource() == jb1){
       //设置文件选择器只能选择0（文件），1（文件夹）
       chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
       chooser.setMultiSelectionEnabled(true);
       //打开文件浏览器，点击取消则返回1
       int status = chooser.showOpenDialog(null);
       if(status == 1){
       return;
       }else{
       //读取选择器选择到的文件
       File file[]=chooser.getSelectedFiles();
       String file_name = "";
       //获取文件绝对路径并写入到文本框内
   for (int m=0;m<file.length;m++)
   {
       file[m].getAbsolutePath();
       file_name=file_name+"\""+file[m].getName()+"\"";
       System.out.println(file[m].getName());
       file_list.add(file[m].getAbsolutePath());//add file
   }

       jt1.setText(file_name);

       }
       }
       /*if(e.getSource() == jb2){
       chooser.setFileSelectionMode(0);
       int status = chooser.showOpenDialog(null);
       if(status == 1){
       return;
       }else{
       File file = chooser.getSelectedFile();
       jt2.setText(file.getAbsolutePath());
       }
       }*/
       if(e.getSource() == jb3){
       chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
       int status = chooser.showOpenDialog(null);
       if(status == 1){
       return;
       }else{
       File file1 = chooser.getSelectedFile();
       jt3.setText(file1.getAbsolutePath());
       }
       }
       if(e.getSource() == jb4){
           System.out.println(jt1.getText().isEmpty());
           if (jt1.getText().isEmpty())
           {
               JOptionPane.showMessageDialog(jp, "Please Choose Excel!", "WARNING",JOptionPane.WARNING_MESSAGE);
               return;
       };
       if(jt3.getText().isEmpty())
       {
           JOptionPane.showMessageDialog(jp, "Please Choose Output Directory!", "WARNING",JOptionPane.WARNING_MESSAGE);
               return;
       }
   try
   {
           ExecuteExcel temp=new ExecuteExcel();
   for (int aa=0;aa<file_list.size();aa++)
   {
       System.out.println("4232323"+file_list.get(aa).toString());
           temp.Execute(file_list.get(aa).toString().replace("'\'","'/'"), jt3.getText().replace("'\'", "'/'"));
   }
   JOptionPane.showMessageDialog(jp, "Generate Successful", "Info",JOptionPane.INFORMATION_MESSAGE);
           }catch(Exception e1)
           {
               JOptionPane.showMessageDialog(jp, e1, "Error",JOptionPane.ERROR_MESSAGE);
               System.out.println(e1);
               return;
           }
   /*   Main m = new Main();
       try {
       m.start();
       } catch (Exception e1) {
       //准备添加错误日志报告写入文件
       e1.printStackTrace();
       }*/
       }
   }
   }