public Dictionary <string, object> ReadCellData(byte[] data, SheetInfo sheetInfo)
{
Dictionary <string, object> m_DataOutput = new Dictionary <string, object>();
using (var stream = new MemoryStream(data))
{
m_DataOutput = this.ReadCellData(data, sheetInfo);
}
return(m_DataOutput);
}
public byte[] AddImage(string imageName, SheetInfo sheetInfo, string address, int height, int width, byte[] imageBytes)
{
byte[] m_DataOutput = null;
using (ExcelPackage m_ExcelPackage = new ExcelPackage())
{
// Open the Excel file and load it to the ExcelPackage
using (var stream = new MemoryStream(this.TemplateFileData))
{
m_ExcelPackage.Load(stream);
}
ExcelWorksheet m_ExcelWorksheet = null;
if (sheetInfo != null)
{
m_ExcelWorksheet = sheetInfo.SheetIndex > 0 ? m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetIndex] : m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetName];
}
else
{
m_ExcelWorksheet = m_ExcelPackage.Workbook.Worksheets.FirstOrDefault();
}
if (m_ExcelWorksheet != null)
{
Bitmap image;
using (var img = new MemoryStream(imageBytes))
{
image = new Bitmap(img);
ExcelPicture excelImage = null;
if (image != null)
{
using (var DefineName = m_ExcelPackage.Workbook.Names[address])
{
if (DefineName != null)
{
excelImage = m_ExcelWorksheet.Drawings.AddPicture(imageName, image);
excelImage.From.Column = DefineName.Start.Column;
excelImage.From.Row = DefineName.Start.Row;
excelImage.SetSize(width, height);
// 2x2 px space for better alignment
excelImage.From.ColumnOff = Pixel2MTU(2);
excelImage.From.RowOff = Pixel2MTU(2);
}
}
}
}
}
else
{
throw new ArgumentException("Không tìm thấy Sheet tương ứng");
}
m_DataOutput = m_ExcelPackage.GetAsByteArray();
}
return(m_DataOutput);
}
public object[,] ReadData(byte[] data, SheetInfo sheetInfo)
{
object[,] m_DataOutput = null;
//2. Import Excel
using (var stream = new MemoryStream(data))
{
m_DataOutput = this.ReadData(stream, sheetInfo);
}
return(m_DataOutput);
}
public byte[] Export(Dictionary <string, string> dicEntities, SheetInfo sheetInfo)
{
byte[] m_OutputData = null;
using (ExcelPackage m_ExcelPackage = new ExcelPackage())
{
// Open the Excel file and load it to the ExcelPackage
using (var stream = new MemoryStream(this.TemplateFileData))
{
m_ExcelPackage.Load(stream);
}
ExcelWorksheet m_ExcelWorksheet = null;
if (sheetInfo != null)
{
m_ExcelWorksheet = sheetInfo.SheetIndex > 0 ? m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetIndex] : m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetName];
}
else
{
m_ExcelWorksheet = m_ExcelPackage.Workbook.Worksheets.FirstOrDefault();
}
if (m_ExcelWorksheet != null)
{
using (var m_Cells = m_ExcelWorksheet.Cells)
{
foreach (var m_dicEntitie in dicEntities)
{
try
{
using (var DefineName = m_ExcelPackage.Workbook.Names[m_dicEntitie.Key])
{
if (DefineName != null)
{
m_Cells[DefineName.Start.Row, DefineName.Start.Column].Value = m_dicEntitie.Value;
}
}
}
catch (Exception) { }
}
}
}
else
{
throw new ArgumentException("Không tìm thấy Sheet tương ứng");
}
m_OutputData = m_ExcelPackage.GetAsByteArray();
}
return(m_OutputData);
}
public Dictionary <string, object> ReadCellData(Stream stream, SheetInfo sheetInfo)
{
Dictionary <string, object> m_DataOutput = new Dictionary <string, object>();
using (ExcelPackage m_ExcelPackage = new ExcelPackage())
{
// Open the Excel file and load it to the ExcelPackage
m_ExcelPackage.Load(stream);
ExcelWorksheet m_ExcelWorksheet = null;
if (sheetInfo != null)
{
m_ExcelWorksheet = sheetInfo.SheetIndex > 0 ? m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetIndex] : m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetName];
}
else
{
m_ExcelWorksheet = m_ExcelPackage.Workbook.Worksheets.FirstOrDefault();
}
if (m_ExcelWorksheet != null)
{
//Get Define Name
foreach (var DefineName in m_ExcelPackage.Workbook.Names)
{
m_DataOutput.Add(DefineName.Name, m_ExcelWorksheet.Cells[DefineName.Start.Row, DefineName.Start.Column].Text);
DefineName.Dispose();
}
//Get Cells
foreach (var cell in m_ExcelWorksheet.Cells)
{
m_DataOutput.Add(cell.Address, cell.Value);
}
}
else
{
throw new ArgumentException("Không tìm thấy Sheet tương ứng");
}
}
return(m_DataOutput);
}
public object[,] ReadData(Stream stream, SheetInfo sheetInfo)
{
object[,] m_DataOutput = null;
//2. Import Excel
using (ExcelPackage m_ExcelPackage = new ExcelPackage())
{
// Open the Excel file and load it to the ExcelPackage
m_ExcelPackage.Load(stream);
ExcelWorksheet m_ExcelWorksheet = null;
if (sheetInfo != null)
{
m_ExcelWorksheet = sheetInfo.SheetIndex > 0 ? m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetIndex] : m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetName];
}
else
{
m_ExcelWorksheet = m_ExcelPackage.Workbook.Worksheets.FirstOrDefault();
}
if (m_ExcelWorksheet != null)
{
var m_Dimension = m_ExcelWorksheet.Dimension;
var m_Cells = m_ExcelWorksheet.Cells;
m_DataOutput = new object[m_Dimension.Rows, m_Dimension.Columns];
for (int m_RowIndex = 0; m_RowIndex < m_Dimension.Rows; m_RowIndex++)
{
for (int m_ColumnIndex = 0; m_ColumnIndex < m_Dimension.Columns; m_ColumnIndex++)
{
m_DataOutput[m_RowIndex, m_ColumnIndex] = m_Cells[m_RowIndex + 1, m_ColumnIndex + 1].Value;
}
}
}
else
{
throw new ArgumentException("Không tìm thấy Sheet tương ứng");
}
}
return(m_DataOutput);
}