#region 初始化定义
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook = null;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
//定义Range对象,此对象代表单元格区域
Microsoft.Office.Interop.Excel.Range excelRange;
#endregion
try
{
#region 创建实例
//初始化 Application 对象 excelApp
excelApp = new Microsoft.Office.Interop.Excel.Application();
if (excelApp == null)
{
return false;
}
excelApp.DisplayAlerts = false;
//在工作薄的第一个工作表上创建任务列表
workBook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
// 命名工作表的名称为数据块名称
workSheet.Name = dataBlock.NAME;
//设置全部列宽
workSheet.Columns.ColumnWidth = 15;
workSheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
workSheet.Cells.NumberFormat = "@";
#endregion
#region 设置数据块基本信息标题
//合并单元格
excelRange = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, 6]);
excelRange.Merge(0);
//单元格赋值
workSheet.Cells[1, 1] = dataBlock.NAME + "数据块";
//单元格设置
excelRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置水平居中
excelRange.RowHeight = 33; //设置单元格高度
excelRange.Font.Bold = true; //设置是否加粗
excelRange.Font.Size = 16; //设置字体大小
excelRange.Font.Name = "黑体"; //设置字体类型
excelRange.Cells.Interior.Color = Color.LightGray.ToArgb(); //设置背景色
#endregion
#region 设置数据块基本信息
workSheet.Cells[2, 1] = "版本：";
workSheet.get_Range(workSheet.Cells[2, 1], workSheet.Cells[2, 1]).Font.Bold = true;
workSheet.Cells[2, 2] = dataBlock.REVISION_NUMBER.ToString("0.0");
workSheet.Cells[2, 3] = "创建者：";
workSheet.get_Range(workSheet.Cells[2, 3], workSheet.Cells[2, 3]).Font.Bold = true;
workSheet.Cells[2, 4] = SingletonProvider<UsersBLL>.Instance.GetFullNameByUserID(dataBlock.CREATOR_ID);
workSheet.Cells[2, 5] = "创建日期：";
workSheet.get_Range(workSheet.Cells[2, 5], workSheet.Cells[2, 5]).Font.Bold = true;
workSheet.Cells[2, 6] = dataBlock.CREATE_DATE.ToString();
workSheet.Cells[3, 1] = "专业：";
workSheet.get_Range(workSheet.Cells[3, 1], workSheet.Cells[3, 1]).Font.Bold = true;
workSheet.Cells[3, 2] = SingletonProvider<CommonBLL>.Instance.GetSpecialtyName(dataBlock.SPECIALTY_ID);
workSheet.Cells[3, 3] = "部件：";
workSheet.get_Range(workSheet.Cells[3, 3], workSheet.Cells[3, 3]).Font.Bold = true;
workSheet.Cells[3, 4] = SingletonProvider<CommonBLL>.Instance.GetPartName(dataBlock.PART_ID);
workSheet.Cells[3, 5] = "阶段：";
workSheet.get_Range(workSheet.Cells[3, 5], workSheet.Cells[3, 5]).Font.Bold = true;
workSheet.Cells[3, 6] = SingletonProvider<CommonBLL>.Instance.GetStageName(dataBlock.STAGE_ID);
workSheet.Cells[4, 1] = "状态：";
workSheet.get_Range(workSheet.Cells[4, 1], workSheet.Cells[4, 1]).Font.Bold = true;
workSheet.Cells[4, 2] = SingletonProvider<CommonBLL>.Instance.GetStateName(dataBlock.STATE_ID);
// [10/22/2010 Administrator] 添加数据定义工具的输出
workSheet.Cells[4, 3] = "定义工具：";
workSheet.get_Range(workSheet.Cells[4, 3], workSheet.Cells[4, 3]).Font.Bold = true;
workSheet.Cells[4, 4] = SingletonProvider<CommonBLL>.Instance.GetToolsTypeName(dataBlock.OUTPUT_TYPE_ID) ;
workSheet.Cells[4, 5] = "描述：";
workSheet.get_Range(workSheet.Cells[4, 5], workSheet.Cells[4, 5]).Font.Bold = true;
workSheet.Cells[4, 6] = dataBlock.DESCRIPTION;
excelRange = workSheet.get_Range(workSheet.Cells[4, 6], workSheet.Cells[4, 8]);
excelRange.Merge(0);
excelRange.EntireRow.AutoFit();
excelRange.WrapText = true; //文本自动换行
//给每个单元格加边框
for (int i = 1; i < 5; i++)
{
for (int j = 1; j < 7; j++)
{
excelRange = workSheet.get_Range(workSheet.Cells[i, j], workSheet.Cells[i, j]);
excelRange.Borders.LineStyle = 1;
excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Color.Black.ToArgb());
}
}
//设置边框
excelRange = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[4, 6]);
excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Color.Black.ToArgb()); //单元格加边框
#endregion
#region 设置数据项信息表头
workSheet.Cells[6, 1] = "数据项名";
workSheet.Cells[6, 2] = "父节点";
workSheet.Cells[6, 3] = "节点";
workSheet.Cells[6, 4] = "数据格式";
workSheet.Cells[6, 5] = "数据类型";
workSheet.Cells[6, 6] = "单值";
workSheet.Cells[6, 7] = "单位";
excelRange = workSheet.get_Range(workSheet.Cells[6, 1], workSheet.Cells[6, 7]);
excelRange.RowHeight = 25; //行高
excelRange.Font.Name = "黑体"; //字体
excelRange.Font.Bold = true; //加粗
excelRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
excelRange.Cells.Interior.Color = Color.LightGray.ToArgb(); //背景色
#endregion
#region 设置数据项信息
if (dtItem != null && dtItem.Rows.Count > 0)
{
int row = dtItem.Rows.Count;
int cell = dtItem.Columns.Count;
for (int i = 0; i < row; i++)
{
workSheet.Cells[7 + i, 1] = dtItem.Rows[i]["NAME"].ToString();
workSheet.Cells[7 + i, 2] = dtItem.Rows[i]["PARENT_NAME"].ToString() == "" ? dataBlock.NAME : dtItem.Rows[i]["PARENT_NAME"].ToString();
workSheet.Cells[7 + i, 3] = dtItem.Rows[i]["NODE_NAME"].ToString();
workSheet.Cells[7 + i, 4] = dtItem.Rows[i]["NODE_NAME"].ToString() == "中间节点" ? "" : dtItem.Rows[i]["FORMAT_TYPE_NAME"].ToString();
workSheet.Cells[7 + i, 5] = dtItem.Rows[i]["NODE_NAME"].ToString() == "中间节点" ? "" : dtItem.Rows[i]["DATA_TYPE_NAME"].ToString();
workSheet.Cells[7 + i, 6] = dtItem.Rows[i]["SINGLE_NAME"].ToString();
workSheet.Cells[7 + i, 7] = dtItem.Rows[i]["NODE_NAME"].ToString() == "中间节点" ? "" : dtItem.Rows[i]["UNIT_NAME"].ToString();
}
//给每个单元格加边框
for (int i = 6; i < 7 + row; i++)
{
for (int j = 1; j < 8; j++)
{
excelRange = workSheet.get_Range(workSheet.Cells[i, j], workSheet.Cells[i, j]);
excelRange.Borders.LineStyle = 1;
excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Color.Black.ToArgb());
}
}
//设置边框
excelRange = workSheet.get_Range(workSheet.Cells[6, 1], workSheet.Cells[6 + row, 7]);
excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
Color.Black.ToArgb());
}
#endregion
#region 保存
workBook.Saved = true;
workBook.SaveCopyAs(excelFileName);
#endregion
return true;
}
catch
{
return false;
}
finally
{
#region 清空对象并杀掉进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
killAllExcelProcess();
#endregion
}
/// <summary>
/// 杀掉EXCEL进程
/// </summary>
protected void killAllExcelProcess()
{
System.Diagnostics.Process[] myPs;
myPs = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process p in myPs)
{
if (p.Id != 0)
{
string myS = "EXCEL.EXE" + p.ProcessName + " ID:" + p.Id.ToString();
try
{
if (p.Modules != null)
if (p.Modules.Count > 0)
{
System.Diagnostics.ProcessModule pm = p.Modules[0];
myS += "\n Modules[0].FileName:" + pm.FileName;
myS += "\n Modules[0].ModuleName:" + pm.ModuleName;
myS += "\n Modules[0].FileVersionInfo:\n" + pm.FileVersionInfo.ToString();
if (pm.ModuleName.ToLower() == "excel.exe")
p.Kill();
}
}
catch
{ }
finally
{
}
}
}
}
