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 }
