DataTable转出到Excel

DataTable转出到Excel   

2009-11-11 10:11:25|  分类： c#编程 |  标签： |举报 |字号大中小 订阅

1 /// <summary>

 2         /// 将内存中的DataTable转成Excel

 3         /// </summary>

 4         /// <param name="excelSavePath">Excel保存路径</param>

 5         /// <param name="sourceTable">内存中的DataTable</param>

 6         /// <param name="sheetName">在Excel中保存的Sheet名称</param>

 7         public static void Table2Excel(string excelSavePath, DataTable sourceTable, string sheetName)

 8         {

 9             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelSavePath + ";" + "Extended Properties=Excel 8.0;";

10             using (OleDbConnection conn = new OleDbConnection(strConn))

11             {

12                 conn.Open();

13                 //检查表单是否已存在

14                 string[] sheets = GetSheets(conn);

15                 for (int i = 0; i < sheets.Length; i++)

16                 {

17                     if (sheets[i].Equals(sheetName, StringComparison.OrdinalIgnoreCase))

18                     {

19                         //如果存在，删除

20                         using (OleDbCommand cmd = conn.CreateCommand())

21                         {

22                             cmd.CommandText = string.Format("DROP TABLE {0} ", sheetName);

23                             cmd.ExecuteNonQuery();

24                         }

25                         break;

26                     }

27                 }

28                 //创建表单

29                 using (OleDbCommand cmd = conn.CreateCommand())

30                 {

31                     cmd.CommandText = string.Format("CREATE TABLE {0} ({1})", sheetName, BuildColumnsString(sourceTable));

32                     cmd.ExecuteNonQuery();

33                 }

34                 using (OleDbDataAdapter da = new OleDbDataAdapter(string.Format("SELECT * FROM {0}", sheetName), conn))

35                 {

36                     da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

37                     OleDbCommandBuilder cb = new OleDbCommandBuilder(da);

38                     DataTable dataTable = new DataTable(sheetName);

39                     //读取表单（空表）

40                     da.Fill(dataTable);

41                     //为空表写数据

42                     foreach (DataRow sRow in sourceTable.Rows)

43                     {

44                         DataRow nRow = dataTable.NewRow();

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

46                         {

47                             if (sRow[i] is System.Byte[])

48                                 nRow[i] = "二进制数据";

49                             else

50                                 nRow[i] = sRow[i];

51                         }

52                         dataTable.Rows.Add(nRow);

53                     }

54                     //更新表单

55                     da.Update(dataTable);

56                 }

57             }

58 

Code

/// <summary>

        /// 从<paramref name="sourceTable"/>构建字段字符串

        /// </summary>

        /// <param name="sourceTable"></param>

        /// <returns></returns>

        private static string BuildColumnsString(DataTable sourceTable)

        {

            StringBuilder sb = new StringBuilder();

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

            {

                if (i > 0)

                {

                    sb.Append(",");

                }

                string colName = sourceTable.Columns[i].ColumnName;

                sb.Append(colName);

                sb.Append("_ ");//为了避免系统关键字，将所有字段名后添加下划线

                sb.Append(SwitchToSqlType(sourceTable.Columns[i]));

            }

            return sb.ToString();

        }

        /// <summary>

        /// 将<paramref name="column"/>的DataType转成数据库关键字

        /// </summary>

        /// <param name="column"></param>

        /// <returns></returns>

        private static string SwitchToSqlType(DataColumn column)

        {

            string TypeFullName = column.DataType.FullName;

            switch (TypeFullName)

            {

                case "System.Int32":

                    return "INTEGER";

                case "System.Int16":

                    return "SMALLINT";

                case "System.String":

                    return string.Format("VARCHAR ({0})", column.MaxLength);

                case "System.Int64":

                    return "BIGINT";

                case "System.Double":

                case "System.Float":

                case "System.Single":

                    return "REAL";

                case "System.Numeric":

                    return "NUMERIC";

                case "System.DateTime":

                    return "DATETIME";

                case "System.Decimal":

                    return "Decimal";

                default:

                    return "VARCHAR(50)";

            }

        }

        /// <summary>

        /// 获取EXCEL的所有表单

        /// </summary>

        /// <param name="conn"></param>

        /// <returns></returns>

        private static string[] GetSheets(OleDbConnection conn)

        {

            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等

            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            //包含excel中表名的字符串数组

            string[] strTableNames = new string[dtSheetName.Rows.Count];

            for (int k = 0; k < dtSheetName.Rows.Count; k++)

            {

                DataRow row = dtSheetName.Rows[k];

                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();

            }

            return strTableNames;