Cold Fusion Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsProgrammingCold Fusion Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old April 20th, 2005, 02:08 PM
SandNSea SandNSea is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2005
Posts: 2 SandNSea User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 43 m 40 sec
Reputation Power: 0
Export table to Excel - Multiple tabs (worksheets)

Hello,
I am looking for a way to export a recordset to Excel. However, I need to put some information in worksheet1 and then some more of the data into worksheet2. To make things more difficult we are using Excel 2000. From what I understand Excel 2003 can support what I am looking for by using XML, but Excel 2000 does not support the xml option. I am also open to suggestions that use asp or php.

I have read some other posts that say the code for exporting to excel is:
Code:
<cffile action="WRITE" file="orderForm.xls" output="<cfcontent type='application/ms-excel'> <Table>etc.</Table"

How do I specify where to save the file (and name it) and email the file automatically. This would be going to a set location, not a user defined location.

Thanks.

Reply With Quote
  #2  
Old April 20th, 2005, 04:02 PM
errodr errodr is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2002
Posts: 7 errodr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 46 m 22 sec
Reputation Power: 0
Use the <CFOBJECT> tag to make a com connection to Excel and then go from there. You will be able to use the objExcel to create a multi-worksheet workbook.

<CFOBJECT ACTION="CONNECT" CLASS="Excel.Application" NAME="objExcel" TYPE="COM">

Reply With Quote
  #3  
Old April 20th, 2005, 04:51 PM
SandNSea SandNSea is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2005
Posts: 2 SandNSea User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 43 m 40 sec
Reputation Power: 0
Thanks for the advice. I haven't used the cfobject much. Do you have an example or know of an article that could show me how to add the worksheets using the cfobject? Does the 'Connect' run excel, so I would use vb like I was writting a macro for excel?
Thanks again.

Reply With Quote
  #4  
Old July 8th, 2008, 02:26 AM
dhanyatm dhanyatm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2008
Location: INDIA
Posts: 2 dhanyatm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 9 sec
Reputation Power: 0
Send a message via Yahoo to dhanyatm Send a message via Google Talk to dhanyatm
Thumbs up Here is a solution

The solution is to export the data as an XML Spreadsheet. That is, you first save a basic template with multiple sheets (as per your requirement with some sample data) as an xml file. Copy the whole XML code into your coldfusion code inside <cfxml> tag. Write this xml using <cffile> onto an excel file. In the code you can replace the value with your query output or any other data as you wish. Simple solution right?


Step:1
---------
I am writing a query to retrieve data which is to be imported on an excel file

<!--- For sheet no: 1 --->
<CFQUERY name="ReportData" datasource="#SystemDS#">
SELECT *
FROM <Table Name>
</CFQUERY>
<!--- For sheet no:2 --->
<CFQUERY name="ReportData1" datasource="#SystemDS#">
SELECT *
FROM <Table Name>
</CFQUERY>

Step :2
----------
Now write the below code:
<cfxml variable="xmlDataDump"> (define your own variable name)
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="m16272500">
<Alignment ss:Horizontal="Left" ss:Vertical="Top"/>
<Borders>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Size="16" ss:Bold="1"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
</Style>
</Styles>
<Worksheet ss:Name="ReportData">
<Table>
<Column ss:Width="66"/>
<Column ss:Width="70.5"/>
<Column ss:Width="192"/>
<Column ss:Index="5" ss:Width="71.25"/>
<Column ss:Width="192"/>
<Row ss:AutoFitHeight="0" ss:Height="15.75">
<Cell ss:MergeAcross="6" ss:StyleID="m16276522"><Data ss:Type="String">Title</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0">
<Cell ss:MergeAcross="5" ss:StyleID="m16276552"></Cell>
</Row>
<!--- Define your own code here. Loop thru your query and display data in rows --->
<CFOUTPUT query="Reportdata">
<Row ss:Height="54.75">
<Cell ss:StyleID="s55"><Data ss:Type="String">#column1#</Data></Cell>
<Cell ss:StyleID="s55"><Data ss:Type="String"><CFIF Abbreviation NEQ ''>#XMLFormat(column2)#<CFELSE>#XMLFormat(column3)#</CFIF></Data></Cell>
<Cell ss:StyleID="s55"><Data ss:Type="String">#XMLFormat(Background)#</Data></Cell>
</Row>
</CFOUTPUT>
</Worksheet>
<Worksheet ss:Name="ReportData2">
<!--- Repeat code using second query --->
</Worksheet>
</cfxml>

<cfset xml = ToString(xmlDataDump)>
<cffile action="write" nameconflict="overwrite" file="#getDirectoryFromPath(ExpandPath("../../"))#WriteableFolder\DeploymentReports\Service Introduction Project Deployment Weekly Report.xls" output="#xml#">

