C# Access数据库相关操作
private void BindData()
{
OleDbConnection thisConnection = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\ass3d3\\db\\asS3D.mdb");
thisConnection.Open();
//string sql = "SELECT * from MeasureData";
string sql = string.Format(@"SELECT customer.*, MeasureData.*, MeasureValues.* FROM (customer INNER JOIN MeasureData ON
customer.CustID = MeasureData.CustID) INNER JOIN MeasureValues ON MeasureData.id=MeasureValues.id");
OleDbDataAdapter adp = new OleDbDataAdapter(sql, thisConnection);
System.Data.DataTable mesureData = new System.Data.DataTable();
adp.Fill(mesureData);
this.customerView1.DataSource = mesureData;
thisConnection.Close();
// dataGridView1.Columns[1].HeaderText = "关系"; //改列名称
// dataGridView1.Columns[2].HeaderText = "姓名";//改列名称
// dataGridView1.Columns[1].DataPropertyName = ds.Tables[0].Columns[2].ToString();//前台第1列，显示数据库第2列的内容
// dataGridView1.Columns[2].DataPropertyName = ds.Tables[0].Columns[1].ToString();//前台第2列，显示数据库第1列的内容
// int count = dataGridView1.RowCount; //总行数
}
private void button1_Click(object sender, EventArgs e)
{
/*
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel文件|*.xlsx|Word文件|*.docx";
sfd.FilterIndex = 0;
if (sfd.ShowDialog() == DialogResult.OK)
{
OleDbConnection con = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\s3d3\\db\\S3D.mdb");
//string sql = "SELECT * from MeasureData";
string sql =string.Format( @"SELECT customer.*, MeasureData.*, MeasureValues.* FROM (customer INNER JOIN MeasureData ON
customer.CustID = MeasureData.CustID) INNER JOIN MeasureValues ON MeasureData.id=MeasureValues.id");
OleDbDataAdapter sda = new OleDbDataAdapter(sql, con);
System.Data.DataTable dt = new System.Data.DataTable();
//将数据库中查到的数据填充到DataTable数据表
sda.Fill(dt);
ExportExcel(dt, sfd.FileName);
MessageBox.Show("导出数据成功", "导出数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
*/
//导出datagridewiew
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
OleDbConnection conn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\s3d3\\db\\S3D.mdb");
conn.Open();
//string sql = "SELECT * from MeasureData";
// string sql = string.Format(@"SELECT * from Models");
string sql = string.Format(@"SELECT customer.*, MeasureData.*, MeasureValues.* FROM (customer INNER JOIN MeasureData ON
customer.CustID = MeasureData.CustID) INNER JOIN MeasureValues ON MeasureData.id=MeasureValues.id");
OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
System.Data.DataTable mesureData = new System.Data.DataTable();
adp.Fill(mesureData);
DataTabletoExcel(mesureData, saveFileName);
conn.Close();
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
private Excel.Application m_xlApp = new Excel.Application();
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="dtTmp">要导出的DataTable</param>
/// <param name="filePath">Excel的保存路径及名称</param>
public void DataTabletoExcel(System.Data.DataTable dtTmp, string filePath)
{
if (dtTmp == null)
{
return;
}
//向Excel中逐行逐列写入表格中的数据
long rowNum = dtTmp.Rows.Count;//行数
int columnNum = dtTmp.Columns.Count;//列数
m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得Sheet1
try
{
//单张Excel表格最大行数
if (rowNum > 65536)
{
long pageRows = 65535; //定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows); //导出数据生成的表单数
if (scount * pageRows < rowNum) //当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1) //这里由1改为3，20140922
{
object missing = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing); //添加一个sheet
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc]; //取得sheet1
}
string[,] datas = new string[pageRows + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = dtTmp.Columns[i].Caption; //表头信息
}
Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
range.Interior.ColorIndex = 15; //15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < columnNum; i++)
{
object obj = dtTmp.Rows[r][dtTmp.Columns[i].ToString()];
datas[index, i] = obj == null ? "" : obj.ToString().Trim();
}
progressBar1.Visible = true;
label2.Visible = true;
int step = Convert.ToInt32(r * 100 / result);
//MessageBox.Show("step:2222"+step);
this.label2.Text = "导出进度：" + step + "%";
this.progressBar1.Value = step;
}
Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; //Sheet表最大化
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = dtTmp.Columns[i].Caption;
}
Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
range.Interior.ColorIndex = 15; //15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = dtTmp.Rows[r][dtTmp.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : obj.ToString().Trim();
}
progressBar1.Visible = true;
label2.Visible = true;
int step = Convert.ToInt32(r * 100 / rowNum);
this.label2.Text = "导出进度：" + step + "%";
progressBar1.Value = step;
}
Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
workbook.Saved = true;
workbook.SaveCopyAs(filePath);
label2.Text = "导出进度：100%";
progressBar1.Hide();
MessageBox.Show("导出数据成功", "导出数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
//lblMsg.Text = "导出成功！" + DateTime.Now.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
//string dd = Server.MapPath("~/Tmp/1.txt");
// System.IO.File.AppendAllText(dd, "导出异常：" + ex.Message + Environment.NewLine);
}
finally
{
EndReport();
}
}
/// <summary>
/// 退出报表时关闭Excel和清理垃圾Excel进程
/// </summary>
private void EndReport()
{
object missing = System.Reflection.Missing.Value;
try
{
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
}
catch
{
}
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch
{
}
try
{
//清理垃圾进程
this.killProcessThread();
}
catch
{
}
GC.Collect();
}
}
/// <summary>
/// 杀掉不死进程
/// </summary>
private void killProcessThread()
{
ArrayList myProcess = new ArrayList();
for (int i = 0; i < myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch
{
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
//设置进度条
progressBar1.Refresh();
progressBar1.Visible = true;
progressBar1.Minimum = 0;
progressBar1.Value = 0;
//progressBar1.Step = 1;
this.label2.Text = "导出进度：";
label2.Visible = false;
label2.AutoSize = true;
progressBar1.Visible = false;
progressBar1.Maximum = 100;
}
}
