namespace ExportToExcel { using System; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; using System.Runtime.InteropServices; public class ExcelManager:IDisposable { Excel.Range m_objRange = null; Excel.Application m_objExcel = null; Excel.Workbooks m_objBooks = null; Excel._Workbook m_objBook = null; Excel.Sheets m_objSheets = null; Excel._Worksheet m_objSheet = null; Excel.QueryTable m_objQryTable = null; object m_objOpt = System.Reflection.Missing.Value; //DataBase-used variable private System.Data.SqlClient.SqlConnection sqlConn = null; private string strConnect = string.Empty; private System.Data.SqlClient.SqlCommand sqlCmd = null; //Sheets variable private double dbSheetSize = 65535;//the hight limit number in one sheet private int intSheetTotalSize = 0;//total record can divied sheet number private double dbTotalSize = 0;//record total number /// <summary> /// 建构函数 /// </summary> public ExcelManager(){} /// <summary> /// 建构函数 /// </summary> /// <param name="dbHL">一个Excel表格的最大记录数</param> /// <param name="dbTotal">该数据库表共查询出多少条记录</param> /// <param name="intDivide">查询出的记录可分成几个Excel</param> /// <param name="conn">sqlConnection</param> public ExcelManager(Double dbHL,Double dbTotal,int intDivide,SqlConnection conn ) { dbSheetSize = dbHL; intSheetTotalSize = intDivide; dbTotalSize = dbTotal; sqlConn = conn; } /// <summary> /// 建构函数 /// </summary> /// <param name="dbHL">一个Excel表格的最大记录数</param> /// <param name="strTableName">需查询的数据库的表名</param> /// <param name="conn">sqlConnection</param> public ExcelManager(Double dbHL,string strTableName,SqlConnection conn) { dbSheetSize = dbHL; sqlConn = conn; intSheetTotalSize = GetTotalSize(strTableName,sqlConn); } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } private void Dispose(bool disposing) { if(disposing) { // Dispose managed resources. Marshal.FinalReleaseComObject(m_objExcel); m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBooks = null; m_objBook = null; m_objExcel = null; } } /// <summary> /// 取得总记录数跟可分成几个Excel sheet. /// </summary> /// <param name="strTableName">被查询的数据库的表名</param> /// <param name="sqlConn">sqlConnection</param> /// <returns>可分成Excel Sheet的个数</returns> private int GetTotalSize(string strTableName,SqlConnection sqlConn) { //sqlConn = new System.Data.SqlClient.SqlConnection(strConnect); sqlCmd = new System.Data.SqlClient.SqlCommand("Select Count(*) From "+strTableName, sqlConn); if(this.sqlConn.State == ConnectionState.Closed) sqlConn.Open(); dbTotalSize = (int)sqlCmd.ExecuteScalar(); sqlConn.Close(); return (int)Math.Ceiling(dbTotalSize / this.dbSheetSize); } /// <summary> /// 新建一个Excel实例 /// </summary> /// <param name="strTitle">Excel表头上的文字</param> public void DeclareExcelApp(string[] strTitle,string strSql,string strTableName,string strMastTitle) { m_objExcel = new Excel.ApplicationClass(); m_objExcel.Visible = true; m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; if (intSheetTotalSize <= 3) { if (this.dbTotalSize <= this.dbSheetSize) { this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle ); return; } else if (this.dbTotalSize <= this.dbSheetSize * 2) { this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle ); this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle ); return; } else { this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle ); this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle ); this.ExportDataByQueryTable(3, true,strTitle,strSql,strTableName,strMastTitle ); return; } } for (int i = 3; i < intSheetTotalSize; i++) { m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt); } ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle ); for (int i = 2; i <= m_objSheets.Count; i++) { ExportDataByQueryTable(i, true,strTitle,strSql,strTableName,strMastTitle ); } } /// <summary> /// 以用户输入的文件名保存文件 /// </summary> public void SaveExcelApp() { string excelFileName = string.Empty; SaveFileDialog sf = new SaveFileDialog(); sf.Filter = "*.xls|*.*"; if (sf.ShowDialog() == DialogResult.OK) { excelFileName = sf.FileName; } else { return; } m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt,m_objOpt); if (m_objExcel != null) m_objExcel = null; } /// <summary> /// 利用Excel的QueryTable导出数据 /// </summary> /// <param name="intSheetNumber">导出第几个sheet</param> /// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param> /// <param name="strTitle">表头,需与查询sql语句对齐一致。</param> /// <param name="strSql">查询的sql语句,表头的文字需与该sql语句对齐一致。</param> /// <param name="strTablName">查询的表名</param> /// <param name="strMastTitle">主标题</param> /// </summary> public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan,string[] strTitle,string strSql,string strTablName,string strMastTitle) { string strQuery = string.Empty; if (blIsMoreThan) { strQuery = "Select Top " + this.dbSheetSize + strSql + " From " + strTablName + " Where Not OrderID In (Select Top " + dbSheetSize * (intSheetNumber - 1) + " OrderID From " + strTablName + ")"; } else { strQuery = "Select Top " + this.dbSheetSize + strSql+ " From "+strTablName; } m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber)); m_objSheet.Cells[1,1] = strMastTitle; m_objSheet.Cells[2,1] = "打印日期"+DateTime.Now.ToShortDateString(); for(int i = 1;i<=strTitle.Length;i++) { m_objSheet.Cells[4,i] = strTitle[i-1].ToString(); } m_objRange = m_objSheet.get_Range("A5", m_objOpt); m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + sqlConn.ConnectionString, m_objRange, strQuery); m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.FieldNames = false; m_objQryTable.Refresh(false); } } } 首先,注意要先引用组件Microsoft.Office.Interop.Excel using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.IO; using System.Data.OleDb; using Microsoft.Office.Interop.Excel; //导入Excel,可以把Excel当做是一个数据库 DataTable dt = new DataTable(); //准备放在这个DataTable中 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +"Excel文件路径"+ "; Extended Properties=Excel 8.0;"; OleDbConnection oleConn = new OleDbConnection(strConn); oleConn.Open(); //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等 System.Data.DataTable dtSheetName = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //包含excel中表名的字符串数组 string[] strTableNames = new string[dtSheetName.Rows.Count]; for (int k = 0; k < dtSheetName.Rows.Count; k++) { strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString(); } string strSql = "select * FROM [" + strTableNames[0] + "]"; //读Excel中第一个表 OleDbCommand oleCom = new OleDbCommand(strSql, oleConn); using (OleDbDataReader rdr = oleCom.ExecuteReader()) { dt.Load(rdr); } oleConn.Close(); //导出Excel,以DataTable数据源为例 if (dt.Rows.Count > 0) { string fileName = "预置文件名";//可以加个saveFileDialog文件保存控件,saveFileDialog1.FileName Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; object ms = Type.Missing; Microsoft.Office.Interop.Excel.Workbook wk = excel.Workbooks.Add(ms); Microsoft.Office.Interop.Excel.Worksheet ws = wk.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; for (int i = 0; i < dt.Columns.Count; i++) { ws.Cells[1, i + 1] = dt.Columns[i].ColumnName; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { ws.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } } try { wk.SaveAs(fileName, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms); MessageBox.Show("信息导出成功!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception) { MessageBox.Show("信息导出失败!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } excel.Quit(); }