Though its a very late reply after 3 years , someone in future may find it helpful !

Cheers,
Dhanya

Reply With Quote
  #5  
Old July 8th, 2008, 08:28 AM
errodr errodr is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2002
Posts: 7 errodr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 46 m 22 sec
Reputation Power: 0
The XML solution posted above does work, but will become very confusing with a complicated layout and/or formatting. In my opinion the best way to do this is to use the Apache POI library to load a pre-formatted Excel template and the write directly to the file using JAVA:

Code:
	<cfscript>
		// Load the Excel template file
		fileIn = createObject("java","java.io.FileInputStream").init("#currentDirectory#reportTemplate.xls");

		// Create a HSSF filesystem object based on the template file
		fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(fileIn);

		// Create a HSSFWorkbook object
		wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs);

		// Create a ByteArrayOutputStream object, used to write the contents on the wb object for writing to the DB
		out = createObject("java","java.io.ByteArrayOutputStream").init();
	</cfscript>

Reply With Quote
  #6  
Old July 8th, 2008, 08:43 AM
dhanyatm dhanyatm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2008
Location: INDIA
Posts: 2 dhanyatm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 9 sec
Reputation Power: 0
Send a message via Yahoo to dhanyatm Send a message via Google Talk to dhanyatm
POI did not work for me !!

Thanks ! But POI did not work for me coz of some reasons. The advantage of my code is "no need to write any code" just copy paste code from the XML spreadsheet (when we save as the excel as XML) and replace the value with query output.
Well, yes, I am not sure how others will be comfortable with it !

Cheers,
Dhanya

Reply With Quote
  #7  
Old October 10th, 2008, 10:58 AM
rameshwar rameshwar is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2008
Posts: 2 rameshwar User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 17 m 32 sec
Reputation Power: 0
<WorkBook> support for Excel 2000

As I am also trying to export the data into excel using
the following stmt

public System.IO.StringWriter ExportToExcelXML(DataSet source)
{

System.IO.StringWriter excelDoc;



excelDoc = new System.IO.StringWriter();

StringBuilder ExcelXML = new StringBuilder();

ExcelXML.Append("<xml version>\r\n<Workbook ");

ExcelXML.Append("xmlns=\"urn:schemas-microsoft-comffice:spreadsheet\"\r\n");

ExcelXML.Append(" xmlns=\"urn:schemas-microsoft-comfficeffice\"\r\n ");

ExcelXML.Append("xmlns:x=\"urn:schemas- microsoft-comffice:");

ExcelXML.Append("excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:");

ExcelXML.Append("office:spreadsheet\">\r\n <Styles>\r\n ");

ExcelXML.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n ");

ExcelXML.Append("<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");

ExcelXML.Append("\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>");

ExcelXML.Append("\r\n <Protection/>\r\n </Style>\r\n ");

ExcelXML.Append("<Style ss:ID=\"BoldColumn\">\r\n <Font ");

ExcelXML.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n ");

ExcelXML.Append("<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat");

ExcelXML.Append(" ss:Format=\"@\"/>\r\n </Style>\r\n <Style ");

ExcelXML.Append("ss:ID=\"Decimal\">\r\n <NumberFormat ");

ExcelXML.Append("ss:Format=\"0.0000\"/>\r\n </Style>\r\n ");

ExcelXML.Append("<Style ss:ID=\"Integer\">\r\n <NumberFormat ");

ExcelXML.Append("ss:Format=\"0\"/>\r\n </Style>\r\n <Style ");

ExcelXML.Append("ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
ExcelXML.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n ");

ExcelXML.Append("<Style ss:ID=\"s28\">\r\n");

ExcelXML.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\" ss:ReadingOrder=\"LeftToRight\" ss:WrapText=\"1\"/>\r\n");

ExcelXML.Append("<Font x:CharSet=\"1\" ss:Size=\"9\" ss:Color=\"#808080\" ss:Underline=\"Single\"/>\r\n");

ExcelXML.Append("<Interior ss:Color=\"#D3D3D3\" ss:Pattern=\"Solid\"/></Style>\r\n");

ExcelXML.Append("</Styles>\r\n ");

string startExcelXML = ExcelXML.ToString();

const string endExcelXML = "</Workbook>";

int rowCount = 0;

int sheetCount = 1;

excelDoc.Write(startExcelXML);

excelDoc.Write("<Worksheet ss:Name=\"Report_Sheet" + sheetCount + "\">");

excelDoc.Write("<Table>");

// excelDoc.Write("<Row ss:AutoFitHeight=\"0\" ss:Height=\"6.75\"/>\r\n");

excelDoc.Write("<Row ><Cell ss:MergeAcross=\"10\"><Data ss:Type=\"String\">");

excelDoc.Write("HEADER TEXT");

excelDoc.Write("</Data></Cell>");

excelDoc.Write("<Cell ss:MergeAcross=\"1\" ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");

excelDoc.Write("Report Date");

excelDoc.Write("</Data></Cell>");

excelDoc.Write("<Cell ss:MergeAcross=\"1\" ss:StyleID=\"DateLiteral\"><Data ss:Type=\"String\">");

excelDoc.Write(DateTime.Now.ToShortDateString());

excelDoc.Write("</Data></Cell></Row>");

// excelDoc.Write("<Row ss:AutoFitHeight=\"0\" ss:Height=\"10\"/>\r\n");

///Complete

excelDoc.Write("<Row ss:StyleID=\"s28\">");

for (int x = 0; x < source.Tables[0].Columns.Count; x++)
{

excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");

excelDoc.Write(source.Tables[0].Columns[x].ColumnName);

excelDoc.Write("</Data></Cell>");

}

excelDoc.Write("</Row>");

foreach (DataRow x in source.Tables[0].Rows)
{

rowCount++;

//if the number of rows is > 63000 create a new page to continue output

if (rowCount == 63000)
{

rowCount = 0;

sheetCount++;

excelDoc.Write("</Table>");

excelDoc.Write(" </Worksheet>");

excelDoc.Write("<Worksheet ss:Name=\"Report_Sheet" + sheetCount + "\">");

excelDoc.Write("<Table>");



excelDoc.Write("<Row>");

for (int xi = 0; xi < source.Tables[0].Columns.Count; xi++)
{


excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");




excelDoc.Write(source.Tables[0].Columns[xi].ColumnName);

excelDoc.Write("</Data></Cell>");

}

excelDoc.Write("</Row>");

}

excelDoc.Write("<Row>");

for (int y = 0; y < source.Tables[0].Columns.Count; y++)
{

string XMLstring = x[y].ToString();

XMLstring = XMLstring.Trim();

XMLstring = XMLstring.Replace("&", "&");

XMLstring = XMLstring.Replace(">", ">");

XMLstring = XMLstring.Replace("<", "<");


excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");


excelDoc.Write(XMLstring);

excelDoc.Write("</Data></Cell>");

}

excelDoc.Write("</Row>");

}

excelDoc.Write("</Table>");

excelDoc.Write(" </Worksheet>");


excelDoc.Write(endExcelXML);

return excelDoc;

}

