///
/// 选择文件
///
///
///
private void btnSelectFile_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "Excel Files(2007以上) (*.xlsx)|*.xlsx|Excel Files(2000-2003) (*.xls)|*.xls";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
//openFileDialog1.CreatePrompt = true;
openFileDialog1.Title = "导入文件路径";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
this.txtFilesUrl.Text = openFileDialog1.FileName;
this.btnSelectFile.Enabled = false;
this.txtFilesUrl.ReadOnly = true;
}
}
///
/// 获得当前所选择的Excel Sheet的所有名字
///
///
///
public static string[] GetExcelSheetNames(string filePath)
{
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
int count = wb.Worksheets.Count;
string[] names = new string[count];
for (int i = 1; i <= count; i++)
{
names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
}
return names;
}
///
/// 开始导入
///
///
///
private void btnImport_Click(object sender, EventArgs e)
{
if (txtFilesUrl.Text.Length == 0)
{
MessageBoxEx.Show("请选择导入数据的Execl文件");
}
else
{
OleDbConnection conExcel = new OleDbConnection();
try
{
string filename = txtFilesUrl.Text;
//string sql = "insert into main_body select * from [Excel 8.0;HDR=Yes;IMEX=1;database=" + txtFilesUrl.Text + "].[Sheet1$] ";
string sql = "select * from [Excel 8.0;HDR=Yes;IMEX=1;database=" + txtFilesUrl.Text + "].[Sheet1$] ";
System.Data.DataTable dt = new System.Data.DataTable();
dt = DbHelperOleDb.Query_dt(sql);
//this.dataGridViewX2.DataSource = dt.DefaultView;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
//写入数据库数据
string MySql = "insert into Main_Body values(" + DbHelperOleDb.GetMaxID("oae001", "main_body") + ",'" + dt.Rows[i]["姓名"].ToString() + "','" + dt.Rows[i]["性别"].ToString()
+ "','" + dt.Rows[i]["职位层次"].ToString()
+ "','" + dt.Rows[i]["工作单位及职务"].ToString() + "','" + dt.Rows[i]["技术职务"].ToString()
+ "','" + dt.Rows[i]["移动电话"].ToString()
+ "','" + dt.Rows[i]["办公电话"].ToString()
+ "','" + dt.Rows[i]["行政区划"].ToString()
+ "','" + dt.Rows[i]["考官类别"].ToString()
+ "')";
DbHelperOleDb.ExecuteSql(MySql);
}
MessageBoxEx.Show("数据导入成功！");
}
else
{
MessageBoxEx.Show("请检查你的Excel中是否存在数据");
}
//OleDbCommand com = new OleDbCommand(sql, conExcel);
//conExcel.Open();
//com.ExecuteNonQuery();
//DbHelperOleDb.ExecuteSql(sql);
//MessageBoxEx.Show("导入数据成功", "导入数据", MessageBoxButtons.OK, rmation);
this.txtFilesUrl.Text = "";
this.txtFilesUrl.ReadOnly = false;
this.btnSelectFile.Enabled = true;
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.ToString());
}
finally
{
conExcel.Close();
}
}
}
///
/// //导出到execl
///
///
///
private void btnSaveFile_Click(object sender, EventArgs e)
{
//没有数据的话就不往下执行
if (dataGridViewX2.Rows.Count == 0)
{
MessageBoxEx.Show("筛选结果为空！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
// toolStripProgressBar1.Visible = true;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
//实例化一个Excel.Application对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
if (excel == null)
{
MessageBoxEx.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
= "Sheet1";
//int m = 0, n = 0;
//生成Excel中列头名称
for (int i = 0; i < dataGridViewX2.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridViewX2.Columns[i].HeaderText;//输出DataGridView列头名
}
//把DataGridView当前页的数据保存在Excel中
for (int i = 0; i < dataGridViewX2.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridViewX2.Columns.Count; j++)
{
if (dataGridViewX2[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridViewX2[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dataGridViewX2[j, i].Value.ToString();
}
}
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.Re
leaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageBoxEx.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, rmation);
// toolStripProgressBar1.Value = 0;
System.Diagnostics.Process.Start(strName);
}
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "错误提示");
}
}