/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="data">数据</param> /// <param name="excelColumns">sheetName</param> /// <param name="sheetName">sheet名</param> /// <returns></returns> public void ExportExcel <T>(List <T> data, List <ExcelHeaderColumn> excelColumns, ref ISheet sheet) { CreateHeader(sheet, excelColumns); Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); string value = null; for (int i = 0; data != null && i < data.Count; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < excelColumns.Count; j++) { value = null; ExcelHeaderColumn col = excelColumns[j]; PropertyInfo property = properties.FirstOrDefault(p => p.Name == col.Name); if (property != null) { Object tempValue = property.GetValue(data[i], null); if (tempValue != null) { value = tempValue.ToString(); } } SetCellValue(row, j, value); } } }
/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="data">数据</param> /// <param name="excelColumns">列头</param> /// <param name="titleName">标题</param> /// <returns></returns> public MemoryStream ExportExcel <T>(List <T> data, List <ExcelHeaderColumn> excelColumns, string titleName) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); //标题 IRow rowTitle = sheet.CreateRow(0); ICell celltitle = rowTitle.CreateCell(0, CellType.String); celltitle.SetCellValue(titleName); rowTitle.Cells.Add(celltitle); //列名 IRow rowColum = sheet.CreateRow(1); for (int j = 0; j < excelColumns.Count; j++) { ICell cell = rowColum.CreateCell(j); cell.SetCellValue(excelColumns[j].DisplayName); rowColum.Cells.Add(cell); } Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); string value = null; for (int i = 0; i < data.Count; i++) { IRow row = sheet.CreateRow(i + 2); for (int j = 0; j < excelColumns.Count; j++) { value = null; ExcelHeaderColumn col = excelColumns[j]; PropertyInfo property = properties.FirstOrDefault(p => p.Name == col.Name); if (property != null) { Object tempValue = property.GetValue(data[i], null); if (tempValue != null) { value = tempValue.ToString(); } } SetCellValue(row, j, value); } if (worker != null) { worker.ReportProgress(i); Thread.Sleep(10); } } MemoryStream stream = new MemoryStream(); workbook.Write(stream); return(stream); }
/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="dataTable">数据</param> /// <param name="excelColumns">列头</param> /// <param name="titleName">标题</param> /// <returns></returns> public MemoryStream ExportExcel(DataTable dataTable, List <ExcelHeaderColumn> excelColumns, string titleName) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); //标题 IRow rowTitle = sheet.CreateRow(0); ICell celltitle = rowTitle.CreateCell(0, CellType.String); celltitle.SetCellValue(titleName); rowTitle.Cells.Add(celltitle); //列名 IRow column = sheet.CreateRow(1); for (int j = 0; j < excelColumns.Count; j++) { ICell cell = column.CreateCell(j); cell.SetCellValue(excelColumns[j].DisplayName); column.Cells.Add(cell); } string value = null; for (int i = 0; i < dataTable.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 2); for (int j = 0; j < excelColumns.Count; j++) { value = null; ExcelHeaderColumn col = excelColumns[j]; if (dataTable.Columns.Contains(col.Name)) { Object tempValue = dataTable.Rows[i][col.Name]; if (tempValue != null && tempValue != DBNull.Value) { value = tempValue.ToString(); } } SetCellValue(row, j, value); } if (worker != null) { worker.ReportProgress(i); Thread.Sleep(10); } } MemoryStream stream = new MemoryStream(); workbook.Write(stream); return(stream); }
/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="data">数据</param> /// <param name="excelColumns">列头</param> /// <returns></returns> public MemoryStream ExportExcel <T>(List <T> data, List <ExcelHeaderColumn> excelColumns) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); CreateHeader(sheet, excelColumns); Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); string value = null; for (int i = 0; i < data.Count; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < excelColumns.Count; j++) { value = null; ExcelHeaderColumn col = excelColumns[j]; PropertyInfo property = properties.FirstOrDefault(p => p.Name == col.Name); if (property != null) { Object tempValue = property.GetValue(data[i], null); if (tempValue != null) { value = tempValue.ToString(); } } SetCellValue(row, j, value); } if (worker != null) { worker.ReportProgress(i); Thread.Sleep(10); } } MemoryStream stream = new MemoryStream(); workbook.Write(stream); return(stream); }