利用数据集作为数据源,在winform中导出Excel。
1、首先要确保使用的机器安装Office工具。 然后在工程中添加如下引用。
2、建立一个强类型的数据集
3、在这个强类型数据集里面建立一个dataAdapter,用于对Product表进行查询。
4、建立一个winform的应用程序,添加一个窗体,在窗体上添加一个button。
5、下面我们开始写代码了。
首先,添加如下引用。
using
Microsoft.Office.Interop;
using
Microsoft.Office.Interop.Excel;
然后,实现以下代码。
namespace
WindowsApplication1
...
{ public partial class Form1 : Form ...{ public Form1() ...{ InitializeComponent(); } private Microsoft.Office.Interop.Excel.Application myExcel = null; private void button1_Click(object sender, EventArgs e) ...{ List<Process> excelProcesses = GetExcelProcesses(); if (excelProcesses.Count > 0) ...{ MessageBox.Show("请将其他的Excel关闭再进行导出!"); } if (myExcel == null) myExcel = new Microsoft.Office.Interop.Excel.Application(); //效率低 //ExprotExcel1(); //效率低 //效率高 ExprotExcel2(); //效率高 } private void ExprotExcel2() ...{ //强类型数据集包含的Products表 dsProducts.ProductsDataTable productsTable = new dsProducts.ProductsDataTable(); productsTable.AcceptChanges(); this.Cursor = Cursors.WaitCursor; //保存文化环境 System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //保存文化环境 Workbook workbookData = myExcel.Workbooks.Add(Missing.Value); Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets[1]; xlSheet.Name = "Products Table In Northwind"; myExcel.Visible = true; myExcel.Cells[1, 1] = "Northwind数据库产品表";//默认的就是在sheet1里面的。 WorksheetClass wsClass = new WorksheetClass(); //设置主标题单元格的样式 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).MergeCells = true;//合并单元格 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平对齐方式 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).VerticalAlignment = XlVAlign.xlVAlignBottom;//垂直对齐方式 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.Bold = true;//字体加粗 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.ColorIndex = 0;//字体颜色 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.Italic = true;//是否斜体 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.Size = 22;//字体大小 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Borders.LineStyle = XlLineStyle.xlContinuous;//边框样式 xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).RowHeight = 33.75;//行高 //设置主标题单元格的样式 //设置标题单元格样式 xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[2, 10]).RowHeight = 22.75;//行高 xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[2, 1]).ColumnWidth = 33.75;//列宽 //设置标题单元格样式 //填充标题 for (int i = 0; i < productsTable.Columns.Count; i++) ...{ myExcel.Cells[2, i + 1] = productsTable.Columns[i].Caption; } //填充标题 //获得数据表的值 dsProductsTableAdapters.ProductsTableAdapter adtProducts = new WindowsApplication1.dsProductsTableAdapters.ProductsTableAdapter(); productsTable = adtProducts.GetProducts();//获得数据库所有的Products //获得数据表的值 int RowCount = productsTable.Rows.Count; int colCount = productsTable.Columns.Count; //设置填充单元格样式 xlSheet.get_Range(xlSheet.Cells[3, 1], xlSheet.Cells[RowCount, colCount]).Borders.LineStyle = XlLineStyle.xlContinuous;//边框的样式 //设置填充单元格样式 object[,] objData = new object[RowCount, colCount]; //将DataTable里的值填充到对象数组中 for (int i = 0; i < RowCount; i++) ...{ for (int j = 0; j < colCount; j++) ...{ objData[i, j] = productsTable.Rows[i][j].ToString(); } System.Windows.Forms.Application.DoEvents(); } //将DataTable里的值填充到对象数组中 //将对象数组的值赋值给Excel Range range = xlSheet.get_Range(xlSheet.Cells[3, 1], xlSheet.Cells[RowCount + 2, colCount]); range.Value2 = objData; //将对象数组的值赋值给Excel //恢复文化环境 System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; //恢复文化环境 try ...{ myExcel.Save(@"C:a.xls"); myExcel.Quit(); this.Cursor = Cursors.Default; MessageBox.Show("导出成功!", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } finally ...{ //释放资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookData); System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel); GC.Collect(); range = null; xlSheet = null; workbookData = null; myExcel = null; KillTheExcel(); //释放资源 } } /**//// <summary> /// 效率低的方法 /// </summary> private void ExprotExcel1() ...{ List<Process> processes = GetExcelProcesses(); if (processes.Count > 0) ...{ MessageBox.Show("请关闭其他的Excel程序,以便进行导出!"); return; } dsProducts.ProductsDataTable productsTable = new dsProducts.ProductsDataTable(); productsTable.AcceptChanges(); this.Cursor = Cursors.WaitCursor; //保存文化环境 System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //保存文化环境 myExcel.Application.Workbooks.Add(true); myExcel.Visible = true; myExcel.Cells[1, 1] = "Northwind数据库产品表"; //填充标题 for (int i = 0; i < productsTable.Columns.Count; i++) ...{ myExcel.Cells[2, i + 1] = productsTable.Columns[i].Caption; } //填充标题 dsProductsTableAdapters.ProductsTableAdapter adtProducts = new WindowsApplication1.dsProductsTableAdapters.ProductsTableAdapter(); productsTable = adtProducts.GetProducts(); /**////效率低 //填充数据 for (int i = 0; i < productsTable.Rows.Count; i++) ...{ for (int j = 0; j < productsTable.Columns.Count; j++) ...{ if (productsTable.Rows[i][j].GetType() == typeof(System.String)) ...{ //从第3行开始 myExcel.Cells[i + 3, j + 1] = "'" + productsTable.Rows[i][j].ToString(); } else ...{ //从第3行开始 myExcel.Cells[i + 3, j + 1] = productsTable.Rows[i][j].ToString(); } } } //填充数据 /**////效率低 //恢复文化环境 System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; //恢复文化环境 this.Cursor = Cursors.Default; MessageBox.Show("导出成功!", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } /**//// <summary> /// 效率高的方法 /// </summary> private void KillTheExcel() ...{ /**////下面三句话根本没办法杀掉Excel进程,知道的同志请发Email:firewood001@yahoo.com.cn告知 //myExcel.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel); //GC.Collect(); KillAllExcel(); } /**//// <summary> /// 获得所有的Excel进程 /// </summary> /// <returns>所有的Excel进程</returns> private List<Process> GetExcelProcesses() ...{ Process[] processes = Process.GetProcesses(); List<Process> excelProcesses = new List<Process>(); for (int i = 0; i < processes.Length; i++) ...{ if (processes[i].ProcessName.ToUpper() == "EXCEL") excelProcesses.Add(processes[i]); } return excelProcesses; } private void Form1_FormClosing(object sender, FormClosingEventArgs e) ...{ MessageBox.Show("系统退出将windows所有的Excel,请保存!"); KillAllExcel(); } private void KillAllExcel() ...{ List<Process> excelProcess = GetExcelProcesses(); for (int i = 0; i < excelProcess.Count; i++) ...{ excelProcess[i].Kill(); } } } }