*readSheet1.java
package kaohe1;
import java.io.File;
import java.util.HashMap;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class readSheet1 {
public static HashMap<Integer, student> readSheet1(){
HashMap<Integer, student> stuMap = new HashMap<>();
int i;
Sheet sheet;
Workbook book;
Cell cell1,cell2,cell3,cell4,cell5,cell6,cell7;
try {
//2233.xls为要读取的excel文件名
book= Workbook.getWorkbook(new File("F:\\2233\\2233.xls"));
//获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
sheet=book.getSheet(0);
//获取左上角的单元格
cell1=sheet.getCell(0,0);
System.out.println("标题:"+cell1.getContents());
i=1;
while(true)
{
//获取每一行的单元格
cell1=sheet.getCell(0,i);//(列,行)
cell2=sheet.getCell(1,i);
cell3=sheet.getCell(2,i);
student s = new student();
s.setName(cell1.getContents());
s.setSex(cell2.getContents());
s.setNo(cell3.getContents());
stuMap.put(i, s);
if("".equals(cell1.getContents())==true) //如果读取的数据为空
break;
System.out.println(cell1.getContents()+"\t"+cell2.getContents()+"\t"+cell3.getContents()+"\t");
i++;
}
// return stuMap;
book.close();
}
catch(Exception e) { }
return stuMap;
}
public static HashMap<Integer, grade> readSheet2(){
HashMap<Integer, grade> graMap = new HashMap<>();
int i;
Sheet sheet;
Workbook book;
Cell cell1,cell2,cell3,cell4;
try {
//2233.xls为要读取的excel文件名
book= Workbook.getWorkbook(new File("F:\\2233\\2233.xls"));
//获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
sheet=book.getSheet(1);
//获取左上角的单元格
cell1=sheet.getCell(0,0);
System.out.println("标题:"+cell1.getContents());
i=1;
while(true)
{
//获取每一行的单元格
cell1=sheet.getCell(0,i);//(列,行)
cell2=sheet.getCell(1,i);
cell3=sheet.getCell(2,i);
cell4=sheet.getCell(3,i);
grade g = new grade();
g.setNo(cell1.getContents());
g.setSubject(cell2.getContents());;
g.setGrade(cell3.getContents());
g.setDate(cell4.getContents());
graMap.put(i, g);
if("".equals(cell1.getContents())==true) //如果读取的数据为空
break;
System.out.println(cell1.getContents()+"\t"+cell2.getContents()+"\t"+cell3.getContents()+"\t"+cell4.getContents()+"\t");
i++;
}
// return stuMap;
book.close();
}
catch(Exception e) { }
return graMap;
}
}
*student.java
package kaohe1;
import jxl.Cell;
public class student {
String no;
String name;
String sex;
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
}
}
*txtWrite.java
package kaohe1;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
public class txtWrite {
public static void txtWrite(HashMap<Integer, all> allMap) throws IOException{
for (int i = 0; i < allMap.size(); i++) {
all all = new all();
all = (all) allMap.get(i);
System.out.println(all);
String name = all.getName();
String sex = all.getSex();
String c = all.getC();
String java = all.getJava();
String sjjg = all.getSjjg();
String sum = all.getSum();
String date = all.getDate();
System.out.println(date);
String data = "------------------"+"\n"+"姓名:"+name+"\n"+"性别:"+sex+"\n"+"分数\n"+"日期:"+date+"\nc语言:"+c+" | java:"+java+" | 数据结构:"+sjjg+"\n"+"总分:"+sum+"\n";
try {
FileOutputStream fos = new FileOutputStream("F:\\2233\\2233.txt",true);
byte[] tempByte = data.getBytes();
fos.write(tempByte);
//4.关水龙头
fos.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
}
}
*writeSheet1.java
package kaohe1;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class writeSheet1 {
public static boolean writeSheet1(HashMap stumap){
boolean flag = false;
System.out.println(stumap.get(1));
// student s = new student();
// s = (student) stumap.get(1);
for (int i = 1; i <= stumap.size(); i++) {
student s = new student();
s = (student) stumap.get(i);
String no = s.getNo();
String name =s.getName();
String sex = s.getSex();
System.out.println(s.getName());
JDBC insertStu = new JDBC();
insertStu.insertStu(no, name, sex);
}
// System.out.println(stumap.size());
// jdbc.insertStu(no, name, sex)
return flag;
}
public static boolean writeSheet2(HashMap graMap){
boolean flag = false;
System.out.println(graMap.get(1));
// student s = new student();
// s = (student) stumap.get(1);
for (int i = 1; i <= graMap.size(); i++) {
grade g = new grade();
g = (grade) graMap.get(i);
String no = g.getNo();
String subjects = g.getSubject();
String grade = g.getGrade();
String date = g.getDate();
System.out.println(date);
JDBC insertGra = new JDBC();
insertGra.insertGra(no, subjects, grade,date);
}
// System.out.println(stumap.size());
// jdbc.insertStu(no, name, sex)
return flag;
}
public static HashMap<Integer, all> select(){
HashMap<Integer, all> allMap = new HashMap<>();
String outputstr = null;
JDBC jdbc = new JDBC();
String selectSql="SELECT s.name,s.sex,MAX(CASE WHEN g.subjects = 'C' THEN g.grade ELSE 0 END ) as c语言,MAX(CASE WHEN g.subjects = 'JAVA' THEN g.grade ELSE 0 END ) as java,MAX(CASE WHEN g.subjects = '数据结构' THEN g.grade ELSE 0 END ) as 数据结构,sum(g.grade) ,g.date from student s left join grade g on s.no = g.no GROUP BY s.name";
ResultSet rs=jdbc.findTableValue(selectSql);
System.out.println(rs);
List listConnect=new ArrayList();
try {
int i = 0;
while(rs.next()){
// System.out.println(rs.getString(2));
List rowList=new ArrayList();
all all = new all();
all.setName(rs.getString(1));
all.setSex(rs.getString(2));
all.setC(rs.getString(3));
all.setJava(rs.getString(4));
all.setSjjg(rs.getString(5));
all.setSum(rs.getString(6));
allMap.put(i, all);
// rowList.add(rs.getString(1));
// rowList.add(rs.getString(2));
// rowList.add(rs.getString(3));
// rowList.add(rs.getString(4));
// rowList.add(rs.getString(5));
// rowList.add(rs.getString(6));
String inputdate = rs.getString(7);
DateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd");
DateFormat outputFormat = new SimpleDateFormat("yyyy年MM月dd日");
try {
Date date = inputFormat.parse(inputdate);
outputstr = outputFormat.format(date);
all.setDate(outputstr);
System.out.println(outputstr);
} catch (Exception e) {
// TODO: handle exception
}
rowList.add(outputstr);
listConnect.add(rowList);
System.out.println(listConnect);
i++;
// Export export = new Export();
// export.exportTxt(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),outputstr);
}
// for(int i=0;i<listConnect.size();i++){
// HashMap map =(HashMap)listConnect.get(i);
System.out.println(map);
// System.out.println(map.get("name"));
// System.out.println(map.get("num"));
// System.out.println(map.get("sex"));
// System.out.println("---------------------------------");
//
// }
// System.out.println(listConnect.get(1));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return allMap;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
}
}