/// <summary>
/// 导出到Execl
/// </summary>
public void ExportDataToExcel(DataTable argDt ,string argSaveAddress)
{
int columnIndex = argDt.Columns.Count;
string cName = ((ColumnName)columnIndex).ToString();
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //Execl的操作类
Microsoft.Office.Interop.Excel.Workbook bookDest = (Microsoft.Office.Interop.Excel.Workbook)excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet sheetDest = bookDest.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;//给工作薄添加一个Sheet
//去除合并单元格提醒
excel.Application.DisplayAlerts = false;
#region 合并单元格
Microsoft.Office.Interop.Excel.Range item1 = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 2]);
item1.Value2 = "Traveller Name";
item1.Merge(0);
Microsoft.Office.Interop.Excel.Range item3 = excel.get_Range(excel.Cells[1, 5], excel.Cells[1, 6]);
item3.Value2 = "Traveller Details";
item3.Merge(0);
Microsoft.Office.Interop.Excel.Range item4 = excel.get_Range(excel.Cells[1, 7], excel.Cells[1, 12]);
item4.Value2 = "Flight Data";
item4.Merge(0);
Microsoft.Office.Interop.Excel.Range item5 = excel.get_Range(excel.Cells[1, 13], excel.Cells[1, 20]);
item5.Value2 = "Rail Data";
item5.Merge(0);
Microsoft.Office.Interop.Excel.Range item6 = excel.get_Range(excel.Cells[1, 21], excel.Cells[1, 27]);
item6.Value2 = "Hotel Data";
item6.Merge(0);
// //去除合并单元格提醒
excel.Application.DisplayAlerts = true;
#endregion
int rowIndex = 2;
int colIndex = 0;
//Microsoft.Office.Interop.Excel.Range rngRow = (Microsoft.Office.Interop.Excel.Range)sheetDest.Columns[1, Type.Missing];
//rngRow.UseStandardWidth = 70;
Microsoft.Office.Interop.Excel.Range rngA = (Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["D", Type.Missing];//设置单元格格式
rngA.NumberFormatLocal = "#";//字符型格式
Microsoft.Office.Interop.Excel.Range rngF = (Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["F", Type.Missing];//设置单元格格式
rngF.NumberFormatLocal = "#";//字符型格式
foreach (DataColumn col in argDt.Columns)
{
colIndex++;
//Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)sheetDest.Cells[2, colIndex];
sheetDest.Cells[2, colIndex] = col.ColumnName;//Execl中的第一列把DataTable的列名先导进去
}
//导入数据行
foreach (DataRow row in argDt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in argDt.Columns)
{
colIndex++;
sheetDest.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
//设置单元格格式
excel.get_Range(excel.Cells[1, 1], excel.Cells[argDt.Rows.Count + 2, 30]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 30]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[argDt.Rows.Count + 2, 30]).Font.Name = "Arial Unicode MS";
//为第一行标题设下划线
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 30]).Font.Underline = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 30]).Font.Size = 14;
//设置第二行格式
//excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 30]).Font.Name = "Calibri";
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 30]).Font.Size = 11;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 30]).Font.Color = ConsoleColor.Red;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 30]).RowHeight = 50;
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 30]).ColumnWidth = 20;
//excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 30]).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble;//设置单元格边框
#region 设置背景色
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["C", Type.Missing]).Interior.Color = Color.FromArgb(238,236, 225);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["D", Type.Missing]).Interior.Color = Color.FromArgb(221,217, 196);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["E", Type.Missing]).Interior.Color = Color.FromArgb(253,233, 217);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["F", Type.Missing]).Interior.Color = Color.FromArgb(253,233, 217);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["G", Type.Missing]).Interior.Color = Color.FromArgb(228, 223, 236);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["H", Type.Missing]).Interior.Color = Color.FromArgb(228, 223, 236);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["I", Type.Missing]).Interior.Color = Color.FromArgb(228, 223, 236);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["J", Type.Missing]).Interior.Color = Color.FromArgb(228, 223, 236);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["K", Type.Missing]).Interior.Color = Color.FromArgb(228, 223, 236);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["L", Type.Missing]).Interior.Color = Color.FromArgb(228, 223, 236);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["M", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["N", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["O", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["P", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["Q", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["R", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["S", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["T", Type.Missing]).Interior.Color = Color.FromArgb(235, 241, 222);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["U", Type.Missing]).Interior.Color = Color.FromArgb(242, 220, 219);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["V", Type.Missing]).Interior.Color = Color.FromArgb(242, 220, 219);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["W", Type.Missing]).Interior.Color = Color.FromArgb(242, 220, 219);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["X", Type.Missing]).Interior.Color = Color.FromArgb(242, 220, 219);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["Y", Type.Missing]).Interior.Color = Color.FromArgb(242, 220, 219);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["Z", Type.Missing]).Interior.Color = Color.FromArgb(242, 220, 219);
((Microsoft.Office.Interop.Excel.Range)sheetDest.Columns["AA", Type.Missing]).Interior.Color = Color.FromArgb(242, 220, 219);
#endregion
//保存文件
bookDest.Saved = true;
bookDest.SaveCopyAs(argSaveAddress);
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}