读取Excel到DataSet

需要引用的3个dll

 

 

 

 /// <summary>
        /// 读取Excel到DataTable
        /// </summary>
        /// <param name="excelPath">Excel路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="msg">异常信息</param>
        /// <returns></returns>
        public static DataTable GetDataTableByExcel(string excelPath, string sheetname,out string msg)
        {
            msg = "";
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                ISheet sheet = workbook.GetSheet(sheetname);
                if (sheet == null)
                    return new DataTable();
                DataTable dtl = new DataTable(sheetname);
                IRow col_row = sheet.GetRow(0);
                int col_count = col_row.Cells.Count;

                for (int i = 0; i < col_count; i++)
                {
                    if (col_row.Cells[i] == null)
                    {
                        dtl.Columns.Add("", typeof(string));
                    }
                    else
                    {
                        dtl.Columns.Add(col_row.Cells[i].ToString().Trim(), typeof(string));
                    }
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow content_row = sheet.GetRow(i);

                    if (content_row == null)
                    {
                        continue;
                    }

                    DataRow newRow = dtl.NewRow();
                    dtl.Rows.Add(newRow);


                    for (int j = 0; j < col_count; j++)
                    {
                        if (content_row.GetCell(j) == null)
                        {
                            newRow[j] = DBNull.Value;
                        }
                        else
                        {
                            ICell content_cell = content_row.GetCell(j);
                            switch (content_cell.CellType)
                            {
                                case CellType.BLANK:

                                    break;
                                case CellType.BOOLEAN:
                                    break;
                                case CellType.ERROR:
                                    break;
                                case CellType.FORMULA:
                                    break;
                                case CellType.NUMERIC:
                                    if (DateUtil.IsCellDateFormatted(content_cell))
                                    {
                                        if (string.IsNullOrEmpty(content_cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss").Trim()))
                                            newRow[j] = DBNull.Value;
                                        else
                                            newRow[j] = content_cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss").Trim();
                                    }
                                    else
                                    {
                                        if (string.IsNullOrEmpty(content_cell.NumericCellValue.ToString().Trim()))
                                            newRow[j] = DBNull.Value;
                                        else
                                            newRow[j] = content_cell.NumericCellValue.ToString().Trim();
                                    }
                                    break;
                                case CellType.STRING:
                                    if (string.IsNullOrEmpty(content_cell.StringCellValue.ToString().Trim()))
                                        newRow[j] = DBNull.Value;
                                    else
                                        newRow[j] = content_cell.StringCellValue.ToString().Trim();
                                    break;
                                case CellType.Unknown:
                                    break;
                            }


                        }
                    }
                    //取前五列的值如果为空不拼数据了
                    if (col_row.Cells.Count > 4)
                    {
                        string strOne = col_row.Cells[0].ToString().Trim();
                        string strTwo = col_row.Cells[1].ToString().Trim();
                        string strThr = col_row.Cells[2].ToString().Trim();
                        string strFou = col_row.Cells[3].ToString().Trim();
                        string strFiv = col_row.Cells[4].ToString().Trim();
                        if (!string.IsNullOrEmpty(strOne) && !string.IsNullOrEmpty(strTwo) && !string.IsNullOrEmpty(strThr) && !string.IsNullOrEmpty(strFou) && !string.IsNullOrEmpty(strFiv))
                        {
                            if (string.IsNullOrEmpty(dtl.Rows[0][strOne].ToString()) && string.IsNullOrEmpty(dtl.Rows[0][strTwo].ToString()) && string.IsNullOrEmpty(dtl.Rows[0][strThr].ToString()) && string.IsNullOrEmpty(dtl.Rows[0][strFou].ToString()) && string.IsNullOrEmpty(dtl.Rows[0][strFiv].ToString()))
                            {
                                dtl.Rows.RemoveAt(0);
                                break;
                            }
                            if (string.IsNullOrEmpty(dtl.Rows[i - 1][strOne].ToString()) && string.IsNullOrEmpty(dtl.Rows[i - 1][strTwo].ToString()) && string.IsNullOrEmpty(dtl.Rows[i - 1][strThr].ToString()) && string.IsNullOrEmpty(dtl.Rows[i - 1][strFou].ToString()) && string.IsNullOrEmpty(dtl.Rows[i - 1][strFiv].ToString()))
                            {
                                dtl.Rows.RemoveAt(i - 1);
                                break;
                            }
                        }
                    }


                }

                return dtl;
            }
            catch (Exception ex)
            {
                msg = ex.ToString();
                return new DataTable();
            }
        }

        /// <summary>
        /// 读取Excel到DataSet
        /// </summary>
        /// <param name="excelPath">Excel路径</param>
        /// <param name="index">第几个开始</param>
        /// <returns></returns>
        public static DataTable GetDataSetByExcel(string excelPath, int index)
        {
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                ISheet sheet = workbook.GetSheetAt(index);
                if (sheet == null)
                    return new DataTable();
                DataTable dtl = new DataTable(sheet.SheetName);
                IRow col_row = sheet.GetRow(0);
                int col_count = col_row.Cells.Count;

                for (int i = 0; i < col_count; i++)
                {
                    if (col_row.Cells[i] == null)
                    {
                        dtl.Columns.Add("", typeof(string));
                    }
                    else
                    {
                        dtl.Columns.Add(col_row.Cells[i].ToString().Trim(), typeof(string));
                    }
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow content_row = sheet.GetRow(i);
                    if (content_row == null)
                    {
                        continue;
                    }

                    DataRow newRow = dtl.NewRow();
                    dtl.Rows.Add(newRow);

                    for (int j = 0; j < col_count; j++)
                    {
                        if (content_row.GetCell(j) == null)
                        {
                            newRow[j] = DBNull.Value;
                        }
                        else
                        {
                            ICell content_cell = content_row.GetCell(j);

                            switch (content_cell.CellType)
                            {
                                case CellType.BLANK:
                                    break;
                                case CellType.BOOLEAN:
                                    break;
                                case CellType.ERROR:
                                    break;
                                case CellType.FORMULA:
                                    break;
                                case CellType.NUMERIC:
                                    if (DateUtil.IsCellDateFormatted(content_cell))
                                    {
                                        if (string.IsNullOrEmpty(content_cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss").Trim()))
                                            newRow[j] = DBNull.Value;
                                        else
                                            newRow[j] = content_cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss").Trim();
                                    }
                                    else
                                    {
                                        if (string.IsNullOrEmpty(content_cell.NumericCellValue.ToString().Trim()))
                                            newRow[j] = DBNull.Value;
                                        else
                                            newRow[j] = (decimal)content_cell.NumericCellValue;//.ToString("G").Trim();
                                    }
                                    break;
                                case CellType.STRING:
                                    if (string.IsNullOrEmpty(content_cell.StringCellValue.ToString().Trim()))
                                        newRow[j] = DBNull.Value;
                                    else
                                        newRow[j] = content_cell.StringCellValue.ToString().Trim();
                                    break;
                                case CellType.Unknown:
                                    break;
                            }
                        }
                    }
                }

                return dtl;

            }
            catch (Exception ex)
            {
                return new DataTable();
            }

        }