.net 上传Excel 并读取导入 隐藏行的处理

引用:

 

 

导入事件:

 

以前自己写的啥,都忘了 ,现在重新整理一下。

批量上传文件:

 1 private void UploadImportFile()
 2         {
 3             HttpFileCollection files = HttpContext.Current.Request.Files;
 4             var fileList = files.GetMultiple("fupd");
 5             for (int ifile = 0; ifile < fileList.Count; ifile++)
 6             {
 7 
 8                 HttpPostedFile postedfile = fileList[ifile];
 9                 string filename, fileExt;
10                 filename = System.IO.Path.GetFileName(postedfile.FileName);    //获取文件名
11                 fileExt = System.IO.Path.GetExtension(filename);    //获取文件后缀
12                 int MaxAllowUploadFileSize = 100000;    //定义允许上传文件大小
13                 string allowexts = "xls|xlsx";      //定义允许上传文件类型
14                 Regex allowext = new Regex(allowexts);
15                 if (postedfile.ContentLength < MaxAllowUploadFileSize && allowext.IsMatch(fileExt)) //检查文件大小及扩展名
16                 {
17                     postedfile.SaveAs(Server.MapPath("~/ImportBaseInfo/" + filename));    //upload为与本页面同一目录,可自行修改
18                 }
19                 else
20                 {
21                     Response.Write("<script>alert('不允许上传类型" + fileExt + "。)</script>");
22                 }
23             }
24         }

上传完之后:

 1 protected void btnImport_Click(object sender, EventArgs e)
 2         {
 3             UploadImportFile(); //将选择文件先上传至ImportBaseInfo文件夹
 4             string resultStr = "";
 5             string filePath = Server.MapPath("~/ImportBaseInfo/");
 6                 DirectoryInfo root = new DirectoryInfo(filePath);
 7                 FileInfo[] files = root.GetFiles();  //返回当前目录的文件列表
 8                 
 9                 foreach (FileInfo f in files)
10                 {
11                     string name = f.Name;
12                     string Extension = f.Extension;
13                     string fullName = f.FullName;
14                     //获取文件上传创建时间
15                     DateTime createTime = f.LastWriteTime;
16                     DateTime today = DateTime.Now;
17                     //只遍历今天上传的文件
18                     if(today.Date==createTime.Date)
19                 {
20                     if (Extension != ".xls" && Extension != ".xlsx")
21                     {
22                         MessageBox.Show(this, "您导入的\"" + name + "\"文件不正确,请确认后重试!");
23                         return;
24                     }
25 
26                     Workbook workbook = new Workbook(fullName);
27 
28                     Worksheet worksheet = workbook.Worksheets[0];
29 
30                     Cells cells = worksheet.Cells;
31 
32                     DataTable table;
33 
34                     table = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
35 
36                     
37                     string formid = string.Empty;
38                     bool main = MainData(table, ref formid);
39                     
40                 }
41                     
42 
43                 }
44             
45            //导入后删除服务器上的文件,以免下次导入时遍历所有的文件
46             //string[] filePaths = Directory.GetFiles(filePath);  
47             //foreach (string filepath in filePaths)
48             //{
49             //    File.Delete(filepath);
50             //}
51 
52            
53         }
ExportDataTableAsString只是其中一种方法,可以F12进去看看其他方法,都很好用。

 

That‘s ALLLLLLLLLLLLLLLLLLLLLLLLLL!用过两次了 很好用!

一定要下载NuGet程序包  搜Aspose.Cells~~~~~~~~

我jio得我写的注释很清楚 (叉腰!

 

 

---------------------------------------------------------分割线--------------------------------------------------------------

2022年的我长大了,需求也更过分了。

导入Excel文件之后还需要处理其中的数据,本来是个很简单的导入操作,但文件中存在隐藏的行,隐藏的行不要求我们导入数据,但怎么忽略隐藏行呢?

找了半天也没大神解答,自己试出来一个办法。IRow有个Hidden属性,但返回值是bool?。这个问号也卡住我了。解释见:不能隐式转换类型'bool?”“bool”。存在显式转换(您是否丢失了强制转换?) - cannot implicitly convert type 'bool?' to 'bool'. An explicit conversion exists (are you missing a cast?) - 开发者知识库 (itdaan.com)

 

以下是自己的代码。逻辑为:先读取第一行,将第一行先定下来,故列数就确定了;然后再一行行的填充,填充的时候判断下是否是隐藏行,如果是则跳出本次循环。

public static DataTable ExcelToDataTable(string filePath)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 1;//开始从第几行开始读取

            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数

                                //填充列
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    if (!sheet.IsColumnHidden(i)) {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            column = new DataColumn(cell.ToString());
                                            dataTable.Columns.Add(column);
                                        }
                                    }
                                }

                                //填充行
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    
                                    row = sheet.GetRow(i);
                                    bool IsRowHidden=(bool)row.Hidden;//判断隐藏列
                                    if (!IsRowHidden)
                                    {
                                        if (row == null) continue;

                                        dataRow = dataTable.NewRow();
                                        //for (int j = row.FirstCellNum; j < cellCount - 1; ++j)    //原先的
                                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                                        {

                                            cell = row.GetCell(j);
                                            if (cell == null)
                                            {
                                                dataRow[j] = "";
                                            }
                                            else
                                            {
                                                //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                                switch (cell.CellType)
                                                {
                                                    case CellType.Blank:
                                                        dataRow[j] = "";
                                                        break;
                                                    case CellType.Numeric:
                                                        short format = cell.CellStyle.DataFormat;
                                                        //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理
                                                        if (format == 14 || format == 31 || format == 57 || format == 58)
                                                            dataRow[j] = cell.DateCellValue;
                                                        else
                                                            dataRow[j] = cell.NumericCellValue;
                                                        break;
                                                    case CellType.String:
                                                        dataRow[j] = cell.StringCellValue;
                                                        break;
                                                    case CellType.Formula:
                                                        try
                                                        {
                                                            dataRow[j] = cell.NumericCellValue;
                                                        }
                                                        catch (Exception)
                                                        {
                                                            dataRow[j] = cell.StringCellValue;
                                                        }
                                                        break;
                                                }
                                            }
                                        }
                                        dataTable.Rows.Add(dataRow);
                                    }
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

 

posted on 2021-02-08 17:56  张不胖  阅读(206)  评论(0编辑  收藏  举报

导航