BN089-BS开发框架MVC4版/source20131023/Zephyr.Net/Zephyr.Utils/Document/Excel/ExcelLibrary/DataSetHelper.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.Data;
using System.IO;
using Zephyr.Utils.ExcelLibrary.SpreadSheet;

namespace Zephyr.Utils.ExcelLibrary
{
    /// <summary>
    /// Provides simple way to convert Excel workbook into DataSet
    /// </summary>
    public sealed class DataSetHelper
    {
        /// <summary>
        /// Populate all data (all converted into String) in all worksheets 
        /// from a given Excel workbook.
        /// </summary>
        /// <param name="filePath">File path of the Excel workbook</param>
        /// <returns>DataSet with all worksheet populate into DataTable</returns>
        public static DataSet CreateDataSet(String filePath)
        {
            DataSet ds = new DataSet();
            Workbook workbook = Workbook.Load(filePath);
            foreach (Worksheet ws in workbook.Worksheets)
            {
                DataTable dt = PopulateDataTable(ws);
                ds.Tables.Add(dt);
            }
            return ds;
        }

        /// <summary>
        /// Populate data (all converted into String) from a given Excel 
        /// workbook and also work sheet name into a new instance of DataTable.
        /// Returns null if given work sheet is not found.
        /// </summary>
        /// <param name="filePath">File path of the Excel workbook</param>
        /// <param name="sheetName">Worksheet name in workbook</param>
        /// <returns>DataTable with populate data</returns>
        public static DataTable CreateDataTable(String filePath, String sheetName)
        {
            Workbook workbook = Workbook.Load(filePath);
            foreach (Worksheet ws in workbook.Worksheets)
            {
                if (ws.Name.Equals(sheetName))
                    return PopulateDataTable(ws);
            }
            return null;
        }

        private static DataTable PopulateDataTable(Worksheet ws)
        {
            CellCollection Cells = ws.Cells;

            // Creates DataTable from a Worksheet
            // All values will be treated as Strings
            DataTable dt = new DataTable(ws.Name);

            // Extract columns
            for (int i = 0; i <= Cells.LastColIndex; i++)
                dt.Columns.Add(Cells[0, i].StringValue, typeof(String));