C#在FORM页面上将excel表格从SQL数据库导出，导入txt格式表格

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 using Excel = Microsoft.Office.Interop.Excel;
 10 using System.Data.SqlClient;
 11 
 12 namespace FormPhoneWork
 13 {
 14     public partial class takeinto : Form
 15     {
 16         public takeinto()
 17         {
 18             InitializeComponent();
 19 
 20         }
 21 
 22         private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
 23         {
 24 
 25         }
 26 
 27         public int DataTabletoExcel(DataGridView tmpDataTable)
 28         {
 29             if (tmpDataTable.RowCount == 0)
 30                 return 1;
 31             try
 32             {
 33                 saveFileDialog1.Filter = "Execl files (*.xlsx)|*.xlsx";
 34                 saveFileDialog1.FilterIndex = 0;
 35                 saveFileDialog1.RestoreDirectory = true;
 36                 saveFileDialog1.Title = "导出文件保存路径";
 37                 saveFileDialog1.FileName = null;
 38                 saveFileDialog1.ShowDialog();
 39                 string FileName = saveFileDialog1.FileName;
 40 
 41                 if (FileName != "")
 42                 {
 43 
 44                     int rowNum = tmpDataTable.Rows.Count;
 45 
 46                     int columnNum = tmpDataTable.Columns.Count;
 47                     int rowIndex = 1;
 48                     int columnIndex = 0;
 49 
 50                     Excel.Application xlApp = new Excel.Application();
 51                     xlApp.DefaultFilePath = "";
 52                     xlApp.DisplayAlerts = true;
 53                     xlApp.SheetsInNewWorkbook = 1;
 54                     Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
 55 
 56                     foreach (DataGridViewColumn dc in tmpDataTable.Columns)
 57                     {
 58                         columnIndex++;
 59 
 60                         xlApp.Cells[rowIndex, columnIndex] = dc.HeaderText;
 61                     }
 62 
 63                     for (int i = 0; i < rowNum; i++)
 64                     {
 65 
 66                         rowIndex++;
 67                         columnIndex = 0;
 68                         for (int j = 0; j < columnNum; j++)
 69                         {
 70                             columnIndex++;
 71                             xlApp.Cells[rowIndex, columnIndex] = tmpDataTable[j, i].Value;
 72                         }
 73 
 74                     }
 75                     xlBook.SaveCopyAs(FileName);
 76                     MessageBox.Show("数据已经成功导出到：" + saveFileDialog1.FileName.ToString(),
 77                         "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
 78 
 79                 }
 80                 return 0;
 81             }
 82             catch (System.Exception)
 83             {
 84                 return 2;
 85             }
 86         }
 87 
 88         private void button1_Click(object sender, EventArgs e)
 89         {
 90             string str = "select * from " + textBox1.Text;
 91             string source = "server=(local);" + "integrated security=SSPI;" + "database=PhoneWork";
 92             SqlConnection conn = new SqlConnection(source);
 93             DataSet ds = new DataSet();
 94             SqlDataAdapter da = new SqlDataAdapter(str, source);
 95             da.Fill(ds, textBox1.Text);
 96             dataGridView1.AutoGenerateColumns = true;
 97             dataGridView1.DataSource = ds;
 98             dataGridView1.DataMember = textBox1.Text;
 99             DataTabletoExcel(dataGridView1);
100         }
101 
102         private void textBox1_TextChanged(object sender, EventArgs e)
103         {
104 
105         }
106 
107         private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
108         {
109 
110         }
111 
112 
113 
114         private void button2_Click(object sender, EventArgs e)
115         {
116             openFileDialog1.ShowDialog();
117             DataExceltoTable(dataGridView1);
118 
119 
120         }
121         public void DataExceltoTable(DataGridView tmpDataTable)
122         {
123             string source = "server=(local);" + "integrated security=SSPI;" + "database=PhoneWork";
124            // string str = "truncate table " + textBox1.Text;//清空原来的表格
125             SqlConnection conn = new SqlConnection(source);
126             conn.Open();
127             try
128             {
129                // SqlCommand card = new SqlCommand(str, conn);
130                 //card.ExecuteNonQuery();
131                 SqlCommand com = new SqlCommand("BULK INSERT PhoneWork.dbo."+ textBox1.Text + " FROM '" + openFileDialog1.FileName +
132                     "'WITH  (FIELDTERMINATOR = ',',ROWTERMINATOR= '\n')", conn);
133                 com.ExecuteNonQuery();
134                 MessageBox.Show("导入数据成功");
135 
136             }
137 
138             catch(Exception e)
139             {
140                 MessageBox.Show(e.ToString());
141             }
142         }
143     }
144 }