


How to import data and insert pictures into an EXECL file at the same time?
Jun 23, 2017 pm 04:21 PM因为项目需要在导出数据到EXECL文档的同时还需要导出图片进去,在处理是遇到的一些问题,在此记录一下。
首先代码写好之后放测试服务器上去执行的时候报错了,报检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败,原因是出现以下错误: 80070005。
这个解决方法我基本上是按照网上来进行解决的,但是需要注意的就是,设置完权限之后,还需要在服务器安装的office文件夹分配权限。
权限问题设置完之后就是调试代码了,网上搜索到的资料都是先插入数据,然后在数据的最后插入图片,而不是我想要的每一行都有图片,所以不符合我的需求,于是就自己看着网上的代码整吧整吧。
/// <summary>/// /// </summary>/// <param name="dt">数据源</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;//设置单元格的宽 range.RowHeight = 120;//设置单元格的高worksheet.Shapes.AddPicture(dt.Rows[r][i].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, PicLeft, PicTop, 100, 100);//这后面的100 是设置图片的宽高 }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; }
The above is the detailed content of How to import data and insert pictures into an EXECL file at the same time?. For more information, please follow other related articles on the PHP Chinese website!

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Use ddrescue to recover data on Linux

Open source! Beyond ZoeDepth! DepthFM: Fast and accurate monocular depth estimation!

What to do if the 0x80004005 error code appears. The editor will teach you how to solve the 0x80004005 error code.

What is hiberfil.sys file? Can hiberfil.sys be deleted?

Google is ecstatic: JAX performance surpasses Pytorch and TensorFlow! It may become the fastest choice for GPU inference training

The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks

Slow Cellular Data Internet Speeds on iPhone: Fixes

The U.S. Air Force showcases its first AI fighter jet with high profile! The minister personally conducted the test drive without interfering during the whole process, and 100,000 lines of code were tested for 21 times.
