C#导出excel文件

首先你需要添加引用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;

       }