实验目标:
根据已有的学生成绩单,完成对学生期末成绩的统计、分析功能。已知学生成绩已经录入到具有固定格式的Excel文档中(文档格式见成绩单.xls),需要通过程序计算出学生期末成绩中90分-100分、80分-89分、70分-79分、60分-69分、小于60分这几个分数段的学生人数和比例、成绩的平均值、最高分和最低分,并将这些结果插入到给出的成绩分析.xls文件中。
实验材料:(表格只写出了要用的数据)
成绩单.xls
序号 | 班级 | 学号 | 姓名 | 平时(必填) | 实验(必填) | 期末(必填) | 备注 |
---|---|---|---|---|---|---|---|
13 | |||||||
90 | |||||||
56 | |||||||
50 | |||||||
23 | |||||||
64 | |||||||
37 | |||||||
58 | |||||||
85 | |||||||
63 | |||||||
60 | |||||||
38 | |||||||
28 | |||||||
38 | |||||||
96 | |||||||
26 | |||||||
51 | |||||||
74 | |||||||
55 | |||||||
64 | |||||||
50 | |||||||
80 | |||||||
89 | |||||||
53 | |||||||
25 | |||||||
46 | |||||||
38 | |||||||
40 | |||||||
55 | |||||||
36 | |||||||
72 | |||||||
64 | |||||||
44 | |||||||
34 | |||||||
65 | |||||||
94 | |||||||
90 | |||||||
49 | |||||||
37 | |||||||
94 | |||||||
31 | |||||||
66 | |||||||
77 | |||||||
77 | |||||||
99 | |||||||
92 | |||||||
98 | |||||||
28 | |||||||
69 | |||||||
31 | |||||||
36 | |||||||
67 | |||||||
79 |
成绩分析.xls
基础准备:(jar包的下载和导入eclipse)
1、在进行此实验前首先须准备好Apache POI相关文档压缩包:poi-bin-4.1.0-20190412.zip,下载地址:https://poi.apache.org/
2、在eclipse中新建一个项目。
3、在项目名称上右键。选择New->Floder,新建一个lib文件夹,将下载的压缩包里要用到的文件夹/jar包拷贝到lib文件夹下。
(需要用到的文件夹/jar包有压缩包中的lib文件夹、ooxml-lib文件夹、poi-4.1.0.jar、poi-examples-4.1.0.jar、poi-excelant-4.1.0.jar、poi-ooxml-4.1.0.jar、poi-ooxml-schemas-4.1.0.jar、poi-scratchpad-4.1.0.jar)
4、在所有jar包上右击,选择Buid Path->Add To Build Path。
核心代码:
1、数据读取:
public static void readExcel() {
try {
// 获得Excel文件输入流
FileInputStream in = new FileInputStream("D:\\test.xls");
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(in);
// 创建对工作表的引用。
// 得到Excel工作簿的第一页,即excel工作表对象
// 在Excel文档中,第一张工作表的索引是0
HSSFSheet sheet = workbook.getSheetAt(0);
// 得到工作表中第一行的引用,第一行的索引是0
HSSFRow row = sheet.getRow(0);
//得到某一行的第一个单元格,单元格的索引也是从0开始
HSSFCell cell = row.getCell(0);
in.close();// 关闭文件输入流
}
//上面程序正确时,不会执行此处代码
catch (Exception e) {
System.out.println("出错了!");
e.printStackTrace();
}
}
2、数据写入:
public static void writeExcel() {
try {
// 获得Excel文件输入流
FileInputStream in = new FileInputStream("D:\\test1.xls");
// 创建excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook(in);
// 获得excel中第一个工作页对象,索引从0开始
HSSFSheet sheet = wb.getSheetAt(0);
// 创建工作表中一行,索引从0开始
HSSFRow row1 = sheet.createRow(0);
// 创建工作表中单元格,索引从0开始
HSSFCell cell1_1 = row1.createCell(0);
// 设置单元格内容
cell1_1.setCellValue("内容");
// 获得Excel文件输出流
FileOutputStream out = new FileOutputStream("D:\\test2.xls");
// 输出excel
wb.write(out);
// 关闭文件输入、输出流
in.close();
out.close();
}
// 上面程序正确时,不会执行此处代码
catch (Exception e) {
System.out.println("出错了!");
e.printStackTrace();
}
}
注意:
上面的写入函数是创建新的excel,并且将在新的excel文档中输出。HSSFRow row1 = sheet.createRow(0);这是创建了新的一行。如果直接使用这个方式来写入数据会发生什么呢?你会发现,原excel中的一些内容被覆盖了。新写入的数据是存在的,但是如“人数”、“比例”、“平均值”、“最高分”、“最低分”这些内容不再存在。
怎么解决这个问题?将create改为get。字面意义是生成改为获取。HSSFRow row1 = sheet.getRow(0);获取第一行。HSSFCell cell1_1 = row1.createCell(0);在第一个位置生成单元格。cell1_1.setCellValue(“内容”);在这个单元格写入内容。因为避免了create新的单元格,就避免了原先的值被覆盖。
数据处理(个人理解):
1、处理数据时肯定要遍历excel单元格,那么怎么知道有多少数据呢?int rownums = sheet.getLastRowNum(); rownums就是行数。
2、通过遍历就可以得到平均值、最高分、最低分、人数比例等数据。
3、题目要求有小数的数据保留两位小数,如何使double型数据保留两位s小数?Math.round(data*100)/100; 此操作后,double型数据data就保留了两位小数。(还有其他方法如format请自行百度)
4、最后的结果示例图中有这样的细节:单元格左上角带有绿色小三角。绿色小三角是什么?在excel中可得知,之所以带有绿色小三角是因为该单元格的单元格格式为文本类型,提示你可能在使用该单元格内数据进行运算时可能会出错。怎么才会造成单元格出现绿色小三角?其实在你写入数据的时候,如果类型为String类型,那么在excel中就将单元格设置成了文本类型,自然就会出现绿色小三角。
5、如果你直接这样做:cell3_2.setCellValue(average); (average已经是58.0) 那么你的excel表格中的数据将会是58,小数点将被省略。其实你在excel表格中在一个单元格输入58.0,它存入的总是58。如何解决excel自动将.0忽略的问题呢?很简单,将double型转为String型。最简单的转换方式是隐式转换。即58.0+"";即cell3_2.setCellValue(average+"");
6、try和catch,这两个是搭配使用的,不可或缺。
7、迭代器。其实进行数据处理的时候,迭代器是最好的选择。创建好迭代器进行循环,直到最后一行,不需要得知行数。本质上和我们写循环遍历是一样的。
全部代码:
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.text.DecimalFormat;
import javax.swing.text.html.HTMLDocument.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
public class POI_test {
static double averagescore = 0, excellentratio = 0, fineratio = 0, mediumratio = 0, passratio = 0, failratio = 0;
static int excellent = 0, fine = 0, medium = 0, pass = 0, fail = 0;
static double topscore = 0;
static double lowestmark = 101;
public static void main(String[] args) {
// TODO Auto-generated method stub
readExcel();
writeExcel();
}
public static void readExcel() {
try {
FileInputStream in = new FileInputStream("F:\\java程序设计\\实验一\\成绩单.xls");
HSSFWorkbook workbook = new HSSFWorkbook(in);
HSSFSheet sheet = workbook.getSheetAt(0);
//计算
double totalpoints = 0;
HSSFRow row;
HSSFCell cell;
int rownum = sheet.getLastRowNum();
for(int i = 1; i<=rownum; i++) {
row = sheet.getRow(i);
cell = row.getCell(6);
//总分
totalpoints = totalpoints + cell.getNumericCellValue();
//最高分
if(cell.getNumericCellValue() > topscore) {
topscore = cell.getNumericCellValue();
}
//最低分
if(cell.getNumericCellValue() < lowestmark) {
lowestmark = cell.getNumericCellValue();
}
//分档
if(cell.getNumericCellValue()>=90)
{
excellent++;
}
if(cell.getNumericCellValue()<=89 && cell.getNumericCellValue()>=80)
{
fine++;
}
if(cell.getNumericCellValue()<=79 && cell.getNumericCellValue()>=70)
{
medium++;
}
if(cell.getNumericCellValue()<=69 && cell.getNumericCellValue()>=60)
{
pass++;
}
if(cell.getNumericCellValue()<60)
{
fail++;
}
}
averagescore = totalpoints / (double)rownum;
excellentratio = excellent / (double)rownum * 100;
fineratio = fine / (double)rownum * 100;
mediumratio = medium / (double)rownum * 100;
passratio = pass / (double)rownum * 100;
failratio = fail / (double)rownum * 100;
in.close();
}
catch (Exception e) {
System.out.println("出错了!");
e.printStackTrace();
}
}
public static void writeExcel() {
try {
FileInputStream in = new FileInputStream("F:\\java程序设计\\实验一\\成绩分析.xls");
HSSFWorkbook wb = new HSSFWorkbook(in);
HSSFSheet sheet = wb.getSheetAt(0);
//写入第二行
HSSFRow row1 = sheet.getRow(1);
HSSFCell cell1_2 = row1.createCell(2);
cell1_2.setCellValue(excellent);
HSSFCell cell1_3 = row1.createCell(3);
cell1_3.setCellValue(fine);
HSSFCell cell1_4 = row1.createCell(4);
cell1_4.setCellValue(medium);
HSSFCell cell1_5 = row1.createCell(5);
cell1_5.setCellValue(pass);
HSSFCell cell1_6 = row1.createCell(6);
cell1_6.setCellValue(fail);
//写入第三行
HSSFRow row2 = sheet.getRow(2);
HSSFCell cell2_2 = row2.createCell(2);
cell2_2.setCellValue((double)Math.round(excellentratio*100)/100+"%");
HSSFCell cell2_3 = row2.createCell(3);
cell2_3.setCellValue((double)Math.round(fineratio*100)/100+"%");
HSSFCell cell2_4 = row2.createCell(4);
cell2_4.setCellValue((double)Math.round(mediumratio*100)/100+"%");
HSSFCell cell2_5 = row2.createCell(5);
cell2_5.setCellValue((double)Math.round(passratio*100)/100+"%");
HSSFCell cell2_6 = row2.createCell(6);
cell2_6.setCellValue((double)Math.round(failratio*100)/100+"%");
//写入第四行
HSSFRow row3 = sheet.getRow(3);
HSSFCell cell3_2 = row3.createCell(2);
cell3_2.setCellValue(averagescore+"");
HSSFCell cell3_4 = row3.createCell(4);
cell3_4.setCellValue(topscore);
HSSFCell cell3_6 = row3.createCell(6);
cell3_6.setCellValue(lowestmark);
// 获得Excel文件输出流
FileOutputStream out = new FileOutputStream("F:\\java程序设计\\实验一\\成绩分析.xls");
// 输出excel
wb.write(out);
// 关闭文件输入、输出流
in.close();
out.close();
} catch (Exception e) {
// 上面程序正确时,不会执行此处代码
System.out.println("出错了!");
e.printStackTrace();
}
}
}