joinbydbcontrol.java

利用poi插件和Java语言在web中实现excel的合并功能

package com.dao.JoinByDB;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.poifs.filesystem.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


import com.bean.JoinByDB.JoinByDBForm;
import com.dao.conn.ConnDB;


public class JoinByDBControl {
	/*将数据从数据入口：excel表格录入数据库，并从数据库
	 * 取出数据按公式统计计算，将结果重新录入excel表格中
	*/
	
	private String[] name=new String[5];//存储学生姓名
	private String course;//存储课程名称
	private double[] score=new double[5];//存储学生成绩
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	private String query;
	public ConnDB control = null;
	
	public JoinByDBControl() {
		control=new ConnDB();
	}
	/**
     * 读取文件内容
     * @param sourcefile
     *        源文件路径
     * @param targetfile
     *        目标文件路径
     */
      public void readsourcefile(String sourcefile) {
   	  try {
   		  POIFSFileSystem sourceFs = null;
  		      HSSFWorkbook sourceWb = null;
  		      HSSFSheet sourceSheet = null;
  		     
  		      sourceFs = new POIFSFileSystem(new FileInputStream(sourcefile));
		      sourceWb = new HSSFWorkbook(sourceFs);
		      
		      int sheetCount = getSheetCount(sourceWb);
		     
		      for (int j = 0; j < sheetCount; j++) {
					// 取得源sheet
					sourceSheet = sourceWb.getSheetAt(j);
					getcontent(sourceSheet);
				}
		  }catch (Exception e) {    
   	   e.printStackTrace();    
   	  }    
   	}
      
      /**
     	 * 获取所有sheet数量
     	 * 
     	 * @param wb
     	 * @return 返回该sheet数量
     	 */
     	protected int getSheetCount(HSSFWorkbook wb) {
     		int count = 0;
     		try {
     			for (int i = 0; null != wb.getSheetAt(i); i++) {
     				count++;
     			}
     		} catch (Exception e) {
     			return count;
     		}
     		return count;
     	}
     	
