欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

asp.net 使用NPOI读取excel文件

程序员文章站 2023-11-22 08:15:52
asp.net 使用NPOI读取excel文件内容 NPOI下载地址:NPOI public class ExcelHelper { /// /// 读取Excel文件数据到DataSet,一个Sheet对应一个DataTable /// ///

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;
        }
    }
}