ASPose导出excel简单操作
string file = FileDialogHelper.SaveExcel(string.Format("{0}.xls", excelName));
if (!string.IsNullOrEmpty(file) && dt.Rows.Count > 0)
{
try
{
string error = "";
DataTableToExcel(dt, file, out error);
if (!string.IsNullOrEmpty(error))
{
MessageDxUtil.ShowError(string.Format("导出Excel出现错误：{0}", error));
}
else
{
if (MessageDxUtil.ShowYesNoAndTips("导出成功，是否打开文件？") == System.Windows.Forms.DialogResult.Yes)
{
System.Diagnostics.Process.Start(file);
}
}
}
catch (Exception ex)
{
LogTextHelper.Error(ex);
MessageDxUtil.ShowError(ex.Message);
}
}
对ASPose的操作
error = "";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
//为单元格添加样式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
//style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
//为单元格添加样式
Aspose.Cells.Style styleHeader = wb.Styles[wb.Styles.Add()];
//设置居中
styleHeader.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
styleHeader.ForegroundColor = System.Drawing.Color.Gold;
styleHeader.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Medium; //应用边界线 左边界线
styleHeader.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Medium; //应用边界线 右边界线
styleHeader.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Medium; //应用边界线 上边界线
styleHeader.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Medium; //应用边界线 下边界线
styleHeader.Pattern = Aspose.Cells.BackgroundType.Solid;
styleHeader.Font.IsBold = true;
int rowIndex = 0;
//Aspose.Cells.Range range;
//Aspose.Cells.Cell cell;
//range = wb.Worksheets[0].Cells.CreateRange(0, 0, 2, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("评估项目");
////range.SetStyle(styleHeader);
//range = wb.Worksheets[0].Cells.CreateRange(0, 1, 2, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("评估分值");
//range = wb.Worksheets[0].Cells.CreateRange(0, 2, 1, 3);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("评分结果(不适合项目调整前)");
//range = wb.Worksheets[0].Cells.CreateRange(1, 2, 1, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("制度健全性");
//range = wb.Worksheets[0].Cells.CreateRange(1, 3, 1, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("遵循有效性");
//range = wb.Worksheets[0].Cells.CreateRange(1, 4, 1, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("合计");
//range = wb.Worksheets[0].Cells.CreateRange(0, 5, 2, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("评分结果(不适合项目调整后)");
//range = wb.Worksheets[0].Cells.CreateRange(0, 6, 2, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("权重");
//range = wb.Worksheets[0].Cells.CreateRange(0, 7, 2, 1);
//range.Merge();
//range.SetStyle(styleHeader);
//cell = range[0, 0];
//cell.PutValue("最终结果");
////Aspose.Cells.Range range1 = wb.Worksheets[0].Cells.CreateRange(0, 1, 2, 1);
////range1.Merge();
////Aspose.Cells.Range range2 = wb.Worksheets[0].Cells.CreateRange(0, 2, 1, 3);
////range2.Merge();
////Aspose.Cells.Range range3 = wb.Worksheets[0].Cells.CreateRange(0, 5, 2, 1);
////range3.Merge();
////Aspose.Cells.Range range4 = wb.Worksheets[0].Cells.CreateRange(0, 6, 2, 1);
////range4.Merge();
////Aspose.Cells.Range range5 = wb.Worksheets[0].Cells.CreateRange(0, 7, 2, 1);
////range5.Merge();
////wb.Worksheets[0].Cells[rowIndex, 0].PutValue("评估项目");
////wb.Worksheets[0].Cells[rowIndex, 0].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[rowIndex, 1].PutValue("评估分值");
////wb.Worksheets[0].Cells[rowIndex, 1].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[rowIndex, 2].PutValue("评分结果(不适合项目调整前)");
////wb.Worksheets[0].Cells[rowIndex, 2].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[1, 2].PutValue("制度健全性");
////wb.Worksheets[0].Cells[1, 2].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[1, 3].PutValue("遵循有效性");
////wb.Worksheets[0].Cells[1, 3].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[1, 4].PutValue("合计");
////wb.Worksheets[0].Cells[1, 4].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[rowIndex, 5].PutValue("评分结果(不适合项目调整后)");
////wb.Worksheets[0].Cells[rowIndex, 5].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[rowIndex, 6].PutValue("权重");
////wb.Worksheets[0].Cells[rowIndex, 6].SetStyle(styleHeader);
////wb.Worksheets[0].Cells[rowIndex, 7].PutValue("最终结果");
////wb.Worksheets[0].Cells[rowIndex, 7].SetStyle(styleHeader);
//styleHeader.ForegroundColor = System.Drawing.Color.LightCoral;
//wb.Worksheets[0].Cells[2, 0].PutValue("(1)");
//wb.Worksheets[0].Cells[2, 0].SetStyle(styleHeader);
//wb.Worksheets[0].Cells[2, 1].PutValue("(2)");
//wb.Worksheets[0].Cells[2, 1].SetStyle(styleHeader);
//wb.Worksheets[0].Cells[2, 2].PutValue("(3)");
//wb.Worksheets[0].Cells[2, 2].SetStyle(styleHeader);
//wb.Worksheets[0].Cells[2, 3].PutValue("(4)");
//wb.Worksheets[0].Cells[2, 3].SetStyle(styleHeader);
//wb.Worksheets[0].Cells[2, 4].PutValue("（5）=（3）+（4）");
//wb.Worksheets[0].Cells[2, 4].SetStyle(styleHeader);
//wb.Worksheets[0].Cells[2, 5].PutValue("(6)");
//wb.Worksheets[0].Cells[2, 5].SetStyle(styleHeader);
//wb.Worksheets[0].Cells[2, 6].PutValue("(7)");
//wb.Worksheets[0].Cells[2, 6].SetStyle(styleHeader);
//wb.Worksheets[0].Cells[2, 7].PutValue("（8）=（6）×（7）");
//wb.Worksheets[0].Cells[2, 7].SetStyle(styleHeader);
for (int i = 0; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++;
//rowIndex = 3;
foreach (DataRow row in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());
wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++;
}
string ExcelHeader = "PROJECTNAME,STANDARDSCORE,SYSINTEGRITY,FOLLOWEFFECTIVENESS,TOTAL,SCORERESULT,WEIGHT,ENDRESULT";
//foreach (DataRow row in datatable.Rows)
//{
// int i = 0;
// foreach (string col in ExcelHeader.Split(','))
// {
// wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[col].ToString());
// wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
// i++;
// }
// rowIndex++;
//}
//for (int k = 0; k < datatable.Columns.Count; k++)
//{
// wb.Worksheets[0].AutoFitColumn(k, 0, 150);
//}
wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);
wb.Save(filepath);
return true;
}
catch (Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}
