第一种方式：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 }
在使用一段时间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 }
导出（暂时未使用,所以没调试过）：
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;
}
}