用java读取EXCEL文件

在项目中，往往数据库部分要牵涉到大部分的表，1， 200个字段，横跨一二十个表，又因为业务部门的人不会去看SQL脚本而自己也为了减少重复的coding劳动，于是一般习惯在EXCEL表里整理字段，然后给业务部门确认，而自己就只需要读取这个EXCEL表来生成自己想要的SQL脚本就好

        今天写了一段小程序如下：

package com.test;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringReader;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CreateSqlFromExcel
{
    //设置被读取的EXCEL的位置
    public static String fileToBeRead = "D:/Ricky/车险报表整理后资料/车险报表筛选详情.xls";

    public static void main(String args[])
    {
       createFile();

    }
    //生成一个SQL脚本文件
    public static void createFile()
    {
        String str = createTable() + "/n"+createComment();
        String ss;
        BufferedReader bf = new BufferedReader(new StringReader(str));
        try
        {
            PrintWriter pw = new PrintWriter(new BufferedWriter(new FileWriter(
                    "d://ricky//temp.sql")));
            while ((ss = bf.readLine()) != null)
                pw.println(ss);

            bf.close();
            pw.close();
        } catch (IOException e)
        {
            e.printStackTrace();
        }
    }

    //创建一个生成建表的SQL语句的方法
    public static String createTable()
    {
        StringBuffer sql = new StringBuffer(
                "create or replace table pa18_new_sync(");
        String temp = null;
        try
        {
            HSSFWorkbook wb = new HSSFWorkbook(
                    new FileInputStream(fileToBeRead));
            HSSFSheet sh = wb.getSheet("整合后的表");
            HSSFRow row;
            HSSFCell cell;
            for (int i = 0; i < 220; i++)
            {
                row = sh.getRow(i);
                for (int j = 1; j < 3; j++)
                {
                    cell = row.getCell((short) j);
                    temp = cell.getStringCellValue();
                    sql.append(temp + " ");
                }
                if (row.getCell((short) 3).getStringCellValue().equals(
                        "not null"))
                {
                    sql.append("not null");
                }
                if (i != 219)
                {
                    sql.append("," + "/n");
                }
            }
            sql.append(");");
            temp = sql.toString();
        } catch (Exception e)
        {
            e.printStackTrace();
        }
        return temp;
    }

    //创建一个生成注释信息的SQL语句方法
    public static String createComment()
    {
        StringBuffer comment_sql = new StringBuffer();
        String temp = null;
        try
        {
            HSSFWorkbook wb = new HSSFWorkbook(
                    new FileInputStream(fileToBeRead));
            HSSFSheet sh = wb.getSheet("整合后的表");
            HSSFRow row;
            HSSFCell cell;

            for (int i = 0; i < 220; i++)
            {
                comment_sql.append("COMMENT ON COLUMN epcis_pa18_sync.");
                row = sh.getRow(i);
                cell = row.getCell((short) 1);
                temp = cell.getStringCellValue();
                comment_sql.append(temp + " " + "IS" + " ");
                cell = row.getCell((short) 0);
                temp = cell.getStringCellValue();
                comment_sql.append("'" + temp + "'" + ";" + "/n");
            }
            temp = comment_sql.toString();
        } catch (Exception ex)
        {
            ex.printStackTrace();
        }
        return temp;
    }

}

 

这段代码可以声成创建表的SQL，但是还需要有查询部分的内容，在下次再补上