# 还在担心报表不好做？不用怕，试试这个方法

01/29 09:39

1. 抽象数据结构

class Data {
public String School;
public int Class;
public String PrintDate;
public ArrayList<Score> Scores = new ArrayList<>();
}

class Score {
public int SID;
public String SName;
public int Chinese;
public int Math;
public int English;

public Score(int sid, String sName, int chinese, int math, int english) {
this.SID = sid;
this.SName = sName;
this.Chinese = chinese;
this.Math = math;
this.English = english;
}
}


2. 传数据

private static Data CreateData() {
Data data = new Data();
data.School = "高新第六小学";
data.Class = 2;
data.PrintDate = "2023年1月5日";

data.Scores.add(new Score(1, "李明", 98, 100, 96));
data.Scores.add(new Score(2, "王芳", 96, 97, 98));
data.Scores.add(new Score(3, "张锋", 99, 99, 95));
data.Scores.add(new Score(4, "高明", 94, 96, 100));
data.Scores.add(new Score(5, "沈梦", 90, 94, 93));
data.Scores.add(new Score(6, "张菲", 93, 94, 95));
data.Scores.add(new Score(7, "白洁", 95, 92, 94));
data.Scores.add(new Score(8, "王鹏", 96, 97, 99));
data.Scores.add(new Score(9, "刘玲", 96, 93, 94));
data.Scores.add(new Score(10, "李丽", 94, 98, 99));
return data;
}


3. 生成报表逻辑

Data data = CreateData();

Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getRange("A1").setValue(data.School);
worksheet.getRange("A3").setValue("年级");
worksheet.getRange("D3").setValue("班级");
worksheet.getRange("E3").setValue(data.Class);

Object[] array = new Object[]{"学号", "姓名", "语文", "数学", "英语"};
worksheet.getRange("A5:E5").setValue(array);

worksheet.getRange("D7").setValue("打印日期");
worksheet.getRange("E7").setValue("2023年1月5日");

//报表样式
worksheet.getRange("A1:E1").setColumnWidthInPixel(140);
worksheet.getRange("1:2").setRowHeightInPixel(50);

worksheet.getRange("A1:E1").merge();
worksheet.getRange("A1").getFont().setSize(16);
worksheet.getRange("A1").setHorizontalAlignment(HorizontalAlignment.Center);

worksheet.getRange("B3,E3").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.GetBlack());
worksheet.getRange("A3:E3,D7").setHorizontalAlignment(HorizontalAlignment.Center);

worksheet.getRange("E7").setHorizontalAlignment(HorizontalAlignment.Right);

int baseRow = 5;
int dataRowCount = data.Scores.size();
worksheet.getRange(5, 0, dataRowCount, 1).getEntireRow().insert(InsertShiftDirection.Down);
for (int i = 0; i < data.Scores.size(); i++) {
worksheet.getRange(baseRow + i, 0).setValue(data.Scores.get(i).SName);
worksheet.getRange(baseRow + i, 1).setValue(data.Scores.get(i).SID);
worksheet.getRange(baseRow + i, 2).setValue(data.Scores.get(i).Chinese);
worksheet.getRange(baseRow + i, 3).setValue(data.Scores.get(i).Math);
worksheet.getRange(baseRow + i, 4).setValue(data.Scores.get(i).English);
}

worksheet.getRange("A5:E5").getInterior().setColor(Color.FromArgb(21, 96, 130));
worksheet.getRange("A5:E5").getFont().setColor(Color.GetWhite());
worksheet.getRange("A5:E5").setHorizontalAlignment(HorizontalAlignment.Center);

worksheet.getRange(baseRow, 0, dataRowCount, 5).getInterior().setColor(Color.FromArgb(192, 230, 245));
worksheet.getRange(baseRow - 1, 0, dataRowCount + 1, 5).getBorders().setColor(Color.FromArgb(68, 179, 225));
worksheet.getRange(baseRow - 1, 0, dataRowCount + 1, 2).setHorizontalAlignment(HorizontalAlignment.Center);

worksheet.getSheetView().setDisplayGridlines(false);


4. 通过模板简化生成报表逻辑代码

Data data = CreateData();

Workbook workbook = new Workbook();
workbook.open("template.xlsx");
IWorksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getRange("A1").setValue(data.School);
worksheet.getRange("E3").setValue(data.Class);
worksheet.getRange("E8").setValue("2023年1月5日");

int baseRow = 5;
int dataRowCount = data.Scores.size();
worksheet.getRange(baseRow + 1, 0, dataRowCount - 1, 1).getEntireRow().insert(InsertShiftDirection.Down);
worksheet.getRange(baseRow, 0, 1, 5).copy(worksheet.getRange(baseRow + 1, 0, dataRowCount - 1, 5));
//循环给报表中的字段传值
for (int i = 0; i < data.Scores.size(); i++) {
worksheet.getRange(baseRow + i, 0).setValue(data.Scores.get(i).SName);
worksheet.getRange(baseRow + i, 1).setValue(data.Scores.get(i).SID);
worksheet.getRange(baseRow + i, 2).setValue(data.Scores.get(i).Chinese);
worksheet.getRange(baseRow + i, 3).setValue(data.Scores.get(i).Math);
worksheet.getRange(baseRow + i, 4).setValue(data.Scores.get(i).English);
}


5. 使用模板语言二次简化代码

Workbook workbook = new Workbook();
workbook.open("template.xlsx");
workbook.processTemplate();
workbook.save("reprot.xlsx");


Redis从入门到实践

Chrome开发者工具使用教程

0 评论
0 收藏
0