• .NET 导入导出Excel


    第一种方式:OleDb

    需要安装office,且读数据慢,而且有数据格式的Cell读出数据不正确等问题.放弃。

    第二种方式:NPOI开源库

    使用NPOI导入导出Excel应该是.NET开发很常用的手段.

    代码如下:

      1 public ExcelHelper(string fileName)
      2         {
      3             this.fileName = fileName;
      4             disposed = false;
      5         }
      6 
      7         /// <summary>
      8         /// 将DataTable数据导入到excel中
      9         /// </summary>
     10         /// <param name="data">要导入的数据</param>
     11         /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
     12         /// <param name="sheetName">要导入的excel的sheet的名称</param>
     13         /// <returns>导入数据行数(包含列名那一行)</returns>
     14         public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
     15         {
     16             int i = 0;
     17             int j = 0;
     18             int count = 0;
     19             ISheet sheet = null;
     20 
     21             fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
     22             if (fileName.IndexOf(".xlsx") > 0) // 2007版本
     23                 workbook = new HSSFWorkbook();
     24             else if (fileName.IndexOf(".xls") > 0) // 2003版本
     25                 workbook = new HSSFWorkbook();
     26 
     27             try
     28             {
     29                 if (workbook != null)
     30                 {
     31                     sheet = workbook.CreateSheet(sheetName);
     32                 }
     33                 else
     34                 {
     35                     return -1;
     36                 }
     37 
     38                 if (isColumnWritten == true) //写入DataTable的列名
     39                 {
     40                     IRow row = sheet.CreateRow(0);
     41                     for (j = 0; j < data.Columns.Count; ++j)
     42                     {
     43                         row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
     44                     }
     45                     count = 1;
     46                 }
     47                 else
     48                 {
     49                     count = 0;
     50                 }
     51 
     52                 for (i = 0; i < data.Rows.Count; ++i)
     53                 {
     54                     IRow row = sheet.CreateRow(count);
     55                     for (j = 0; j < data.Columns.Count; ++j)
     56                     {
     57                         row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
     58                     }
     59                     ++count;
     60                 }
     61                 workbook.Write(fs); //写入到excel
     62                 return count;
     63             }
     64             catch (Exception ex)
     65             {
     66                 Console.WriteLine("Exception: " + ex.Message);
     67                 return -1;
     68             }
     69         }
     70 
     71         /// <summary>
     72         /// 将excel中的数据导入到DataTable中
     73         /// </summary>
     74         /// <param name="sheetName">excel工作薄sheet的名称</param>
     75         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
     76         /// <returns>返回的DataTable</returns>
     77         public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
     78         {
     79             ISheet sheet = null;
     80             DataTable data = new DataTable();
     81             int startRow = 0;
     82             try
     83             {
     84                 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
     85                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
     86                     workbook = new XSSFWorkbook(fs);
     87                 else if (fileName.IndexOf(".xls") > 0) // 2003版本
     88                     workbook = new HSSFWorkbook(fs);
     89 
     90                 if (sheetName != null)
     91                 {
     92                     sheet = workbook.GetSheetAt(0);
     93                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
     94                     {
     95                         sheet = workbook.GetSheetAt(0);
     96                     }
     97                 }
     98                 else
     99                 {
    100                     sheet = workbook.GetSheetAt(0);
    101                 }
    102                 if (sheet != null)
    103                 {
    104 
    105                     IRow firstRow = sheet.GetRow(0);
    106                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
    107 
    108                     if (isFirstRowColumn)
    109                     {
    110                         try
    111                         {
    112                             for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
    113                             {
    114                                 ICell cell = firstRow.GetCell(i);
    115                                 if (cell != null)
    116                                 {
    117                                     string cellValue = cell.StringCellValue;
    118                                     if (cellValue != null)
    119                                     {
    120                                         DataColumn column = new DataColumn(cellValue);
    121                                         data.Columns.Add(column);
    122                                     }
    123                                 }
    124                             }
    125                             startRow = sheet.FirstRowNum + 1;
    126                         }
    127                         catch (Exception e)
    128                         {
    129 
    130                         }
    131 
    132                     }
    133                     else
    134                     {
    135                         startRow = sheet.FirstRowNum;
    136                     }
    137 
    138                     //最后一列的标号
    139                     int rowCount = sheet.LastRowNum;
    140                     for (int i = startRow; i <= rowCount; ++i)
    141                     {
    142                         IRow row = sheet.GetRow(i);
    143                         if (row == null) continue; //没有数据的行默认是null       
    144 
    145                         DataRow dataRow = data.NewRow();
    146                         for (int j = row.FirstCellNum; j < cellCount; ++j)
    147                         {
    148                             ICell cell = row.GetCell(j);
    149                             if (cell != null)//同理,没有数据的单元格都默认是null
    150                             {
    151                                 if (cell.CellType == CellType.Numeric)
    152                                 {
    153                                     //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
    154                                     if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
    155                                     {
    156                                         dataRow[j] = cell.DateCellValue;
    157                                     }
    158                                     else//其他数字类型
    159                                     {
    160                                         dataRow[j] = cell.NumericCellValue;
    161                                     }
    162                                 }
    163                                 else
    164                                 {
    165 
    166 
    167                                     dataRow[j] = cell.ToString();
    168 
    169                                 }
    170 
    171                             }
    172 
    173                         }
    174                         data.Rows.Add(dataRow);
    175                     }
    176                 }
    177                 fs.Close();
    178                 return data;
    179             }
    180             catch (Exception ex)
    181             {
    182                 Console.WriteLine("Exception: " + ex.Message);
    183                 return null;
    184             }
    185         }
    View Code

    在使用一段时间NPOI后,遇到一个问题.当导入大数据量Excel时,很不稳定,经常会出现内存溢出异常.

    不稳定在于有时是20W行数据有时是10W行数据就内存溢出.跟踪了一段时间未发现原因所在,百度了

    很多解决方案,发现都无法解决NPOI内存溢出的问题.最终还是选择放弃了NPOI转Aspose.

    第三种方式:Aspose【收费】

    代码如下:

    1  public DataTable ReadExcel()
    2         {
    3             Workbook book = new Workbook(fileName);
    4             //book.Open(strFileName);
    5             Worksheet sheet = book.Worksheets[0];
    6             Cells cells = sheet.Cells;
    7             var dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
    8             return dt;
    9         }
    View Code

    导出(暂时未使用,所以没调试过):

    public MemoryStream Export<T>(IEnumerable<T> data) //, HttpResponse response
            {
                Workbook workbook = new Workbook();
                Worksheet sheet = (Worksheet)workbook.Worksheets[0];
    
                PropertyInfo[] ps = typeof(T).GetProperties();
                var colIndex = "A";
    
                foreach (var p in ps)
                {
                    object[] objs = p.GetCustomAttributes(typeof(DescriptionAttribute), true);
                    sheet.Cells[colIndex + 1].PutValue(((DescriptionAttribute)objs[0]).Description);
                    int i = 2;
                    foreach (var d in data)
                    {
                        var x = p.GetValue(d, null);
                        if (p.PropertyType.ToString().ToLower() == "system.datetime")
                        {
                            var date = (DateTime)x;
                            sheet.Cells[colIndex + i].PutValue(DateTime.Parse(x.ToString()));
                            
                            Style style = sheet.Cells[colIndex + i].GetStyle();
                            style.Number = 14;
                            sheet.Cells[colIndex + i].SetStyle(style);
                        }
                        else
                        {
                            sheet.Cells[colIndex + i].PutValue(x);
                        }
    
                        i++;
                    }
    
                    colIndex = ((char)(colIndex[0] + 1)).ToString();
                }
                MemoryStream ms = new MemoryStream();
                //导出格式设置xlsx xls
                workbook.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));
                return ms;
                //response.Clear();
                //response.Buffer = true;
                //response.Charset = "utf-8";
                //response.AppendHeader("Content-Disposition", "attachment;filename=xxx.xls");
                //response.ContentEncoding = System.Text.Encoding.UTF8;
                //response.ContentType = "application/ms-excel";
                //response.BinaryWrite(workbook.SaveToStream().ToArray());
                //response.End();
            }
    

      

    public ActionResult ExportExcel(string ids)
            {
                ExcelHelper helper = new ExcelHelper("导出数据");
                HttpResponse response;
                if (ids.Contains(','))
                {
                    var idArr = ids.Split(',');
                    try
                    {
                        var idList = Array.ConvertAll(idArr, new Converter<string, int>(StrToInt)).ToList();
                        var ests = estSvc.Query(c => (idList.Contains(c.ID))).ToList();
                        List<ExportModel> exportData = new List<ExportModel>();
                        foreach (var i in ests)
                        {
                            var m = new ExportModel();
                            m.Type = i.EstateType == 0 ? "" : dicList.Find(d => d.ID == i.EstateType).Value;
                            m.RegisteDate = i.RegisteDate;
                            m.EstateName = i.EstateName;
                            m.Dept = i.AffilicatedDept;
                            m.Register = i.RegisterName;
                            m.OperateTeam = i.EstateType == 43 || i.EstateType == 44 ? i.OperateTeamChannel : i.OperateTeamProxy;
                            exportData.Add(m);
    
                        }
                        var s = helper.Export<ExportModel>((IEnumerable<ExportModel>)exportData);
                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "utf-8";
                        Response.AppendHeader("Content-Disposition", "attachment;filename=导出盘源信息.xlsx");
                        Response.ContentEncoding = System.Text.Encoding.UTF8;
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.BinaryWrite(s.ToArray());
                        Response.End();
                        return null;
                    }
                    catch (Exception e)
                    {
                        return null;
                    }
                }
                else
                {
                    var ests = estSvc.Query(c => c.ID == Convert.ToInt32(ids)).ToList();
                    List<ExportModel> exportData = new List<ExportModel>();
                    foreach (var i in ests)
                    {
                        var m = new ExportModel();
                        m.Type = i.EstateType == 0 ? "" : dicList.Find(d => d.ID == i.EstateType).Value;
                        m.RegisteDate = i.RegisteDate;
                        m.EstateName = i.EstateName;
                        m.Dept = i.AffilicatedDept;
                        m.Register = i.RegisterName;
                        m.OperateTeam = i.EstateType == 43 || i.EstateType == 44 ? i.OperateTeamChannel : i.OperateTeamProxy;
                        exportData.Add(m);
    
                    }
                    var s = helper.Export<ExportModel>((IEnumerable<ExportModel>)exportData);
                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "utf-8";
                    Response.AppendHeader("Content-Disposition", "attachment;filename=导出盘源信息.xlsx");
                    Response.ContentEncoding = System.Text.Encoding.UTF8;
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.BinaryWrite(s.ToArray());
                    Response.End();
                    return null;
                }
            }
    

      

  • 相关阅读:
    ThinkPHP 5 中AJAX跨域请求头设置方法【转】
    phpstorm激活大全【转】
    PHP严重致命错误处理:php Fatal error: Cannot redeclare class or function【转】
    php的dirname(__FILE__)和dirname(dirname(__FILE__))使用总结【转】
    php 设置临时内存和超时设置脚本最大执行时间
    mysql 实现表连接(左,右,内,全连接)【转】
    Maximum execution time of 30 seconds exceeded解决办法【转】
    常用邮箱SMTP服务器地址大全
    叉积(POJ
    乘法逆元(P3811)(四种方法)
  • 原文地址:https://www.cnblogs.com/toloe/p/6547788.html
Copyright © 2020-2023  润新知