上传文件并读取excel总结

最近做的项目需要上传excel并读取里面的数据，尝试了几个方法，发现：

1、js读取excel是不靠谱的，因为只能在ie下进行，需要用户安装了excel程序，并且要用户做安全设置的更改；

2、用input标签上传文件是不靠谱的，只有在ie下面，进行浏览器安全设置让js能够获取到路径。

最优的办法是：

1、在aspx页面，用aspx的FileUpload控件：

<asp:FileUpload ID="FileUpload1" runat="server" Width="475px" />

2、然后可以在cs文件里：

string filepath = FileUpload1.PostedFile.FileName;
string filename = filepath.Substring(filepath.LastIndexOf("\\") + 1);
serverpath = Server.MapPath("../files/UploadFile/") + filename;
FileUpload1.PostedFile.SaveAs(serverpath);

3、解析用NPOI解析：

    DataTable GetDataFromExcelByNPOI()
    {
        IWorkbook workbook;

        if (serverpath == "") return null;

        string fileType = Path.GetExtension(serverpath);

        using (FileStream file = new FileStream(serverpath, FileMode.Open, FileAccess.Read))
        {
            if (fileType == ".xls")
            {
                workbook = new HSSFWorkbook(file);
            }
            else if (fileType == ".xlsx")
            {
                workbook = new XSSFWorkbook(file);
            }
            else
            {
                return null;
            }


            ISheet sheet = workbook.GetSheetAt(0);//取第一个表
            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                table.Columns.Add(column);
            }

            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = GetCellValue(row.GetCell(j));
                    }
                }

                table.Rows.Add(dataRow);
            }
            return table;
        }

    }

    private object GetCellValue(ICell cell)
    {
        object value = null;
        try
        {
            if (cell.CellType != CellType.Blank)
            {
                switch (cell.CellType)
                {
                    case CellType.Numeric:
                        // Date comes here
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            value = cell.DateCellValue;
                        }
                        else
                        {
                            // Numeric type
                            value = cell.NumericCellValue;
                        }
                        break;
                    case CellType.Boolean:
                        // Boolean type
                        value = cell.BooleanCellValue;
                        break;
                    case CellType.Formula:
                        value = cell.CellFormula;
                        break;
                    default:
                        // String type
                        value = cell.StringCellValue;
                        break;
                }
            }
        }
        catch (Exception)
        {
            value = "";
        }

        return value;
    }