初学总结: 如果有什么指教请留言,谢谢。
首先我的界面是这样
导入:选择文件-选择表-DataGridView中显示-保存到数据库
导出:(这边少了一个获取当前数据库所有表按钮)-选择表-DataGridView-导出
引用如下
private int _ReturnStatus;
private string _ReturnMessage;
System.Data.DataTable table = new System.Data.DataTable();
DataSet ds = new DataSet();
System.Windows.Forms.OpenFileDialog openFileDialog1 = new OpenFileDialog();
下面进入导入代码:
选择文件(目的:获取当前Excel所有sheet)
public System.Data.DataTable SelectFile(string fileName)//获取表名
{
CmbDemand.Items.Clear();
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象,可能您的计算机未安装Excel";
return null;
}
//判断文件是否被其他进程使用
Microsoft.Office.Interop.Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnStatus = -1;
_ReturnMessage = "Excel文件处于打开状态,请保存关闭";
return null;
}
//获得所有Sheet名称
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
//循环一次获取一次表名
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
CmbDemand.Items.Add(SheetSet[i - 1]);
}
//释放Excel相关对象
workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return null;
}
选择表(目的:将当前选择的ComboBox.Text展现到DataGridView中,并且判断当前Sheet是否存在与数据库,根据sheet判断)
public System.Data.DataTable ExcelToData(string fileName, int CmbNum)//Excel导入dgv
{
//每次清空dgv1
if (dgv1.Rows.Count > 0)
{
DataTable dt = (DataTable)dgv1.DataSource;
dt.Rows.Clear();
dt.Columns.Clear();
dgv1.DataSource = dt;
}
//把EXCEL导入到DataSet
string connStr = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
try
{
conn.Open();
OleDbDataAdapter Ole = null;
string strExcel = "";
if (CmbDemand.SelectedIndex < 0)
{
MessageBox.Show("请选择需要查看的表!", "提示:");
}
else if (CmbDemand.SelectedIndex == CmbNum)
{
strExcel = string.Format("select * from [" + CmbDemand.SelectedItem.ToString() + "$] ", fileName);
Ole = new OleDbDataAdapter(strExcel, conn);
Ole.Fill(ds, fileName);
table = ds.Tables[0];
dgv1.DataSource = table;
//如果表存在,则输出存在,否则创建
string strSql = string.Format("if exists(select * from sysobjects where name = '" + CmbDemand.SelectedItem.ToString() + "')print '存在'if not exists(select * from sysobjects where name = '" + CmbDemand.SelectedItem.ToString() + "')create table " + CmbDemand.SelectedItem.ToString() + "(", fileName);
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strSql += string.Format("[{0}] varchar(400),", c.ColumnName);
}
strSql = strSql.Trim(',') + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(StrCon))
{
sqlconn.Open();
cmd = sqlconn.CreateCommand();
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
sqlconn.Close();
}
}
Ole.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return table;
}
}
保存到数据库:
private void button2_Click(object sender, EventArgs e)//保存数据到数据库
{
// 若你的dataGridView是用DataSource绑定的,可直接得到DataTable:
DataSet SelectTable = DBHelper.Query("select * from " + CmbDemand.SelectedItem.ToString() + "");
if (SelectTable.Tables[0].Columns.Count > 0)
{
DataTable dt = dgv1.DataSource as DataTable;
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(StrCon, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = "" + CmbDemand.SelectedItem.ToString() + "";//数据库中的表名
sqlbulkcopy.WriteToServer(dt);
MessageBox.Show("添加成功!");
}
else
{
MessageBox.Show("此表不存在!");
}
下面进入导出代码:
获取数据库所有表
private void Form1_Load(object sender, EventArgs e)//获取数据库所有表
{
CmbExout.Items.Clear();
DataSet TableName = DBHelper.Query("SELECT SysObjects.name AS Tablename FROM sysobjects WHERE xtype = 'U'");
foreach (DataRow dr in TableName.Tables[0].Rows)
{
CmbExout.Items.Add(dr["TableName"]);
}
}
选择表
private void CmbExout_SelectedIndexChanged(object sender, EventArgs e)//获取表
{
if (dgv1.Rows.Count > 0)
{
DataTable dt = (DataTable)dgv1.DataSource;
dt.Rows.Clear();
dt.Columns.Clear();
dgv1.DataSource = dt;
}
DataTable GetTable = DBHelper.Query(" SELECT * FROM " + CmbExout.Text + "").Tables[0];
dgv1.DataSource = GetTable;
}
导出表(注:这里的序号是我添加的自增列,因为没有绑定数据源,所有导出时我给他输出,还有当表中有隐藏列的时候会自动判断不进行导出)
public void DataToExcel(DataGridView dataGridView1, int ParaStartCol) //保存数据到Excel (导出于Excel方法)
{
//导出到execl
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)
{
//没有数据的话就不往下执行
if (dataGridView1.Rows.Count == 0)
return;
// 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)
{
MessageBox.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;
// int m = 0, n = 0;
//生成Excel中列头名称
int iCol = 0;
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
if (i == 0)
{
if (ParaStartCol != 0)
{
excel.Cells[1, iCol + 1] = "序号";
iCol++;
}
}
else
{
if (dataGridView1.Columns[i].Visible)
{
excel.Cells[1, iCol + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名
iCol++;
}
}
}
//把DataGridView当前页的数据保存在Excel中
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
iCol = 0;
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (j == 0)
{
if (ParaStartCol != 0)
{
excel.Cells[i + 2, iCol + 1] = (i + 1).ToString();
iCol++;
}
}
else
{
if (dataGridView1[j, i].Visible)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, iCol + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, iCol + 1] = dataGridView1[j, i].Value.ToString();
}
iCol++;
}
}
}
}
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.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
// toolStripProgressBar1.Value = 0;
System.Diagnostics.Process.Start(strName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}