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