首先你需要添加引用Microsoft Excel 11.0 Object Library
添加方法:选择项目->引用->右击“添加引用”->选择COM 找到上面组件—>点击“确定”。
其次添加 Microsoft.Office.Interop.Excel 引用
添加方法:选择项目->引用->右击“添加引用”->浏览 C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
/// <summary>
/// 导出到EXCEL
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsExportToExcel_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel(*.xls)|*.xls|All Files(*.*)|*.*";
dialog.FileName = "CallHistory.xls";
if (dialog.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application xlApp = newMicrosoft.Office.Interop.Excel.Application();
object missing =System.Reflection.Missing.Value;
try
{
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.WorkbooksxlBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlBook =xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.WorksheetxlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = null;
//****** 抬头*********************************************************************************
range =xlSheet.get_Range("A1", "G1");
range.Merge(Missing.Value); // 合并单元格
range.Columns.AutoFit(); // 设置列宽为自动适应
// 设置单元格左边框加粗
range.Borders[XlBordersIndex.xlEdgeLeft].Weight =XlBorderWeight.xlThick;
// 设置单元格右边框加粗
range.Borders[XlBordersIndex.xlEdgeRight].Weight =XlBorderWeight.xlThick;
range.HorizontalAlignment =XlHAlign.xlHAlignCenter;// 设置单元格水平居中
range.Value2 = "G1 通话记录清单";
range.Font.Size = 18; // 设置字体大小
range.Font.ColorIndex =5; // 设置字体颜色
//range.Interior.ColorIndex= 6; // 设置单元格背景色
range.RowHeight = 25; // 设置行高
range.ColumnWidth =20; // 设置列宽
xlSheet.Cells[2, 1] ="时间";
xlSheet.Cells[2, 2] ="电话号码";
xlSheet.Cells[2, 3] ="联系人";
xlSheet.Cells[2, 4] ="类型";
xlSheet.Cells[2, 5] ="通话时长";
xlSheet.Cells[2, 6] ="分组";
xlSheet.Cells[2, 7] ="归属地";
int rowIndex = 3;//这个用来标记数据有多少行位置
//-----------------------设置单元格--------------------------------------------------------------------------------
range = xlSheet.get_Range(xlSheet.Cells[3,7], xlSheet.Cells[rowIndex + this.lvwList.Items.Count, 7]);//归属地
range.HorizontalAlignment =XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal ="@";//文本格式
range.ColumnWidth =30;
range =xlSheet.get_Range(xlSheet.Cells[3, 6], xlSheet.Cells[rowIndex +this.lvwList.Items.Count, 6]);//分组
range.HorizontalAlignment =XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal ="@";//文本格式
range.ColumnWidth = 12;
range =xlSheet.get_Range(xlSheet.Cells[3, 5], xlSheet.Cells[rowIndex +this.lvwList.Items.Count, 5]);//通话时长
range.HorizontalAlignment =XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal ="@";//文本格式
range.ColumnWidth = 12;
range =xlSheet.get_Range(xlSheet.Cells[3, 4], xlSheet.Cells[rowIndex + this.lvwList.Items.Count,4]); //类型
range.HorizontalAlignment =XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.ColumnWidth = 12;
range =xlSheet.get_Range(xlSheet.Cells[3, 3], xlSheet.Cells[rowIndex + this.lvwList.Items.Count,3]); //联系人
range.HorizontalAlignment =XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.ColumnWidth = 18;
range =xlSheet.get_Range(xlSheet.Cells[3, 2], xlSheet.Cells[rowIndex + this.lvwList.Items.Count,2]); //电话号码
range.HorizontalAlignment =XlHAlign.xlHAlignLeft;// 设置单元格水平居左
range.NumberFormatLocal ="@";//文本格式
range.ColumnWidth = 18;
range = xlSheet.get_Range(xlSheet.Cells[3,1], xlSheet.Cells[rowIndex + this.lvwList.Items.Count, 1]); //时间
range.NumberFormatLocal ="yyyy-MM-dd HH:mm";//日期格式
range.ColumnWidth = 18;
//-----------------------设置单元格--------------------------------------------------------------------------------
//标题栏
range =xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[2, 7]);
range.Interior.ColorIndex =45;//设置标题背景色为浅橙色
range.Font.Bold = true;//标题字体加粗
foreach (ListViewItemobjItem in this.lvwList.Items)
{
xlSheet.Cells[rowIndex,1] = Convert.ToDateTime(objItem.Text);
xlSheet.Cells[rowIndex, 2]= objItem.SubItems[1].Text;
xlSheet.Cells[rowIndex,3] = objItem.SubItems[2].Text;
xlSheet.Cells[rowIndex,4] = objItem.SubItems[3].Text;
xlSheet.Cells[rowIndex, 5] =objItem.SubItems[4].Text;
xlSheet.Cells[rowIndex,6] = objItem.SubItems[5].Text;
xlSheet.Cells[rowIndex,7] = objItem.SubItems[6].Text;
rowIndex += 1;
}
//数据区域
range =xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowIndex, 7]);
range.Borders.LineStyle =1;
range.Font.Size = 10;
range=xlSheet.get_Range(xlSheet.Cells[rowIndex,1],xlSheet.Cells[rowIndex,7]);
range.Merge(Missing.Value); // 合并单元格
//range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;
// 设置单元格右边框加粗
//range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;
range.RowHeight = 20;
range.Value2 = "汇出时间: "+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
range.HorizontalAlignment =XlHAlign.xlHAlignRight;// 设置单元格水平居中
//***** 格式设定******************************************************************************
if (xlSheet != null)
{
xlSheet.SaveAs(dialog.FileName, missing, missing, missing, missing,missing, missing, missing, missing, missing);
xlApp.Visible = true;
}
}
catch (Exception)
{
xlApp.Quit();
throw;
}
}
KillAllExcel();
}
///操作完成结束进程
private static void KillAllExcel()
{
List<Process>excelProcess = GetExcelProcesses();
for (int i = 0; i < excelProcess.Count; i++)
{
excelProcess[i].Kill();
}
}
/// <summary>
/// 获得所有的Excel进程
/// </summary>
/// <returns>所有的Excel进程</returns>
private static List<Process> GetExcelProcesses()
{
Process[] processes = Process.GetProcesses();
List<Process> excelProcesses = new List<Process>();
for (int i = 0; i < processes.Length; i++)
{
if(processes[i].ProcessName.ToUpper() == "EXCEL")
excelProcesses.Add(processes[i]);
}
return excelProcesses;
}