excel导出

 

//写的时候查了很多资料需求了一些帮助，一个导出ef里的数据

Db_laienTransEntities3 db = new Db_laienTransEntities3();
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("DBSET");
IQueryable<NikeDTCDailyReport> table= db.NikeDTCDailyReport.Where(p => true);
DataTable dt = ToDataTable(table);
int rowIndex = 0;
int shheCount = 1;
foreach (DataRow item in dt.Rows)
{
if (rowIndex > 10000)
{
save(workbook, context, sheet);
rowIndex = 0;
shheCount++;
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet("DBSET" + shheCount);
}
IRow dataRow = sheet.CreateRow(rowIndex);
int rowSize=0;
foreach (DataColumn Colums in dt.Columns)
{
dataRow.CreateCell(rowSize).SetCellValue(item[Colums].ToString());
rowSize++;
}
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
// sting path= MappingType("/"+"/tempfiles")
string path = context.Server.MapPath("~/"+"/Excel文件/"+sheet.SheetName+".xlsx");
using (FileStream fs = new FileStream(path,FileMode.Create, FileAccess.Write))
{
byte[] bArr = ms.ToArray();
fs.Write(bArr, 0, bArr.Length);
fs.Flush();
ms.Close();
}

//如果数据大于10000就创建一个新的excel装进去

 

private void save(XSSFWorkbook workbook,HttpContext context,ISheet sheet)
{
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
// sting path= MappingType("/"+"/tempfiles")
string path = context.Server.MapPath("~/"+"/Excel文件/"+sheet.SheetName+".xlsx");
using (FileStream fs = new FileStream(path,FileMode.Create, FileAccess.Write))
{
byte[] bArr = ms.ToArray();
fs.Write(bArr, 0, bArr.Length);
fs.Flush();
}
ms.Close();
}

 

 

 

 

 

 

 

 

//这是一个吧list转化成datatable的一个方法

public static DataTable ToDataTable<T>(IQueryable<T> varlist)
{
DataTable dtReturn = new DataTable();
// column names
System.Reflection.PropertyInfo[] oProps = null;
if (varlist == null)
return dtReturn;
foreach (T rec in varlist)
{
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (System.Reflection.PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow();
foreach (System.Reflection.PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}
dtReturn.Rows.Add(dr);
}
return dtReturn;
}