导出带图的数据到Excel

        DataTable中的图片为byte类型的string.

 

    /// <summary>
    /// 导出数据到Exce。
    /// </summary>
    public class ExcelExport
    {
        private static ExcelExport _instance = null;

        private Microsoft.Office.Interop.Excel.Application _xlApp = null;
        private Microsoft.Office.Interop.Excel.Workbooks _workbooks = null;
        private Microsoft.Office.Interop.Excel.Workbook _workbook = null;
        private Microsoft.Office.Interop.Excel.Worksheet _worksheet = null;
        private Microsoft.Office.Interop.Excel.Range _range = null;
        private object _objOpt = System.Reflection.Missing.Value;

        private ExcelExport()
        { }

        public static ExcelExport Instance
        {
            get
            {
                if (_instance == null)
                    _instance = new ExcelExport();
                return _instance;
            }
        }

        /// <summary>
        /// 导出数据。
        /// </summary>
        /// <param name="dataTable">导出数据</param>
        /// <returns>1:成功 0:失败</returns>
        public bool DoExport(System.Data.DataTable dataTable, ProcessBoxManager pbManager)
        {
            bool isExport = true;
            try
            {
                string xlsFile = TDataManager.getManager().CurrSection.FilePath + "\\" + TDataManager.getManager().CurrSection.Name + ".xlsx";
                ExportExcel(dataTable, xlsFile, "画像", "C", 80, 45, true, pbManager);
            }
            catch (Exception e)
            {
                throw e;
            }

            return isExport;
        }

        /// <summary>
        /// 导出数据到Excel。
        /// </summary>
        /// <param name="tmpDataTable">导出数据</param>
        /// <param name="strFileName">导出文件名全路径</param>
        /// <param name="imgColumnName">图片列名</param>
        /// <param name="execlImageColumName">图片列在Eexcel中列名。如:A,B,C</param>
        /// <param name="imgWidth">图片宽度</param>
        /// <param name="imgHeight">图片高度</param>
        /// <param name="isByte">图片数据为byte类型</param>
        public void ExportExcel(System.Data.DataTable tmpDataTable, string strFileName, string imgColumnName, string execlImageColumName, int imgWidth, int imgHeight, bool isByte, ProcessBoxManager pbManager)
        {
            if (tmpDataTable == null)
            {
                return;
            }

            try
            {
                long rowNum = tmpDataTable.Rows.Count;//行数 
                int columnNum = tmpDataTable.Columns.Count;//列数 
                _xlApp = new Microsoft.Office.Interop.Excel.Application();
                _xlApp.DisplayAlerts = false;//不显示更改提示 
                _xlApp.Visible = false;

                _workbooks = _xlApp.Workbooks;
                _workbook = _workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                _worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets[1];//取得sheet1

                for (int i = 0; i < columnNum; i++) //写入列名
                {
                    if (!tmpDataTable.Columns[i].ColumnName.Contains("空白列"))
                    {
                        //非空白列
                        _xlApp.Cells[1, i + 1] = tmpDataTable.Columns[i].ColumnName;
                    }
                    else
                    {
                        //空白列
                        _xlApp.Cells[1, i + 1] = "";
                    }
                }

                int r = 0;

                pbManager.PushStep(tmpDataTable.Rows.Count);
                for (r = 0; r < rowNum; r++)
                {
                    #region
                    for (int i = 0; i < columnNum; i++)
                    {

                        ////行的共同属性
                        _range = (Microsoft.Office.Interop.Excel.Range)(_worksheet.Rows[r + 2, Type.Missing]);
                        _range.RowHeight = imgHeight + 14; //设置行高  +14 避免图片紧贴着单元格边线

                        ////图片
                        if (tmpDataTable.Columns[i].ColumnName == imgColumnName)
                        {
                            #region 图片列的共同设置
                            string strTemp = "";
                            for (int j = 0; j < (imgWidth / 3) + 2; j++)
                            {
                                strTemp += " ";
                            }
                            _xlApp.Cells[r + 2, i + 1] = strTemp; //添加空格,用于自动适应格式.免得图片位置不正确.
                            #endregion

                            if (isByte)
                            {
                                #region 保存图片并添加到excel---图片二进制字符串[请使用Convert.FromBase64String转换]
                                Byte[] imgbyte = Convert.FromBase64String(tmpDataTable.Rows[r][i].ToString());
                                MemoryStream my = new MemoryStream(imgbyte);
                                Image img = Image.FromStream(my);
                                string fileNameTemp = ""; //图片暂时路径.
                                try
                                {
                                    string parentPath = System.Windows.Forms.Application.StartupPath;
                                    string hzm = "jpg"; //默认jpg
                                    #region 获取后缀名
                                    //获取后缀名
                                    if (img.RawFormat.Guid == ImageFormat.Gif.Guid)
                                    {
                                        hzm = ImageFormat.Gif.ToString();
                                    }
                                    if (img.RawFormat.Guid == ImageFormat.Png.Guid)
                                    {
                                        hzm = ImageFormat.Png.ToString();
                                    }
                                    if (img.RawFormat.Guid == ImageFormat.Bmp.Guid)
                                    {
                                        hzm = ImageFormat.Bmp.ToString();
                                    }
                                    #endregion
                                    fileNameTemp = parentPath + "\\" + Guid.NewGuid().ToString() + "." + hzm;
                                    img.Save(fileNameTemp);

                                    InsertPicture(execlImageColumName + (r + 2), fileNameTemp, imgWidth, imgHeight);
                                }
                                catch (Exception e)
                                {
                                    throw e;
                                    // 单个图片未添加成功.不作处理
                                }
                                finally
                                {
                                    File.Delete(fileNameTemp); //有没有成功都删除临时图片.
                                }
                                #endregion
                            }
                        }
                        else
                        {
                            if (i == 4)
                            {
                                _xlApp.Cells[r + 2, i + 1] = "'" + tmpDataTable.Rows[r][i].ToString();
                            }
                            else
                            {
                                _xlApp.Cells[r + 2, i + 1] = tmpDataTable.Rows[r][i].ToString();
                            }
                        }
                    }
                    #endregion

                    pbManager.NextStep();
                }
                pbManager.PopStep();

                _worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 
                //_xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;

                _workbook.Saved = true;
                _workbook.SaveCopyAs(strFileName);
                _workbook.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                KillSpecialExcel();
            }
        }

        /// <summary>
        /// 插入图片。
        /// </summary>
        /// <param name="RangeName">图片区域</param>
        /// <param name="PicturePath">图片路径</param>
        /// <param name="PictuteWidth">图片宽度</param>
        /// <param name="PictureHeight">图片高度</param>
        public void InsertPicture(string RangeName, string PicturePath, float PictuteWidth, float PictureHeight)
        {
            _range = _worksheet.get_Range(RangeName, _objOpt);

            _range.Select();
            float PicLeft, PicTop;
            PicLeft = Convert.ToSingle(_range.Left);
            PicTop = Convert.ToSingle(_range.Top);
            //参数含义：
            //图片路径 
            //是否链接到文件
            //图片插入时是否随文档一起保存 
            //图片在文档中的坐标位置（单位：points）
            //图片显示的宽度和高度（单位：points）
            _worksheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
        }

        #region Kill Special Excel Process
        /// <summary>
        /// 取得线程号
        /// </summary>
        /// <param name="hWnd"></param>
        /// <param name="lpdwProcessId"></param>
        /// <returns></returns>
        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);

        /// <summary>
        /// 关闭Excel线程
        /// </summary>
        public void KillSpecialExcel()
        {
            try
            {
                if (_xlApp != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(_xlApp.Hwnd), out lpdwProcessId);

                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception)
            {

            }
        }

        #endregion
    }
}