**
网上能搜到的比较零碎，整理帖
**
导入比较简单代码如下
EXCEL
private void button2_Click(object sender, EventArgs e)
{
string fileName = “”;
fileName = this.textBox1.Text;
if (this.textBox1.Text != “”)
{
try
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + " ;Extended Properties=Excel 8.0";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] ";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
this.dataGridView1.DataSource = dt;
}
catch
{
MessageBox.Show(“请选择需要导入的文件”);
}
}
else
{
MessageBox.Show(“请选择Excel文件”);
}
}
DBF
private void button3_Click(object sender, EventArgs e)
{
try
{
string fileName = textBox1.Text;
if (this.textBox1.Text != “”)
{
FileInfo fi = new FileInfo(fileName);
string mulu = fi.DirectoryName;
string filename = fi.Name;
OleDbConnection conn = new OleDbConnection();string table = filePath;string connStr = @"Provider=VFPOLEDB.1;Data Source=" + mulu + ";Collating Sequence=MACHINE";conn.ConnectionString = connStr;conn.Open();string sql = @"select * from " + filename;OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);DataTable dt = new DataTable();da.Fill(dt);this.dataGridView1.DataSource = dt;}}catch{MessageBox.Show("请选择需要导入的文件");}}else{MessageBox.Show("请选择Dbf文件");}}
导出EXCEL
导出成excel比较简单可以用datatgridview控件
private void button4_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("当前无数据可导出!");}else{ExportDataToExcel(dataGridView1);}}public void ExportDataToExcel(DataGridView myDGV){string path = "";SaveFileDialog saveDialog = new SaveFileDialog();saveDialog.Title = "请选择要导出的位置";saveDialog.Filter = "Excel文件| *.xlsx;*.xls";saveDialog.ShowDialog();path = saveDialog.FileName;if (path.IndexOf(":") < 0) return; //判断是否点击取消try{Thread.Sleep(1000);StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));StringBuilder sb = new StringBuilder();//写入标题for (int k = 0; k < myDGV.Columns.Count; k++){if (myDGV.Columns[k].Visible)//导出可见的标题{//"\t"就等于键盘上的Tab,加个"\t"的意思是: 填充完后进入下一个单元格.sb.Append(myDGV.Columns[k].HeaderText.ToString().Trim() + "\t");}}sb.Append(Environment.NewLine);//换行//写入每行数值for (int i = 0; i < myDGV.Rows.Count - 1; i++){System.Windows.Forms.Application.DoEvents();for (int j = 0; j < myDGV.Columns.Count; j++){if (myDGV.Columns[j].Visible)//导出可见的单元格{sb.Append(myDGV.Rows[i].Cells[j].Value.ToString().Trim() + "\t");}}sb.Append(Environment.NewLine); //换行}sw.Write(sb.ToString());sw.Flush();sw.Close();MessageBox.Show(path + "，导出成功", "系统提示", MessageBoxButtons.OK);}catch (Exception ex){MessageBox.Show(ex.Message);}}
导出DBF
搜了大半天用datagridview如何导出，无果
只能尝试使用比较笨的方法一波三折来达到效果
这里用dataset来储存数据
public DataSet ExcelToDataSet(string filename, string tableName){filename = textBox1.Text;string strExtension = Path.GetExtension(filename);OleDbConnection myConn = null;switch (strExtension){case ".xls":myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");break;case ".xlsx":myConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel 和 Excel 的连接字符串) //"HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 break;default:myConn = null;break;}if (myConn == null){return null;}string strCom = " SELECT * FROM [" + tableName + "$]";myConn.Open();//获取Excel指定Sheet表中的信息OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);DataSet ds;ds = new DataSet();myCommand.Fill(ds, tableName);myConn.Close();return ds;}public void ExportExcelToDbf(DataTable datatable){string localFilePath = "D:\\demo\\"; //文件存储位置Console.WriteLine("Writing to: " + localFilePath + datatable.TableName + ".dbf ...");//连接字符串string sConn ="Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" + Directory.GetCurrentDirectory() + "; " +"Extended Properties=dBASE IV;";OleDbConnection conn = new OleDbConnection(sConn);conn.Open();try{//如果存在同名文件则先删除if (File.Exists(localFilePath + datatable.TableName + ".dbf")){File.Delete(localFilePath + datatable.TableName + ".dbf");}Console.WriteLine(localFilePath + datatable.TableName + ".dbf");OleDbCommand cmd;//建立Dbf对象StringBuilder sbCreate = new StringBuilder();sbCreate.Append("CREATE TABLE " + localFilePath + datatable.TableName + ".dbf (");for (int i = 0; i < datatable.Columns.Count; i++){sbCreate.Append(datatable.Columns[i].ColumnName);sbCreate.Append(" char(25)");if (i != datatable.Columns.Count - 1){sbCreate.Append(", ");}else{sbCreate.Append(')');}}Console.WriteLine("\nCreating Table ...");Console.WriteLine(sbCreate.ToString());cmd = new OleDbCommand(sbCreate.ToString(), conn);cmd.ExecuteNonQuery();//插入各行StringBuilder sbInsert = new StringBuilder();foreach (DataRow dr in datatable.Rows){sbInsert.Clear();sbInsert.Append("INSERT INTO " + localFilePath + datatable.TableName + ".dbf (");for (int i = 0; i < datatable.Columns.Count; i++){sbInsert.Append(datatable.Columns[i].ColumnName);if (i != datatable.Columns.Count - 1){sbInsert.Append(", ");}}sbInsert.Append(") VALUES (");for (int i = 0; i < datatable.Columns.Count; i++){sbInsert.Append("'" + dr[i].ToString() + "'");if (i != datatable.Columns.Count - 1){sbInsert.Append(", ");}}sbInsert.Append(')');Console.WriteLine("\nInserting lines ...");Console.WriteLine(sbInsert.ToString());cmd = new OleDbCommand(sbInsert.ToString(), conn);cmd.ExecuteNonQuery();}DataSet ds = new DataSet();OleDbDataAdapter da = new OleDbDataAdapter(cmd);da.Fill(ds);foreach (DataRow theRow in ds.Tables[0].Rows){Console.WriteLine(theRow["ID"]);}conn.Close();MessageBox.Show(localFilePath + "，导出成功", "系统提示", MessageBoxButtons.OK);}catch (Exception ex){Console.WriteLine(ex.Message);}}
