通过DataSet导出Excel .

在以前的项目中，做这些类似的功能也挺多的，没保存好，现在在这里记下，会以后所用：

[c-sharp] view plain copy print ?

1. /// <summary>  
2. /// 通过DataSet导出Excel  
3. /// </summary>  
4. /// <param name="ds"></param>  
5. public static void ExporDataSetToExcel(DataSet ds)  
6. {  
7.     if (ds == null) return;  
8.   
9.     string savefilename = "";  
10.     bool filesaved = false;  
11.   
12.     SaveFileDialog savedialog = new SaveFileDialog();  
13.     savedialog.DefaultExt = "xls";  
14.     savedialog.Filter = "excel文件|*.xls";  
15.     savedialog.FileName = "sheet1";  
16.     savedialog.ShowDialog();  
17.     savefilename = savedialog.FileName;  
18.     if (savefilename.IndexOf(":") < 0) return; //被点了取消   
19.   
20.     Excel.Application xlapp = new Excel.ApplicationClass();  
21.   
22.     if (xlapp == null)  
23.     {  
24.         MessageBox.Show("无法创建excel对象，可能您的机子未安装excel");  
25.         return;  
26.     }  
27.   
28.     Excel.Workbooks workbooks = xlapp.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.     //   string oldcaption=this.captiontext;   
34.     long totalcount = ds.Tables[0].Rows.Count;  
35.     long rowread = 0;  
36.     float percent = 0;  
37.   
38.     //   worksheet.Cells[1,1]=this.captiontext;   
39.     //写入字段   
40.     for (int i = 0; i < ds.Tables[0].Columns.Count; i++)  
41.     {  
42.         worksheet.Cells[2, i + 1] = ds.Tables[0].Columns[i].ColumnName;  
43.         range = (Excel.Range)worksheet.Cells[2, i + 1];  
44.         range.Interior.ColorIndex = 15;  
45.         range.Font.Bold = true;  
46.   
47.     }  
48.     //写入数值    
49.     //   this.captionvisible = true;   
50.     for (int r = 0; r < ds.Tables[0].Rows.Count; r++)  
51.     {  
52.         for (int i = 0; i < ds.Tables[0].Columns.Count; i++)  
53.         {  
54.             worksheet.Cells[r + 3, i + 1] = ds.Tables[0].Rows[r][i];  
55.         }  
56.         rowread++;  
57.         percent = ((float)(100 * rowread)) / totalcount;  
58.         //    this.captiontext = "正在导出数据["+ percent.tostring("0.00") +"%]";   
59.         System.Windows.Forms.Application.DoEvents();  
60.     }  
61.     //   this.captionvisible = false;   
62.     //   this.captiontext = oldcaption;   
63.   
64.     range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds.Tables[0].Rows.Count + 2, ds.Tables[0].Columns.Count]);  
65.     range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);  
66.   
67.     range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;  
68.     range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;  
69.     range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;  
70.   
71.     if (ds.Tables[0].Columns.Count > 1)  
72.     {  
73.         range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;  
74.         range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;  
75.         range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;  
76.     }  
77.   
78.     if (savefilename != "")  
79.     {  
80.         try  
81.         {  
82.             workbook.Saved = true;  
83.             workbook.SaveCopyAs(savefilename);  
84.             filesaved = true;  
85.             MessageBox.Show("文件已经成功导出...");  
86.         }  
87.         catch (Exception ex)  
88.         {  
89.             filesaved = false;  
90.             MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message);  
91.         }  
92.     }  
93.     else  
94.     {  
95.         filesaved = false;  
96.     }  
97.     xlapp.Quit();  
98.     GC.Collect();//强行销毁   
99.   
100. }   

/// <summary> /// 通过DataSet导出Excel /// </summary> /// <param name="ds"></param> public static void ExporDataSetToExcel(DataSet ds) { if (ds == null) return; string savefilename = ""; bool filesaved = false; SaveFileDialog savedialog = new SaveFileDialog(); savedialog.DefaultExt = "xls"; savedialog.Filter = "excel文件|*.xls"; savedialog.FileName = "sheet1"; savedialog.ShowDialog(); savefilename = savedialog.FileName; if (savefilename.IndexOf(":") < 0) return; //被点了取消 Excel.Application xlapp = new Excel.ApplicationClass(); if (xlapp == null) { MessageBox.Show("无法创建excel对象，可能您的机子未安装excel"); return; } Excel.Workbooks workbooks = xlapp.Workbooks; Excel._Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Excel.Range range; // string oldcaption=this.captiontext; long totalcount = ds.Tables[0].Rows.Count; long rowread = 0; float percent = 0; // worksheet.Cells[1,1]=this.captiontext; //写入字段 for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { worksheet.Cells[2, i + 1] = ds.Tables[0].Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 // this.captionvisible = true; for (int r = 0; r < ds.Tables[0].Rows.Count; r++) { for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { worksheet.Cells[r + 3, i + 1] = ds.Tables[0].Rows[r][i]; } rowread++; percent = ((float)(100 * rowread)) / totalcount; // this.captiontext = "正在导出数据["+ percent.tostring("0.00") +"%]"; System.Windows.Forms.Application.DoEvents(); } // this.captionvisible = false; // this.captiontext = oldcaption; range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds.Tables[0].Rows.Count + 2, ds.Tables[0].Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; if (ds.Tables[0].Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } if (savefilename != "") { try { workbook.Saved = true; workbook.SaveCopyAs(savefilename); filesaved = true; MessageBox.Show("文件已经成功导出..."); } catch (Exception ex) { filesaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message); } } else { filesaved = false; } xlapp.Quit(); GC.Collect();//强行销毁 }

 

这些代码可以完成此功能，以后会继续完善!