I've been trying to center an image in an Excel export file using Classic ASP. I tried everything and resorted to CSS. CSS didn't work (examples below):
img {
position: absolute;
top: 0; bottom:0; left: 0; right:0;
margin: auto;
}
and
img.center {
display: block;
margin: 0 auto;
}
I tried putting the image in a div and centering the div. That also didn't work. I have the image in an img tag like this:
<img border=0 id= "img" name= "img" src="pic.jpg" height ="100" width= "1000">
When I export the file to Excel and open it, the picture is always to the very left.
While simple Excel files can be created through ASP Classic simply by setting the headers for the page, you will often need more control.
Please take a look at this: http://www.crydust.be/blog/2009/03/02/generate-excel-files-in-asp-classic/
Code repostedform Kristof Neirynck's blog here:
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%>
<%
function getData(connectionString, sql){
var result = null;
var adStateOpen = 1;
var connection = new ActiveXObject("ADODB.CONNECTION");
try{
connection.Open(connectionString);
} catch(e1){
return null;
}
if (connection.State !== adStateOpen) {
return null;
}
try{
var recordset = connection.Execute(sql);
} catch(e2){
return null;
}
if (!recordset.EOF) {
result = recordset.GetRows().toArray();
recordset.Close();
}
recordset = null;
connection.Close();
connection = null;
return result;
}
function writeCsvHttpHeaders(filename){
Response.ContentType = "text/csv";
Response.Charset = "utf-8";
Response.AddHeader("Content-Disposition",
"attachment; filename="+filename+".csv");
}
function writeXlsHttpHeaders(filename){
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "utf-8";
Response.AddHeader("Content-Disposition",
"attachment; filename="+filename+".xls");
}
function getXlsStart(){
return ""
+ "<html>\n"
+ "<head>\n"
+ "<meta http-equiv=\"Content-Type\" "
+ "content=\"text/html; charset=UTF-8\">\n"
+ "<style type=\"text/css\">\n"
+ "html, body, table {\n"
+ " margin: 0;\n"
+ " padding: 0;\n"
+ " font-size: 11pt;\n"
+ "}\n"
+ "table, th, td { \n"
+ " border: 0.1pt solid #D0D7E5;\n"
+ " border-collapse: collapse;\n"
+ " border-spacing: 0;\n"
+ "}\n"
+ "</style>\n"
+ "</head>\n"
+ "<body>\n"
+ "<table>\n"
+ "";
}
function getXlsEnd(){
return ""
+ "</table>\n"
+ "</body>\n"
+ "</html>"
+ "";
}
function csvEscape(val){
if (typeof val === "number") {
return val.toString(10).replace(".", ",");
} else if (typeof val === "string") {
if (val.indexOf("\"") !== -1) {
return "\""+val.replace(/"/g, "\"\"")+"\"";
} else if (val.indexOf(";") !== -1) {
return "\""+val+"\"";
} else {
return val;
}
} else if (val === null) {
return "#NULL#";
} else if (val === undefined) {
return "#UNDEFINED#";
} else {
return "#ERROR#";
}
}
function writeCsv(filename, data, columnCount){
writeCsvHttpHeaders(filename);
// utf-8 BOM (very important for special characters)
Response.Write("\uFEFF");
for (var i=0, il=data.length; i<il; i+=columnCount) {
for (var j=0; j<columnCount; j++) {
Response.Write(csvEscape(data[i+j]));
if (j !== columnCount-1) {
Response.Write(";");
}
}
Response.Write("\n");
// prevent Response Buffering Limit Exceeded
if (i % 1000 === 0) {
Response.Flush();
}
}
}
function xlsEscape(val){
if (typeof val === "number") {
return val.toString(10).replace(".", ",");
} else if (typeof val === "string") {
return Server.HTMLEncode(val);
} else if (val === null) {
return "#NULL#";
} else if (val === undefined) {
return "#UNDEFINED#";
} else {
return "#ERROR#";
}
}
function writeXls(filename, data, columnCount){
writeXlsHttpHeaders(filename);
Response.Write(getXlsStart());
for (var i=0, il=data.length; i<il; i+=columnCount) {
Response.Write("<tr>");
for (var j=0; j<columnCount; j++) {
Response.Write("<td>");
Response.Write(xlsEscape(data[i+j]));
Response.Write("</td>");
}
Response.Write("</tr>\n");
// prevent Response Buffering Limit Exceeded
if (i % 1000 === 0) {
Response.Flush();
}
}
Response.Write(getXlsEnd());
}
function main(){
var filetype = Request.QueryString("filetype")();
Var connectionString = "Provider=SQLOLEDB.1;"
+ "Data Source=LAPTOP\\SQLEXPRESS;"
+ "User ID=internal;"
+ "Password=internal;"
+ "Initial Catalog=trees_in_sql";
Var sql = ""
+ "SELECT id \n"
+ ", name \n"
+ "FROM People \n"
+ ";";
var filename = "filename";
var columnCount = 2;
var data = getData(connectionString, sql);
if (data !== null) {
Response.Clear();
if (filetype === "csv") {
writeCsv(filename, data, columnCount);
} else {
writeXls(filename, data, columnCount);
}
} else {
Response.Write("Error, no data found");
}
Response.End();
}
main();
%>
.
IMPORTANT
Regarding images specifically, you should look at this: http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_27115638.html
From the above link:
<!--#include file="functions.asp"-->
<%
response.ContentType = "application/vnd.ms-excel"
thedate = CStr(Now())
groupid = request.querystring("groupid")
GroupName = getGroupName(groupid)
datetimestring = Replace(FormatDateTime(Now(), vbShortDate), "/", "-") & "_" & Replace(FormatDateTime(Now(), vbShortTime), ":", "")
response.AddHeader "Content-Disposition", "attachment;filename=GroupAttendanceExport_" & GroupName & "_" & datetimestring & ".xls"
response.write "<html><body><table>"
response.write "<tr><td><img border=0 src='https://www.example.org/xxx_Logo_small.jpg'></td></tr>"
response.write "<tr><td></td></tr>"
response.write "<tr><td></td></tr>"
response.write "<tr><td></td></tr>"
response.write "<tr><td></td></tr>"
response.write "<tr><td></td></tr>"
response.write "<tr><td > Attendance</td></tr>"
response.write "</table>"
MORE CODE HERE
………
………
………
………
response.write "</body></html>"
.
UPDATE
It sounds like you need to use OpenXML instead of HTML.
Here is an example of how to insert an image into a specific cell using OpenXML: https://social.msdn.microsoft.com/Forums/office/en-US/157e2fab-ed30-43a5-9824-e144d673a5b7/insert-images-into-specific-excel-cells-using-openxml?forum=oxmlsdk