• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

asp.net使用NPOI读取excel文件

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

asp.net 使用NPOI读取excel文件内容

NPOI下载地址:NPOI

public class ExcelHelper
{
    /// <summary>
    /// 读取Excel文件数据到DataSet,一个Sheet对应一个DataTable
    /// </summary>
    /// <param name="strExcelFilePath">Excel文件的物理路径</param>
    /// <returns></returns>
    public static DataSet GetDataFromExcel(string strExcelPhysicalPath, out string strError)
    {
        try
        {
            DataSet dsResult = new DataSet();
            strError = "";

            IWorkbook wbook = null;
            using (FileStream fs = new FileStream(strExcelPhysicalPath, FileMode.Open, FileAccess.Read))
            {
                if (strExcelPhysicalPath.IndexOf(".xlsx") > 0)
                {
                    wbook = new XSSFWorkbook(fs);
                }
                else
                {
                    wbook = new HSSFWorkbook(fs);
                }
            }

            for (int i = 0; i < wbook.NumberOfSheets; i++)
            {
                ISheet wsheet = wbook.GetSheetAt(i);
                if (wsheet == null) continue;

                DataTable dtSheet = GetDataFromSheet(wsheet, out strError);
                if (dtSheet != null)
                {
                    dtSheet.TableName = wsheet.SheetName.Trim();
                    dsResult.Tables.Add(dtSheet);
                }
                else
                {
                    dsResult = null;
                    break;
                }
            }
            return dsResult;
        }
        catch (Exception ex)
        {
            strError = ex.Message.ToString();
            return null;
        }
    }

    private static DataTable GetDataFromSheet(ISheet wsheet, out string strError)
    {
        try
        {
            DataTable dtResult = new DataTable();
            strError = "";

            //取sheet最大列数
            int max_column = 0;
            for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
            {
                IRow rsheet = wsheet.GetRow(i);
                if (rsheet != null && rsheet.LastCellNum > max_column)
                {
                    max_column = rsheet.LastCellNum;
                }
            }
            //给DataTable添加列
            for (int i = 0; i < max_column; i++)
            {
                dtResult.Columns.Add("A" + i.ToString());
            }

            for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
            {
                DataRow dRow = dtResult.NewRow();
                IRow rsheet = wsheet.GetRow(i);

                if (rsheet == null) continue;

                for (int j = rsheet.FirstCellNum; j < rsheet.LastCellNum; j++)
                {
                    ICell csheet = rsheet.GetCell(j);

                    if (csheet == null) continue;

                    switch (csheet.CellType)
                    {
                        case CellType.Blank:
                            dRow[j] = "";
                            break;
                        case CellType.Boolean:
                            dRow[j] = csheet.BooleanCellValue;
                            break;
                        case CellType.Error:
                            dRow[j] = csheet.ErrorCellValue;
                            break;
                        case CellType.Formula:
                            try
                            {
                                dRow[j] = csheet.NumericCellValue;

                                short format1 = csheet.CellStyle.DataFormat;
                                if (format1 == 177 || format1 == 178 || format1 == 188)
                                {
                                    dRow[j] = csheet.NumericCellValue.ToString("#0.00");
                                }
                            }
                            catch
                            {
                                dRow[j] = csheet.StringCellValue.Trim();
                            }
                            break;
                        case CellType.Numeric:
                            try
                            {
                                short format2 = csheet.CellStyle.DataFormat;
                                if (format2 == 14 || format2 == 31 || format2 == 57 || format2 == 58)
                                {
                                    dRow[j] = csheet.DateCellValue;
                                }
                                else
                                {
                                    dRow[j] = csheet.NumericCellValue;
                                }
                                if (format2 == 177 || format2 == 178 || format2 == 188)
                                {
                                    dRow[j] = csheet.NumericCellValue.ToString("#0.00");
                                }
                            }
                            catch
                            {
                                dRow[j] = csheet.StringCellValue.Trim();
                            }
                            break;
                        case CellType.String:
                            dRow[j] = csheet.StringCellValue.Trim();
                            break;
                        default:
                            dRow[j] = csheet.StringCellValue.Trim();
                            break;
                    }
                }

                dtResult.Rows.Add(dRow);
            }
            return dtResult;
        }
        catch (Exception ex)
        {
            strError = ex.Message.ToString();
            return null;
        }
    }
}

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
(转)Asp.NetMVC中身份认证和授权发布时间:2022-07-10
下一篇:
ASP.NET基础笔记发布时间:2022-07-10
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap