数据表文件导出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;