Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 результаты sql запроса в Exсel.  [new]
незнайка....
Guest
доброго времени суток! не подскажете каким образом выводить результаты sql запроса в Exсel.
на данный момент делаю так:
.......
spool report1.csv
select 
a ||';'|| 
00121 ||';'|| 
from dual
/
spool off
disconnect
quit
Есть 1 недостаток- необходимо задавать тип полей (при открывании csv в Exсel-е происходит автоматическое преобразование данных: из строки '0123' получаем 123).
Буду признателен за помошь.
16 сен 08, 13:20    [6190034]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
maxxstorm
Member

Откуда: Москва
Сообщений: 385
Я вывожу с помощью проги TalendOpenStudio - очень рекомендую, там много всяких интерфейсов, в том числе и для оракла и экселя.
16 сен 08, 13:22    [6190046]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
незнайка....
Guest
maxxstorm
У меня необходимость отправлять отчеты по расписанию.
стоит шедулер, который запускает батник, вызывающий Sql скрип, далее результаты данного скрипта (текстовый файл) автоматом отправляются по почте.
16 сен 08, 13:26    [6190073]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
maxxstorm
Member

Откуда: Москва
Сообщений: 385
Формат Экселя это не текстовый файл, поэтому тебе нужен конвертер. TOS является таким. Он генерирует код из разных модулей(оракл->excel->mail) на perl или java. Потом его просто выполняешь по расписанию.
16 сен 08, 13:29    [6190099]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
незнайка....
Guest
а средствами sql создать документ Exсel возможно?
16 сен 08, 13:49    [6190279]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
maxxstorm
Member

Откуда: Москва
Сообщений: 385
Можно, для этого надо знать формат Excel.
16 сен 08, 13:52    [6190316]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
ХреноРедька
Member

Откуда: из огорода
Сообщений: 825
Есть внутренний формат excel-файлов в виде xml.
Если сможете сформировать xml с нужной структурой, то считайте что можете формировать и excel-файлы.
16 сен 08, 14:03    [6190392]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
незнайка....
Guest
ХреноРедька
примерно в этом направлении и копаю. осталось только найти формать xml файла для Exсel-я.
16 сен 08, 14:08    [6190438]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
maxxstorm
Можно, для этого надо знать формат Excel.

строго говоря - не обязательно..
(я уж молчу, что формат Excel - это не фига не формат, а поколение форматов ;)

есть пути:
1) юзать (кто умеет ;) комовское апи m$офиса
2) текстовые форматы xml,csv,html,rtf - которые коммуникационный смысл и имеют
3) =1+2
16 сен 08, 14:12    [6190466]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
незнайка....
Guest
orawish
создаю текст в формате csv, но сталкиваюсь с тем что идет автоматическое преобразование из строки в число, там где это мне ненужно! поэтому хотелось бы узнать текстовые форматы других форматов (xml,html,rtf ), которые могут быть открыты экселем и в которых возможно задавать тип данных для ячеек!
16 сен 08, 14:15    [6190501]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
Denis Popov
Member

Откуда: Санкт-Петербург
Сообщений: 7862

незнайка.... wrote:

> ХреноРедька
> примерно в этом направлении и копаю. осталось только найти формать xml
> файла для Exсel-я.

В принципе Excel умеет открывать файл html-формата без каких-то дополнительных вопросов, если
поменять ему расширение на xls.

Posted via ActualForum NNTP Server 1.4

16 сен 08, 14:15    [6190503]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
ХреноРедька
Member

Откуда: из огорода
Сообщений: 825
незнайка....
ХреноРедька
примерно в этом направлении и копаю. осталось только найти формать xml файла для Exсel-я.


Я делал так: в самом excele рисуем все что надо вместе с какими-то тестовыми данными. Потом сохраняем файл через Save As и указываем тип файла "четатам (*.xml)" (делал это в 2003 офисе). Все. Дальше открываем этот файл хоть в блокноте и смотрим что получилось. Сам формат простой и наглядный. По крайней мере заполнить строки данными не было никаких проблем.
Просто нам также нужно было формировать на App Servere exceleвские отчеты, НО без участия самого приложения "Excel". Все получилось.
16 сен 08, 14:16    [6190511]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
ХреноРедька
Member

Откуда: из огорода
Сообщений: 825
незнайка....
orawish
создаю текст в формате csv, но сталкиваюсь с тем что идет автоматическое преобразование из строки в число, там где это мне ненужно! поэтому хотелось бы узнать текстовые форматы других форматов (xml,html,rtf ), которые могут быть открыты экселем и в которых возможно задавать тип данных для ячеек!