     	/**
  	 * 取文件内容
  	 * 
  	 * @param sheet
  	 *            取sheet的内容
  	 */
  	protected void getcontent(HSSFSheet sheet) {
  		int total = 0;
  		HSSFRow row;
  		String Name=new String();
  		double Score=0;
  		String Course=new String();
  		for (int i = 0;; i++) {
  			// 取前第i行
  			row = sheet.getRow(i);			
  			if (null == row) {
  				total = i;
  				break;
  			}
            for(int j=0;j<row.getLastCellNum();j++)
   			{//按行取数据
  				switch(j) {
  				case 0:{//取第一列数据，即学生姓名
  					     Name=row.getCell((short)j).getStringCellValue();
  				         break;
  				       }
  				case 1:{//取第二列数据：成绩，并保存课程信息
  					    if(i==0) Course=row.getCell((short)j).getStringCellValue();
  				        if(i>0) Score=row.getCell((short)j).getNumericCellValue();
  				        break;
  				       }
  			}
  			name[i]=Name;//利用姓名数组保存记录属性：学生姓名
  			score[i]=Score;//利用成绩数组保存记录属性：学生成绩
  		}
  		if(i==0) course=Course;//保存课程信息
  		else {//从第二行开始，将数据录入数据库中
  				if(insert(name[i],course,score[i])) ;
  			}
  		}
  	}
     //将excel中数据录入到数据库中	
    public boolean insert(String name,String course,double score) {
		conn = ConnDB.getConnection();
		query = "insert into t2(name,course,score)";
		query += " values(?,?,?)";
		try {
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1,name);
			pstmt.setString(2,course);
			pstmt.setDouble(3,score);
			int count = 0 ;
			count = pstmt.executeUpdate();
			conn.commit();
			if(1 == count){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
 
    //获得标题
	public List getCourse() {
		List list = new ArrayList();
		query = "select distinct course from t2;";
        try {
	         rs = control.executeQuery(query);
	         while (rs.next()) {
		      JoinByDBForm bean = new JoinByDBForm();
		       bean.setCourse(rs.getString(1));
		       list.add(bean);
	         }
        } catch (SQLException e) {
	          e.printStackTrace();
        } finally {
	          control.close();
        }
        return list;
	}
	
	 //获得姓名及成绩
	public List getnameandscore() {
		List list = new ArrayList();
		query = "select distinct t2.name,english.score,chinese.score,math.score,personal.avg,personal.total";
		query += " from t2 join (select name,score from t2 where course='英语') as english";
        query += " join (select name,score from t2 where course='语文') as chinese";
        query += " join (select name,score from t2 where course='数学') as math";
        query += " join (select distinct name,avg(score) avg,sum(score) total from t2 group by name) as personal";
        query += " on t2.name=english.name and t2.name=chinese.name and t2.name=math.name and personal.name=t2.name";
        try {
	         rs = control.executeQuery(query);
	         while (rs.next()) {
		      JoinByDBForm bean = new JoinByDBForm();
		       bean.setName(rs.getString(1));
		       bean.setEnglishscore(rs.getDouble(2));
		       bean.setChinesescore(rs.getDouble(3));
		       bean.setMathscore(rs.getDouble(4));
		       bean.setAverage(rs.getDouble(5));
		       bean.setTotal(rs.getDouble(6));
		       list.add(bean);
	         }
        } catch (SQLException e) {
	          e.printStackTrace();
        } finally {
	          control.close();
        }
        return list;
	}
	
	//获得每门课的平均成绩
	public List getAverage() {
		List list = new ArrayList();
		query = "select distinct avg(english.score),avg(chinese.score),avg(math.score) from t2";
        query += " join (select name,score from t2 where course='英语') as english";
        query += " join (select name,score from t2 where course='语文') as chinese";
        query += " join (select name,score from t2 where course='数学') as math";
        query += " on t2.name=english.name and t2.name=chinese.name and t2.name=math.name group by course order by avg(english.score)";
		try {
	         rs = control.executeQuery(query);
	         while (rs.next()) {
		      JoinByDBForm bean = new JoinByDBForm();
		       bean.setEnglishscore(rs.getDouble(1));
		       bean.setChinesescore(rs.getDouble(2));
		       bean.setMathscore(rs.getDouble(3));
		       list.add(bean);
	         }
        } catch (SQLException e) {
	          e.printStackTrace();
        } finally {
	          control.close();
        }
        return list;
	}
	
	//获得每门课的总计成绩
	public List getTotal() {
		List list = new ArrayList();
		query = "select distinct sum(english.score),sum(chinese.score),sum(math.score) from t2";
        query += " join (select name,score from t2 where course='英语') as english";
        query += " join (select name,score from t2 where course='语文') as chinese";
        query += " join (select name,score from t2 where course='数学') as math";
        query += " on t2.name=english.name and t2.name=chinese.name and t2.name=math.name group by course order by sum(english.score)";
		try {
	         rs = control.executeQuery(query);
	         while (rs.next()) {
		      JoinByDBForm bean = new JoinByDBForm();
		       bean.setEnglishscore(rs.getDouble(1));
		       bean.setChinesescore(rs.getDouble(2));
		       bean.setMathscore(rs.getDouble(3));
		       list.add(bean);
	         }
        } catch (SQLException e) {
	          e.printStackTrace();
        } finally {
	          control.close();
        }
        return list;
	}
    //将统计结果按规定格式录入excel表格中
    public void WriteCell() {
		HSSFWorkbook wb = new HSSFWorkbook();//创建工作簿
		HSSFSheet sheet = wb.createSheet("achievement");//创建工作表
		sheet.setColumnWidth((short) 5, (short) 3000);//设置列宽
		sheet.setColumnWidth((short) 6, (short) 3000);
		sheet.setColumnWidth((short) 7, (short) 3000);
		sheet.setColumnWidth((short) 8, (short) 3000);
		sheet.setColumnWidth((short) 9, (short) 3000);
		sheet.setColumnWidth((short) 10, (short) 3000);
		sheet.setHorizontallyCenter(true);//设置内容位置
		sheet.setVerticallyCenter(true);
		HSSFCellStyle style = wb.createCellStyle();//创建单元格格式对象
		HSSFFont font=wb.createFont(); //创建字体对象
		font.setFontHeight((short)300);//设置字体大小
		style.setFont(font);//将字体设置加入到单元格格式定义中
		  
		style.setBorderBottom(HSSFCellStyle.BORDER_THICK); // 设置单无格的边框为粗体 
		  style.setBottomBorderColor(HSSFColor.GOLD.index); // 设置单元格的边框颜色． 
		  style.setBorderLeft(HSSFCellStyle.BORDER_THICK); 
		  style.setLeftBorderColor(HSSFColor.GOLD.index); 
		  style.setBorderRight(HSSFCellStyle.BORDER_THICK); 
		  style.setRightBorderColor(HSSFColor.GOLD.index); 
		  style.setBorderTop(HSSFCellStyle.BORDER_THICK); 
		  style.setTopBorderColor(HSSFColor.GOLD.index); 
		  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置单元格内容对齐方式
		  style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
          //设定单元个背景颜色
          style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
          //设置单元格显示颜色
          style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置单元格显示样式
          
          HSSFCellStyle style1 = wb.createCellStyle();
		  style1.setFont(font);
		  style1.setBorderBottom(HSSFCellStyle.BORDER_DASHED); // 设置单无格的边框为粗体 
		  style1.setBottomBorderColor(HSSFColor.DARK_YELLOW.index); // 设置单元格的边框颜色． 
		  style1.setBorderLeft(HSSFCellStyle.BORDER_DASHED); 
		  style1.setLeftBorderColor(HSSFColor.DARK_YELLOW.index); 
		  style1.setBorderRight(HSSFCellStyle.BORDER_DASHED); 
		  style1.setRightBorderColor(HSSFColor.DARK_YELLOW.index); 
		  style1.setBorderTop(HSSFCellStyle.BORDER_DASHED); 
		  style1.setTopBorderColor(HSSFColor.DARK_YELLOW.index);
		  style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		  style1.setFillBackgroundColor(HSSFColor.AQUA.index);
          //设定单元个背景颜色
          style1.setFillForegroundColor(HSSFColor.AQUA.index);
          //设置单元格显示颜色
          style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        
        int rownum=11;//定义工作表数据起始行
		
        HSSFRow row = sheet.createRow(11);//创建行
        rownum++;
		HSSFCell title[] = new HSSFCell[20];//定义单元格数组
		for (short i = 5; i < 11; i++) {
			title[i] = row.createCell(i);//创建单元格
			title[i].setCellStyle(style);//获取单元格格式定义
		}
		title[5].setCellValue("姓名\\课程");//获取单元格内容
		List course = this.getCourse();
		if (course.size() > 0 && course != null) {
			int i=0;
			for (i = 0; i < course.size(); i++) {
				JoinByDBForm titleform = (JoinByDBForm) course.get(i);
				title[i+6].setCellValue(titleform.getCourse());
			}
			title[i+6].setCellValue("平均");
			title[i+7].setCellValue("总计");
		}
		
		List nameandscore =this.getnameandscore();
		if (nameandscore.size() > 0 && nameandscore != null) {
			for (int i = 0; i < nameandscore.size(); i++) {
                JoinByDBForm namescore = (JoinByDBForm) nameandscore.get(i);
                HSSFRow datarow = sheet.createRow(rownum);
				rownum++;
				HSSFCell data[] = new HSSFCell[20];
				for (short j = 5; j < 11; j++) {
					data[j] = datarow.createCell(j);
					data[j].setCellStyle(style1);
				}
                data[5].setCellValue(namescore.getName());
				data[6].setCellValue(namescore.getEnglishscore());
				data[7].setCellValue(namescore.getChinesescore());
				data[8].setCellValue(namescore.getMathscore());
				data[9].setCellValue(namescore.getAverage());
				data[10].setCellValue(namescore.getTotal());
			}
		}
		row = sheet.createRow(rownum);
        rownum++;
        HSSFCell average[] = new HSSFCell[20];
		for (short i = 5; i < 11; i++) {
			average[i] = row.createCell(i);
			average[i].setCellStyle(style);
		}
		average[5].setCellValue("平均：");
		List avgscore = this.getAverage();
		if (avgscore.size() > 0 && avgscore != null) {
			for (int i = 0; i < avgscore.size(); i++) {
				JoinByDBForm averages = (JoinByDBForm) avgscore.get(i);
		        average[6].setCellValue(averages.getEnglishscore());
				average[7].setCellValue(averages.getChinesescore());
				average[8].setCellValue(averages.getMathscore());
			}
		}
		row = sheet.createRow(rownum);
        rownum++;
        HSSFCell total[] = new HSSFCell[20];
		for (short i = 5; i < 11; i++) {
			total[i] = row.createCell(i);
			total[i].setCellStyle(style);
		}
		total[5].setCellValue("总计：");
		List toascore = this.getTotal();
		if (toascore.size() > 0 && toascore != null) {
			for (int i = 0; i < toascore.size(); i++) {
				JoinByDBForm totals = (JoinByDBForm) toascore.get(i);
		        total[6].setCellValue(totals.getEnglishscore());
				total[7].setCellValue(totals.getChinesescore());
				total[8].setCellValue(totals.getMathscore());
			}
		}
		File file = new File("e:\\CalculateCell\\achievement.xls");//创建文件对象
		try {
			FileOutputStream fileOut = new FileOutputStream(file);//创建文件输出流对象
			wb.write(fileOut);//调用工作簿对象的写方法，将定义的工作簿内容写入输出流文件中
			fileOut.close();//关闭输出流
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
}