C# Excel导入以流的方式保存，不保存到本地 

在System.IO名称空间中有以下类 

BinaryReader/Writer 

TextReader/Writer 

Stream 

其中类Stream为抽象类。由此有三个派生类： 

MemoryStream：对内存进行读取与写入 

BufferedStream：对缓冲器进行读取/写入 

FileStream：对文件执行读取与写入 

TextReader/Writer为抽象类。由此派生类： 

StreamReader/StreamWirter 

StringReader/StreamWriter 

需要引入命名空间: 

using System.IO

protected void btnExcel_Click(object sender, EventArgs e)
    {
        //exel导入以流的方式存储
        HttpFileCollection files = HttpContext.Current.Request.Files;
        if (files == null || files.Count == 0)
        {
            throw new Exception("您没有选择导入文件！");
        }
        bool useOldVersionOfExcel = false;
        string aLastName = files[0].FileName.Substring(files[0].FileName.LastIndexOf(".") + 1, (files[0].FileName.Length - files[0].FileName.LastIndexOf(".") - 1)); //扩展名
        if (aLastName == "xlsx")
        {
            useOldVersionOfExcel = false;
        }else
        {
            useOldVersionOfExcel = true;
        }
        DataSet ds = ExcelSteamToDataSet(fulV12.FileContent, useOldVersionOfExcel);

        #region MyRegion
        //string fullName = HttpContext.Current.Server.MapPath("~") + @"\导入Excel" + System.IO.Path.GetExtension(files[0].FileName).ToLower();
        //files[0].SaveAs(fullName);
        //DataSet ds = ExcelToDataSet(fullName);
        //if (File.Exists(fullName))
        //    File.Delete(fullName);


        DataTable dt = new DataTable();
        DataColumn col1 = new DataColumn("sid");
        dt.Columns.Add(col1);
        DataColumn dc1 = new DataColumn("V1", Type.GetType("System.String"));
        DataColumn dc2 = new DataColumn("V2", Type.GetType("System.String"));
        DataColumn dc3 = new DataColumn("V3", Type.GetType("System.String"));
        DataColumn dc7 = new DataColumn("V7", Type.GetType("System.String"));
        DataColumn dc4 = new DataColumn("V4", Type.GetType("System.String"));
        DataColumn dc5 = new DataColumn("V5", Type.GetType("System.String"));
        DataColumn dc9 = new DataColumn("V9", Type.GetType("System.String"));
        DataColumn dc10 = new DataColumn("V10", Type.GetType("System.String"));
        dt.Columns.Add(dc1);
        dt.Columns.Add(dc2);
        dt.Columns.Add(dc3);
        dt.Columns.Add(dc7);
        dt.Columns.Add(dc4);
        dt.Columns.Add(dc5);
        dt.Columns.Add(dc9);
        dt.Columns.Add(dc10);
        if (ds.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();
                dr["V1"] = ds.Tables[0].Rows[i][0].ToString();
                dr["V2"] = ds.Tables[0].Rows[i][1].ToString();
                dr["V3"] = ds.Tables[0].Rows[i][2].ToString();
                dr["V7"] = ds.Tables[0].Rows[i][3].ToString();
                dr["V4"] = ds.Tables[0].Rows[i][4].ToString();
                dr["V5"] = ds.Tables[0].Rows[i][5].ToString();
                dr["V9"] = ds.Tables[0].Rows[i][6].ToString();
                dr["V10"] = ds.Tables[0].Rows[i][7].ToString();
                dt.Rows.Add(dr);
            }

        }
        BindGridView(dt);

        #endregion




    }
 1 // <summary> 流的方式接收，进行读取内容
 2     /// 
 3     /// </summary>
 4     /// <param name="fs"></param>
 5     /// <returns></returns>
 6     public static DataSet ExcelSteamToDataSet(Stream fs,bool useOldVersionOfExcel)
 7     {
 8         DataSet res = new DataSet();
 9         IWorkbook workbook;// = new XSSFWorkbook(fs);
10         if (useOldVersionOfExcel)
11         {
12             //旧版
13             workbook = new HSSFWorkbook(fs);
14         }
15         else
16         {   //2017以上版本
17             workbook = new XSSFWorkbook(fs);
18         }
19        
20         int sheetNum = workbook.NumberOfSheets;
21         for (int index = 0; index < sheetNum; index++)
22         {
23             ISheet sheet = workbook.GetSheetAt(index);
24             DataTable dt = new DataTable(sheet.SheetName);
25             if (sheet.LastRowNum == 0)
26             {
27                 continue;
28             }
29             //表头  
30             IRow header = sheet.GetRow(sheet.FirstRowNum);
31             List<int> columns = new List<int>();
32             for (int i = 0; i < header.LastCellNum; i++)
33             {
34                 object obj = header.GetCell(i);
35                 if (obj == null || obj.ToString() == string.Empty)
36                 {
37                     dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
38                 }
39                 else
40                     dt.Columns.Add(new DataColumn(obj.ToString()));
41             }
42             //数据  
43             for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
44             {
45                 DataRow dr = dt.NewRow();
46                 bool hasValue = false;
47                 for (int j = 0; j < dt.Columns.Count; j++)
48                 {
49                     try
50                     {
51                         if (sheet.GetRow(i) == null)
52                         {
53                             hasValue = false;
54                             break;
55                         }
56 
57                         //判断当前单元格的内容是否为日期格式的数据，是则进行处理
58                         ICell cell = sheet.GetRow(i).GetCell(j);
59                         if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
60                         {
61                             dr[j] = cell.DateCellValue.ToString();
62                         }
63                         else
64                         {
65                             dr[j] = cell.ToString();
66                         }
67                         //dr[j] = sheet.GetRow(i).GetCell(j);
68                         if (dr[j] != null && dr[j].ToString() != string.Empty)
69                         {
70                             hasValue = true;
71                         }
72                     }
73                     catch { }
74                 }
75                 if (hasValue)
76                 {
77                     dt.Rows.Add(dr);
78                 }
79             }
80             res.Tables.Add(dt);
81         }
82         return res;
83     }

 版本很旧啦，突然跟我说文件不能保存在本地，只能以流的方式保存，

posted @ 2019-09-06 16:10  SDdemon  阅读(1129)  评论(0)  编辑  收藏  举报