ListView -> Excel

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

zhangbp

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

阅读量719

点赞数

分类专栏：工作随想文章标签： listview excel exception object null dialog

工作随想专栏收录该内容

21 篇文章 0 订阅

订阅专栏

/// <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 = 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", "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 (ListViewItem objItem 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;
                }
            }
        }

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/nd200642/archive/2009/01/08/3733049.aspx