从数据库中导出图片插入到Excel.docx
- 文档编号:6557339
- 上传时间:2023-01-07
- 格式:DOCX
- 页数:11
- 大小:21.23KB
从数据库中导出图片插入到Excel.docx
《从数据库中导出图片插入到Excel.docx》由会员分享,可在线阅读,更多相关《从数据库中导出图片插入到Excel.docx(11页珍藏版)》请在冰豆网上搜索。
从数据库中导出图片插入到Excel
1.请问如何把sqlserver数据库中的图片导入到excel的指定表格中打印?
数据表有“photo”的字段保存了图片,先想把图片导出到excel中打印,需指定excel的表格,入C3
中打印!
以下是我用的方法,贴出来,其他人如用到的可参考一下
思路:
先从数据库中把图片保存到根目录中(文件名:
temp.jpg),然后从excel中导入该图片,导入后删除temp.jpg
If Not IsNull(mRst("photo")) Then
'读取显示数据库中的图像
Set StmPic = New ADODB.Stream
With StmPic
.Type = adTypeBinary
.Open
.Write mRst.Fields("photo") '写入数据库中的数据至Stream中
' .SaveToFile StrPicTemp, adSaveCreateOverWrite
.SaveToFile App.Path & "\temp.jpg"
.Close
End With
' Image1.Picture = LoadPicture(App.Path & "\temp.jpg") '用image控件显示图像
Range("J8:
K11").Select
ActiveSheet.Pictures.Insert(App.Path & "\temp.jpg").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 99#
Selection.ShapeRange.Width = 90.75
Selection.ShapeRange.Rotation = 0#
Selection.ShapeRange.IncrementLeft 2.35
Selection.ShapeRange.IncrementTop 0.75
Kill (App.Path & "\temp.jpg")
End If
2.用C#编程从数据库中读取图片数据导进Excel文件的方法
收藏到手机 转发 评论
2008-03-1011:
40
usingSystem;
usingSystem.IO;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Drawing.Imaging;
usingSystem.Windows.Forms;
usingExcel;
privatevoidEduceExcel()
{
stringpicPath=Directory.GetCurrentDirectory()+'\\Excelpic.jpg';
stringExcelName=Directory.GetCurrentDirectory()+'\\ExcelModul.xls';
stringfileName='Excel'+DateTime.Now.ToString().Replace(':
','')+'.xls';
Excel.Applicationapp=newExcel.Application();//建立Excel对象
app.Visible=true;//让Excel文件可见
Workbookobjbook;
objbook=app.Workbooks.Add(ExcelName);
Worksheetworksheet;
worksheet=(Worksheet)objbook.Worksheets[1];
Excel.Picturespics=(Excel.Pictures)worksheet.Pictures(Type.Missing);//建立图片集合对象
intTD=1;
intp=1;
PicItemColletionpicItems=PicItem.Getpictems();//读取数据库图片数据集合
foreach(PicItemPIinpicItems)
{
if(PI.ImageData!
=null)
{
MemoryStreamstream=newMemoryStream(PI.ImageData,0,PI.ImageData.Length);
Bitmapbitmap=newBitmap(stream);
bitmap.Save(picPath,System.Drawing.Imaging.ImageFormat.Jpeg);//保存临时图片文件到硬盘里
if(File.Exists(picPath))
{
stream.Close();//关闭图像文件流
Rangerange_1=range=worksheet.get_Range('A'+TD.ToString(),'A'+TD.ToString());
//获取填充单元格范围
pics.Insert(picPath,Type.Missing);//获取图片
Excel.Picturepic=(Excel.Picture)pics.Item(p);//建立图片集合某一图片对象
pic.Left=(double)range_1.Left;
pic.Top=(double)range_1.Top;
pic.Height=(double)range_1.Height;
pic.Width=(double)range_1.Width;
TD=TD+1;
p=p+1;
}
}
if(File.Exists(picPath))
{
File.Delete(picPath);//删除图片
}
}
3.请教各位:
我想用c#,通过闹钟实现定时把sqlserver的数据导入到一个excel文件,同时生成一个EXCEL文件,
不是手动保存,那位大侠知道的指导下,谢谢。
要是有什么原代码就太感谢了。
这是我写的一个方法,也找了不少资料,参数是,dataset,要生成的excel的路径,根据strColumnName 这一列的值生成不同的excel文件名称为1.xls,2.xsl.......
希望对你有帮助.要先添加对excel的引用,在引用中的com中选
Public Sub CreateExcel(ByVal _strExcelPath As String, ByVal _ExcelDataSet As DataSet, ByVal _strColumnName As String)
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim xlDBTable As Excel.DataTable
Dim strPath As String
Try
'define an arry list
Dim arry As ArrayList
arry = New ArrayList
Dim dr As DataRow
Dim i As Integer = 0
Dim strAll As String
'first,get the deffent column's value
'then send the values into a arrylist
'define a dataview in order to sort
Dim dv As DataView
dv = New DataView(_ExcelDataSet.Tables(0))
'sort by the column
dv.Sort = _strColumnName
Dim tmp As String
Dim drv As DataRowView
For Each drv In dv
If tmp <> drv.Row(_strColumnName) Then
tmp = Convert.ToString(drv.Row(_strColumnName))
arry.Add(tmp)
End If
Next
Dim k As Integer = 0
For k = 0 To arry.Count - 1
Dim xlApp As New Excel.Application
xlBook = xlApp.Workbooks.Add
xlSheet = xlBook.Worksheets("sheet1")
'the file's full name
strPath = _strExcelPath & "\" & k + 1 & ".xls"
Dim col As DataColumn
Dim colIndex As Integer = 0
'For Each col In _ExcelDataSet.Tables(0).Columns
' colIndex = colIndex + 1
' 'set the cell's column name
' xlApp.Cells(1, colIndex) = col.ColumnName
'Next
Dim row As DataRow
Dim rowIndex As Integer = 0
'base on the arrylist's value,circle
For Each row In _ExcelDataSet.Tables(0).Select(_strColumnName & "='" & Convert.ToString(arry(k)) & "'")
'add the cell's value
rowIndex = rowIndex + 1
colIndex = 0
For Each col In _ExcelDataSet.Tables(0).Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex, colIndex) = row(col.ColumnName)
Next
Next
xlBook.SaveAs(strPath, xlApp.ActiveWorkbook.FileFormat, "", "", xlApp.ActiveWorkbook.ReadOnlyRecommended, xlApp.ActiveWorkbook.CreateBackup, Excel.XlSaveAsAccessMode.xlShared.xlShared, xlApp.ActiveWorkbook.ConflictResolution, False, "", "")
'kill the process
xlBook.Close()
xlApp.Quit()
xlApp = Nothing
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
将数据库表导出到Excel,并生成文件(C#实现)
分类:
Net方面2010-04-0815:
31 1001人阅读 评论(0) 收藏 举报
excel数据库c#null工作html
1 将数据库表导出到Excel,并生成文件(C#实现)[转载]
2
3
4 需添加项目引用:
5
6 1. .NET->System.Data.OracleClient.dll
7
8 2. COM->Microsoft Excel 11.0 Object Library
9
10
11
12 代码如下:
13
14 using System;
15 using System.IO;
16 using System.Data;
17 using System.Reflection;
18 using System.Diagnostics;
19 using System.Configuration;
20 using System.Collections;
21 using Excel;
22
23 namespace ProtoType
24 {
25 ///
26 /// 套用模板输出Excel,生成xls文件和html文件
27 /// Author:
Liu Wen
28 /// Date Created:
2006-8
29 ///
30 public class ExportExcel
31 {
32 #region variable member
33 protected string templateFile = null;
34 protected string excelFile = null;
35 protected string htmlFile = null;
36 protected object missing = Missing.Value;
37 Excel.ApplicationClass app;
38 Excel.Workbook book;
39 Excel.Worksheet sheet;
40 Excel.Range range;
41 private DateTime beforeTime; //Excel启动之前时间
42 private DateTime afterTime; //Excel启动之后时间
43 #endregion
44
45 ///
46 /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径
47 ///
48 /// Excel模板文件路径
49 /// Excel输出文件路径
50 /// Html输出文件路径
51 public ExportExcel(string templateFile, string excelFile, string htmlFile)
52 {
53 if(templateFile == null)
54 throw new Exception("Excel模板文件路径不能为空!
");
55
56 if(excelFile == null)
57 throw new Exception("Excel输出文件路径不能为空!
");
58
59 if(htmlFile == null)
60 throw new Exception("Html输出文件路径不能为空!
");
61
62 if(!
File.Exists(templateFile))
63 throw new Exception("指定路径的Excel模板文件不存在!
");
64
65 this.templateFile = templateFile;
66 this.excelFile = excelFile;
67 this.htmlFile = htmlFile;
68
69 //创建一个Application对象
70 beforeTime = DateTime.Now;
71 app = new ApplicationClass();
72 //app.Visible = true;
73 afterTime = DateTime.Now;
74
75 //打开模板文件,得到WorkBook对象
76 try
77 {
78 book = app.Workbooks.Open(templateFile,missing,missing,missing,missing,missing,
79 missing,missing,missing,missing,missing,missing,missing,missing,missing);
80 }
81 catch (Exception e)
82 {
83 throw e;
84 }
85 //得到WorkSheet对象
86 sheet = (Excel.Worksheet)book.Sheets.get_Item
(1);
87 }
88
89 ///
90 /// 将DataTable数据导出到Excel(可动态插入行)
91 ///
92 /// DataTable
93 /// 插入行的索引
94 /// 插入列的索引
95 public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex)
96 {
97 //range = sheet.get_Range("A7", missing);
98 //range.Value2 = "raogerrr";
99 //string str = range.Text.ToString();
100
101 int rowCount = dt.Rows.Count; //DataTable行数
102 int colCount = dt.Columns.Count; //DataTable列数
103 int iRow;
104 int iCol;
105
106 //将数据导出到相应的单元格
107 for (iRow = 0; iRow < rowCount; iRow++)
108 {
109 //插入新行
110 this.InsertRows(sheet, iRow+rowIndex);
111 //填充当前行
112 for (iCol = 0; iCol < colCount; iCol++)
113 {
114 sheet.Cells[iRow+rowIndex, iCol+colIndex] = dt.Rows[iRow][iCol].ToString();
115 }
116 }
117 this.DeleteRows(sheet, rowCount+rowIndex);
118
119 //Excel.QueryTables qts = sheet.QueryTables;
120 //Excel.QueryTable qt = qts.Add(,,);
121 //qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
122 //qt.Refresh();
123 }
124
125 ///
126 /// 将DataTable数据导出到Excel(可动态插入行)
127 ///
128 /// DataTable
129 /// 插入数据的起始单元格
130 public void DataTableToExcel(System.Data.DataTable dt, string cellID)
131 {
132 in
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 导出 图片 插入 Excel
![提示](https://static.bdocx.com/images/bang_tan.gif)