I have a table with all employee pictures in SQL Server table. Column is of type Image
I have a requirement to download emplyee profile in word format with their picture in it. I was able to export all their data in HTML format and then I used the following C# code to convert that html to word and give it as download.
public static void HtmlToWordDownload(string HTML, string FileName)
{
lock (LockMulti)
{
string strBody = string.Empty;
strBody = @"<html xmlns:o='urn:schemas-microsoft-com:office:office' " +
"xmlns:w='urn:schemas-microsoft-com:office:word'" +
"xmlns='http://www.w3.org/TR/REC-html40'>" +
"<head><title>Document Title</title>" +
"<!--[if gte mso 9]><xml><w:WordDocument><w:View>Print</w:View><w:Zoom>100</w:Zoom>" +
"<w:DoNotOptimizeForBrowser/></w:WordDocument></xml><![endif]-->" +
"<style> @page Section1 {size:8.27in 11.69in; mso-first-footer:ff1; mso-footer: f1; mso-header: h1; " +
//"border:solid navy 2.25pt; padding:24.0pt 24.0pt 24.0pt 24.0pt; " +
"margin:0.6in 0.6in 0.6in 0.6in ; mso-header-margin:.1in; " +
"mso-footer-margin:.1in; mso-paper-source:0;} " +
"div.Section1 {page:Section1;} p.MsoFooter, li.MsoFooter, " +
"div.MsoFooter{margin:0in; margin-bottom:.0001pt; " +
"mso-pagination:widow-orphan; tab-stops:center 3.0in right 6.0in; " +
"font-size:12.0pt; font-family:'Arial';} " +
"p.MsoHeader, li.MsoHeader, div.MsoHeader {margin:0in; " +
"margin-bottom:.0001pt; mso-pagination:widow-orphan; tab-stops:center " +
"3.0in right 6.0in; font-size:12.0pt; font-family:'Arial';}--></style></head> ";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.ms-word";
HttpContext.Current.Response.AddHeader("Content-Disposition", "inline;filename=" + FileName + ".doc");
StringBuilder htmlCode = new StringBuilder();
htmlCode.Append(strBody);
htmlCode.Append("<body><div class=Section1>");
htmlCode.Append(HTML);
htmlCode.Append("</div></body></html>");
HttpContext.Current.Response.Write(htmlCode.ToString());
HttpContext.Current.ApplicationInstance.CompleteRequest();
HttpContext.Current.Response.Flush();
}
}
It is working absolutely fine. Now how can I embed employe's picture in it. What format should I convert that Image to embed in the HTML. I tried the following query, but it is giving same output for all the pictures. Please help.
select
top 30 convert(varchar, convert(binary, i.Photo))
from hrm.hrm_rp_Employee_Image i
Output is as follows
(No column name)
ÿØÿà
ÿØÿà
ÿØÿà
ÿØÿà
ÿØÿà
ÿØÿà
Is my approach correct or something else needs to be done?
Update
I found out the following query to convert it to base64.
declare @pic varchar(max)
SELECT @pic = '<img src="data:image/jpg;base64,' + CAST(N'' AS XML).value(
'xs:base64Binary(xs:hexBinary(sql:column("Pic")))'
, 'VARCHAR(MAX)'
)
FROM (
SELECT CAST(Photo AS VARBINARY(MAX)) AS Pic from hrm.hrm_rp_Employee_Image
) AS h;
set @pic = @pic + '" style="height: 100px; width: 100px;" />'
print @pic
It successfully converts and gives me a string and it works perfectly when I test it on any online html editor.
But it is not showing in my word file after download, an image with red color 'X' symbol appears, can somebody tell me what could be the problem.
