/// <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));
}
