常见的方法《二》

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

 

posted @ 2013-07-16 16:44  FengLu-1  阅读(420)  评论(0)  编辑  收藏  举报