package sample;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
public class ExportExcel {
private String title;
private String[] rowname;
private List<Person> dataList =new ArrayList<>();
public ExportExcel(String title,String[] rowname,List<Person> dataList){
this.dataList =dataList;
this.rowname = rowname;
this.title= title;
}
public void export(OutputStream out,List<Person> dataList)throws Exception{
//创建一个excel表格
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(title);
//产生表格标题
HSSFRow rowm =sheet.createRow(0);
HSSFCell celltitle =rowm.createCell(0);
//sheet样式定义
HSSFCellStyle columnTopStyle =this.getColumnTopStyle(workbook);
HSSFCellStyle style =this.getStyle(workbook);
sheet.addMergedRegion(new CellRangeAddress(0,1,0,(rowname.length-1)));
celltitle.setCellValue(title);
//定义所需函数
int columnNum = rowname.length;
HSSFRow rowRowName = sheet.createRow(2);
//将列头设置到sheet单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n);
HSSFRichTextString text =new HSSFRichTextString(rowname[n]);
cellRowName.setCellValue(text);
cellRowName.setCellStyle(columnTopStyle);
}
//将查询到的数据设置到sheet当中
for (int i=0;i<dataList.size();i++){
Person person1 =dataList.get(i) ;
HSSFRow row =sheet.createRow(i+3);
HSSFCell cell =row.createCell(0);
cell.setCellValue(person1.getName());
cell.setCellStyle(style);
cell =row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue(person1.getTel1());
cell =row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue(person1.getTel2());
cell =row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue(person1.getEmail());
}
//让列宽随着导出的列长自适应
for(int colNum = 0; colNum< columnNum;colNum++){
int columnWidth = sheet.getColumnWidth(colNum)/256;
for(int rowNum = 0; rowNum<sheet.getLastRowNum();rowNum++){
HSSFRow curremtRow;
if(sheet.getRow(rowNum)==null){
curremtRow =sheet.createRow(rowNum);
}else{
curremtRow =sheet.getRow(rowNum);
}
if(curremtRow.getCell(colNum)!=null){
HSSFCell currentCell = curremtRow.getCell(colNum);
int length =currentCell.getStringCellValue().getBytes().length;
if(columnWidth < length){
columnWidth = length;
}
}
}
if (colNum ==0){
sheet.setColumnWidth(colNum,(columnWidth - 2)*256);
} else {
sheet.setColumnWidth(colNum,(columnWidth + 4)*256);
}
}
if(workbook !=null){
try {
workbook.write(out);
}catch(Exception e){
e.printStackTrace();
}
}
out.flush();
}
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook){
//设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 11) ;
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("Courier New");
//设置样式
HSSFCellStyle style =workbook.createCellStyle();
//设置低边框
style.setBorderBottom(BorderStyle.THIN);
//设置低边框颜色
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//设置右边框
style.setBorderRight(BorderStyle.THIN);
//设置顶边框
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//设置顶边框颜色
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//在样式中应用设置字体
style.setFont(font);
//设置自动换行
style.setWrapText(false);
//设置水平对齐的样式为居中对齐
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
//设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 10) ;
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("Courier New");
//设置样式
HSSFCellStyle style =workbook.createCellStyle();
//设置低边框
style.setBorderBottom(BorderStyle.THIN);
//设置低边框颜色
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//设置右边框
style.setBorderRight(BorderStyle.THIN);
//设置顶边框
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//设置顶边框颜色
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//在样式中应用设置字体
style.setFont(font);
//设置自动换行
style.setWrapText(false);
//设置水平对齐的样式为居中对齐
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
}
主类中
package sample;
import javafx.application.Application;
import javafx.beans.Observable;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.Group;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.layout.FlowPane;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.scene.text.Font;
import javafx.stage.Stage;
import org.apache.commons.math3.analysis.function.Exp;
import javax.tools.Tool;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Table01 /*extends Application*/ {
//上半部分
VBox vAll = new VBox();
VBox vb = new VBox();
HBox h1 = new HBox();
HBox h2 = new HBox();
HBox h3 = new HBox();
TextField tfName = new TextField();
TextField tfTel1 = new TextField();
TextField tfTel2 = new TextField();
TextField tfEmail = new TextField();
Button btAdd = new Button("新增");
Button btQuery = new Button("查询");
Button btChange = new Button("更改");
Button btDel = new Button("删除");
Button btnExport = new Button("导出");
TableView table = new TableView();
//下半部分
TableColumn idCol = new TableColumn("序号");
TableColumn nameCol = new TableColumn("姓名");
TableColumn tel1Col = new TableColumn("手机");
TableColumn tel2Col = new TableColumn("办公电话");
TableColumn emailCol = new TableColumn("邮箱");
//文本框中的信息
String sName = tfName.getText();
String sTel1 = tfTel1.getText();
String sTel2 = tfTel2.getText();
String sEmail = tfEmail.getText();
//数据
final ObservableList<Person> data = FXCollections.observableArrayList();
// @Override
public Table01()/*void start(Stage stage)*/ {
Stage stage = new Stage();
stage.setTitle("Table View Sample");
stage.setWidth(450);
stage.setHeight(600);
//上半部分
h1.getChildren().addAll(new Label("姓名:"),tfName,new Label("手机:"),tfTel1);
h2.getChildren().addAll(new Label("办公电话:"),tfTel2,new Label("邮箱:"),tfEmail);
h3.getChildren().addAll(btAdd,btChange,btQuery,btDel,btnExport);
h3.setSpacing(40);
h3.setAlignment(Pos.TOP_CENTER);
vb.getChildren().addAll(h1,h2,h3);
vb.setSpacing(10);
table.setEditable(true);
//设置自动拉满 table.setColumnResizePolicy(TableView.CONSTRAINED_RESIZE_POLICY);
//下半部分
table.getColumns().addAll(idCol,nameCol,tel1Col,tel2Col,emailCol);
//列与person类绑定
idCol.setCellValueFactory(new PropertyValueFactory<Person,String>("id"));
nameCol.setCellValueFactory(new PropertyValueFactory<Person,String>("name"));
tel1Col.setCellValueFactory(new PropertyValueFactory<Person,String>("tel1"));
tel2Col.setCellValueFactory(new PropertyValueFactory<Person,String>("tel2"));
emailCol.setCellValueFactory(new PropertyValueFactory<Person,String>("email"));
//合并
VBox vbox = new VBox();
vbox.setSpacing(5);
vbox.setPadding(new Insets(10, 0, 0, 10));
vbox.getChildren().addAll(vb,table);
Scene scene = new Scene(new Group());
((Group) scene.getRoot()).getChildren().addAll(vbox);
stage.setScene(scene);
stage.show();
btAdd.setOnAction(e->{
add();
});
btDel.setOnAction(e->{
del();
});
btQuery.setOnAction(e->{
query();
});
btChange.setOnAction(e->{
change();
});
btnExport.setOnAction(e->{
Export();
});
}
// public static void main(String[] args) {
// launch(args);
// }
public boolean IsIntact(){ //判断是否
sName = tfName.getText();
sTel1 = tfTel1.getText();
sTel2 = tfTel2.getText();
sEmail = tfEmail.getText();
if(sName.equals("")||sTel1.equals("")||sTel2.equals("")||sEmail.equals("")){
System.out.println("请输入正确的查询信息");
return true;
}
return false;
}
public void clean(){
tfName.clear();
tfTel1.clear();
tfTel2.clear();
tfEmail.clear();
}
public Boolean isSpecification(){
sTel1 = tfTel1.getText();
for(char c:sTel1.toCharArray()){
if(!Character.isDigit(c)||sTel1.length()!=11){
Stage stage = new Stage();
stage.setScene((new Scene(new Label(" 电话号错误!"),200,200)));
stage.show();
return false;
}
}
sTel2 = tfTel2.getText();
for(char c:sTel2.toCharArray()){
if(!Character.isDigit(c)){
Stage stage = new Stage();
stage.setScene((new Scene(new Label(" 座机号错误!"),200,200)));
stage.show();
return false;
}
}
sEmail = tfEmail.getText();
int cotain = 0;
for(char c:sEmail.toCharArray()){
if(c=='@')
cotain++;
if(!(Character.isDigit(c)||Character.isAlphabetic(c)||c=='@'||c=='.')){
Stage stage = new Stage();
System.out.println(c);
stage.setScene((new Scene(new Label(" 1.邮箱错误!"),200,200)));
stage.show();
return false;
}
}
if(cotain != 1){
Stage stage = new Stage();
stage.setScene((new Scene(new Label(" 2.邮箱错误!"),200,200)));
stage.show();
return false;
}
return true;
}
public void refresh(DataBaseUtil db){
try {
ResultSet rs = db.queryExecute("SELECT * FROM Person");
data.clear();
Tools.count = 1;
while(rs.next()) {
Person pp = null;
pp = new Person(rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
data.add(pp);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
table.setItems(data);
}
public void refresh(DataBaseUtil db,String sql){
try {
ResultSet rs = db.queryExecute(sql);
data.clear();
Tools.count = 1;
while(rs.next()) {
Person pp = null;
pp = new Person(rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
data.add(pp);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
table.setItems(data);
}
public void add() {
if(IsIntact())
return;
if(isSpecification()==false)
return;
if(Tools.list.contains(sName)) {
System.out.println("有重名者,看看是否添加重复了!");
}
Tools.list.add(sName);
Person p = new Person(sName,sTel1,sTel2,sEmail);
String sql = "insert into Person (id,name,tel1,tel2,email) values (?,?,?,?,?)";
DataBaseUtil db = new DataBaseUtil();
boolean b = db.updateExecute(sql,new String[]{p.id,p.name,p.tel1,p.tel2,p.email});
refresh(db);
clean();
}
public void change(){
if(IsIntact())
return;
sName = tfName.getText();
String sql = "update Person set id=?,name=?,tel1=?,tel2=?,email=? where name = '"+sName+"' ";
DataBaseUtil db = new DataBaseUtil();
db.updateExecute(sql,new String[]{tfName.getText(),tfName.getText(),tfTel1.getText(),tfTel2.getText(),tfEmail.getText()});
refresh(db);
clean();
}
public void query(){
String sql = "select * from Person where name = '"+tfName.getText()+"' ";
DataBaseUtil db = new DataBaseUtil();
db.queryExecute(sql);
Tools.count = 1;
refresh(db,sql);
clean();
}
public void del(){
sName = tfName.getText();
String sql = "delete from Person where name = ?";
data.clear();
DataBaseUtil db = new DataBaseUtil();
db.updateExecute(sql,new String[] {sName});
Tools.count = 1;
refresh(db);
table.setItems(data);
clean();
}
public void Export(){
String title = "通讯录";
String[] rowName = new String[]{"姓名","电话号码","家庭电话","邮箱"};
List<Person> dataList = this.getPerson();
ExportExcel exportExcel = new ExportExcel(title,rowName,dataList);
try {
OutputStream outputStream = new FileOutputStream("D:\\test.xls");
System.out.println(dataList);
exportExcel.export(outputStream,dataList);
// outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public ArrayList<Person> getPerson(){
ArrayList<Person> arrayList = new ArrayList<>();
for(Person t:data){
arrayList.add(t);
}
return arrayList;
}
}