C#导出Excle (简化版): public partial class Form1 : Form { private static OleDbConnection conn = new OleDbConnection(); private static OleDbCommand comm = new OleDbCommand(); private OleDbDataAdapter coda; private DataSet cods = new DataSet(); private string sqlword; private int c; private List<string> columnList; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { this.dataGridView1.RowHeadersVisible = false; this.dataGridView1.AllowUserToAddRows = false; this.dataGridView1.AllowUserToOrderColumns = false; this.dataGridView1.AllowUserToDeleteRows = false; this.dataGridView1.AllowUserToResizeColumns = false; this.dataGridView1.AllowUserToResizeRows = false; this.dataGridView1.MultiSelect = false; this.dataGridView1.ReadOnly = true; this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //this.dataGridView1.AutoGenerateColumns = false; DataColumn dtColumn = new DataColumn(); System.Data.DataTable dt = new System.Data.DataTable(); DataSet ds = new DataSet(); int i = 0; ds.ReadXml(System.IO.Path.GetFullPath("../../MyDataTable.xml")); //读取数据库绝对路径 DataRow[] drColumnToDisplay = new DataRow[ds.Tables[0].Select("visiable = 'true'").Length]; string[] strName = new string[drColumnToDisplay.Length]; drColumnToDisplay = ds.Tables[0].Select("visiable = 'true'");//读取xml中有效的列名 columnList = new List<string>(); foreach (DataRow dr in drColumnToDisplay) { dtColumn = new DataColumn(); dtColumn.ColumnName = dr["strucrtName"].ToString(); dt.Columns.Add(dtColumn); columnList.Add(dr["strucrtName"].ToString()); strName[i] = dr["strucrtName"].ToString(); i += 1; } //增加数据行 System.Data.DataTable dt1 = fillDT(""); for (i = 0; i <= dt1.Rows.Count - 1; i++) { DataRow dr = dt.NewRow(); for (int j = 0; j <= dt1.Columns.Count - 1; j++) { string a = dt1.Columns[j].ColumnName.ToString(); if (caseName(dt1.Columns[j].ColumnName.ToString(), dt1, i, j) != "") { dr[dt1.Columns[j].ColumnName.ToString()] = caseName(dt1.Columns[j].ColumnName.ToString(), dt1, i, j); } } dt.Rows.Add(dr); } this.dataGridView1.DataSource = dt; for (int k = 0; k <= dt.Columns.Count - 1; k++) { dt.Columns[k].ColumnName = columnName(dt.Columns[k].ColumnName); } } public OleDbConnection GetSqlConnection() { string dbPath = System.IO.Path.GetFullPath("../../post.mdb"); string Connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + dbPath + ";Persist Security Info=True;Jet OLEDB:Database Password=smartcard"; OleDbConnection conn = new OleDbConnection(Connect); return conn; } public System.Data.DataTable fillDT(string seekword) { conn = GetSqlConnection(); if (seekword.Length > 0) { seekword = "and " + seekword; } sqlword = " select * from tb_service " + seekword; coda = new OleDbDataAdapter(sqlword, conn); try { DataSet fillds = new DataSet(); coda.Fill(fillds, "list"); c = fillds.Tables["list"].Rows.Count; GC.Collect(); return fillds.Tables["list"]; } catch (Exception e) { System.Data.DataTable dt = new System.Data.DataTable(); GC.Collect(); return dt; } } private string caseName(string name, System.Data.DataTable dt, int i, int j) { int keyIndex = columnList.IndexOf(name); if (keyIndex != -1) { return dt.Rows[i][dt.Columns[j].ColumnName.ToString()].ToString(); } return ""; } private string columnName(string name) { switch (name) { case "service_name": return "服务名称"; case "service_type": return "服务类别"; case "service_price": return "服务价格"; case "service_cycle": return "服务时限"; case "cycle_time": return "服务周期"; case "start_time": return "开始时间"; case "available": return "是否有效"; } return ""; } private string[] colName(System.Data.DataTable dt) //获取DataGridView列名 { string colName = ""; for (int i = 0; i <= dt.Columns.Count - 1; i++) { colName = dt.Columns[i].ColumnName.ToString() + "|" + colName; } colName = colName.Substring(0, colName.LastIndexOf("|")); string[] sColName = colName.Split(new char[] { '|' }); string[] newColName = new string[sColName.Length]; for (int j = sColName.Length - 1; j >= 0; j--) { newColName[(sColName.Length - 1) - j] = sColName[j]; } return newColName; } private void button1_Click(object sender, EventArgs e) { ExportExcel.ExcelOP ep = new ExcelOP(); ep.DataTableToExcel(colName((System.Data.DataTable)this.dataGridView1.DataSource), "test", (System.Data.DataTable)this.dataGridView1.DataSource, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "test", "0", 0, 0); } private void button2_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; Workbook wBook = (Workbook)app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; for (int k = 0; k <= colName((System.Data.DataTable)this.dataGridView1.DataSource).Length - 1; k++) { wSheet.Cells[1, k + 1] = colName((System.Data.DataTable)this.dataGridView1.DataSource)[k].ToString(); } if (((System.Data.DataTable)this.dataGridView1.DataSource).Rows.Count > 0) { int row, col; row = ((System.Data.DataTable)this.dataGridView1.DataSource).Rows.Count; col = ((System.Data.DataTable)this.dataGridView1.DataSource).Columns.Count; for (int i = 0; i <= row - 1; i++) { for (int j = 0; j <= col - 1; j++) { wSheet.Cells[i + 2, j + 1] = ((System.Data.DataTable)this.dataGridView1.DataSource).Rows[i][j].ToString(); } } } wSheet.Columns.AutoFit(); wSheet.Columns.Font.Name = "Arial"; wSheet.Columns.Font.Size = 10; } } VB导出Excel : Public Class ExcelOP #Region "Public Definition" Private oXL As Excel.Application Private oWB As Excel.Workbook Private oSheet As Excel.Worksheet Private oRng As Excel.Range Private totalValue As Double #End Region ''' <summary> ''' 导出Excel ''' </summary> ''' <param name="CellsName">表头</param> ''' <param name="Title">标题</param> ''' <param name="DT">数据源</param> ''' <param name="ExcelDate">日期</param> ''' <param name="SheetName">工作簿名字</param> ''' <param name="ToTalName">需要计算的列名</param> ''' <param name="isTotal">是否需要计算</param> ''' <param name="colIndex">列所在的索引值</param> ''' <returns>无返回值</returns> ''' <remarks></remarks> Public Function DataTableToExcel(ByVal CellsName As String(), ByVal Title As String, ByVal DT As System.Data.DataTable, ByVal ExcelDate As String, ByVal SheetName As String, ByVal ToTalName As String, ByVal isTotal As Integer, ByVal colIndex As Integer) Dim i, j As Integer Dim startRow As Integer = 4 Dim startCell, endCell As String Try oXL = CreateObject("Excel.Application") 'create a excel application when was setup the office of excel oXL.Visible = True oWB = oXL.Workbooks.Add oSheet = oXL.ActiveSheet oSheet.Name = SheetName With oSheet For i = 0 To CellsName.Length - 1 .Cells(startRow, i + 1) = CellsName(i).ToString() Next End With Dim Rcount As Integer = DT.Rows.Count Dim Ccount As Integer = DT.Columns.Count If DT.Rows.Count > 0 Then For i = 0 To Rcount - 1 For j = 0 To Ccount - 1 If (DT.Rows(i).Item(j).ToString().Length > 10) Then oSheet.Cells(i + startRow + 1, j + 1) = "'" + DT.Rows(i).Item(j) Else oSheet.Cells(i + startRow + 1, j + 1) = DT.Rows(i).Item(j) End If Next If isTotal = 1 Then Get_Total(Double.Parse(DT.Rows(i).Item(ToTalName))) End If Next End If If isTotal = 1 Then 'format the excel column oSheet.Cells(i + 6, 1) = "总计" oSheet.Cells(i + 6, colIndex + 1) = totalValue oSheet.Range("A" & (i + 6), Get_Excel_Rang(colIndex + 1) & (i + 6)).Interior.ColorIndex = 37 oSheet.Range("A" & (i + 6), Get_Excel_Rang(colIndex + 1) & (i + 6)).Font.Bold = True End If startCell = "A" & startRow endCell = Get_Excel_Rang(CellsName.Length) & startRow.ToString() oSheet.Range(startCell, endCell).Interior.ColorIndex = 37 oSheet.Range(startCell, endCell).Font.Bold = True oSheet.Range(startCell, endCell).Borders.LineStyle = Excel.XlLineStyle.xlContinuous oSheet.Columns.Font.Name = "Arial" oSheet.Columns.Font.Size = 10 oSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter oSheet.Columns.AutoFit() oSheet.Cells(1, 1) = Title oSheet.Cells(2, 1) = ExcelDate oRng = oSheet.Range("A1", "E1") With oRng .Merge() .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft End With oRng = oSheet.Range("A2", "E2") With oRng .Merge() .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft End With 'delete the sheet for excel oWB.Worksheets("Sheet2").Delete() oWB.Worksheets("Sheet3").Delete() Catch ex As Exception System.Windows.Forms.MessageBox.Show(ex.Message.ToString()) End Try Return True End Function Private Function Get_Total(ByVal Value As Double) 'get the value and return sum of the value totalValue = totalValue + Value Return True End Function Private Function Get_Excel_Rang(ByVal sheetCells As Integer) As String Select Case sheetCells Case 1 Return "A" Exit Select Case 2 Return "B" Exit Select Case 3 Return "C" Exit Select Case 4 Return "D" Exit Select Case 5 Return "E" Exit Select Case 6 Return "F" Exit Select Case 7 Return "G" Exit Select Case 8 Return "H" Exit Select Case 9 Return "I" Exit Select Case 10 Return "J" Exit Select Case 11 Return "K" Exit Select Case 12 Return "L" Exit Select Case 13 Return "M" Exit Select Case 14 Return "N" Exit Select Case 15 Return "O" Exit Select Case 16 Return "P" Case 17 Return "Q" Exit Select Case 18 Return "R" Exit Select Case 19 Return "S" Exit Select Case 20 Return "T" Exit Select Case 21 Return "U" Exit Select Case 22 Return "V" Exit Select Case 23 Return "W" Exit Select Case 24 Return "X" Exit Select Case 25 Return "Y" Exit Select Case 26 Return "Z" Exit Select End Select Return True End Function End Class 实例源码下载地址: VB+C# 导出Excel 实例源码