451 lines
16 KiB
C#
451 lines
16 KiB
C#
namespace Kit.Helpers
|
|
{
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text.RegularExpressions;
|
|
using System.IO;
|
|
using DocumentFormat.OpenXml.Spreadsheet;
|
|
using DocumentFormat.OpenXml.Packaging;
|
|
using Microsoft.AspNetCore.Http;
|
|
|
|
public class ExcelReader
|
|
{
|
|
private string GetColumnName(string cellReference)
|
|
{
|
|
var regex = new Regex("[A-Za-z]+");
|
|
var match = regex.Match(cellReference);
|
|
|
|
return match.Value;
|
|
}
|
|
|
|
private int ConvertColumnNameToNumber(string columnName)
|
|
{
|
|
var alpha = new Regex("^[A-Z]+$");
|
|
if (!alpha.IsMatch(columnName)) throw new ArgumentException();
|
|
|
|
char[] colLetters = columnName.ToCharArray();
|
|
Array.Reverse(colLetters);
|
|
|
|
var convertedValue = 0;
|
|
for (int i = 0; i < colLetters.Length; i++)
|
|
{
|
|
char letter = colLetters[i];
|
|
int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
|
|
convertedValue += current * (int)Math.Pow(26, i);
|
|
}
|
|
|
|
return convertedValue;
|
|
}
|
|
|
|
private IEnumerator<Cell> GetExcelCellEnumerator(Row row)
|
|
{
|
|
int currentCount = 0;
|
|
foreach (Cell cell in row.Descendants<Cell>())
|
|
{
|
|
string columnName = GetColumnName(cell.CellReference);
|
|
|
|
int currentColumnIndex = ConvertColumnNameToNumber(columnName);
|
|
|
|
for (; currentCount < currentColumnIndex; currentCount++)
|
|
{
|
|
var emptycell = new Cell() { DataType = null, CellValue = new CellValue(string.Empty) };
|
|
yield return emptycell;
|
|
}
|
|
|
|
yield return cell;
|
|
currentCount++;
|
|
}
|
|
}
|
|
|
|
private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
|
|
{
|
|
var cellValue = cell.CellValue;
|
|
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
|
|
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
|
|
{
|
|
text = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(
|
|
Convert.ToInt32(cell.CellValue.Text)).InnerText;
|
|
}
|
|
|
|
return (text ?? string.Empty).Trim();
|
|
}
|
|
|
|
private PatternFill GetCellPatternFill(Cell cell, WorkbookPart workbookPart)
|
|
{
|
|
WorkbookStylesPart styles = workbookPart.WorkbookStylesPart;
|
|
if (styles == null)
|
|
{
|
|
return null;
|
|
}
|
|
|
|
int cellStyleIndex;
|
|
if (cell.StyleIndex == null) // I think (from testing) if the StyleIndex is null
|
|
{ // then this means use cell style index 0.
|
|
cellStyleIndex = 0; // However I did not found it in the open xml
|
|
} // specification.
|
|
else
|
|
{
|
|
cellStyleIndex = (int)cell.StyleIndex.Value;
|
|
}
|
|
|
|
CellFormat cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];
|
|
|
|
Fill fill = (Fill)styles.Stylesheet.Fills.ChildElements[(int)cellFormat.FillId.Value];
|
|
return fill.PatternFill;
|
|
}
|
|
|
|
protected ExcelData ReadSheet(WorkbookPart workbookPart, Sheet sheet)
|
|
{
|
|
var data = new ExcelData();
|
|
List<Row> rows;
|
|
try
|
|
{
|
|
data.SheetName = sheet.Name;
|
|
|
|
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
|
|
var workSheet = worksheetPart.Worksheet;
|
|
var columns = workSheet.Descendants<Columns>().FirstOrDefault();
|
|
data.ColumnConfigurations = columns;
|
|
|
|
var comments = GetComments(worksheetPart);
|
|
var sheetData = workSheet.Elements<SheetData>().First();
|
|
|
|
this.DeleteBlankRows(sheetData);
|
|
|
|
rows = sheetData.Elements<Row>().ToList();
|
|
|
|
//if (rows.Count > 0)
|
|
//{
|
|
// var row = rows[0];
|
|
// var cellEnumerator = GetExcelCellEnumerator(row);
|
|
// while (cellEnumerator.MoveNext())
|
|
// {
|
|
// var cell = cellEnumerator.Current;
|
|
// var text = ReadExcelCell(cell, workbookPart).Trim();
|
|
// data.Headers.Add(text);
|
|
// }
|
|
//}
|
|
|
|
// Read the sheet data
|
|
if (rows.Count > 0)
|
|
{
|
|
for (var i = 0; i < rows.Count; i++)
|
|
{
|
|
var cellRow = new ExcelRow();
|
|
var dataRow = new List<string>();
|
|
var patternFillRow = new List<PatternFill>();
|
|
var row = rows[i];
|
|
var cellEnumerator = GetExcelCellEnumerator(row);
|
|
int z = 0;
|
|
while (cellEnumerator.MoveNext())
|
|
{
|
|
var cell = cellEnumerator.Current;
|
|
var text = ReadExcelCell(cell, workbookPart).Trim();
|
|
var comment = comments.FirstOrDefault(x => x.Reference == cell.CellReference);
|
|
var patternFill = GetCellPatternFill(cell, workbookPart);
|
|
if (patternFill != null)
|
|
{
|
|
patternFillRow.Add(patternFill);
|
|
}
|
|
|
|
dataRow.Add(text);
|
|
cellRow.Cells.Add(new ExcelCell
|
|
{
|
|
IsHeader = i == 0,
|
|
Comment = comment,
|
|
Header = (i == 0 ? text : z < data.Headers.Count() ? data.Headers[z] : string.Empty),
|
|
PatternFill = patternFill,
|
|
Value = text
|
|
});
|
|
z++;
|
|
}
|
|
data.DataRowCells.Add(cellRow);
|
|
if (i == 0)
|
|
{
|
|
data.Headers = dataRow;
|
|
}
|
|
else
|
|
{
|
|
data.DataRows.Add(dataRow);
|
|
data.PatternFillRows.Add(patternFillRow);
|
|
}
|
|
}
|
|
}
|
|
|
|
// Read the sheet PatternFill
|
|
//if (rows.Count > 1)
|
|
//{
|
|
// for (var i = 1; i < rows.Count; i++)
|
|
// {
|
|
// var dataRow = new List<PatternFill>();
|
|
// data.PatternFillRows.Add(dataRow);
|
|
// var row = rows[i];
|
|
// var cellEnumerator = GetExcelCellEnumerator(row);
|
|
// while (cellEnumerator.MoveNext())
|
|
// {
|
|
// var cell = cellEnumerator.Current;
|
|
// var patternFill = GetCellPatternFill(cell, workbookPart);
|
|
// dataRow.Add(patternFill);
|
|
// }
|
|
// }
|
|
//}
|
|
|
|
return data;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
data.Status.Message = "Unable to open the file";
|
|
return data;
|
|
}
|
|
}
|
|
|
|
|
|
protected IEnumerable<ExcelData> ReadAllExcel(Stream stream)
|
|
{
|
|
var datas = new List<ExcelData>();
|
|
WorkbookPart workbookPart; List<Row> rows;
|
|
try
|
|
{
|
|
var document = SpreadsheetDocument.Open(stream, false);
|
|
workbookPart = document.WorkbookPart;
|
|
|
|
var sheets = workbookPart.Workbook.Descendants<Sheet>();
|
|
|
|
foreach (var sheet in sheets)
|
|
{
|
|
var data = ReadSheet(workbookPart, sheet);
|
|
datas.Add(data);
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
datas.First().Status.Message = "Unable to open the file";
|
|
return datas;
|
|
}
|
|
|
|
return datas;
|
|
|
|
}
|
|
public IEnumerable<ExcelData> ReadAllExcel(IFormFile file)
|
|
{
|
|
var datas = new List<ExcelData>();
|
|
// Check if the file is excel
|
|
if (file.Length <= 0)
|
|
{
|
|
datas.First().Status.Message = "You uploaded an empty file";
|
|
return datas;
|
|
}
|
|
|
|
if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
|
|
{
|
|
datas.First().Status.Message = "Please upload a valid excel file of version 2007 and above";
|
|
return datas;
|
|
}
|
|
|
|
using (Stream stream = file.OpenReadStream())
|
|
{
|
|
return ReadAllExcel(stream);
|
|
}
|
|
// Open the excel document
|
|
//WorkbookPart workbookPart; List<Row> rows;
|
|
//try
|
|
//{
|
|
// var document = SpreadsheetDocument.Open(file.InputStream, false);
|
|
// workbookPart = document.WorkbookPart;
|
|
|
|
// var sheets = workbookPart.Workbook.Descendants<Sheet>();
|
|
|
|
// foreach (var sheet in sheets)
|
|
// {
|
|
// var data = new ExcelData();
|
|
// data.SheetName = sheet.Name;
|
|
|
|
// var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
|
|
// var workSheet = worksheetPart.Worksheet;
|
|
// var columns = workSheet.Descendants<Columns>().FirstOrDefault();
|
|
// data.ColumnConfigurations = columns;
|
|
|
|
// var comments = GetComments(worksheetPart);
|
|
// var sheetData = workSheet.Elements<SheetData>().First();
|
|
|
|
// rows = sheetData.Elements<Row>().ToList();
|
|
|
|
// if (rows.Count > 0)
|
|
// {
|
|
// var row = rows[0];
|
|
// var cellEnumerator = GetExcelCellEnumerator(row);
|
|
// while (cellEnumerator.MoveNext())
|
|
// {
|
|
// var cell = cellEnumerator.Current;
|
|
// var text = ReadExcelCell(cell, workbookPart).Trim();
|
|
// data.Headers.Add(text);
|
|
// }
|
|
// }
|
|
|
|
// // Read the sheet data
|
|
// if (rows.Count > 1)
|
|
// {
|
|
// for (var i = 1; i < rows.Count; i++)
|
|
// {
|
|
// var dataRow = new List<string>();
|
|
// data.DataRows.Add(dataRow);
|
|
// var row = rows[i];
|
|
// var cellEnumerator = GetExcelCellEnumerator(row);
|
|
// while (cellEnumerator.MoveNext())
|
|
// {
|
|
// var cell = cellEnumerator.Current;
|
|
// var text = ReadExcelCell(cell, workbookPart).Trim();
|
|
// var patternFill = GetCellPatternFill(cell, workbookPart);
|
|
|
|
// dataRow.Add(text);
|
|
// }
|
|
// }
|
|
// }
|
|
|
|
// // Read the sheet PatternFill
|
|
// if (rows.Count > 1)
|
|
// {
|
|
// for (var i = 1; i < rows.Count; i++)
|
|
// {
|
|
// var dataRow = new List<PatternFill>();
|
|
// data.PatternFillRows.Add(dataRow);
|
|
// var row = rows[i];
|
|
// var cellEnumerator = GetExcelCellEnumerator(row);
|
|
// while (cellEnumerator.MoveNext())
|
|
// {
|
|
// var cell = cellEnumerator.Current;
|
|
// var patternFill = GetCellPatternFill(cell, workbookPart);
|
|
// dataRow.Add(patternFill);
|
|
// }
|
|
// }
|
|
// }
|
|
|
|
// datas.Add(data);
|
|
// }
|
|
|
|
|
|
//}
|
|
//catch (Exception e)
|
|
//{
|
|
// datas.First().Status.Message = "Unable to open the file";
|
|
// return datas;
|
|
//}
|
|
|
|
return datas;
|
|
|
|
}
|
|
|
|
|
|
public ExcelData ReadExcel(IFormFile file)
|
|
{
|
|
var data = new ExcelData();
|
|
|
|
// Check if the file is excel
|
|
if (file.Length <= 0)
|
|
{
|
|
data.Status.Message = "You uploaded an empty file";
|
|
return data;
|
|
}
|
|
|
|
if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
|
|
{
|
|
data.Status.Message = "Please upload a valid excel file of version 2007 and above";
|
|
return data;
|
|
}
|
|
|
|
// Open the excel document
|
|
WorkbookPart workbookPart; List<Row> rows;
|
|
try
|
|
{
|
|
using (Stream stream = file.OpenReadStream())
|
|
{
|
|
return ReadExcel(stream);
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
data.Status.Message = "Unable to open the file";
|
|
return data;
|
|
}
|
|
}
|
|
|
|
public ExcelData ReadExcel(Stream file,string sheetName)
|
|
{
|
|
var data = new ExcelData();
|
|
|
|
// Open the excel document
|
|
WorkbookPart workbookPart; List<Row> rows;
|
|
try
|
|
{
|
|
var document = SpreadsheetDocument.Open(file, false);
|
|
workbookPart = document.WorkbookPart;
|
|
|
|
var sheets = workbookPart.Workbook.Descendants<Sheet>();
|
|
var sheet = sheets.FirstOrDefault(x => x.Name == sheetName);
|
|
data = ReadSheet(workbookPart, sheet);
|
|
|
|
return data;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
data.Status.Message = "Unable to open the file";
|
|
return data;
|
|
}
|
|
}
|
|
|
|
public void DeleteBlankRows(SheetData sheet)
|
|
{
|
|
|
|
var rows = sheet.Elements<Row>().ToList();
|
|
for (var rowIndex = 0; rowIndex < rows.Count; rowIndex++)
|
|
{
|
|
var row = rows[rowIndex];
|
|
if (string.IsNullOrWhiteSpace(row.InnerText)) row.Remove();
|
|
}
|
|
}
|
|
|
|
public ExcelData ReadExcel(Stream file)
|
|
{
|
|
var data = new ExcelData();
|
|
|
|
// Open the excel document
|
|
WorkbookPart workbookPart; List<Row> rows;
|
|
try
|
|
{
|
|
var document = SpreadsheetDocument.Open(file, false);
|
|
workbookPart = document.WorkbookPart;
|
|
|
|
var sheets = workbookPart.Workbook.Descendants<Sheet>();
|
|
var sheet = sheets.FirstOrDefault();
|
|
|
|
data = ReadSheet(workbookPart, sheet);
|
|
|
|
return data;
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
data.Status.Message = "Unable to open the file";
|
|
return data;
|
|
}
|
|
}
|
|
public IEnumerable<ExcelComment> GetComments(WorksheetPart sheet)
|
|
{
|
|
var result = new List<ExcelComment>();
|
|
foreach (WorksheetCommentsPart commentsPart in sheet.GetPartsOfType<WorksheetCommentsPart>())
|
|
{
|
|
foreach (Comment comment in commentsPart.Comments.CommentList)
|
|
{
|
|
var newComment = new ExcelComment
|
|
{
|
|
Reference = comment.Reference,
|
|
Comment = comment.FirstChild.InnerText.Trim()
|
|
};
|
|
result.Add(newComment);
|
|
}
|
|
}
|
|
|
|
return result;
|
|
}
|
|
}
|
|
} |