public DatasetWorksheetValidator()
{
RuleFor(model => model)
.NotNull()
.Custom((excel, context) => {
ExcelWorksheet firstWorkSheet = excel.Workbook.Worksheets[1];
ExcelRange firstCell = firstWorkSheet.Cells[1, 1];
if (firstCell.Value == null || string.IsNullOrWhiteSpace(firstCell.Value.ToString()))
{
context.AddFailure("Excel file does not contain any values");
return;
}
MergeCellsCollection mergedCells = firstWorkSheet.MergedCells;
if (mergedCells.Count > 0)
{
string mergedCellLocations = string.Join(",", mergedCells);
context.AddFailure("Excel file contains merged cells");
return;
}
});
}
/// <summary>
/// Hỗ trợ định dạng mới của excel (*.xlsx).
/// </summary>
/// <param name="fileName"></param>
public void InsertFromExcel(string fileName)
{
FileInfo excelFile = new FileInfo(fileName);
using (var excel = new ExcelPackage(excelFile, false))
{
ExcelWorkbook workbook = excel.Workbook;
ExcelWorksheets worksheets = workbook.Worksheets;
foreach (ExcelWorksheet sheet in worksheets)
{
ExcelAddressBase activeRange = sheet.Dimension;
if (activeRange != null)
{
int columnLength = activeRange.Columns;
int rowLength = activeRange.Rows;
ExcelRange cells = sheet.Cells[activeRange.Start.Row, activeRange.Start.Column, activeRange.End.Row, activeRange.End.Column];
// ExcelStyle fullStyle = cells.Style;
ITable table = Table.Create(columnLength);
// Duyệt từng dòng.
for (int i = 1; i <= rowLength; i++)
{
ITableRow tableRow = table.NewRow();
// Duyệt từng cột.
//int columnIndex = 1;
for (int j = 1; j <= columnLength; j++)
{
ITableCell tableCell = tableRow.NewCell();
ExcelRange cell = sheet.Cells[i, j];
// tableCell.BackgoundColor = cell.Style.Fill.BackgroundColor.ToReportColorByExcelColor();
var style = cell.Style;
var fill = style.Fill;
var font = style.Font;
// border
if (style.Border.Top.Style == ExcelBorderStyle.None)
{
tableCell.Border.Top.Style = BorderStyle.None;
}
else
{
tableCell.Border.Top.Style = BorderStyle.Solid;
}
if (style.Border.Right.Style == ExcelBorderStyle.None)
{
tableCell.Border.Right.Style = BorderStyle.None;
}
else
{
tableCell.Border.Right.Style = BorderStyle.Solid;
}
if (style.Border.Bottom.Style == ExcelBorderStyle.None)
{
tableCell.Border.Bottom.Style = BorderStyle.None;
}
else
{
tableCell.Border.Bottom.Style = BorderStyle.Solid;
}
if (style.Border.Left.Style == ExcelBorderStyle.None)
{
tableCell.Border.Left.Style = BorderStyle.None;
}
else
{
tableCell.Border.Left.Style = BorderStyle.Solid;
}
if (string.IsNullOrEmpty(cell.Text))
{
// Gán cell đầu tiên của hàng là 1 space để nếu hàng có tất cả các cell empty thì in ra PDF vẫn có hàng đó (mặc định không hiển thị).
if (j == 1)
{
tableCell.Value = " ";
}
}
else
{
if (cell.IsRichText)
{
tableCell.Value = cell.Text;
}
else
{
tableCell.Value = cell.Text.Replace("\n", " ");
}
if (font.Bold && font.Italic)
{
tableCell.Style = FontStyle.BoldItalic;
}
else if (font.Bold)
{
tableCell.Style = FontStyle.Bold;
}
else if (font.Italic)
{
tableCell.Style = FontStyle.Italic;
}
tableCell.FontSize = font.Size;
// align
tableCell.HorizontalAlignment = style.HorizontalAlignment.ToHorizontalAlignment();
tableCell.VerticalAlignment = style.VerticalAlignment.ToVerticalAlignment();
}
}
}
// caculate merge cells.
MergeCellsCollection mergeCells = sheet.MergedCells;
foreach (string mergeCell in mergeCells)
{
var mergeRange = sheet.Cells[mergeCell];
int startRow = mergeRange.Start.Row;
int startColumn = mergeRange.Start.Column;
int endRow = mergeRange.End.Row;
int endColumn = mergeRange.End.Column;
int colspan = endColumn - startColumn + 1;
int rowspan = endRow - startRow + 1;
for (int i = startRow; i <= endRow; i++)
{
for (int j = startColumn; j <= endColumn; j++)
{
ITableCell cellMerge = table.Rows[i - 1].Cells[j - 1];
if (i == startRow && j == startColumn)
{
if (colspan > 1)
{
cellMerge.Colspan = colspan;
}
if (rowspan > 1)
{
cellMerge.Rowspan = rowspan;
}
}
else
{
((TableCell)cellMerge).IsMerge = true;
}
}
}
}
// Xoá tất cả cell merge có Colspan <= 1 && Rowspan <= 1
// IEnumerable<ITableCell> mergeCellsTmp = table.Rows.SelectMany(row => row.Cells).Where(cell => cell.IsMerge && (!cell.Colspan.HasValue || cell.Colspan <= 1) && (!cell.Rowspan.HasValue || cell.Rowspan <= 1));
foreach (ITableRow row in table.Rows)
{
row.Cells.RemoveAll(cell => cell.IsMerge && (!cell.Colspan.HasValue || cell.Colspan <= 1) && (!cell.Rowspan.HasValue || cell.Rowspan <= 1));
}
AddElement(table);
}
}
}
}