ASP.NET中 Execl导出的六种方法实例(3)


        private void CreateExcelFile(string filePath, DataTable dt)

        {

            if (File.Exists(filePath))

            {

                File.Delete(filePath);

            }

            OleDbConnection oleDbConn = new OleDbConnection();

            OleDbCommand oleDbCmd = new OleDbCommand();

 

            try

            {

                string sSql = "";

                oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";

                oleDbConn.Open();

                oleDbCmd.CommandType = CommandType.Text;

                oleDbCmd.Connection = oleDbConn;

                //写列名

                sSql = "CREATE TABLE sheet1(";

                for (int i = 0; i < dt.Columns.Count; i++)

                {

                    if (i < dt.Columns.Count - 1)

                    {

                        if (dt.Columns[i].DataType.Name == "String")

                        {

                            sSql += "[" + dt.Columns[i].ColumnName + "] Text,";

                        }

                        else if (dt.Columns[i].DataType.Name == "DateTime")

                        {

                            sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";

                        }

                        else

                        {

                            sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";

                        }

                    }

                    else

                    {

                        if (dt.Columns[i].DataType.Name == "String")

                        {

                            sSql += "[" + dt.Columns[i].ColumnName + "] Text)";

                        }

                        else if (dt.Columns[i].DataType.Name == "DateTime")

                        {

                            sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";

                        }

                        else

                        {

                            sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";

                        }

                    }

                }

                oleDbCmd.CommandText = sSql;

                oleDbCmd.ExecuteNonQuery();

 

                for (int j = 0; j < dt.Rows.Count; j++)

                {

                    sSql = "INSERT INTO sheet1 VALUES(";

                    for (int i = 0; i < dt.Columns.Count; i++)

                    {

                        if (i < dt.Columns.Count - 1)

                        {

                            if (DBNull.Value.Equals(dt.Rows[j][i]))

                            {

                                sSql += "NULL,";

                            }

                            else

                            {

                                if (dt.Columns[i].DataType.Name == "Decimal")

                                {

                                    sSql += dt.Rows[j][i].ToString() + ",";

                                }

                                else

                                {

                                    sSql += "'" + dt.Rows[j][i].ToString() + "',";

                                }

                            }

                        }

                        else

                            if (DBNull.Value.Equals(dt.Rows[j][i]))

                            {

                                sSql += "NULL)";

                            }

                            else

                            {

                                if (dt.Columns[i].DataType.Name == "Decimal")

                                {

                                    sSql += dt.Rows[j][i].ToString() + ")";

                                }

                                else

                                {

                                    sSql += "'" + dt.Rows[j][i].ToString() + "')";

                                }

                            }

                    }

                    oleDbCmd.CommandText = sSql;

                    oleDbCmd.ExecuteNonQuery();

                }

            }

            catch (System.Exception ex)

            {

                throw ex;

            }

            finally

            {

                //断开连接

                oleDbCmd.Dispose();

                oleDbConn.Close();

                oleDbConn.Dispose();

            }

        }


        方法五:

复制代码 代码如下:

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wjgypx.html