/// <summary> /// DataTable2ExcelStream /// </summary> /// <param name="dataTable">dataTable</param> /// <param name="stream">stream</param> /// <param name="excelFormat">excelFormat</param> /// <returns></returns> public static void ToExcelStream([NotNull] this DataTable dataTable, [NotNull] Stream stream, ExcelFormat excelFormat) => ToExcelStream(dataTable, stream, excelFormat, null);
///<summary>Creates an ExcelResult that sends an Excel spreadsheet with the specified filename and format.</summary> public ExcelResult(string name, ExcelFormat format) //OleDb fails if the extension doesn't match... : base(GetFileName(ExcelExport.GetExtension(format)), ContentTypes[format]) { FileDownloadName = name; Format = format; }
/// <summary> /// export excel via template /// </summary> /// <typeparam name="TEntity">Entity Type</typeparam> /// <param name="entities">entities</param> /// <param name="templateStream">templateStream</param> /// <param name="excelFormat">excelFormat</param> /// <param name="sheetIndex">sheetIndex,zero by default</param> /// <param name="extraData">extraData</param> /// <returns>exported excel bytes</returns> public static byte[] ToExcelBytesByTemplate <TEntity>([NotNull] this IEnumerable <TEntity> entities, Stream templateStream, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0, object extraData = null) { if (templateStream == null) { throw new ArgumentNullException(nameof(templateStream)); } var workbook = ExcelHelper.LoadExcel(templateStream, excelFormat); return(ToExcelBytesByTemplate(entities, workbook, sheetIndex, extraData)); }
/// <summary> /// EntityList2ExcelBytes /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="excelFormat">excelFormat</param> public static byte[] ToExcelBytes <TEntity>([NotNull] this IEnumerable <TEntity> entityList, ExcelFormat excelFormat) => ToExcelBytes(entityList, excelFormat, 0);
/// <summary> /// 读取DataTable数据源到Excel内存流(NPOI方式) /// 1.写入到Excel文件,2.输出到响应 /// 3.用完之后记得释放(using{}) /// </summary> /// <param name="source">数据源</param> /// <param name="format">文件扩展名(.xls,.xlsx)</param> /// <returns>数据流</returns> public static MemoryStream ReadDataTableToExcel(DataTable source, ExcelFormat format = ExcelFormat.xls) { if (source == null || source.Rows.Count == 0) { throw new ArgumentNullException(nameof(source)); } IWorkbook workbook = null; switch (format) { case ExcelFormat.xls: workbook = new HSSFWorkbook(); break; case ExcelFormat.xlsx: workbook = new XSSFWorkbook(); break; default: workbook = new HSSFWorkbook(); break; } ISheet sheet = workbook.CreateSheet("Sheet0"); //创建一个名称为Sheet0的表 int rowCount = source.Rows.Count; //行数 int columnCount = source.Columns.Count; //列数 //设置列头 IRow row = sheet.CreateRow(0);//excel第一行设为列头 for (int c = 0; c < columnCount; c++) { ICell cell = row.CreateCell(c); cell.SetCellValue(source.Columns[c].ColumnName); } //设置每行每列的单元格, for (int i = 0; i < rowCount; i++) { row = sheet.CreateRow(i + 1); for (int j = 0; j < columnCount; j++) { ICell cell = row.CreateCell(j); //excel第二行开始写入数据 string defaultValue = source.Rows[i][j].ToString(); //默认为字符串 switch (source.Columns[j].DataType.ToString()) { case "System.String": //字符串类型 cell.SetCellValue(defaultValue); break; case "System.DateTime": //日期类型 cell.SetCellValue(defaultValue); break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(defaultValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(defaultValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(defaultValue, out doubV); cell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 cell.SetCellValue(""); break; default: cell.SetCellValue(defaultValue); break; } } } var ms = new NpoiMemoryStream(); ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Position = 0; ms.AllowClose = false; return(ms); }
/// <summary> /// 读取List<T>数据源到Excel内存流(NPOI方式) /// 1.写入到Excel文件,2.输出到响应 /// 3.用完之后记得释放(using{}) /// </summary> /// <param name="source">数据源</param> /// <param name="format">文件扩展名(.xls,.xlsx)</param> /// <returns>数据流</returns> public static MemoryStream ReadListToExcel <T>(List <T> source, ExcelFormat format = ExcelFormat.xls) where T : class, new() { return(ReadDataTableToExcel(source.ToDataTable(), format)); }
/// <summary> /// prepare a workbook for export /// </summary> /// <param name="excelFormat">excelFormat</param> /// <param name="excelSetting">excelSetting</param> /// <returns></returns> public static IWorkbook PrepareWorkbook(ExcelFormat excelFormat, ExcelSetting excelSetting) { return(PrepareWorkbook(excelFormat == ExcelFormat.Xlsx, excelSetting)); }
/// <summary> /// DataTable2ExcelBytes /// </summary> /// <param name="dataTable">dataTable</param> /// <param name="excelFormat">excel格式</param> public static byte[] ToExcelBytes([NotNull] this DataTable dataTable, ExcelFormat excelFormat) => ToExcelBytes(dataTable, excelFormat, null);
///<summary>Gets the file extension for a database format.</summary> public static string GetExtension(ExcelFormat format) { return(FormatExtensions.First(kvp => kvp.Key == format).Value); }
public ExcelExporter(ExcelFormat excelFormat = ExcelFormat.OfficeOpenXmlDocument) { ExcelFormat = excelFormat; }
public IActionResult ChangeExcelFormat() { string pathToGrTruth = $@"C:\Users\stahi\Desktop\SkinLesSuggest\Server\.NET Core\ExcelFiles\grTruth.xlsx"; ExcelPackage epGT = new ExcelPackage(new FileInfo(pathToGrTruth)); ExcelWorksheet grTruth = epGT.Workbook.Worksheets.First(); string pathToMet = $@"C:\Users\stahi\Desktop\SkinLesSuggest\Server\.NET Core\ExcelFiles\metadata.xlsx"; ExcelPackage epMet = new ExcelPackage(new FileInfo(pathToMet)); ExcelWorksheet met = epMet.Workbook.Worksheets.First(); List <ExcelFormat> excelFormat = new List <ExcelFormat>(); for (int rw = 2; rw <= grTruth.Dimension.End.Row; rw++) { var imageId = grTruth.Cells[rw, 1].Text.ToString(); string dx = null; var mel = grTruth.Cells[rw, 2].Text.ToString(); var nv = grTruth.Cells[rw, 3].Text.ToString(); var bcc = grTruth.Cells[rw, 4].Text.ToString(); var ak = grTruth.Cells[rw, 5].Text.ToString(); var bkl = grTruth.Cells[rw, 6].Text.ToString(); var df = grTruth.Cells[rw, 7].Text.ToString(); var vasc = grTruth.Cells[rw, 8].Text.ToString(); var scc = grTruth.Cells[rw, 9].Text.ToString(); #region dx if (mel == "1") { dx = "mel"; } if (nv == "1") { dx = "nv"; } if (bcc == "1") { dx = "bcc"; } if (ak == "1") { dx = "akiec"; } if (bkl == "1") { dx = "bkl"; } if (df == "1") { dx = "df"; } if (vasc == "1") { dx = "vasc"; } if (scc == "1") { dx = "scc"; } #endregion ExcelFormat dataToAdd = new ExcelFormat() { ImageId = imageId, Dx = dx }; excelFormat.Add(dataToAdd); } for (int rw = 2; rw <= met.Dimension.End.Row; rw++) { var imageId = met.Cells[rw, 1].Text.ToString(); var age = met.Cells[rw, 2].Text.ToString(); var localization = met.Cells[rw, 3].Text.ToString(); var lesionId = met.Cells[rw, 4].Text.ToString(); var sex = met.Cells[rw, 5].Text.ToString(); var dataToUpdate = excelFormat.FirstOrDefault(x => x.ImageId == imageId); if (dataToUpdate != null) { dataToUpdate.Age = age; dataToUpdate.Localization = localization; dataToUpdate.LesionId = string.IsNullOrEmpty(lesionId) ? Guid.NewGuid().ToString() : lesionId; dataToUpdate.Sex = sex; } else { Console.WriteLine($"!!!!!!!! {imageId} !!!!!!!!!!"); } } using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("HAM25000_metadata"); worksheet.Cells[1, 1].Value = "lesion_id"; worksheet.Cells[1, 2].Value = "image_id"; worksheet.Cells[1, 3].Value = "dx"; worksheet.Cells[1, 4].Value = "age"; worksheet.Cells[1, 5].Value = "sex"; worksheet.Cells[1, 6].Value = "localization"; for (int i = 2; i <= excelFormat.Count + 1; i++) { worksheet.Cells[i, 1].Value = excelFormat[i - 2].LesionId; worksheet.Cells[i, 2].Value = excelFormat[i - 2].ImageId; worksheet.Cells[i, 3].Value = excelFormat[i - 2].Dx; worksheet.Cells[i, 4].Value = excelFormat[i - 2].Age; worksheet.Cells[i, 5].Value = excelFormat[i - 2].Sex; worksheet.Cells[i, 6].Value = excelFormat[i - 2].Localization; } FileInfo excelFile = new FileInfo(@"D:\test.xlsx"); package.SaveAs(excelFile); } return(Ok()); }
/// <summary> /// 返回Excel文本 /// </summary> /// <param name="dirPath"></param> /// <param name="length">被评估人信息长度</param> /// <returns></returns> public Result GetExcelTxt(string dirPath, int length) { return(RunFun(logPath => { dirPath = ToolFile.GetAbsolutelyPath(dirPath); if (!Directory.Exists(dirPath)) { Res.Msg = "文件夹不存在"; return Res; } // 获取所有子文件夹 List <DirectoryInfo> listDir = new DirectoryInfo(dirPath).GetDirectories().ToList(); foreach (var dir in listDir) { // 获取待整理的名单文件 List <FileInfo> fileList = dir.GetFiles().Where(c => c.Name.Contains("_") && c.Extension.ToLower().Contains("txt") && !c.Name.Contains("Res")).ToList(); // 循环名单文件 foreach (var file in fileList) { List <ExcelFormat> excelList = new List <ExcelFormat>(); //WriteLog(logPath, file.FullName); // 循环行 foreach (var line in File.ReadAllLines(file.FullName)) { List <ExcelFormat> tmpList = new List <ExcelFormat>(); // 获取被评估人信息 string[] edArr = line.Split(' ').Where(c => !string.IsNullOrWhiteSpace(c)).Select(c => c.Replace(" ", "")).ToArray(); if (edArr.Length != length) { WriteLog(logPath, file.FullName + "\t" + "被评估人信息\t" + line); continue; } string t = "名单"; if (edArr[4].Contains(t)) { string name = ToolString.GetLetter(edArr[4]); for (int i = 0; i < name.Length; i++) { // 循环名单列表 foreach (var nameLine in File.ReadAllLines(ToolFile.GetAbsolutelyPath(file.DirectoryName) + t + name[i] + ".txt")) { string[] nameArr = nameLine.Split(' ').Where(c => !string.IsNullOrWhiteSpace(c)).Select(c => c.Replace(" ", "")).ToArray(); if (nameArr.Length != 2) { WriteLog(logPath, file.FullName + "\t" + line + "\t" + "循环名单列表\t" + nameLine); continue; } if (edArr[1] == nameArr[1]) { WriteLog(logPath, file.FullName + "\t" + edArr[1] + "\t" + "重名\t" + name[i] + ".txt"); continue; } ExcelFormat excel = new ExcelFormat(); // 基础信息 excel.ObservedDepartment = edArr[0]; excel.ObservedName = edArr[1]; excel.Mapping = edArr[2]; excel.Weight = edArr[3]; excel.ObserverDepartment = nameArr[0]; excel.ObserverName = nameArr[1]; tmpList.Add(excel); } } } else { // 添加分割属性 edArr[4] += "、"; // 获取多评估人信息, string[] erArr = edArr[4].Split('、').Where(c => !string.IsNullOrWhiteSpace(c)).ToArray(); foreach (var er in erArr) { ExcelFormat excel = new ExcelFormat(); // 基础信息 excel.ObservedDepartment = edArr[0]; excel.ObservedName = edArr[1]; excel.Mapping = edArr[2]; excel.Weight = edArr[3]; excel.ObserverName = er; tmpList.Add(excel); } } excelList.AddRange(tmpList); } ExcelFormat.CreateText(ToolFile.GetAbsolutelyPath(file.DirectoryName) + "Res" + file.Name, excelList); } } return Res; })); }
public void DataTableImportExportTestWithoutEmptyRowsAndAdditionalColumns(string file, ExcelFormat excelFormat) { // Arrange var excelBytes = File.ReadAllBytes(file); // Act var importedData = ExcelHelper.ToDataTable(excelBytes, excelFormat, removeEmptyRows: true, maxColumns: 3); // Assert var dt = new DataTable(); dt.Columns.AddRange(new[] { new DataColumn("A"), new DataColumn("B"), new DataColumn("C"), }); dt.AddNewRow(new object[] { "1", "2", "3" }); dt.AddNewRow(new object[] { "1", "", "" }); dt.AddNewRow(new object[] { "1", "2", "3" }); dt.AddNewRow(new object[] { "", "2", "3" }); Assert.NotNull(importedData); Assert.Equal(4, importedData.Rows.Count); importedData.AssertEquals(dt); }
/// <summary> /// EntityList2ExcelStream /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="stream">stream where to write</param> /// <param name="excelFormat">excelFormat</param> public static int ToExcelStream <TEntity>([NotNull] this IEnumerable <TEntity> entityList, [NotNull] Stream stream, ExcelFormat excelFormat) where TEntity : new() => ToExcelStream(entityList, stream, excelFormat, 0);
/// <summary> /// get a excel workbook /// </summary> /// <param name="excelFormat">excelFormat</param> /// <returns></returns> public static IWorkbook PrepareWorkbook(ExcelFormat excelFormat) => PrepareWorkbook(excelFormat == ExcelFormat.Xlsx);
/// <summary> /// DataTable2ExcelStream /// </summary> /// <param name="dataTable">datatable</param> /// <param name="stream">stream</param> /// <param name="excelFormat">excelFormat</param> /// <param name="excelSetting">excelSetting</param> /// <returns></returns> public static int ToExcelStream([NotNull] this DataTable dataTable, [NotNull] Stream stream, ExcelFormat excelFormat, ExcelSetting excelSetting) { var workbook = ExcelHelper.PrepareWorkbook(excelFormat); var sheet = workbook.CreateSheet( string.IsNullOrWhiteSpace(dataTable.TableName) ? "Sheet0" : dataTable.TableName); var headerRow = sheet.CreateRow(0); for (var i = 0; i < dataTable.Columns.Count; i++) { headerRow.CreateCell(i, CellType.String).SetCellValue(dataTable.Columns[i].ColumnName); } for (var i = 1; i <= dataTable.Rows.Count; i++) { var row = sheet.CreateRow(i); for (var j = 0; j < dataTable.Columns.Count; j++) { row.CreateCell(j, CellType.String).SetCellValue(dataTable.Rows[i][j]); } } workbook.Write(stream); return(1); }
/// <summary> /// read first sheet of excel from excel file bytes to a list /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="excelBytes">excelBytes</param> /// <param name="excelFormat">excelFormat</param> /// <returns>List</returns> public static List <TEntity> ToEntityList <TEntity>([NotNull] byte[] excelBytes, ExcelFormat excelFormat) where TEntity : new() => ToEntityList <TEntity>(excelBytes, excelFormat, 0);
public static void DatasetToExcel(DataSet ds, Stream stream, ExcelFormat excelFormat) { var workbook = DatasetToWorkbook(ds, excelFormat); workbook.Write(stream); }
/// <summary> /// read first sheet of excel from excel file bytes to a list /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="excelStream">excelStream</param> /// <param name="excelFormat">excelFormat</param> /// <returns>List</returns> public static List <TEntity> ToEntityList <TEntity>([NotNull] Stream excelStream, ExcelFormat excelFormat) where TEntity : new() => ToEntityList <TEntity>(excelStream, excelFormat, 0);
/// <summary> /// 读取Excel到List<T>(NPOI方式) /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="file">含有Excel的文件流</param> /// <param name="format">文件扩展名(.xls,.xlsx)</param> /// <returns>List<T></returns> public static List <T> ReadExcelToList <T>(Stream file, ExcelFormat format) where T : class, new() { return(ReadExcelToTable(file, format).ToList <T>()); }
/// <summary> /// read (sheetIndex) sheet of excel from excel bytes path to a list /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="excelStream">excelStream</param> /// <param name="excelFormat">excelFormat</param> /// <param name="sheetIndex">sheetIndex</param> /// <returns>List</returns> public static List <TEntity> ToEntityList <TEntity>([NotNull] Stream excelStream, ExcelFormat excelFormat, int sheetIndex) where TEntity : new() { var workbook = LoadExcel(excelStream, excelFormat); return(workbook.ToEntityList <TEntity>(sheetIndex)); }
/// <summary> /// export excel via template /// </summary> /// <typeparam name="TEntity">Entity Type</typeparam> /// <param name="entities">entities</param> /// <param name="templateBytes">templateBytes</param> /// <param name="excelFormat">excelFormat</param> /// <param name="excelPath">excelPath</param> /// <param name="sheetIndex">sheetIndex,zero by default</param> /// <param name="extraData">extraData</param> /// <returns>exported excel bytes</returns> public static void ToExcelFileByTemplate <TEntity>([NotNull] this IEnumerable <TEntity> entities, byte[] templateBytes, string excelPath, ExcelFormat excelFormat = ExcelFormat.Xls, int sheetIndex = 0, object extraData = null) { if (templateBytes == null) { throw new ArgumentNullException(nameof(templateBytes)); } if (excelPath == null) { throw new ArgumentNullException(nameof(excelPath)); } var workbook = ExcelHelper.LoadExcel(templateBytes, excelFormat); entities.ToExcelFileByTemplate(workbook, excelPath, sheetIndex, extraData); }
/// <summary> /// read first sheet of excel from excelBytes to a data table /// </summary> /// <param name="excelBytes">excelBytes</param> /// <param name="excelFormat"></param> /// <returns>DataTable</returns> public static DataTable ToDataTable([NotNull] byte[] excelBytes, ExcelFormat excelFormat) => ToDataTable(excelBytes, excelFormat, 0);
/// <summary> /// EntityList2ExcelStream /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="stream">stream where to write</param> /// <param name="excelFormat">excelFormat</param> public static void ToExcelStream <TEntity>([NotNull] this IEnumerable <TEntity> entityList, [NotNull] Stream stream, ExcelFormat excelFormat) => ToExcelStream(entityList, stream, excelFormat, 0);
/// <summary> /// read (sheetIndex) sheet of excel from excelBytes to a data table /// </summary> /// <param name="excelBytes">excelBytes</param> /// <param name="excelFormat"></param> /// <param name="sheetIndex">sheetIndex</param> /// <returns>DataTable</returns> public static DataTable ToDataTable([NotNull] byte[] excelBytes, ExcelFormat excelFormat, int sheetIndex) => ToDataTable(excelBytes, excelFormat, sheetIndex, 0);
/// <summary> /// EntityList2ExcelBytes /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="excelFormat">excelFormat</param> /// <param name="sheetIndex">sheetIndex</param> public static byte[] ToExcelBytes <TEntity>([NotNull] this IEnumerable <TEntity> entityList, ExcelFormat excelFormat, int sheetIndex) { var configuration = InternalHelper.GetExcelConfigurationMapping <TEntity>(); var workbook = ExcelHelper.PrepareWorkbook(excelFormat, configuration.ExcelSetting); workbook.ImportData(entityList.ToArray(), sheetIndex); return(workbook.ToExcelBytes()); }
/// <summary> /// EntityList2ExcelBytes /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="excelFormat">excelFormat</param> public static byte[] ToExcelBytes <TEntity>([NotNull] this IEnumerable <TEntity> entityList, ExcelFormat excelFormat) where TEntity : new() { InternalCache.TypeExcelConfigurationDictionary.TryGetValue(typeof(TEntity), out var configuration); var workbook = ExcelHelper.PrepareWorkbook(excelFormat, configuration?.ExcelSetting); workbook.ImportData(entityList.ToArray()); return(workbook.ToExcelBytes()); }
private static string GetConnectionString(ExcelFormat format, string filename) { return format == ExcelFormat.Excel2003 ? string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", filename) : string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\"", filename); }
private static string GetConnectionString(ExcelFormat format, string filename) { return(format == ExcelFormat.Excel2003 ? string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", filename) : string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\"", filename)); }