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

ListView数据导出成Excel#region ListView数据导出成Excel2

/**//// <summary>3
/// ListView导出数据至Excel4
/// </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];//取得sheet131
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
try96

{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
else108

{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
浙公网安备 33010602011771号