RE: [PHP] Excel Report Formatting
-Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 8:30 AM To: php-general@lists.php.net Subject: [PHP] Excel Report Formatting Hi, Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. Thank you __ Get the name you've always wanted @ymail.com or @rocketmail.com! Go to http://ca.promos.yahoo.com/jacko/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php http://www.phpeveryday.com/articles/Write-report-in-Excel-Format-Part-1--P155.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Report Formatting
[snip] Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. [/snip] STFW or STFA much? This was posted yesterday Stupid browser tricks http://www.evolt.org/node/26896 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Report Formatting
-Original Message- From: Jay Blanchard [mailto:jblanch...@pocket.com] Sent: Tuesday, April 20, 2010 8:40 AM To: Php Developer; php-general@lists.php.net Subject: RE: [PHP] Excel Report Formatting [snip] Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. [/snip] STFW or STFA much? This was posted yesterday Stupid browser tricks http://www.evolt.org/node/26896 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I believe the OP wants to format the excel for download. And not display the data in excel like in the web browser. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Report Formatting
[snip]I believe the OP wants to format the excel for download. And not display the data in excel like in the web browser.[/snip] He didn't specify that, but you can change the code in the article to save the file and make available for download. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Report Formatting
Its good to see we're all playing nice today! -- -Dan Joseph www.canishosting.com - Unlimited Hosting Plans start @ $3.95/month. Promo Code NEWTHINGS for 10% off initial order http://www.facebook.com/canishosting http://www.facebook.com/originalpoetry
Re: [PHP] Excel Report Formatting
Hi Tommy, The code in the link is traightforward. The problem is you need to install ADOdb. I'm wondering if there is a way to do it from scratch. Thanks - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php-general@lists.php.net Sent: Tue, April 20, 2010 11:37:21 AM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 8:30 AM To: php-general@lists.php.net Subject: [PHP] Excel Report Formatting Hi, Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. Thank you __ Get the name you've always wanted @ymail.com or @rocketmail.com! Go to http://ca.promos.yahoo.com/jacko/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php http://www.phpeveryday.com/articles/Write-report-in-Excel-Format-Part-1--P155.html __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Report Formatting
On Tue, 2010-04-20 at 12:50 -0400, Dan Joseph wrote: Its good to see we're all playing nice today! It's still not an excel file but an HTML table sent with an Excel mime-type. This is just asking for trouble if the user does not use Excel, as it's exploiting behaviour that looks very particular to Excel and not ALL spreadsheet software. Thanks, Ash http://www.ashleysheridan.co.uk
RE: [PHP] Excel Report Formatting
-Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 9:52 AM To: Tommy Pham; php-general@lists.php.net Subject: Re: [PHP] Excel Report Formatting Hi Tommy, The code in the link is traightforward. The problem is you need to install ADOdb. I'm wondering if there is a way to do it from scratch. Thanks - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php- gene...@lists.php.net Sent: Tue, April 20, 2010 11:37:21 AM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 8:30 AM To: php-general@lists.php.net Subject: [PHP] Excel Report Formatting Hi, Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. Thank you __ Get the name you've always wanted @ymail.com or @rocketmail.com! Go to http://ca.promos.yahoo.com/jacko/ http://www.phpeveryday.com/articles/Write-report-in-Excel-Format-Part- 1--P155.html I think the article/sample uses PEAR's Excel Writer. http://pear.php.net/package/Spreadsheet_Excel_Writer/ http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setfgcolor.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Report Formatting
[snip] It's still not an excel file but an HTML table sent with an Excel mime-type. This is just asking for trouble if the user does not use Excel, as it's exploiting behaviour that looks very particular to Excel and not ALL spreadsheet software. [/snip] This is true, it is exploiting behavior that is particular to Excel. I was able to do this with Open Office too, even though I didn't fully bake the code for that. The article is pretty old and there are other tools that would probably work a lot better, but the OP asked about Excel and coloring. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Report Formatting
Hi, somebody knows if it is possible to open excel content in a new window? what i want is basically initialize the headers and instead of echo $content i want it to open in a new window. is that possible? - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php-general@lists.php.net Sent: Tue, April 20, 2010 1:47:40 PM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 9:52 AM To: Tommy Pham; php-general@lists.php.net Subject: Re: [PHP] Excel Report Formatting Hi Tommy, The code in the link is traightforward. The problem is you need to install ADOdb. I'm wondering if there is a way to do it from scratch. Thanks - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php- gene...@lists.php.net Sent: Tue, April 20, 2010 11:37:21 AM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 8:30 AM To: php-general@lists.php.net Subject: [PHP] Excel Report Formatting Hi, Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. Thank you __ Get the name you've always wanted @ymail.com or @rocketmail.com! Go to http://ca.promos.yahoo.com/jacko/ http://www.phpeveryday.com/articles/Write-report-in-Excel-Format-Part- 1--P155.html I think the article/sample uses PEAR's Excel Writer. http://pear.php.net/package/Spreadsheet_Excel_Writer/ http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setfgcolor.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Report Formatting
On Tue, 2010-04-20 at 12:58 -0700, Php Developer wrote: Hi, somebody knows if it is possible to open excel content in a new window? what i want is basically initialize the headers and instead of echo $content i want it to open in a new window. is that possible? - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php-general@lists.php.net Sent: Tue, April 20, 2010 1:47:40 PM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 9:52 AM To: Tommy Pham; php-general@lists.php.net Subject: Re: [PHP] Excel Report Formatting Hi Tommy, The code in the link is traightforward. The problem is you need to install ADOdb. I'm wondering if there is a way to do it from scratch. Thanks - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php- gene...@lists.php.net Sent: Tue, April 20, 2010 11:37:21 AM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 8:30 AM To: php-general@lists.php.net Subject: [PHP] Excel Report Formatting Hi, Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. Thank you __ Get the name you've always wanted @ymail.com or @rocketmail.com! Go to http://ca.promos.yahoo.com/jacko/ http://www.phpeveryday.com/articles/Write-report-in-Excel-Format-Part- 1--P155.html I think the article/sample uses PEAR's Excel Writer. http://pear.php.net/package/Spreadsheet_Excel_Writer/ http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setfgcolor.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Erm, why? Wouldn't it be easier to output the table as HTML in a new window? Excel does have plugins for *some* browsers that allow the browser to open Excel files within its own window (in that annoying way that PDF's try to open up within the browser) but this can be turned off by the user (and I can't imagine many people who wouldn't turn it off tbh) This is also only available for some browsers on some operating systems, and you could well be alienating users who don't use Microsoft software. Are you looking to allow a way for users to edit spreadsheets within your web app? It's worth considering that this is a huge task. There are some Javascript-based plugins that you can use to embed spreadsheet-like data into your web pages, but I think anything more than basic formatting and data entry is a pipe dream at the moment. The only really usable web-based spreadsheets I've seen come from the likes of very big companies like Google and Microsoft. Thanks, Ash http://www.ashleysheridan.co.uk
RE: [PHP] Excel Report Formatting
[snip] Hi, somebody knows if it is possible to open excel content in a new window? what i want is basically initialize the headers and instead of echo $content i want it to open in a new window. is that possible? [/snip] Yes, it is possible. a href= target=_blank Is this too obvious? My apologies, I am in a funky mood today. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Report Formatting
On Tue, Apr 20, 2010 at 1:00 PM, Ashley Sheridan a...@ashleysheridan.co.uk wrote: On Tue, 2010-04-20 at 12:58 -0700, Php Developer wrote: Hi, somebody knows if it is possible to open excel content in a new window? what i want is basically initialize the headers and instead of echo $content i want it to open in a new window. is that possible? - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php-general@lists.php.net Sent: Tue, April 20, 2010 1:47:40 PM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 9:52 AM To: Tommy Pham; php-general@lists.php.net Subject: Re: [PHP] Excel Report Formatting Hi Tommy, The code in the link is traightforward. The problem is you need to install ADOdb. I'm wondering if there is a way to do it from scratch. Thanks - Original Message From: Tommy Pham tommy...@gmail.com To: Php Developer pdevelo...@rocketmail.com; php- gene...@lists.php.net Sent: Tue, April 20, 2010 11:37:21 AM Subject: RE: [PHP] Excel Report Formatting -Original Message- From: Php Developer [mailto:pdevelo...@rocketmail.com] Sent: Tuesday, April 20, 2010 8:30 AM To: php-general@lists.php.net Subject: [PHP] Excel Report Formatting Hi, Do you know if there is a way to generate a formated Excel report. I want some rows to be of different colors than the others. Thank you __ Get the name you've always wanted @ymail.com or @rocketmail.com! Go to http://ca.promos.yahoo.com/jacko/ http://www.phpeveryday.com/articles/Write-report-in-Excel-Format-Part- 1--P155.html I think the article/sample uses PEAR's Excel Writer. http://pear.php.net/package/Spreadsheet_Excel_Writer/ http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setfgcolor.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Erm, why? Wouldn't it be easier to output the table as HTML in a new window? Excel does have plugins for *some* browsers that allow the browser to open Excel files within its own window (in that annoying way that PDF's try to open up within the browser) but this can be turned off by the user (and I can't imagine many people who wouldn't turn it off tbh) This is also only available for some browsers on some operating systems, and you could well be alienating users who don't use Microsoft software. Are you looking to allow a way for users to edit spreadsheets within your web app? It's worth considering that this is a huge task. There are some Javascript-based plugins that you can use to embed spreadsheet-like data into your web pages, but I think anything more than basic formatting and data entry is a pipe dream at the moment. The only really usable web-based spreadsheets I've seen come from the likes of very big companies like Google and Microsoft. Thanks, Ash http://www.ashleysheridan.co.uk If you're looking for something like Google's spreadsheet document capability, you got a LOT of work of you ;) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Spreadsheets and PHP
On Fri, Feb 26, 2010 at 3:47 PM, Ian Robertson irobert...@americantextile.com wrote: Thank you all very much for your replies. I learned about a few new approaches. I didn't see it come up yet, so I'll post the URL of what I have been using. php_writeexcel - http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/ I've been able to pull off quite a bit with this class and actually have referenced this Perl page for documentation since this PHP class was ported from a Perl class - http://cpansearch.perl.org/src/JMCNAMARA/Spreadsheet-WriteExcel-0.37/WriteExcel/doc/WriteExcel.html Another option just occurred to me. You can attach to and read/write to Excel sheets using an ODBC client. As long as you are building a sheet/workbook with strictly tabular data, that should allow you to issue SQL commands. I'm not sure whether you can create an Excel file from scratch that way, but if not it would be trivial to have an empty Excel file that you can use as a template. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
Thank you all very much for your replies. I learned about a few new approaches. I didn't see it come up yet, so I'll post the URL of what I have been using. php_writeexcel - http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/ I've been able to pull off quite a bit with this class and actually have referenced this Perl page for documentation since this PHP class was ported from a Perl class - http://cpansearch.perl.org/src/JMCNAMARA/Spreadsheet-WriteExcel-0.37/WriteExcel/doc/WriteExcel.html So, many thanks for your replies and also thanks to Johann and his 3 Bettinas, wherever you are, haha. -Original Message- From: Jay Blanchard [mailto:jblanch...@pocket.com] Sent: Sunday, February 21, 2010 9:44 PM To: Ian Robertson; php-general@lists.php.net Subject: RE: [PHP] Excel Spreadsheets and PHP [snip] What are you using, if anything, to create Excel spreadsheets with PHP? [/snip] PHP http://www.evolt.org/node/26896 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
[snip] What are you using, if anything, to create Excel spreadsheets with PHP? [/snip] PHP http://www.evolt.org/node/26896 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Spreadsheets and PHP
On 19-2-2010 21:28, Ian Robertson wrote: Hello, everyone. Just a quick question. What are you using, if anything, to create Excel spreadsheets with PHP? Thank you in advance. Hello Ian, You can use the PHPExcel class. It can read, write and convert between Excel versions without the need of the COM. Depending on the version of the file needs to be, it uses XML or bitbashing. Or, like others said, you can use XML for writing Excel files in the new xlsx format. This is actually a ZIP-file with a lot of XML-files in it. This makes it possible to use other XML techniques as well like Schema, XSL, XPath and DTD. I prefer to make a XML based template and use XSL to transform and parse the data. With PHP you can pass parameters to the XSL file and fill in the blanks where needed. Excel is quite complex due to the cells, the styles, the formulas and the worksheets. Kind regards, Aschwin Wesselius -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Spreadsheets and PHP
Bastien Koert wrote: You can also create an htnl table and excel will happily handle that as well. The real trick is to get IE to accept the stream as a file download. I find that I need to save the file first and the push the file down. +1 this approach; Excel is HTTP aware and you can simply plumb in the URL of an HTML table and excel will do the rest; it works v well; saves tonnes of work and means you can do nice little things like importing SPARQL over HTTP queries straight in to excel - and then make nice pivot [1] views of the data, all in a couple of minutes. [1] http://www.getpivot.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
From: Ian Robertson What are you using, if anything, to create Excel spreadsheets with PHP? Output CSV files with the correct MIME type. MS-Windows will open them in Excel by default in both IE and Firefox. Unfortunately, this happens even if you have Open Office installed and would prefer to use that. Bob McConnell -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
On Fri, 2010-02-19 at 15:36 -0500, Bob McConnell wrote: From: Ian Robertson What are you using, if anything, to create Excel spreadsheets with PHP? Output CSV files with the correct MIME type. MS-Windows will open them in Excel by default in both IE and Firefox. Unfortunately, this happens even if you have Open Office installed and would prefer to use that. Bob McConnell I believe there are pear classes to output these files, and the new xlsx format is XML-based so shouldnt be too difficult to output something simple. Also, the ods format is well documented, and again should be very easy to output to. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Excel Spreadsheets and PHP
On Fri, Feb 19, 2010 at 3:36 PM, Bob McConnell r...@cbord.com wrote: From: Ian Robertson What are you using, if anything, to create Excel spreadsheets with PHP? Output CSV files with the correct MIME type. MS-Windows will open them in Excel by default in both IE and Firefox. Unfortunately, this happens even if you have Open Office installed and would prefer to use that. Bob McConnell -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php If you need more control over formatting, formulas, etc., there is an XML format that Microsoft supports. http://msdn.microsoft.com/en-us/library/aa140066%28office.10%29.aspx Then you can use DOM in PHP to build what you need. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
-Original Message- From: Ian Robertson [mailto:irobert...@americantextile.com] Sent: Friday, February 19, 2010 1:28 PM To: php-general@lists.php.net Subject: [PHP] Excel Spreadsheets and PHP Hello, everyone. Just a quick question. What are you using, if anything, to create Excel spreadsheets with PHP? Thank you in advance. Pear Spreadsheet Excel Writer. http://pear.php.net/package/Spreadsheet_Excel_Writer Mike -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Spreadsheets and PHP
You can also create an htnl table and excel will happily handle that as well. The real trick is to get IE to accept the stream as a file download. I find that I need to save the file first and the push the file down. On 2/19/10, Hansen, Mike mike.han...@atmel.com wrote: -Original Message- From: Ian Robertson [mailto:irobert...@americantextile.com] Sent: Friday, February 19, 2010 1:28 PM To: php-general@lists.php.net Subject: [PHP] Excel Spreadsheets and PHP Hello, everyone. Just a quick question. What are you using, if anything, to create Excel spreadsheets with PHP? Thank you in advance. Pear Spreadsheet Excel Writer. http://pear.php.net/package/Spreadsheet_Excel_Writer Mike -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Sent from my mobile device Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
-Original Message-
From: Ian Robertson [mailto:irobert...@americantextile.com]
Sent: Friday, February 19, 2010 1:28 PM
To: php-general@lists.php.net
Subject: [PHP] Excel Spreadsheets and PHP
Hello, everyone.
Just a quick question.
What are you using, if anything, to create Excel spreadsheets
with PHP?
Thank you in advance.
Pear Spreadsheet Excel Writer.
http://pear.php.net/package/Spreadsheet_Excel_Writer
Related, here is a routine we use. Assuming you already have your data in a
multi-array.
/**
* Outputs an Excel .xls file
* Note: a row that starts with --- will be considered a separator row
and output any text following the --- as such.
*
* @param string $title_text The name of the title in the Excel .xls
document (gmdate('Y-m-d H:i') is auto appended)
* @param array $header_array an array of headers for each column
* @param array $data_array the data for each column and row
* @param string $file_name the name of the .xls file to save as
(gmdate('Y-m-d H:i') is auto appended), defaults to $title_text
* @author Daevid Vincent
* @date 10/29/2009
*/
function download_table_to_excel($title_text, $header_array, $data_array,
$file_name=null)
{
//require_once './includes/gui/gui_setup.inc.php';
if (!$file_name) $file_name = $title_text;
$file_name = str_replace( array('[', ']'), array('(',')'),
$file_name);
add_user_log('Action', 'Download '.$file_name.' Excel file');
set_include_path(get_include_path().PATH_SEPARATOR.ROOTPATH.'/includes/pear
');
require_once
ROOTPATH.'/includes/pear/Spreadsheet/Excel/Writer.php';
$excel_control_characters = array('@', '=');
$exceldoc = new Spreadsheet_Excel_Writer();
// Set version to 8 (BIFF8) so strings are not truncated to 255
chars
//$exceldoc-setVersion(8);
//http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-write
r.spreadsheet-excel-writer-workbook.setversion.php
//http://forum.openx.org/index.php?showtopic=503418353
//http://pear.php.net/bugs/bug.php?id=3384
$worksheet = $exceldoc-addWorksheet('Sheet 1'); //sheet name can
only be 31 chars, but we only use one sheet, so hard-code it
$format_data = $exceldoc-addFormat();
$format_data-setTextWrap();
// Create an array to track the value length per column, the
default width is 8.11
$max_column = count($header_array) - 1;
$max_len_by_column = array();
for ($col = 0; $col = $max_column; $col++)
$max_len_by_column[$col] = 8.11;
$row = -1;
// Optionally write table title
if ($title_text)
{
$format_title = $exceldoc-addFormat();
$format_title-setAlign('center');
$format_title-setAlign('vcenter');
$format_title-setBold();
$format_title-setTextWrap();
$title_text .= ' (created on '.gmdate('Y-m-d @ H:i').'
UTC)';
// adjust the row height from the number of lines in the
table title
$lines = substr_count($title_text, 'br') + 1;
$height = $lines * 14;
$row++;
$value =
html_entity_decode(trim(strip_tags(str_replace('br', \n,
$title_text;
if (is_string($value) in_array(substr($value,0,1),
$excel_control_characters)) $value = ' '.$value; // Add a space before
Excel control characters
$worksheet-write($row, 0, $value, $format_title);
$worksheet-setRow($row, $height);
$worksheet-mergeCells($row, 0, $row, $max_column);
}
// Write column headers
$format_header = $exceldoc-addFormat();
$format_header-setBold();
$format_header-setTextWrap();
$row++;
foreach ($header_array as $col = $header)
{
// remove html tags from values
$value =
html_entity_decode(trim(strip_tags(str_replace('br', \n,
is_array($header) ? $header[0] : $header;
if (is_string($value) and in_array(substr($value,0,1),
$excel_control_characters)) $value = .$value; // Add a space before
Excel control characters
$worksheet-write($row, $col, $value, $format_header);
if (is_array($header)) $worksheet-writeNote($row, $col,
$header[1]);
}
foreach ($data_array as $i = $data)
{
$row++;
$col = 0;
//check for magic separator rows
if ( substr($data,0,3) == '---' )
{
$separator_row = substr($data,3);
// adjust the row height from the number of lines
in the table title
$lines = substr_count($separator_row, 'br') + 1;
$height = $lines * 14;
$row++;
$value =
Re: [PHP] excel macro into php
On 5/2/07, Anton Krall [EMAIL PROTECTED] wrote: Guys.. I have a problem and I was wondering if somebody with good php knowledge could help. I have this excel macro that converts number currency (mexican) into a string and I was wondering if somebody could help translating it to php... Her eis the macro, if you could help, I would really appreciate it. Thanks guys! I think that starting over is better then trying to convert this excel macro. What exactly do you want to convert, from ?? to ?? Since all functions are in a language that isn't english, dutch, german or frisian, I can't understand. snip laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO, SEIS, SIETE, OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE, QUINCE, DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE, VEINTE, VEINTIUN, VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO, VEINTISEIS, VEINTISIETE, VEINTIOCHO, VEINTINUEVE) laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA, CINCUENTA, SESENTA, SETENTA, OCHENTA, NOVENTA) laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS, CUATROCIENTOS, QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS, NOVECIENTOS) Nice array, if you need it, you can use it directly in PHP, just add a $ sign before the variable and ; at the end, and it is a PHP array :) snip I've snipped the rest of your code, because it's very though to understand the code if you can't read the language. Like i said, show me what you want to convert from and to, so that i can help you writing something new, or comment your own macro in english so that people can understand it. Tijnema -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel macro into php
Sie nicht auch sprechen Spanischen, Tij? On 5/3/07, Tijnema ! [EMAIL PROTECTED] wrote: On 5/2/07, Anton Krall [EMAIL PROTECTED] wrote: Guys.. I have a problem and I was wondering if somebody with good php knowledge could help. I have this excel macro that converts number currency (mexican) into a string and I was wondering if somebody could help translating it to php... Her eis the macro, if you could help, I would really appreciate it. Thanks guys! I think that starting over is better then trying to convert this excel macro. What exactly do you want to convert, from ?? to ?? Since all functions are in a language that isn't english, dutch, german or frisian, I can't understand. snip laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO, SEIS, SIETE, OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE, QUINCE, DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE, VEINTE, VEINTIUN, VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO, VEINTISEIS, VEINTISIETE, VEINTIOCHO, VEINTINUEVE) laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA, CINCUENTA, SESENTA, SETENTA, OCHENTA, NOVENTA) laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS, CUATROCIENTOS, QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS, NOVECIENTOS) Nice array, if you need it, you can use it directly in PHP, just add a $ sign before the variable and ; at the end, and it is a PHP array :) snip I've snipped the rest of your code, because it's very though to understand the code if you can't read the language. Like i said, show me what you want to convert from and to, so that i can help you writing something new, or comment your own macro in english so that people can understand it. Tijnema -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Daniel P. Brown [office] (570-) 587-7080 Ext. 272 [mobile] (570-) 766-8107
Re: [PHP] excel macro into php
On 5/3/07, Daniel Brown [EMAIL PROTECTED] wrote: Sie nicht auch sprechen Spanischen, Tij? Nein, aber mein deutsch ist also nicht gut ;) Sprechen sie Spanischen Daniel?\ Tijnema On 5/3/07, Tijnema ! [EMAIL PROTECTED] wrote: On 5/2/07, Anton Krall [EMAIL PROTECTED] wrote: Guys.. I have a problem and I was wondering if somebody with good php knowledge could help. I have this excel macro that converts number currency (mexican) into a string and I was wondering if somebody could help translating it to php... Her eis the macro, if you could help, I would really appreciate it. Thanks guys! I think that starting over is better then trying to convert this excel macro. What exactly do you want to convert, from ?? to ?? Since all functions are in a language that isn't english, dutch, german or frisian, I can't understand. snip laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO, SEIS, SIETE, OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE, QUINCE, DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE, VEINTE, VEINTIUN, VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO, VEINTISEIS, VEINTISIETE, VEINTIOCHO, VEINTINUEVE) laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA, CINCUENTA, SESENTA, SETENTA, OCHENTA, NOVENTA) laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS, CUATROCIENTOS, QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS, NOVECIENTOS) Nice array, if you need it, you can use it directly in PHP, just add a $ sign before the variable and ; at the end, and it is a PHP array :) snip I've snipped the rest of your code, because it's very though to understand the code if you can't read the language. Like i said, show me what you want to convert from and to, so that i can help you writing something new, or comment your own macro in english so that people can understand it. Tijnema -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Daniel P. Brown [office] (570-) 587-7080 Ext. 272 [mobile] (570-) 766-8107 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel macro into php
On Thu, May 3, 2007 4:37 am, Tijnema ! wrote: On 5/2/07, Anton Krall [EMAIL PROTECTED] wrote: Guys.. I have a problem and I was wondering if somebody with good php knowledge could help. I have this excel macro that converts number currency (mexican) into a string and I was wondering if somebody could help translating it to php... Her eis the macro, if you could help, I would really appreciate it. Thanks guys! I think that starting over is better then trying to convert this excel macro. What exactly do you want to convert, from ?? to ?? Since all functions are in a language that isn't english, dutch, german or frisian, I can't understand. snip laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO, SEIS, SIETE, OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE, QUINCE, DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE, VEINTE, VEINTIUN, VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO, VEINTISEIS, VEINTISIETE, VEINTIOCHO, VEINTINUEVE) laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA, CINCUENTA, SESENTA, SETENTA, OCHENTA, NOVENTA) laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS, CUATROCIENTOS, QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS, NOVECIENTOS) Nice array, if you need it, you can use it directly in PHP, just add a $ sign before the variable and ; at the end, and it is a PHP array :) snip I've snipped the rest of your code, because it's very though to understand the code if you can't read the language. Like i said, show me what you want to convert from and to, so that i can help you writing something new, or comment your own macro in english so that people can understand it. I deleted the original, but... 90% of the macro can be converted to PHP by putting $ in front of all the variables. 90% of the function names are the same, or very similar. There was one MOD call that needs to change to % Do all that, run it in PHP, fix any syntax errors, and then run tests side-by-side and compare outputs. I'm betting it's a no-brainer conversion, based on what I recall of the OP. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel macro into php
Tij: Ja, spreche ich Spanischen. Me habla espanol bien no muy bien, pero bastante bueno. Ik leer enkel het Nederlands. Het is als het Engels, maar er is één of ander verschil. Richard (the original, if you want it): Guys.. I have a problem and I was wondering if somebody with good php knowledge could help. I have this excel macro that converts number currency (mexican) into a string and I was wondering if somebody could help translating it to php... Her eis the macro, if you could help, I would really appreciate it. Thanks guys! Function CantidadEnLetra(tyCantidad As Currency) As String Dim lyCantidad As Currency, lyCentavos As Currency, lnDigito As Byte, lnPrimerDigito As Byte, lnSegundoDigito As Byte, lnTercerDigito As Byte, lcBloque As String, lnNumeroBloques As Byte, lnBloqueCero tyCantidad = Round(tyCantidad, 2) lyCantidad = Int(tyCantidad) lyCentavos = (tyCantidad - lyCantidad) * 100 laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO, SEIS, SIETE, OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE, QUINCE, DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE, VEINTE, VEINTIUN, VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO, VEINTISEIS, VEINTISIETE, VEINTIOCHO, VEINTINUEVE) laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA, CINCUENTA, SESENTA, SETENTA, OCHENTA, NOVENTA) laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS, CUATROCIENTOS, QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS, NOVECIENTOS) lnNumeroBloques = 1 Do lnPrimerDigito = 0 lnSegundoDigito = 0 lnTercerDigito = 0 lcBloque = lnBloqueCero = 0 For i = 1 To 3 lnDigito = lyCantidad Mod 10 If lnDigito 0 Then Select Case i Case 1 lcBloque =laUnidades(lnDigito - 1) lnPrimerDigito = lnDigito Case 2 If lnDigito = 2 Then lcBloque =laUnidades((lnDigito * 10) + lnPrimerDigito - 1) Else lcBloque =laDecenas(lnDigito - 1) IIf(lnPrimerDigito 0, Y, Null) lcBloque End If lnSegundoDigito = lnDigito Case 3 lcBloque =IIf(lnDigito = 1 And lnPrimerDigito = 0 And lnSegundoDigito = 0, CIEN, laCentenas(lnDigito - 1)) lcBloque lnTercerDigito = lnDigito End Select Else lnBloqueCero = lnBloqueCero + 1 End If lyCantidad = Int(lyCantidad / 10) If lyCantidad = 0 Then Exit For End If Next i Select Case lnNumeroBloques Case 1 CantidadEnLetra = lcBloque Case 2 CantidadEnLetra = lcBloque IIf(lnBloqueCero = 3, Null, MIL) CantidadEnLetra Case 3 CantidadEnLetra = lcBloque IIf(lnPrimerDigito = 1 And lnSegundoDigito = 0 And lnTercerDigito = 0, MILLON, MILLONES) CantidadEnLetra End Select lnNumeroBloques = lnNumeroBloques + 1 Loop Until lyCantidad = 0 CantidadEnLetra = ( CantidadEnLetra IIf(tyCantidad 1, PESOS , PESO ) Format(Str(lyCentavos), 00) /100 M.N. ) End Function On 5/3/07, Tijnema ! [EMAIL PROTECTED] wrote: On 5/3/07, Daniel Brown [EMAIL PROTECTED] wrote: Sie nicht auch sprechen Spanischen, Tij? Nein, aber mein deutsch ist also nicht gut ;) Sprechen sie Spanischen Daniel?\ Tijnema On 5/3/07, Tijnema ! [EMAIL PROTECTED] wrote: On 5/2/07, Anton Krall [EMAIL PROTECTED] wrote: Guys.. I have a problem and I was wondering if somebody with good php knowledge could help. I have this excel macro that converts number currency (mexican) into a string and I was wondering if somebody could help translating it to php... Her eis the macro, if you could help, I would really appreciate it. Thanks guys! I think that starting over is better then trying to convert this excel macro. What exactly do you want to convert, from ?? to ?? Since all functions are in a language that isn't english, dutch, german or frisian, I can't understand. snip laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO, SEIS, SIETE, OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE, QUINCE, DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE, VEINTE, VEINTIUN, VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO, VEINTISEIS, VEINTISIETE, VEINTIOCHO, VEINTINUEVE) laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA, CINCUENTA, SESENTA, SETENTA, OCHENTA, NOVENTA) laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS, CUATROCIENTOS, QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS, NOVECIENTOS) Nice array, if you need it, you can use it directly in PHP, just add a $ sign before the variable and ; at the end, and it is a PHP array :) snip I've snipped the rest of your code, because it's very though to understand the code if you can't read the language. Like i said, show me what you want to convert from and to, so that i can help you writing something new, or comment your own macro in english so that people can understand it. Tijnema -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Daniel P. Brown [office] (570-) 587-7080 Ext. 272 [mobile] (570-) 766-8107 -- Daniel P. Brown [office] (570-) 587-7080 Ext. 272 [mobile] (570-) 766-8107
Re: [PHP] excel macro into php
On Thu, May 3, 2007 11:17 am, Daniel Brown wrote:
Function CantidadEnLetra(tyCantidad As Currency) As String
function CantidadEnLetra ($tyCantidad) {
/*
Dim lyCantidad As Currency, lyCentavos As Currency, lnDigito As Byte,
lnPrimerDigito As Byte, lnSegundoDigito As Byte, lnTercerDigito As
Byte,
lcBloque As String, lnNumeroBloques As Byte, lnBloqueCero
*/
tyCantidad = Round(tyCantidad, 2)
$tyCantidad = round($tyCantidad, 2);
lyCantidad = Int(tyCantidad)
$lyCantidad = (int) $tyCantidad; //or IntVal
lyCentavos = (tyCantidad - lyCantidad) * 100
$lyCentavos = ($tyCantidad - $lyCantidad) * 100;
laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO, SEIS,
SIETE,
OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE, QUINCE,
DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE, VEINTE,
VEINTIUN,
VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO,
VEINTISEIS,
VEINTISIETE, VEINTIOCHO, VEINTINUEVE)
$laUnidades = array(...);
laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA,
CINCUENTA,
SESENTA, SETENTA, OCHENTA, NOVENTA)
laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS,
CUATROCIENTOS,
QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS,
NOVECIENTOS)
$laDecenas = array(...);
lnNumeroBloques = 1
$luNumeroBloques = 1;
Do
do {
lnPrimerDigito = 0
lnSegundoDigito = 0
lnTercerDigito = 0
lcBloque =
lnBloqueCero = 0
//I refuse to re-type these with $ in front at this point. You do it.
//In fact, from here on, I only translate any new syntax
For i = 1 To 3
for ($i = 1; $i = 3; $i++){
lnDigito = lyCantidad Mod 10
$lnDigito = $lyCantidad % 10;
If lnDigito 0 Then
if ($lnDigito != 0){ // might actually work in php as well...
Select Case i
switch ($i){
Case 1
case 1:
lcBloque =laUnidades(lnDigito - 1)
$lcBloque = . laUnidades($lnDigito - 1);
lnPrimerDigito = lnDigito
//$ and ;
Case 2
case 2:
If lnDigito = 2 Then
lcBloque =laUnidades((lnDigito * 10) + lnPrimerDigito - 1)
Else
lcBloque =laDecenas(lnDigito - 1) IIf(lnPrimerDigito 0,
Y,
Null) lcBloque
$lcBloque = . laDecenas($lnDigito - 1) . ($lnPrimerDigito != 0 ?
Y : '') . $lcBloque;
End If
}
lnSegundoDigito = lnDigito
Case 3
lcBloque =IIf(lnDigito = 1 And lnPrimerDigito = 0 And
lnSegundoDigito
= 0, CIEN, laCentenas(lnDigito - 1)) lcBloque
Use in PHP for And in VB or whatever this is.
lnTercerDigito = lnDigito
End Select
}
Else
lnBloqueCero = lnBloqueCero + 1
End If
lyCantidad = Int(lyCantidad / 10)
If lyCantidad = 0 Then
Exit For
break; //get out of for loop
End If
}
Next i
}
Select Case lnNumeroBloques
Case 1
CantidadEnLetra = lcBloque
Case 2
CantidadEnLetra = lcBloque IIf(lnBloqueCero = 3, Null, MIL)
CantidadEnLetra
Case 3
CantidadEnLetra = lcBloque IIf(lnPrimerDigito = 1 And
lnSegundoDigito = 0
And lnTercerDigito = 0, MILLON, MILLONES) CantidadEnLetra
End Select
lnNumeroBloques = lnNumeroBloques + 1
Loop Until lyCantidad = 0
I've lost track of where this loop is going at this point, because
the indentation of the macro is non-existent...
Indent the PHP code correctly, and it should get pretty clear.
CantidadEnLetra = ( CantidadEnLetra IIf(tyCantidad 1, PESOS
,
PESO ) Format(Str(lyCentavos), 00) /100 M.N. )
Format is probably some variant of http://php.net/sprintf
End Function
}
On 5/3/07, Tijnema ! [EMAIL PROTECTED] wrote:
On 5/3/07, Daniel Brown [EMAIL PROTECTED] wrote:
Sie nicht auch sprechen Spanischen, Tij?
Nein, aber mein deutsch ist also nicht gut ;) Sprechen sie
Spanischen
Daniel?\
Tijnema
On 5/3/07, Tijnema ! [EMAIL PROTECTED] wrote:
On 5/2/07, Anton Krall [EMAIL PROTECTED] wrote:
Guys.. I have a problem and I was wondering if somebody with
good
php
knowledge could help.
I have this excel macro that converts number currency
(mexican) into
a
string and I was wondering if somebody could help translating
it to
php...
Her eis the macro, if you could help, I would really
appreciate it.
Thanks guys!
I think that starting over is better then trying to convert this
excel
macro. What exactly do you want to convert, from ?? to ??
Since all functions are in a language that isn't english, dutch,
german or frisian, I can't understand.
snip
laUnidades = Array(UN, DOS, TRES, CUATRO, CINCO,
SEIS,
SIETE,
OCHO, NUEVE, DIEZ, ONCE, DOCE, TRECE, CATORCE,
QUINCE,
DIESISEIS, DIESISIETE, DIESIOCHO, DIESINUEVE,
VEINTE,
VEINTIUN,
VEINTIDOS, VEINTITRES, VEINTICUATRO, VEINTICINCO,
VEINTISEIS,
VEINTISIETE, VEINTIOCHO, VEINTINUEVE)
laDecenas = Array(DIEZ, VEINTE, TREINTA, CUARENTA,
CINCUENTA,
SESENTA, SETENTA, OCHENTA, NOVENTA)
laCentenas = Array(CIENTO, DOSCIENTOS, TRESCIENTOS,
CUATROCIENTOS,
QUINIENTOS, SEISCIENTOS, SETECIENTOS, OCHOCIENTOS,
NOVECIENTOS)
Nice array, if you need it, you can use it directly in PHP, just
add a
$ sign before the variable and ; at the end, and it is a PHP
array :)
snip
Re: [PHP] excel macro into php
On 5/3/07, Daniel Brown [EMAIL PROTECTED] wrote: Tij: Ja, spreche ich Spanischen. Me habla espanol bien no muy bien, pero bastante bueno. Ik leer enkel het Nederlands. Het is als het Engels, maar er is één of ander verschil. Haha, dat is toch redelijk nederlands :) Dutch Bien, esto es mi español, Eso Es todo puedo decir Spanish Engels is quite easy for me, but i still make some grammar faults ...:( English Meine Deutsch ist besser dann meine Francés. German Je parle un petit français de morceau French Mar ik sprek ek frysk Frisian Sei um português pequeno de bit demais Portuguese И немного русский язык Russian So can you read all those languages? :P Tijnema snip
Re: [PHP] excel macro into php
Actually, yes, about half but I didn't use a translator, dork! ;-P On 5/3/07, Tijnema ! [EMAIL PROTECTED] wrote: On 5/3/07, Daniel Brown [EMAIL PROTECTED] wrote: Tij: Ja, spreche ich Spanischen. Me habla espanol bien no muy bien, pero bastante bueno. Ik leer enkel het Nederlands. Het is als het Engels, maar er is één of ander verschil. Haha, dat is toch redelijk nederlands :) Dutch Bien, esto es mi español, Eso Es todo puedo decir Spanish Engels is quite easy for me, but i still make some grammar faults ...:( English Meine Deutsch ist besser dann meine Francés. German Je parle un petit français de morceau French Mar ik sprek ek frysk Frisian Sei um português pequeno de bit demais Portuguese И немного русский язык Russian So can you read all those languages? :P Tijnema snip -- Daniel P. Brown [office] (570-) 587-7080 Ext. 272 [mobile] (570-) 766-8107
[PHP] Re: PHP excel capability
Jonathan Kahan wrote: Can PHP be used to generate an excel file that A) Contains Macros B) Allows Graphics to be attached C) where cells can contain borders as well as cells being merged. I know there is some PHP excel functionality so i am assume basics like underlines, italics and i am hoping vertical alignments can be done. If the answer is no, then can a template that has all of these be opened and read using php where all that needs to be done is simply place in certain cells whatever values it is that I want to place. Lastly, do only newer versions of PHP contain excel/pdf functionality? I am in a bit of a time crunch so I apologize if this is not considered PHP stuff or if I could have found my answer easily online. I could not find this discussed in any other posts. Thanks in advance for any help you can give. There is a pear module that can do some degree of Excel tinkering but I've not had much success with it in the past. http://pear.php.net/package/Spreadsheet_Excel_Writer While I've not idea what it is, looking on Pear just now I see: http://pear.php.net/package/Structures_DataGrid_Renderer_XLS which leads to http://pear.php.net/package/Structures_DataGrid not sure if either are what you need but I think I'll have a look at using it ;) Also Excel is quite good at loading HTML pages with tables in them loads up nicely with font styles etc. colspan and rowspan (in html tables) are supported etc. All it requires is the html to be served up with the correct mime type and Excel will load it up. Create a sample spreadsheet in Excel which meets your needs and export it as HTML and then use that as a basis to write your script. I doubt very much if it will support macros, but you can suck it and see. Let me know if you want more info as I have done this before. Col. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel problem
[snip] please advice. [/snip] Have you read the documentation? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel problem
?php
$query = SELECT ;
$result = mysql_query($query);
header (Content-Type: application/octet-stream);
header (Content-Disposition: attachment; filename=whatever.xls);
echo Field1\tField2\r\n; // Column names
while($row = mysql_fetch_array($result)) {
echo $row['field1'] . \t . $row['field2']. \r\n; // Data
}
?
You could also name the file *.csv and use a comma as a delimiter instead.
Don't forget to put quotes around any data that may have tab characters in
it. (Or commas if you're using csv)
Hope that helps
-B
-Original Message-
From: amit hetawal [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 16, 2006 2:26 PM
To: php-general@lists.php.net
Subject: [PHP] Excel problem
Hello all,
Am pretty new to the world of PHP. And am now stuck.
Its like i am displayin the data from a database on to my webpage in
the form of tables.
but now i also want an option for the user to download the above data
into an excel format for the offline use. I dont want to create the
excel file for each of the webpage i am displaying as they are all
dynamic so there can be many.
is there a way so that only if user click the given link at the bottm
of the page then only the data is transferred to excel.
i.e how to i write the displayed data dynamically to the excel without
storing anything on the server.
I am able to config the pear:excelwriter but dont know hwo to get it
working according to my requirements.
please advice.
thanks
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel problem
hello, Yes i have gone through the documentation. bu ti can find a way to link the download excel thing with the link which says donwload in excel. Is it that i have to fetch the data 2 times , one for displayin it in table and the other time downloadin in excel Any suggestions Thanks On 11/16/06, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] please advice. [/snip] Have you read the documentation? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel problem
On Thursday 16 November 2006 21:25, amit hetawal wrote: Hello all, Am pretty new to the world of PHP. And am now stuck. Its like i am displayin the data from a database on to my webpage in the form of tables. but now i also want an option for the user to download the above data into an excel format for the offline use. I dont want to create the excel file for each of the webpage i am displaying as they are all dynamic so there can be many. is there a way so that only if user click the given link at the bottm of the page then only the data is transferred to excel. i.e how to i write the displayed data dynamically to the excel without storing anything on the server. I am able to config the pear:excelwriter but dont know hwo to get it working according to my requirements. please advice. thanks Hi maybe 5 years ago I had same kind of problem. I solve this way.. Excel 2000 has able to save excel document as web page. I generate a excell page and format it. Then I save it html. It was MS xml format. After then that. I use my excel xml file as template for my datas. When user click the link php connects to mysql then create ms xml formatted values then combine with excell xml file. If correct dll's are loaded, an excell sheet will show in IE. It behaves like excel. Also you can easly save it excel file. Regards Sancar -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheet Writer
OUTSTANDING!!! Thanks for taking the time. However, I am bit confused. Are you telling me that the blank lines are what is causing my problems? Jef From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 12:41 PM To: Jef Sullivan; php-general@lists.php.net Subject: re: [PHP] Excel Spreadsheet Writer Jef, Hello, Knowing nothing about Excel Writer at the start, I thought I would tackle this... I found all of the pieces on the net and copied to my webserver... I got the same results you did until I got my header warnings fixed: Warning: Cannot modify header information - headers already sent by (output started at C:\WebServ\www\trunk\include\parser.php:1762) in C:\WebServ\www\trunk\include\excelWriter.php on line 67 Warning: Cannot modify header information - headers already sent by (output started at C:\WebServ\www\trunk\include\parser.php:1762) in C:\WebServ\www\trunk\include\excelWriter.php on line 68 Warning: Cannot modify header information - headers already sent by (output started at C:\WebServ\www\trunk\include\parser.php:1762) in C:\WebServ\www\trunk\include\excelWriter.php on line 69 Warning: Cannot modify header information - headers already sent by (output started at C:\WebServ\www\trunk\include\parser.php:1762) in C:\WebServ\www\trunk\include\excelWriter.php on line 70 Warning: Cannot modify header information - headers already sent by (output started at C:\WebServ\www\trunk\include\parser.php:1762) in C:\WebServ\www\trunk\include\excelWriter.php on line 71 I had serveral others besides the ones listed above... These are caused by PHP Scripts that post anything before or after the Excel (or any other ) Headers are sent... AND THIS INCLUDES BLANK LINES. My problems were caused by: 1. 2.?php ... 9.? Line one above can't be present. and: 1. ?PHP 9.? 10. 11. Lines 10 and 11 above can't be present... Once I fixed all of my Cannot Modify Headers errors, the Cut and Paste of your exact code, created an Excel Spread Sheet. When I ran your code, I got a Open File Dialog box, that says: You have chosen to opentest.xls which is a Microsoft Excel Spreadsheet... What should Firefox do ? Save or Open.- I chose open, and there it is ! I got no output on my Web Browser page at all ( View source just showe htmlhead/headbody/body/html ) Hope this Helps. Scot L. Diddle, Richmond VA, UPS Freight, 804.231.8407 From: Jef Sullivan [EMAIL PROTECTED] Sent: Monday, October 02, 2006 8:44 PM To: php-general@lists.php.net Subject: [PHP] Excel Spreadsheet Writer Greetings all, I am having some problems with the Excel Spreadsheet Writer and I am hoping that I can get some help from this group. I am getting garbled text in my spreadsheet when I go to run the php file. I created a general file to see if I could narrow the problem down. No luck so far… This is what I am getting in the spreadsheet… ÐÏࡱá; þÿ þÿÿÿ[1] þÿÿÿ l ÉB[1]ä [1] My first worksheet=¼%r8X[1][1]1ÈÿArial1ÈÿArial1ÈÿArial1ÈÿArial1ÈÿArial1È Arialàõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À à À à À “[1] €ÿ’â8€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿ€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…? My first worksheet l É[1][1][1]*[1]+[1]€‚[1][1]Á ¡d[1]X[1]X[1]à?à?[1] [1] [1] Name [1] Age [1] John Smith
Re: [PHP] excel - ( csv - ) mysql ?!?
Afan Pasalic wrote:
?php
#names of columns in products table
$column_names = array('ITEM NUMBER', 'ITEM NAME', 'DESCRIPTION',
'COLORS', 'INCLUDES', 'QTY', 'PRICE', 'IMAGE', 'THUMB', 'CATALOG',
'STATUS', 'SUPPLIER', 'EXP_DATE');
if(isset($_POST['SubmitExcelFile']))
{
#VALIDATING UPLOADED FILE
if($_FILES['NewItems']['type'] != 'text/x-csv')
This is not a Good Thing.
The MIME type sent by the browser is browser-dependent. So IE will send
text/x-csv and Netscape will sent text/csv and Mozilla will send
text/plain and...
You should just remove this test entirely.
$alert = 'Uploaded file is not csv file.';
elseif($_FILES['NewItems']['size'] 100)
$alert = 'File you are uploading is bigger then 1MB. Please
contact administrator';
else
{
#IF THERE IS NO TEMP DIR CREATE ONE
#COPY SELECTED FILE TO TEMP DIR
copy($_FILES['NewItems']['tmp_name'],
temp/.$_FILES['NewItems']['name']) or die (mysql_error());
Use http://php.net/move_uploaded_file instead. Safer.
#READING UPLOADED CSV FILE
$row = 1;
$handle = fopen('temp/'.$_FILES['NewItems']['name'], r);
$data = fgetcsv($handle, 1, ,);
, is the default, so adding it here is cruft.
I believe the 1 is optional in recent versions of PHP, and while you
might want to limit lines to 10K, it's not needed, and if you ever NEED a
line bigger than 10K...
#checking if number of columns in csv file and number of columns of
table ($column_names) are the same
if(count($data) != count($column_names))
$alert = 'CSV file is not correct. Number of columns in
database and number of columns in file are not the same.';
This should probably be $alert .= instead of = and you should initialize
$alert = '' at the tip-top of your script.
Because A) this is in a loop, so you may have LOTS of lines with too many
columns, and B) you may need to add an alert earlier in your script some
day.
Also consider using an array for $alert, and using:
$alert[] = 'Alert message here.';
You can then use something like:
echo 'span class=errorp', implode(/p\np, $alert), '/p/span';
if your SAPI is *not* CLI nor CGI, but use just
echo implode(\n, $alert);
if your SAPI is CLI/CGI (in a cron job or something some day)
else
{
for($i=0; $icount($data); $i++)
{
#checking if the order of columns in csv file is the same as order
in table
if($data[$i] != $column_names[$i])
You might want to use http://php.net/strtolower on each, in case different
CSV engines decide to use upper/lower case on the column names...
Also be aware that some folks don't put the first line with column names
when they do CSV dumps...
Maybe you can force your users to do that, maybe not.
{
$alert .= $data[$i] .' - '. $column_names[$i].' ---
Columns in database and CSV file are not in the same orderbr';
}
}
}
if(!isset($alert))
This will change to !count($alert)
{
while (($data = fgetcsv($handle, 1000, ,)) !== FALSE)
{
$query = select itemNumber from products_test where
itemNumber='.$data[0].';
$query = mysql_query($query) or die (mysql_error());
Ouch. If two people are uploading CSV files, then they will most likely
be generating duplicate itemNumber values for the same data...
If you're just uploading your own data, this is fine, though.
Although I think there's a PHP application out there that is designed to
synchronize ODBC and MySQL databases. You may want to look around for it
instead of rolling you rown.
if(mysql_num_rows($query) 0)
{
$duplicates[] = $data[0];
}
else
{
$num = count($data);
$insert_item_query = 'insert into products_test values
(';
for ($c=1; $c $num; $c++)#starting from 1
beacause first row are column names
But this is not counting ROWS, it's counting COLUMNS.
You've ALREADY completely and thoroughly processed that first bogus row
with the field names...
Maybe eXcel dumps out some bogus first column you don't want?
{
$c == 1 ?$insert_item_query .=
'.addslashes($data[$c]).' : $insert_item_query .= ,
'.addslashes($data[$c]).' ;
}
$insert_item_query .= ')';
echo 'QUERY: '.$insert_item_query.'brbr';
mysql_query($insert_item_query) or die
(mysql_error());
++$row;
}
}
fclose($handle);
#list of all items already exist in table
echo 'pno. of duplicates:
'.count($duplicates).'brduplicates: '.print_array($duplicates).'br';
}
}
}
RE: [PHP] Excel files with multiple sheets
[snip] I have been looking but have not found yet of a way to generate an excel file with multiple pages using a PHP page. I was hoping someone here might have hit on a multiple page document. I can do a single file, but was looking for something a little more powerful before I try to create my own. No sense in re-inventing the wheel... [/snip] There is PHP class called writeExcel (original was PERL) http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-0.37/WriteEx cel/doc/WriteExcel.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel output question
On Sat, 7 Feb 2004, Jake McHenry wrote: Hi everyone. I'm outputing payroll information to an excel csv file, and when anyone get's paid over 1k, the amount is split into two fields because of the comma in the amount. Is there anyway I can tell excel that this particular comma is not a deliminator? quote it Thanks, Jake -- Co-Founder Command Prompt, Inc. The wheel's spinning but the hamster's dead -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Table download it
[snip] I want generate an excel table download it as a file (not view in IE), what HTML mimetype should i use? [/snip] header(Content-Type: application/vnd.ms-excel); header(Content-Disposition: inline; filename=\excel.xls\); header(Expires: 0); header(Cache-Control: must-revalidate, post-check=0, pre-check=0); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Table download it
Jay Blanchard wrote: [snip] I want generate an excel table download it as a file (not view in IE), what HTML mimetype should i use? [/snip] header(Content-Type: application/vnd.ms-excel); header(Content-Disposition: inline; filename=\excel.xls\); header(Expires: 0); header(Cache-Control: must-revalidate, post-check=0, pre-check=0); header(Content-type: application/octet-stream); header(Content-Disposition: attachment; filename=yourfile.xls); header(Pragma: no-cache); header(Expires: 0); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Files
Hi, is there a way to create excel files with php? Thanks. This was answered yesterday and I'm way too lazy to type out my reply again. Here is an archive search on the word 'excel' http://marc.theaimsgroup.com/?l=php-generalw=2r=1s=excelq=b Here is yesterday's thread http://marc.theaimsgroup.com/?t=10637468632r=1w=2 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Files
:)...stupid me :), 10x Paul GnuPG Key http://sgi.rdscv.ro/~paulm/paulm.PGP On Thu, 18 Sep 2003, Jennifer Goodie wrote: Hi, is there a way to create excel files with php? Thanks. This was answered yesterday and I'm way too lazy to type out my reply again. Here is an archive search on the word 'excel' http://marc.theaimsgroup.com/?l=php-generalw=2r=1s=excelq=b Here is yesterday's thread http://marc.theaimsgroup.com/?t=10637468632r=1w=2 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Parser
snip Does anyone know of any free Excel parsing non-COM code available on the net? Just straight php driven? /snip I have not tried any of these but there might be something in here you can use. http://www.phpclasses.org/search.html?words=Excelgo_search=1 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Parser
Hi, Friday, July 11, 2003, 4:27:13 AM, you wrote: JM Hi all, JM Does anyone know of any free Excel parsing non-COM code available on the JM net? Just straight php driven? JM I've found a couple commercial solutions but would like to try this myself JM or save money. Thanks for any help. JM Johnny I have started an exel reader class that outputs a table and a style sheet that may help get you started. still needs quite a bit of work but the basics are there. Let me know and I'll email you the files. -- regards, Tom -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
yes i hope it comes handy , i'm stoked i found it, the import works perfect
i simply upload the xls file str8 into mysql :D or u can play around with
the csv data using getcsv()
-Original Message-
From: jon roig [mailto:[EMAIL PROTECTED]
Sent: Friday, April 04, 2003 4:13 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP] excel 2 csv 2 mysql
Pretty cool, man... I actually didn't know about xls2csv.
Learn something new every day -- this'll certainly come in handy.
'Course, it's not exactly standard with most distributions. For anyone
looking for it, you can download it here:
http://www.45.free.net/~vitus/ice/catdoc/
Or, depending on your distro, you might be able to find an RPM:
http://www.rpmfind.net/linux/rpm2html/search.php?query=catdoc
... sounds like this might make for a good web service of some kind... I'll
have to see if I can whip something up.
-- jon
-Original Message-
From: daniel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 1:26 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; daniel
Subject: RE: [PHP] excel 2 csv 2 mysql
ok i've built a class to acomplish this now
class Export_Excel {
var $command
function Export_Excel() {
$this-command = /www_tools/apache/catdoc/bin/xls2csv;
}
function xls2csv($path,$xls_file,$short_name) {
$cmd = escapeshellcmd($this-command -q 0 $path.$xls_file);
$cmd .= $path.$short_name.csv;
@exec($cmd,$stdout,$errocode);
unlink($path.$xls_file);
if ($errorcode 0) return $errocode;
}
}
and here is my db class function
function
insert_file($filename,$table,$col_terminator,$line_terminator,$cols=false,$o
pt
ions=false,$remove_file=false) {
if ($options) $option = OPTIONALLY ENCLOSED BY '\' LINES TERMINATED
BY
'\n';
$this-query(LOAD DATA INFILE '$filename' INTO TABLE $table FIELDS
TERMINATED BY '$col_terminator' $option $cols);
if ($remove_files) unlink($filename);
}
its just a quicky, twiddling thumbs today i just had to find out how to do
it
:D
= Original Message From daniel [EMAIL PROTECTED] =
ok it works now
/www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls test2.csv
column1`,column2,column3
faffafs,fsafsa,fsafs
fssfa,fasfs,fasfs
fasaf,fasfs,asffaf
fafs,sfafsa,fasfas
sfafs,fssfa,fassfa
sfasfa,asfafs,fasfas
asffas,sfaaf,sfafsa
fssaf,asffsa,asffas
sfasfa,sfasaf,fasasf
then i can load that into mysql using load data into :D, aparantly there
are
some bugs , gotta find them
= Original Message From daniel [EMAIL PROTECTED] =
ok guys are you ready ,
/www_tools/apache/catdoc/bin/xls2csv test.xls test2.csv
gave me this
column1`,column2,column3
faffafs,fsafsa,fsafs
fssfa,fasfs,fasfs
fasaf,fasfs,asffaf
fafs,sfafsa,fasfas
sfafs,fssfa,fassfa
sfasfa,asfafs,fasfas
asffas,sfaaf,sfafsa
fssaf,asffsa,asffas
sfasfa,sfasaf,fasasf
unfortunately its putting quotes in, have to strip them
= Original Message From [EMAIL PROTECTED] =
Yeah... you're going to have to use COM or something similar on a windows
server to get effective access to the server.
Another option is dynamically adding at as an odbc datasource and
querying
it. Again, this will only work on a windows server...
It's all much easier if you can convince users to upload csv files.
- jon
-
jon roig
senior manager, online production
epilepsy foundation
http://jonroig.com
-Original Message-
From: daniel [mailto:[EMAIL PROTECTED]
Sent: Monday, March 31, 2003 7:48 PM
To: [EMAIL PROTECTED]
Subject: [PHP] excel 2 csv 2 mysql
hi guys i am trying to work out how to dynamically be able to upload an
excel
file , export it to csv to be able to import into mysql , is there any
examples out there ? fopen gave me binary code :|
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
Pretty cool, man... I actually didn't know about xls2csv.
Learn something new every day -- this'll certainly come in handy.
'Course, it's not exactly standard with most distributions. For anyone
looking for it, you can download it here:
http://www.45.free.net/~vitus/ice/catdoc/
Or, depending on your distro, you might be able to find an RPM:
http://www.rpmfind.net/linux/rpm2html/search.php?query=catdoc
... sounds like this might make for a good web service of some kind... I'll
have to see if I can whip something up.
-- jon
-Original Message-
From: daniel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 1:26 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; daniel
Subject: RE: [PHP] excel 2 csv 2 mysql
ok i've built a class to acomplish this now
class Export_Excel {
var $command
function Export_Excel() {
$this-command = /www_tools/apache/catdoc/bin/xls2csv;
}
function xls2csv($path,$xls_file,$short_name) {
$cmd = escapeshellcmd($this-command -q 0 $path.$xls_file);
$cmd .= $path.$short_name.csv;
@exec($cmd,$stdout,$errocode);
unlink($path.$xls_file);
if ($errorcode 0) return $errocode;
}
}
and here is my db class function
function
insert_file($filename,$table,$col_terminator,$line_terminator,$cols=false,$o
pt
ions=false,$remove_file=false) {
if ($options) $option = OPTIONALLY ENCLOSED BY '\' LINES TERMINATED
BY
'\n';
$this-query(LOAD DATA INFILE '$filename' INTO TABLE $table FIELDS
TERMINATED BY '$col_terminator' $option $cols);
if ($remove_files) unlink($filename);
}
its just a quicky, twiddling thumbs today i just had to find out how to do
it
:D
= Original Message From daniel [EMAIL PROTECTED] =
ok it works now
/www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls test2.csv
column1`,column2,column3
faffafs,fsafsa,fsafs
fssfa,fasfs,fasfs
fasaf,fasfs,asffaf
fafs,sfafsa,fasfas
sfafs,fssfa,fassfa
sfasfa,asfafs,fasfas
asffas,sfaaf,sfafsa
fssaf,asffsa,asffas
sfasfa,sfasaf,fasasf
then i can load that into mysql using load data into :D, aparantly there
are
some bugs , gotta find them
= Original Message From daniel [EMAIL PROTECTED] =
ok guys are you ready ,
/www_tools/apache/catdoc/bin/xls2csv test.xls test2.csv
gave me this
column1`,column2,column3
faffafs,fsafsa,fsafs
fssfa,fasfs,fasfs
fasaf,fasfs,asffaf
fafs,sfafsa,fasfas
sfafs,fssfa,fassfa
sfasfa,asfafs,fasfas
asffas,sfaaf,sfafsa
fssaf,asffsa,asffas
sfasfa,sfasaf,fasasf
unfortunately its putting quotes in, have to strip them
= Original Message From [EMAIL PROTECTED] =
Yeah... you're going to have to use COM or something similar on a windows
server to get effective access to the server.
Another option is dynamically adding at as an odbc datasource and
querying
it. Again, this will only work on a windows server...
It's all much easier if you can convince users to upload csv files.
- jon
-
jon roig
senior manager, online production
epilepsy foundation
http://jonroig.com
-Original Message-
From: daniel [mailto:[EMAIL PROTECTED]
Sent: Monday, March 31, 2003 7:48 PM
To: [EMAIL PROTECTED]
Subject: [PHP] excel 2 csv 2 mysql
hi guys i am trying to work out how to dynamically be able to upload an
excel
file , export it to csv to be able to import into mysql , is there any
examples out there ? fopen gave me binary code :|
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re:[PHP] excel 2 csv 2 mysql
Justin, For reading, you could try this: http://www.zakkis.ca/products/abc_excelparser/index.php Cost is 150 CAD - quite steep. Tried the online demo - seems to work. Will only work with cells containing strings though - doesn't resolve formulae. If you purchase it, perhaps you could let me know how you get on? For writing, there seems to be a module in Pear: http://pear.php.net/package-info.php?pacid=156 hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- Geoff Caplan Advantae Ltd http://www.advantae.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel 2 csv 2 mysql
The quotes are valid, use fgetcsv() function, it will strip them daniel wrote: ok guys are you ready , /www_tools/apache/catdoc/bin/xls2csv test.xls test2.csv gave me this column1`,column2,column3 faffafs,fsafsa,fsafs fssfa,fasfs,fasfs fasaf,fasfs,asffaf fafs,sfafsa,fasfas sfafs,fssfa,fassfa sfasfa,asfafs,fasfas asffas,sfaaf,sfafsa fssaf,asffsa,asffas sfasfa,sfasaf,fasasf unfortunately its putting quotes in, have to strip them = Original Message From [EMAIL PROTECTED] = Yeah... you're going to have to use COM or something similar on a windows server to get effective access to the server. Another option is dynamically adding at as an odbc datasource and querying it. Again, this will only work on a windows server... It's all much easier if you can convince users to upload csv files. - jon - jon roig senior manager, online production epilepsy foundation http://jonroig.com -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 7:48 PM To: [EMAIL PROTECTED] Subject: [PHP] excel 2 csv 2 mysql hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel
I've got one here -- http://jonroig.com/modules.php?op=modloadname=Newsfile=articlesid=55 (Watch out, url may wrap wrong) That is, if you're just looking to export data to excel... -- jon -Original Message- From: Evan Nemerson [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 1:47 PM To: Gabi Moise; [EMAIL PROTECTED] Subject: Re: [PHP] Excel -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Not exactly, but http://pear.php.net/package-info.php?pacid=156 On Thursday 27 March 2003 05:47 am, Gabi Moise wrote: Hi, Can someone point me to a php and Excel tutorial? tnx. - -- First they came for the Communists, and I didn't speak up, because I wasn't a Communist. Then they came for the Jews, and I didn't speak up, because I wasn't a Jew. Then they came for the Catholics, and I didn't speak up, because I was a Protestant. Then they came for me, and by that time there was no one left to speak up for me. - -Martin Niemoller -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+g0c3/rncFku1MdIRApbWAKCyeYSh+AxzuBoqz73H7lMTbnBnQwCggdvv ZmDL1xWz1fdxvvrVJM+YLmU= =wm4h -END PGP SIGNATURE- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
Yeah... you're going to have to use COM or something similar on a windows server to get effective access to the server. Another option is dynamically adding at as an odbc datasource and querying it. Again, this will only work on a windows server... It's all much easier if you can convince users to upload csv files. - jon - jon roig senior manager, online production epilepsy foundation http://jonroig.com -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 7:48 PM To: [EMAIL PROTECTED] Subject: [PHP] excel 2 csv 2 mysql hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel 2 csv 2 mysql
on 01/04/03 11:48 AM, daniel ([EMAIL PROTECTED]) wrote: hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| also see fgetscsv() Justin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
http://www.ice.ru/~vitus/catdoc/ anyone seen this ? its pretty much wot i needed, havent used it yet though = Original Message From [EMAIL PROTECTED] = Yeah... you're going to have to use COM or something similar on a windows server to get effective access to the server. Another option is dynamically adding at as an odbc datasource and querying it. Again, this will only work on a windows server... It's all much easier if you can convince users to upload csv files. - jon - jon roig senior manager, online production epilepsy foundation http://jonroig.com -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 7:48 PM To: [EMAIL PROTECTED] Subject: [PHP] excel 2 csv 2 mysql hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
ok guys are you ready , /www_tools/apache/catdoc/bin/xls2csv test.xls test2.csv gave me this column1`,column2,column3 faffafs,fsafsa,fsafs fssfa,fasfs,fasfs fasaf,fasfs,asffaf fafs,sfafsa,fasfas sfafs,fssfa,fassfa sfasfa,asfafs,fasfas asffas,sfaaf,sfafsa fssaf,asffsa,asffas sfasfa,sfasaf,fasasf unfortunately its putting quotes in, have to strip them = Original Message From [EMAIL PROTECTED] = Yeah... you're going to have to use COM or something similar on a windows server to get effective access to the server. Another option is dynamically adding at as an odbc datasource and querying it. Again, this will only work on a windows server... It's all much easier if you can convince users to upload csv files. - jon - jon roig senior manager, online production epilepsy foundation http://jonroig.com -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 7:48 PM To: [EMAIL PROTECTED] Subject: [PHP] excel 2 csv 2 mysql hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
ok it works now /www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls test2.csv column1`,column2,column3 faffafs,fsafsa,fsafs fssfa,fasfs,fasfs fasaf,fasfs,asffaf fafs,sfafsa,fasfas sfafs,fssfa,fassfa sfasfa,asfafs,fasfas asffas,sfaaf,sfafsa fssaf,asffsa,asffas sfasfa,sfasaf,fasasf then i can load that into mysql using load data into :D, aparantly there are some bugs , gotta find them = Original Message From daniel [EMAIL PROTECTED] = ok guys are you ready , /www_tools/apache/catdoc/bin/xls2csv test.xls test2.csv gave me this column1`,column2,column3 faffafs,fsafsa,fsafs fssfa,fasfs,fasfs fasaf,fasfs,asffaf fafs,sfafsa,fasfas sfafs,fssfa,fassfa sfasfa,asfafs,fasfas asffas,sfaaf,sfafsa fssaf,asffsa,asffas sfasfa,sfasaf,fasasf unfortunately its putting quotes in, have to strip them = Original Message From [EMAIL PROTECTED] = Yeah... you're going to have to use COM or something similar on a windows server to get effective access to the server. Another option is dynamically adding at as an odbc datasource and querying it. Again, this will only work on a windows server... It's all much easier if you can convince users to upload csv files. - jon - jon roig senior manager, online production epilepsy foundation http://jonroig.com -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 7:48 PM To: [EMAIL PROTECTED] Subject: [PHP] excel 2 csv 2 mysql hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
ok i've built a class to acomplish this now
class Export_Excel {
var $command
function Export_Excel() {
$this-command = /www_tools/apache/catdoc/bin/xls2csv;
}
function xls2csv($path,$xls_file,$short_name) {
$cmd = escapeshellcmd($this-command -q 0 $path.$xls_file);
$cmd .= $path.$short_name.csv;
@exec($cmd,$stdout,$errocode);
unlink($path.$xls_file);
if ($errorcode 0) return $errocode;
}
}
and here is my db class function
function
insert_file($filename,$table,$col_terminator,$line_terminator,$cols=false,$opt
ions=false,$remove_file=false) {
if ($options) $option = OPTIONALLY ENCLOSED BY '\' LINES TERMINATED
BY
'\n';
$this-query(LOAD DATA INFILE '$filename' INTO TABLE $table FIELDS
TERMINATED BY '$col_terminator' $option $cols);
if ($remove_files) unlink($filename);
}
its just a quicky, twiddling thumbs today i just had to find out how to do it
:D
= Original Message From daniel [EMAIL PROTECTED] =
ok it works now
/www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls test2.csv
column1`,column2,column3
faffafs,fsafsa,fsafs
fssfa,fasfs,fasfs
fasaf,fasfs,asffaf
fafs,sfafsa,fasfas
sfafs,fssfa,fassfa
sfasfa,asfafs,fasfas
asffas,sfaaf,sfafsa
fssaf,asffsa,asffas
sfasfa,sfasaf,fasasf
then i can load that into mysql using load data into :D, aparantly there are
some bugs , gotta find them
= Original Message From daniel [EMAIL PROTECTED] =
ok guys are you ready ,
/www_tools/apache/catdoc/bin/xls2csv test.xls test2.csv
gave me this
column1`,column2,column3
faffafs,fsafsa,fsafs
fssfa,fasfs,fasfs
fasaf,fasfs,asffaf
fafs,sfafsa,fasfas
sfafs,fssfa,fassfa
sfasfa,asfafs,fasfas
asffas,sfaaf,sfafsa
fssaf,asffsa,asffas
sfasfa,sfasaf,fasasf
unfortunately its putting quotes in, have to strip them
= Original Message From [EMAIL PROTECTED] =
Yeah... you're going to have to use COM or something similar on a windows
server to get effective access to the server.
Another option is dynamically adding at as an odbc datasource and querying
it. Again, this will only work on a windows server...
It's all much easier if you can convince users to upload csv files.
- jon
-
jon roig
senior manager, online production
epilepsy foundation
http://jonroig.com
-Original Message-
From: daniel [mailto:[EMAIL PROTECTED]
Sent: Monday, March 31, 2003 7:48 PM
To: [EMAIL PROTECTED]
Subject: [PHP] excel 2 csv 2 mysql
hi guys i am trying to work out how to dynamically be able to upload an
excel
file , export it to csv to be able to import into mysql , is there any
examples out there ? fopen gave me binary code :|
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Not exactly, but http://pear.php.net/package-info.php?pacid=156 On Thursday 27 March 2003 05:47 am, Gabi Moise wrote: Hi, Can someone point me to a php and Excel tutorial? tnx. - -- First they came for the Communists, and I didn't speak up, because I wasn't a Communist. Then they came for the Jews, and I didn't speak up, because I wasn't a Jew. Then they came for the Catholics, and I didn't speak up, because I was a Protestant. Then they came for me, and by that time there was no one left to speak up for me. - -Martin Niemoller -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+g0c3/rncFku1MdIRApbWAKCyeYSh+AxzuBoqz73H7lMTbnBnQwCggdvv ZmDL1xWz1fdxvvrVJM+YLmU= =wm4h -END PGP SIGNATURE- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel to MySQL
You could export, or save as a tab delimited text file, then go ahead and use phpMyAdmin or a custom script to pull all that data into the database. It really shouldn¹t be much of a problem coming from Excel. Cheers! Rick Finish each day and be done with it. You have done what you could; some blunders and absurdities have crept in; forget them as soon as you can. Tomorrow is a new day; you shall begin it serenely and with too high a spirit to be encumbered with your old nonsense. - Ralph Waldo Emerson From: Joe LoMoglio [EMAIL PROTECTED] Date: Fri, 3 Jan 2003 14:20:02 -0700 To: [EMAIL PROTECTED] Subject: [PHP] Excel to MySQL Does anyone know of a FREE utility that will export data from an MS Excel file and import it to MySQL. I have found a bunch of nice utilities, but none of them are FREE. I'm in a bind and have to get over 2000 rows imported to my database by morning and don't have to order one of the other solutions. Joe LoMoglio -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel to MySQL
I think BiffWriter might do it... By why not simply output a comma delimited files from excel and import it in with a small php script. Mike *** REPLY SEPARATOR *** On 03/01/2003 at 2:20 PM Joe LoMoglio wrote: Does anyone know of a FREE utility that will export data from an MS Excel file and import it to MySQL. I have found a bunch of nice utilities, but none of them are FREE. I'm in a bind and have to get over 2000 rows imported to my database by morning and don't have to order one of the other solutions. Joe LoMoglio -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel to MySQL
Thanks Michael and Richard I took your advice and wrote a PHP script to import it. I was having a hard time getting Excel on my Mac to export to CSV and Tab Delim, but finally got it to give me a plain text file which worked fine with fopen(). Joe On Friday, January 3, 2003, at 02:32 PM, Michael J. Pawlowsky wrote: I think BiffWriter might do it... By why not simply output a comma delimited files from excel and import it in with a small php script. Mike *** REPLY SEPARATOR *** On 03/01/2003 at 2:20 PM Joe LoMoglio wrote: Does anyone know of a FREE utility that will export data from an MS Excel file and import it to MySQL. I have found a bunch of nice utilities, but none of them are FREE. I'm in a bind and have to get over 2000 rows imported to my database by morning and don't have to order one of the other solutions. Joe LoMoglio -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel to MySQL
Does anyone know of a FREE utility that will export data from an MS Excel file and import it to MySQL. I have found a bunch of nice utilities, but none of them are FREE. I'm in a bind and have to get over 2000 rows imported to my database by morning and don't have to order one of the other solutions. Maybe you can use this: Save the Excel document as tab delimited text and use the mysql command LOAD DATA or the mysqlimport utility. mysql LOAD DATA INFILE 'data.txt' INTO TABLE table; http://www.mysql.com/doc/en/LOAD_DATA.html You'd better check out the stuff about enclosed by. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP Excel
Have a look at my post of 17/05/2002, I got a lot of help from there. Bye, Evan Jean-Louis Letortorec [EMAIL PROTECTED] ha scritto nel messaggio 615315231286D21182E40008C7B1EED241EDF7@COMPAQ3000">news:615315231286D21182E40008C7B1EED241EDF7@COMPAQ3000... Hello every one: I'm trying to create XLS files on the fly. I found BiffWriter. Looks great for adding texts and numbers, but not formulas. Has anyone implemented something about formulas? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel and PHP in Unix
hi, i just wanted to put in a plug for my own code to generate dynamic Excel or Word files here. it's ultra easy to use: http://sourceforge.net/projects/php-doc-xls-gen/ thanks, tom Pavel Jartsev wrote: Iván Toda Caraballo wrote: Hello, I am using the Unix version of PHP, and i would write some information from the HTML in a Excel file. I was made this using COM functions in a Windows version of PHP and it works. But COM functions are only available on the Windows version of PHP. I want to know how I can write a Excel file but using a Unix version of PHP. Thanks! Maybe this helps a little: http://sourceforge.net/projects/psxlsgen/ -- Pavel a.k.a. Papi -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Excel to MySQL
I use to save my access data to a dbf file and then use the dbf2mysql (see www.mysql.com site). dbf2mysql will create proper tables and export data to the mysql really faster than odbc. Very easy. I have exported someting like 5.000.000 records into 8 differents tables in 4 hours (3 cdroms of access data, 1 cd of dbf data :) And no need to create the table into mysql (this could be long if the table is big). Byez Daniel Harik [EMAIL PROTECTED] ha scritto nel messaggio [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Guys getting more serious here now, 3 500 000 records, foxPro i'll try Jeff's method -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Excel to MySQL
At 04:11 PM 10/27/01 -0800, Daniel Harik wrote: You see i have huge(for me) 100 000 record access table, i wanted to convert it to mysql, i thought of making php convertor that uses odbc and mysql, but maybe there is faster way? Daniel, You're making it too hard on yourself. 1. CREATE TABLES in MySQL that match up to your Access tables. 2. In MICROSOFT ACCESS, go to the Export... command (I believe it's under the File menu.) 3. You'll save it as a .txt file with NO TEXT QUALIFIERS, TAB DELIMITED, without having column names on the first row. 4. FTP the .txt file to your server. I always use ASCII mode for it because I remember one time having some problem which I think was due to Binary transfer (I could've been wrong about that, though). 5. Go into MySQL an use the LOAD DATA INFILE command. ie: LOAD DATA INFILE '/home/virtual/yoursite/home/html/filename.txt' REPLACE INTO tablename; The first few times it can be kind of sticky, but after you do it a couple fo times, it will be as easy as breathing. There is 1 compatibility issue that I ran into. If a field in MS ACCESS has data on 2 lines For instance... ++---+ | ID | Address | ++---+ | 1 | 123 Evergreen Terrace | -- This is GOOD ++---+ | 2 | 321 Pine Street | -- This is BAD || Apartment A | ++---+ You see, when MySQL runs the LOAD DATA, new records are indicated by a NEW LINE. You'll want to check your ACCESS table for new lines before creating the export file. Later, Jeff Gannaway ___ Save 15% on 2002 Calendars and Holiday Cards! http://www.AvantGifts.com Discount Code: hopper22ct Offer Good Through October 31, 2001 ___ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Excel to MySQL
Another idea that may work... and I say may because I have never done it myself. Is to use MyODBC drivers and do the import code native to Access. I would be interested to hear if others have done this. Gerard -Original Message- From: Jeff Gannaway [mailto:[EMAIL PROTECTED]] Sent: Saturday, October 27, 2001 10:30 AM To: Daniel Harik; [EMAIL PROTECTED] Subject: Re: [PHP] Excel to MySQL At 04:11 PM 10/27/01 -0800, Daniel Harik wrote: You see i have huge(for me) 100 000 record access table, i wanted to convert it to mysql, i thought of making php convertor that uses odbc and mysql, but maybe there is faster way? Daniel, You're making it too hard on yourself. 1. CREATE TABLES in MySQL that match up to your Access tables. 2. In MICROSOFT ACCESS, go to the Export... command (I believe it's under the File menu.) 3. You'll save it as a .txt file with NO TEXT QUALIFIERS, TAB DELIMITED, without having column names on the first row. 4. FTP the .txt file to your server. I always use ASCII mode for it because I remember one time having some problem which I think was due to Binary transfer (I could've been wrong about that, though). 5. Go into MySQL an use the LOAD DATA INFILE command. ie: LOAD DATA INFILE '/home/virtual/yoursite/home/html/filename.txt' REPLACE INTO tablename; The first few times it can be kind of sticky, but after you do it a couple fo times, it will be as easy as breathing. There is 1 compatibility issue that I ran into. If a field in MS ACCESS has data on 2 lines For instance... ++---+ | ID | Address | ++---+ | 1 | 123 Evergreen Terrace | -- This is GOOD ++---+ | 2 | 321 Pine Street | -- This is BAD || Apartment A | ++---+ You see, when MySQL runs the LOAD DATA, new records are indicated by a NEW LINE. You'll want to check your ACCESS table for new lines before creating the export file. Later, Jeff Gannaway ___ Save 15% on 2002 Calendars and Holiday Cards! http://www.AvantGifts.com Discount Code: hopper22ct Offer Good Through October 31, 2001 ___ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Excel to MySQL
Yet another good way (if you have Windows) is to grab a copy of the MSDE (comes with most distros of Office, on the CD.. you'll hafta search for it), which is essentially a stripped down version of Microsoft SQL Server. Install that, and on your start menu somewhere (it'll say like MSDE or something), there will be the Import and Export Wizard. It supports tonnes of database formats, and allows you to map fields to other fields. Use that in conjunction with a MySQL ODBC driver, and you're cooking with gravy. Mike Gerard Onorato wrote: Another idea that may work... and I say may because I have never done it myself. Is to use MyODBC drivers and do the import code native to Access. I would be interested to hear if others have done this. Gerard -Original Message- From: Jeff Gannaway [mailto:[EMAIL PROTECTED]] Sent: Saturday, October 27, 2001 10:30 AM To: Daniel Harik; [EMAIL PROTECTED] Subject: Re: [PHP] Excel to MySQL At 04:11 PM 10/27/01 -0800, Daniel Harik wrote: You see i have huge(for me) 100 000 record access table, i wanted to convert it to mysql, i thought of making php convertor that uses odbc and mysql, but maybe there is faster way? Daniel, You're making it too hard on yourself. 1. CREATE TABLES in MySQL that match up to your Access tables. 2. In MICROSOFT ACCESS, go to the Export... command (I believe it's under the File menu.) 3. You'll save it as a .txt file with NO TEXT QUALIFIERS, TAB DELIMITED, without having column names on the first row. 4. FTP the .txt file to your server. I always use ASCII mode for it because I remember one time having some problem which I think was due to Binary transfer (I could've been wrong about that, though). 5. Go into MySQL an use the LOAD DATA INFILE command. ie: LOAD DATA INFILE '/home/virtual/yoursite/home/html/filename.txt' REPLACE INTO tablename; The first few times it can be kind of sticky, but after you do it a couple fo times, it will be as easy as breathing. There is 1 compatibility issue that I ran into. If a field in MS ACCESS has data on 2 lines For instance... ++---+ | ID | Address | ++---+ | 1 | 123 Evergreen Terrace | -- This is GOOD ++---+ | 2 | 321 Pine Street | -- This is BAD || Apartment A | ++---+ You see, when MySQL runs the LOAD DATA, new records are indicated by a NEW LINE. You'll want to check your ACCESS table for new lines before creating the export file. Later, Jeff Gannaway ___ Save 15% on 2002 Calendars and Holiday Cards! http://www.AvantGifts.com Discount Code: hopper22ct Offer Good Through October 31, 2001 ___ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Excel to MySQL
I used Jeff's method http://adsl20066.estpak.ee/emt here is it working live Thank You Jeff so much -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Excel to MySQL
On Saturday 27 October 2001 17:11, Daniel Harik wrote: You see i have huge(for me) 100 000 record access table, i wanted to convert it to mysql, i thought of making php convertor that uses odbc and mysql, but maybe there is faster way? Your subject says excel but above, you say access. If, in fact, you want to convert from access to mySQL, then forget about importing/exporting. Just grab this access module: http://www.rot13.org/~dpavlin/projects/sql/exportSQL3.txt It exports a native mySQL script that creates all your tables, indexes, primary keys and then imports your data. It absolutely kicks butt. I've used it several times and have never had a problem with it. It's a very slick solution. --kurt -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Excel to MySQL
Guys getting more serious here now, 3 500 000 records, foxPro i'll try Jeff's method -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Excel
Probably not exactly the answer that you're looking for, but you can do the following. Output your data in PHP to a webpage that consists of purely a table of your data. Open Excel, go to : Data-Get External Data-New Web Query Type in the URL for the page with your data. Select which table you want to display (by name or number) Click OK, and hey presto! This spreadsheet can be saved, and anyone opening it can see the current results of the data from the webpage. -Original Message- From: Ron [mailto:[EMAIL PROTECTED]] Sent: 24 May 2001 15:51 To: [EMAIL PROTECTED] Subject: [PHP] Excel I was looking for a way to build a spreadsheet (Excel) dynamically with data from a table, and I was curious about doing it in PHP .. I prefer to use PHP, but I know in ASP it can be done. If I'm running PHP on Linux (can't use the COM functions), is there a way this can be done? Preferably starting with something like header(application/ms-excel...) or whatever the mime is - dunno off the top of my head, and just writing data? If there's no way to do this directly is it possible that by starting with this header and then sending a csv (comma separated) text page that excel will read it as a spreadsheet? Any help would be appreciated.. -Ron - [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Excel not opened in browser when changing header
I tried using the code below to send the output to an Excel sheet in my
browser by changing the header to "Content-type:
application/vnd.ms-excel".
However I only see normal text in a browser. Excel is not launched. What
is
wrong??
--
?php
Header ("Content-type: application/ms-excel");
Header ("Content-type: application/vnd.ms-excel");
Two different Content-type: headers? That seems pretty broken to me...
But I could be just too narrow-minded.
Try uploading an .xls file, and surfing to it to be sure you get the results
you want.
Then use:
telnet yourhost.com 80
GET /whatever.xls HTTP/1.0
(hit return twice)
from a Un*x box to see what headers are being sent that make it work.
(Or find a way to make Windows give you an actual command-line telnet
session without launching a GUI... G.)
Send all those headers.
--
Visit the Zend Store at http://www.zend.com/store/
Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
