1
ListView数据导出成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
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