使用Microsoft.Office.Interop.Excel.Application xlApp 生成Excel
object filePath = @"C:\" + DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.ToLongTimeString().Replace(":", "") + ".xlsx"; //文件保存路径 public void GeneExcel(DataSet ds) { Excel.ApplicationClass xlApp = new Excel.ApplicationClass(); Workbooks workbooks = xlApp.Workbooks; Workbook workBook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet workSheet = (Worksheet)workBook.Worksheets[1];//取得sheet1 //打开一个WorkBook //Workbooks workbooks = xlApp.Workbooks; //Workbook workBook = xlApp.Workbooks.Open(filePath.ToString(), // Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ////得到WorkSheet对象 //Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); try { workSheet.Name = "表单一"; workSheet.Cells[1, 1] = "单元名称"; workSheet.Cells[1, 2] = "指标及其描述、特征值和权重"; workSheet.Cells[1, 23] = "属性"; workSheet.Cells[2, 2] = "D"; workSheet.Cells[2, 5] = "R"; workSheet.Cells[2, 8] = "A"; workSheet.Cells[2, 11] = "S"; workSheet.Cells[2, 14] = "T"; workSheet.Cells[2, 17] = "I"; workSheet.Cells[2, 20] = "C"; workSheet.Cells[2, 23] = "结果"; workSheet.Cells[2, 24] = "等级"; workSheet.Cells[3, 2] = "描述"; workSheet.Cells[3, 3] = "特征值"; workSheet.Cells[3, 4] = "权重"; workSheet.Cells[3, 5] = "描述"; workSheet.Cells[3, 6] = "特征值"; workSheet.Cells[3, 7] = "权重"; workSheet.Cells[3, 8] = "描述"; workSheet.Cells[3, 9] = "特征值"; workSheet.Cells[3, 10] = "权重"; workSheet.Cells[3, 11] = "描述"; workSheet.Cells[3, 12] = "特征值"; workSheet.Cells[3, 13] = "权重"; workSheet.Cells[3, 14] = "描述"; workSheet.Cells[3, 15] = "特征值"; workSheet.Cells[3, 16] = "权重"; workSheet.Cells[3, 17] = "描述"; workSheet.Cells[3, 18] = "特征值"; workSheet.Cells[3, 19] = "权重"; workSheet.Cells[3, 20] = "描述"; workSheet.Cells[3, 21] = "特征值"; workSheet.Cells[3, 22] = "权重"; workSheet.get_Range("A1", "A3").MergeCells = true; //合并单元格 Range rg1 = workSheet.get_Range(workSheet.Cells[1, 2], workSheet.Cells[1, 22]); //range.ClearContents(); //先把Range内容清除，合并才不会出错 rg1.MergeCells = true; rg1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; rg1.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; workSheet.get_Range(workSheet.Cells[1, 23], workSheet.Cells[1, 24]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 2], workSheet.Cells[2, 4]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 5], workSheet.Cells[2, 7]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 8], workSheet.Cells[2, 10]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 11], workSheet.Cells[2, 13]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 14], workSheet.Cells[2, 16]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 17], workSheet.Cells[2, 19]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 20], workSheet.Cells[2, 22]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 23], workSheet.Cells[3, 23]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 24], workSheet.Cells[3, 24]).MergeCells = true; /*往Excel格式表格中写入数据*/ int waterdataRows = ds.Tables[0].Rows.Count; int waterdataCols = ds.Tables[0].Columns.Count; for (int i = 1; i <= waterdataRows; i++) { workSheet.Cells[i + 3, 1] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(0); for (int j = 1; j <= 7; j++) { workSheet.Cells[i + 3, 3 * j] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(j); } workSheet.Cells[i + 3, 23] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(1); workSheet.Cells[i + 3, 24] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(3); } //插入空行 //Range rgInsert = (Excel.Range)workSheet.Rows[6, Type.Missing]; //rgInsert.Insert(Excel.XlDirection.xlDown, Type.Missing); //rgInsert.Copy(); //复制 //rgInsert.Delete(); //删除 //range1.Copy(rgStyle); //自动填充(如星期一到星期五） //Excel.Range rng = workSheet.get_Range("B4", Type.Missing); //rng.Value2 = "星期一 "; //rng.AutoFill(workSheet.get_Range("B4", "B9"), // Excel.XlAutoFillType.xlFillWeekdays); Excel.Range rng = workSheet.get_Range("C4", Type.Missing); rng.Value2 = "一月"; rng.AutoFill(workSheet.get_Range("C4", "C9"), Excel.XlAutoFillType.xlFillMonths); //rng.Value2 = "1"; //rng.AutoFill(workSheet.get_Range("D4", "D9"), // Excel.XlAutoFillType.xlFillSeries); //Excel.Range rangePic = workSheet.get_Range("A10", "A11"); //rangePic.Select(); //workSheet.Shapes.AddPicture(@"C:\6.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, //Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(rangePic.Left), Convert.ToSingle(rangePic.Top), 10, 10); #region 应用样式 Excel.Range rgStyle = workSheet.get_Range("O10", Type.Missing); rgStyle.Formula = "=SUM(O4:O9)"; rgStyle.Calculate(); //rgStyle.Interior.ColorIndex = ColorIndex.红色;//15背景色 //rgStyle.Font.Color = ColorIndex.白色; //无效 //rgStyle.Font.Bold = true; //rgStyle.Font.Size = 9; Excel.Style style; //style = workBook.Styles["NewStyle"]; style = workBook.Styles.Add("NewStyle", Type.Missing); style.Font.Name = "Verdana"; style.Font.Size = 12; style.Font.Color = 255;//前景色 style.Interior.Color = (200 << 16) | (200 << 8) | 200; //背景色 //rgStyle.Interior.ColorIndex = ColorIndex.红色;//15背景色 style.Interior.Pattern = Excel.XlPattern.xlPatternSolid; rgStyle.Value2 = "'Style Test"; rgStyle.Style = "NewStyle"; rgStyle.Columns.AutoFit(); #endregion #region 行高、列宽 //Range rgHeight = workSheet.get_Range("C9", Type.Missing); //rgHeight.Value = "sdfsdfsdf"; //rgHeight.RowHeight = 100; //range.EntireColumn.AutoFit(); Range rgWidth = workSheet.get_Range("C9", Type.Missing); rgWidth.Value = "sdfsfscvfffffffffffdf"; //rgWidth.ColumnWidth = 20; rgWidth.EntireColumn.AutoFit(); //自动根据内容设定宽度 #endregion //sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden; //隐藏工作表 #region 保存Excel workBook.Saved = true; //workBook.Save(); //workBook.SaveCopyAs(filePath); workBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //workBook.SaveAs(filePath, Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //Excel.XlFileFormat.xlXMLSpreadsheet 生成多种格式文件 SaveAsFileFormat枚举 #endregion #region 释放Excel资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(rg1); //释放Range System.Runtime.InteropServices.Marshal.ReleaseComObject(rgStyle); System.Runtime.InteropServices.Marshal.ReleaseComObject(rgWidth); if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (workbooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; } if (xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } workBook.Close(false, null, null); xlApp.Workbooks.Close(); xlApp.Quit(); //自动打开Excel，抛异常 GC.WaitForPendingFinalizers(); GC.Collect(); #endregion } catch (Exception ex) { } finally { } System.Diagnostics.Process.Start(filePath.ToString()); }
转 http://www.cnblogs.com/gossip/archive/2011/10/22/2221465.html
