1.使用OLEDB导出Excel ，这种方式有点慢，慎用
/// <summary> /// 使用OLEDB导出Excel /// </summary> /// <param name="dt"></param> /// <param name="filepath"></param> /// <param name="tablename"></param> public static void Export(System.Data.DataTable dt, string filepath, string tablename) { //excel 2003格式 // string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; //Excel 2007格式 string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;"; try { using (OleDbConnection con = new OleDbConnection(connString)) { con.Open(); StringBuilder strSQL = new StringBuilder(); strSQL.Append("CREATE TABLE ").Append("[" + tablename + "]"); strSQL.Append("("); for (int i = 0; i < dt.Columns.Count; i++) { strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,"); } strSQL = strSQL.Remove(strSQL.Length - 1, 1); strSQL.Append(")"); OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con); cmd.ExecuteNonQuery(); for (int i = 0; i < dt.Rows.Count; i++) { strSQL.Clear(); StringBuilder strfield = new StringBuilder(); StringBuilder strvalue = new StringBuilder(); for (int j = 0; j < dt.Columns.Count; j++) { strfield.Append("[" + dt.Columns[j].ColumnName + "]"); strvalue.Append("'" + dt.Rows[i][j].ToString() + "'"); if (j != dt.Columns.Count - 1) { strfield.Append(","); strvalue.Append(","); } else { } } cmd.CommandText = strSQL.Append(" insert into [" + tablename + "]( ") .Append(strfield.ToString()) .Append(") values (").Append(strvalue).Append(")").ToString(); cmd.ExecuteNonQuery(); } con.Close(); } Console.WriteLine("OK"); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
2.NPOI 导出excel
2007 版
public FileResult Export(SC_ServiceCardUsedRecordSearchParam param) { int cou = 0; //创建Excel文件的对象 //NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();//03 NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(); //NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); //添加一个sheet //NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //03 NPOI.SS.UserModel.ISheet sheet1= book.CreateSheet("Sheet1"); //07 List<SC_ServiceCardUsedRecord> cards = new List<SC_ServiceCardUsedRecord>(); //获取list数据 cards = _AppContext.ServiceCardUsedRecordApp.SelectRecordList(param).ToList<SC_ServiceCardUsedRecord>(); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("编号"); row1.CreateCell(1).SetCellValue("卡券号码"); row1.CreateCell(2).SetCellValue("手机号"); row1.CreateCell(3).SetCellValue("姓名"); row1.CreateCell(4).SetCellValue("车架号"); row1.CreateCell(5).SetCellValue("经销商"); row1.CreateCell(6).SetCellValue("行驶里程"); row1.CreateCell(7).SetCellValue("创建时间"); row1.CreateCell(8).SetCellValue("卡劵类型"); row1.CreateCell(9).SetCellValue("车型"); row1.CreateCell(10).SetCellValue("来源"); row1.CreateCell(11).SetCellValue("经销商名称"); row1.CreateCell(12).SetCellValue("发卡时间"); row1.CreateCell(13).SetCellValue("购车时间"); row1.CreateCell(14).SetCellValue("会员等级"); //将数据逐步写入sheet1各个行 for (int i = 0; i < cards.Count(); i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(cards[i].Id.ToString()); rowtemp.CreateCell(1).SetCellValue(cards[i].CardNo.ToString()); rowtemp.CreateCell(2).SetCellValue(cards[i].PhoneNumber.ToString()); rowtemp.CreateCell(3).SetCellValue(cards[i].CustName == null ? "" : cards[i].CustName.ToString()); rowtemp.CreateCell(4).SetCellValue(cards[i].VIN == null ? "" : cards[i].VIN.ToString()); rowtemp.CreateCell(5).SetCellValue(cards[i].DealerId == null ? "" : cards[i].DealerId.ToString()); rowtemp.CreateCell(6).SetCellValue(cards[i].Mileage == null ? "" : cards[i].Mileage.ToString()); rowtemp.CreateCell(7).SetCellValue(cards[i].CreateTime.ToLongDateString()); rowtemp.CreateCell(8).SetCellValue(cards[i].CardTypeName == null ? "" : cards[i].CardTypeName.ToString()); rowtemp.CreateCell(9).SetCellValue(cards[i].CarCategory == null ? "" : cards[i].CarCategory.ToString()); rowtemp.CreateCell(10).SetCellValue(cards[i].ActivityTag == null ? "" : cards[i].ActivityTag.ToString()); rowtemp.CreateCell(11).SetCellValue(cards[i].DealerName == null ? "" : cards[i].DealerName.ToString()); rowtemp.CreateCell(12).SetCellValue(cards[i].kqCreateTime == null ? "" : cards[i].kqCreateTime.ToString()); rowtemp.CreateCell(13).SetCellValue(cards[i].BuyTime == null ? "" : cards[i].BuyTime.ToString()); rowtemp.CreateCell(14).SetCellValue(cards[i].MLevel == null ? "" : cards[i].MLevel.ToString()); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); // MemoryStream stream = new MemoryStream(); book.Write(ms); var buf = ms.ToArray(); //ms.Seek(0, SeekOrigin.Begin); //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet //return File(ms, "application/vnd.ms-excel", "Card.xlsx"); return File(buf, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Card.xlsx"); }
2003 版
public ActionResult Export(DateTime? startTime, DateTime? endTime, string tableName) { IWorkbook book = new HSSFWorkbook(); //获取list数据 DataTable dt = _AppContext.ReportApp.GetReport(startTime ?? DateTime.Now.AddDays(-1), endTime ?? DateTime.Now, tableName); if (dt.Rows.Count < EXCEL03_MaxRow) DataWrite2Sheet(dt, 0, dt.Rows.Count - 1, book, "Sheet1"); else { int page = dt.Rows.Count / EXCEL03_MaxRow; for (int i = 0; i < page; i++) { int start = i * EXCEL03_MaxRow; int end = (i * EXCEL03_MaxRow) + EXCEL03_MaxRow - 1; DataWrite2Sheet(dt, start, end, book, "Sheet" + i.ToString()); } int lastPageItemCount = dt.Rows.Count % EXCEL03_MaxRow; DataWrite2Sheet(dt, dt.Rows.Count - lastPageItemCount, dt.Rows.Count - 1, book, "Sheet" + page.ToString()); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return File(ms, "application/vnd.ms-excel", tableName + ".xls"); }
注意：操作Excel2003与操作Excel2007使用的是不同的命名空间下的内容
使用NPOI.HSSF.UserModel空间下的HSSFWorkbook操作Excel2003
使用NPOI.XSSF.UserModel空间下的XSSFWorkbook操作Excel2007