First thing I would do is to understand the format. Save your files as xlsx, then change the extension to .zip, unzip them and peruse the data.

You can see that the data (text and numbers) is in one xml file and the styles (and which cell has what style) are kept in another. So, you will need 2 passes:

1. Need to check all unique formats your source data range uses, and copy only those (with an xml Perl module) then reference to them in your copied cells, but they will have a new format index number, which you will have to keep in a hash.

2. Once you update the style xml in the destination file (you can manipulate zip files with Perl), you can reopen the destination spreadsheet, and only paste the cell values, as their format was "pasted" in the first pass.

Also checked PHP:spreadsheet and big nope there too (no easy style cloning). Doing it cell by cell will make your styles xml much bigger than detecting similar styling and applying ranges, but if it's not many cells, it should be manageable.

Alternative 3:

LibreOffice has a commandline transformation (search for soffice --headless --convert-to), you could output to html, and then use xml parsers to parse everything, then convert the html back to an excel format. But I've noticed that things get lost in translation and the new xls will have lost formatting. (then again, I have an ancient Debian, so ymmv). Did not check .fods format, seems much more complete.

In reply to Re: Unable to retain the format in copying from one excel file to another by FreeBeerReekingMonk
in thread Unable to retain the format in copying from one excel file to another by nemadepsn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.