#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; #endregiontry
{ #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); #endregionreturn 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 { } } } }