Препарирование файлов .XLSX: строковые значения, разметка ячеек

[NeverIn]

Для генерации простого документа использую только инлайн хранение строк.
Использовать словарь вхождения слова со счетчиком показалось достаточно проблемным с точки зрения сложности формата.

[Azy]

А как записываются формулы?

[Lachrimae]

Поэкспериментировал. С формулами оказалось интересно.
Формулы хранятся в виде ключевого слова в файлах разметки листов, внутри тега ячейки. Выглядит это как-то так:

<c><f>SUM(A1:C1)</f><v>6</v></c>

Но как видно, при этом результат все равно записывается сразу за ним.
Если его вручную удалить, то файл откроется, но будет считаться заранее отредактированным. То бишь если его открыть и сразу закрыть, Excel предложит сохраниться.

[Alex57]

Не очень понятно зачем shared строковые данные в формате, который ОБЯЗАТЕЛЬНО затем собирается архиватором ZIP. ZIP отлично сожмет текст, который повторяется несколько раз.
Могу только предположить что формат (кусок про shared) придумали и утвердили чуть раньше чем решили зиповать…

[Lachrimae]

ZIP-то. может, и сожмет, но разжимать-то потом Excel-ю. И, как следствие, на экран выводить ему же. Думаю, дело именно в этом.

[Alex57]

думаю что Excel хранит данные «в ОЗУ» в том же формате что и раньше (95, 97,2000,XP, 2003), приближенному к тому бинарному виду что потом на диске в формате xls.
И еще ему нужно очень быстро это находить и обсчитывать, и тут проще с распакованным работать :)

Для верности я провел опыт (Excel 2016, 64bit, все апдейты установлены, на компе 8 ГБ ОЗУ).
Открыл Excel, вбил в ячейку A1 число 1. Скопировал на всю строку. Размер занятой памяти с 45 мегабайт поднялся до 48.
Потом попросил всю ПЕРВУЮ строку скопироваться на все строки (миллион). Excel ОТКАЗАЛСЯ, потому что ему не хватит ОЗУ. Если бы он хранил это в компрессированом виде, то проблема была бы только хранить 1 млн. вхождений ОДИНАКОВОЙ строки.
Однако в том же файле скопировать цифру 2 млн раз (на весь столбец) — вполне удалось. (занято 122 МБ).Т.е. в ОЗУ он хранит все таки распакованный вид, иначе ему что хранить ссылку на 2, что хранить ссылку на целый ряд «как первый» было примерно одинаково.

[Lachrimae]

Получается, так. Значит, причина кроется скорее в скорострельности при открытии файла. Числа «1» и «2» — это замечательно, но памяти требует значительно меньше, чем строка из хотя бы 4 символов.

[Alex57]

ок :) Сделал «very long string», скопировал на миллион раз. Нормально. Сделал "=rand()" и еще миллион копий — нормально. Миллион формул и значений с большой дробной частью. (Памяти заняло примерно 120 мегов ОЗУ, в .xlsx 32 мега, в .xlsb — 15 мегов, в формате .xls сохранять отказался, ибо в нем все свыше 65 тыс строк отрежется…

[Lachrimae]

Тогда Ich kapitulieren.
И возникает вопрос, почему у нашей бухгалтерии начинаются адские тормоза при попытке сделать файл хотя бы в 10000 строк.

[Alex57]

Спасибо за статью, стал экспериментировать.

Пару выводов:
1) Для r и c не обязательно указывать полный адрес (r=«1», r=«A3» и т.п.)
Такой код:

<sheetData>
  <row><c t="inlineStr"><is><t>Paris</t></is></c></row>
  <row><c t="inlineStr"><is><t>Seattle</t></is></c></row>
  <row><c t="inlineStr"><is><t>London</t></is></c></row>
  <row><c t="inlineStr"><is><t>Copenhagen</t></is></c></row>
  <row><c t="inlineStr"><is><t>Paris</t></is></c></row>
  <row><c t="inlineStr"><is><t>London</t></is></c></row>
</sheetData>

вполне себе нормально делает Excel-файл из 6 строк.

2) про inline строки — их вполне можно вставлять :) Правда хватает на один раз, при сохранении этого файла Excel-ем, он сконвертнет все в sharedstring. Но для вывода из какой-нибудь другой системы это реально проще, чем справочники делать.

и пара вопросов:
3) Есть ли у вас на примете какой-нибудь валидатор этого XML языка? Разметку Notepad++ подсвечивает, но вот незакрытые теги он не дает.

4) есть ли на примете bat-файл для сжатия обратно в zip/xlsx из отдельных файлов? Разобрать можно одной командой вида
7z.exe x file1.xlsx
а вот собрать…

[dim2r]

Спасибо за статью. Смог разобраться, как препарировать коцанные файлы excel c ошибками, которые ни одна библиотека не открывает.

function xlsx2sql($xl_file){


    $xl_dir = $xl_file.'unzip';
    //dbg("unzip   '$xl_file' -d '$xl_dir'");
    exec("unzip   '$xl_file' -d '$xl_dir'");
    
    $xml = simplexml_load_file("$xl_dir/xl/sharedStrings.xml");
    $json = json_encode($xml);   
    $sharedStrings = json_decode($json,TRUE);


    $xml = simplexml_load_file("$xl_dir/xl/worksheets/sheet1.xml");
    $json = json_encode($xml);   
    $sheet1 = json_decode($json,TRUE);



    //print_r($sharedStrings['si'][0]['t']);    
    //print_r($sheet1['sheetData']['row'][0]['c'][0]['@attributes']['t']);    
    //
    //exit;
$row_num=0;

    foreach($sheet1['sheetData']['row'] as $row){

//            echo('row-------------'."\n");
        $row_num++;    
        $row_array = array();
        foreach($row['c'] as $cell){
    //            print_r($cell);
                $t='';
                if(isset($cell['@attributes']['t'])){
                    $t=$cell['@attributes']['t'];
                }


                $value='';
                if($t=='s'){
                    if(isset($cell['v'])){
                        $v=$cell['v'];
                        $value=$sharedStrings['si'][$v]['t'];

                    }
                }else{
                    if(isset($cell['v'])){
                        $value=$cell['v'];
                    }
                }
//                echo $value."\n";
                array_push($row_array,$value) ;
        }//row
                
             
            // тут работаем с $row_array -- список ячеек

    }
}

[beevasya]

Все хорошо и понятно. Но есть вопрос :)

Как привести ширину столбца в нормальный размер, например в мм или дюймах?

<col min="1" max="2" width="9" customWidth="1"/>

В этой строке width - вычисляемое значение,

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

А как его привести в привычные единицы измерения?