asp.net 使用NPOI读取excel文件

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