protected void lnkExcel_Click(object sender, System.EventArgs e)
{

DataSet ds = new DataSet();
ds.Tables.Add("Customers");
ds.Tables[0].Columns.Add("CustomerID");
ds.Tables[0].Columns.Add("CompanyName");
ds.Tables[0].Columns.Add("CompanyLOGO");
ds.Tables[0].Columns.Add("CompanyAddress");
ds.Tables[0].Columns.Add("CompanyPhone");
ds.Tables[0].Columns.Add("CompanyEmailID");
ds.Tables[0].Columns.Add("CompanyFAX");
ds.Tables[0].Columns.Add("CompanyDETAIL");
ds.Tables[0].Columns.Add("CompanyIP");
ds.Tables[0].Columns.Add("CompanyWEB");
for (double i = 0; i <= 67000; i++)
{
DataRow newCustomersRow = ds.Tables["Customers"].NewRow();

newCustomersRow["CustomerID"] = "ALFKI"+i.ToString();
newCustomersRow["CompanyName"] = "Alfreds Futterkiste" + i.ToString();

newCustomersRow["CompanyLOGO"] = "HDFCSL";
newCustomersRow["CompanyAddress"] = "HDFCSLAdd";
newCustomersRow["CompanyPhone"] = "HDFCSLPhone";
newCustomersRow["CompanyEmailID"] = "HDFCSLEmailID";
newCustomersRow["CompanyFAX"] = "HDFCSLFAX";
newCustomersRow["CompanyDETAIL"]= "HDFCSLDETAIL";
newCustomersRow["CompanyIP"]= "HDFCSLIP";
newCustomersRow["CompanyWEB"] = "12/12/2008";
ds.Tables["Customers"].Rows.Add(newCustomersRow);
}



Response.Buffer = true;

Response.ContentType = "application/vnd.ms-excel";

Response.AddHeader("Content-Disposition", "attachment; filename=Report.xls");


this.EnableViewState = false;

Response.Charset = string.Empty;

System.IO.StringWriter myTextWriter = new System.IO.StringWriter();
myTextWriter = ExportToExcelXML(ds);


Response.Write(myTextWriter.ToString());
//File.WriteAllText(strPath, excelFile);
Response.End();
}

This is worked/support on Excel 2003 version
but it is not worked on Excel 2000 version
If you know then How it is possible to support on any version ?
or suggest any alternative way except COM interop

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsProgrammingCold Fusion Development > Export table to Excel - Multiple tabs (worksheets)


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap