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