package newexam;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import newexam.DAOAccess;
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;
public class GetScoreToExcel {
static String fileName = "d:/fc/19考场汇总.crp";
static String excelFileName = "19考场汇总.xls";
static File outDir = new File("d:/fc");
// 下面的两个量不要改,一个是成绩工作簿的模板文件,另一个是工作表的名称
static String tempFile = "成绩模板";
static String sheetName = "成绩表";
static String titleName = "";
// 文件输出路径
static File absoluteDir = new File(outDir.getAbsolutePath());
static Connection con = null;
// 类静态方法,完成对数据库连接等初始化工作
static {
if (!absoluteDir.exists()) {
System.out.println("正在创建文件夹");
absoluteDir.mkdirs();
}
titleName = excelFileName.substring(0, excelFileName.length() - 4);
}
/**
* 从加密的成绩字符串Tx中取出各单项成绩,形成一个成绩字符串数组
*
* @param tx
* 加密后成绩字符串
* @return 解密后的<code>成绩字符串数组</code>
* 其中分数列表为:单选、多选、判断、填空、Windows操作、Word、Excel、PPT等
*/
public static String[] getScoreByTx(String tx) {
char hexChar[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
'a', 'b', 'c', 'd', 'e', 'f' };
tx = tx.trim();
// 成绩字符串解密,密码0x79
byte b[] = tx.getBytes();
for (int i = 0; i < b.length; i++)
b[i] ^= 0x79;
// 定义一个分数数组,长度由Tx决定
char cHex[] = new char[b.length];
int k = 0;
// 将每个字节内容转换成分数,分数之间有个分隔符'c'
for (int i = 0; i < b.length; i++) {
byte x = b[i];
// 高四位没有使用
// cHex[k++] = hexChar[x >>> 4 & 0xf];
cHex[k++] = hexChar[x & 0xf];
}
String res = new String(cHex);
// 成绩字符串数组使用"c"分隔
return res.split("c");
}
/**
* @param score
* 存放考生成绩,其中第4,8,13位是成绩,其它位是学生的考号
* @return 考生的成绩
*/
public static int getTotal(String score) {
char hexChar[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
'a', 'b', 'c', 'd', 'e', 'f' };
byte b[] = score.getBytes();
for (int i = 0; i < b.length; i++)
b[i] ^= 0x79;
char cHex[] = new char[b.length];
int k = 0;
for (int i = 0; i < b.length; i++) {
byte x = b[i];
// 高四位没有使用
// cHex[k++] = hexChar[x >>> 4 & 0xf];
cHex[k++] = hexChar[x & 0xf];
}
String res = new String(cHex);
// 从成绩字符串中的第4,8,13位中取出成绩(其它位是该学生的考号)
String s = "" + res.charAt(4) + res.charAt(8) + res.charAt(13);
return Integer.parseInt(s);
}
/**
* 从数据库中读取成绩,包括单项成绩和总成绩,放入指定的文件中
*/
public static void writeScoreToExcel() throws Exception {
// 利用模板创建新的成绩表文件
DAOAccess dao = new DAOAccess(fileName);
con = dao.getConnection();
Statement stat = con.createStatement();
String sql = "select * from kaoshengxinxi Order By sno";
ResultSet res = stat.executeQuery(sql);
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
new File(new File("").getAbsolutePath(), tempFile)));
HSSFWorkbook wb = new HSSFWorkbook(in);
HSSFSheet sheet = wb.getSheet(sheetName);
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell(0);
cell.setCellValue(titleName);
// line操作行号,从第2号开始第0行是标题,第1行是表头,num为考生人数
int line = 2, num = 0;
while (res.next()) {
num++;
row = sheet.createRow(line++);
// 开始写入考生各项信息
int item = 0;
row.createCell(item++).setCellValue(res.getString("sno"));
row.createCell(item++).setCellValue(res.getString("id"));
row.createCell(item++).setCellValue(res.getString("name"));
row.createCell(item++).setCellValue(res.getString("sex"));
row.createCell(item++).setCellValue(res.getString("major"));
row.createCell(item++).setCellValue(res.getString("levels"));
row.createCell(item++).setCellValue(
Integer.parseInt(res.getString("grade")));
row.createCell(item++).setCellValue(
Integer.parseInt(res.getString("times")));
row.createCell(item++).setCellValue(
Integer.parseInt(res.getString("examroom")));
String tempTx[] = getScoreByTx(res.getString("tx"));
for (String s : tempTx) {
row.createCell(item++).setCellValue(Integer.parseInt(s));
}
int tempScore = getTotal(res.getString("score"));
row.createCell(item++).setCellValue(tempScore);
}
in.close();
BufferedOutputStream out = new BufferedOutputStream(
new FileOutputStream(new File(absoluteDir, excelFileName)));
wb.write(out);
out.close();
System.out.println("成绩读取成功,共有" + num + "个考生,请检查" + excelFileName
+ "文件!");
}
public static void main(String[] args) throws Exception {
GetScoreToExcel.writeScoreToExcel();
}
}
分数放入Excel文件
最新推荐文章于 2022-08-23 20:01:16 发布