使用Net.SourceForge.Koogra.Excel读取Excel,无需安装Excel

我是通过这篇文章来学习的,网上一搜好几个:)

http://hi.baidu.com/daijun2007/blog/item/eb39373e54f6e33a71cf6c50.html

下面是自己写的

            var type = System.IO.Path.GetExtension(FileUpload1.FileName);
            Regex rNumber = new Regex(@"^\d+(\.\d+)?|[A-Z]$");  //@"^\d+(\.\d+)?$"  //"^[0-9]*[1-9][0-9]*|[A-Z]$"
            Regex rChinese = new Regex(@"^[\u4e00-\u9fa5]+$"); //过滤掉中文
            Regex rOnlyNumber = new Regex(@"^\d+(\.\d+)?$");
            #region 读取Excel文件到DataTable
            DataTable excelData = new DataTable();
            if (type.Equals(".xlsx") || type.Equals(".xls"))
            {
                var path = Request.MapPath("../../UploadFile/PdMainDayPlan/") + "test" + type;//文件在服务器路径
                FileInfo fileinfo = new FileInfo(path);
                if (fileinfo.Exists)
                    fileinfo.Delete();
                HttpPostedFile file = this.FileUpload1.PostedFile;
                file.SaveAs(path);
                Workbook wb = new Workbook(path);
                Worksheet xSheet = wb.Sheets[0];
                var paiBie = 0;//设置牌别所处行的初始值
                for (int r = xSheet.Rows.FirstRow; r <= xSheet.Rows.LastRow; ++r)
                {
                    if (xSheet.Rows[(uint)r].Cells[0].Value != null && xSheet.Rows[(uint)r].Cells[0].Value + "" == "牌别")
                    {
                        paiBie = r;
                        break;
                    }
                }
                for (int c = xSheet.Rows[(uint)paiBie].Cells.FirstCol; c <= xSheet.Rows[(uint)paiBie].Cells.LastCol; ++c)
                {
                    Cell cell = xSheet.Rows[(uint)paiBie].Cells[(byte)c];
                    if (cell != null)
                    {
                        if (excelData.Columns.Contains(cell.Value + ""))
                            continue;
                        if (!rNumber.IsMatch(cell.Value + "") && cell.Value + "" != "牌别")
                            continue;
                        excelData.Columns.Add(cell.Value + "", typeof(string));
                    }
                }
                excelData.AcceptChanges();
                for (int r = paiBie + 1; r <= xSheet.Rows.LastRow; ++r)
                {
                    if (xSheet.Rows[(uint)r].Cells[0] != null && xSheet.Rows[(uint)r].Cells[0].Value != null &&
                        rOnlyNumber.IsMatch(xSheet.Rows[(uint)r].Cells[0].Value + ""))
                    {
                        DataRow dr = excelData.NewRow();
                        for (int c = 0; c <= excelData.Columns.Count - 1; ++c)
                        {
                            dr[c] = xSheet.Rows[(uint)r].Cells[(byte)c] == null
                                        ? string.Empty
                                        : xSheet.Rows[(uint)r].Cells[(byte)c].Value + "";
                        }
                        excelData.Rows.Add(dr);
                    }
                }
                excelData.AcceptChanges();
            }
            else
            {
                ContextHelper.ShowMessage(BtnOk, "请选择一个Excel文件");
                return;
            }
            #endregion