1、从Excel导入
private void btnImport_Click(object sender, EventArgs e)//从Excel导入
{
DataTable dt = new DataTable();
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "EXCEL2003文件|*.xls|EXCEL2007文件|*.xlsx";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
dt = GetExcelDataTable(openFileDialog.FileName, "sheet");
ImportExcel(dt);
}
}
Excel导入
1 // <summary>
2 /// 通过上传文件将Excel数据保存到DataTable临时表中
3 /// </summary>
4 /// <param name="path">路径</param>
5 /// <param name="tname">excel中表名</param>
6 /// <returns></returns>
7 public static DataTable GetExcelDataTable(string path, string tname)
8 {
9 /*Office 2007*/
10 string ace = "Microsoft.ACE.OLEDB.12.0";
11 /*Office 97 - 2003*/
12 string jet = "Microsoft.Jet.OLEDB.4.0";
13 string xl2007 = "Excel 12.0 Xml";
14 string xl2003 = "Excel 8.0";
15 string imex = "IMEX=1";
16 /* csv */
17 string text = "text";
18 string fmt = "FMT=Delimited";
19 string hdr = "Yes";
20 string conn = "Provider={0};Data Source={1};Extended Properties="{2};HDR={3};{4}";";
21 string select = string.Format("SELECT * FROM [{0}$]", tname);
22 //string select = sql;
23 string ext = Path.GetExtension(path);
24 OleDbDataAdapter oda;
25 DataTable dt = new DataTable("data");
26 switch (ext.ToLower())
27 {
28 case ".xlsx":
29 conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hdr, imex);
30 break;
31 case ".xls":
32 conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hdr, imex);
33 break;
34 case ".csv":
35 conn = String.Format(conn, jet, Path.GetDirectoryName(path), text, hdr, fmt);
36 //sheet = Path.GetFileName(path);
37 break;
38 default:
39 throw new Exception("File Not Supported!");
40 }
41 OleDbConnection con = new OleDbConnection(conn);
42 con.Open();
43 //select = string.Format(select, sql);
44 oda = new OleDbDataAdapter(select, con);
45 oda.Fill(dt);
46 con.Close();
47 return dt;
48 }
GetExcelDataTable
2、关闭窗体是关闭进程
private void PrintMain_FormClosing(object sender, FormClosingEventArgs e)
{
if (MessageBox.Show("您确定要取消登录吗?", "信息提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//关闭某个进程
this.Dispose();
this.Close();
Application.Exit();
}
}
FormClosing(object sender, FormClosingEventArgs e)
3、获取所选分类
1 /// <summary>
2 /// 获取所选分类
3 /// </summary>
4 /// <returns>分类字符串</returns>
5 private string GetCategory()
6 {
7 //string s = string.Empty;
8 StringBuilder strbuild = new StringBuilder();
9 strbuild.Append("('");
10 for (int i = 0; i < clb_category.Items.Count; i++)
11 {
12 if (clb_category.GetItemChecked(i))
13 {
14 strbuild.Append(clb_category.GetItemText(clb_category.Items[i]));
15 strbuild.Append("','");
16 }
17 }
18 strbuild.Remove(strbuild.Length - 2, 2);
19 strbuild.Append(")");
20 return strbuild.ToString();
21 }
GetCategory
1 clb_category.DataSource = DsCateory.Tables[0];
2 clb_category.ValueMember = "cbm";
3 clb_category.DisplayMember = "c";
绑定
4、DataTable转换成XML
1 private string SerializeDataTableXml(DataTable pDt)
2 {
3 StringBuilder sb = new StringBuilder();
4 XmlWriter writer = XmlWriter.Create(sb);
5 XmlSerializer serializer = new XmlSerializer(typeof(DataTable));
6 serializer.Serialize(writer, pDt);
7 writer.Close();
8 sb.Replace('<', '《');
9 sb.Replace('>', '》');
10 return sb.ToString();
11 }
SerializeDataTableXml
5、XML转换成DataTable
1 public int ConvertXMLToDataSet(string xmlData, string operation)
2 {
3 StringBuilder sb = new StringBuilder(xmlData);
4 //bool statue = false;
5 StringReader stream = null;
6 XmlTextReader reader = null;
7 sb.Replace('《', '<');
8 sb.Replace('》', '>');
9 int n = 0;
10 DataSet xmlDS = new DataSet();
11 stream = new StringReader(sb.ToString());
12 reader = new XmlTextReader(stream);
13 xmlDS.ReadXml(reader);
14
15 switch (operation)
16 {
17 case "aa": break;
18 case "bb": break;
19 }
20 if (reader != null)
21 reader.Close();
22 return n;
23 }
ConvertXMLToDataSet
6、导出到DBF
1 class DbfHelp
2 {
3 public DbfHelp()
4 {
5 //
6 // TODO: 在此处添加构造函数逻辑
7 //
8 }
9 private string _ErrInfo = "";
10 private static string tablename = string.Empty;
11 public string ErrInfo
12 {
13 get { return _ErrInfo; }
14 }
15 //filename实际上是一个目录
16 public bool Create(string filename, string mTableName)
17 {
18 bool r = false;
19 //filename = filename + mTableName;
20 tablename = mTableName;
21 string outconnstring = string.Format("Provider = Microsoft.Jet.OLEDB.4.0 ;Data Source ={0};Extended Properties=dBASE IV;", filename);
22 string sqlt = "CREATE TABLE " + mTableName + "(" +
23 "aaa char(15)," +
24 "bbb char(8)," +
25 "ccc varchar(14)," +
26 "dddc har(64)," +
27 "eee char(18)," +
28 "fff char(2)," +
29
30 ")";
31 OleDbConnection outConn = new OleDbConnection(outconnstring);
32 OleDbCommand dc = outConn.CreateCommand();
33 try
34 {
35
36 outConn.Open();
37 dc.CommandType = CommandType.Text;
38 dc.CommandText = sqlt;
39 dc.ExecuteNonQuery();
40 r = true;
41
42 }
43 catch (Exception c)
44 {
45 _ErrInfo = c.Message;
46 }
47 finally
48 {
49 dc.Dispose();
50 if (outConn.State == System.Data.ConnectionState.Open)
51 outConn.Close();
52 outConn.Dispose();
53 }
54 return r;
55 }
56
57 //五个参数,dt是含有需要导出数据的DataTable,strExportPath是导出路径,strExportFile是导出文件名,要带有.dbf的后缀,strStructFile是标准库的文件名,含有路径,prgBar是用来在界面上显示导出进度的。
58
59 public static void dbfExport(string filename, DataTable dt)
60 {
61 string outconnstring = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + @"/;Extended Properties=""dBASE IV;HDR=Yes;"";";
62 OleDbConnection outConn = new OleDbConnection(outconnstring);
63
64 int successc = 0;
65 int failc = 0;
66 //循环读取dt的数据添加到dt,注意方法,还有一个就是null值的处理,具体要根据你的dbf库结构来设定。
67 //我的dbf库主要就是两种字段,一种是字符型,一种是整型
68 for (int i = 0; i < dt.Rows.Count; i++)
69 {
70 DataRow dr = dt.Rows[i];
71 #region
72 StringBuilder strSql = new StringBuilder();
73 strSql.Append("insert into " + tablename + "(");
74 strSql.Append("aaa,bbb,ccc,ddd,eee,fff)");
75 strSql.Append(" values (");
76 //strSql.Append(":aaa,:bbb,:ccc,:ddd,:eee)");
77 strSql.Append(":aaa,:bbb,:ccc,:ddd,:eee,fff)");
78
79 OleDbParameter[] parameters = {
80 new OleDbParameter(":aaa", OleDbType.VarChar,15),
81 new OleDbParameter(":bbb", OleDbType.VarChar,8),
82 new OleDbParameter(":ccc", OleDbType.VarChar,14),
83 new OleDbParameter(":ddd", OleDbType.VarChar,64),
84 new OleDbParameter(":eee", OleDbType.VarChar,18),
85 new OleDbParameter(":fff", OleDbType.VarChar,2),};
86 #endregion
87
88 for (int j = 0; j < dt.Columns.Count; j++)
89 {
90 //下面这段代码我做点解释,对于dr[j]的赋值,首先判断dt.Rows[i][dt1.Colums[j].ColumnName.ToString()]的值是否为null,
91 //如果是null,则赋值为DBNull.Value;如果不是null,则插入原始值dt.Rows[i][dt1.Colums[j].ColumnName.ToString()]
92 //直接赋值null是不可以的,null主要用于对象类的数据,DataTable数据库中的null应该用DBNull.Value来代替
93 dr[j] = dt.Rows[i][dt.Columns[j].ColumnName.ToString()] == null ? DBNull.Value : dt.Rows[i][dt.Columns[j].ColumnName.ToString()];
94 //condition.Append(dr[j].ToString().TrimEnd() + "','");
95 parameters[j].Value = dr[j].ToString().TrimEnd();
96 }
97 if (outConn.State == ConnectionState.Closed)
98 {
99 outConn.Open();
100 if (ExecuteSql(outconnstring, strSql.ToString(), parameters) > 0)
101 {
102 successc++;
103 }
104 else
105 {
106 failc++;
107 }
108 }
109 else
110 {
111 if (ExecuteSql(outconnstring, strSql.ToString(), parameters) > 0)
112 {
113 successc++;
114 }
115 else
116 {
117 failc++;
118 }
119 }
120 }
121 MessageBox.Show("成功插入" + successc + "条,失败" + failc + "条!");
122
123 }
124
125 /// <summary>
126 /// 执行SQL语句,带参数
127 /// </summary>
128 /// <param name="SQLString"></param>
129 /// <param name="cmdParms"></param>
130 /// <returns></returns>
131 public static int ExecuteSql(string con, string SQLString, params OleDbParameter[] cmdParms)
132 {
133 using (OleDbConnection connection = new OleDbConnection(con))
134 {
135 using (OleDbCommand cmd = new OleDbCommand())
136 {
137 try
138 {
139 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
140 int rows = cmd.ExecuteNonQuery();
141 cmd.Parameters.Clear();
142 return rows;
143 }
144 catch (System.Data.OleDb.OleDbException E)
145 {
146 throw new Exception(E.Message);
147 }
148 }
149 }
150 }
151 /// <summary>
152 /// 参数准备
153 /// </summary>
154 /// <param name="cmd"></param>
155 /// <param name="conn"></param>
156 /// <param name="trans"></param>
157 /// <param name="cmdText"></param>
158 /// <param name="cmdParms"></param>
159 private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
160 {
161 if (conn.State != ConnectionState.Open)
162 conn.Open();
163 cmd.Connection = conn;
164 cmd.CommandText = cmdText;
165 if (trans != null)
166 cmd.Transaction = trans;
167 cmd.CommandType = CommandType.Text;//cmdType;
168 if (cmdParms != null)
169 {
170 foreach (OleDbParameter parm in cmdParms)
171 cmd.Parameters.Add(parm);
172 }
173 }
174 }
Class DBFHelper
1 DbfHelp dh = new DbfHelp();
2 string mTableName = txtFileName.Text.ToString();//名字必须与表名字相同
3 FolderBrowserDialog fbd = new FolderBrowserDialog();
4 fbd.Description = "选择dBase IV文件保存的文件夹";
5 fbd.SelectedPath = System.Environment.CurrentDirectory;
6 fbd.ShowNewFolderButton = true;
7 string sqlstr = txtSqlStr.Text.ToString();
8 if (sqlstr.Trim()!=""&&mTableName.Trim()!="")
9 {
10 try
11 {
12 OracleHelper or = new OracleHelper();
13 DataSet dsdata = or.Method(sqlstr);
14 if (fbd.ShowDialog() == DialogResult.OK)
15 {
16 if (System.IO.File.Exists(fbd.SelectedPath + "\" + mTableName + ".dbf") == true)
17 {
18 System.IO.File.Delete(fbd.SelectedPath + "\" + mTableName + ".dbf");
19 }
20 if (dh.Create(fbd.SelectedPath, mTableName + ".dbf"))//创建文件成功
21 {
22 DbfHelp.dbfExport(fbd.SelectedPath, dsdata.Tables[0]);
23 MessageBox.Show("恭喜,导出成功!");
24 }
25 }
26 }
27 catch( Exception ex )
28 {
29 MessageBox.Show(ex.ToString());
30 }
31 }
32 else
33 {
34 MessageBox.Show("请输入完整信息!");
35 }
ExportToDBF
7、插入请选择
1 public void insertFirst(DataTable dt, string typeID, string typeName)
2 {
3 DataRow dr = dt.NewRow();
4 dr[typeID] = "0";
5 dr[typeName] = "-请选择-";
6 dt.Rows.InsertAt(dr, 0);
7 }
insertFirst
8、为第一列插入选择框
1 dgv_PrintData.Columns.Clear();
2 p_checkboxcolumn.HeaderText = "选择";
3 dgv_PrintData.Columns.Add(p_checkboxcolumn);
4
5 dgv_PrintData.DataSource = dt;
View Code
9、将空值转换为数据库类型Null
1 foreach (OracleParameter parm in parameters)
2 {
3 if (parm.Value == null)
4 parm.Value = DBNull.Value;
5 }
NullToNull
原文链接: https://www.cnblogs.com/bindot/p/cyff.html
欢迎关注
微信关注下方公众号,第一时间获取干货硬货;公众号内回复【pdf】免费获取数百本计算机经典书籍
原创文章受到原创版权保护。转载请注明出处:https://www.ccppcoding.com/archives/96104
非原创文章文中已经注明原地址,如有侵权,联系删除
关注公众号【高性能架构探索】,第一时间获取最新文章
转载文章受原作者版权保护。转载请注明原作者出处!