строки данных там идут так:
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ss:ExpandedRowCount="тут количество всех строк">
...
<Row ss:AutoFitHeight="0" ss:Height="42">
    <Cell ss:StyleID="s25">
        <Data ss:Type="String">Это будут текстовые данные</Data>
    </Cell>
    <Cell ss:StyleID="s25">
        <Data ss:Type="Number">А тут должны быть числовые</Data>
    </Cell>
</Row>
дальше пошли другие строки
<Row>
   ...
</Row>
...
16 сен 08, 14:23    [6190540]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
незнайка....
orawish
создаю текст в формате csv, но сталкиваюсь с тем что идет автоматическое преобразование из строки в число, там где это мне ненужно! поэтому хотелось бы узнать текстовые форматы других форматов (xml,html,rtf ), которые могут быть открыты экселем и в которых возможно задавать тип данных для ячеек!

..еще на формат sylk посмотрите
16 сен 08, 14:42    [6190726]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
AndreyArtemev
Member

Откуда:
Сообщений: 777
Если твоя проблемка только в получении строки "0123" пиши так
select ''''||'0123' from dual
Тогда excel будет оставлять строку "как есть"
16 сен 08, 14:51    [6190813]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
незнайка....
Guest
AndreyArtemev
Да, кончно это выход - сам думал - но не очень хочется вставлять постороние символы в значения.
16 сен 08, 15:28    [6191117]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
Курильщик
Member

Откуда:
Сообщений: 445
я воспользовался библиотекой POI + написал на java обертку для создания файлов xls сам файл отдается мне в виде блоба. если интересно то могу рассказать по подробнее, кратко изложено в местном faq.
16 сен 08, 15:42    [6191237]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
Zloxa
Member

Откуда: СССР ☭
Сообщений: 1033
AndreyArtemev
пиши так
select ''''||'0123' from dual

Пробовал, однака. Для csv не проканало, открытую кавычку, для csv, эксель воспринимает не как управляющий символ, а как данные.
16 сен 08, 15:57    [6191347]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
terrymoon
Member

Откуда:
Сообщений: 315
незнайка....
доброго времени суток! не подскажете каким образом выводить результаты sql запроса в Exсel.
на данный момент делаю так:
.......
spool report1.csv
select 
a ||';'|| 
00121 ||';'|| 
from dual
/
spool off
disconnect
quit
Есть 1 недостаток- необходимо задавать тип полей (при открывании csv в Exсel-е происходит автоматическое преобразование данных: из строки '0123' получаем 123).
Буду признателен за помошь.

set feed off markup html ON spool on ENTMAP OFF;
set pagesize 2000;
SET LONG 9000;
set lines 9000;
spool report1.xls;
...
select a, 00121 from dual;
...
set markup html off spool off ;
exit;
В экселе отлично откроется. Если все же понадобится дополнительное форматирование ячеек - обрабатывайте макросами.
16 сен 08, 16:56    [6191824]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
terrymoon
Member

Откуда:
Сообщений: 315
автор
необходимо задавать тип полей (при открывании csv в Exсel-е происходит автоматическое преобразование данных: из строки '0123' получаем 123).

тип полей в экселе практически в любом решении придется править.
Так что макросы вас спасут)
16 сен 08, 17:00    [6191847]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
alsov
Member

Откуда: Санкт-Петербург
Сообщений: 153
terrymoon
Так что макросы вас спасут)


Тогда и выгружать данные можно макросами.
16 сен 08, 17:08    [6191896]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
terrymoon
Member

Откуда:
Сообщений: 315
alsov
terrymoon
Так что макросы вас спасут)


Тогда и выгружать данные можно макросами.

через ODBC?
16 сен 08, 17:20    [6191960]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
alsov
Member

Откуда: Санкт-Петербург
Сообщений: 153
terrymoon
alsov
terrymoon
Так что макросы вас спасут)


Тогда и выгружать данные можно макросами.

через ODBC?


можно и через ADO :)
16 сен 08, 17:34    [6192062]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
terrymoon
Member

Откуда:
Сообщений: 315
alsov
terrymoon
alsov
terrymoon
Так что макросы вас спасут)


Тогда и выгружать данные можно макросами.

через ODBC?


можно и через ADO :)


