【原】winform高效导出Excel带格式设置
参考网上的方法修改,1000条记录导出只要3S(1GRDM,C2.8CPU).
1.项目添加excel对象类库的引用,Microsoft Excel 11.0 object library(不同版本的Excel,类库不同,这是2003的)
2.代码
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
1
//-***************获取要写入excel的数据源***************
2
Dao model=new Dao();
3
DataTable dt=model.GetFileNameList(0,intPageSize,Convert.ToDateTime(dtPFrom.Value), Convert.ToDateTime(dtPTo.Value));//取得dataGrid绑定的DataSet
4
if(dt==null) return;
5
DataGridTableStyle ts = dataGrid1.TableStyles[0];
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
//-***************获取excel对象***************
9
string saveFileName="";
10
bool fileSaved=false;
11
SaveFileDialog saveDialog=new SaveFileDialog();
12
saveDialog.DefaultExt ="xls";
13
saveDialog.Filter="Excel文件|*.xls";
14
saveDialog.FileName ="导入记录查询结果 "+DateTime.Today.ToString("yyyy-MM-dd");
15
saveDialog.ShowDialog();
16
saveFileName=saveDialog.FileName;
17
if(saveFileName.IndexOf(":")<0) return; //被点了取消
18
Excel.Application xlApp=new Excel.Application();
19
if(xlApp==null)
20
{
21
MessageBox.Show("无法启动Excel,可能您的机子未安装Excel");
22
return;
23
}
24
Excel.Workbook workbook = xlApp.Workbooks.Add(true);
25
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
26
Excel.Range range;
27![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
29
string oldCaption=dataGrid1.CaptionText;
30
// 列索引,行索引,总列数,总行数
31
int colIndex = 0;
32
int RowIndex = 0;
33
int colCount = ts.GridColumnStyles.Count;
34
int RowCount=dt.Rows.Count;
35
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
// *****************获取数据*********************
38
dataGrid1.CaptionVisible = true;
39
dataGrid1.CaptionText = "正在导出数据![](https://www.cnblogs.com/Images/dot.gif)
";
40
// 创建缓存数据
41
object[,] objData = new object[RowCount + 1, colCount];
42
// 获取列标题
43
foreach(DataGridColumnStyle cs in ts.GridColumnStyles)
44
{
45
objData[RowIndex,colIndex++] = cs.HeaderText;
46
}
47
48
// 获取具体数据
49
for(RowIndex =1;RowIndex< RowCount;RowIndex++)
50
{
51
for(colIndex=0;colIndex < colCount;colIndex++)
52
{
53
objData[RowIndex,colIndex] =dt.Rows[RowIndex-1][colIndex+1];
54
}
55
56
}
57![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
//********************* 写入Excel*******************
59
60
range = worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[RowCount,colCount]);
61
range.Value2= objData;
62
Application.DoEvents();
63
64
//*******************设置输出格式******************************
65
66
//设置顶部説明
67
range = worksheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,colCount]);
68
range.MergeCells = true;
69
range.RowHeight=38;
70
range.Font.Bold=true;
71
range.Font.Size=14;
72
range.Font.ColorIndex=10;//字体颜色
73
xlApp.ActiveCell.FormulaR1C1 = "导入记录查询结果";
74![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
//特殊数字格式
76
range = worksheet.get_Range(xlApp.Cells[2,colCount],xlApp.Cells[RowCount,colCount]);
77
range.NumberFormat="yyyy-MM-dd hh:mm:ss";
78![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
xlApp.Cells.HorizontalAlignment=Excel.Constants.xlCenter;
80
range = worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[2,colCount]);
81
range.Interior.ColorIndex = 10;//背景色
82
range.Font.Bold = true;
83
range.RowHeight=20;
84
((Excel.Range)worksheet.Cells[2,1]).ColumnWidth=25;
85
((Excel.Range)worksheet.Cells[2,2]).ColumnWidth=13;
86
((Excel.Range)worksheet.Cells[2,3]).ColumnWidth=18;
87
((Excel.Range)worksheet.Cells[2,4]).ColumnWidth=15;
88
((Excel.Range)worksheet.Cells[2,5]).ColumnWidth=22;
89![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
//***************************保存**********************
91
dataGrid1.CaptionVisible = false;
92
dataGrid1.CaptionText = oldCaption;
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
xlApp.Quit();
112
GC.Collect();//强行销毁
113
TimeSpan dateEnd=new TimeSpan(DateTime.Now.Ticks);
114
TimeSpan tspan=dateBegin.Subtract(dateEnd).Duration();
115
MessageBox.Show(tspan.ToString());
116
if(fileSaved && File.Exists(saveFileName))
117
System.Diagnostics.Process.Start(saveFileName);
1.项目添加excel对象类库的引用,Microsoft Excel 11.0 object library(不同版本的Excel,类库不同,这是2003的)
2.代码
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Powered By D&J (URL:http://www.cnblogs.com/Areas/)