package PbuliClass;
|
import java.awt.Color;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileNotFoundException;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.sql.ResultSet;
|
import javax.swing.JFileChooser;
|
import javax.swing.JOptionPane;
|
import javax.swing.JPanel;
|
import javax.swing.filechooser.FileNameExtensionFilter;
|
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.poifs.filesystem.POIFSFileSystem;
|
import DataBase.DatabaseManagement;
|
import Frame.AnchorManage;
|
import Judge.JugeNumber;
|
import Method.GetNowTime;
|
import anchor.Anchor_Dell;
|
import tag.Tag_Dell;
|
import tag.Tag_Into_Database;
|
|
/**±í¸ñµ¼Èëµ¼³öÀà*/
|
|
public class ImExport {
|
HSSFWorkbook wb = null;//´´½¨EXCEL¶ÔÏó
|
InputStream ins=null; //ÊäÈëÁ÷
|
POIFSFileSystem pfs=null;
|
HSSFSheet sheet=null ;//ÉêÃ÷±íµ¥
|
int num=0;//±íµ¥ÖеÄÐÐÊý
|
HSSFRow hssfRow=null;//ÐжÔÏó
|
File file=null;
|
File saveFile=null;
|
String outName;//µ¼³öÎļþµÄÃû³Æ
|
JFileChooser chooser=null;// ´´½¨ÎļþÑ¡ÔñÆ÷
|
FileNameExtensionFilter filter;//Îļþ¹ýÂËÆ÷
|
int option ;
|
dataToExcel toexcel;//Êý¾ÝתΪexcelÊä³ö
|
|
|
public ImExport() {//¹¹Ôì·½·¨
|
chooser = new JFileChooser("./savefile/");
|
filter=new FileNameExtensionFilter("Excel", "xls");
|
//»ñÈ¡jar°üλÖã¬ÉèÖÃJFileChooserµ±Ç°Â·¾¶
|
|
}
|
|
/**µ¼³ö±í¸ñ·½·¨*/
|
public void export(JPanel jpan,String sql) {
|
chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷
|
option = chooser.showSaveDialog(jpan);// Èç¹ûÈ·¶¨Ôò·µ»Ø0£¬È¡Ïû»ò¹Ø±Õ·µ»Ø1
|
if (option == JFileChooser.APPROVE_OPTION){
|
//Êý¾Ý±íµÄÐÐÊý
|
int size=OutButton.getSize();
|
if(size >65535) {
|
|
double j=size/65535;
|
int maxPageNumber=(int) Math.ceil(j)+1;
|
for(int i=1;i<maxPageNumber+1;i++) {
|
int k=65535;
|
String a=String.valueOf(i*k-k);
|
String newsql=sql+" limit "+a+","+k;
|
outName=getFile().getAbsolutePath()+i+".xls";//µ¼³öµÄÎļþÃû×ÖĬÈÏΪexcelÎļþ
|
toexcel=new dataToExcel();
|
try {
|
toexcel.toExcel(newsql, outName, "±êÇ©Áбí");//µ÷Óõ¼³öÎļþ·½·¨
|
} catch (Exception e2) {
|
e2.printStackTrace();
|
}
|
|
double k1=100/maxPageNumber;
|
OutButton .getProgressBar().setValue((int)Math.ceil(k1)*i);
|
}
|
OutButton .getProgressBar().setValue(100);
|
OutButton .getProgressBar().setBackground(Color.green);
|
ShowMessage.zidingyi_24("×ܹ²³É¹¦µ¼³ö"+maxPageNumber+"¸ö±í¸ñ£¡");
|
|
} else {
|
outName=getFile().getAbsolutePath()+".xls";//µ¼³öµÄÎļþÃû×ÖĬÈÏΪexcelÎļþ
|
toexcel=new dataToExcel();
|
try {
|
toexcel.toExcel(sql, outName, "±êÇ©Áбí");//µ÷Óõ¼³öÎļþ·½·¨
|
} catch (Exception e2) {
|
e2.printStackTrace();
|
}
|
OutButton .getProgressBar().setValue(100);
|
OutButton .getProgressBar().setBackground(Color.green);
|
ShowMessage.zidingyi(outName+"µ¼³ö³É¹¦£¡");
|
}
|
|
}
|
|
}
|
|
/**»ñÈ¡±£´æÎļþµÄ·½·¨*/
|
public File getFile() {
|
if(file==null) {
|
file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ
|
}
|
return file;
|
}
|
|
|
/**»ñÈ¡±íµ¥¶ÔÏóµÄ·½·¨*/
|
public HSSFSheet getSheet(File file) {// Sheet(ÊõÓ¹¤×÷±í)¾ÍÊÇExcel±í¸ñ×óϽǵÄSheet1,Sheet2,Sheet3µ«ÔÚ³ÌÐòÖÐSheetµÄϱêÊÇ´Ó0¿ªÊ¼
|
|
if(wb == null) {
|
try {
|
ins = new FileInputStream(file);
|
try {
|
pfs=new POIFSFileSystem(ins);//Îļþϵͳ¿É½ÓÊÜÒ»¸öÊäÈëÁ÷
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
} catch (FileNotFoundException e) {
|
e.printStackTrace();
|
}
|
try {
|
wb = new HSSFWorkbook(pfs);
|
sheet=wb.getSheetAt(0);//»ñÈ¡µÚÒ»ÕÅ±íµ¥
|
// num= sheet.getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄÐÐÊý
|
// hssfRow=sheet.getRow(num);//»ñȡÿÐжÔÏó
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
return sheet;
|
|
}
|
|
|
|
/**¹Ø±ÕÊý¾ÝÁ÷µÄ·½·¨*/
|
public void getClose() {
|
try {
|
wb.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
try {
|
pfs.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
try {
|
ins.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
/**±êÇ©±í¸ñµ¼ÈëÊý¾Ý¿âµÄ·½·¨*/
|
public void excelTagToMysql(JPanel jpan) {
|
|
|
String power="100";
|
String zaixian="ÔÚÏß";
|
chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷
|
option=chooser.showOpenDialog(jpan);//ÏÔʾѡÔñÎļþ¶Ô»°¿ò
|
if(option==JFileChooser.APPROVE_OPTION) {//Èç¹ûÓû§Ñ¡ÔñµÄÊÇ¡°´ò¿ª¡±°´Å¥
|
file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ
|
num= getSheet(file).getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄ×ÜÐÐÊý
|
//±éÀúrow
|
for(int rowNum=1;rowNum <= num;rowNum++) {
|
String tag_id=String.valueOf(getSheet(file).getRow(rowNum).getCell(1)).substring(0,4);//±êÇ©ID
|
String tag_state=String.valueOf(getSheet(file).getRow(rowNum).getCell(2));//±êÇ©°ó¶¨¶ÔÏó
|
String tag_posz=String.valueOf(getSheet(file).getRow(rowNum).getCell(3));//±êÇ©¸ß¶È
|
String tag_pinglv=String.valueOf(getSheet(file).getRow(rowNum).getCell(4));//±êǩƵÂÊ
|
String tag_sudu=String.valueOf(getSheet(file).getRow(rowNum).getCell(5));//±êÇ©ËÙ¶È
|
|
|
//Èç¹û±êǩ״̬Ϊ¿ÕÔò¸³ÖµÎª·ñ
|
if(tag_state=="null") {
|
tag_state="δ°ó¶¨";
|
}
|
|
if(tag_posz=="null") {
|
tag_posz="150";
|
}
|
|
|
if(tag_pinglv=="null") {
|
tag_pinglv="1";
|
}
|
|
if(tag_sudu=="null") {
|
tag_sudu="1000";
|
}
|
|
|
// Ö´ÐÐSQL²éѯÓï¾ä»ñµÃµÄ½á¹û¼¯Èç¹û´æÔÚÖظ´±êǩɾ³ýÔÀ´±êÇ©
|
ResultSet haveTag = DatabaseManagement.query("select * from tb_tag where tag_id='" + tag_id.trim()+ "'");
|
try {
|
if (haveTag.next()) {// ½á¹û¼¯ÖбêÇ©IDÓг¬¹ýÒ»ÌõµÄ¼Ç¼
|
String deleteSql="DELETE FROM tb_tag WHERE tag_id='"+tag_id.trim()+"'";
|
DatabaseManagement.update(deleteSql);// ɾ³ý±êÇ©ÐÅÏ¢
|
}
|
}
|
catch (Exception er) {
|
er.printStackTrace();
|
}
|
|
|
|
Tag_Into_Database.insert_all_Tag_info_dabase(tag_id, tag_state, power, zaixian,tag_posz,tag_sudu, tag_pinglv);
|
|
}
|
getClose();
|
JOptionPane.showMessageDialog(jpan, "±í¸ñµ¼Èë³É¹¦£¡", "ÌáʾÐÅÏ¢", JOptionPane.INFORMATION_MESSAGE);// µ¯³öÌáʾ¿ò
|
}
|
|
|
|
}
|
|
/**ÈËÔ±ÐÅÏ¢±í¸ñµ¼ÈëÊý¾Ý¿âµÄ·½·¨*/
|
public void excelPersonToMysql(JPanel jpan) {
|
|
chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷
|
option=chooser.showOpenDialog(jpan);//ÏÔʾѡÔñÎļþ¶Ô»°¿ò
|
if(option==JFileChooser.APPROVE_OPTION) {//Èç¹ûÓû§Ñ¡ÔñµÄÊÇ¡°´ò¿ª¡±°´Å¥
|
file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ
|
num= getSheet(file).getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄ×ÜÐÐÊý
|
//±éÀúrow
|
for(int rowNum=1;rowNum <=num;rowNum++) {
|
String tagids=String.valueOf(getSheet(file).getRow(rowNum).getCell(1)).trim();//±êÇ©ID
|
String name=String.valueOf(getSheet(file).getRow(rowNum).getCell(2)).trim();//ÐÕÃû
|
String bumen=String.valueOf(getSheet(file).getRow(rowNum).getCell(3)).trim();//²¿ÃÅ
|
String sex=String.valueOf(getSheet(file).getRow(rowNum).getCell(4)).trim();//ÐÔ±ð
|
String phone=String.valueOf(getSheet(file).getRow(rowNum).getCell(5)).trim();//µç»°
|
|
|
if (tagids.length()<4){
|
ShowMessage.zidingyi("Ìí¼Óʧ°Ü,"+tagids+"±êÇ©Id³¤¶ÈСÓÚ4");
|
}
|
String tagid=tagids.substring(0,4);
|
|
if(name.length()<2 || name.equals("null")) {
|
name="δ°ó¶¨";
|
}
|
|
if(bumen.length()<3 || bumen.equals("null")) {
|
bumen="ϵͳĬÈÏ";
|
}
|
|
if(sex.equals("")|| sex.equals("null")) {
|
sex="ÄÐ";
|
}
|
|
if(phone.length()<8 || phone.equals("null")) {
|
phone="***";
|
}
|
|
if(tagid.length()>3) {
|
if(Tag_Dell.addtag_person_excel_into(tagid, name, bumen, sex, phone)) {
|
// µ¯³öÌáʾ¿ò
|
ShowMessage.zidingyi(tagid+"µ¼ÈëÊý¾Ý¿â³É¹¦..."+GetNowTime.now());
|
}
|
}
|
|
}
|
getClose();
|
|
}
|
|
}
|
|
/**»ùÕ¾ÐÅÏ¢±í¸ñµ¼ÈëÊý¾Ý¿âµÄ·½·¨*/
|
public void excelAnchorToMysql(JPanel jpan) {
|
|
chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷
|
option=chooser.showOpenDialog(jpan);//ÏÔʾѡÔñÎļþ¶Ô»°¿ò
|
if(option==JFileChooser.APPROVE_OPTION) {//Èç¹ûÓû§Ñ¡ÔñµÄÊÇ¡°´ò¿ª¡±°´Å¥
|
file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ
|
num= getSheet(file).getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄ×ÜÐÐÊý
|
//±éÀúrow
|
for(int rowNum=1;rowNum <=num;rowNum++) {
|
String anchorid=String.valueOf(getSheet(file).getRow(rowNum).getCell(1));//»ùÕ¾id
|
String POSX=String.valueOf(getSheet(file).getRow(rowNum).getCell(2));//»ùÕ¾X×ø±ê
|
String POSY=String.valueOf(getSheet(file).getRow(rowNum).getCell(3));//»ùÕ¾Y×ø±ê
|
String POSZ=String.valueOf(getSheet(file).getRow(rowNum).getCell(4));//»ùÕ¾Z×ø±ê
|
String layer=String.valueOf(getSheet(file).getRow(rowNum).getCell(5));//»ùÕ¾ËùÔÚ²ã
|
String anchormode=String.valueOf(getSheet(file).getRow(rowNum).getCell(6));//»ùվ״̬
|
String anchorip=String.valueOf(getSheet(file).getRow(rowNum).getCell(7));//»ùÕ¾ipµØÖ·
|
|
if(!JugeNumber.isDigit(anchorid)) {
|
ShowMessage.zidingyi_24(anchorid+"»ùÕ¾ID±ØÐë´¿Êý×ÖÇëÐ޸ģ¡");
|
return;
|
}
|
|
if(layer.equals("null")) {
|
layer="0";
|
}
|
|
if(POSX.equals("null")) {
|
POSX="0";
|
}
|
|
if(POSY.equals("null")) {
|
POSY="0";
|
}
|
|
if(POSY.equals("null")) {
|
POSZ="0";
|
}
|
|
if(anchorip.equals("null")) {
|
anchorip="";
|
}
|
|
if(Anchor_Dell.get_anchor(anchorid) ==null) {
|
|
String[] ziduan= {"anchorid","POSX","POSY","POSZ","layer","zu",
|
"liangcheng","anchormode","anchorip","baoliu1","baoliu5","GreateTime"};
|
String[] zhi= {anchorid,POSX,POSY,POSZ,layer,"0",
|
"100000",anchormode,anchorip,"","0",GetNowTime.now()};
|
|
if(DatabaseManagement.insertfast("tb_achor", ziduan, zhi)) {
|
AnchorManage.add_new_anchor_do(anchorid, POSX, POSY, POSZ, layer, "0", "100000", anchorip,"0", anchormode,"0");
|
ShowMessage.zidingyi(anchorid+"µ¼ÈëÊý¾Ý¿â³É¹¦..."+GetNowTime.now());
|
}
|
}
|
|
}
|
ShowMessage.zidingyi_24("Êý¾Ýµ¼ÈëÍê±ÏÇëÖØÆôÈí¼þ");
|
getClose();
|
JOptionPane.showMessageDialog(jpan, "±í¸ñµ¼Èë³É¹¦£¡", "ÌáʾÐÅÏ¢", JOptionPane.INFORMATION_MESSAGE);// µ¯³öÌáʾ¿ò
|
}
|
|
}
|
|
|
}
|