Можно. Но вот допустим у нас не один отчетик, а 10 разных.
С разной агрегацией данных, да еще надо перед этим пару хранимых процедур вызвать и обновить пару MVIEW.
Итого выгрузка макросами превратится в аццкий VBA скриптинг.)

А если через sqlplus, то на долю макроса выпадет только форматирование колонок, да рисование графиков, что можно сделать и в графическом интерфейсе (записать макрос через графику).
16 сен 08, 17:46    [6192151]     Ответить | Цитировать Сообщить модератору
 Re: результаты sql запроса в Exсel.  [new]
Oleg M.Ivanov
Member

Откуда: Москва
Сообщений: 1164
Делается очень просто на PHP. Причем файл получается формата xls 4-го екселя.
Если умельцам не влом, то данный класс можно и на PL/SQL переложить.
Файл toExcell.php :
<?php
 class  PhpToExcell {
    var  $data   = "";          // данные структуры

    // формирования заголовка открытия
    function  ExBOF()
     {
       // begin of the excel file header
       $this->data = pack("c*", 0x09, 0x00, 0x04, 0x00, 0x02, 0x00, 0x10, 0x0);
     }
     
    // формирования заголовка закрытия
    function  ExEOF()
     {
     $this->data .= pack("cc", 0x0A, 0x00);
     }
     
    // Переводит в строку attr1..3
    function RowAttr($attr1,$attr2,$attr3)
    {
     return chr($attr1).chr($attr2).chr($attr3);
    }

    // устанавливает русский язык в структура xls
    function Rus()
    {
     $this->data .= (chr(0x42).chr(0x00).chr(0x02).chr(0x00).chr(0x01).chr(0x80));
    }

    // запись строки
    // Col,Row - колонка и строка
    // attr1 - атрибут показа ячейки и защита от записи
    // attr2 - размер шрифта
    // attr3 - обрамление ячейки
    // для формирования атрибутов используйте соотвествующие функции
    function WriteLabel($Col,$Row,$attr1=0,$attr2=0,$attr3=0,$value) // { Запись String }
    {
     $i=strlen($value);
     $this->data .= pack("v*",0x04,8+$i,$Col,$Row);
     $this->data .= $this->RowAttr($attr1,$attr2,$attr3);
     $this->data .= pack("c",$i);
     $this->data .= $value;
    }

    // установка ширины колонки  Width*1/256
    //                           3000 - 100% }
    function ColWidth($ColFirst,$ColLast,$Width)
    {
      $this->data .= (CHR(0x24).CHR(00).Chr(04).CHR(00).chr($ColFirst).chr($ColLast).pack('s',$Width));
    }

    // Управляет видом колонок и строк при
    // ReferenceMode=1 Стиль ссылок = R1C1
    // ReferenceMode=0 Стиль ссылок стандартный A1...
    function RefMode($ReferenceMode=1)
    {
     if ($ReferenceMode==1)
     {
      $this->data .= (CHR(0x0f).chr(0x00).chr(2).chr(0x00).chr(0x00).chr(0x00));
     } else
      {
       $this->data .= (CHR(0x0f).chr(0x00).chr(2).chr(0x00).chr(0x00).chr(0x01));
      }
    }

    // запись целого числа
    function WriteInteger($Col,$Row,$attr1,$attr2,$attr3,$value=0)
    {
     $this->data.=pack("v*",0x02,0x09,$Col,$Row);
     $this->data.=$this->RowAttr($attr1,$attr2,$attr3);
     $this->data.=pack("v",$value);
    }

    // запись дробного числа
    function WriteNumber($Col,$Row,$attr1,$attr2,$attr3,$value=0.00)
    {
     $this->data.=pack("v*",0x03,0x0F,$Col,$Row);
     $this->data.=$this->RowAttr($attr1,$attr2,$attr3);
     $this->data.=pack("d",$value);

    }
    // запись пустой ячейки
    function WriteBlank($Col,$Row,$attr1,$attr2,$attr3)
    {
     $this->data.=pack("v*",0x01,0x07,$Col,$Row);
     $this->data.=$this->RowAttr($attr1,$attr2,$attr3);
    }

    // Установка шрифта. Height*1/20
    //      Для 10 пунктов Height = 200   }
    function Font($Height,$Bold=0,$Italic=0,$Underline=0,$StrikeOut=0,$FontName)
    {
     $i=strlen($FontName);
     $this->data.=CHR(0x31).chr(0x00).Chr($i+5).chr(0x00);
     $this->data.=pack("v",$Height);
     $k=0;
     if ($Bold==1) $k=$k|1;
     if ($Italic==1) $k=$k|2;
     if ($Underline==1) $k=$k|4;
     if ($StrikeOut==1) $k=$k|8;
     $this->data.=pack("v",$k);
     $this->data.=chr($i);
     $this->data.=$FontName;
    }

    // Формируем аттрибут №1 ($Attr1)
    // $CellHidden - скрыть формулы
    // $CellLocked - защищенная ячейка
    function Attr1($CellHidden=0,$CellLocked=0)
    {
     $r=0x0;
     if ($CellHidden==1)$r=$r|128;
     if ($CellLocked==1)$r=$r|64;
     return $r;
    }
    
    // Формируем аттрибут №2 ($Attr2)
    // $FontNumber 0..3
    function Attr2($FontNumber=0)
    {
     $r=0;
     switch ($FontNumber)
     {
      case 1:
            $r=64;
            break;
      case 2:
            $r=128;
            break;
      case 3:
            $r=129;
            break;
      }
      return $r;
    }
    
    // Формируем аттрибут №3 ($Attr3)
    // Alignment  0 - General
    //            1 - left
    //            2 - center
    //            3 - Right
    //            4 - Fill
    function Attr3($Shaded=0,$BottomBorder=0,$TopBorder=0,$RightBorder=0,$LeftBorder=0,$Alignment=0)
    {
     $r=0;
     if ($Shaded==1) $r=$r|128;
     if ($BottomBorder==1) $r=$r|64;
     if ($TopBorder==1) $r=$r|32;
     if ($RightBorder==1) $r=$r|16;
     if ($LeftBorder==1) $r= $r|8;
     if ($Alignment<8) $r=$r|$Alignment;
     return $r;
    }
    
    // сохранение данных в xls файл
    function SaveToFileXls($FName='file.xls')
    {
     $fp = fopen( $FName, "wb" );
     fwrite( $fp,$this->data);
     fclose( $fp );
    }
    
    // показать структуру в web
    function SendFileToHTTP($FName='file.xls')
    {
     header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
     header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
     header ( "Cache-Control: no-cache, must-revalidate" );
     header ( "Pragma: no-cache" );
     header ( "Content-type: application/x-msexcel" );
     header ( "Content-Disposition: attachment; filename=".$FName );
     print $this->data;
    }
  }

