none
How can I insert a Bitmap object into an Excel cell without creating any temporary gif file in the server? And how can I store the Excel file using Response stream instead of FileStream? RRS feed

  • Question

  • Hi.
    Firstly, I post the following codes what I wrote.

    private void ExportDataIntoExcel(string exportedExcelFileName)
        {
            DataTable data = new DataTable();
            GetDataTableForExportingIntoExcelFile(data);
          
            Microsoft.Office.Interop.Excel.Application thisExcelApplication = null;
            Microsoft.Office.Interop.Excel.Workbook thisWorkBook = null;
            string imageFileTempPath = @"~\pages\cmsPatent\ExportImageTemp";        
            try
            {
                thisExcelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                thisWorkBook = thisExcelApplication.Application.Workbooks.Add(Type.Missing);
                Microsoft.Office.Interop.Excel.Worksheet thisWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)thisWorkBook.Worksheets[1];
                for (int i = 0; i < data.Rows.Count; ++i)
                {
                    for (int j = 0; j < data.Columns.Count; ++j)
                    {
                        if (i == 0)
                        {
                            ((Microsoft.Office.Interop.Excel.Range)thisWorksheet.Cells[1, j + 1]).Value2 = data.Columns[j].ColumnName;
                        }
                        if (string.Compare(data.Columns[j].ColumnName, "Image", false) != 0)
                        {
                            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)thisWorksheet.Cells[i + 2, j + 1];
                            range.Value2 = data.Rows[i][j];
                            range.WrapText = true;
                            range.ColumnWidth = 13;
                            continue;
                        }
                        // if (string.Compare(data.Columns[j].ColumnName, "Image", false) == 0):
                        if (DBNull.Value.Equals(data.Rows[i][j]))
                        {
                            ((Microsoft.Office.Interop.Excel.Range)thisWorksheet.Cells[i + 2, j + 1]).Value2 = string.Empty;
                            continue;
                        }                    
                        string virtualImageFileTempPath = HttpContext.Current.Server.MapPath(imageFileTempPath);
                        if (!Directory.Exists(virtualImageFileTempPath))
                        {
                            Directory.CreateDirectory(virtualImageFileTempPath);
                        }
                        Random random = new Random();
                        virtualImageFileTempPath += @"\tmp" + random.Next() + "_" + DateTime.Now.ToString("MMddyyyyHHmmss") + ".gif";
                        #region Create the temporary file tmp***.gif, which will be insert into an Excel cell.
                        using (FileStream fs = new FileStream(virtualImageFileTempPath, FileMode.Create, FileAccess.ReadWrite))
                        {                       
                            byte[] firstPageImageData = null;
                            firstPageImageData = (byte[])data.Rows[i][j];
                            
                            int firstPageImageDataSize = new int();
                            firstPageImageDataSize = firstPageImageData.GetUpperBound(0);
                            
                            // This will create the temporary file tmp***.gif, which will be insert into an Excel cell. 
                            fs.Write(firstPageImageData, 0, firstPageImageDataSize + 1); // had to add 1 here for this to work
                            //fs.Close();
                            //fs.Dispose();
                        }
                        #endregion
                        #region Insert the temporary tmp***.gif file into an Excel cell.
                        // Assign the temporary tmp***.gif file to be inserted into an Excel cell.
                        using (Bitmap imgBitmap = new Bitmap(virtualImageFileTempPath))
                        {                     
                            if (imgBitmap == null || imgBitmap.Width < 1 || imgBitmap.Height < 1)
                            {
                                ((Microsoft.Office.Interop.Excel.Range)thisWorksheet.Cells[i + 2, j + 1]).Value2 = string.Empty;
                                continue;
                            }
                            Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)thisWorksheet.Cells[i + 2, j + 1];
                            float Left = (float)((double)oRange.Left + 3);
                            float Top = (float)((double)oRange.Top + 3);
                            thisWorksheet.Shapes.AddPicture(virtualImageFileTempPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, (float)imgBitmap.Width, (float)imgBitmap.Height);
                            if ((imgBitmap.Width + 5) > 255)
                            {
                                oRange.ColumnWidth = 59;
                            }
                            else
                            {
                                oRange.ColumnWidth = imgBitmap.Width + 5;
                            }
                            if ((imgBitmap.Height + 5) > 409)
                            {
                                oRange.RowHeight = 409;
                            }
                            else
                            {
                                oRange.RowHeight = imgBitmap.Height + 5;
                            }
                            //imgBitmap.Dispose();
                        }
                        #endregion
                        if (File.Exists(virtualImageFileTempPath))
                        {
                            File.Delete(virtualImageFileTempPath);
                        }
                    }
                }
                string excelFullName = imageFileTempPath + @"\" + exportedExcelFileName + ".xls";
                thisWorkBook.SaveCopyAs(excelFullName);
                thisWorkBook.Saved = true;
                /*
                thisWorkBook.Close(false, null, null);
                thisExcelApplication.Quit();
                thisWorkBook = null;
                thisExcelApplication = null;
                GC.Collect();           
                */
                thisWorkBook.Close(true, excelFullName, false);
                if (thisExcelApplication != null)
                {
                    thisExcelApplication.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(thisExcelApplication);
                    thisExcelApplication = null;
                }
                GC.Collect();            
                if (!File.Exists(excelFullName))
                {
                    throw new Exception();
                }
                #region Export records in this DataTable into an Excel file.
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + exportedExcelFileName + ".xls");
                //HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                HttpContext.Current.Response.ContentType = "application/x-msexcel";
                HttpContext.Current.Response.Charset = "";
                HttpContext.Current.Response.WriteFile(excelFullName);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
                #endregion
                File.Delete(excelFullName);
            }
            catch (Exception excelException)
            {
                
            }
            finally
            {
                /*
                if (thisWorkBook != null)
                {
                    thisWorkBook.Close(false, null, null);                           
                }
                */
                if (thisExcelApplication != null)
                {
                    thisExcelApplication.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(thisExcelApplication);
                    thisWorkBook = null;
                    thisExcelApplication = null;
                }            
                GC.Collect();
                if (!string.IsNullOrEmpty(imageFileTempPath) && Directory.Exists(imageFileTempPath))
                {
                    var files = Directory.EnumerateFiles(imageFileTempPath, "*.*");
                    if (files != null)
                    {
                        foreach (string currentFile in files)
                        {
                            File.Delete(currentFile);
                        }
                    }
                    Directory.Delete(imageFileTempPath);
                }
            }
        }    
        private void GetDataTableForExportingIntoExcelFile(DataTable data)
        {
            data.Columns.Add(new DataColumn("RowNumber", typeof(int)));
            data.Columns.Add(new DataColumn("Image", typeof(byte[])));
            for (int i = 1; i <= 5000; ++i)
            {
                DataRow dr = new DataRow();
                dr["RowNumber"] = i;
                dr["Image"] = ConvertImageUrlIntoImageByteData(@"~\images\" + i + ".gif");
                data.Rows.Add(dr);
            }        
        }
        private byte[] ConvertImageUrlIntoImageByteData(string imageUrl)
        {
            byte[] imageByteData = null;
            try
            {
                System.Net.WebRequest request = System.Net.WebRequest.Create(imageUrl);
                System.Net.WebResponse responseWeb = request.GetResponse();
                Stream responseStream = responseWeb.GetResponseStream();
                var imgBitmap = new Bitmap(responseStream);
                responseStream.Close();
                if (imgBitmap == null || imgBitmap.Width < 1 || imgBitmap.Height < 1)
                {
                    return null;
                }
                ImageConverter converter = new ImageConverter();
                imageByteData = (byte[])converter.ConvertTo(imgBitmap, typeof(byte[]));
                if (imageByteData == null || imageByteData.Length < 1)
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                return null;
            }
            return imageByteData;
        }

    I have 2 questions.
    1. Although the method "ExportDataIntoExcel" located in the above codes can make me export data into an Excel file successfully, I do not want to create any tempopary gif file in the server before inserting a Bitmap object into an Excel cell. I have that idea because I think less writing to the server is better. So How can I insert a Bitmap object into an Excel cell without creating any temporary gif file in the server?

    I guess the following codes need to be modified for solving this question, so if you do not want to read the above long codes, please consentrating on the following codes.

    #region Create the temporary file tmp***.gif, which will be insert into an Excel cell.
                        using (FileStream fs = new FileStream(virtualImageFileTempPath, FileMode.Create, FileAccess.ReadWrite))
                        {                       
                            byte[] firstPageImageData = null;
                            firstPageImageData = (byte[])data.Rows[i][j];
                            
                            int firstPageImageDataSize = new int();
                            firstPageImageDataSize = firstPageImageData.GetUpperBound(0);
                            
                            // This will create the temporary file tmp***.gif, which will be insert into an Excel cell. 
                            fs.Write(firstPageImageData, 0, firstPageImageDataSize + 1); // had to add 1 here for this to work
                            //fs.Close();
                            //fs.Dispose();
                        }
                        #endregion
                        #region Insert the temporary tmp***.gif file into an Excel cell.
                        // Assign the temporary tmp***.gif file to be inserted into an Excel cell.
                        using (Bitmap imgBitmap = new Bitmap(virtualImageFileTempPath))
                        {                     
                            if (imgBitmap == null || imgBitmap.Width < 1 || imgBitmap.Height < 1)
                            {
                                ((Microsoft.Office.Interop.Excel.Range)thisWorksheet.Cells[i + 2, j + 1]).Value2 = string.Empty;
                                continue;
                            }
                            Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)thisWorksheet.Cells[i + 2, j + 1];
                            float Left = (float)((double)oRange.Left + 3);
                            float Top = (float)((double)oRange.Top + 3);
                            thisWorksheet.Shapes.AddPicture(virtualImageFileTempPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, (float)imgBitmap.Width, (float)imgBitmap.Height);
                            if ((imgBitmap.Width + 5) > 255)
                            {
                                oRange.ColumnWidth = 59;
                            }
                            else
                            {
                                oRange.ColumnWidth = imgBitmap.Width + 5;
                            }
                            if ((imgBitmap.Height + 5) > 409)
                            {
                                oRange.RowHeight = 409;
                            }
                            else
                            {
                                oRange.RowHeight = imgBitmap.Height + 5;
                            }
                            //imgBitmap.Dispose();
                        }
                        #endregion

    2. How can I store the Excel file using Response stream instead of FileStream?

    Thank you in advance.




    QQ

    Sunday, May 5, 2013 10:18 AM

Answers