|||
版本1
51.源码51,部件程序,添加式从office文件,包括word、excel、pdf文件导入导入部件。
/**
* 程序文件名:dataTransfer11.java
* 作者:程学先
* 功能:添加式从office文件,包括word、excel、pdf文件导入。
* 文件中的数据添加到原有数据尾部。注意本程序未作实体完整性测试,未要求提供关键字,关键字值相同的记录可能被误导入。
* 目的表与源表数据类型、数据宽度应当对应相容,符合本程序中“类型对照表"设置的对应关系。
* 完成日期:2013年1月22日
* 被调用语句:dataTransfer11.means(parameter)
* 必须提供的参数:数据“表名”、“导入文件名”。
* 可选择参数:“字段号表”、“要求按钮号表”、
* 要求字段号表:所操作数据可以是表的全部字段,也可以只涉及部分字段。如果选择全部字段,初始值设为"";
* 否则写入所有将涉及的字段在表中的顺序号,号与号之间以英文逗号分隔。
* 本部件预设的按钮控件共4个,序号及按钮名:1添加式从word文件导入、2添加式从excel文件导入、
* 3添加式从PDF文件导入、4退出
* 通过“要求按钮号表”可以选择按钮以满足功能需求。
* 如果从PDF文件导入。不支持大数据类型,每字段宽度不超过50字符。
* 只能从由dataTransfer4.java生成的PDF文件导入。
*/
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.io.File.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
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;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.pdfbox.pdfparser.PDFParser;
import org.pdfbox.pdmodel.PDDocument;
import org.pdfbox.util.PDFTextStripper;
public class dataTransfer11 extends JFrame {
static dataTransfer11 frame = new dataTransfer11(); // 创建窗体
private static JTable table; // 创建表格
static Connection con1;
static String url;
static ResultSetMetaData rsmd4;
static PreparedStatement pstmt;
static Statement sta4;
static ResultSet rs; // 数据库数据集,存放查询的结果
static int 窗口宽 = 800, 窗口高 = 600;// 窗口宽、窗口高
static int 列数; // 数据表列数
static int 列数1;
static String[][] 列名; // 数据表列名
static String[] 列名1;
static int[] 对应目的表序号; // 存放当前记录各字段数据宽度的数组
static String[] 表格列名;
static String[] 列数据类型; // 存放当前记录各字段数据类型的数组
static String[] 表列数据类型; // 存放当前记录各字段数据类型的数组
static int[] 列数据宽度, 列小数位数; // 存放当前记录各字段数据宽度的数组
static int 记录条数 = 0, 记录条数1 = 0; // 记录条数
static int[] 表列数据宽度, 表列小数位数;
static int 当前记录号;
static String[][] 表格数据;
static DefaultTableModel model;
static String 表名; // 数据表名
static String 字典表名;
static String 导入文件名; // 导入表名,
static String[] 按钮集 = null;
static String 要求按钮号表;
static String 字段号表;
static String s1 = "", s2 = "", s3 = "";
static String s4 = " ";
static char x1 = 10, x2 = 13, x3 = '"', x4 = ',';
static String 类型1 = main1.类型1;
static int 按钮数 = 0, 按钮宽 = 0;
public static void means(String[] parameter) {
frame = new dataTransfer11();
窗口宽 = 800;
窗口高 = 600;
记录条数 = 0;
记录条数1 = 0;
s1 = "";
s2 = "";
s3 = "";
s4 = " ";
x1 = 10;
x2 = 13;
x3 = '"';
x4 = ',';
类型1 = main1.类型1;
File file = new File(".\\com\\iText-5.0.5.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
file = new File(".\\com\\jacob.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
file = new File(".\\com\\poi-3.8-20120326.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
file = new File(".\\com\\PDFBox-0.7.3.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
表名 = parameter[4]; // 数据表名
字段号表 = parameter[8];
导入文件名 = parameter[18];
String[] 按钮集01 = { "从word导入", "从excel导入", "从PDF文件导入", "退出" };
按钮集 = new String[按钮集01.length];
按钮数 = 0;
for (int i0 = 0; i0 < 按钮集01.length; i0++)
按钮集[i0] = 按钮集01[i0];
if (parameter[11].length() > 0) {
要求按钮号表 = parameter[11];// 需要显示的按钮的顺序号
// 将中文分号、冒号、句号、英文分号统一为英文分号
要求按钮号表 = 要求按钮号表.replaceAll(";", ";");
要求按钮号表 = 要求按钮号表.replaceAll("。", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
if (要求按钮号表.indexOf(";") > 0) { // 如果有按钮更名要求
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
按钮集[Integer.parseInt((s602[0]))] = s602[1];
要求按钮号表 = 要求按钮号表 + "," + s602[0];// 只留号,去更名
按钮数++;
} else {
要求按钮号表 = 要求按钮号表 + "," + s601[i01];// 只留号,原无更名
按钮数++;
}
}
} // 如果没有按钮更名要求,要求按钮号长度又大于0,保持原有摸样
else {
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
要求按钮号表 = s601[i01];
else
要求按钮号表 = 要求按钮号表 + "," + s601[i01];
按钮数++;
}
}
} else {
要求按钮号表 = ""; // 没输入要求按钮号,定全部按钮号
for (int i1 = 0; i1 < 按钮集.length; i1++) {
if (i1 == 0)
要求按钮号表 = 要求按钮号表 + i1;
else
要求按钮号表 = 要求按钮号表 + "," + i1;
按钮数++;
}
}
要求按钮号表 = "," + 要求按钮号表 + ",";
窗口宽 = main1.窗口宽;
窗口高 = main1.窗口高;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);// 宽度参数
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
窗口宽 = m1;
窗口高 = m2;
}
} catch (Exception e2) {
}
String 字段名表 = parameter[19].trim();
if ((字段名表.length() > 0)
&& (字段名表.substring(字段名表.length() - 1).equals(",")))
字段名表 = 字段名表.substring(0, 字段名表.length() - 1);
String 表名表 = parameter[0];
String[] l3 = 字段名表.split(",");
字典表名 = "";
String[] t1;
int b = 0, c = 0;
if (表名表.lastIndexOf(表名 + "字典表") >= 0) {
t1 = 表名表.split(",");
for (int i = 0; i < t1.length; i++)
if (t1[i].lastIndexOf(表名 + "字典表") >= 0)
字典表名 = t1[i];
}
String[] l1;
if (字段号表.length() > 0) {
l1 = 字段号表.split(",");
int[] l2 = new int[l1.length];
for (int i = 0; i < l1.length; i++) {
l2[i] = Integer.valueOf(l1[i]);
}
列数 = l1.length;
列名 = new String[2][列数];
for (int i = 0; i < 列数; i++) {
列名[0][i] = l3[l2[i]];
列名[1][i] = 列名[0][i];
}
} else {
列数 = l3.length;
列名 = new String[2][列数];
for (int i = 0; i < 列数; i++) {
列名[0][i] = l3[i];
列名[1][i] = 列名[0][i];
}
}
s1 = "";
for (int i = 0; i < 列数; i++)
s1 = s1 + 列名[0][i] + ",";
s1 = s1.substring(0, s1.length() - 1);
try {
if (("," + 表名表).lastIndexOf("," + 表名 + ",") >= 0) {
con1 = main1.getConn();
sta4 = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select " + s1 + " from " + 表名;
} else {
JOptionPane.showMessageDialog(null, "目的数据表不存在,不能导入!");
return;
}
ResultSet rs = sta4.executeQuery(s1);
rsmd4 = rs.getMetaData();
列数 = rsmd4.getColumnCount(); // 获取列数
列名 = new String[2][列数]; // 定义列名数组
列数据类型 = new String[列数]; // 定义列字段类型数组
列数据宽度 = new int[列数]; // 定义列数据宽度
for (int i = 0; i < 列数; i++) {
列名[0][i] = rsmd4.getColumnName(i + 1); // 获取列名存到数组中
列名[1][i] = 列名[0][i];
列数据类型[i] = rsmd4.getColumnTypeName(i + 1);
列数据宽度[i] = rsmd4.getColumnDisplaySize(i + 1);
if (列数据宽度[i] < 列名[0][i].length())
列数据宽度[i] = 列名[0][i].length();
if (列数据宽度[i] < 列名[1][i].length())
列数据宽度[i] = 列名[1][i].length();
if (列数据宽度[i] > 50)
列数据宽度[i] = 50;
}
表格列名 = new String[列数];
表列数据类型 = new String[列数];
表列数据宽度 = new int[列数];
for (b = 0; b < 列数; b++) {
表格列名[b] = 列名[1][b];
表列数据宽度[b] = 列数据宽度[b];
}
记录条数 = 0;
rs.last();
记录条数 = rs.getRow();
表格数据 = new String[记录条数][列数];
rs.absolute(1);
c = 0; // 行号
b = 0; // 列号
while (c < 记录条数) {
rs.absolute(c + 1);
while (b < 列数) {
表格数据[c][b] = rs.getString(b + 1);
b++;
}
c++;
b = 0;
}
rs.close(); // 关闭查询结果集
sta4.close(); // 关闭连接
con1.close();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "读取数据出错!");
}
if (字典表名.length() > 0) {
try {
con1 = main1.getConn();// 连接数据库
sta4 = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + 字典表名;
rs = sta4.executeQuery(s1);
String s4 = "";
String s5 = "";
b = 1;
while (rs.absolute(b)) {
b++;
s4 = rs.getString(1); // 字段名
s5 = rs.getString(2);
for (int j = 0; j < 列数; j++) {
if (列名[0][j].trim().equals(s4.trim())) {
列名[1][j] = s5;
表格列名[j] = s5;
break;
}
}
}
rs.close(); // 关闭查询结果集
con1.close();
sta4.close();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "连接数据库出错!");
}
}
frame.setTitle("数据添加式从office文件导入部件。 作者:程学先"); // 设置窗体标题
frame.setBounds(10, 10, 窗口宽, 窗口高); // 设置窗体的位置和大小
frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE); // 设置窗体的默认关闭模式
frame.setLayout(null);
final JPanel panel = new JPanel(false); // 定义面板
panel.setLayout(null); // 关闭面板布局管理器
panel.setBounds(10, 10, 窗口宽, 窗口高);
final JScrollPane scrollPane = new JScrollPane(); // 创建滚动面板
scrollPane.setBounds(0, 0, 窗口宽 - 20, 窗口高 - 100); // 定义滚动面板大小位置
panel.add(scrollPane);
model = new DefaultTableModel(表格数据, 列名[1]); // 创建表格模型
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < 列数; i++) {
TableColumn tc = table.getColumn(列名[1][i]);
tc.setPreferredWidth(列数据宽度[i] * 14);
tc.setMaxWidth(列数据宽度[i] * 14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
int 按钮宽 = (窗口宽 - 30) / 按钮数;
int 按钮左边距 = 10;
JButton 从word = new JButton(按钮集[0]);
从word.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
当前记录号 = model.getRowCount();
if (导入文件名.length() == 0) {
导入文件名 = JOptionPane.showInputDialog(
"请输入导入文件完整路径 例如:F:\\java.doc", "");
return;
}
int p1 = 0, p2 = 0;
if (导入文件名.lastIndexOf(".doc") < 0) {
p1 = 导入文件名.lastIndexOf(".");
p2 = 导入文件名.lastIndexOf("\\\\");
if (p2 > p1) {
导入文件名 = 导入文件名 + ".doc";
导入文件名 = 导入文件名.replace("\\.", "\\.\\.");
} else {
导入文件名 = 导入文件名.substring(0, p1) + ".doc";
}
}
try {
FileInputStream in = new FileInputStream(new File(导入文件名));
WordExtractor extractor = null;
extractor = new WordExtractor(in);
String 内容 = extractor.getText();
int m1 = 0; ;
String[] 记录 = new String[列数];
int k = 0;
int k2 = 0, k5 = 0;
记录[k] = "";
for (int i = 0; i < 内容.length(); i++) {
if (内容.charAt(i) == '\r') {
k5 = 0;
model.addRow(记录);
记录[0] = "";
k = 0;
m1 = 1;
if ((i < 内容.length() - 2)
&& (内容.charAt(i + 2) == '\r'))
break;
} else if (内容.charAt(i) == '\n') {
} else if ((byte) (内容.charAt(i)) == 9) {
k++;
m1 = 0;
if (k < 列数)
记录[k] = "";
} else if (k < 列数) {
记录[k] = 记录[k] + 内容.charAt(i);
}
}
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "读取数据出错!");
}
try {
main1.driver1();
con1 = main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
for (int i = 当前记录号; i < model.getRowCount(); i++) {
s1 = "insert into " + 表名 + " (";
for (int k1 = 0; k1 < 列名[1].length; k1++)
s1 = s1 + 列名[1][k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < 列名[1].length; j++) {
if (类型1.lastIndexOf("," + 列数据类型[j] + ",") >= 0) {
if (model.getValueAt(i, j) == null)
s1 = s1 + "0";
else
s1 = s1+ model.getValueAt(i, j).toString().trim();
} else {
if (model.getValueAt(i, j) == null)
s1 = s1 + "' '";
else
s1 = s1+ "'"+ model.getValueAt(i, j).toString().trim() + "'";
}
if (j != 列名[1].length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
sta4.executeUpdate(s1);
}
sta4.close(); // 关闭连接
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",0,") >= 0)) {
从word.setBounds(按钮左边距, 窗口高 - 80, 按钮宽, 20);
panel.add(从word);
按钮左边距 = 按钮左边距 + 按钮宽;
}
JButton 从excel = new JButton(按钮集[1]);
从excel.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
当前记录号 = model.getRowCount();
if (导入文件名.length() == 0) {
导入文件名 = JOptionPane.showInputDialog(
"请输入导入文件完整路径 例如:F:\\java.xls", "");
return;
}
int p1 = 0, p2 = 0;
if (导入文件名.lastIndexOf(".xls") < 0) {
p1 = 导入文件名.lastIndexOf(".");
p2 = 导入文件名.lastIndexOf("\\\\");
if (p2 > p1) {
导入文件名 = 导入文件名 + ".xls";
导入文件名 = 导入文件名.replace("\\.", "\\.\\.");
} else {
导入文件名 = 导入文件名.substring(0, p1) + ".xls";
}
}
InputStream is = null;
HSSFWorkbook wbWorkbook = null;
try {
is = new FileInputStream(导入文件名);
wbWorkbook = new HSSFWorkbook(is);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "读取文件失败,请检查路径是否正确!",
"错误", JOptionPane.INFORMATION_MESSAGE);
} catch (IOException e2) {
e2.printStackTrace();
}
HSSFSheet sheet = wbWorkbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
int 导入表格行数 = sheet.getPhysicalNumberOfRows();
HSSFRow row = sheet.getRow(1);
int 导入表格列数 = row.getPhysicalNumberOfCells();
if (导入表格列数 != 列数)
JOptionPane.showMessageDialog(null, "原文件中数据结构与数据表不同!");
String[] val;
int k2 = 0, k5 = 0;
String value = "";
for (int i = 0; i < 导入表格行数; i++) {
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < 导入表格列数; j++) {
HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
}
val = value.split(",");
k5 = 0;
model.addRow(val);
value = "";
}
}
try {
main1.driver1();
con1 = main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
for (int i = 当前记录号; i < model.getRowCount(); i++) {
s1 = "insert into " + 表名 + " (";
for (int k1 = 0; k1 < 列名[1].length; k1++)
s1 = s1 + 列名[1][k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < 列名[1].length; j++) {
if (类型1.lastIndexOf("," + 列数据类型[j] + ",") >= 0) {
if (model.getValueAt(i, j) == null)
s1 = s1 + "0";
else
s1 = s1+ model.getValueAt(i, j).toString().trim();
} else {
if (model.getValueAt(i, j) == null)
s1 = s1 + "' '";
else
s1 = s1+ "'"+ model.getValueAt(i, j).toString().trim() + "'";
}
if (j != 列名[1].length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
sta4.executeUpdate(s1);
}
sta4.close(); // 关闭连接
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",1,") >= 0)) {
从excel.setBounds(按钮左边距, 窗口高 - 80, 按钮宽, 20);
panel.add(从excel);
按钮左边距 = 按钮左边距 + 按钮宽;
}
JButton 从pdf = new JButton(按钮集[2]);
从pdf.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
当前记录号 = model.getRowCount();
if (导入文件名.length() == 0) {
导入文件名 = JOptionPane.showInputDialog(
"请输入导入文件完整路径 例如:F:\\java.pdf", "");
return;
}
int p1 = 0, p2 = 0;
if (导入文件名.lastIndexOf(".pdf") < 0) {
p1 = 导入文件名.lastIndexOf(".");
p2 = 导入文件名.lastIndexOf("\\\\");
if (p2 > p1) {
导入文件名 = 导入文件名 + ".pdf";
导入文件名 = 导入文件名.replace("\\.", "\\.\\.");
} else {
导入文件名 = 导入文件名.substring(0, p1) + ".pdf";
}
}
try {
FileInputStream in = new FileInputStream(导入文件名);
PDFParser parser = new PDFParser(in);
parser.parse();
PDDocument pdfdocument = parser.getPDDocument();
PDFTextStripper stripper = new PDFTextStripper();
s1 = stripper.getText(pdfdocument);
in.close();
} catch (Exception e1) {
e1.printStackTrace();
}
String[] 记录 = new String[列数];
String s4 = "";
int k = 0, m1 = 0, k2 = 0, k5 = 0;
记录[0] = "";
int l1 = s1.length();
for (int i1 = 0; i1 < l1; i1++) {
s2 = s1.substring(i1, i1 + 1);
if (s1.charAt(i1) == '\r') {
} else if (s1.charAt(i1) == '\n') {
} else if ((s2.trim().equals("│")) && (m1 == 1)) {
if (k == 列数 - 1) {
k5 = 0;
model.addRow(记录);
k = 0;
m1 = 0;
记录[k] = "";
} else {
k++;
if (k < 列数)
记录[k] = "";
}
} else if (s2.trim().equals("│")) {
m1 = 1;
k = 0;
记录[0] = "";
} else if ((k < 列数) && (m1 == 1)) {
try {
记录[k] = 记录[k] + s2;
} catch (Exception e1) {
}
}
}
try {
main1.driver1();
con1 = main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
for (int i = 当前记录号; i < model.getRowCount(); i++) {
s1 = "insert into " + 表名 + " (";
for (int k1 = 0; k1 < 列名[1].length; k1++)
s1 = s1 + 列名[1][k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < 列名[1].length; j++) {
if (类型1.lastIndexOf("," + 列数据类型[j] + ",") >= 0) {
if (model.getValueAt(i, j) == null)
s1 = s1 + "0";
else
s1 = s1+ model.getValueAt(i, j).toString().trim();
} else {
if (model.getValueAt(i, j) == null)
s1 = s1 + "' '";
else
s1 = s1+ "'"+ model.getValueAt(i, j).toString().trim() + "'";
}
if (j != 列名[1].length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
sta4.executeUpdate(s1);
}
sta4.close();
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",2,") >= 0)) {
从pdf.setBounds(按钮左边距, 窗口高 - 80, 按钮宽, 20);
panel.add(从pdf);
按钮左边距 = 按钮左边距 + 按钮宽;
}
JButton exitButton = new JButton(按钮集[3]);
exitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
frame.dispose();
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",3,") >= 0)) {
exitButton.setBounds(按钮左边距, 窗口高 - 80, 按钮宽, 20);
panel.add(exitButton);
按钮左边距 = 按钮左边距 + 按钮宽;
}
frame.add(panel);
frame.setVisible(true); // 显示窗体
}
}
52.源码52,部件程序,修改式从office文件,包括word、excel、pdf文件导入数据导入部件。
/**
* 程序文件名:dataTransfer12.java
* 作者:程学先
* 功能:修改式从office文件,包括word、excel、pdf文件导入。
* 本程序要求提供关键字,关键字值相同的记录将替代(修改)原有记录,关键字再原表中不存在的记录会添加到原有数据尾部。
* 目的表与源表数据类型、数据宽度应当对应相容,符合本程序中“类型对照表"设置的对应关系。 完成日期:2013年1月22日
* 被调用语句:dataTransfer12.means(parameter) 必须提供的参数:数据“表名”、“关键字”、“导入文件名”。
* 可选择参数:“字段号表”、“要求按钮号表”、 要求字段号表:所操作数据可以是表的全部字段,也可以只涉及部分字段。如果选择全部字段,初始值设为"";
* 否则写入所有将涉及的字段在表中的顺序号,号与号之间以英文逗号分隔。
* 本部件预设的按钮控件共4个,序号及按钮名:1修改式从word文件导入、2修改式从excel文件导入、 3修改式从PDF文件导入、4退出
* 通过“要求按钮号表”可以选择按钮以满足功能需求。 如果从PDF文件导入。不支持大数据类型,每字段宽度不超过50字符。
* 只能从由dataTransfer4.java生成的PDF文件导入。
*/
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.io.File.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
import java.util.ArrayList;
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;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.pdfbox.pdfparser.PDFParser;
import org.pdfbox.pdmodel.PDDocument;
import org.pdfbox.util.PDFTextStripper;
public class dataTransfer12 extends JFrame {
static dataTransfer12 frame = new dataTransfer12(); // 创建窗体
private static JTable table; //创建表格
static Connection con1;
static String url;
static ResultSetMetaData rsmd4;
static PreparedStatement pstmt;
static Statement sta4;
static ResultSet rs;
static int 窗口宽=800,窗口高=600;//窗口宽、窗口高
static int 列数; //数据表列数
static int 列数1;
static String [][] 列名; //数据表列名
static String [] 列名1;
static int[] 对应目的表序号; //存放当前记录各字段数据宽度的数组
static String [] 表格列名;
static String[] 列数据类型; //存放当前记录各字段数据类型的数组
static String[] 表列数据类型; //存放当前记录各字段数据类型的数组
static int[] 列数据宽度,列小数位数; //存放当前记录各字段数据宽度的数组
static int 记录条数=0,记录条数1=0; //记录条数
static int[] 表列数据宽度,表列小数位数; //存放当前记录各字段数据宽度的数组
static int 当前记录号;
static ArrayList 读入数据=new ArrayList();
static String[][] 表格数据;//存放表格数据的数组
static String[] 关键字数据1;
static String[][] 表格数据2;
static DefaultTableModel model;
static String 表名; //数据表名
static String 字典表名;
static String 导入文件名;
static String [] 按钮集=null;
static String 要求按钮号表;
static String 字段号表;
static String 关键字;
static String[] 关键字名,关键字名1;
static int[] 关键字序号,关键字序号1;
static String s1="",s2="",s3="";
static String s4=" ";
static char x1=10,x2=13,x3='"',x4=',';
static String 类型1=main1.类型1;
static int 按钮数=0,按钮宽=0;
static JPanel panel = new JPanel(false); //定义面板
static JScrollPane scrollPane = new JScrollPane(); // 创建滚动面板
public static void means(String [] parameter) {
frame = new dataTransfer12();
窗口宽=800;窗口高=600;
记录条数=0;记录条数1=0;
读入数据=new ArrayList();
s1="";s2="";s3="";
s4=" ";
x1=10;x2=13;x3='"';x4=',';
类型1=main1.类型1;
File file = new File(".\\com\\iText-5.0.5.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
file = new File(".\\com\\jacob.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
file = new File(".\\com\\poi-3.8-20120326.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
file = new File(".\\com\\PDFBox-0.7.3.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
表名=parameter[4] ; // 数据表名
字段号表=parameter[8];
导入文件名=parameter[18];
String [] 按钮集01={"从word导入","从excel导入","从PDF文件导入","退出"};
按钮集=new String[按钮集01.length];
按钮数=0;
for (int i0=0;i0<按钮集01.length;i0++)
按钮集[i0]= 按钮集01[i0];
if (parameter[11].length()>0){
要求按钮号表=parameter[11] ;// 需要显示的按钮的顺序号
//将中文分号、冒号、句号、英文分号统一为英文分号
要求按钮号表=要求按钮号表.replaceAll(";",";");
要求按钮号表=要求按钮号表.replaceAll("。",";");
要求按钮号表=要求按钮号表.replaceAll(":",";");
要求按钮号表=要求按钮号表.replaceAll(":",";");
if (要求按钮号表.indexOf(";")>0){ //如果有按钮更名要求
String s601[]=要求按钮号表.split(",");
要求按钮号表="";
for (int i01=0;i01<s601.length;i01++){
if (s601[i01].indexOf(";")>0){
String s602[]=s601[i01].split(";");
按钮集[Integer.parseInt((s602[0]))]=s602[1];
要求按钮号表=要求按钮号表+","+s602[0];//只留号,去更名
按钮数++;
}
else {
要求按钮号表=要求按钮号表+","+s601[i01];//只留号,原无更名
按钮数++;
}
}
} //如果没有按钮更名要求,要求按钮号长度又大于0,保持原有摸样
else {
String s601[]=要求按钮号表.split(",");
要求按钮号表="";
for (int i01=0;i01<s601.length;i01++){
if (i01==0) 要求按钮号表=s601[i01];
else 要求按钮号表=要求按钮号表+","+s601[i01];
按钮数++;
}
}
}
else {
要求按钮号表=""; //没输入要求按钮号,定全部按钮号
for (int i1=0;i1<按钮集.length;i1++){
if (i1==0) 要求按钮号表=要求按钮号表+i1;
else 要求按钮号表=要求按钮号表+","+i1;
按钮数++;
}
}
要求按钮号表=","+要求按钮号表+",";
窗口宽=main1.窗口宽; 窗口高=main1.窗口高;
int m1,m2;
try{
if (parameter[17].length()==0) {
m1=0;m2=0;
}
else {
m1=Integer.parseInt(parameter[17]) ;//宽度参数
m2=Integer.parseInt(parameter[18]) ;
}
if (m1>0){
if (m2<80){
m1=m1*10;
m2=m2*10;
}
窗口宽=m1 ;
窗口高=m2 ;
}
}catch(Exception e2){}
关键字=parameter[6];
String 字段名表=parameter[19].trim();
if ((字段名表.length()>0)&&(字段名表.substring(字段名表.length()-1).equals(",")))
字段名表=字段名表.substring(0,字段名表.length()-1);
String 表名表=parameter[0];
String [] l3=字段名表.split(",");
字典表名="";
String []t1;
int b=0,c=0;
if (表名表.lastIndexOf(表名+"字典表")>=0){
t1=表名表.split(",");
for (int i=0;i<t1.length;i++)
if (t1[i].lastIndexOf(表名+"字典表")>=0) 字典表名=t1[i];
}
String[] l1;
if (字段号表.length()>0){
l1=字段号表.split(",");
int [] l2=new int[l1.length];
for (int i=0;i<l1.length;i++){
l2[i]=Integer.valueOf(l1[i]);
}
列数=l1.length;
列名=new String[2][列数];
for (int i=0;i<列数;i++){
列名[0][i]=l3[l2[i]];
列名[1][i]=列名[0][i];
}
}
else {
列数=l3.length;
列名=new String[2][列数];
for (int i=0;i<列数;i++){
列名[0][i]=l3[i];
列名[1][i]=列名[0][i];
}
}
s1="";
for (int i=0;i<列数;i++)
s1=s1+列名[0][i]+",";
s1=s1.substring(0,s1.length()-1);
try {
if ((","+表名表).lastIndexOf(","+表名+",")>=0){
con1=main1.getConn();// 连接数据库
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1="select "+s1+" from "+表名;
}
else {
JOptionPane.showMessageDialog( null, "目的数据表不存在,不能导入!");
return;
}
ResultSet rs=sta4.executeQuery(s1);
rsmd4 = rs.getMetaData();
列数 = rsmd4.getColumnCount(); // 获取列数
列名 = new String[2][列数]; // 定义列名数组
列数据类型 = new String[列数]; // 定义列字段类型数组
列数据宽度 = new int[列数]; // 定义列数据宽度
for (int i=0;i<列数;i++) {
列名[0][i] = rsmd4.getColumnName(i + 1); // 获取列名存到数组中
列名[1][i]=列名[0][i];
列数据类型[i] = rsmd4.getColumnTypeName(i + 1);
列数据宽度[i] = rsmd4.getColumnDisplaySize(i + 1);
if (列数据宽度[i]<列名[0][i].length()) 列数据宽度[i]=列名[0][i].length();
if (列数据宽度[i]<列名[1][i].length()) 列数据宽度[i]=列名[1][i].length();
if (列数据宽度[i]>50) 列数据宽度[i]=50;
}
关键字名=关键字.split(",");
关键字名1=new String[关键字名.length];
关键字序号=new int[关键字名.length];
关键字序号1=new int[关键字名.length];
表格列名=new String[列数];
表列数据类型=new String[列数];
表列数据宽度=new int[列数];
for (b=0;b<列数;b++){
表格列名[b]=列名[1][b];
表列数据宽度[b]=列数据宽度[b];
}
记录条数=0; //记录条数
rs.last();
记录条数=rs.getRow();
表格数据=new String[记录条数][列数];
rs.absolute(1);
c=0; //行号
b=0; //列号
while(c<记录条数) {
rs.absolute(c+1);
while(b<列数){
表格数据[c][b]=rs.getString(b+1);
b++;
}
c++;
b=0;
}
rs.close(); //关闭查询结果集
sta4.close(); //关闭连接
con1.close();
}
catch(SQLException e)
{
JOptionPane.showMessageDialog( null, "读取数据出错!");
}
if (字典表名.length()>0){
try
{
con1=main1.getConn();// 连接数据库
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + 字典表名;
rs = sta4.executeQuery(s1);
String s4="";
String s5="";
b=1;
while (rs.absolute(b)) {
b++;
s4=rs.getString(1); //字段名
s5=rs.getString(2); //标签名
for (int j=0;j<列数;j++){
if (列名[0][j].trim().equals(s4.trim())){
列名[1][j]=s5;
表格列名[j]=s5;
break;
}
}
}
rs.close(); //关闭查询结果集
con1.close();
sta4.close();
} catch (SQLException e)
{
JOptionPane.showMessageDialog( null, "连接数据库出错!");
}
}
for (int i=0;i<列数;i++) {
for (int j=0;j<关键字名.length;j++)
if (关键字名[j].trim().equals(列名[0][i].trim())){
关键字名1[j]=列名[1][i];
关键字序号[j]=i;
}
}
frame.setTitle("数据修改式从office文件导入部件。 作者:程学先"); // 设置窗体标题
frame.setBounds(10, 10, 窗口宽, 窗口高); // 设置窗体的位置和大小
frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE); // 设置窗体的默认关闭模式
frame.setLayout(null);
panel = new JPanel(false); //定义面板
panel.setLayout(null); //关闭面板布局管理器
panel.setBounds(10, 10,窗口宽,窗口高);
scrollPane = new JScrollPane(); // 创建滚动面板
scrollPane.setBounds(0,0,窗口宽-20,窗口高-100); //定义滚动面板大小位置
panel.add(scrollPane);
model = new DefaultTableModel(表格数据,列名[1]); // 创建表格模型
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for(int i=0;i<列数;i++) {
TableColumn tc = table.getColumn(列名[1][i]);
tc.setPreferredWidth(列数据宽度[i]*14);
tc.setMaxWidth(列数据宽度[i]*14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
int 按钮宽=(窗口宽-30)/按钮数;
int 按钮左边距=10;
JButton 从word = new JButton(按钮集[0]);
从word.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (导入文件名.length()==0){
导入文件名=JOptionPane.showInputDialog("请输入导入文件完整路径 例如:F:\\java.doc","");
return;
}
int p1=0,p2=0;
if (导入文件名.lastIndexOf(".doc")<0){
p1=导入文件名.lastIndexOf(".");
p2=导入文件名.lastIndexOf("\\\\");
if (p2>p1){
导入文件名=导入文件名+".doc";
导入文件名=导入文件名.replace("\\.","\\.\\.");
}
else {
导入文件名=导入文件名.substring(0,p1)+".doc";
}
}
try {
FileInputStream in=new FileInputStream(new File(导入文件名));
WordExtractor extractor=null;
extractor=new WordExtractor(in);
String 内容=extractor.getText();
读入数据.clear();
int m1=0;;
String[] 记录=new String[列数];
int k=0;
int k2=0,k5=0;
记录[k]="";
for (int i=0;i<内容.length();i++){
if (内容.charAt(i)=='\r'){
s3="";
for (int k3=0;k3<关键字名.length;k3++)
s3=s3+记录[关键字序号[k3]].trim();
k2=0;
for (int j1=0;j1<model.getRowCount();j1++){ //表中所有记录
s2="";
for (int k4=0;k4<关键字名.length;k4++){
s2=s2+model.getValueAt(j1,关键字序号[k4]).toString().trim();
}
if (s3.trim().equals(s2.trim())) { //找到关键字相同记录
k2=j1;
k5=1;
break;
}
}
if (k5>0){
model.removeRow(k2);
记录条数--;
}
k5=0;
model.addRow(记录);
记录[0]="";
记录条数++;
k=0;
m1=1;
if ((i<内容.length()-2)&&(内容.charAt(i+2)=='\r')) break;
}
else if (内容.charAt(i)=='\n'){}
else if ((byte)(内容.charAt(i))==9){
k++;
m1=0;
if (k<列数) 记录[k]="";
}
else if (k<列数){
记录[k]=记录[k]+内容.charAt(i);
}
}
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "读取数据出错!");
}
查询001();
}
});
if ((要求按钮号表.length()<3) || (要求按钮号表.lastIndexOf(",0,")>=0)){
从word.setBounds(按钮左边距,窗口高-80,按钮宽,20);
panel.add(从word);
按钮左边距=按钮左边距+按钮宽;
}
JButton 从excel = new JButton(按钮集[1]);
从excel.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
读入数据.clear();
if (导入文件名.length()==0){
导入文件名=JOptionPane.showInputDialog("请输入导入文件完整路径 例如:F:\\java.xls","");
return;
}
int p1=0,p2=0;
if (导入文件名.lastIndexOf(".xls")<0){
p1=导入文件名.lastIndexOf(".");
p2=导入文件名.lastIndexOf("\\\\");
if (p2>p1){
导入文件名=导入文件名+".xls";
导入文件名=导入文件名.replace("\\.","\\.\\.");
}
else {
导入文件名=导入文件名.substring(0,p1)+".xls";
}
}
InputStream is = null;
HSSFWorkbook wbWorkbook = null;
try {
is = new FileInputStream(导入文件名);
wbWorkbook = new HSSFWorkbook(is);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "读取文件失败,请检查路径是否正确!", "错误",
JOptionPane.INFORMATION_MESSAGE);
} catch (IOException e2) {
e2.printStackTrace();
}
HSSFSheet sheet = wbWorkbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
int 导入表格行数 = sheet.getPhysicalNumberOfRows();
HSSFRow row = sheet.getRow(1);
int 导入表格列数=row.getPhysicalNumberOfCells();
if (导入表格列数!=列数)
JOptionPane.showMessageDialog(null, "原文件中数据结构与数据表不同!");
String[] val;
int k2=0,k5=0;
String value = "";
for (int i = 0; i < 导入表格行数; i++) {
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < 导入表格列数; j++) {
HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
}
val = value.split(",");
for (int i1=0;i1<val.length;i1++){
读入数据.add(val[i1]);
}
k5=0;
model.addRow(val);
value = "";
}
}
记录条数1=读入数据.size()/列数;
表格数据2=new String[记录条数1][列数];
关键字数据1=new String[记录条数1];
for (int i=0;i<记录条数1;i++){
for (int j=0;j<列数;j++){
if (读入数据.get(i*列数+j)==null) 表格数据2[i][j]="";
else 表格数据2[i][j]=读入数据.get(i*列数+j).toString();
}
关键字数据1[i]="";
for (int k3=0;k3<关键字名.length;k3++)
关键字数据1[i]=关键字数据1[i]+表格数据2[i][关键字序号[k3]].trim();
}
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
int f0=0;
for (int i=0;i<记录条数1;i++) {
f0=0;
for (int i1=0;i1<记录条数;i1++){
s3="";
for (int j=0;j<关键字名.length;j++){
s3=s3+表格数据[i1][关键字序号[j]].trim();
}
if (关键字数据1[i].equals(s3)){
f0=1;
break;
}
}
if (f0==0){
s1="insert into "+表名+" (";
for (int k1=0;k1<列名[1].length;k1++)
s1=s1+列名[1][k1]+",";
s1=s1.substring(0,s1.length()-1)+" ) values (";
for (int j=0;j<列名[1].length;j++){
if (类型1.lastIndexOf(","+列数据类型[j]+",")>=0){
if (表格数据2[i][j]==null) 表格数据2[i][j]="0";
s1=s1+表格数据2[i][j].trim();
}
else{
if (表格数据2[i][j]==null) 表格数据2[i][j]=" ";
s1=s1+"'"+表格数据2[i][j].trim()+"'";
}
if (j!=列名[1].length-1) s1=s1+",";
}
s1=s1+")";
sta4.executeUpdate(s1);
}
else {
s1="update "+表名+" set ";
for (int k1=0;k1<列名[1].length;k1++){
s1=s1+列名[1][k1]+"=";
if (类型1.lastIndexOf(","+列数据类型[k1]+",")>=0){
if (表格数据2[i][k1]==null) 表格数据2[i][k1]="0";
s1=s1+表格数据2[i][k1].trim();
}
else{
if (表格数据2[i][k1]==null) 表格数据2[i][k1]=" ";
s1=s1+"'"+表格数据2[i][k1].trim()+"'";
}
if (k1!=列名[1].length-1) s1=s1+",";
}
s2=" where ";
for (int k=0;k<关键字名.length;k++){
s2=s2+关键字名1[k]+"='"+表格数据2[i][关键字序号[k]]+"'";
if (k<关键字名.length-1) s2=s2+" and ";
}
s1=s1+s2;
sta4.executeUpdate(s1);
}
}
sta4.close(); //关闭连接
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "操作出错,请检查数据表名是否重复,录入语句是否正确!"+s1);
}
查询002();
}
});
if ((要求按钮号表.length()<3) || (要求按钮号表.lastIndexOf(",1,")>=0)){
从excel.setBounds(按钮左边距,窗口高-80,按钮宽,20);
panel.add(从excel);
按钮左边距=按钮左边距+按钮宽;
}
JButton 从pdf = new JButton(按钮集[2]);
从pdf.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
读入数据.clear();
if (导入文件名.length()==0){
导入文件名=JOptionPane.showInputDialog("请输入导入文件完整路径 例如:F:\\java.pdf","");
return;
}
int p1=0,p2=0;
if (导入文件名.lastIndexOf(".pdf")<0){
p1=导入文件名.lastIndexOf(".");
p2=导入文件名.lastIndexOf("\\\\");
if (p2>p1){
导入文件名=导入文件名+".pdf";
导入文件名=导入文件名.replace("\\.","\\.\\.");
}
else {
导入文件名=导入文件名.substring(0,p1)+".pdf";
}
}
try {
FileInputStream in = new FileInputStream(导入文件名);
PDFParser parser = new PDFParser(in);
parser.parse();
PDDocument pdfdocument = parser.getPDDocument();
PDFTextStripper stripper = new PDFTextStripper();
s1 = stripper.getText(pdfdocument);
in.close();
} catch (Exception e1) {
e1.printStackTrace();
}
String[] 记录=new String[列数];
String s4="";
int k=0,m1=0,k2=0,k5=0;
记录[0]="";
int l1=s1.length();
for (int i1=0;i1<l1;i1++){
s2=s1.substring(i1,i1+1);
if (s1.charAt(i1)=='\r') {}
else if (s1.charAt(i1)=='\n') {}
else if ((s2.trim().equals("│"))&&(m1==1)) {
if (k==列数-1) {
k5=0;
读入数据.add(记录[k]);
model.addRow(记录);
k=0;
m1=0;
记录[k]="";
}
else {
读入数据.add(记录[k]);
k++;
if (k<列数) 记录[k]="";
}
}
else if (s2.trim().equals("│")) {
m1=1;
k=0;
记录[0]="";
}
else if ((k<列数)&&(m1==1)){
try{
记录[k]=记录[k]+s2;
} catch (Exception e1) {
}
}
}
记录条数1=读入数据.size()/列数;
表格数据2=new String[记录条数1][列数];
关键字数据1=new String[记录条数1];
for (int i=0;i<记录条数1;i++){
for (int j=0;j<列数;j++){
if (读入数据.get(i*列数+j)==null) 表格数据2[i][j]="";
else 表格数据2[i][j]=读入数据.get(i*列数+j).toString();
}
关键字数据1[i]="";
for (int k3=0;k3<关键字名.length;k3++)
关键字数据1[i]=关键字数据1[i]+表格数据2[i][关键字序号[k3]].trim();
}
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
int f0=0;
for (int i=0;i<记录条数1;i++) {
f0=0;
for (int i1=0;i1<记录条数;i1++){
s3="";
for (int j=0;j<关键字名.length;j++){
s3=s3+表格数据[i1][关键字序号[j]].trim();
}
if (关键字数据1[i].trim().equals(s3.trim())){
f0=1;
break;
}
}
if (f0==0){
s1="insert into "+表名+" (";
for (int k1=0;k1<列名[1].length;k1++)
s1=s1+列名[1][k1]+",";
s1=s1.substring(0,s1.length()-1)+" ) values (";
for (int j=0;j<列名[1].length;j++){
if (类型1.lastIndexOf(","+列数据类型[j]+",")>=0){
if (表格数据2[i][j]==null) 表格数据2[i][j]="0";
s1=s1+表格数据2[i][j].trim();
}
else{
if (表格数据2[i][j]==null) 表格数据2[i][j]=" ";
s1=s1+"'"+表格数据2[i][j].trim()+"'";
}
if (j!=列名[1].length-1) s1=s1+",";
}
s1=s1+")";
sta4.executeUpdate(s1);
}
else {
s1="update "+表名+" set ";
for (int k1=0;k1<列名[1].length;k1++){
s1=s1+列名[1][k1]+"=";
if (类型1.lastIndexOf(","+列数据类型[k1]+",")>=0){
if (表格数据2[i][k1]==null) 表格数据2[i][k1]="0";
s1=s1+表格数据2[i][k1].trim();
}
else{
if (表格数据2[i][k1]==null) 表格数据2[i][k1]=" ";
s1=s1+"'"+表格数据2[i][k1].trim()+"'";
}
if (k1!=列名[1].length-1) s1=s1+",";
}
s2=" where ";
for (int k4=0;k4<关键字名.length;k4++){
s2=s2+关键字名1[k4]+"='"+表格数据2[i][关键字序号[k4]]+"'";
if (k4<关键字名.length-1) s2=s2+" and ";
}
s1=s1+s2;
sta4.executeUpdate(s1);
}
}
sta4.close(); //关闭连接
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "操作出错,请检查数据表名是否重复,录入语句是否正确!"+s1);
}
查询002();
}
});
if ((要求按钮号表.length()<3) || (要求按钮号表.lastIndexOf(",2,")>=0)){
从pdf.setBounds(按钮左边距,窗口高-80,按钮宽,20);
panel.add(从pdf);
按钮左边距=按钮左边距+按钮宽;
}
JButton exitButton = new JButton(按钮集[3]);
exitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
frame.dispose();
}
});
if ((要求按钮号表.length()<3) || (要求按钮号表.lastIndexOf(",3,")>=0)){
exitButton.setBounds(按钮左边距,窗口高-80,按钮宽,20);
panel.add(exitButton);
按钮左边距=按钮左边距+按钮宽;
}
frame.add(panel);
frame.setVisible(true);
}
public static void 查询001(){
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1="delete from "+表名;
sta4.executeUpdate(s1);
sta4.close(); //关闭连接
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "删除操作出错,请检查数据表名是否正确!"+s1);
}
String s0="";
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s0="insert into "+表名+" (";
for (int k1=0;k1<列名[1].length;k1++)
s0=s0+列名[1][k1]+",";
s0=s0.substring(0,s0.length()-1)+" ) values (";
for (int i=0;i<model.getRowCount();i++){
s1=s0;
for (int j=0;j<列名[1].length;j++){
if (类型1.lastIndexOf(","+列数据类型[j]+",")>=0){
s1=s1+model.getValueAt(i,j).toString().trim();
}
else{
s1=s1+"'"+model.getValueAt(i,j).toString().trim()+"'";
}
if (j!=列名[1].length-1) s1=s1+",";
}
s1=s1+")";
sta4.executeUpdate(s1);
}
sta4.close(); //关闭连接
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "修改数据表操作出错,请检查语句是否正确!"+s1);
}
}
public static void 查询002(){
try {
con1=main1.getConn();// 连接数据库
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1="select * from "+表名;
ResultSet rs=sta4.executeQuery(s1);
rs.last();
记录条数=rs.getRow();
表格数据=new String[记录条数][列数];
rs.absolute(1);
int c=0; //行号
int b=0; //列号
while(c<记录条数) {
rs.absolute(c+1);
while(b<列数){
表格数据[c][b]=rs.getString(b+1);
b++;
}
c++;
b=0;
}
rs.close(); //关闭查询结果集
sta4.close(); //关闭连接
con1.close();
}
catch(SQLException e)
{
JOptionPane.showMessageDialog( null, "读取数据出错!");
}
model = new DefaultTableModel(表格数据,列名[1]); // 创建表格模型
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for(int i=0;i<列数;i++) {
TableColumn tc = table.getColumn(列名[1][i]);
tc.setPreferredWidth(列数据宽度[i]*14);
tc.setMaxWidth(列数据宽度[i]*14);
tc.sizeWidthToFit();
}
panel.add(scrollPane);
scrollPane.setViewportView(table);
}
}
53.源码53,部件程序,从网页导入数据到当前表某个文本型字段数据导入部件。
/**
* 程序文件名:dataTransfer13.java
* 作者:程学先
* 功能:从网页导入数据到当前表某个文本型字段部件。
* 完成日期:2014年4月22日
* 被调用语句:dataTransfer13.means(parameter)
* 必须提供的参数:"网页地址","表名","关键字段名","导入字段名”。
* 可选择参数:“要求按钮号表”。
* 导入字段应当是text或ntext类型字段。
* 本部件预设的按钮控件共5个,序号及按钮名:1.读网页内容,2.导入源程序,3.导入网页内容,
* 4.删除当前记录,5.新记录存盘,6.退出"
* 开始运行后显示当前数据表中数据情况,点击“读网页内容”会将相关网页内容屏蔽部分字符后读到文本域中。
* 之后点击表格某一行指引所存记录,再点击“导入网页内容”,将网页内容读入到数据表所指记录的文本数据字段中。
* 也可点击“导入源程序”,但只有在源程序中不存在会导致语法错误或违反访问规则的字符时操作才会成功。
* 一般不要作此操作。
*/
import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.EventQueue;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableColumn;
public class dataTransfer13 extends JFrame {
private static dataTransfer13 frame1 = new dataTransfer13();
private static JTextArea 网页内容;
private static JTextField 网页地址1;
private static int 窗口宽 = 1000;
private static int 窗口高 = 700;
private static String s1 = "", s2 = "";
private static String 网页地址 = "";
private static String 表名 = "";
private static String 网页内容0 = "";
private static String[] 关键字段名;
private static String 导入字段名 = "";
private static Connection cont4;
private static Statement stat4;
private static ResultSetMetaData rsmdt4;
static PreparedStatement pstmt;
private static ResultSet rs;
private static int 列数 = 0;
private static String[] 列名;
private static String[] 列数据类型;
private static int[] 列数据宽度;
private static int 记录条数 = 0;
private static String[] 一条空记录;
private static int 列号 = 0, 行号 = 0;
private static String[][] 表格数据;
private static DefaultTableModel 表格模型;
private static JTable 表格;
private static String[] 关键字段值;
private static int[] 关键字序号;
private static String 类型1 = main1.数字数据类型;
public static String[] sw = new String[11];
static String[] 按钮集 = null;
static String 要求按钮号表;
static int 按钮数 = 0, 按钮宽 = 0;
static void means(String parameter[]) {
sw = new String[11];
sw[1] = main1.sw1 + "";
sw[2] = main1.sw2;
sw[3] = main1.sw3; // 为了不出现0影响判断,在树程序中将工序号统加1
sw[4] = main1.sw4;
sw[5] = main1.sw5;
sw[6] = main1.sw6;
sw[7] = main1.sw7;
sw[8] = main1.sw8;
sw[9] = main1.sw9;
sw[10] = main1.sw10;
main1.sw1 = 0;
main1.sw2 = "";
main1.sw3 = "";
main1.sw4 = "";
main1.sw5 = "";
main1.sw6 = "";
main1.sw7 = "";
main1.sw8 = "";
main1.sw9 = "";
main1.sw10 = "";
String[] 按钮集01 = { "读网页内容", "导入源程序", "导入网页内容", "删除当前记录", "新记录存盘", "退出" };
按钮集 = new String[按钮集01.length];
按钮数 = 0;
for (int i0 = 0; i0 < 按钮集01.length; i0++)
按钮集[i0] = 按钮集01[i0];
if (parameter[11].length() > 0) {
要求按钮号表 = parameter[11];// 需要显示的按钮的顺序号
// 将中文分号、冒号、句号、英文分号统一为英文分号
要求按钮号表 = 要求按钮号表.replaceAll(";", ";");
要求按钮号表 = 要求按钮号表.replaceAll("。", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
if (要求按钮号表.indexOf(";") > 0) { // 如果有按钮更名要求
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
按钮集[Integer.parseInt((s602[0]))] = s602[1];
要求按钮号表 = 要求按钮号表 + "," + s602[0];// 只留号,去更名
按钮数++;
} else {
要求按钮号表 = 要求按钮号表 + "," + s601[i01];// 只留号,原无更名
按钮数++;
}
}
} // 如果没有按钮更名要求,要求按钮号长度又大于0,保持原有摸样
else {
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
要求按钮号表 = s601[i01];
else
要求按钮号表 = 要求按钮号表 + "," + s601[i01];
按钮数++;
}
}
} else {
要求按钮号表 = ""; // 没输入要求按钮号,定全部按钮号
for (int i1 = 0; i1 < 按钮集.length; i1++) {
if (i1 == 0)
要求按钮号表 = 要求按钮号表 + i1;
else
要求按钮号表 = 要求按钮号表 + "," + i1;
按钮数++;
}
}
要求按钮号表 = "," + 要求按钮号表 + ",";
窗口宽 = main1.窗口宽;
窗口高 = main1.窗口高;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);// 宽度参数
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
窗口宽 = m1;
窗口高 = m2;
}
} catch (Exception e2) {
}
网页地址 = parameter[2];
表名 = parameter[4];
关键字段名 = parameter[6].split(",");
关键字序号 = new int[关键字段名.length];
关键字段值 = new String[关键字段名.length];
导入字段名 = parameter[9];
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + 表名;
if ((sw[3].length() > 0) && (sw[4].length() > 0))
s1 = s1 + " where " + sw[4] + " = '" + sw[5] + "'";
rs = stat4.executeQuery(s1);
rs.last();
记录条数 = rs.getRow();
rsmdt4 = rs.getMetaData();
列数 = rsmdt4.getColumnCount();
列名 = new String[列数];
列数据类型 = new String[列数];
列数据宽度 = new int[列数];
一条空记录 = new String[列数];
表格数据 = new String[记录条数][列数];
for (int i = 0; i < 列数; i++) {
列名[i] = rsmdt4.getColumnName(i + 1);
列数据类型[i] = rsmdt4.getColumnTypeName(i + 1);
列数据宽度[i] = rsmdt4.getColumnDisplaySize(i + 1);
一条空记录[i] = "";
}
int c = 0, b = 0;
for (int j = 0; j < 关键字段名.length; j++)
for (int i = 0; i < 列数; i++) {
if (关键字段名[j].trim().equals(列名[i].trim())) {
关键字序号[j] = i;
break;
}
}
rs.absolute(1);
c = 0;
b = 0;
while (c < 记录条数) {
rs.absolute(c + 1);
while (b < 列数) {
表格数据[c][b] = rs.getString(b + 1);
b++;
}
c++;
b = 0;
}
rs.close();
stat4.close();
cont4.close();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "连接数据库出错!" + s1);
}
frame1.setTitle("读取网页内容到字段中。 作者:程学先");
frame1.setBounds(10, 10, 窗口宽, 窗口高);
frame1.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
frame1.getContentPane().setLayout(null); // 关闭布局管理器
final JPanel 面板 = new JPanel();
面板.setBounds(10, 10, 窗口宽 - 10, 窗口高 - 10);
面板.setLayout(null);
frame1.add(面板);
final JLabel 输入网址 = new JLabel("请输入网址:");
输入网址.setBounds(10, 10, 80, 20);
面板.add(输入网址);
网页地址1 = new JTextField(网页地址);
网页地址1.setBounds(90, 10, 700, 20);
面板.add(网页地址1);
int 左边距 = 30;
按钮宽 = (窗口宽 - 100) / 按钮数;
final JButton 读网页内容 = new JButton(按钮集[0]);
读网页内容.setBounds(左边距, 窗口高 - 110, 140, 20);
左边距 = 左边距 + 按钮宽;
if (要求按钮号表.indexOf(",0,") >= 0)
面板.add(读网页内容);
网页内容 = new JTextArea(" ", 100, 200);
网页内容.setFont(new Font("", Font.BOLD, 14));
网页内容.setLineWrap(true);
final JScrollPane scrollPane = new JScrollPane(网页内容);
scrollPane.setBounds(10, 50, 880, 200);
面板.add((Component) scrollPane);
表格模型 = new DefaultTableModel(表格数据, 列名);
表格 = new JTable(表格模型);
表格模型.addRow(一条空记录);
表格.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
表格.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < 列数; i++) {
TableColumn tc = 表格.getColumn(列名[i]);
JTableHeader header = 表格.getTableHeader();
tc.setPreferredWidth(列数据宽度[i] * 14);
tc.setMaxWidth(列数据宽度[i] * 14);
tc.sizeWidthToFit();
}
final JScrollPane scrollPane1 = new JScrollPane();
scrollPane1.setBounds(10, 270, 窗口宽 - 110, 290);
scrollPane1.setViewportView(表格);
面板.add(scrollPane1, BorderLayout.CENTER);
表格.addMouseListener(new MouseAdapter() { // 表格获得焦点
public void mouseClicked(MouseEvent e) {
int selectedRow = 表格.getSelectedRow();
行号 = 表格.getSelectedRow();
}
});
读网页内容.addActionListener(new ActionListener() {
public void actionPerformed(final ActionEvent e) {
JOptionPane.showMessageDialog(null, "分析网页需要一点时间,请耐心等待。");
String 地址 = 网页地址1.getText().trim();
URL url = null;
URLConnection conn = null;
Collection<String> 内容集合 = new ArrayList<String>();
String regex = "<[^>]+>";
String[] s4 = new String[3000];
String[] s5 = new String[1000];
char x1 = '"', x2 = 10, x3 = 13;
int n = 0, k1 = 0, k2 = 0, k3 = 0;
String candidate = "";
Pattern p = Pattern.compile(regex);
Matcher matcher = p.matcher(candidate);
int c = 0;
try {
url = new URL(地址);
conn = url.openConnection();
conn.connect();
InputStream is = conn.getInputStream();
InputStreamReader in = new InputStreamReader(is, "UTF-8");
BufferedReader br = new BufferedReader(in);
String 一行数据;
网页内容0 = "";
k1 = 0;
for (int i = 0; (一行数据 = br.readLine()) != null; i++) {
if ((k1 == 0) && (一行数据.lastIndexOf("<script") >= 0)
&& (一行数据.lastIndexOf("<\\/script") < 0)) {
k2 = 0;
k1 = 1;
s5[k2] = 一行数据;
k2++;
} else if (k1 == 1) {
if (一行数据.lastIndexOf("</script") >= 0) {
if (一行数据.lastIndexOf("name") >= 0) {
k3 = 1;
}
k1 = 0;
k2 = 0;
k3 = 0;
} else {
s5[k2] = 一行数据;
k2++;
}
if (一行数据.lastIndexOf("name") >= 0) {
s4[c] = 一行数据.replaceAll(
x1 + "name" + x1 + ":" + x1, "")
.replaceAll(x1 + ",", "");
c++;
k3 = 1;
}
} else {
s4[c] = 一行数据;
c++;
}
网页内容0 = 网页内容0 + 一行数据;
}
} catch (Exception e1) {
e1.printStackTrace();
}
s2 = "";
for (int i = 0; i < c; i++) {
s1 = s4[i];
s1 = s1.replaceAll("<[^>]+>", "");
s1 = s1.replaceAll("\\|", "");
s1 = s1.replaceAll("\\s{2}", "");
if (s1.trim().length() > 0) {
s2 = s2 + s1 + x2 + x3;
}
}
网页内容.setText(s2);
}
});
JButton 导入源程序 = new JButton(按钮集[1]);
导入源程序.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "";
for (int i = 0; i < 关键字段名.length; i++) {
if (表格模型.getValueAt(行号, 关键字序号[i]) == null) {
JOptionPane.showMessageDialog(null, "关键字不能缺少!");
return;
}
关键字段值[i] = 表格模型.getValueAt(行号, 关键字序号[i]).toString();
if (main1.类型1.lastIndexOf("," + 列数据类型[关键字序号[i]] + ",") > 0)
s1 = s1 + 关键字段名[关键字序号[i]] + "=" + 关键字段值[i];
else
s1 = s1 + 关键字段名[关键字序号[i]] + "='" + 关键字段值[i] + "' ";
if (i < 关键字段名.length - 1)
s1 = s1 + " and ";
}
s1 = "update " + 表名 + " set " + 列名[Integer.parseInt(导入字段名)]
+ " = '" + 网页内容0 + "' where " + s1;
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
});
导入源程序.setBounds(左边距, 窗口高 - 110, 按钮宽, 20);
if (要求按钮号表.indexOf(",1,") >= 0) {
左边距 = 左边距 + 按钮宽;
面板.add(导入源程序);
}
JButton 导入网页内容 = new JButton(按钮集[2]);
导入网页内容.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "";
for (int i = 0; i < 关键字段名.length; i++) {
if (表格模型.getValueAt(行号, 关键字序号[i]) == null) {
JOptionPane.showMessageDialog(null, "关键字不能缺少!");
return;
}
关键字段值[i] = 表格模型.getValueAt(行号, 关键字序号[i]).toString();
if (main1.类型1.lastIndexOf("," + 列数据类型[关键字序号[i]] + ",") >= 0)
s1 = s1 + 关键字段名[关键字序号[i]] + "=" + 关键字段值[i];
else
s1 = s1 + 关键字段名[关键字序号[i]] + "='" + 关键字段值[i] + "' ";
if (i < 关键字段名.length - 1)
s1 = s1 + " and ";
}
s1 = "update " + 表名 + " set " + 列名[Integer.parseInt(导入字段名)]
+ " = '" + 网页内容.getText() + "' where " + s1;
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
});
导入网页内容.setBounds(左边距, 窗口高 - 110, 按钮宽, 20);
if (要求按钮号表.indexOf(",2,") >= 0) {
面板.add(导入网页内容);
左边距 = 左边距 + 按钮宽;
}
JButton 删除记录 = new JButton(按钮集[3]);
删除记录.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "";
for (int i = 0; i < 关键字段名.length; i++) {
if (表格模型.getValueAt(行号, 关键字序号[i]) == null) {
JOptionPane.showMessageDialog(null, "关键字不能缺少!");
return;
}
关键字段值[i] = 表格模型.getValueAt(行号, 关键字序号[i]).toString();
if (main1.类型1.lastIndexOf("," + 列数据类型[关键字序号[i]] + ",") >= 0)
s1 = s1 + 关键字段名[关键字序号[i]] + "=" + 关键字段值[i];
else
s1 = s1 + 关键字段名[关键字序号[i]] + "='" + 关键字段值[i] + "' ";
if (i < 关键字段名.length - 1)
s1 = s1 + " and ";
}
表格模型.removeRow(行号);
s1 = "delete from " + 表名 + " where " + s1;
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
});
删除记录.setBounds(左边距, 窗口高 - 110, 按钮宽, 20);
if (要求按钮号表.indexOf(",3,") >= 0) {
左边距 = 左边距 + 按钮宽;
面板.add(删除记录);
}
JButton 添加新记录 = new JButton(按钮集[4]);
添加新记录.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "insert into " + 表名 + " (";
for (int k1 = 0; k1 < 列名.length; k1++)
s1 = s1 + 列名[k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < 列名.length; j++) {
s2 = 表格模型.getValueAt(行号, j).toString();
if (main1.数字数据类型.lastIndexOf("," + 列数据类型[j] + ",") >= 0) {
if (s2 == null)
s2 = "0";
s1 = s1 + s2;
} else {
if (s2 == null)
s2 = " ";
s1 = s1 + "'" + s2 + "'";
}
if (j != 列名.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
表格模型.addRow(一条空记录);
}
});
添加新记录.setBounds(左边距, 窗口高 - 110, 按钮宽, 20);
if (要求按钮号表.indexOf(",4,") >= 0) {
左边距 = 左边距 + 按钮宽;
面板.add(添加新记录);
}
JButton 退出 = new JButton(按钮集[5]);
退出.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if ((sw[2].length() > 0) && (sw[3].length() > 0)
&& (sw[4].length() > 0))
WorkflowDrivek1.means(sw);
frame1.setVisible(false);
frame1.dispose();
}
});
退出.setBounds(左边距, 窗口高 - 110, 按钮宽, 20);
if (要求按钮号表.indexOf(",5,") >= 0) {
左边距 = 左边距 + 按钮宽;
面板.add(退出);
}
frame1.setVisible(true);
}
}
54.源码54,部件程序,灵活导出到数据表或文件程序。以表格为界面可变换标签后作数据浏览、排序显示、组合排序显示、导出到数据库、导出到txt文件、打印等使用数据导出部件。
/**
* 程序文件名:dataTransfer16.java
* 作者:程学先
* 功能:灵活导出到数据表或文件程序。以表格为界面可变换标签后作数据浏览、排序显示、组合排序显示、导出到数据库、导出到txt文件、打印等使用。
* 完成日期:2013年1月22日
* 被调用语句:dataTransfer16.TableModel();
* 必须提供的参数:数据“表名”、“关键字”。
* 可选择参数:“字段号表”、“要求按钮号表”、“打印格式文件名”。
* 本程序允许借用字典表变换标签内容,默认字典表名为表名加“字典表”。
* 导出文件或表名在运行时由操作者选择决定。
* 关键字段名为识别与区分记录的主要标志,不允许重复,不允许为空,可以是单一字段,也可以是多个字段,
* 如果是多字段,以英文逗号分隔。如果不要求导出到数据库,可以不给具体值。
* 要求字段号表:所操作数据可以是表的全部字段,也可以只涉及部分字段。如果选择全部字段,初始值设为"";
* 否则写入所有将涉及的字段在表中的顺序后,号与号之间以英文逗号分隔。
* 本部件预设的按钮控件共5个,序号及按钮名:1排序、2接续排序、3导出到数据库、4导出到txt文件、5打印、6退出
* 在入口处通过“要求按钮号表”选择按钮以实现所需要的功能。
* 排序:要求先点击表格中某一列,将按本列数据从小到大对全表格数据排序。按本按钮后,标签变为还原,再按后恢复原表数据模样。
* 接续排序:在按“排序”按钮后本按钮激活,如果先点某一列,则会在上一次排序基础上对于上一排序列中值相同者按当前列继续排序。
* 导出到数据库:将当前显示数据导出到一个数据表中。将提问表名,输入表名后会在当前数据库中查是否存在该表?
* 如果已经存在该表,会检查字段匹配情况。
* 如果完全匹配,则提问导入方式:1、删除原表数据,用新表覆盖。2、与原表数据关键字相同者用新数据覆盖,否则添加。3、全部添加到原表尾。
* 如果不完全匹配,则先提问:是只导入匹配部分的数据,还是补充添加那些原表中不存在的字段后再导入。之后提问导入方式。
* 如果数据库中没有该表,将表格中数据全部复制存到新表中。
* 导出到txt文件:将提问导出格式:1、每字段值一行导出。2、各字段按设定长度全部以字符串形式导出,每条记录一行,字段数据间无分隔。
* 3、每条记录一行,数字各类型数据以10进制数据形式表示;其他类型数据均以字符串形式表示,加双引号与逗号分隔。
* 4、提问分隔符,每条记录一行,各类型数据以自然形式表示,字符串类型数据也不加双引号,字段间加所输入的分隔符分隔。
* 分隔符可以由多个字符组成,例如:####
* 打印:如果提供了表格式打印格式文件名,调用dataPreview1程序组织打印。
* 否则,提供JDK的print类打印本表格数据。
*/
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.io.File.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
public class dataTransfer16 extends JFrame {
private static JTable table; // 创建表格
static Connection con; // 连接数据库
static String 表名 = main1.表名; // 数据表名
static String 字典表名 = "";
static int 窗口宽 = main1.窗口宽, 窗口高 = main1.窗口高;// 窗口宽、窗口高
static int 列数; // 数据表列数
static int 表格列数;
static String[] 列名; // 数据表列名
static String[] 表格列名;
static String[] 列标题;
static String[] 列数据类型; // 存放当前记录各字段数据类型的数组
static String[] 表列数据类型;
static int[] 列数据宽度, 列小数位数; // 存放当前记录各字段数据宽度的数组
static int[] 表列数据宽度;
static int 记录条数, 记录条数1 = 0; // 记录条数
static String[][] 表格数据;// 存放表格数据的数组
static String[][] 备份数据;
static String 要求字段号表; // 需要显示的字段的顺序号
static JButton sortButton1, sortButton, printButton, exitButton;
static JButton 导出1, 导出2, 导出3;
static String[][] 排序数据;
static String[][] 排序备份;
static int 起始序号 = 0, 终了序号 = 0, 当前列号 = 0, 上次列号 = 0;
static String[] 一条空记录;
static String[] 关键字段值;
static int[] 关键字序号;
static int selectedRow = 0;
static String 表格标题;
static String 表名表;
static String 关键字段名;
static String 打印格式文件名;
static String[] 按钮集 = null;
static String 要求按钮号表;
static int 按钮数 = 0, 按钮宽 = 0;
static DefaultTableModel model;
static String[] rowValues = new String[表格列数];
static String 导出表名 = "", 导出文件名 = "";
static String s1 = "", s2 = "", s3 = "";
static String[] 当前字段值;
static PreparedStatement pstmt;
static ResultSetMetaData rsmd3;
static Statement sta3;
public static void means(String[] parameter) {
表名 = main1.表名; // 数据表名
窗口宽 = main1.窗口宽;
窗口高 = main1.窗口高;// 窗口宽、窗口高
记录条数1 = 0; // 记录条数
起始序号 = 0;
终了序号 = 0;
当前列号 = 0;
上次列号 = 0;
selectedRow = 0;
rowValues = new String[表格列数];
s1 = "";
s2 = "";
s3 = "";
表名 = parameter[4]; // 数据表名
表名表 = parameter[0];
表格标题 = 表名;
要求字段号表 = parameter[8];
打印格式文件名 = parameter[15];
String[] 按钮集01 = { "排序", "接续排序", "打印", "导出到数据表", "导出到txt文件", "退出" };
按钮集 = new String[按钮集01.length];
按钮数 = 0;
for (int i0 = 0; i0 < 按钮集01.length; i0++)
按钮集[i0] = 按钮集01[i0];
if (parameter[11].length() > 0) {
要求按钮号表 = parameter[11];// 需要显示的按钮的顺序号
// 将中文分号、冒号、句号、英文分号统一为英文分号
要求按钮号表 = 要求按钮号表.replaceAll(";", ";");
要求按钮号表 = 要求按钮号表.replaceAll("。", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
if (要求按钮号表.indexOf(";") > 0) { // 如果有按钮更名要求
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
按钮集[Integer.parseInt((s602[0]))] = s602[1];
要求按钮号表 = 要求按钮号表 + "," + s602[0];// 只留号,去更名
按钮数++;
} else {
要求按钮号表 = 要求按钮号表 + "," + s601[i01];// 只留号,原无更名
按钮数++;
}
}
} // 如果没有按钮更名要求,要求按钮号长度又大于0,保持原有摸样
else {
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
要求按钮号表 = s601[i01];
else
要求按钮号表 = 要求按钮号表 + "," + s601[i01];
按钮数++;
}
}
} else {
要求按钮号表 = ""; // 没输入要求按钮号,定全部按钮号
for (int i1 = 0; i1 < 按钮集.length; i1++) {
if (i1 == 0)
要求按钮号表 = 要求按钮号表 + i1;
else
要求按钮号表 = 要求按钮号表 + "," + i1;
按钮数++;
}
}
要求按钮号表 = "," + 要求按钮号表 + ",";
窗口宽 = main1.窗口宽;
窗口高 = main1.窗口高;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);// 宽度参数
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
窗口宽 = m1;
窗口高 = m2;
}
} catch (Exception e2) {
}
关键字段名 = parameter[6];
try {
con = main1.getConn();// 连接数据库
sta3 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = sta3.executeQuery("select * from " + 表名);
rsmd3 = rs.getMetaData();
列数 = rsmd3.getColumnCount(); // 获取列数
列名 = new String[列数]; // 定义列名数组
列数据类型 = new String[列数]; // 定义列字段类型数组
列数据宽度 = new int[列数]; // 定义列数据宽度
for (int i = 0; i < 列数; i++) {
列名[i] = rsmd3.getColumnName(i + 1); // 获取列名存到数组中
列数据类型[i] = rsmd3.getColumnTypeName(i + 1); // 获取列数据类型名存到数组中
列数据宽度[i] = rsmd3.getPrecision(i + 1); // 获取列数据宽度存到数组中
}
rs.last();
记录条数 = rs.getRow();
if ((要求字段号表 != null) && (要求字段号表.length() > 0)) {
String[] f1 = 要求字段号表.split(",");
表格列数 = f1.length;
表格列名 = new String[表格列数];
表列数据类型 = new String[表格列数];
表列数据宽度 = new int[表格列数];
for (int i = 0; i < 表格列数; i++) {
表格列名[i] = 列名[Integer.valueOf(f1[i])];
表列数据类型[i] = 列数据类型[Integer.valueOf(f1[i])];
表列数据宽度[i] = 列数据宽度[Integer.valueOf(f1[i])];
s2 = s2 + 表格列名[i] + ",";
}
s2 = s2.substring(0, s2.length() - 1);
表格数据 = new String[记录条数][表格列数];
rs = sta3.executeQuery("select " + s2 + " from " + 表名);
rsmd3 = rs.getMetaData();
} else {
表格列数 = 列数;
表格列名 = new String[表格列数];
表列数据类型 = new String[表格列数];
表列数据宽度 = new int[表格列数];
for (int i = 0; i < 表格列数; i++) {
表格列名[i] = 列名[i];
表列数据类型[i] = 列数据类型[i];
表列数据宽度[i] = 列数据宽度[i];
}
表格数据 = new String[记录条数][列数];
}
rs.absolute(1);
for (int i = 0; i < 记录条数; i++) {
rs.absolute(i + 1);
for (int j = 0; j < 表格列数; j++) {
表格数据[i][j] = rs.getString(j + 1);
}
}
rs.close(); // 关闭查询结果集
sta3.close(); // 关闭连接
} catch (Exception e) {
e.printStackTrace();
}
备份数据 = new String[记录条数][表格列数];
要求按钮号表 = "," + 要求按钮号表 + ",";
列标题 = new String[表格列数];
int b = 0; // 列号
while (b < 表格列数) {
列标题[b] = 表格列名[b];
b++;
}
if (表名表.indexOf(表名 + "字典表") >= 0)
字典表名 = 表名 + "字典表";
else
字典表名 = "";
if (字典表名.length() > 0) {
con = main1.getConn();
try {
Statement sta = con.createStatement(
// 建立连接
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + 字典表名;
ResultSet rs = sta.executeQuery(s1);// 执行查询,获得查询结果集
while (rs.next()) {
String 字段名称 = rs.getString(1);
String 标签名称 = rs.getString(2);
b = 0;
while (b < 表格列数) {
if (列标题[b].compareTo(字段名称) == 0) {
列标题[b] = 标签名称;
break;
}
b++;
}
}
rs.close(); // 关闭查询结果集
sta.close(); // 关闭连接
} catch (Exception e1) {
e1.printStackTrace();
}
}
dataTransfer16 frame = new dataTransfer16(); // 创建窗体
frame.setTitle("表格式显示部件之1 作者:程学先"); // 设置窗体标题
frame.setBounds(10, 10, 窗口宽, 窗口高); // 设置窗体的位置和大小
frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE); // 设置窗体的默认关闭模式
final JScrollPane scrollPane = new JScrollPane(); // 创建滚动面板
scrollPane.setBounds(0, 0, 窗口宽 - 100, 窗口高 - 100); // 定义滚动面板大小位置
frame.setLayout(null);
frame.getContentPane().add(scrollPane, BorderLayout.CENTER); // 在窗体中央添加滚动面板
model = new DefaultTableModel(表格数据, 列标题); // 创建表格模型
for (int c = 0; c < 记录条数; c++)
for (b = 0; b < 表格列数; b++)
备份数据[c][b] = 表格数据[c][b];
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < 表格列数; i++) {
TableColumn tc = table.getColumn(列标题[i]);
JTableHeader header = table.getTableHeader();
tc.setPreferredWidth(表列数据宽度[i] * 14);
tc.setMaxWidth(表列数据宽度[i] * 14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
frame.setVisible(true); // 显示窗体
table.addMouseListener(new MouseAdapter() {
public void mouseClicked(MouseEvent e) {
selectedRow = table.getSelectedRow();
}
});
int 按钮宽 = (窗口宽 - 30) / 按钮数;
int 按钮左边距 = 10;
排序数据 = new String[记录条数][表格列数 + 1];
排序备份 = new String[记录条数][表格列数 + 1];
for (b = 0; b < 表格列数; b++) {
for (int c = 0; c < 记录条数; c++) {
if (备份数据[c][b] == null)
排序数据[c][b] = "";
else
排序数据[c][b] = 备份数据[c][b];
}
if (表列数据类型[b].compareTo("int") == 0) {
for (int c = 0; c < 记录条数; c++) {
排序数据[c][b] = "0000000000" + 排序数据[c][b];
排序数据[c][b] = 排序数据[c][b].substring(排序数据[c][b].length() - 10);
}
} else if ((表列数据类型[b].compareTo("float") == 0)
|| (表列数据类型[b].compareTo("numeric") == 0)) {
for (int c = 0; c < 记录条数; c++) {
排序数据[c][b] = "0000000000" + 排序数据[c][b];
int k1 = 排序数据[c][b].lastIndexOf('.');
if (k1 == -1) {
k1 = 排序数据[c][b].length();
排序数据[c][b] = 排序数据[c][b] + ".000000";
} else
排序数据[c][b] = 排序数据[c][b] + "000000";
排序数据[c][b] = 排序数据[c][b].substring(k1 - 10, k1 + 1)
+ 排序数据[c][b].substring(k1 + 1, k1 + 6);
}
}
}
for (int c = 0; c < 记录条数; c++)
排序数据[c][表格列数] = "" + c;
for (int c = 0; c < 记录条数; c++)
for (b = 0; b < 表格列数 + 1; b++)
排序备份[c][b] = 排序数据[c][b];
起始序号 = 0;
终了序号 = 0;
sortButton = new JButton(按钮集[0]);
sortButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
rowValues = new String[表格列数];
if (sortButton.getText().trim().equals("排序")) {
if (table.getSelectedColumn() < 0)
JOptionPane.showMessageDialog(null,
"请先点击要排序的某一列,再按本按钮!");
else {
sortButton.setText("还原");
sortButton1.setEnabled(true);
上次列号 = table.getSelectedColumn();
当前列号 = table.getSelectedColumn();
}
String s;
int i, j, k = 0;
for (int c = 0; c < 记录条数; c++) {
for (i = 0; i < 记录条数 - c - 1; i++) {
if (排序数据[i][当前列号].compareTo(排序数据[i + 1][当前列号]) > 0)
for (j = 0; j <= 表格列数; j++) {
s = 排序数据[i][j];
排序数据[i][j] = 排序数据[i + 1][j];
排序数据[i + 1][j] = s;
}
}
}
model.setRowCount(0);
for (int c = 0; c < 记录条数; c++) {
int n = Integer.parseInt(排序数据[c][表格列数]);
for (int b = 0; b < 表格列数; b++) {
表格数据[c][b] = 备份数据[n][b];
rowValues[b] = 备份数据[n][b];
}
model.addRow(rowValues);
}
} else {
sortButton.setText("排序");
sortButton1.setEnabled(false);
model.setRowCount(0);
for (int c = 0; c < 记录条数; c++) {
for (int b = 0; b < 表格列数; b++) {
rowValues[b] = 备份数据[c][b];
排序数据[c][b] = 排序备份[c][b];
}
model.addRow(rowValues);
排序数据[c][表格列数] = 排序备份[c][表格列数];
}
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",0,") >= 0)) {
sortButton.setBounds(按钮左边距, 窗口高 - 80, 按钮宽 - 10, 20);
frame.add(sortButton);
按钮左边距 = 按钮左边距 + 按钮宽;
}
sortButton1 = new JButton(按钮集[1]);
sortButton1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
当前列号 = table.getSelectedColumn();
String s = "", s2 = 排序数据[0][上次列号];
int n1 = 0, n2 = 0;
int i, j, k = 0;
for (int c = 0; c < 记录条数; c++) {
if ((c == 记录条数 - 1)
|| (s2.compareTo(排序数据[c + 1][上次列号]) != 0)) {
if (c < 记录条数 - 1)
s2 = 排序数据[c + 1][上次列号];
n2 = c;
for (i = n1; i < n2; i++) {
for (j = n1; j < n2 + n1 - i; j++) {
if (排序数据[j][当前列号].compareTo(排序数据[j + 1][当前列号]) > 0)
for (k = 0; k <= 表格列数; k++) {
s = 排序数据[j][k];
排序数据[j][k] = 排序数据[j + 1][k];
排序数据[j + 1][k] = s;
}
}
}
n1 = n2 + 1;
}
}
上次列号 = 当前列号;
model.setRowCount(0);
for (int c = 0; c < 记录条数; c++) {
int n = Integer.parseInt(排序数据[c][表格列数]);
for (int b = 0; b < 表格列数; b++) {
rowValues[b] = 备份数据[n][b];
}
model.addRow(rowValues);
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",1,") >= 0)) {
sortButton1.setBounds(按钮左边距, 窗口高 - 80, 按钮宽 - 5, 20);
frame.add(sortButton1);
按钮左边距 = 按钮左边距 + 按钮宽;
}
sortButton1.setEnabled(false);
printButton = new JButton(按钮集[2]);
printButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
if (打印格式文件名.trim().length() == 0)
table.print();
else
dataPreview1.printView1(表格数据, 打印格式文件名);
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "打印失败!");
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",2,") >= 0)) {
printButton.setBounds(按钮左边距, 窗口高 - 80, 按钮宽 - 5, 20);
frame.add(printButton);
按钮左边距 = 按钮左边距 + 按钮宽;
}
导出1 = new JButton(按钮集[3]);
导出1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
int k = 0;
int k0 = 0;
String[] 导出表列名;
String[] 不匹配列名;
int[] 不匹配序号;
int[] 匹配字段序号 = new int[表格列数];
int 匹配字段数 = 0;
if (导出表名.length() == 0)
导出表名 = JOptionPane.showInputDialog("请输入数据表名称", "");
k = 0;
if (("," + 表名表 + ",").lastIndexOf(导出表名) >= 0)
k = 1;
if (k == 0) {
s1 = "select * into " + 导出表名 + " from " + 表名;
try {
con = main1.getConn();
if (!con.isClosed()) {
Statement rsmd = con.createStatement();
rsmd.executeUpdate(s1);
}
} catch (Exception e1) {
e1.printStackTrace();
}
return; // 完成导入
}
if (k != 0) {
con = main1.getConn();
s1 = "select * from " + 导出表名;
try {
Statement sta = con.createStatement(
// 建立连接
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = sta.executeQuery(s1);
ResultSetMetaData rsmd = rs.getMetaData();
int 导出表列数 = rsmd.getColumnCount(); // 获取列数
导出表列名 = new String[导出表列数]; // 欲导入表结构
不匹配列名 = new String[导出表列数]; // 准备保存未找到的字段名
不匹配序号 = new int[导出表列数]; // 准备保存未找到的字段在表格中序号
int b0 = 0;
while (b0 < 导出表列数) {
导出表列名[b0] = rsmd.getColumnName(b0 + 1); // 获取列名存到数组中
b0++;
}
int k1 = 0;
匹配字段数 = 0;
String 类型0 = "";
for (int i = 0; i < 表格列数; i++) {
k = 0;
for (int j = 0; j < 导出表列数; j++)
if (表格列名[i].compareTo(导出表列名[j]) == 0) {
k = 1;
break;
}
if (k == 0) {
不匹配列名[k1] = 表格列名[i];
不匹配序号[k1] = i;
k1++;
} else {
匹配字段序号[匹配字段数] = i;
匹配字段数++;
}
}
String[] options = new String[] { "在导出表中增加这些字段",
"只导出同名字的字段值", "退出" };
int num = 0;
if (不匹配列名.length > 0) {
num = JOptionPane.showOptionDialog(null,
"有些字段在导出目标表中不存在,请定处理方式?", "选择字段匹配方式",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE, null,
options, "只导出同名字的字段值");
if (options[num].trim().equals("退出"))
return;
if (options[num].trim().equals("在导出表中增加这些字段")) {
try {
con = main1.getConn(); // 调用前面方法连接数据库
if (!con.isClosed()) {
Statement rsmd1 = con.createStatement();
for (int i = 0; i < k1; i++) {
类型0 = 表列数据类型[不匹配序号[i]];
s1 = "ALTER TABLE " + 导出表名+ " ADD " + 不匹配列名[i]
+ " " + 类型0;
if (类型0.lastIndexOf("char") >= 0)
s1 = s1+ "("+ ("" + 表列数据宽度[不匹配序号[i]])+ ")";
else if ((类型0.compareTo("decimal") == 0)
|| (类型0.compareTo("numeric") == 0))
s1 = s1+ "("+ ("" + 表列数据宽度[不匹配序号[i]])
+ "," + 列小数位数[不匹配序号[i]]+ ")";
匹配字段序号[匹配字段数] = 不匹配序号[i];
匹配字段数++;
rsmd1.executeUpdate(s1);
}
for (int i = 0; i < 匹配字段数; i++) {
System.out.println(匹配字段序号[i]);
}
}
} catch (Exception e1) {
e1.printStackTrace();
}
}
}
rs.close(); // 关闭查询结果集
sta.close(); // 关闭连接
} catch (Exception e1) {
e1.printStackTrace();
}
}
String[] options = new String[] { "覆盖原表数据", "替代原记录", "添加到原表尾",
"退出" };
int num = JOptionPane.showOptionDialog(null,
"是否复盖原表内容(原表数据将删除)?", "选择导出方式",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, options,
"覆盖原表数据");
if (options[num].trim().equals("退出"))
return;
if (options[num].trim().equals("替代原记录"))
k0 = 1;
if (options[num].trim().equals("覆盖原表数据")) {
con = main1.getConn();
s1 = "delete from " + 导出表名;
try {
sta3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = con.prepareStatement(s1);
pstmt.execute();
con.close();
sta3.close();
} catch (Exception e2) {
e2.printStackTrace();
}
for (int i = 0; i < 记录条数; i++)
dataTransfer16.insert1(i);
}
if (k0 == 0) {
String s3 = "(";
for (int i = 0; i < 匹配字段数; i++) { // 字段序号
s3 = s3 + 列名[匹配字段序号[i]];
if (i < 匹配字段数 - 1)
s3 = s3 + ",";
else
s3 = s3 + ")";
}
String sp = "";
for (int j = 0; j < 记录条数; j++) {
s1 = "";
for (int i = 0; i < 匹配字段数; i++) { // 字段序号
int k1 = 匹配字段序号[i];
sp = 表格数据[j][k1];
if (sp == null)
sp = "";
if ((表列数据类型[k1].lastIndexOf("int") >= 0)
|| (表列数据类型[k1].compareTo("decimal") == 0)
|| (表列数据类型[k1].compareTo("numeric") == 0)) {
if (sp.length() == 0)
sp = "0";
s1 = s1 + sp;
} else {
s1 = s1 + "'" + sp + "'";
}
if (i < 匹配字段数 - 1)
s1 = s1 + ",";
}
s1 = "insert into " + 导出表名 + s3 + " values (" + s1
+ ")";
try {
pstmt = con.prepareStatement(s1);
pstmt.execute();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
else {
con = main1.getConn(); // 连接数据库
try {
sta3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "";
int j = 0;
String[] 关键字段名1 = 关键字段名.split(",");
for (int i = 0; i < 关键字段名1.length; i++) {
关键字段值[i] = model.getValueAt(selectedRow, 关键字序号[i])
.toString();// 取当前行关键字值
if (关键字段值[i].compareTo(" ") < 0)
j = -1;
s1 = s1 + 关键字段名1[关键字序号[i]] + "='" + 关键字段值[i] + "' ";
if (i < 关键字段名1.length - 1)
s1 = s1 + " and ";
}
if (j >= 0) {
s3 = " where " + s1;
s1 = "select * from " + 表名 + " where " + s1;
ResultSet rs = sta3.executeQuery(s1);
rs.last();
记录条数1 = rs.getRow();
s1 = "";
s2 = " set ";
for (int i = 0; i < 列数; i++) {
当前字段值[i] = model.getValueAt(selectedRow, i)
.toString();
if (当前字段值[i] == null)
当前字段值[i] = "";
if ((表列数据类型[i].lastIndexOf("int") >= 0)
|| (表列数据类型[i].compareTo("decimal") == 0)
|| (表列数据类型[i].compareTo("numeric") == 0)) {
if (当前字段值[i].length() == 0)
当前字段值[i] = "0";
s1 = s1 + 当前字段值[i];
s2 = s2 + 列名[i] + "=" + 当前字段值[i];
} else {
s1 = s1 + "'" + 当前字段值[i] + "'";
s2 = s2 + 列名[i] + "='" + 当前字段值[i] + "'";
}
if (i < 列数 - 1) {
s1 = s1 + ",";
s2 = s2 + ",";
}
}
if (记录条数1 == 0) {
s1 = "insert into " + 表名 + " values (" + s1
+ ")";
} else {
s1 = "update " + 表名 + s2 + s3;
}
pstmt = con.prepareStatement(s1);
pstmt.execute();
rs.close();
con.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",3,") >= 0)) {
导出1.setBounds(按钮左边距, 窗口高 - 80, 按钮宽 - 5, 20);
frame.add(导出1);
按钮左边距 = 按钮左边距 + 按钮宽;
}
导出2 = new JButton(按钮集[4]);
导出2.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (导出文件名.length() == 0)
导出文件名 = JOptionPane.showInputDialog("请输入导出文件名名称", "");
String[] options = new String[] { "每字段数据一行", "定长等长每记录一行",
"逗号分隔", "自定义符号分隔", "退出" };
int num = JOptionPane.showOptionDialog(null, "是否按每字段数据一行?",
"选择导出文件格式", JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, options,
"覆盖原表数据");
if (options[num].trim().equals("退出"))
return;
else if (options[num].trim().equals("每字段数据一行")) {
try {
File file = new File(导出文件名);
if (!file.exists()) {
file.createNewFile();
}
char x1 = 10, x2 = 13;
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < 记录条数; j1++)
for (int j2 = 0; j2 < 表格列数; j2++) {
fs.write((表格数据[j1][j2] + x2).getBytes());
}
fs.close();// 释放资源
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
}
else if (options[num].trim().equals("定长等长每记录一行")) {
try {
File file = new File(导出文件名);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < 记录条数; j1++)
for (int j2 = 0; j2 < 表格列数; j2++) {
fs.write(表格数据[j1][j2].getBytes());
}
fs.close();// 释放资源
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
} else if (options[num].trim().equals("逗号分隔")) {
try {
File file = new File(导出文件名);
if (!file.exists()) {
file.createNewFile();
}
int length = 0;
char x1 = 10, x2 = 13, x3 = '"', x4 = ',';
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < 记录条数; j1++) {
for (int j2 = 0; j2 < 表格列数; j2++)
if (j2 < (表格列数 - 1))
fs.write((x3 + 表格数据[j1][j2] + x3 + x4)
.getBytes());
else
fs.write((x3 + 表格数据[j1][j2] + x3)
.getBytes());
fs.write(("" + x1).getBytes());
}
fs.close();// 释放资源
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
} else {
try {
File file = new File(导出文件名);
if (!file.exists()) {
file.createNewFile();
}
int length = 0;
char x1 = 10, x2 = 13, x3 = '"', x4 = ',';
String x5 = JOptionPane.showInputDialog("请输入字段与字段间分隔符","");
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < 记录条数; j1++) {
for (int j2 = 0; j2 < 表格列数; j2++)
if (j2 < (表格列数 - 1))
fs.write((表格数据[j1][j2] + x5).getBytes());
else
fs.write(表格数据[j1][j2].getBytes());
fs.write(("" + x1).getBytes());
}
fs.close();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
}
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",4,") >= 0)) {
导出2.setBounds(按钮左边距, 窗口高 - 80, 按钮宽 - 5, 20);
frame.add(导出2);
按钮左边距 = 按钮左边距 + 按钮宽;
}
exitButton = new JButton(按钮集[5]);
exitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
System.exit(0);
}
});
if ((要求按钮号表.length() < 3) || (要求按钮号表.lastIndexOf(",5,") >= 0)) {
exitButton.setBounds(按钮左边距, 窗口高 - 80, 按钮宽 - 5, 20);
frame.add(exitButton);
按钮左边距 = 按钮左边距 + 按钮宽;
}
}
static void insert1(int no1) {
s1 = "";
for (int i = 0; i < 列数; i++) {
当前字段值[i] = model.getValueAt(no1, i).toString();
if (当前字段值[i] == null)
当前字段值[i] = "";
if ((表列数据类型[i].lastIndexOf("int") >= 0)
|| (表列数据类型[i].compareTo("decimal") == 0)
|| (表列数据类型[i].compareTo("numeric") == 0)) {
if (当前字段值[i].length() == 0)
当前字段值[i] = "0";
s1 = s1 + 当前字段值[i];
} else {
s1 = s1 + "'" + 当前字段值[i] + "'";
if (i < 列数 - 1)
s1 = s1 + ",";
}
s1 = "insert into " + 表名 + " values (" + s1 + ")";
try {
pstmt = con.prepareStatement(s1);
pstmt.execute();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
55.源码55,部件程序,从其他数据库中的数据表导入数据到当前系统数据库中,可变换标签内容。数据导入部件。
/**
* 程序文件名:dataTransfer17.java
* 作者:程学先
* 功能:从其他数据库中的数据表导入数据到当前系统数据库中,可变换标签内容。
* 完成日期:2012年12月12日 被调用语句:dataTransfer17.TableModel();
* 必须提供的参数:目的数据“表名”、如果修改式导入必须输入“关键字”、源数据“表名”。
* 可选择参数:“字段号表”、“要求按钮号表”、“导入条件式”、另一个数据库的“DBMS名称”、“ODBC数据源”名。 要求字段号表:源数据表中所选字段号
* 本程序允许借用字典表变换标签内容,默认字典表名为表名加“字典表”,程序自动测试源数据表结构,
* 如果字段名有出入,应通过字典表说明,其第1个字段为源表中字段名,第二个字段为对应的目的表字段名。 条件表达式为SQL语句承认的条件式,例如 学号 like
* '%20%' 根据源于目的表所在DBMS的不同,程序将自动分析数据类型的变换,但由于彼此可能存在多对多关系
* 自动分析的对应关系不一定准确,如果不合变换要求,需要操作者输入对应类型关系,程序提供对话框修改,
* 程序设置:建新表并导入、覆盖式导入、添加式导入、修改式导入、退出等5个按钮。 如果修改式导入,必须提供关键字。
*/
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
public class dataTransfer17 extends JFrame {
static dataTransfer17 frame = new dataTransfer17(); // 创建窗体
private static JTable table; // 创建表格,显示导入后数据表中年数据情况
static String DBMS名称;
static Connection con;
static Connection con1;
static String ODBC数据源;
static String url;
private static Statement stat3;
private static Statement stat4;
private static ResultSetMetaData rsmd4;
private static ResultSet rs;
static String 表名;
static String 表名1;
static String 字典表名 = "";
static String s1 = "", s2 = "", s3 = "";
static String[] 关键字段名;
private static String 关键字段值;
private static int[] 关键字序号;
static int 窗口宽 = 900, 窗口高 = 600;// 窗口宽、窗口高
static int 列数;
static int 表格列数;
static int 表格列数1;
static String 表名表;
static String[] 列名;
static String[] 表格列名;
static String[] 表格列名1;
static String[] 列标题;
static String[] 列数据类型;
static String[] 表列数据类型;
static String[] 表列数据类型1;
static int[] 列数据宽度;
static int[] 表列数据宽度;
static int[] 表列数据宽度1;
static int 记录条数, 记录条数1;
private static int[] 列序号;
static Object[][] 表格数据1;
static Object[][] 表格数据;
static String[] 按钮集 = null;
static String 要求按钮号表;
static String 要求字段号表;
static String 条件表达式;
private static String[] 原始表格列名;
private static String[] 一条空记录;
private static String[][] 类型对照表 = {
{ "java", "string", "string", "string", "int", "int", "string",
"string", "string", "boolean", "byte", "short", "long",
"float", "int", "double", "float", "float", "byte[]" },
{ "sqlserver", "char", "nchar", "nvarchar", "int", "float",
"datetime", "datetime", "text", "bit", "tinyint",
"smallint", "bigint", "numeric", "integer", "double",
"money", "real", "image" },
{ "vfp", "c", "c", "c", "i", "n", "d", "t", "m", "l", "i", "i",
"i", "n", "i", "n", "b", "y", "g" },
{ "access", "text", "text", "text", "integer", "integer", "date",
"date", "memo", "logical", "integer", "integer", "integer",
"float", "integer", "float", "float", "float", "general" },
{ "oracle", "char", "char", "char", "number", "number", "date",
"date", "long", "char", "raw", "number", "number",
"number", "number", "number", "number", "number", "blob" },
{ "mysql", "char", "char", "varchar", "int", "float", "date",
"datetime", "text", "tinyint", "tinyint", "smallint",
"bigint", "float", "integer", "double", "float", "float",
"blob" } };
static int 按钮数 = 0, 按钮宽 = 0;
public static void means(String[] parameter) {
DBMS名称 = parameter[1];
ODBC数据源 = parameter[3];
表名 = parameter[4];
if (parameter[6].trim().length() > 0)
关键字段名 = parameter[6].split(",");
else
关键字段名 = new String[0];
关键字序号 = new int[关键字段名.length];
String[] 按钮集01 = { "建新表并导入", "复盖式导入", "添加式导入", "修改式导入", "退出" };
按钮集 = new String[按钮集01.length];
按钮数 = 0;
for (int i0 = 0; i0 < 按钮集01.length; i0++)
按钮集[i0] = 按钮集01[i0];
if (parameter[11].length() > 0) {
要求按钮号表 = parameter[11];// 需要显示的按钮的顺序号
// 将中文分号、冒号、句号、英文分号统一为英文分号
要求按钮号表 = 要求按钮号表.replaceAll(";", ";");
要求按钮号表 = 要求按钮号表.replaceAll("。", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
要求按钮号表 = 要求按钮号表.replaceAll(":", ";");
if (要求按钮号表.indexOf(";") > 0) { // 如果有按钮更名要求
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
按钮集[Integer.parseInt((s602[0]))] = s602[1];
要求按钮号表 = 要求按钮号表 + "," + s602[0];// 只留号,去更名
按钮数++;
} else {
要求按钮号表 = 要求按钮号表 + "," + s601[i01];// 只留号,原无更名
按钮数++;
}
}
} // 如果没有按钮更名要求,要求按钮号长度又大于0,保持原有摸样
else {
String s601[] = 要求按钮号表.split(",");
要求按钮号表 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
要求按钮号表 = s601[i01];
else
要求按钮号表 = 要求按钮号表 + "," + s601[i01];
按钮数++;
}
}
} else {
要求按钮号表 = ""; // 没输入要求按钮号,定全部按钮号
for (int i1 = 0; i1 < 按钮集.length; i1++) {
if (i1 == 0)
要求按钮号表 = 要求按钮号表 + i1;
else
要求按钮号表 = 要求按钮号表 + "," + i1;
按钮数++;
}
}
要求按钮号表 = "," + 要求按钮号表 + ",";
窗口宽 = main1.窗口宽;
窗口高 = main1.窗口高;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);// 宽度参数
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
窗口宽 = m1;
窗口高 = m2;
}
} catch (Exception e2) {
}
要求字段号表 = parameter[8];
条件表达式 = parameter[13];
表名1 = parameter[2];
表名表 = parameter[0]; // 数据表名
字典表名 = "";
String[] t1 = parameter[0].split(",");
if (表名表.lastIndexOf(表名 + "字典表") >= 0)
for (int i = 0; i < t1.length; i++)
if (t1[i].lastIndexOf(表名 + "字典表") >= 0)
字典表名 = t1[i];
main1.driver1();
con1 = main1.getConn();
try {
stat4 = con1.createStatement(
// 建立连接
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
s1 = "select * from " + 表名1;
if ((条件表达式 != null) && (条件表达式.trim().length() > 0))
s1 = s1 + " where " + 条件表达式;
rs = stat4.executeQuery(s1);
rs.last();
记录条数1 = rs.getRow();
rsmd4 = rs.getMetaData();
列数 = rsmd4.getColumnCount(); // 获取列数
列名 = new String[列数]; // 定义列名数组
列数据类型 = new String[列数]; // 定义列字段类型数组
列数据宽度 = new int[列数]; // 定义列数据宽度
for (int i = 0; i < 列数; i++) {
列名[i] = rsmd4.getColumnName(i + 1); // 获取列名存到数组中
列数据类型[i] = rsmd4.getColumnTypeName(i + 1); // 获取列数据类型名存到数组中
列数据宽度[i] = rsmd4.getColumnDisplaySize(i + 1);// 指定宽度数据类型列宽度按指定值
}
for (int j = 0; j < 关键字段名.length; j++)
for (int i = 0; i < 列数; i++) {
if (关键字段名[j].trim().equals(列名[i].trim())) {
关键字序号[j] = i;
if ((要求字段号表.length() > 0)
&& (("," + 要求字段号表 + ",").lastIndexOf("," + i
+ ",") < 0))
要求字段号表 = 要求字段号表 + "," + i;
break;
}
}
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "连接数据库出错!" + s1);
}
要求字段号表 = "," + 要求字段号表 + ",";
列序号 = new int[列数];
int b0 = 0, b = -1;
while (b0 < 列数) {
if ((要求字段号表.length() < 3)
|| (要求字段号表.lastIndexOf("," + b0 + ",") >= 0)) {
b = b + 1;
列序号[b] = b0;
}
b0++;
}
表格列数1 = b + 1;
表格列名1 = new String[表格列数1]; // 定义列名数组
表列数据类型1 = new String[表格列数1]; // 定义列字段类型数组
表列数据类型 = new String[表格列数1];
表列数据宽度1 = new int[表格列数1]; // 定义列数据宽度
表列数据宽度 = new int[表格列数1];
表格数据1 = new String[记录条数1][表格列数1];
一条空记录 = new String[表格列数1];
for (int j = 0; j < 关键字段名.length; j++) {
for (int i = 0; i < 表格列数1; i++) {
if (关键字序号[j] == i)
关键字序号[j] = 列序号[i];
}
}
try {
rs.absolute(1);
int c = 0; // 行号
b = 0; // 列号
while (c < 记录条数1) {
rs.absolute(c + 1);
while (b < 表格列数1) {
表格数据1[c][b] = rs.getString(列序号[b] + 1);
b++;
}
c++;
b = 0;
}
rs.close(); // 关闭查询结果集
stat4.close(); // 关闭连接
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, "读取数据库数据出错!");
}
列标题 = new String[表格列数1];
b = 0; // 列号
s2 = "";
s3 = "";
int 类型序号 = 0, 类型序号1 = 0;
if ((DBMS名称.trim().length() > 0) && (!DBMS名称.equals("sqlserver")))
for (int i = 0; i < 类型对照表.length; i++) {
if (DBMS名称.trim().equals(类型对照表[i][0]))
类型序号1 = i;
else if (main1.DBMS系统.equals(类型对照表[i][0]))
类型序号 = i;
}
while (b < 表格列数1) {
列标题[b] = 列名[列序号[b]];
表格列名1[b] = 列标题[b];
表列数据宽度1[b] = 列数据宽度[列序号[b]];
表列数据类型1[b] = 列数据类型[列序号[b]];
s2 = s2 + 表列数据类型1[b] + ",";
for (int i = 1; i < 类型对照表[0].length; i++) {
if (表列数据类型1[b].toLowerCase().trim()
.equals(类型对照表[类型序号1][i].trim())) {
s3 = s3 + 类型对照表[类型序号][i] + ",";
表列数据类型[b] = 类型对照表[类型序号][i];
break;
}
}
b++;
}
s2 = s2.substring(0, s2.length() - 1);
s3 = s3.substring(0, s3.length() - 1);
if ((DBMS名称.trim().length() > 0) && (!DBMS名称.equals("sqlserver"))) {
String s4 = JOptionPane.showInputDialog(null, "原数据类型:" + s2
+ " 准备改为:" + s3 + " 是否正确?如果有错请对应修改。");
if ((s4 != null) && (s4.length() > 0))
表列数据类型 = s4.split(",");
else if ((s3 != null) && (s3.length() > 0))
表列数据类型 = s3.split(",");
}
if (字典表名.length() > 0) {
main1.driver1();
con = main1.getConn();
try {
Statement sta = con1.createStatement(
// 建立连接
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
String s1 = "select * from " + 字典表名;
ResultSet rs = sta.executeQuery(s1);// 执行查询,获得查询结果集
while (rs.next()) {
String 字段名称 = rs.getString(1);
String 标签名称 = rs.getString(2);
b = 0;
while (b < 表格列数1) {
if (列标题[b].compareTo(字段名称) == 0) {
列标题[b] = 标签名称;
break;
}
b++;
}
}
rs.close(); // 关闭查询结果集
sta.close(); // 关闭连接
con.close();
} catch (Exception e1) {
e1.printStackTrace();
}
}
frame.setTitle("源数据表数据情况 作者:程学先"); // 设置窗体标题
frame.setBounds(10, 10, 窗口宽, 窗口高); // 设置窗体的位置和大小
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); // 设置窗体的默认关闭模式
final JScrollPane scrollPane = new JScrollPane(); // 创建滚动面板
scrollPane.setBounds(0, 0, 窗口宽 - 60, 窗口高 - 100); // 定义滚动面板大小位置
frame.setLayout(null);
frame.getContentPane().add(scrollPane, BorderLayout.CENTER); // 在窗体中央添加滚动面板
final TableModel model = new DefaultTableModel(表格数据1, 表格列名1);
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < 表格列数1; i++) {
TableColumn tc = table.getColumn(表格列名1[i]);
JTableHeader header = table.getTableHeader();
tc.setPreferredWidth(表列数据宽度1[i] * 14);
tc.setMaxWidth(表列数据宽度1[i] * 14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
final JButton leadinginButton1 = new JButton("建新表并导入");
leadinginButton1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "CREATE TABLE " + 表名 + " (";
for (int i = 0; i < 表格列名1.length; i++) {
s1 = s1 + 列标题[i] + " " + 表列数据类型[i];
String 类型1 = main1.数字数据类型;
if (main1.类型.lastIndexOf("," + 表列数据类型[i] + ",") >= 0)
s1 = s1 + "(" + 表列数据宽度1[0] + ")";
if (i != 表格列名1.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
try {
main1.driver1();
con = main1.getConn();// 连接数据库
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stat3.executeUpdate(s1);
String 类型1 = main1.数字数据类型;
for (int i = 0; i < 表格数据1.length; i++) {
s1 = "insert into " + 表名 + " values (";
for (int j = 0; j < 表格列名1.length; j++) {
if (类型1.lastIndexOf("," + 表列数据类型1[j] + ",") >= 0) {
if (表格数据1[i][j] == null)
表格数据1[i][j] = "0";
s1 = s1 + 表格数据1[i][j];
} else {
if (表格数据1[i][j] == null)
表格数据1[i][j] = " ";
s1 = s1 + "'" + 表格数据1[i][j] + "'";
}
if (j != 表格列名1.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
}
stat3.close(); // 关闭连接
con.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton1.setBounds(30, 窗口高 - 80, 150, 20);
frame.add(leadinginButton1);
int 按钮宽 = (窗口宽 - 250) / 按钮数;
int 左边距 = 180;
final JButton leadinginButton2 = new JButton(按钮集[0]);
leadinginButton2.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "delete from " + 表名;
try {
main1.driver1();
con = main1.getConn();
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stat3.executeUpdate(s1);
String 类型1 = main1.数字数据类型;
for (int i = 0; i < 表格数据1.length; i++) {
s1 = "insert into " + 表名 + " (";
for (int k1 = 0; k1 < 表格列名1.length; k1++)
s1 = s1 + 列标题[k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < 表格列名1.length; j++) {
if (类型1.lastIndexOf("," + 表列数据类型1[j] + ",") >= 0) {
if (表格数据1[i][j] == null)
表格数据1[i][j] = "0";
s1 = s1 + 表格数据1[i][j];
} else {
if (表格数据1[i][j] == null)
表格数据1[i][j] = " ";
s1 = s1 + "'" + 表格数据1[i][j] + "'";
}
if (j != 表格列名1.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
}
stat3.close(); // 关闭连接
con.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton2.setBounds(左边距, 窗口高 - 80, 按钮宽, 20);
if (要求按钮号表.indexOf(",0,") >= 0) {
左边距 = 左边距 + 按钮宽;
frame.add(leadinginButton2);
}
final JButton leadinginButton3 = new JButton(按钮集[1]);
leadinginButton3.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
main1.driver1();
con = main1.getConn();
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String 类型1 = main1.数字数据类型;
for (int i = 0; i < 表格数据1.length; i++) {
s1 = "insert into " + 表名 + " (";
for (int k1 = 0; k1 < 表格列名1.length; k1++)
s1 = s1 + 列标题[k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < 表格列名1.length; j++) {
if (类型1.lastIndexOf("," + 表列数据类型1[j] + ",") >= 0) {
if (表格数据1[i][j] == null)
表格数据1[i][j] = "0";
s1 = s1 + 表格数据1[i][j];
} else {
if (表格数据1[i][j] == null)
表格数据1[i][j] = " ";
s1 = s1 + "'" + 表格数据1[i][j] + "'";
}
if (j != 表格列名1.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
}
stat3.close(); // 关闭连接
con.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton3.setBounds(左边距, 窗口高 - 80, 按钮宽, 20);
if (要求按钮号表.indexOf(",1,") >= 0) {
左边距 = 左边距 + 按钮宽;
frame.add(leadinginButton3);
}
final JButton leadinginButton4 = new JButton(按钮集[2]);
leadinginButton4.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
main1.driver1();
con = main1.getConn();
s1 = "";
try {
Statement sta = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
for (int k1 = 0; k1 < 表格列名1.length; k1++)
s1 = s1 + 列标题[k1] + ",";
表格列数 = 列标题.length;
s1 = s1.substring(0, s1.length() - 1);
s1 = "select " + s1 + " from " + 表名;
ResultSet rs = sta.executeQuery(s1);
rs.last();
记录条数 = rs.getRow();
表格数据 = new String[记录条数][表格列数];
for (int i = 0; i < 记录条数; i++) {
rs.absolute(i + 1);
for (int j = 0; j < 表格列数; j++) {
表格数据[i][j] = rs.getString(j + 1);
}
}
rs.close(); // 关闭查询结果集
con.close();
sta.close(); // 关闭连接
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "读取数据错!" + s1);
}
try {
main1.driver1();
con = main1.getConn();
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String 类型1 = main1.数字数据类型;
int 重复标志 = 0;
String 关键字串 = ",";
for (int k2 = 0; k2 < 表格数据.length; k2++)
for (int k3 = 0; k3 < 关键字段名.length; k3++)
关键字串 = 关键字串 + 表格数据[k2][关键字序号[k3]] + ",";
for (int i = 0; i < 表格数据1.length; i++) {
关键字段值 = "";
重复标志 = 0;
for (int k1 = 0; k1 < 关键字段名.length; k1++) {
关键字段值 = 关键字段值 + 表格数据1[i][关键字序号[k1]];
if (关键字串.lastIndexOf("," + 关键字段值 + ",") >= 0) {
重复标志 = 1;
break;
}
}
if (重复标志 == 0) {
s1 = "insert into " + 表名 + " (";
for (int k1 = 0; k1 < 表格列名1.length; k1++)
s1 = s1 + 列标题[k1] + ",";
s1 = s1.substring(0, s1.length() - 1)
+ " ) values (";
for (int j = 0; j < 表格列名1.length; j++) {
if (类型1.lastIndexOf("," + 表列数据类型1[j] + ",") >= 0) {
if (表格数据1[i][j] == null)
表格数据1[i][j] = "0";
s1 = s1 + 表格数据1[i][j];
} else {
if (表格数据1[i][j] == null)
表格数据1[i][j] = " ";
s1 = s1 + "'" + 表格数据1[i][j] + "'";
}
if (j != 表格列名1.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
关键字串 = 关键字串 + 关键字段值 + ",";
} else {
s1 = "update " + 表名 + " set ";
for (int j = 0; j < 表格列名1.length; j++) {
if (类型1.lastIndexOf("," + 表列数据类型1[j] + ",") >= 0) {
if (表格数据1[i][j] == null)
表格数据1[i][j] = "0";
s1 = s1 + 列标题[j] + " = " + 表格数据1[i][j];
} else {
if (表格数据1[i][j] == null)
表格数据1[i][j] = " ";
s1 = s1 + 列标题[j] + " = " + "'"
+ 表格数据1[i][j] + "'";
}
if (j != 表格列名1.length - 1)
s1 = s1 + ",";
}
s1 = s1 + " where ";
for (int k2 = 0; k2 < 关键字段名.length; k2++) {
s1 = s1 + 关键字段名[k2] + " = ";
if (main1.类型1.lastIndexOf(","
+ 表列数据类型1[关键字序号[k2]] + ",") >= 0) {
s1 = s1 + 表格数据1[i][关键字序号[k2]];
} else {
s1 = s1 + "'" + 表格数据1[i][关键字序号[k2]] + "'";
}
}
stat3.executeUpdate(s1);
}
}
stat3.close(); // 关闭连接
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton4.setBounds(左边距, 窗口高 - 80, 按钮宽, 20);
if (要求按钮号表.indexOf(",2,") >= 0) {
左边距 = 左边距 + 按钮宽;
frame.add(leadinginButton4);
}
final JButton exitButton1 = new JButton(按钮集[3]);
exitButton1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
frame.dispose();
}
});
exitButton1.setBounds(左边距, 窗口高 - 80, 按钮宽, 20);
if (要求按钮号表.indexOf(",3,") >= 0) {
左边距 = 左边距 + 按钮宽;
frame.add(exitButton1);
frame.setVisible(true); // 显示窗体
}
}
}
版本2
51.源码51,部件程序,添加式从office文件,包括word、excel、pdf文件导入导入部件。
/**
* 程序文件名:dataTransfer11.java
* 作者:程学先
* 功能:添加式从office文件,包括word、excel、pdf文件导入。
* 文件中的数据添加到原有数据尾部。注意本程序未作实体完整性测试,未要求提供关键字,关键字值相同的记录可能被误导入。
* 目的表与源表数据类型、数据宽度应当对应相容,符合本程序中“类型对照表"设置的对应关系。
* 完成日期:2013年1月22日
* 被调用语句:dataTransfer11.means(parameter)
* 必须提供的参数:数据“表名”、“导入文件名”。
* 可选择参数:“字段号表”、“要求按钮号表”、
* 要求字段号表:所操作数据可以是表的全部字段,也可以只涉及部分字段。如果选择全部字段,初始值设为"";
* 否则写入所有将涉及的字段在表中的顺序号,号与号之间以英文逗号分隔。
* 本部件预设的按钮控件共4个,序号及按钮名:1添加式从word文件导入、2添加式从excel文件导入、
* 3添加式从PDF文件导入、4退出
* 通过“要求按钮号表”可以选择按钮以满足功能需求。
* 如果从PDF文件导入。不支持大数据类型,每字段宽度不超过50字符。
* 只能从由dataTransfer4.java生成的PDF文件导入。
*/
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.io.File.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
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;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.pdfbox.pdfparser.PDFParser;
import org.pdfbox.pdmodel.PDDocument;
import org.pdfbox.util.PDFTextStripper;
public class dataTransfer11 extends JFrame {
static dataTransfer11 frame = new dataTransfer11();
private static JTable table;
static Connection con1;
static String url;
static ResultSetMetaData rsmd4;
static PreparedStatement pstmt;
static Statement sta4;
static ResultSet rs;
static int variabl2483 = 800, variabl2503 = 600;
static int variabl2651;
static int variabl26511;
static String[][] variabl2517;
static String[] variabl25171;
static int[] variabl1065;
static String[] variabl1913;
static String[] variabl1501;
static String[] variabl1169;
static int[] variabl1489, variabl1571;
static int variabl1853 = 0, variabl18531 = 0;
static int[] variabl1119, variabl1125;
static int variabl1527;
static String[][] variabl2197;
static DefaultTableModel model;
static String variabl2603;
static String variabl1873;
static String variabl1339;
static String[] variabl2405 = null;
static String variabl1187;
static String variabl1735;
static String s1 = "", s2 = "", s3 = "";
static String s4 = " ";
static char x1 = 10, x2 = 13, x3 = '"', x4 = ',';
static String variabl25111 = main1.variabl25111;
static int variabl2851 = 0, variabl2339 = 0;
public static void means(String[] parameter) {
frame = new dataTransfer11();
variabl2483 = 800;
variabl2503 = 600;
variabl1853 = 0;
variabl18531 = 0;
s1 = "";
s2 = "";
s3 = "";
s4 = " ";
x1 = 10;
x2 = 13;
x3 = '"';
x4 = ',';
variabl25111 = main1.variabl25111;
File file = new File(".\\com\\iText-5.0.5.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
file = new File(".\\com\\jacob.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
file = new File(".\\com\\poi-3.8-20120326.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
file = new File(".\\com\\PDFBox-0.7.3.jar");
if (!file.exists()) {
JOptionPane.showMessageDialog(null, "请将有关jar文件包下载到com文件夹中!");
return;
}
variabl2603 = parameter[4];
variabl1735 = parameter[8];
variabl1339 = parameter[18];
String[] variabl240501 = { "从word导入", "从excel导入", "从PDF文件导入", "退出" };
variabl2405 = new String[variabl240501.length];
variabl2851 = 0;
for (int i0 = 0; i0 < variabl240501.length; i0++)
variabl2405[i0] = variabl240501[i0];
if (parameter[11].length() > 0) {
variabl1187 = parameter[11];
variabl1187 = variabl1187.replaceAll(";", ";");
variabl1187 = variabl1187.replaceAll("。", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
if (variabl1187.indexOf(";") > 0) {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
variabl2405[Integer.parseInt((s602[0]))] = s602[1];
variabl1187 = variabl1187 + "," + s602[0];
variabl2851++;
} else {
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
} else {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
variabl1187 = s601[i01];
else
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
} else {
variabl1187 = "";
for (int i1 = 0; i1 < variabl2405.length; i1++) {
if (i1 == 0)
variabl1187 = variabl1187 + i1;
else
variabl1187 = variabl1187 + "," + i1;
variabl2851++;
}
}
variabl1187 = "," + variabl1187 + ",";
variabl2483 = main1.variabl2483;
variabl2503 = main1.variabl2503;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
variabl2483 = m1;
variabl2503 = m2;
}
} catch (Exception e2) {
}
String variabl1633 = parameter[19].trim();
if ((variabl1633.length() > 0)
&& (variabl1633.substring(variabl1633.length() - 1).equals(",")))
variabl1633 = variabl1633.substring(0, variabl1633.length() - 1);
String variabl2429 = parameter[0];
String[] l3 = variabl1633.split(",");
variabl1873 = "";
String[] t1;
int b = 0, c = 0;
if (variabl2429.lastIndexOf(variabl2603 + "字典表") >= 0) {
t1 = variabl2429.split(",");
for (int i = 0; i < t1.length; i++)
if (t1[i].lastIndexOf(variabl2603 + "字典表") >= 0)
variabl1873 = t1[i];
}
String[] l1;
if (variabl1735.length() > 0) {
l1 = variabl1735.split(",");
int[] l2 = new int[l1.length];
for (int i = 0; i < l1.length; i++) {
l2[i] = Integer.valueOf(l1[i]);
}
variabl2651 = l1.length;
variabl2517 = new String[2][variabl2651];
for (int i = 0; i < variabl2651; i++) {
variabl2517[0][i] = l3[l2[i]];
variabl2517[1][i] = variabl2517[0][i];
}
} else {
variabl2651 = l3.length;
variabl2517 = new String[2][variabl2651];
for (int i = 0; i < variabl2651; i++) {
variabl2517[0][i] = l3[i];
variabl2517[1][i] = variabl2517[0][i];
}
}
s1 = "";
for (int i = 0; i < variabl2651; i++)
s1 = s1 + variabl2517[0][i] + ",";
s1 = s1.substring(0, s1.length() - 1);
try {
if (("," + variabl2429).lastIndexOf("," + variabl2603 + ",") >= 0) {
con1 = main1.getConn();
sta4 = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select " + s1 + " from " + variabl2603;
} else {
JOptionPane.showMessageDialog(null, "目的数据表不存在,不能导入!");
return;
}
ResultSet rs = sta4.executeQuery(s1);
rsmd4 = rs.getMetaData();
variabl2651 = rsmd4.getColumnCount();
variabl2517 = new String[2][variabl2651];
variabl1501 = new String[variabl2651];
variabl1489 = new int[variabl2651];
for (int i = 0; i < variabl2651; i++) {
variabl2517[0][i] = rsmd4.getColumnName(i + 1);
variabl2517[1][i] = variabl2517[0][i];
variabl1501[i] = rsmd4.getColumnTypeName(i + 1);
variabl1489[i] = rsmd4.getColumnDisplaySize(i + 1);
if (variabl1489[i] < variabl2517[0][i].length())
variabl1489[i] = variabl2517[0][i].length();
if (variabl1489[i] < variabl2517[1][i].length())
variabl1489[i] = variabl2517[1][i].length();
if (variabl1489[i] > 50)
variabl1489[i] = 50;
}
variabl1913 = new String[variabl2651];
variabl1169 = new String[variabl2651];
variabl1119 = new int[variabl2651];
for (b = 0; b < variabl2651; b++) {
variabl1913[b] = variabl2517[1][b];
variabl1119[b] = variabl1489[b];
}
variabl1853 = 0;
rs.last();
variabl1853 = rs.getRow();
variabl2197 = new String[variabl1853][variabl2651];
rs.absolute(1);
c = 0;
b = 0;
while (c < variabl1853) {
rs.absolute(c + 1);
while (b < variabl2651) {
variabl2197[c][b] = rs.getString(b + 1);
b++;
}
c++;
b = 0;
}
rs.close();
sta4.close();
con1.close();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "读取数据出错!");
}
if (variabl1873.length() > 0) {
try {
con1 = main1.getConn();
sta4 = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + variabl1873;
rs = sta4.executeQuery(s1);
String s4 = "";
String s5 = "";
b = 1;
while (rs.absolute(b)) {
b++;
s4 = rs.getString(1);
s5 = rs.getString(2);
for (int j = 0; j < variabl2651; j++) {
if (variabl2517[0][j].trim().equals(s4.trim())) {
variabl2517[1][j] = s5;
variabl1913[j] = s5;
break;
}
}
}
rs.close();
con1.close();
sta4.close();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "连接数据库出错!");
}
}
frame.setTitle("数据添加式从office文件导入部件。 作者:程学先");
frame.setBounds(10, 10, variabl2483, variabl2503);
frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
frame.setLayout(null);
final JPanel panel = new JPanel(false);
panel.setLayout(null);
panel.setBounds(10, 10, variabl2483, variabl2503);
final JScrollPane scrollPane = new JScrollPane();
scrollPane.setBounds(0, 0, variabl2483 - 20, variabl2503 - 100);
panel.add(scrollPane);
model = new DefaultTableModel(variabl2197, variabl2517[1]);
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < variabl2651; i++) {
TableColumn tc = table.getColumn(variabl2517[1][i]);
tc.setPreferredWidth(variabl1489[i] * 14);
tc.setMaxWidth(variabl1489[i] * 14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
int variabl2339 = (variabl2483 - 30) / variabl2851;
int variabl1423 = 10;
JButton variabl1563 = new JButton(variabl2405[0]);
variabl1563.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
variabl1527 = model.getRowCount();
if (variabl1339.length() == 0) {
variabl1339 = JOptionPane.showInputDialog(
"请输入导入文件完整路径 例如:F:\\java.doc", "");
return;
}
int p1 = 0, p2 = 0;
if (variabl1339.lastIndexOf(".doc") < 0) {
p1 = variabl1339.lastIndexOf(".");
p2 = variabl1339.lastIndexOf("\\\\");
if (p2 > p1) {
variabl1339 = variabl1339 + ".doc";
variabl1339 = variabl1339.replace("\\.", "\\.\\.");
} else {
variabl1339 = variabl1339.substring(0, p1) + ".doc";
}
}
try {
FileInputStream in = new FileInputStream(new File(
variabl1339));
WordExtractor extractor = null;
extractor = new WordExtractor(in);
String variabl2581 = extractor.getText();
int m1 = 0;
;
String[] variabl2615 = new String[variabl2651];
int k = 0;
int k2 = 0, k5 = 0;
variabl2615[k] = "";
for (int i = 0; i < variabl2581.length(); i++) {
if (variabl2581.charAt(i) == '\r') {
k5 = 0;
model.addRow(variabl2615);
variabl2615[0] = "";
k = 0;
m1 = 1;
if ((i < variabl2581.length() - 2)
&& (variabl2581.charAt(i + 2) == '\r'))
break;
} else if (variabl2581.charAt(i) == '\n') {
} else if ((byte) (variabl2581.charAt(i)) == 9) {
k++;
m1 = 0;
if (k < variabl2651)
variabl2615[k] = "";
} else if (k < variabl2651) {
variabl2615[k] = variabl2615[k]
+ variabl2581.charAt(i);
}
}
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "读取数据出错!");
}
try {
main1.driver1();
con1 = main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
for (int i = variabl1527; i < model.getRowCount(); i++) {
s1 = "insert into " + variabl2603 + " (";
for (int k1 = 0; k1 < variabl2517[1].length; k1++)
s1 = s1 + variabl2517[1][k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < variabl2517[1].length; j++) {
if (variabl25111.lastIndexOf("," + variabl1501[j]
+ ",") >= 0) {
if (model.getValueAt(i, j) == null)
s1 = s1 + "0";
else
s1 = s1
+ model.getValueAt(i, j).toString()
.trim();
} else {
if (model.getValueAt(i, j) == null)
s1 = s1 + "' '";
else
s1 = s1
+ "'"
+ model.getValueAt(i, j).toString()
.trim() + "'";
}
if (j != variabl2517[1].length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
sta4.executeUpdate(s1);
}
sta4.close();
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",0,") >= 0)) {
variabl1563.setBounds(variabl1423, variabl2503 - 80, variabl2339,
20);
panel.add(variabl1563);
variabl1423 = variabl1423 + variabl2339;
}
JButton variabl1565 = new JButton(variabl2405[1]);
variabl1565.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
variabl1527 = model.getRowCount();
if (variabl1339.length() == 0) {
variabl1339 = JOptionPane.showInputDialog(
"请输入导入文件完整路径 例如:F:\\java.xls", "");
return;
}
int p1 = 0, p2 = 0;
if (variabl1339.lastIndexOf(".xls") < 0) {
p1 = variabl1339.lastIndexOf(".");
p2 = variabl1339.lastIndexOf("\\\\");
if (p2 > p1) {
variabl1339 = variabl1339 + ".xls";
variabl1339 = variabl1339.replace("\\.", "\\.\\.");
} else {
variabl1339 = variabl1339.substring(0, p1) + ".xls";
}
}
InputStream is = null;
HSSFWorkbook wbWorkbook = null;
try {
is = new FileInputStream(variabl1339);
wbWorkbook = new HSSFWorkbook(is);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "读取文件失败,请检查路径是否正确!",
"错误", JOptionPane.INFORMATION_MESSAGE);
} catch (IOException e2) {
e2.printStackTrace();
}
HSSFSheet sheet = wbWorkbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
int variabl1157 = sheet.getPhysicalNumberOfRows();
HSSFRow row = sheet.getRow(1);
int variabl1185 = row.getPhysicalNumberOfCells();
if (variabl1185 != variabl2651)
JOptionPane.showMessageDialog(null, "原文件中数据结构与数据表不同!");
String[] val;
int k2 = 0, k5 = 0;
String value = "";
for (int i = 0; i < variabl1157; i++) {
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < variabl1185; j++) {
HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
}
val = value.split(",");
k5 = 0;
model.addRow(val);
value = "";
}
}
try {
main1.driver1();
con1 = main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
for (int i = variabl1527; i < model.getRowCount(); i++) {
s1 = "insert into " + variabl2603 + " (";
for (int k1 = 0; k1 < variabl2517[1].length; k1++)
s1 = s1 + variabl2517[1][k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < variabl2517[1].length; j++) {
if (variabl25111.lastIndexOf("," + variabl1501[j]
+ ",") >= 0) {
if (model.getValueAt(i, j) == null)
s1 = s1 + "0";
else
s1 = s1
+ model.getValueAt(i, j).toString()
.trim();
} else {
if (model.getValueAt(i, j) == null)
s1 = s1 + "' '";
else
s1 = s1
+ "'"
+ model.getValueAt(i, j).toString()
.trim() + "'";
}
if (j != variabl2517[1].length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
sta4.executeUpdate(s1);
}
sta4.close();
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",1,") >= 0)) {
variabl1565.setBounds(variabl1423, variabl2503 - 80, variabl2339,
20);
panel.add(variabl1565);
variabl1423 = variabl1423 + variabl2339;
}
JButton variabl1567 = new JButton(variabl2405[2]);
variabl1567.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
variabl1527 = model.getRowCount();
if (variabl1339.length() == 0) {
variabl1339 = JOptionPane.showInputDialog(
"请输入导入文件完整路径 例如:F:\\java.pdf", "");
return;
}
int p1 = 0, p2 = 0;
if (variabl1339.lastIndexOf(".pdf") < 0) {
p1 = variabl1339.lastIndexOf(".");
p2 = variabl1339.lastIndexOf("\\\\");
if (p2 > p1) {
variabl1339 = variabl1339 + ".pdf";
variabl1339 = variabl1339.replace("\\.", "\\.\\.");
} else {
variabl1339 = variabl1339.substring(0, p1) + ".pdf";
}
}
try {
FileInputStream in = new FileInputStream(variabl1339);
PDFParser parser = new PDFParser(in);
parser.parse();
PDDocument pdfdocument = parser.getPDDocument();
PDFTextStripper stripper = new PDFTextStripper();
s1 = stripper.getText(pdfdocument);
in.close();
} catch (Exception e1) {
e1.printStackTrace();
}
String[] variabl2615 = new String[variabl2651];
String s4 = "";
int k = 0, m1 = 0, k2 = 0, k5 = 0;
variabl2615[0] = "";
int l1 = s1.length();
for (int i1 = 0; i1 < l1; i1++) {
s2 = s1.substring(i1, i1 + 1);
if (s1.charAt(i1) == '\r') {
} else if (s1.charAt(i1) == '\n') {
} else if ((s2.trim().equals("│")) && (m1 == 1)) {
if (k == variabl2651 - 1) {
k5 = 0;
model.addRow(variabl2615);
k = 0;
m1 = 0;
variabl2615[k] = "";
} else {
k++;
if (k < variabl2651)
variabl2615[k] = "";
}
} else if (s2.trim().equals("│")) {
m1 = 1;
k = 0;
variabl2615[0] = "";
} else if ((k < variabl2651) && (m1 == 1)) {
try {
variabl2615[k] = variabl2615[k] + s2;
} catch (Exception e1) {
}
}
}
try {
main1.driver1();
con1 = main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
for (int i = variabl1527; i < model.getRowCount(); i++) {
s1 = "insert into " + variabl2603 + " (";
for (int k1 = 0; k1 < variabl2517[1].length; k1++)
s1 = s1 + variabl2517[1][k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < variabl2517[1].length; j++) {
if (variabl25111.lastIndexOf("," + variabl1501[j]
+ ",") >= 0) {
if (model.getValueAt(i, j) == null)
s1 = s1 + "0";
else
s1 = s1
+ model.getValueAt(i, j).toString()
.trim();
} else {
if (model.getValueAt(i, j) == null)
s1 = s1 + "' '";
else
s1 = s1
+ "'"
+ model.getValueAt(i, j).toString()
.trim() + "'";
}
if (j != variabl2517[1].length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
sta4.executeUpdate(s1);
}
sta4.close();
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",2,") >= 0)) {
variabl1567.setBounds(variabl1423, variabl2503 - 80, variabl2339,
20);
panel.add(variabl1567);
variabl1423 = variabl1423 + variabl2339;
}
JButton exitButton = new JButton(variabl2405[3]);
exitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
frame.dispose();
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",3,") >= 0)) {
exitButton
.setBounds(variabl1423, variabl2503 - 80, variabl2339, 20);
panel.add(exitButton);
variabl1423 = variabl1423 + variabl2339;
}
frame.add(panel);
frame.setVisible(true);
}
}
52.源码52,部件程序,修改式从office文件,包括word、excel、pdf文件导入数据导入部件。
/**
* 程序文件名:dataTransfer12.java 作者:程学先 功能:修改式从office文件,包括word、excel、pdf文件导入。
* 本程序要求提供关键字,关键字值相同的记录将替代(修改)原有记录,关键字再原表中不存在的记录会添加到原有数据尾部。
* 目的表与源表数据类型、数据宽度应当对应相容,符合本程序中“类型对照表"设置的对应关系。 完成日期:2013年1月22日
* 被调用语句:dataTransfer12.means(parameter) 必须提供的参数:数据“表名”、“关键字”、“导入文件名”。
* 可选择参数:“字段号表”、“要求按钮号表”、 要求字段号表:所操作数据可以是表的全部字段,也可以只涉及部分字段。如果选择全部字段,初始值设为"";
* 否则写入所有将涉及的字段在表中的顺序号,号与号之间以英文逗号分隔。
* 本部件预设的按钮控件共4个,序号及按钮名:1修改式从word文件导入、2修改式从excel文件导入、 3修改式从PDF文件导入、4退出
* 通过“要求按钮号表”可以选择按钮以满足功能需求。 如果从PDF文件导入。不支持大数据类型,每字段宽度不超过50字符。
* 只能从由dataTransfer4.java生成的PDF文件导入。
*/
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.io.File.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
import java.util.ArrayList;
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;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.pdfbox.pdfparser.PDFParser;
import org.pdfbox.pdmodel.PDDocument;
import org.pdfbox.util.PDFTextStripper;
public class dataTransfer12 extends JFrame {
static dataTransfer12 frame = new dataTransfer12();
private static JTable table;
static Connection con1;
static String url;
static ResultSetMetaData rsmd4;
static PreparedStatement pstmt;
static Statement sta4;
static ResultSet rs;
static int variabl2483=800,variabl2503=600;
static int variabl2651;
static int variabl26511;
static String [][] variabl2517;
static String [] variabl25171;
static int[] variabl1065;
static String [] variabl1913;
static String[] variabl1501;
static String[] variabl1169;
static int[] variabl1489,variabl1571;
static int variabl1853=0,variabl18531=0;
static int[] variabl1119,variabl1125;
static int variabl1527;
static ArrayList variabl1665=new ArrayList();
static String[][] variabl2197;
static String[] variabl14211;
static String[][] variabl21972;
static DefaultTableModel model;
static String variabl2603;
static String variabl1873;
static String variabl1339;
static String [] variabl2405=null;
static String variabl1187;
static String variabl1735;
static String variabl2253;
static String[] variabl2053,variabl20531;
static int[] variabl1287,variabl12871;
static String s1="",s2="",s3="";
static String s4=" ";
static char x1=10,x2=13,x3='"',x4=',';
static String variabl25111=main1.variabl25111;
static int variabl2851=0,variabl2339=0;
static JPanel panel = new JPanel(false);
static JScrollPane scrollPane = new JScrollPane();
public static void means(String [] parameter) {
frame = new dataTransfer12();
variabl2483=800;variabl2503=600;
variabl1853=0;variabl18531=0;
variabl1665=new ArrayList();
s1="";s2="";s3="";
s4=" ";
x1=10;x2=13;x3='"';x4=',';
variabl25111=main1.variabl25111;
File file = new File(".\\com\\iText-5.0.5.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
file = new File(".\\com\\jacob.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
file = new File(".\\com\\poi-3.8-20120326.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
file = new File(".\\com\\PDFBox-0.7.3.jar");
if (!file.exists()) {JOptionPane.showMessageDialog( null, "请将有关jar文件包下载到com文件夹中!"); return;}
variabl2603=parameter[4] ;
variabl1735=parameter[8];
variabl1339=parameter[18];
String [] variabl240501={"从word导入","从excel导入","从PDF文件导入","退出"};
variabl2405=new String[variabl240501.length];
variabl2851=0;
for (int i0=0;i0<variabl240501.length;i0++)
variabl2405[i0]= variabl240501[i0];
if (parameter[11].length()>0){
variabl1187=parameter[11] ;
variabl1187=variabl1187.replaceAll(";",";");
variabl1187=variabl1187.replaceAll("。",";");
variabl1187=variabl1187.replaceAll(":",";");
variabl1187=variabl1187.replaceAll(":",";");
if (variabl1187.indexOf(";")>0){
String s601[]=variabl1187.split(",");
variabl1187="";
for (int i01=0;i01<s601.length;i01++){
if (s601[i01].indexOf(";")>0){
String s602[]=s601[i01].split(";");
variabl2405[Integer.parseInt((s602[0]))]=s602[1];
variabl1187=variabl1187+","+s602[0];
variabl2851++;
}
else {
variabl1187=variabl1187+","+s601[i01];
variabl2851++;
}
}
}
else {
String s601[]=variabl1187.split(",");
variabl1187="";
for (int i01=0;i01<s601.length;i01++){
if (i01==0) variabl1187=s601[i01];
else variabl1187=variabl1187+","+s601[i01];
variabl2851++;
}
}
}
else {
variabl1187="";
for (int i1=0;i1<variabl2405.length;i1++){
if (i1==0) variabl1187=variabl1187+i1;
else variabl1187=variabl1187+","+i1;
variabl2851++;
}
}
variabl1187=","+variabl1187+",";
variabl2483=main1.variabl2483; variabl2503=main1.variabl2503;
int m1,m2;
try{
if (parameter[17].length()==0) {
m1=0;m2=0;
}
else {
m1=Integer.parseInt(parameter[17]) ;
m2=Integer.parseInt(parameter[18]) ;
}
if (m1>0){
if (m2<80){
m1=m1*10;
m2=m2*10;
}
variabl2483=m1 ;
variabl2503=m2 ;
}
}catch(Exception e2){}
variabl2253=parameter[6];
String variabl1633=parameter[19].trim();
if ((variabl1633.length()>0)&&(variabl1633.substring(variabl1633.length()-1).equals(",")))
variabl1633=variabl1633.substring(0,variabl1633.length()-1);
String variabl2429=parameter[0];
String [] l3=variabl1633.split(",");
variabl1873="";
String []t1;
int b=0,c=0;
if (variabl2429.lastIndexOf(variabl2603+"字典表")>=0){
t1=variabl2429.split(",");
for (int i=0;i<t1.length;i++)
if (t1[i].lastIndexOf(variabl2603+"字典表")>=0) variabl1873=t1[i];
}
String[] l1;
if (variabl1735.length()>0){
l1=variabl1735.split(",");
int [] l2=new int[l1.length];
for (int i=0;i<l1.length;i++){
l2[i]=Integer.valueOf(l1[i]);
}
variabl2651=l1.length;
variabl2517=new String[2][variabl2651];
for (int i=0;i<variabl2651;i++){
variabl2517[0][i]=l3[l2[i]];
variabl2517[1][i]=variabl2517[0][i];
}
}
else {
variabl2651=l3.length;
variabl2517=new String[2][variabl2651];
for (int i=0;i<variabl2651;i++){
variabl2517[0][i]=l3[i];
variabl2517[1][i]=variabl2517[0][i];
}
}
s1="";
for (int i=0;i<variabl2651;i++)
s1=s1+variabl2517[0][i]+",";
s1=s1.substring(0,s1.length()-1);
try {
if ((","+variabl2429).lastIndexOf(","+variabl2603+",")>=0){
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1="select "+s1+" from "+variabl2603;
}
else {
JOptionPane.showMessageDialog( null, "目的数据表不存在,不能导入!");
return;
}
ResultSet rs=sta4.executeQuery(s1);
rsmd4 = rs.getMetaData();
variabl2651 = rsmd4.getColumnCount();
variabl2517 = new String[2][variabl2651];
variabl1501 = new String[variabl2651];
variabl1489 = new int[variabl2651];
for (int i=0;i<variabl2651;i++) {
variabl2517[0][i] = rsmd4.getColumnName(i + 1);
variabl2517[1][i]=variabl2517[0][i];
variabl1501[i] = rsmd4.getColumnTypeName(i + 1);
variabl1489[i] = rsmd4.getColumnDisplaySize(i + 1);
if (variabl1489[i]<variabl2517[0][i].length()) variabl1489[i]=variabl2517[0][i].length();
if (variabl1489[i]<variabl2517[1][i].length()) variabl1489[i]=variabl2517[1][i].length();
if (variabl1489[i]>50) variabl1489[i]=50;
}
variabl2053=variabl2253.split(",");
variabl20531=new String[variabl2053.length];
variabl1287=new int[variabl2053.length];
variabl12871=new int[variabl2053.length];
variabl1913=new String[variabl2651];
variabl1169=new String[variabl2651];
variabl1119=new int[variabl2651];
for (b=0;b<variabl2651;b++){
variabl1913[b]=variabl2517[1][b];
variabl1119[b]=variabl1489[b];
}
variabl1853=0;
rs.last();
variabl1853=rs.getRow();
variabl2197=new String[variabl1853][variabl2651];
rs.absolute(1);
c=0;
b=0;
while(c<variabl1853) {
rs.absolute(c+1);
while(b<variabl2651){
variabl2197[c][b]=rs.getString(b+1);
b++;
}
c++;
b=0;
}
rs.close();
sta4.close();
con1.close();
}
catch(SQLException e)
{
JOptionPane.showMessageDialog( null, "读取数据出错!");
}
if (variabl1873.length()>0){
try
{
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + variabl1873;
rs = sta4.executeQuery(s1);
String s4="";
String s5="";
b=1;
while (rs.absolute(b)) {
b++;
s4=rs.getString(1);
s5=rs.getString(2);
for (int j=0;j<variabl2651;j++){
if (variabl2517[0][j].trim().equals(s4.trim())){
variabl2517[1][j]=s5;
variabl1913[j]=s5;
break;
}
}
}
rs.close();
con1.close();
sta4.close();
} catch (SQLException e)
{
JOptionPane.showMessageDialog( null, "连接数据库出错!");
}
}
for (int i=0;i<variabl2651;i++) {
for (int j=0;j<variabl2053.length;j++)
if (variabl2053[j].trim().equals(variabl2517[0][i].trim())){
variabl20531[j]=variabl2517[1][i];
variabl1287[j]=i;
}
}
frame.setTitle("数据修改式从office文件导入部件。 作者:程学先");
frame.setBounds(10, 10, variabl2483, variabl2503);
frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
frame.setLayout(null);
panel = new JPanel(false);
panel.setLayout(null);
panel.setBounds(10, 10,variabl2483,variabl2503);
scrollPane = new JScrollPane();
scrollPane.setBounds(0,0,variabl2483-20,variabl2503-100);
panel.add(scrollPane);
model = new DefaultTableModel(variabl2197,variabl2517[1]);
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for(int i=0;i<variabl2651;i++) {
TableColumn tc = table.getColumn(variabl2517[1][i]);
tc.setPreferredWidth(variabl1489[i]*14);
tc.setMaxWidth(variabl1489[i]*14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
int variabl2339=(variabl2483-30)/variabl2851;
int variabl1423=10;
JButton variabl1563 = new JButton(variabl2405[0]);
variabl1563.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (variabl1339.length()==0){
variabl1339=JOptionPane.showInputDialog("请输入导入文件完整路径 例如:F:\\java.doc","");
return;
}
int p1=0,p2=0;
if (variabl1339.lastIndexOf(".doc")<0){
p1=variabl1339.lastIndexOf(".");
p2=variabl1339.lastIndexOf("\\\\");
if (p2>p1){
variabl1339=variabl1339+".doc";
variabl1339=variabl1339.replace("\\.","\\.\\.");
}
else {
variabl1339=variabl1339.substring(0,p1)+".doc";
}
}
try {
FileInputStream in=new FileInputStream(new File(variabl1339));
WordExtractor extractor=null;
extractor=new WordExtractor(in);
String variabl2581=extractor.getText();
variabl1665.clear();
int m1=0;;
String[] variabl2615=new String[variabl2651];
int k=0;
int k2=0,k5=0;
variabl2615[k]="";
for (int i=0;i<variabl2581.length();i++){
if (variabl2581.charAt(i)=='\r'){
s3="";
for (int k3=0;k3<variabl2053.length;k3++)
s3=s3+variabl2615[variabl1287[k3]].trim();
k2=0;
for (int j1=0;j1<model.getRowCount();j1++){
s2="";
for (int k4=0;k4<variabl2053.length;k4++){
s2=s2+model.getValueAt(j1,variabl1287[k4]).toString().trim();
}
if (s3.trim().equals(s2.trim())) {
k2=j1;
k5=1;
break;
}
}
if (k5>0){
model.removeRow(k2);
variabl1853--;
}
k5=0;
model.addRow(variabl2615);
variabl2615[0]="";
variabl1853++;
k=0;
m1=1;
if ((i<variabl2581.length()-2)&&(variabl2581.charAt(i+2)=='\r')) break;
}
else if (variabl2581.charAt(i)=='\n'){}
else if ((byte)(variabl2581.charAt(i))==9){
k++;
m1=0;
if (k<variabl2651) variabl2615[k]="";
}
else if (k<variabl2651){
variabl2615[k]=variabl2615[k]+variabl2581.charAt(i);
}
}
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "读取数据出错!");
}
variabl2637001();
}
});
if ((variabl1187.length()<3) || (variabl1187.lastIndexOf(",0,")>=0)){
variabl1563.setBounds(variabl1423,variabl2503-80,variabl2339,20);
panel.add(variabl1563);
variabl1423=variabl1423+variabl2339;
}
JButton variabl1565 = new JButton(variabl2405[1]);
variabl1565.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
variabl1665.clear();
if (variabl1339.length()==0){
variabl1339=JOptionPane.showInputDialog("请输入导入文件完整路径 例如:F:\\java.xls","");
return;
}
int p1=0,p2=0;
if (variabl1339.lastIndexOf(".xls")<0){
p1=variabl1339.lastIndexOf(".");
p2=variabl1339.lastIndexOf("\\\\");
if (p2>p1){
variabl1339=variabl1339+".xls";
variabl1339=variabl1339.replace("\\.","\\.\\.");
}
else {
variabl1339=variabl1339.substring(0,p1)+".xls";
}
}
InputStream is = null;
HSSFWorkbook wbWorkbook = null;
try {
is = new FileInputStream(variabl1339);
wbWorkbook = new HSSFWorkbook(is);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "读取文件失败,请检查路径是否正确!", "错误",
JOptionPane.INFORMATION_MESSAGE);
} catch (IOException e2) {
e2.printStackTrace();
}
HSSFSheet sheet = wbWorkbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
int variabl1157 = sheet.getPhysicalNumberOfRows();
HSSFRow row = sheet.getRow(1);
int variabl1185=row.getPhysicalNumberOfCells();
if (variabl1185!=variabl2651)
JOptionPane.showMessageDialog(null, "原文件中数据结构与数据表不同!");
String[] val;
int k2=0,k5=0;
String value = "";
for (int i = 0; i < variabl1157; i++) {
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < variabl1185; j++) {
HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
}
val = value.split(",");
for (int i1=0;i1<val.length;i1++){
variabl1665.add(val[i1]);
}
k5=0;
model.addRow(val);
value = "";
}
}
variabl18531=variabl1665.size()/variabl2651;
variabl21972=new String[variabl18531][variabl2651];
variabl14211=new String[variabl18531];
for (int i=0;i<variabl18531;i++){
for (int j=0;j<variabl2651;j++){
if (variabl1665.get(i*variabl2651+j)==null) variabl21972[i][j]="";
else variabl21972[i][j]=variabl1665.get(i*variabl2651+j).toString();
}
variabl14211[i]="";
for (int k3=0;k3<variabl2053.length;k3++)
variabl14211[i]=variabl14211[i]+variabl21972[i][variabl1287[k3]].trim();
}
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
int f0=0;
for (int i=0;i<variabl18531;i++) {
f0=0;
for (int i1=0;i1<variabl1853;i1++){
s3="";
for (int j=0;j<variabl2053.length;j++){
s3=s3+variabl2197[i1][variabl1287[j]].trim();
}
if (variabl14211[i].equals(s3)){
f0=1;
break;
}
}
if (f0==0){
s1="insert into "+variabl2603+" (";
for (int k1=0;k1<variabl2517[1].length;k1++)
s1=s1+variabl2517[1][k1]+",";
s1=s1.substring(0,s1.length()-1)+" ) values (";
for (int j=0;j<variabl2517[1].length;j++){
if (variabl25111.lastIndexOf(","+variabl1501[j]+",")>=0){
if (variabl21972[i][j]==null) variabl21972[i][j]="0";
s1=s1+variabl21972[i][j].trim();
}
else{
if (variabl21972[i][j]==null) variabl21972[i][j]=" ";
s1=s1+"'"+variabl21972[i][j].trim()+"'";
}
if (j!=variabl2517[1].length-1) s1=s1+",";
}
s1=s1+")";
sta4.executeUpdate(s1);
}
else {
s1="update "+variabl2603+" set ";
for (int k1=0;k1<variabl2517[1].length;k1++){
s1=s1+variabl2517[1][k1]+"=";
if (variabl25111.lastIndexOf(","+variabl1501[k1]+",")>=0){
if (variabl21972[i][k1]==null) variabl21972[i][k1]="0";
s1=s1+variabl21972[i][k1].trim();
}
else{
if (variabl21972[i][k1]==null) variabl21972[i][k1]=" ";
s1=s1+"'"+variabl21972[i][k1].trim()+"'";
}
if (k1!=variabl2517[1].length-1) s1=s1+",";
}
s2=" where ";
for (int k=0;k<variabl2053.length;k++){
s2=s2+variabl20531[k]+"='"+variabl21972[i][variabl1287[k]]+"'";
if (k<variabl2053.length-1) s2=s2+" and ";
}
s1=s1+s2;
sta4.executeUpdate(s1);
}
}
sta4.close();
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "操作出错,请检查数据表名是否重复,录入语句是否正确!"+s1);
}
variabl2637002();
}
});
if ((variabl1187.length()<3) || (variabl1187.lastIndexOf(",1,")>=0)){
variabl1565.setBounds(variabl1423,variabl2503-80,variabl2339,20);
panel.add(variabl1565);
variabl1423=variabl1423+variabl2339;
}
JButton variabl1567 = new JButton(variabl2405[2]);
variabl1567.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
variabl1665.clear();
if (variabl1339.length()==0){
variabl1339=JOptionPane.showInputDialog("请输入导入文件完整路径 例如:F:\\java.pdf","");
return;
}
int p1=0,p2=0;
if (variabl1339.lastIndexOf(".pdf")<0){
p1=variabl1339.lastIndexOf(".");
p2=variabl1339.lastIndexOf("\\\\");
if (p2>p1){
variabl1339=variabl1339+".pdf";
variabl1339=variabl1339.replace("\\.","\\.\\.");
}
else {
variabl1339=variabl1339.substring(0,p1)+".pdf";
}
}
try {
FileInputStream in = new FileInputStream(variabl1339);
PDFParser parser = new PDFParser(in);
parser.parse();
PDDocument pdfdocument = parser.getPDDocument();
PDFTextStripper stripper = new PDFTextStripper();
s1 = stripper.getText(pdfdocument);
in.close();
} catch (Exception e1) {
e1.printStackTrace();
}
String[] variabl2615=new String[variabl2651];
String s4="";
int k=0,m1=0,k2=0,k5=0;
variabl2615[0]="";
int l1=s1.length();
for (int i1=0;i1<l1;i1++){
s2=s1.substring(i1,i1+1);
if (s1.charAt(i1)=='\r') {}
else if (s1.charAt(i1)=='\n') {}
else if ((s2.trim().equals("│"))&&(m1==1)) {
if (k==variabl2651-1) {
k5=0;
variabl1665.add(variabl2615[k]);
model.addRow(variabl2615);
k=0;
m1=0;
variabl2615[k]="";
}
else {
variabl1665.add(variabl2615[k]);
k++;
if (k<variabl2651) variabl2615[k]="";
}
}
else if (s2.trim().equals("│")) {
m1=1;
k=0;
variabl2615[0]="";
}
else if ((k<variabl2651)&&(m1==1)){
try{
variabl2615[k]=variabl2615[k]+s2;
} catch (Exception e1) {
}
}
}
variabl18531=variabl1665.size()/variabl2651;
variabl21972=new String[variabl18531][variabl2651];
variabl14211=new String[variabl18531];
for (int i=0;i<variabl18531;i++){
for (int j=0;j<variabl2651;j++){
if (variabl1665.get(i*variabl2651+j)==null) variabl21972[i][j]="";
else variabl21972[i][j]=variabl1665.get(i*variabl2651+j).toString();
}
variabl14211[i]="";
for (int k3=0;k3<variabl2053.length;k3++)
variabl14211[i]=variabl14211[i]+variabl21972[i][variabl1287[k3]].trim();
}
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
int f0=0;
for (int i=0;i<variabl18531;i++) {
f0=0;
for (int i1=0;i1<variabl1853;i1++){
s3="";
for (int j=0;j<variabl2053.length;j++){
s3=s3+variabl2197[i1][variabl1287[j]].trim();
}
if (variabl14211[i].trim().equals(s3.trim())){
f0=1;
break;
}
}
if (f0==0){
s1="insert into "+variabl2603+" (";
for (int k1=0;k1<variabl2517[1].length;k1++)
s1=s1+variabl2517[1][k1]+",";
s1=s1.substring(0,s1.length()-1)+" ) values (";
for (int j=0;j<variabl2517[1].length;j++){
if (variabl25111.lastIndexOf(","+variabl1501[j]+",")>=0){
if (variabl21972[i][j]==null) variabl21972[i][j]="0";
s1=s1+variabl21972[i][j].trim();
}
else{
if (variabl21972[i][j]==null) variabl21972[i][j]=" ";
s1=s1+"'"+variabl21972[i][j].trim()+"'";
}
if (j!=variabl2517[1].length-1) s1=s1+",";
}
s1=s1+")";
sta4.executeUpdate(s1);
}
else {
s1="update "+variabl2603+" set ";
for (int k1=0;k1<variabl2517[1].length;k1++){
s1=s1+variabl2517[1][k1]+"=";
if (variabl25111.lastIndexOf(","+variabl1501[k1]+",")>=0){
if (variabl21972[i][k1]==null) variabl21972[i][k1]="0";
s1=s1+variabl21972[i][k1].trim();
}
else{
if (variabl21972[i][k1]==null) variabl21972[i][k1]=" ";
s1=s1+"'"+variabl21972[i][k1].trim()+"'";
}
if (k1!=variabl2517[1].length-1) s1=s1+",";
}
s2=" where ";
for (int k4=0;k4<variabl2053.length;k4++){
s2=s2+variabl20531[k4]+"='"+variabl21972[i][variabl1287[k4]]+"'";
if (k4<variabl2053.length-1) s2=s2+" and ";
}
s1=s1+s2;
sta4.executeUpdate(s1);
}
}
sta4.close();
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "操作出错,请检查数据表名是否重复,录入语句是否正确!"+s1);
}
variabl2637002();
}
});
if ((variabl1187.length()<3) || (variabl1187.lastIndexOf(",2,")>=0)){
variabl1567.setBounds(variabl1423,variabl2503-80,variabl2339,20);
panel.add(variabl1567);
variabl1423=variabl1423+variabl2339;
}
JButton exitButton = new JButton(variabl2405[3]);
exitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
frame.dispose();
}
});
if ((variabl1187.length()<3) || (variabl1187.lastIndexOf(",3,")>=0)){
exitButton.setBounds(variabl1423,variabl2503-80,variabl2339,20);
panel.add(exitButton);
variabl1423=variabl1423+variabl2339;
}
frame.add(panel);
frame.setVisible(true);
}
public static void variabl2637001(){
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1="delete from "+variabl2603;
sta4.executeUpdate(s1);
sta4.close();
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "删除操作出错,请检查数据表名是否正确!"+s1);
}
String s0="";
try{
main1.driver1();
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s0="insert into "+variabl2603+" (";
for (int k1=0;k1<variabl2517[1].length;k1++)
s0=s0+variabl2517[1][k1]+",";
s0=s0.substring(0,s0.length()-1)+" ) values (";
for (int i=0;i<model.getRowCount();i++){
s1=s0;
for (int j=0;j<variabl2517[1].length;j++){
if (variabl25111.lastIndexOf(","+variabl1501[j]+",")>=0){
s1=s1+model.getValueAt(i,j).toString().trim();
}
else{
s1=s1+"'"+model.getValueAt(i,j).toString().trim()+"'";
}
if (j!=variabl2517[1].length-1) s1=s1+",";
}
s1=s1+")";
sta4.executeUpdate(s1);
}
sta4.close();
con1.close();
}
catch(SQLException e1)
{
JOptionPane.showMessageDialog( null, "修改数据表操作出错,请检查语句是否正确!"+s1);
}
}
public static void variabl2637002(){
try {
con1=main1.getConn();
sta4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1="select * from "+variabl2603;
ResultSet rs=sta4.executeQuery(s1);
rs.last();
variabl1853=rs.getRow();
variabl2197=new String[variabl1853][variabl2651];
rs.absolute(1);
int c=0;
int b=0;
while(c<variabl1853) {
rs.absolute(c+1);
while(b<variabl2651){
variabl2197[c][b]=rs.getString(b+1);
b++;
}
c++;
b=0;
}
rs.close();
sta4.close();
con1.close();
}
catch(SQLException e)
{
JOptionPane.showMessageDialog( null, "读取数据出错!");
}
model = new DefaultTableModel(variabl2197,variabl2517[1]);
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for(int i=0;i<variabl2651;i++) {
TableColumn tc = table.getColumn(variabl2517[1][i]);
tc.setPreferredWidth(variabl1489[i]*14);
tc.setMaxWidth(variabl1489[i]*14);
tc.sizeWidthToFit();
}
panel.add(scrollPane);
scrollPane.setViewportView(table);
}
}
53.源码53,部件程序,从网页导入数据到当前表某个文本型字段数据导入部件。
/**
* 程序文件名:dataTransfer13.java
* 作者:程学先
* 功能:从网页导入数据到当前表某个文本型字段部件(dataTransfer13.java)。
* 完成日期:2014年4月22日
* 被调用语句:dataTransfer14.means(parameter)
* 必须提供的参数:"网页地址","表名","关键字段名","导入字段名”。
* 可选择参数:“要求按钮号表”。
* 导入字段应当是text或ntext类型字段。
* 本部件预设的按钮控件共5个,序号及按钮名:1.variabl1399,2.variabl1347,3.variabl1205,
* 4.删除当前记录,5.新记录存盘,6.退出"
* 开始运行后显示当前数据表中数据情况,点击“读网页内容”会将相关网页内容屏蔽部分字符后读到文本域中。
* 之后点击表格某一行指引所存记录,再点击“导入网页内容”,将网页内容读入到数据表所指记录的文本数据字段中。
* 也可点击“导入源程序”,但只有在源程序中不存在会导致语法错误或违反访问规则的字符时操作才会成功。
* 一般不要作此操作。
*/
import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.EventQueue;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableColumn;
public class dataTransfer13 extends JFrame {
private static dataTransfer13 frame1 = new dataTransfer13();
private static JTextArea variabl1825;
private static JTextField variabl17191;
private static int variabl2483 = 1000;
private static int variabl2503 = 700;
private static String s1 = "", s2 = "";
private static String variabl1719 = "";
private static String variabl2603 = "";
private static String variabl18250 = "";
private static String[] variabl1525;
private static String variabl1569 = "";
private static Connection cont4;
private static Statement stat4;
private static ResultSetMetaData rsmdt4;
static PreparedStatement pstmt;
private static ResultSet rs;
private static int variabl2651 = 0;
private static String[] variabl2517;
private static String[] variabl1501;
private static int[] variabl1489;
private static int variabl1853 = 0;
private static String[] variabl1507;
private static int variabl2547 = 0, variabl2593 = 0;
private static String[][] variabl2197;
private static DefaultTableModel variabl1965;
private static JTable variabl2655;
private static String[] variabl1377;
private static int[] variabl1287;
private static String variabl25111 = main1.variabl1545;
public static String[] sw = new String[11];
static String[] variabl2405 = null;
static String variabl1187;
static int variabl2851 = 0, variabl2339 = 0;
static void means(String parameter[]) {
sw = new String[11];
sw[1] = main1.sw1 + "";
sw[2] = main1.sw2;
sw[3] = main1.sw3;
sw[4] = main1.sw4;
sw[5] = main1.sw5;
sw[6] = main1.sw6;
sw[7] = main1.sw7;
sw[8] = main1.sw8;
sw[9] = main1.sw9;
sw[10] = main1.sw10;
main1.sw1 = 0;
main1.sw2 = "";
main1.sw3 = "";
main1.sw4 = "";
main1.sw5 = "";
main1.sw6 = "";
main1.sw7 = "";
main1.sw8 = "";
main1.sw9 = "";
main1.sw10 = "";
String[] variabl240501 = { "读网页内容", "导入源程序", "导入网页内容", "删除当前记录", "新记录存盘", "退出" };
variabl2405 = new String[variabl240501.length];
variabl2851 = 0;
for (int i0 = 0; i0 < variabl240501.length; i0++)
variabl2405[i0] = variabl240501[i0];
if (parameter[11].length() > 0) {
variabl1187 = parameter[11];
variabl1187 = variabl1187.replaceAll(";", ";");
variabl1187 = variabl1187.replaceAll("。", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
if (variabl1187.indexOf(";") > 0) {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
variabl2405[Integer.parseInt((s602[0]))] = s602[1];
variabl1187 = variabl1187 + "," + s602[0];
variabl2851++;
} else {
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
}
else {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
variabl1187 = s601[i01];
else
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
} else {
variabl1187 = "";
for (int i1 = 0; i1 < variabl2405.length; i1++) {
if (i1 == 0)
variabl1187 = variabl1187 + i1;
else
variabl1187 = variabl1187 + "," + i1;
variabl2851++;
}
}
variabl1187 = "," + variabl1187 + ",";
variabl2483 = main1.variabl2483;
variabl2503 = main1.variabl2503;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
variabl2483 = m1;
variabl2503 = m2;
}
} catch (Exception e2) {
}
variabl1719 = parameter[2];
variabl2603 = parameter[4];
variabl1525 = parameter[6].split(",");
variabl1287 = new int[variabl1525.length];
variabl1377 = new String[variabl1525.length];
variabl1569 = parameter[9];
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + variabl2603;
if ((sw[3].length() > 0) && (sw[4].length() > 0))
s1 = s1 + " where " + sw[4] + " = '" + sw[5] + "'";
rs = stat4.executeQuery(s1);
rs.last();
variabl1853 = rs.getRow();
rsmdt4 = rs.getMetaData();
variabl2651 = rsmdt4.getColumnCount();
variabl2517 = new String[variabl2651];
variabl1501 = new String[variabl2651];
variabl1489 = new int[variabl2651];
variabl1507 = new String[variabl2651];
variabl2197 = new String[variabl1853][variabl2651];
for (int i = 0; i < variabl2651; i++) {
variabl2517[i] = rsmdt4.getColumnName(i + 1);
variabl1501[i] = rsmdt4.getColumnTypeName(i + 1);
variabl1489[i] = rsmdt4.getColumnDisplaySize(i + 1);
variabl1507[i] = "";
}
int c = 0, b = 0;
for (int j = 0; j < variabl1525.length; j++)
for (int i = 0; i < variabl2651; i++) {
if (variabl1525[j].trim().equals(variabl2517[i].trim())) {
variabl1287[j] = i;
break;
}
}
rs.absolute(1);
c = 0;
b = 0;
while (c < variabl1853) {
rs.absolute(c + 1);
while (b < variabl2651) {
variabl2197[c][b] = rs.getString(b + 1);
b++;
}
c++;
b = 0;
}
rs.close();
stat4.close();
cont4.close();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "连接数据库出错!" + s1);
}
frame1.setTitle("读取网页内容到字段中。 作者:程学先");
frame1.setBounds(10, 10, variabl2483, variabl2503);
frame1.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
frame1.getContentPane().setLayout(null);
final JPanel variabl2613 = new JPanel();
variabl2613.setBounds(10, 10, variabl2483 - 10, variabl2503 - 10);
variabl2613.setLayout(null);
frame1.add(variabl2613);
final JLabel variabl1807 = new JLabel("请输入网址:");
variabl1807.setBounds(10, 10, 80, 20);
variabl2613.add(variabl1807);
variabl17191 = new JTextField(variabl1719);
variabl17191.setBounds(90, 10, 700, 20);
variabl2613.add(variabl17191);
int variabl2337 = 30;
variabl2339 = (variabl2483 - 100) / variabl2851;
final JButton variabl1399 = new JButton(variabl2405[0]);
variabl1399.setBounds(variabl2337, variabl2503 - 110, 140, 20);
variabl2337 = variabl2337 + variabl2339;
if (variabl1187.indexOf(",0,") >= 0)
variabl2613.add(variabl1399);
variabl1825 = new JTextArea(" ", 100, 200);
variabl1825.setFont(new Font("", Font.BOLD, 14));
variabl1825.setLineWrap(true);
final JScrollPane scrollPane = new JScrollPane(variabl1825);
scrollPane.setBounds(10, 50, 880, 200);
variabl2613.add((Component) scrollPane);
variabl1965 = new DefaultTableModel(variabl2197, variabl2517);
variabl2655 = new JTable(variabl1965);
variabl1965.addRow(variabl1507);
variabl2655.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
variabl2655.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < variabl2651; i++) {
TableColumn tc = variabl2655.getColumn(variabl2517[i]);
JTableHeader header = variabl2655.getTableHeader();
tc.setPreferredWidth(variabl1489[i] * 14);
tc.setMaxWidth(variabl1489[i] * 14);
tc.sizeWidthToFit();
}
final JScrollPane scrollPane1 = new JScrollPane();
scrollPane1.setBounds(10, 270, variabl2483 - 110, 290);
scrollPane1.setViewportView(variabl2655);
variabl2613.add(scrollPane1, BorderLayout.CENTER);
variabl2655.addMouseListener(new MouseAdapter() {
public void mouseClicked(MouseEvent e) {
int selectedRow = variabl2655.getSelectedRow();
variabl2593 = variabl2655.getSelectedRow();
}
});
variabl1399.addActionListener(new ActionListener() {
public void actionPerformed(final ActionEvent e) {
JOptionPane.showMessageDialog(null, "分析网页需要一点时间,请耐心等待。");
String variabl2525 = variabl17191.getText().trim();
URL url = null;
URLConnection conn = null;
Collection<String> variabl1911 = new ArrayList<String>();
String regex = "<[^>]+>";
String[] s4 = new String[3000];
String[] s5 = new String[1000];
char x1 = '"', x2 = 10, x3 = 13;
int n = 0, k1 = 0, k2 = 0, k3 = 0;
String candidate = "";
Pattern p = Pattern.compile(regex);
Matcher matcher = p.matcher(candidate);
int c = 0;
try {
url = new URL(variabl2525);
conn = url.openConnection();
conn.connect();
InputStream is = conn.getInputStream();
InputStreamReader in = new InputStreamReader(is, "UTF-8");
BufferedReader br = new BufferedReader(in);
String variabl2157;
variabl18250 = "";
k1 = 0;
for (int i = 0; (variabl2157 = br.readLine()) != null; i++) {
if ((k1 == 0) && (variabl2157.lastIndexOf("<script") >= 0)
&& (variabl2157.lastIndexOf("<\\/script") < 0)) {
k2 = 0;
k1 = 1;
s5[k2] = variabl2157;
k2++;
} else if (k1 == 1) {
if (variabl2157.lastIndexOf("</script") >= 0) {
if (variabl2157.lastIndexOf("name") >= 0) {
k3 = 1;
}
k1 = 0;
k2 = 0;
k3 = 0;
} else {
s5[k2] = variabl2157;
k2++;
}
if (variabl2157.lastIndexOf("name") >= 0) {
s4[c] = variabl2157.replaceAll(
x1 + "name" + x1 + ":" + x1, "")
.replaceAll(x1 + ",", "");
c++;
k3 = 1;
}
} else {
s4[c] = variabl2157;
c++;
}
variabl18250 = variabl18250 + variabl2157;
}
} catch (Exception e1) {
e1.printStackTrace();
}
s2 = "";
for (int i = 0; i < c; i++) {
s1 = s4[i];
s1 = s1.replaceAll("<[^>]+>", "");
s1 = s1.replaceAll("\\|", "");
s1 = s1.replaceAll("\\s{2}", "");
if (s1.trim().length() > 0) {
s2 = s2 + s1 + x2 + x3;
}
}
variabl1825.setText(s2);
}
});
JButton variabl1347 = new JButton(variabl2405[1]);
variabl1347.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "";
for (int i = 0; i < variabl1525.length; i++) {
if (variabl1965.getValueAt(variabl2593, variabl1287[i]) == null) {
JOptionPane.showMessageDialog(null, "关键字不能缺少!");
return;
}
variabl1377[i] = variabl1965.getValueAt(variabl2593, variabl1287[i]).toString();
if (main1.variabl25111.lastIndexOf("," + variabl1501[variabl1287[i]] + ",") > 0)
s1 = s1 + variabl1525[variabl1287[i]] + "=" + variabl1377[i];
else
s1 = s1 + variabl1525[variabl1287[i]] + "='" + variabl1377[i] + "' ";
if (i < variabl1525.length - 1)
s1 = s1 + " and ";
}
s1 = "update " + variabl2603 + " set " + variabl2517[Integer.parseInt(variabl1569)]
+ " = '" + variabl18250 + "' where " + s1;
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
});
variabl1347.setBounds(variabl2337, variabl2503 - 110, variabl2339, 20);
if (variabl1187.indexOf(",1,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
variabl2613.add(variabl1347);
}
JButton variabl1205 = new JButton(variabl2405[2]);
variabl1205.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "";
for (int i = 0; i < variabl1525.length; i++) {
if (variabl1965.getValueAt(variabl2593, variabl1287[i]) == null) {
JOptionPane.showMessageDialog(null, "关键字不能缺少!");
return;
}
variabl1377[i] = variabl1965.getValueAt(variabl2593, variabl1287[i]).toString();
if (main1.variabl25111.lastIndexOf("," + variabl1501[variabl1287[i]] + ",") >= 0)
s1 = s1 + variabl1525[variabl1287[i]] + "=" + variabl1377[i];
else
s1 = s1 + variabl1525[variabl1287[i]] + "='" + variabl1377[i] + "' ";
if (i < variabl1525.length - 1)
s1 = s1 + " and ";
}
s1 = "update " + variabl2603 + " set " + variabl2517[Integer.parseInt(variabl1569)]
+ " = '" + variabl1825.getText() + "' where " + s1;
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
});
variabl1205.setBounds(variabl2337, variabl2503 - 110, variabl2339, 20);
if (variabl1187.indexOf(",2,") >= 0) {
variabl2613.add(variabl1205);
variabl2337 = variabl2337 + variabl2339;
}
JButton variabl1893 = new JButton(variabl2405[3]);
variabl1893.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "";
for (int i = 0; i < variabl1525.length; i++) {
if (variabl1965.getValueAt(variabl2593, variabl1287[i]) == null) {
JOptionPane.showMessageDialog(null, "关键字不能缺少!");
return;
}
variabl1377[i] = variabl1965.getValueAt(variabl2593, variabl1287[i]).toString();
if (main1.variabl25111.lastIndexOf("," + variabl1501[variabl1287[i]] + ",") >= 0)
s1 = s1 + variabl1525[variabl1287[i]] + "=" + variabl1377[i];
else
s1 = s1 + variabl1525[variabl1287[i]] + "='" + variabl1377[i] + "' ";
if (i < variabl1525.length - 1)
s1 = s1 + " and ";
}
variabl1965.removeRow(variabl2593);
s1 = "delete from " + variabl2603 + " where " + s1;
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
});
variabl1893.setBounds(variabl2337, variabl2503 - 110, variabl2339, 20);
if (variabl1187.indexOf(",3,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
variabl2613.add(variabl1893);
}
JButton variabl1475 = new JButton(variabl2405[4]);
variabl1475.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "insert into " + variabl2603 + " (";
for (int k1 = 0; k1 < variabl2517.length; k1++)
s1 = s1 + variabl2517[k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < variabl2517.length; j++) {
s2 = variabl1965.getValueAt(variabl2593, j).toString();
if (main1.variabl1545.lastIndexOf("," + variabl1501[j] + ",") >= 0) {
if (s2 == null)
s2 = "0";
s1 = s1 + s2;
} else {
if (s2 == null)
s2 = " ";
s1 = s1 + "'" + s2 + "'";
}
if (j != variabl2517.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
try {
cont4 = main1.getConn();
stat4 = cont4.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = cont4.prepareStatement(s1);
pstmt.execute();
cont4.close();
} catch (Exception e2) {
e2.printStackTrace();
}
variabl1965.addRow(variabl1507);
}
});
variabl1475.setBounds(variabl2337, variabl2503 - 110, variabl2339, 20);
if (variabl1187.indexOf(",4,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
variabl2613.add(variabl1475);
}
JButton variabl2599 = new JButton(variabl2405[5]);
variabl2599.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if ((sw[2].length() > 0) && (sw[3].length() > 0)
&& (sw[4].length() > 0))
WorkflowDrivek1.means(sw);
frame1.setVisible(false);
frame1.dispose();
}
});
variabl2599.setBounds(variabl2337, variabl2503 - 110, variabl2339, 20);
if (variabl1187.indexOf(",5,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
variabl2613.add(variabl2599);
}
frame1.setVisible(true);
}
}
54.源码54,部件程序,灵活导出到数据表或文件程序。以表格为界面可变换标签后作数据浏览、排序显示、组合排序显示、导出到数据库、导出到txt文件、打印等使用数据导出部件。
/**
* 程序文件名:dataTransfer16.java
* 作者:程学先
* 功能:灵活导出到数据表或文件程序。以表格为界面可变换标签后作数据浏览、排序显示、组合排序显示、导出到数据库、导出到txt文件、打印等使用。
* 完成日期:2013年1月22日
* 被调用语句:dataTransfer16.TableModel();
* 必须提供的参数:数据“表名”、“关键字”。
* 可选择参数:“字段号表”、“要求按钮号表”、“打印格式文件名”。
* 本程序允许借用字典表变换标签内容,默认字典表名为表名加“字典表”。
* 导出文件或表名在运行时由操作者选择决定。
* 关键字段名为识别与区分记录的主要标志,不允许重复,不允许为空,可以是单一字段,也可以是多个字段,
* 如果是多字段,以英文逗号分隔。如果不要求导出到数据库,可以不给具体值。
* 要求字段号表:所操作数据可以是表的全部字段,也可以只涉及部分字段。如果选择全部字段,初始值设为"";
* 否则写入所有将涉及的字段在表中的顺序后,号与号之间以英文逗号分隔。
* 本部件预设的按钮控件共5个,序号及按钮名:1排序、2接续排序、3导出到数据库、4导出到txt文件、5打印、6退出
* 在入口处通过“要求按钮号表”选择按钮以实现所需要的功能。
* 排序:要求先点击表格中某一列,将按本列数据从小到大对全表格数据排序。按本按钮后,标签变为还原,再按后恢复原表数据模样。
* 接续排序:在按“排序”按钮后本按钮激活,如果先点某一列,则会在上一次排序基础上对于上一排序列中值相同者按当前列继续排序。
* 导出到数据库:将当前显示数据导出到一个数据表中。将提问表名,输入表名后会在当前数据库中查是否存在该表?
* 如果已经存在该表,会检查字段匹配情况。
* 如果完全匹配,则提问导入方式:1、删除原表数据,用新表覆盖。2、与原表数据关键字相同者用新数据覆盖,否则添加。3、全部添加到原表尾。
* 如果不完全匹配,则先提问:是只导入匹配部分的数据,还是补充添加那些原表中不存在的字段后再导入。之后提问导入方式。
* 如果数据库中没有该表,将表格中数据全部复制存到新表中。
* 导出到txt文件:将提问导出格式:1、每字段值一行导出。2、各字段按设定长度全部以字符串形式导出,每条记录一行,字段数据间无分隔。
* 3、每条记录一行,数字各类型数据以10进制数据形式表示;其他类型数据均以字符串形式表示,加双引号与逗号分隔。
* 4、提问分隔符,每条记录一行,各类型数据以自然形式表示,字符串类型数据也不加双引号,字段间加所输入的分隔符分隔。
* 分隔符可以由多个字符组成,例如:####
* 打印:如果提供了表格式打印格式文件名,调用dataPreview1程序组织打印。
* 否则,提供JDK的print类打印本表格数据。
*/
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.io.File.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
public class dataTransfer16 extends JFrame {
private static JTable table;
static Connection con;
static String variabl2603 = main1.variabl2603;
static String variabl1873 = "";
static int variabl2483 = main1.variabl2483, variabl2503 = main1.variabl2503;
static int variabl2651;
static int variabl2113;
static String[] variabl2517;
static String[] variabl1913;
static String[] variabl2411;
static String[] variabl1501;
static String[] variabl1169;
static int[] variabl1489, variabl1571;
static int[] variabl1119;
static int variabl1853, variabl18531 = 0;
static String[][] variabl2197;
static String[][] variabl1701;
static String variabl1153;
static JButton sortButton1, sortButton, printButton, exitButton;
static JButton variabl26391, variabl26392, variabl26393;
static String[][] variabl1753;
static String[][] variabl1631;
static int variabl1901 = 0, variabl1691 = 0, variabl1739 = 0, variabl1899 = 0;
static String[] variabl1507;
static String[] variabl1377;
static int[] variabl1287;
static int selectedRow = 0;
static String variabl1685;
static String variabl2429;
static String variabl1525;
static String variabl1079;
static String[] variabl2405 = null;
static String variabl1187;
static int variabl2851 = 0, variabl2339 = 0;
static DefaultTableModel model;
static String[] rowValues = new String[variabl2113];
static String variabl1679 = "", variabl1463 = "";
static String s1 = "", s2 = "", s3 = "";
static String[] variabl1425;
static PreparedStatement pstmt;
static ResultSetMetaData rsmd3;
static Statement sta3;
public static void means(String[] parameter) {
variabl2603 = main1.variabl2603;
variabl2483 = main1.variabl2483;
variabl2503 = main1.variabl2503;
variabl18531 = 0;
variabl1901 = 0;
variabl1691 = 0;
variabl1739 = 0;
variabl1899 = 0;
selectedRow = 0;
rowValues = new String[variabl2113];
s1 = "";
s2 = "";
s3 = "";
variabl2603 = parameter[4];
variabl2429 = parameter[0];
variabl1685 = variabl2603;
variabl1153 = parameter[8];
variabl1079 = parameter[15];
String[] variabl240501 = { "排序", "接续排序", "打印", "导出到数据表", "导出到txt文件",
"退出" };
variabl2405 = new String[variabl240501.length];
variabl2851 = 0;
for (int i0 = 0; i0 < variabl240501.length; i0++)
variabl2405[i0] = variabl240501[i0];
if (parameter[11].length() > 0) {
variabl1187 = parameter[11];
variabl1187 = variabl1187.replaceAll(";", ";");
variabl1187 = variabl1187.replaceAll("。", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
if (variabl1187.indexOf(";") > 0) {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
variabl2405[Integer.parseInt((s602[0]))] = s602[1];
variabl1187 = variabl1187 + "," + s602[0];
variabl2851++;
} else {
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
} else {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
variabl1187 = s601[i01];
else
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
} else {
variabl1187 = "";
for (int i1 = 0; i1 < variabl2405.length; i1++) {
if (i1 == 0)
variabl1187 = variabl1187 + i1;
else
variabl1187 = variabl1187 + "," + i1;
variabl2851++;
}
}
variabl1187 = "," + variabl1187 + ",";
variabl2483 = main1.variabl2483;
variabl2503 = main1.variabl2503;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
variabl2483 = m1;
variabl2503 = m2;
}
} catch (Exception e2) {
}
variabl1525 = parameter[6];
try {
con = main1.getConn();
sta3 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = sta3.executeQuery("select * from " + variabl2603);
rsmd3 = rs.getMetaData();
variabl2651 = rsmd3.getColumnCount();
variabl2517 = new String[variabl2651];
variabl1501 = new String[variabl2651];
variabl1489 = new int[variabl2651];
for (int i = 0; i < variabl2651; i++) {
variabl2517[i] = rsmd3.getColumnName(i + 1);
variabl1501[i] = rsmd3.getColumnTypeName(i + 1);
variabl1489[i] = rsmd3.getPrecision(i + 1);
}
rs.last();
variabl1853 = rs.getRow();
if ((variabl1153 != null) && (variabl1153.length() > 0)) {
String[] f1 = variabl1153.split(",");
variabl2113 = f1.length;
variabl1913 = new String[variabl2113];
variabl1169 = new String[variabl2113];
variabl1119 = new int[variabl2113];
for (int i = 0; i < variabl2113; i++) {
variabl1913[i] = variabl2517[Integer.valueOf(f1[i])];
variabl1169[i] = variabl1501[Integer.valueOf(f1[i])];
variabl1119[i] = variabl1489[Integer.valueOf(f1[i])];
s2 = s2 + variabl1913[i] + ",";
}
s2 = s2.substring(0, s2.length() - 1);
variabl2197 = new String[variabl1853][variabl2113];
rs = sta3.executeQuery("select " + s2 + " from " + variabl2603);
rsmd3 = rs.getMetaData();
} else {
variabl2113 = variabl2651;
variabl1913 = new String[variabl2113];
variabl1169 = new String[variabl2113];
variabl1119 = new int[variabl2113];
for (int i = 0; i < variabl2113; i++) {
variabl1913[i] = variabl2517[i];
variabl1169[i] = variabl1501[i];
variabl1119[i] = variabl1489[i];
}
variabl2197 = new String[variabl1853][variabl2651];
}
rs.absolute(1);
for (int i = 0; i < variabl1853; i++) {
rs.absolute(i + 1);
for (int j = 0; j < variabl2113; j++) {
variabl2197[i][j] = rs.getString(j + 1);
}
}
rs.close();
sta3.close();
} catch (Exception e) {
e.printStackTrace();
}
variabl1701 = new String[variabl1853][variabl2113];
variabl1187 = "," + variabl1187 + ",";
variabl2411 = new String[variabl2113];
int b = 0;
while (b < variabl2113) {
variabl2411[b] = variabl1913[b];
b++;
}
if (variabl2429.indexOf(variabl2603 + "字典表") >= 0)
variabl1873 = variabl2603 + "字典表";
else
variabl1873 = "";
if (variabl1873.length() > 0) {
con = main1.getConn();
try {
Statement sta = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
s1 = "select * from " + variabl1873;
ResultSet rs = sta.executeQuery(s1);
while (rs.next()) {
String variabl2153 = rs.getString(1);
String variabl1755 = rs.getString(2);
b = 0;
while (b < variabl2113) {
if (variabl2411[b].compareTo(variabl2153) == 0) {
variabl2411[b] = variabl1755;
break;
}
b++;
}
}
rs.close();
sta.close();
} catch (Exception e1) {
e1.printStackTrace();
}
}
dataTransfer16 frame = new dataTransfer16();
frame.setTitle("表格式显示部件之1 作者:程学先");
frame.setBounds(10, 10, variabl2483, variabl2503);
frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
final JScrollPane scrollPane = new JScrollPane();
scrollPane.setBounds(0, 0, variabl2483 - 100, variabl2503 - 100);
frame.setLayout(null);
frame.getContentPane().add(scrollPane, BorderLayout.CENTER);
model = new DefaultTableModel(variabl2197, variabl2411);
for (int c = 0; c < variabl1853; c++)
for (b = 0; b < variabl2113; b++)
variabl1701[c][b] = variabl2197[c][b];
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < variabl2113; i++) {
TableColumn tc = table.getColumn(variabl2411[i]);
JTableHeader header = table.getTableHeader();
tc.setPreferredWidth(variabl1119[i] * 14);
tc.setMaxWidth(variabl1119[i] * 14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
frame.setVisible(true);
table.addMouseListener(new MouseAdapter() {
public void mouseClicked(MouseEvent e) {
selectedRow = table.getSelectedRow();
}
});
int variabl2339 = (variabl2483 - 30) / variabl2851;
int variabl1423 = 10;
variabl1753 = new String[variabl1853][variabl2113 + 1];
variabl1631 = new String[variabl1853][variabl2113 + 1];
for (b = 0; b < variabl2113; b++) {
for (int c = 0; c < variabl1853; c++) {
if (variabl1701[c][b] == null)
variabl1753[c][b] = "";
else
variabl1753[c][b] = variabl1701[c][b];
}
if (variabl1169[b].compareTo("int") == 0) {
for (int c = 0; c < variabl1853; c++) {
variabl1753[c][b] = "0000000000" + variabl1753[c][b];
variabl1753[c][b] = variabl1753[c][b]
.substring(variabl1753[c][b].length() - 10);
}
} else if ((variabl1169[b].compareTo("float") == 0)
|| (variabl1169[b].compareTo("numeric") == 0)) {
for (int c = 0; c < variabl1853; c++) {
variabl1753[c][b] = "0000000000" + variabl1753[c][b];
int k1 = variabl1753[c][b].lastIndexOf('.');
if (k1 == -1) {
k1 = variabl1753[c][b].length();
variabl1753[c][b] = variabl1753[c][b] + ".000000";
} else
variabl1753[c][b] = variabl1753[c][b] + "000000";
variabl1753[c][b] = variabl1753[c][b].substring(k1 - 10,
k1 + 1)
+ variabl1753[c][b].substring(k1 + 1, k1 + 6);
}
}
}
for (int c = 0; c < variabl1853; c++)
variabl1753[c][variabl2113] = "" + c;
for (int c = 0; c < variabl1853; c++)
for (b = 0; b < variabl2113 + 1; b++)
variabl1631[c][b] = variabl1753[c][b];
variabl1901 = 0;
variabl1691 = 0;
sortButton = new JButton(variabl2405[0]);
sortButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
rowValues = new String[variabl2113];
if (sortButton.getText().trim().equals("排序")) {
if (table.getSelectedColumn() < 0)
JOptionPane.showMessageDialog(null,
"请先点击要排序的某一列,再按本按钮!");
else {
sortButton.setText("还原");
sortButton1.setEnabled(true);
variabl1899 = table.getSelectedColumn();
variabl1739 = table.getSelectedColumn();
}
String s;
int i, j, k = 0;
for (int c = 0; c < variabl1853; c++) {
for (i = 0; i < variabl1853 - c - 1; i++) {
if (variabl1753[i][variabl1739]
.compareTo(variabl1753[i + 1][variabl1739]) > 0)
for (j = 0; j <= variabl2113; j++) {
s = variabl1753[i][j];
variabl1753[i][j] = variabl1753[i + 1][j];
variabl1753[i + 1][j] = s;
}
}
}
model.setRowCount(0);
for (int c = 0; c < variabl1853; c++) {
int n = Integer.parseInt(variabl1753[c][variabl2113]);
for (int b = 0; b < variabl2113; b++) {
variabl2197[c][b] = variabl1701[n][b];
rowValues[b] = variabl1701[n][b];
}
model.addRow(rowValues);
}
} else {
sortButton.setText("排序");
sortButton1.setEnabled(false);
model.setRowCount(0);
for (int c = 0; c < variabl1853; c++) {
for (int b = 0; b < variabl2113; b++) {
rowValues[b] = variabl1701[c][b];
variabl1753[c][b] = variabl1631[c][b];
}
model.addRow(rowValues);
variabl1753[c][variabl2113] = variabl1631[c][variabl2113];
}
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",0,") >= 0)) {
sortButton.setBounds(variabl1423, variabl2503 - 80,
variabl2339 - 10, 20);
frame.add(sortButton);
variabl1423 = variabl1423 + variabl2339;
}
sortButton1 = new JButton(variabl2405[1]);
sortButton1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
variabl1739 = table.getSelectedColumn();
String s = "", s2 = variabl1753[0][variabl1899];
int n1 = 0, n2 = 0;
int i, j, k = 0;
for (int c = 0; c < variabl1853; c++) {
if ((c == variabl1853 - 1)
|| (s2.compareTo(variabl1753[c + 1][variabl1899]) != 0)) {
if (c < variabl1853 - 1)
s2 = variabl1753[c + 1][variabl1899];
n2 = c;
for (i = n1; i < n2; i++) {
for (j = n1; j < n2 + n1 - i; j++) {
if (variabl1753[j][variabl1739]
.compareTo(variabl1753[j + 1][variabl1739]) > 0)
for (k = 0; k <= variabl2113; k++) {
s = variabl1753[j][k];
variabl1753[j][k] = variabl1753[j + 1][k];
variabl1753[j + 1][k] = s;
}
}
}
n1 = n2 + 1;
}
}
variabl1899 = variabl1739;
model.setRowCount(0);
for (int c = 0; c < variabl1853; c++) {
int n = Integer.parseInt(variabl1753[c][variabl2113]);
for (int b = 0; b < variabl2113; b++) {
rowValues[b] = variabl1701[n][b];
}
model.addRow(rowValues);
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",1,") >= 0)) {
sortButton1.setBounds(variabl1423, variabl2503 - 80,
variabl2339 - 5, 20);
frame.add(sortButton1);
variabl1423 = variabl1423 + variabl2339;
}
sortButton1.setEnabled(false);
printButton = new JButton(variabl2405[2]);
printButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
if (variabl1079.trim().length() == 0)
table.print();
else
dataPreview1.printView1(variabl2197, variabl1079);
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "打印失败!");
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",2,") >= 0)) {
printButton.setBounds(variabl1423, variabl2503 - 80,
variabl2339 - 5, 20);
frame.add(printButton);
variabl1423 = variabl1423 + variabl2339;
}
variabl26391 = new JButton(variabl2405[3]);
variabl26391.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
int k = 0;
int k0 = 0;
String[] variabl1381;
String[] variabl1469;
int[] variabl1583;
int[] variabl1131 = new int[variabl2113];
int variabl1499 = 0;
if (variabl1679.length() == 0)
variabl1679 = JOptionPane.showInputDialog("请输入数据表名称", "");
k = 0;
if (("," + variabl2429 + ",").lastIndexOf(variabl1679) >= 0)
k = 1;
if (k == 0) {
s1 = "select * into " + variabl1679 + " from "
+ variabl2603;
try {
con = main1.getConn();
if (!con.isClosed()) {
Statement rsmd = con.createStatement();
rsmd.executeUpdate(s1);
}
} catch (Exception e1) {
e1.printStackTrace();
}
return;
}
if (k != 0) {
con = main1.getConn();
s1 = "select * from " + variabl1679;
try {
Statement sta = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = sta.executeQuery(s1);
ResultSetMetaData rsmd = rs.getMetaData();
int variabl1461 = rsmd.getColumnCount();
variabl1381 = new String[variabl1461];
variabl1469 = new String[variabl1461];
variabl1583 = new int[variabl1461];
int b0 = 0;
while (b0 < variabl1461) {
variabl1381[b0] = rsmd.getColumnName(b0 + 1);
b0++;
}
int k1 = 0;
variabl1499 = 0;
String variabl25110 = "";
for (int i = 0; i < variabl2113; i++) {
k = 0;
for (int j = 0; j < variabl1461; j++)
if (variabl1913[i].compareTo(variabl1381[j]) == 0) {
k = 1;
break;
}
if (k == 0) {
variabl1469[k1] = variabl1913[i];
variabl1583[k1] = i;
k1++;
} else {
variabl1131[variabl1499] = i;
variabl1499++;
}
}
String[] options = new String[] { "在导出表中增加这些字段",
"只导出同名字的字段值", "退出" };
int num = 0;
if (variabl1469.length > 0) {
num = JOptionPane.showOptionDialog(null,
"有些字段在导出目标表中不存在,请定处理方式?", "选择字段匹配方式",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE, null,
options, "只导出同名字的字段值");
if (options[num].trim().equals("退出"))
return;
if (options[num].trim().equals("在导出表中增加这些字段")) {
try {
con = main1.getConn();
if (!con.isClosed()) {
Statement rsmd1 = con.createStatement();
for (int i = 0; i < k1; i++) {
variabl25110 = variabl1169[variabl1583[i]];
s1 = "ALTER TABLE " + variabl1679
+ " ADD "
+ variabl1469[i] + " "
+ variabl25110;
if (variabl25110
.lastIndexOf("char") >= 0)
s1 = s1
+ "("
+ ("" + variabl1119[variabl1583[i]])
+ ")";
else if ((variabl25110
.compareTo("decimal") == 0)
|| (variabl25110
.compareTo("numeric") == 0))
s1 = s1
+ "("
+ ("" + variabl1119[variabl1583[i]])
+ ","
+ variabl1571[variabl1583[i]]
+ ")";
variabl1131[variabl1499] = variabl1583[i];
variabl1499++;
rsmd1.executeUpdate(s1);
}
for (int i = 0; i < variabl1499; i++) {
System.out.println(variabl1131[i]);
}
}
} catch (Exception e1) {
e1.printStackTrace();
}
}
}
rs.close();
sta.close();
} catch (Exception e1) {
e1.printStackTrace();
}
}
String[] options = new String[] { "覆盖原表数据", "替代原记录", "添加到原表尾",
"退出" };
int num = JOptionPane.showOptionDialog(null,
"是否复盖原表内容(原表数据将删除)?", "选择导出方式",
JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, options,
"覆盖原表数据");
if (options[num].trim().equals("退出"))
return;
if (options[num].trim().equals("替代原记录"))
k0 = 1;
if (options[num].trim().equals("覆盖原表数据")) {
con = main1.getConn();
s1 = "delete from " + variabl1679;
try {
sta3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt = con.prepareStatement(s1);
pstmt.execute();
con.close();
sta3.close();
} catch (Exception e2) {
e2.printStackTrace();
}
for (int i = 0; i < variabl1853; i++)
dataTransfer16.insert1(i);
}
if (k0 == 0) {
String s3 = "(";
for (int i = 0; i < variabl1499; i++) {
s3 = s3 + variabl2517[variabl1131[i]];
if (i < variabl1499 - 1)
s3 = s3 + ",";
else
s3 = s3 + ")";
}
String sp = "";
for (int j = 0; j < variabl1853; j++) {
s1 = "";
for (int i = 0; i < variabl1499; i++) {
int k1 = variabl1131[i];
sp = variabl2197[j][k1];
if (sp == null)
sp = "";
if ((variabl1169[k1].lastIndexOf("int") >= 0)
|| (variabl1169[k1].compareTo("decimal") == 0)
|| (variabl1169[k1].compareTo("numeric") == 0)) {
if (sp.length() == 0)
sp = "0";
s1 = s1 + sp;
} else {
s1 = s1 + "'" + sp + "'";
}
if (i < variabl1499 - 1)
s1 = s1 + ",";
}
s1 = "insert into " + variabl1679 + s3 + " values ("
+ s1 + ")";
try {
pstmt = con.prepareStatement(s1);
pstmt.execute();
} catch (Exception e2) {
e2.printStackTrace();
}
}
} else {
con = main1.getConn();
try {
sta3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s1 = "";
int j = 0;
String[] variabl15251 = variabl1525.split(",");
for (int i = 0; i < variabl15251.length; i++) {
variabl1377[i] = model.getValueAt(selectedRow,
variabl1287[i]).toString();
if (variabl1377[i].compareTo(" ") < 0)
j = -1;
s1 = s1 + variabl15251[variabl1287[i]] + "='"
+ variabl1377[i] + "' ";
if (i < variabl15251.length - 1)
s1 = s1 + " and ";
}
if (j >= 0) {
s3 = " where " + s1;
s1 = "select * from " + variabl2603 + " where "
+ s1;
ResultSet rs = sta3.executeQuery(s1);
rs.last();
variabl18531 = rs.getRow();
s1 = "";
s2 = " set ";
for (int i = 0; i < variabl2651; i++) {
variabl1425[i] = model.getValueAt(selectedRow,
i).toString();
if (variabl1425[i] == null)
variabl1425[i] = "";
if ((variabl1169[i].lastIndexOf("int") >= 0)
|| (variabl1169[i].compareTo("decimal") == 0)
|| (variabl1169[i].compareTo("numeric") == 0)) {
if (variabl1425[i].length() == 0)
variabl1425[i] = "0";
s1 = s1 + variabl1425[i];
s2 = s2 + variabl2517[i] + "="
+ variabl1425[i];
} else {
s1 = s1 + "'" + variabl1425[i] + "'";
s2 = s2 + variabl2517[i] + "='"
+ variabl1425[i] + "'";
}
if (i < variabl2651 - 1) {
s1 = s1 + ",";
s2 = s2 + ",";
}
}
if (variabl18531 == 0) {
s1 = "insert into " + variabl2603
+ " values (" + s1 + ")";
} else {
s1 = "update " + variabl2603 + s2 + s3;
}
pstmt = con.prepareStatement(s1);
pstmt.execute();
rs.close();
con.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",3,") >= 0)) {
variabl26391.setBounds(variabl1423, variabl2503 - 80,
variabl2339 - 5, 20);
frame.add(variabl26391);
variabl1423 = variabl1423 + variabl2339;
}
variabl26392 = new JButton(variabl2405[4]);
variabl26392.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (variabl1463.length() == 0)
variabl1463 = JOptionPane.showInputDialog("请输入导出文件名名称", "");
String[] options = new String[] { "每字段数据一行", "定长等长每记录一行",
"逗号分隔", "自定义符号分隔", "退出" };
int num = JOptionPane.showOptionDialog(null, "是否按每字段数据一行?",
"选择导出文件格式", JOptionPane.YES_NO_CANCEL_OPTION,
JOptionPane.INFORMATION_MESSAGE, null, options,
"覆盖原表数据");
if (options[num].trim().equals("退出"))
return;
else if (options[num].trim().equals("每字段数据一行")) {
try {
File file = new File(variabl1463);
if (!file.exists()) {
file.createNewFile();
}
char x1 = 10, x2 = 13;
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < variabl1853; j1++)
for (int j2 = 0; j2 < variabl2113; j2++) {
fs.write((variabl2197[j1][j2] + x2).getBytes());
}
fs.close();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
} else if (options[num].trim().equals("定长等长每记录一行")) {
try {
File file = new File(variabl1463);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < variabl1853; j1++)
for (int j2 = 0; j2 < variabl2113; j2++) {
fs.write(variabl2197[j1][j2].getBytes());
}
fs.close();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
} else if (options[num].trim().equals("逗号分隔")) {
try {
File file = new File(variabl1463);
if (!file.exists()) {
file.createNewFile();
}
int length = 0;
char x1 = 10, x2 = 13, x3 = '"', x4 = ',';
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < variabl1853; j1++) {
for (int j2 = 0; j2 < variabl2113; j2++)
if (j2 < (variabl2113 - 1))
fs.write((x3 + variabl2197[j1][j2] + x3 + x4)
.getBytes());
else
fs.write((x3 + variabl2197[j1][j2] + x3)
.getBytes());
fs.write(("" + x1).getBytes());
}
fs.close();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
} else {
try {
File file = new File(variabl1463);
if (!file.exists()) {
file.createNewFile();
}
int length = 0;
char x1 = 10, x2 = 13, x3 = '"', x4 = ',';
String x5 = JOptionPane.showInputDialog("请输入字段与字段间分隔符",
"");
FileOutputStream fs = new FileOutputStream(file);
for (int j1 = 0; j1 < variabl1853; j1++) {
for (int j2 = 0; j2 < variabl2113; j2++)
if (j2 < (variabl2113 - 1))
fs.write((variabl2197[j1][j2] + x5)
.getBytes());
else
fs.write(variabl2197[j1][j2].getBytes());
fs.write(("" + x1).getBytes());
}
fs.close();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e2) {
e2.printStackTrace();
}
}
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",4,") >= 0)) {
variabl26392.setBounds(variabl1423, variabl2503 - 80,
variabl2339 - 5, 20);
frame.add(variabl26392);
variabl1423 = variabl1423 + variabl2339;
}
exitButton = new JButton(variabl2405[5]);
exitButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
System.exit(0);
}
});
if ((variabl1187.length() < 3) || (variabl1187.lastIndexOf(",5,") >= 0)) {
exitButton.setBounds(variabl1423, variabl2503 - 80,
variabl2339 - 5, 20);
frame.add(exitButton);
variabl1423 = variabl1423 + variabl2339;
}
}
static void insert1(int no1) {
s1 = "";
for (int i = 0; i < variabl2651; i++) {
variabl1425[i] = model.getValueAt(no1, i).toString();
if (variabl1425[i] == null)
variabl1425[i] = "";
if ((variabl1169[i].lastIndexOf("int") >= 0)
|| (variabl1169[i].compareTo("decimal") == 0)
|| (variabl1169[i].compareTo("numeric") == 0)) {
if (variabl1425[i].length() == 0)
variabl1425[i] = "0";
s1 = s1 + variabl1425[i];
} else {
s1 = s1 + "'" + variabl1425[i] + "'";
if (i < variabl2651 - 1)
s1 = s1 + ",";
}
s1 = "insert into " + variabl2603 + " values (" + s1 + ")";
try {
pstmt = con.prepareStatement(s1);
pstmt.execute();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
55.源码55,部件程序,从其他数据库中的数据表导入数据到当前系统数据库中,可变换标签内容。数据导入部件。
/**
* 程序文件名:dataTransfer17.java 作者:程学先 功能:从其他数据库中的数据表导入数据到当前系统数据库中,可变换标签内容。
* 完成日期:2012年12月12日 被调用语句:dataTransfer17.TableModel();
* 必须提供的参数:目的数据“表名”、如果修改式导入必须输入“关键字”、源数据“表名”。
* 可选择参数:“字段号表”、“要求按钮号表”、“导入条件式”、另一个数据库的“DBMS名称”、“ODBC数据源”名。 要求字段号表:源数据表中所选字段号
* 本程序允许借用字典表变换标签内容,默认字典表名为表名加“字典表”,程序自动测试源数据表结构,
* 如果字段名有出入,应通过字典表说明,其第1个字段为源表中字段名,第二个字段为对应的目的表字段名。 条件表达式为SQL语句承认的条件式,例如 学号 like
* '%20%' 根据源于目的表所在DBMS的不同,程序将自动分析数据类型的变换,但由于彼此可能存在多对多关系
* 自动分析的对应关系不一定准确,如果不合变换要求,需要操作者输入对应类型关系,程序提供对话框修改,
* 程序设置:建新表并导入、覆盖式导入、添加式导入、修改式导入、退出等5个按钮。 如果修改式导入,必须提供关键字。
*/
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
public class dataTransfer17 extends JFrame {
static dataTransfer17 frame = new dataTransfer17();
private static JTable table;
static String variabl1249;
static Connection con;
static Connection con1;
static String variabl1091;
static String url;
private static Statement stat3;
private static Statement stat4;
private static ResultSetMetaData rsmd4;
private static ResultSet rs;
static String variabl2603;
static String variabl26031;
static String variabl1873 = "";
static String s1 = "", s2 = "", s3 = "";
static String[] variabl1525;
private static String variabl1377;
private static int[] variabl1287;
static int variabl2483 = 900, variabl2503 = 600;
static int variabl2651;
static int variabl2113;
static int variabl21131;
static String variabl2429;
static String[] variabl2517;
static String[] variabl1913;
static String[] variabl19131;
static String[] variabl2411;
static String[] variabl1501;
static String[] variabl1169;
static String[] variabl11691;
static int[] variabl1489;
static int[] variabl1119;
static int[] variabl11191;
static int variabl1853, variabl18531;
private static int[] variabl2345;
static Object[][] variabl21971;
static Object[][] variabl2197;
static String[] variabl2405 = null;
static String variabl1187;
static String variabl1153;
static String variabl1413;
private static String[] variabl1231;
private static String[] variabl1507;
private static String[][] variabl1601 = {
{ "java", "string", "string", "string", "int", "int", "string",
"string", "string", "boolean", "byte", "short", "long",
"float", "int", "double", "float", "float", "byte[]" },
{ "sqlserver", "char", "nchar", "nvarchar", "int", "float",
"datetime", "datetime", "text", "bit", "tinyint",
"smallint", "bigint", "numeric", "integer", "double",
"money", "real", "image" },
{ "vfp", "c", "c", "c", "i", "n", "d", "t", "m", "l", "i", "i",
"i", "n", "i", "n", "b", "y", "g" },
{ "access", "text", "text", "text", "integer", "integer", "date",
"date", "memo", "logical", "integer", "integer", "integer",
"float", "integer", "float", "float", "float", "general" },
{ "oracle", "char", "char", "char", "number", "number", "date",
"date", "long", "char", "raw", "number", "number",
"number", "number", "number", "number", "number", "blob" },
{ "mysql", "char", "char", "varchar", "int", "float", "date",
"datetime", "text", "tinyint", "tinyint", "smallint",
"bigint", "float", "integer", "double", "float", "float",
"blob" } };
static int variabl2851 = 0, variabl2339 = 0;
public static void means(String[] parameter) {
variabl1249 = parameter[1];
variabl1091 = parameter[3];
variabl2603 = parameter[4];
if (parameter[6].trim().length() > 0)
variabl1525 = parameter[6].split(",");
else
variabl1525 = new String[0];
variabl1287 = new int[variabl1525.length];
String[] variabl240501 = { "建新表并导入", "复盖式导入", "添加式导入", "修改式导入", "退出" };
variabl2405 = new String[variabl240501.length];
variabl2851 = 0;
for (int i0 = 0; i0 < variabl240501.length; i0++)
variabl2405[i0] = variabl240501[i0];
if (parameter[11].length() > 0) {
variabl1187 = parameter[11];
variabl1187 = variabl1187.replaceAll(";", ";");
variabl1187 = variabl1187.replaceAll("。", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
variabl1187 = variabl1187.replaceAll(":", ";");
if (variabl1187.indexOf(";") > 0) {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (s601[i01].indexOf(";") > 0) {
String s602[] = s601[i01].split(";");
variabl2405[Integer.parseInt((s602[0]))] = s602[1];
variabl1187 = variabl1187 + "," + s602[0];
variabl2851++;
} else {
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
} else {
String s601[] = variabl1187.split(",");
variabl1187 = "";
for (int i01 = 0; i01 < s601.length; i01++) {
if (i01 == 0)
variabl1187 = s601[i01];
else
variabl1187 = variabl1187 + "," + s601[i01];
variabl2851++;
}
}
} else {
variabl1187 = "";
for (int i1 = 0; i1 < variabl2405.length; i1++) {
if (i1 == 0)
variabl1187 = variabl1187 + i1;
else
variabl1187 = variabl1187 + "," + i1;
variabl2851++;
}
}
variabl1187 = "," + variabl1187 + ",";
variabl2483 = main1.variabl2483;
variabl2503 = main1.variabl2503;
int m1, m2;
try {
if (parameter[17].length() == 0) {
m1 = 0;
m2 = 0;
} else {
m1 = Integer.parseInt(parameter[17]);
m2 = Integer.parseInt(parameter[18]);
}
if (m1 > 0) {
if (m2 < 80) {
m1 = m1 * 10;
m2 = m2 * 10;
}
variabl2483 = m1;
variabl2503 = m2;
}
} catch (Exception e2) {
}
variabl1153 = parameter[8];
variabl1413 = parameter[13];
variabl26031 = parameter[2];
variabl2429 = parameter[0];
variabl1873 = "";
String[] t1 = parameter[0].split(",");
if (variabl2429.lastIndexOf(variabl2603 + "字典表") >= 0)
for (int i = 0; i < t1.length; i++)
if (t1[i].lastIndexOf(variabl2603 + "字典表") >= 0)
variabl1873 = t1[i];
main1.driver1();
con1 = main1.getConn();
try {
stat4 = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
s1 = "select * from " + variabl26031;
if ((variabl1413 != null) && (variabl1413.trim().length() > 0))
s1 = s1 + " where " + variabl1413;
rs = stat4.executeQuery(s1);
rs.last();
variabl18531 = rs.getRow();
rsmd4 = rs.getMetaData();
variabl2651 = rsmd4.getColumnCount();
variabl2517 = new String[variabl2651];
variabl1501 = new String[variabl2651];
variabl1489 = new int[variabl2651];
for (int i = 0; i < variabl2651; i++) {
variabl2517[i] = rsmd4.getColumnName(i + 1);
variabl1501[i] = rsmd4.getColumnTypeName(i + 1);
variabl1489[i] = rsmd4.getColumnDisplaySize(i + 1);
}
for (int j = 0; j < variabl1525.length; j++)
for (int i = 0; i < variabl2651; i++) {
if (variabl1525[j].trim().equals(variabl2517[i].trim())) {
variabl1287[j] = i;
if ((variabl1153.length() > 0)
&& (("," + variabl1153 + ",").lastIndexOf(","
+ i + ",") < 0))
variabl1153 = variabl1153 + "," + i;
break;
}
}
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "连接数据库出错!" + s1);
}
variabl1153 = "," + variabl1153 + ",";
variabl2345 = new int[variabl2651];
int b0 = 0, b = -1;
while (b0 < variabl2651) {
if ((variabl1153.length() < 3)
|| (variabl1153.lastIndexOf("," + b0 + ",") >= 0)) {
b = b + 1;
variabl2345[b] = b0;
}
b0++;
}
variabl21131 = b + 1;
variabl19131 = new String[variabl21131];
variabl11691 = new String[variabl21131];
variabl1169 = new String[variabl21131];
variabl11191 = new int[variabl21131];
variabl1119 = new int[variabl21131];
variabl21971 = new String[variabl18531][variabl21131];
variabl1507 = new String[variabl21131];
for (int j = 0; j < variabl1525.length; j++) {
for (int i = 0; i < variabl21131; i++) {
if (variabl1287[j] == i)
variabl1287[j] = variabl2345[i];
}
}
try {
rs.absolute(1);
int c = 0;
b = 0;
while (c < variabl18531) {
rs.absolute(c + 1);
while (b < variabl21131) {
variabl21971[c][b] = rs.getString(variabl2345[b] + 1);
b++;
}
c++;
b = 0;
}
rs.close();
stat4.close();
con1.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, "读取数据库数据出错!");
}
variabl2411 = new String[variabl21131];
b = 0;
s2 = "";
s3 = "";
int variabl2111 = 0, variabl21111 = 0;
if ((variabl1249.trim().length() > 0)
&& (!variabl1249.equals("sqlserver")))
for (int i = 0; i < variabl1601.length; i++) {
if (variabl1249.trim().equals(variabl1601[i][0]))
variabl21111 = i;
else if (main1.variabl1275.equals(variabl1601[i][0]))
variabl2111 = i;
}
while (b < variabl21131) {
variabl2411[b] = variabl2517[variabl2345[b]];
variabl19131[b] = variabl2411[b];
variabl11191[b] = variabl1489[variabl2345[b]];
variabl11691[b] = variabl1501[variabl2345[b]];
s2 = s2 + variabl11691[b] + ",";
for (int i = 1; i < variabl1601[0].length; i++) {
if (variabl11691[b].toLowerCase().trim()
.equals(variabl1601[variabl21111][i].trim())) {
s3 = s3 + variabl1601[variabl2111][i] + ",";
variabl1169[b] = variabl1601[variabl2111][i];
break;
}
}
b++;
}
s2 = s2.substring(0, s2.length() - 1);
s3 = s3.substring(0, s3.length() - 1);
if ((variabl1249.trim().length() > 0)
&& (!variabl1249.equals("sqlserver"))) {
String s4 = JOptionPane.showInputDialog(null, "原数据类型:" + s2
+ " 准备改为:" + s3 + " 是否正确?如果有错请对应修改。");
if ((s4 != null) && (s4.length() > 0))
variabl1169 = s4.split(",");
else if ((s3 != null) && (s3.length() > 0))
variabl1169 = s3.split(",");
}
if (variabl1873.length() > 0) {
main1.driver1();
con = main1.getConn();
try {
Statement sta = con1.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
String s1 = "select * from " + variabl1873;
ResultSet rs = sta.executeQuery(s1);
while (rs.next()) {
String variabl2153 = rs.getString(1);
String variabl1755 = rs.getString(2);
b = 0;
while (b < variabl21131) {
if (variabl2411[b].compareTo(variabl2153) == 0) {
variabl2411[b] = variabl1755;
break;
}
b++;
}
}
rs.close();
sta.close();
con.close();
} catch (Exception e1) {
e1.printStackTrace();
}
}
frame.setTitle("源数据表数据情况 作者:程学先");
frame.setBounds(10, 10, variabl2483, variabl2503);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
final JScrollPane scrollPane = new JScrollPane();
scrollPane.setBounds(0, 0, variabl2483 - 60, variabl2503 - 100);
frame.setLayout(null);
frame.getContentPane().add(scrollPane, BorderLayout.CENTER);
final TableModel model = new DefaultTableModel(variabl21971,
variabl19131);
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
for (int i = 0; i < variabl21131; i++) {
TableColumn tc = table.getColumn(variabl19131[i]);
JTableHeader header = table.getTableHeader();
tc.setPreferredWidth(variabl11191[i] * 14);
tc.setMaxWidth(variabl11191[i] * 14);
tc.sizeWidthToFit();
}
scrollPane.setViewportView(table);
final JButton leadinginButton1 = new JButton("建新表并导入");
leadinginButton1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "CREATE TABLE " + variabl2603 + " (";
for (int i = 0; i < variabl19131.length; i++) {
s1 = s1 + variabl2411[i] + " " + variabl1169[i];
String variabl25111 = main1.variabl1545;
if (main1.variabl2511.lastIndexOf("," + variabl1169[i]
+ ",") >= 0)
s1 = s1 + "(" + variabl11191[0] + ")";
if (i != variabl19131.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
try {
main1.driver1();
con = main1.getConn();
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stat3.executeUpdate(s1);
String variabl25111 = main1.variabl1545;
for (int i = 0; i < variabl21971.length; i++) {
s1 = "insert into " + variabl2603 + " values (";
for (int j = 0; j < variabl19131.length; j++) {
if (variabl25111.lastIndexOf("," + variabl11691[j]
+ ",") >= 0) {
if (variabl21971[i][j] == null)
variabl21971[i][j] = "0";
s1 = s1 + variabl21971[i][j];
} else {
if (variabl21971[i][j] == null)
variabl21971[i][j] = " ";
s1 = s1 + "'" + variabl21971[i][j] + "'";
}
if (j != variabl19131.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
}
stat3.close();
con.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton1.setBounds(30, variabl2503 - 80, 150, 20);
frame.add(leadinginButton1);
int variabl2339 = (variabl2483 - 250) / variabl2851;
int variabl2337 = 180;
final JButton leadinginButton2 = new JButton(variabl2405[0]);
leadinginButton2.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
s1 = "delete from " + variabl2603;
try {
main1.driver1();
con = main1.getConn();
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stat3.executeUpdate(s1);
String variabl25111 = main1.variabl1545;
for (int i = 0; i < variabl21971.length; i++) {
s1 = "insert into " + variabl2603 + " (";
for (int k1 = 0; k1 < variabl19131.length; k1++)
s1 = s1 + variabl2411[k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < variabl19131.length; j++) {
if (variabl25111.lastIndexOf("," + variabl11691[j]
+ ",") >= 0) {
if (variabl21971[i][j] == null)
variabl21971[i][j] = "0";
s1 = s1 + variabl21971[i][j];
} else {
if (variabl21971[i][j] == null)
variabl21971[i][j] = " ";
s1 = s1 + "'" + variabl21971[i][j] + "'";
}
if (j != variabl19131.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
}
stat3.close();
con.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton2.setBounds(variabl2337, variabl2503 - 80, variabl2339,
20);
if (variabl1187.indexOf(",0,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
frame.add(leadinginButton2);
}
final JButton leadinginButton3 = new JButton(variabl2405[1]);
leadinginButton3.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
main1.driver1();
con = main1.getConn();
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String variabl25111 = main1.variabl1545;
for (int i = 0; i < variabl21971.length; i++) {
s1 = "insert into " + variabl2603 + " (";
for (int k1 = 0; k1 < variabl19131.length; k1++)
s1 = s1 + variabl2411[k1] + ",";
s1 = s1.substring(0, s1.length() - 1) + " ) values (";
for (int j = 0; j < variabl19131.length; j++) {
if (variabl25111.lastIndexOf("," + variabl11691[j]
+ ",") >= 0) {
if (variabl21971[i][j] == null)
variabl21971[i][j] = "0";
s1 = s1 + variabl21971[i][j];
} else {
if (variabl21971[i][j] == null)
variabl21971[i][j] = " ";
s1 = s1 + "'" + variabl21971[i][j] + "'";
}
if (j != variabl19131.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
}
stat3.close();
con.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton3.setBounds(variabl2337, variabl2503 - 80, variabl2339,
20);
if (variabl1187.indexOf(",1,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
frame.add(leadinginButton3);
}
final JButton leadinginButton4 = new JButton(variabl2405[2]);
leadinginButton4.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
main1.driver1();
con = main1.getConn();
s1 = "";
try {
Statement sta = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
for (int k1 = 0; k1 < variabl19131.length; k1++)
s1 = s1 + variabl2411[k1] + ",";
variabl2113 = variabl2411.length;
s1 = s1.substring(0, s1.length() - 1);
s1 = "select " + s1 + " from " + variabl2603;
ResultSet rs = sta.executeQuery(s1);
rs.last();
variabl1853 = rs.getRow();
variabl2197 = new String[variabl1853][variabl2113];
for (int i = 0; i < variabl1853; i++) {
rs.absolute(i + 1);
for (int j = 0; j < variabl2113; j++) {
variabl2197[i][j] = rs.getString(j + 1);
}
}
rs.close();
con.close();
sta.close();
} catch (Exception e1) {
JOptionPane.showMessageDialog(null, "读取数据错!" + s1);
}
try {
main1.driver1();
con = main1.getConn();
stat3 = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String variabl25111 = main1.variabl1545;
int variabl2149 = 0;
String variabl1975 = ",";
for (int k2 = 0; k2 < variabl2197.length; k2++)
for (int k3 = 0; k3 < variabl1525.length; k3++)
variabl1975 = variabl1975
+ variabl2197[k2][variabl1287[k3]] + ",";
for (int i = 0; i < variabl21971.length; i++) {
variabl1377 = "";
variabl2149 = 0;
for (int k1 = 0; k1 < variabl1525.length; k1++) {
variabl1377 = variabl1377
+ variabl21971[i][variabl1287[k1]];
if (variabl1975
.lastIndexOf("," + variabl1377 + ",") >= 0) {
variabl2149 = 1;
break;
}
}
if (variabl2149 == 0) {
s1 = "insert into " + variabl2603 + " (";
for (int k1 = 0; k1 < variabl19131.length; k1++)
s1 = s1 + variabl2411[k1] + ",";
s1 = s1.substring(0, s1.length() - 1)
+ " ) values (";
for (int j = 0; j < variabl19131.length; j++) {
if (variabl25111.lastIndexOf(","
+ variabl11691[j] + ",") >= 0) {
if (variabl21971[i][j] == null)
variabl21971[i][j] = "0";
s1 = s1 + variabl21971[i][j];
} else {
if (variabl21971[i][j] == null)
variabl21971[i][j] = " ";
s1 = s1 + "'" + variabl21971[i][j] + "'";
}
if (j != variabl19131.length - 1)
s1 = s1 + ",";
}
s1 = s1 + ")";
stat3.executeUpdate(s1);
variabl1975 = variabl1975 + variabl1377 + ",";
} else {
s1 = "update " + variabl2603 + " set ";
for (int j = 0; j < variabl19131.length; j++) {
if (variabl25111.lastIndexOf(","
+ variabl11691[j] + ",") >= 0) {
if (variabl21971[i][j] == null)
variabl21971[i][j] = "0";
s1 = s1 + variabl2411[j] + " = "
+ variabl21971[i][j];
} else {
if (variabl21971[i][j] == null)
variabl21971[i][j] = " ";
s1 = s1 + variabl2411[j] + " = " + "'"
+ variabl21971[i][j] + "'";
}
if (j != variabl19131.length - 1)
s1 = s1 + ",";
}
s1 = s1 + " where ";
for (int k2 = 0; k2 < variabl1525.length; k2++) {
s1 = s1 + variabl1525[k2] + " = ";
if (main1.variabl25111.lastIndexOf(","
+ variabl11691[variabl1287[k2]] + ",") >= 0) {
s1 = s1 + variabl21971[i][variabl1287[k2]];
} else {
s1 = s1 + "'"
+ variabl21971[i][variabl1287[k2]]
+ "'";
}
}
stat3.executeUpdate(s1);
}
}
stat3.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null,
"操作出错,请检查数据表名是否重复,录入语句是否正确!" + s1);
}
}
});
leadinginButton4.setBounds(variabl2337, variabl2503 - 80, variabl2339,
20);
if (variabl1187.indexOf(",2,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
frame.add(leadinginButton4);
}
final JButton exitButton1 = new JButton(variabl2405[3]);
exitButton1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
frame.dispose();
}
});
exitButton1.setBounds(variabl2337, variabl2503 - 80, variabl2339, 20);
if (variabl1187.indexOf(",3,") >= 0) {
variabl2337 = variabl2337 + variabl2339;
frame.add(exitButton1);
frame.setVisible(true);
}
}
}
Archiver|手机版|科学网 ( 京ICP备07017567号-12 )
GMT+8, 2024-10-19 22:27
Powered by ScienceNet.cn
Copyright © 2007- 中国科学报社