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 }
转载于:https://www.cnblogs.com/zhangyunyun/archive/2013/02/18/2915770.html
相关资源：JAVA上百实例源码以及开源项目
