C#中ListView数据导出excel表

最新推荐文章于 2021-06-29 10:51:42 发布

西门小贤

最新推荐文章于 2021-06-29 10:51:42 发布

阅读量377

点赞数 2

分类专栏： C#_WinForm 文章标签： WinForm C# ListView 导出excel

本文链接：https://blog.csdn.net/han3262302/article/details/84114969

版权

C#_WinForm 专栏收录该内容

3 篇文章 0 订阅

订阅专栏


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;
                }
            }
        }