?>

Запускается отчет по крону и шлет письмо нужному манагеру.
(реальные селекты естественно убраны, изобретайте сами) ;-)

<?
$orauser="ORACLE_SID";
$orapass="USER";
$oraname="PASSWORD";


function XMail( $from, $to, $subj, $text, $filename)
{
$f         = fopen($filename,"rb");
$un        = strtoupper(uniqid(time()));
$head      = "From: $from\n";
$head     .= "To: $to\n";
$head     .= "Subject: $subj\n";
$head     .= "X-Mailer: PHPMail Tool\n";
$head     .= "Reply-To: $from\n";
$head     .= "Mime-Version: 1.0\n";
$head     .= "Content-Type:multipart/mixed;";
$head     .= "boundary=\"----------".$un."\"\n\n";
$zag       = "------------".$un."\nContent-Type:text/html;\n";
$zag      .= "Content-Transfer-Encoding: 8bit\n\n$text\n\n";
$zag      .= "------------".$un."\n";
$zag      .= "Content-Type: application/octet-stream;";
$zag      .= "name=\"".basename($filename)."\"\n";
$zag      .= "Content-Transfer-Encoding:base64\n";
$zag      .= "Content-Disposition:attachment;";
$zag      .= "filename=\"".basename($filename)."\"\n\n";
$zag      .= chunk_split(base64_encode(fread($f,filesize($filename))))."\n";

if (!@mail("$to", "$subj", $zag, $head))
 return 0;
else
 return 1;
}


if(!$fp = fopen ("/tmp/otchet_diler_excel.php", "w+")) {die("can't open file for append...");}
flock($fp,2);

$text='<?php
include( "/root/scripts/toExcell.php" );
$xls = new PhpToExcell();
$xls->ExBOF();
$xls->Rus();
$xls->RefMode(0);
';

$ii=0;


if ( ! $conn=ocilogon($orauser,$orapass,$oraname)  )   { $err = OCIError(); print "Сервер временно не доступен.<br>Попробуйте зайти позже.<br>";  die(); } 


$sql="select '25.5064' from dual";
$stmt = OCIParse($conn,$sql);
 OCIExecute($stmt);
