C#中ListView数据导出excel表

 

• from C#.net2.0--CDRapp--appMain.cs   
•   
•   
•         private void cdrToExcel()   
•         {   
•             SaveFileDialog dialog = new SaveFileDialog();   
•             dialog.Filter = "Excel(*.xls)|*.xls";   
•             dialog.FileName = "CDR.xls";   
•             if (dialog.ShowDialog() == DialogResult.OK)   
•             {   
•                 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();   
•                 object missing = System.Reflection.Missing.Value;   
•                 try  
•                 {   
•                     if (xlApp == null)   
•                     {   
•                         MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");   
•                         return;   
•                     }   
•   
•                     Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;   
•                     Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);   
•                     Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];   
•                     Microsoft.Office.Interop.Excel.Range range = null;   
•                     //抬头   
•   
•                     range = xlSheet.get_Range("A1", "P1");   
•                     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 = "CDR";   
•                     range.Font.Size = 18;                        // 设置字体大小   
•                     //range.Font.ColorIndex = 5;                  // 设置字体颜色                       
•                     //range.Interior.ColorIndex = 6;  // 设置单元格背景色   
•                     //range.RowHeight = 25;           // 设置行高   
•                     //range.ColumnWidth = 20;         // 设置列宽   
•   
•                     xlSheet.Cells[2, 1] = "Cdr_Id";   
•                     xlSheet.Cells[2, 2] = "Create_time";   
•                     xlSheet.Cells[2, 3] = "Setup_time";   
•                     xlSheet.Cells[2, 4] = "Alerting_time";   
•                     xlSheet.Cells[2, 5] = "Connect_timeconnect_time";   
•                     xlSheet.Cells[2, 6] = "During";   
•                     xlSheet.Cells[2, 7] = "Caller";   
•                     xlSheet.Cells[2, 8] = "Called";   
•                     xlSheet.Cells[2, 9] = "Caller_ip";   
•                     xlSheet.Cells[2, 10] = "Called_ip";   
•                     xlSheet.Cells[2, 11] = "Src_info";   
•                     xlSheet.Cells[2, 12] = "Dest_info";   
•                     xlSheet.Cells[2, 13] = "Release";   
•                     xlSheet.Cells[2, 14] = "Release_reason";   
•                     xlSheet.Cells[2, 15] = "Ring_time";   
•                     xlSheet.Cells[2, 16] = "Usernametype";   
•   
•   
•   
•                     int rowIndex = 3;//这个用来标记数据从多少行开始   
•   
•                     //设置单元格   
•                     for (int i = 1; i <= lvCDR.Columns.Count; i++)   
•                     {   
•                         range = xlSheet.get_Range(xlSheet.Cells[3, i], xlSheet.Cells[rowIndex + this.lvCDR.Items.Count, i]);   
•                         range.HorizontalAlignment = XlHAlign.xlHAlignLeft;// 设置单元格水平居左   
•                         range.NumberFormatLocal = "@";//文本格式   
•                         range.ColumnWidth = 12;   
•                     }   
•   
•                     //标题栏   
•                     range = xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[2, 16]);   
•                     range.Interior.ColorIndex = 45;//设置标题背景色为 浅橙色   
•                     range.Font.Bold = true;//标题字体加粗   
•   
•                     int isOut = 0;   
•                     pbProglass.Visible = true;   
•                     foreach (ListViewItem objItem in this.lvCDR.Items)   
•                     {   
•                         for (int i = 0; i < lvCDR.Columns.Count ; i++)   
•                         {   
•                             xlSheet.Cells[rowIndex, i + 1] = objItem.SubItems[i].Text;   
•                         }   
•   
•                         rowIndex += 1;   
•                         isOut++;   
•                         if (isOut > pbProglass.Maximum)   
•                         {   
•                             isOut = 0;   
•                         }   
•                         pbProglass.Value = isOut;   
•                     }   
•                     pbProglass.Visible = false;   
•   
•                     //数据区域   
•                     range = xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowIndex, 16]);   
•                     range.Borders.LineStyle = 1;   
•                     range.Font.Size = 10;   
•   
•   
•   
•                     range = xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, 16]);   
•                     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;   
•                 }   
•             }   
•         }