Excel2Dataset

最新推荐文章于 2024-02-29 13:56:29 发布

weixin_34253539

最新推荐文章于 2024-02-29 13:56:29 发布

阅读量86

点赞数

原文链接：http://www.cnblogs.com/evanmemo/p/9583373.html

版权

//获取用户打开的Excel文档路径
private stringkkk()
{
OpenFileDialog selectFile = new OpenFileDialog();
            selectFile.Multiselect = false;
            selectFile.Filter = "Excel Files(*.xls,*.xlsx)|*.xls;*.xlsx";
            if (selectFile.ShowDialog() != DialogResult.OK)
                return null;
            string filePath = selectFile.FileName;
return filePath ;
}

 

/// <summary>
        /// 创建Excel Table.
        /// </summary>
        /// <param name="colCount">列数</param>
        /// <returns>DataTable</returns>
        private System.Data.DataTable CreateExcelTable(int colCount)
        {
            System.Data.DataTable returnTable = new System.Data.DataTable();
            for (int i = 0; i <= colCount; i++)
                returnTable.Columns.Add("col" + i.ToString(), typeof(string));
            return returnTable;
        }

        /// <summary>
        /// 根据Excel路径，读取数据至DataSet.
        /// </summary>
        /// <param name="excelPath">Excel Path</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetFromExcel(string excelPath)
        {
            DataSet resultDS = new DataSet();
            Aspose.Cells.Workbook excelBook = new Aspose.Cells.Workbook();
            excelBook.Open(excelPath);


            // get the rows and insert into dataset.
            Aspose.Cells.Worksheet excelSheet = excelBook.Worksheets[0];
            if (!excelSheet.IsVisible)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏的Sheet:[{0}],请检查！", excelSheet.Name), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return resultDS;
            }

            Aspose.Cells.Cells excelValues = excelSheet.Cells;
            foreach (Row r in excelValues.Rows)
            {
                if (r.IsHidden)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏行，行号:[{0}],请检查！", r.Index + 1), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return resultDS;
                }
            }
            foreach (Column c in excelValues.Columns)
            {
                if (c.IsHidden)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏列，列号:[{0}],请检查！", (char)(c.Index + 65)), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return resultDS;
                }
            }
            int rowCount = excelValues.MaxRow;
            int colCount = excelValues.MaxColumn;

            System.Data.DataTable excelTable = CreateExcelTable(colCount);
            resultDS.Tables.Add(excelTable);
            for (int i = 0; i <= rowCount; i++)
            {
                //如果前5栏为空的话，则忽略添加新行。
                if (Convert.ToString(excelValues[i, 0].Value) == ""
                    && Convert.ToString(excelValues[i, 1].Value) == ""
                    && Convert.ToString(excelValues[i, 2].Value) == ""
                    && Convert.ToString(excelValues[i, 3].Value) == ""
                    && Convert.ToString(excelValues[i, 4].Value) == "")
                    continue;
                DataRow row = excelTable.NewRow();
                for (int j = 0; j <= colCount; j++)
                {
                    if (excelValues[i, j].Value == null)
                        row[j] = "";
                    else
                        row[j] = excelValues[i, j].Value.ToString();
                }
                excelTable.Rows.Add(row);
            }
            return resultDS;
        }

        /// <summary>
        /// 根据Excel路径，读取指定Sheet表数据至DataSet.
        /// </summary>
        /// <param name="excelPath">Excel Path</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetFromExcel_SG3Nod(string excelPath,int x)
        {
            DataSet resultDS = new DataSet();
            Aspose.Cells.Workbook excelBook = new Aspose.Cells.Workbook();
            excelBook.Open(excelPath);


            // get the rows and insert into dataset.
            Aspose.Cells.Worksheet excelSheet = excelBook.Worksheets[x];
            if (!excelSheet.IsVisible)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏的Sheet:[{0}],请检查！", excelSheet.Name), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return resultDS;
            }

            Aspose.Cells.Cells excelValues = excelSheet.Cells;
            foreach (Row r in excelValues.Rows)
            {
                if (r.IsHidden)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏行，行号:[{0}],请检查！", r.Index + 1), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return resultDS;
                }
            }
            foreach (Column c in excelValues.Columns)
            {
                if (c.IsHidden)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏列，列号:[{0}],请检查！", (char)(c.Index + 65)), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return resultDS;
                }
            }
            int rowCount = excelValues.MaxRow;
            int colCount = excelValues.MaxColumn;

            System.Data.DataTable excelTable = CreateExcelTable(colCount);
            resultDS.Tables.Add(excelTable);
            for (int i = 0; i <= rowCount; i++)
            {
                //如果前5栏为空的话，则忽略添加新行。
                if (Convert.ToString(excelValues[i, 0].Value) == ""
                    && Convert.ToString(excelValues[i, 1].Value) == ""
                    && Convert.ToString(excelValues[i, 2].Value) == ""
                    && Convert.ToString(excelValues[i, 3].Value) == ""
                    && Convert.ToString(excelValues[i, 4].Value) == "")
                    continue;
                DataRow row = excelTable.NewRow();
                for (int j = 0; j <= colCount; j++)
                {
                    if (excelValues[i, j].Value == null)
                        row[j] = "";
                    else
                        row[j] = excelValues[i, j].Value.ToString();
                }
                excelTable.Rows.Add(row);
            }
            return resultDS;
        }

        /// <summary>
        /// 读取Excel第一个Sheet至DataTable
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public DataTable GetDataTableFromExcel(string excelPath)
        {
            DataSet resultDS = new DataSet();
            Aspose.Cells.Workbook excelBook = new Aspose.Cells.Workbook(excelPath);
            //excelBook.Open(excelPath);

            // get the rows and insert into dataset.
            Aspose.Cells.Worksheet excelSheet = excelBook.Worksheets[0];
            Aspose.Cells.Cells excelValues = excelSheet.Cells;
            int rowCount = excelValues.MaxRow + 1;
            int colCount = excelValues.MaxColumn + 1;
            return excelValues.ExportDataTable(0, 0, rowCount, colCount);

        }