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>");
原标题:Aspose.Cells导出Excel(2)
关键词:ASP