在EXECL文件中同時導入數(shù)據(jù)和插入圖片的方法

/// <summary>/// /// </summary>/// <param name="dt">數(shù)據(jù)源</param>/// <param name="filepath">文件路勁例如（E:\\Execl\\201706070001.xls）</param>protected void ExportExcel(DataTable dt, string filepath)
        {if (dt == null || dt.Rows.Count == 0) return;

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();object m_objOpt = System.Reflection.Missing.Value;if (xlApp == null)
            {return;
            }//System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

            Microsoft.Office.Interop.Excel.Range range;long totalCount = dt.Rows.Count;long rowRead = 0;for (int i = 0; i < dt.Columns.Count; i++)

            {

                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];

                range.Interior.ColorIndex = 15;

            }for (int r = 0; r < dt.Rows.Count; r++)
            {for (int i = 0; i < dt.Columns.Count; i++)
                {try{if (i == 4)
                        {
                            range = worksheet.get_Range("E" + (r + 2), m_objOpt); //這里是因為我要給文檔的E列插入圖片，所以加了判斷，并寫死了E列
                            range.Select();float PicLeft, PicTop;
                            PicLeft = Convert.ToSingle(range.Left);
                            PicTop = Convert.ToSingle(range.Top);

                            range.ColumnWidth = 60;//設(shè)置單元格的寬
                            range.RowHeight = 120;//設(shè)置單元格的高worksheet.Shapes.AddPicture(dt.Rows[r][i].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, PicLeft, PicTop, 100, 100);//這后面的100 是設(shè)置圖片的寬高
                        }elseworksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                    }catch{
                        worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString().Replace("=", "");
                    }
                }
                rowRead++;
            }


            xlApp.Visible = true;

            workbook.Saved = true;

            workbook.SaveAs(filepath);


            workbook.Close(true, Type.Missing, Type.Missing);

            workbook = null;

            xlApp.Quit();

            xlApp = null;
        }