ListView数据导出至Excel

  1ListView数据导出成Excel#region   ListView数据导出成Excel
  2        /**//// <summary>
  3        /// ListView导出数据至Excel
  4        /// </summary>
  5        /// <param name="list">ListView</param>
  6        /// <param name="title">标题</param>
  7        public static void ExportExcel(ListView list,string title)
  8        {
  9            if (list.Items.Count == 0) return;
 10            string saveFileName = "";
 11            bool fileSaved = false;
 12            SaveFileDialog saveDialog = new SaveFileDialog();
 13            saveDialog.DefaultExt = "xls";
 14            saveDialog.Filter = "Excel文件|*.xls";
 15            saveDialog.FileName = !String.IsNullOrEmpty(title)?title:"Sheet1";
 16            saveDialog.ShowDialog();
 17            saveFileName = saveDialog.FileName;
 18            if (saveFileName.IndexOf(":") < 0) return; //被点了取消
 19
 20            Excel.Application excel = new Excel.Application();
 21        
 22            if (excel == null)
 23            {
 24                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
 25                return;
 26            }
 27
 28            Excel.Workbooks workbooks = excel.Workbooks;
 29            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
 30            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
 31            Excel.Range range;
 32
 33            excel.Visible = true;
 34            //excel.Cells.Select();
 35            excel.Cells.RowHeight = 20;
 36            excel.Rows.Font.Name = "宋体";
 37            excel.Rows.Font.Size = "12";
 38
 39            //string oldCaption=list.ca.CaptionText;
 40            long totalCount = list.Items.Count;
 41            long rowRead = 0;
 42            float percent = 0;
 43
 44            //写入标题
 45            Excel.Range rangeTitle = worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,list.Columns.Count]);
 46            rangeTitle.MergeCells = true;   //合并单元格
 47            rangeTitle.Font.Name = "宋体";
 48            rangeTitle.Font.Bold = true;
 49            rangeTitle.Font.Size = 16;
 50            rangeTitle.Font.ColorIndex = 5;
 51            worksheet.Cells[1,1] = title;
 52            //写入表头
 53            for (int i = 0; i < list.Columns.Count; i++)
 54            {
 55                worksheet.Cells[2, i + 1] = list.Columns[i].Text.ToString();
 56            }
 57
 58            //设置表头显示样式
 59            Excel.Range productTitle = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, list.Columns.Count]);
 60            productTitle.Font.Bold = true;
 61            productTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
 62            productTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
 63            productTitle.ColumnWidth = 18;
 64           
 65
 66            //写入数据
 67            for (int j = 0; j < list.Items.Count; j++)
 68            {
 69                for (int k = 0; k <= list.Columns.Count - 1; k++)
 70                {
 71                    worksheet.Cells[j + 3, k+1] = list.Items[j].SubItems[k].Text;
 72                    rowRead++;
 73                    percent = ((float)(100 * rowRead)) / totalCount; //进度
 74                }
 75                Application.DoEvents();
 76            }
 77
 78            range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[list.Items.Count + 2, list.Columns.Count]);
 79            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
 80
 81            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
 82            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
 83            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
 84            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
 85
 86            if (list.Columns.Count > 1)
 87            {
 88                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
 89                range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
 90                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
 91            }
 92
 93            if (saveFileName != "")
 94            {
 95                try
 96                {
 97                    workbook.Saved = true;
 98                    workbook.SaveCopyAs(saveFileName);
 99                    fileSaved = true;
100                }
101                catch (Exception ex)
102                {
103                    fileSaved = false;
104                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
105                }
106            }
107            else
108            {
109                fileSaved = false;
110            }
111
112            excel.Quit();
113            GC.Collect();//强行销毁
114            if (fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);
115
116        }
117 #endregion