1 range.NumberFormatLocal = "@";     //设置单元格格式为文本 2 ange.get_Range("B1","B"+dataGridView2.RowCount+1).NumberFormat = "yyyy-m-d hh:mm:ss"; //时间格式的设置 3 range = (Range)worksheet.get_Range("A1", "E1");     //获取Excel多个单元格区域：本例做为Excel表头 4 range.Merge(0);     //单元格合并动作 5 worksheet.Cells[1, 1] = "Excel单元格赋值";     //Excel单元格赋值 6 range.Font.Size = 15;     //设置字体大小 7 range.Font.Underline=true;     //设置字体是否有下划线 8 range.Font.Name="黑体";     设置字体的种类 9 range.HorizontalAlignment=XlHAlign.xlHAlignCenter;     //设置字体在单元格内的对其方式10 range.ColumnWidth=15;     //设置单元格的宽度11 range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();     //设置单元格的背景色12 range.Borders.LineStyle=1;     //设置单元格边框的粗细13 range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());     //给单元格加边框14 range.EntireColumn.AutoFit();     //自动调整列宽15 Range.HorizontalAlignment= xlCenter;     // 文本水平居中方式16 Range.VerticalAlignment= xlCenter     //文本垂直居中方式17 Range.WrapText=true;     //文本自动换行18 Range.Interior.ColorIndex=39;     //填充颜色为淡紫色19 Range.Font.Color=clBlue;     //字体颜色20 xlsApp.DisplayAlerts=false;     //保存Excel的时候，不弹出是否保存的窗口直接进行保存21 workbook.SaveCopyAs(temp);///填入完信息之后另存到路径及文件名字

  1 using System;  2 using System.Collections.Generic;  3 using System.Text;  4 using System.Data.SqlClient;  5 using Excel;  6 using System.Reflection;  7 using System.Data;  8 using System.Data.OleDb;  9 namespace RecruitmentReport 10 { 11     classdoExcel 12     { 13         enumColumnName {A1=1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,V1,W1,X1,Y1,Z1} 14         /// 15         /// 导出到Execl 16         /// 17         /// 数据集 18         /// 工作部名称 19         /// 保存路径 20         /// 标题名 21         publicvoid doExport(DataSet dt, string strSheetName, string pathloading, string title) 22         { 23   24             int columnIndex = dt.Tables[0].Columns.Count; 25             string cName =((ColumnName)columnIndex).ToString(); 26             Excel.Application excel = new Excel.Application();  //Execl的操作类 27             Excel.Workbook bookDest =(Excel.Workbook)excel.Workbooks.Add(Missing.Value); 28             Excel.Worksheet sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;//给工作薄添加一个Sheet   29             sheetDest.Name = strSheetName; 30             for (int i = bookDest.Worksheets.Count; i >1; i--) 31             { 32                 Worksheet wt = (Worksheet)bookDest.Worksheets[i]; 33                 if (wt.Name != strSheetName) 34                 { 35                     wt.Delete(); 36                 } 37             } 38             int rowIndex = 2; 39             int colIndex = 0; 40             Range rngRow = (Excel.Range)sheetDest.Columns[1, Type.Missing]; 41             rngRow.UseStandardWidth = 70; 42             Range rngA = (Range)sheetDest.Columns["A", Type.Missing];//设置单元格格式 43             rngA.NumberFormatLocal = "@";//字符型格式 44             Range rngJ = (Range)sheetDest.Columns["J", Type.Missing]; 45             rngJ.NumberFormatLocal = "@"; 46             Range rngQ = (Range)sheetDest.Columns["Q", Type.Missing]; 47             rngQ.NumberFormatLocal = "@"; 48             Range rngE = (Range)sheetDest.Columns["E", Type.Missing]; 49             rngE.NumberFormatLocal = @"yyyy-mm-dd";//日期型格式 50             sheetDest.get_Range("A1", cName).Merge(sheetDest.get_Range("A1", cName).MergeCells);//合并单元格 51             excel.Application.Workbooks.Add(true); 52             try 53             { 54                 Range rngfirst = (Excel.Range)sheetDest.Cells[1, 1]; 55                 sheetDest.Cells[1, 1] = title + System.DateTime.Now.Month.ToString().PadLeft(2, ‘0‘) + System.DateTime.Now.Day.ToString().PadLeft(2, ‘0‘) + System.DateTime.Now.Year.ToString(); 56                 rngfirst.Font.Size = 14; 57                 rngfirst.Font.Name = "Calibri";//设置单元格字体 58                 rngfirst.RowHeight = 18; 59                 rngfirst.HorizontalAlignment = XlHAlign.xlHAlignCenter; 60                 rngfirst.Font.Bold = true; 61                 rngfirst.Borders.LineStyle = XlLineStyle.xlContinuous;//设置单元格边框 62                 foreach (DataColumn col in dt.Tables[0].Columns) 63                 { 64                  65                         colIndex++; 66                         Range rng = (Excel.Range)sheetDest.Cells[2, colIndex]; 67                        68                             sheetDest.Cells[2, colIndex] = col.ColumnName;//Execl中的第一列把DataTable的列名先导进去 69                             rng.Font.Name = "Calibri"; 70                             rng.Font.Size = 11; 71                             rng.Font.Bold = true; 72                             rng.Font.Color = ConsoleColor.Blue; 73                             rng.HorizontalAlignment = XlHAlign.xlHAlignCenter; 74                             rng.RowHeight = 15; 75                             rng.Borders.LineStyle = XlLineStyle.xlContinuous; 76                             rng.ColumnWidth = 15.5; 77                      //   sheetDest.Range[1, colIndex].Font.Bold = false; 78   79                 } 80                 //导入数据行 81  82                 foreach (DataRow row in dt.Tables[0].Rows) 83                 { 84                     rowIndex++; 85                     colIndex = 0; 86   87                     foreach (DataColumn col in dt.Tables[0].Columns) 88                     { 89                                colIndex++; 90                                 sheetDest.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); 91                                 Range rng01 = (Excel.Range)sheetDest.Cells[rowIndex, colIndex]; 92                                 rng01.HorizontalAlignment = XlHAlign.xlHAlignCenter; 93                                 rng01.Borders.LineStyle = XlLineStyle.xlContinuous; 94                                 rng01.RowHeight = 15; 95                                 rng01.Font.Name = "Calibri"; 96                                 rng01.Font.Size = 11; 97  98                         }   99                 }100             }101             catch  { thrownewException(); }102             bookDest.Saved = true;103             bookDest.SaveCopyAs(pathloading);//保存104             excel.Quit();105             excel = null;106             GC.Collect();//垃圾回收  107         }108    }109 }110