数据表文件导出Execel文件-本地测试成功

最新推荐文章于 2025-06-21 21:53:45 发布

最新推荐文章于 2025-06-21 21:53:45 发布 · 89 阅读

文章标签：

#数据库

本文介绍了一种使用C#从数据库导出数据到Excel文件的方法。通过连接数据库并选取前10条记录，然后将这些数据写入Excel文件，并对单元格进行格式设置，最后保存并关闭Excel文件。

数据表文件导出Execel文件-本地测试成功

测试了我一天.还是在睡觉之前搞定了

好开心的.希望能对大家也有帮助

添加execel 11.0 liburary lib的引用

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.IO;
using System.Diagnostics;
using System.Reflection;

//按钮事件

string CurrentDir=System.Environment.CurrentDirectory;
string strConnection="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+CurrentDir+"//Database//user.mdb;";
OleDbConnection myConn=new OleDbConnection(strConnection);

DataSet objDataset = new DataSet();
OleDbConnection objConn = new OleDbConnection(strConnection);
objConn.Open();

OleDbDataAdapter objAdapter = new OleDbDataAdapter("SELECT top 10 * from Admin",objConn);
objAdapter.Fill(objDataset);

DataView oView = new DataView(objDataset.Tables[0]);

DataTable myTable=new DataTable();
myTable=objDataset.Tables[0];

//读取数据库中的数据表
dataGrid2.DataSource = oView;

Excel.Application excel = new Excel.ApplicationClass();
excel.Workbooks.Add(true);
Excel.Workbook workbook = excel.Workbooks[1];

Excel.Range r = excel.get_Range(excel.Cells[1,1],excel.Cells[1,1]);
excel.Cells[1,1] = dataGrid2.CaptionText.Trim();
r.Font.Name = "宋体";//字体
r.Font.Size = 15;//字号
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
r.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//竖直居中
for(int i = 0 ; i < myTable.Rows.Count; i++)
{
for(int j = 0 ; j < myTable.Columns.Count; j++)
{
excel.Cells[i+2,j+1] = myTable.Rows[i][j].ToString();
}
}
Excel.Range r1 = excel.get_Range(excel.Cells[2,1],excel.Cells[myTable.Rows.Count+1,myTable.Columns.Count]);
r1.Font.Name = "宋体";
r1.Font.Size = 10;
r1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
r1.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
r1.EntireColumn.AutoFit();//自动调整列宽
r1.EntireRow.AutoFit();//自动调整行高
//加表格线
r1.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
r1.Borders.Weight = Excel.XlBorderWeight.xlThin;
//外框加粗
r1.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
r1.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
r1.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
r1.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;

string SaveFilePath=txtSaveExecelPath.Text;

workbook.SaveAs(SaveFilePath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);

excel.Quit();
MessageBox.Show("Execel文件导出成功！");
//填充数据

//填充数据库
//dataGrid2..DataBindings();
objConn.Close();
objConn.Dispose();
objConn = null;