if ( OCIFetch($stmt) ) {
 $cource=OCIResult($stmt,1);
 $data=OCIResult($stmt,2);
 }
 OCIFreeStatement($stmt);

$text=$text.'$xls->WriteLabel('.$ii.',0,0,0,0,"Курс:");
$xls->WriteNumber('.$ii.',1,0,0,0,"$cource");
$xls->WriteLabel('.$ii.',2,0,0,0,"");
$xls->WriteLabel('.$ii.',3,0,0,0,"Отчет за:");
$xls->WriteLabel('.$ii.',4,0,0,0,"$data");
$xls->WriteLabel('.$ii.',5,0,0,0,"");
';

$ii++;
$ii++;

$sql='select '9999.99' as "Время(мин)", '88888.88' as "Сумма", 'p' as "Валюта" , '120' as "Код диллера",'Новый' as "Диллер", '2008-09' as month from dual
union all
select '9999.99' as "Время(мин)", '88888.88' as "Сумма", 'p' as "Валюта" , '120' as "Код диллера",'Новый' as "Диллер", '2008-09' as month from dual
union all
select '9999.99' as "Время(мин)", '88888.88' as "Сумма", 'p' as "Валюта" , '120' as "Код диллера",'Новый' as "Диллер", '2008-09' as month from dual
union all
select '9999.99' as "Время(мин)", '88888.88' as "Сумма", 'p' as "Валюта" , '120' as "Код диллера",'Новый' as "Диллер", '2008-09' as month from dual';

$stmt = OCIParse($conn,$sql);
OCIExecute($stmt);

$text=$text.'$xls->WriteLabel('.$ii.',0,0,0,0,"Время(мин)");
$xls->WriteLabel('.$ii.',1,0,0,0,"Сумма");
$xls->WriteLabel('.$ii.',2,0,0,0,"Валюта");
$xls->WriteLabel('.$ii.',3,0,0,0,"Сумма(руб)");
$xls->WriteLabel('.$ii.',4,0,0,0,"Код диллера");
$xls->WriteLabel('.$ii.',5,0,0,0,"Диллер");
';
fwrite($fp,$text);
unset($text);
$summarur=0;
$summarurall=0;
$summinall=0;
while ( OCIFetch($stmt) ) {
$ii++;
$summa=OCIResult($stmt,2);
$valute=OCIResult($stmt,3);
if ($valute == '$') {$summarur=$summa*$cource;} else {$summarur=$summa;}
$minutes=OCIResult($stmt,1);
$summinall+=$minutes;
$summarur=round($summarur,4);
$summarurall+=$summarur;

$text=$text.'$xls->WriteNumber('.$ii.',0,0,0,0,"'.$minutes.'");
$xls->WriteNumber('.$ii.',1,0,0,0,"'.$summa.'");
$xls->WriteLabel('.$ii.',2,0,0,0,"'.$valute.'");
$xls->WriteNumber('.$ii.',3,0,0,0,"'.$summarur.'");
$xls->WriteNumber('.$ii.',4,0,0,0,"'.OCIResult($stmt,4).'");
$xls->WriteLabel('.$ii.',5,0,0,0,"'.OCIResult($stmt,5).'");
';
fwrite($fp,$text);
unset($text);
$month=OCIResult($stmt,6);
} // while
OCIFreeStatement($stmt);
OCILogoff($conn);
$ii++;
$text=$text.'$xls->WriteNumber('.$ii.',0,0,0,0,"'.$summinall.'");
$xls->WriteLabel('.$ii.',1,0,0,0,"");
$xls->WriteLabel('.$ii.',2,0,0,0,"");
$xls->WriteNumber('.$ii.',3,0,0,0,"'.$summarurall.'");
$xls->WriteLabel('.$ii.',4,0,0,0,"");
$xls->WriteLabel('.$ii.',5,0,0,0,"");
';
fwrite($fp,$text);
unset($text);

$filename='/tmp/othet_diller_'.$month.'.xls';



$text='
$xls->ExEOF();
$xls->SaveToFileXls($filename);
?>
';
fwrite ($fp, $text);
fclose ($fp);
include( "/tmp/otchet_diler_excel.php" );

$from='root@youserver.ru';
$to='manager@youdomain.ru';
$subj='Отчет по дилерам за '.$month;
$text='Отчет по дилерам за '.$month;

XMail( $from, $to, $subj, $text, $filename);

?>
16 сен 08, 18:07    [6192309]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить