DataTable dtTitle = ds.Tables[0];
DataTable dtDetail = ds.Tables[1];
int columns = dtTitle.Columns.Count;
string error = "";//导出出错的原因
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); //工作簿
Worksheet sheet = wb.Worksheets[0]; //第一张工作表
Cells cells = sheet.Cells;//单元格
cells.SetColumnWidth(0, 15f);//第一列的宽
cells.SetColumnWidth(1, 10f);
cells.SetColumnWidth(2, 10f);
cells.SetColumnWidth(3, 10f);
DataRow dr = null;
string p = "";
if (dtTitle != null && dtTitle.Rows.Count > 0)
{
try
{
#region //为第一行表头单元格添加样式
Aspose.Cells.Style styleTitle = wb.Styles[wb.Styles.Add()];//新增样式
//设置居中
styleTitle.HorizontalAlignment = TextAlignmentType.Center;
//设置背景颜色
styleTitle.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
styleTitle.Pattern = BackgroundType.Solid;
//粗体
styleTitle.Font.IsBold = true;
//设置边框
//styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//styleTitle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
//styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//styleTitle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
//styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
//styleTitle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
//styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//styleTitle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
#endregion
#region 拼接表头
for (int i = 0; i < dtTitle.Rows.Count; i++)
{
dr = dtTitle.Rows[i];
#region 学号 姓名 均分 总分
cells.Merge(0, 0, 2, 1);//合并单元格
cells[0, 0].PutValue(dr["F4"]);//填写内容
cells[0, 0].SetStyle(styleTitle);//表头样式
cells.Merge(0, 1, 2, 1);//合并单元格
cells[0, 1].PutValue(dr["F5"]);//填写内容
cells[0, 1].SetStyle(styleTitle);//表头样式
cells.Merge(0, 2, 2, 1);//合并单元格
cells[0, 2].PutValue(dr["F9"]);//填写内容
cells[0, 2].SetStyle(styleTitle);//表头样式
cells.Merge(0, 3, 2, 1);//合并单元格
cells[0, 3].PutValue(dr["F8"]);//填写内容
cells[0, 3].SetStyle(styleTitle);//表头样式
cells.Merge(0, 4, 1, columns - 9);//合并单元格
cells[0, 4].PutValue("作业分数记录");//填写内容
cells[0, 4].SetStyle(styleTitle);//表头样式
#endregion
for (int n = 9; n < columns; n++)
{
p = "F" + (n + 1);
cells[1, n - 5].PutValue(dr[p]);//填写内容
cells[1, n - 5].SetStyle(styleTitle);//表头样式
cells.SetColumnWidth(n - 5, 15f);
}
}
//让各列自适应宽度
//sheet.AutoFitColumns();
#endregion
}
catch (Exception e)
{
error += "DataTableToExcel-Error:" + e.Message;
}
}
if (dtDetail != null && dtDetail.Rows.Count > 0)
{
Aspose.Cells.Style styleCell = wb.Styles[wb.Styles.Add()];//新增样式
//设置居中
//styleCell.HorizontalAlignment = TextAlignmentType.Center;
float result=0.00f;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
dr = dtDetail.Rows[i];
#region 学号 姓名 均分 总分
styleCell.HorizontalAlignment = TextAlignmentType.Left;
cells[i + 2, 0].PutValue(dr["F4"]);//填写内容
cells[i + 2, 0].SetStyle(styleCell);//表头样式
styleCell.HorizontalAlignment = TextAlignmentType.Center;
cells[i + 2, 1].PutValue(dr["F5"]);//填写内容
cells[i + 2, 1].SetStyle(styleCell);//表头样式
styleCell.HorizontalAlignment = TextAlignmentType.Right;
if (float.TryParse(dr["F9"].ToString(), out result))
{
cells[i + 2, 2].PutValue(result.ToString("0.00"));//填写内容
}
else
{
cells[i + 2, 2].PutValue(dr["F9"]);//填写内容
}
cells[i + 2, 2].SetStyle(styleCell);//表头样式
if (float.TryParse(dr["F8"].ToString(), out result))
{
cells[i + 2, 3].PutValue(result.ToString("0.00"));//填写内容
}
else
{
cells[i + 2, 3].PutValue(dr["F8"]);//填写内容
}
cells[i + 2, 3].SetStyle(styleCell);//表头样式
#endregion
for (int n = 9; n < columns; n++)
{
p = "F" + (n + 1);
if (float.TryParse(dr[p].ToString(), out result))
{
cells[i + 2, n - 5].PutValue(result.ToString("0.00"));//填写内容
}
else
{
cells[i + 2, n - 5].PutValue(dr[p]);//填写内容
}
cells[i + 2, n - 5].SetStyle(styleCell);//样式
}
}
}
string finalPath = MapPath("~" + "/UploadFiles/ExportClass/" + filename + ".xls");
wb.Save(finalPath);
//进行编码,便于中文名文件下载
string SiteRoot = "http://" + Request.Url.Authority.ToString() + "/UploadFiles/ExportClass/" + Uri.EscapeDataString(filename + ".xls"); //下载excel
ClientScript.RegisterStartupScript(this.GetType(), "", ",<script type='text/javascript'>window.open('" + SiteRoot + "');</script>");
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步