AsposeCell特定格式表格
2015-11-06 15:02 查士丁尼 阅读(810) 评论(0) 编辑 收藏 举报效果图:
Workbook workbook = new Workbook();
Worksheet sheet = (Worksheet)workbook.Worksheets[0];
Cells cells = sheet.Cells;
cells.SetColumnWidth(8, 15);
cells.SetColumnWidth(3, 16);
cells.SetColumnWidth(0, 17);
List<string> institutionList = (from DataRow q in dt.Rows
select q["institutionId"].ToString()).ToList().Distinct().ToList();
int y = 0;
List<Institution> insList = new List<Institution>();
if (institutionList.Count > 0)
{
insList = (from q in Institution.CreateContext()
where institutionList.Contains(q.Id)
select q).ToList();
}
foreach (string institution in institutionList)
{
Institution ins = (from q in insList
where q.Id == institution
select q).FirstOrDefault();
DataRow[] drs = (from DataRow q in dt.Rows
where q["institutionId"].ToString() == institution
select q).ToArray();
List<string> userIds = new List<string>();
foreach (var dr in drs)
{
userIds.Add(dr["userId"].ToString());
}
userIds = (from q in userIds select q).ToList().Distinct().ToList();
foreach (var userId in userIds)
{
DataRow[] drstu = (from DataRow q in dt.Rows
where q["institutionId"].ToString() == institution && q["userId"].ToString() == userId
select q).ToArray();
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
//标题
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style.Font.Name = "宋体";//文字字体
style.Font.Size = 14;//文字大小
style.Font.IsBold = true;//粗体
cells.Merge(y, 0, 1, 10); //合并单元格
cells[y, 0].PutValue(string.Format("{0}{1}考试通知单", drstu[0]["examName"].ToString(), drstu[0]["institutionName"].ToString())); //填写内容
cells[y, 0].SetStyle(style); //给单元格关联样式
cells.SetRowHeight(y, 28); //设置行高
style.HorizontalAlignment = TextAlignmentType.Left;//文字居左
style.Font.Size = 10;
style.Font.IsBold = false;
cells.Merge(y + 1, 0, 1, 4);
cells.Merge(y + 1, 4, 1, 6);
cells[y + 1, 0].PutValue("班级:");
cells[y + 1, 4].PutValue(string.Format("{0}{1}", drstu[0]["courseName"].ToString(), drstu[0]["courseCode"].ToString()));
cells[y + 1, 0].SetStyle(style);
cells.SetRowHeight(y + 1, 14);
string[] titles = new string[] { "学号", "姓名", "试卷号", "考试科目", "考核形式", "考场号", "座位号", "考试日期", "考试时间", "留考" };
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
for (int i = 0; i < 10; i++)
{
cells[y + 2, i].PutValue(titles[i]);
cells[y + 2, i].SetStyle(style);//调整样式
}
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
for (int j = 0; j < drstu.Length; j++)//行
{
cells[y + 3 + j, 0].PutValue(drstu[j]["code"].ToString());
cells[y + 3 + j, 1].PutValue(drstu[j]["displayName"].ToString());
cells[y + 3 + j, 2].PutValue(string.IsNullOrEmpty(drstu[j]["paperano"].ToString()) ? "---" : drstu[j]["paperano"].ToString());
cells[y + 3 + j, 3].PutValue(drstu[j]["courseName"].ToString());
cells[y + 3 + j, 4].PutValue(drstu[j]["IsOpenBook"].ToInt() == 1 ? "开卷" : "闭卷");
cells[y + 3 + j, 5].PutValue(drstu[j]["placeNumber"].ToString());
cells[y + 3 + j, 6].PutValue(drstu[j]["seatNumber"].ToString());
cells[y + 3 + j, 7].PutValue(drstu[j]["startTime"].ToDateTime().ToShortDateString().ToString());
cells[y + 3 + j, 8].PutValue(string.Format("{0} - {1}", drstu[j]["startTime"].ToDateTime().ToShortTimeString().ToString(), drstu[j]["endTime"].ToDateTime().ToShortTimeString().ToString()));
cells[y + 3 + j, 9].PutValue(drstu[j]["isstay"].ToInt() == 1 ? "是" : "否");
}
cells[y + 3 + drstu.Length, 0].PutValue("考点名称:");
cells.Merge(y + 3 + drstu.Length, 1, 1, 3);
cells[y + 3 + drstu.Length, 1].PutValue(ins.Name);
cells[y + 3 + drstu.Length, 4].PutValue("考点地址:");
cells.Merge(y + 3 + drstu.Length, 5, 1, 5);
cells[y + 3 + drstu.Length, 5].PutValue(ins.Address);
y = y + 5 + drstu.Length;
}
}