凪瀬 Blog
Programming SHOT BAR

目次

Blog 利用状況
  • 投稿数 - 260
  • 記事 - 0
  • コメント - 46925
  • トラックバック - 192
ニュース
広告
  • Java開発者募集中
  • 経歴不問
  • 腕に自信のある方
  • 富山市内
  • (株)凪瀬アーキテクツ
アクセサリ
  • あわせて読みたい
凪瀬悠輝(なぎせ ゆうき)
  • Java技術者
  • お茶好き。カクテル好き。
  • 所属は(株)凪瀬アーキテクツ
  • Twitter:@nagise

書庫

日記カテゴリ

 

Excel 2007では文字列はプールされブック全体で共有されるではxlsxファイル内で 文字列が/xl/sharedStrings.xmlファイルにプールされ、共有されることについて解説しましたが、 実は一部に例外があります。

グラフに使われる文字列はキャッシュされる

Excel上でのグラフは/xl/charts/chartX.xmlによって記述されます(Xは1からはじまり、グラフごとに1つ作られる)。

chartX.xmlを開くとわかりますが、<c:strCache>や<c:numCache>といった要素で ラベルに使われる文字列や、グラフの参照先のデータのキャッシュなどが残されています。

このため、動的に書き出したデータを元にグラフを描画したい場合、 参照先のExcelシートの値を編集しただけではキャッシュの値が表示され、 うまくグラフを描画することができません。

数値やラベル名などが動的に変更される場合、これら<c:strCache>と<c:numCache>は あらかじめchartX.xmlから削除しておきましょう。 キャッシュが存在しなければ実データを参照してくれます。

計算式で別のセルの値を参照した場合、値がキャッシュされる

通常のワークシート内でも計算式がある場合、計算結果がキャッシュされます。

<c r="B1" t="str">
  <f>A1</f>
  <v>Hoge</v>
</c>

f要素が計算式ですね。今の場合はB1セルに"=A1"という単純な計算式を入れています。 A1セルには文字列"Hoge"があり、これはsharedStrings.xmlに格納されています。

数値でも文字列でもそうなのですが、計算結果はキャッシュとしてv要素で保管されます。 このv要素は必須ではないので、動的に書き出したxlsxファイルで表計算を用いる場合、 あらかじめv要素を除去しておく必要があります。

言語の切り替えは容易ではない

Excel 2007が生成するxlsxファイルではこれらのキャッシュがしっかりと作られるため、 テンプレートとして使う場合はこれらのキャッシュの除去を最初に行う必要があります。

sharedStrings.xmlさえ切り替えれば簡単に言語が切り換えられるような気がしますが、 実際にはこれらのキャッシュを除去しないと切り替えが行えません。

システムとして動的に切り替えている場合はプログラムでなんとかするのでしょうが、 現実問題、zipで解凍してsharedStrings.xmlを差し替えてzipで圧縮するという手順では、 正しく文字列を切り替えられない場合があります。

保守フェーズにおける工数は多めに確保しましょう

xlsx形式はExcel 2007を用いて生成できるため、Excelで生成したxlsxファイルを テンプレートに一部のデータを書き換えれば簡単にExcelファイルが作成できるように思うかもしれません。

しかし、実際にはこれらキャッシュなどを除去するなどの工程が必要となるため、 フォーマットの修正は想像以上に工数を食います。 システムでテンプレートとして用いる場合は加工が必要ですからExcelで開いてちょっと修正、というわけにはいかないのです。

文字列はインデックスで管理されていますので、うっかり文字列を挿入したりすると 全部のシートで文字列のインデックスが変更になるなどの影響があったりします。

なお、セルのスタイルについてもIDではなくインデックスで管理されているため、 スタイルの修正も同様に大きく工数を取られることがあります。

xlsx形式での出力は、フォーマットの保守性に難がありますので、 保守フェーズで頻繁に変更が想定される場合は相応の覚悟をしてください。

投稿日時 : 2008年1月25日 18:02
コメント
  • # re: 【OpenXML】Excelでは計算結果がキャッシュされている
    masa
    Posted @ 2008/01/26 1:58
    関連エントリも合わせて読みましたが、動的に変化するインデックスによる文字列の参照って・・・
    相当酷い作りになっているようですね。
    この仕様だと、私の技量では手を出せそうにありません。
  • # Heya i am for the first time here. I came across this board and I find It truly useful & it helped me out much. I hope to give something back and help others like you helped me.
    Heya i am for the first time here. I came across t
    Posted @ 2019/05/10 5:54
    Heya i am for the first time here. I came
    across this board and I find It truly useful & it helped me
    out much. I hope to give something back and help others like you helped me.
  • # No matter if some one searches for his essential thing, so he/she desires to be available that in detail, therefore that thing is maintained over here.
    No matter if some one searches for his essential t
    Posted @ 2019/05/17 23:47
    No matter if some one searches for his essential
    thing, so he/she desires to be available that in detail,
    therefore that thing is maintained over here.
  • # It's perfect time to make a few plans for the long run and it's time to be happy. I've read this post and if I could I want to recommend you few attention-grabbing things or advice. Maybe you can write subsequent articles regarding this article. I des
    It's perfect time to make a few plans for the long
    Posted @ 2019/09/08 5:29
    It's perfect time to make a few plans for the long run and
    it's time to be happy. I've read this post and if I could I want to recommend you few attention-grabbing things or advice.
    Maybe you can write subsequent articles regarding this article.
    I desire to learn even more issues about it!
  • # I blog often and I truly appreciate your content. Your article has truly peaked my interest. I'm going to take a note of your website and keep checking for new details about once a week. I opted in for your RSS feed too.
    I blog often and I truly appreciate your content.
    Posted @ 2019/09/08 23:07
    I blog often and I truly appreciate your content.

    Your article has truly peaked my interest. I'm going to take a note of your website and keep checking for new details about once a week.

    I opted in for your RSS feed too.
  • # Pretty! This was a really wonderful post. Thanks for supplying these details.
    Pretty! This was a really wonderful post. Thanks f
    Posted @ 2021/09/01 7:14
    Pretty! This was a really wonderful post. Thanks for supplying these details.
  • # Pretty! This was a really wonderful post. Thanks for supplying these details.
    Pretty! This was a really wonderful post. Thanks f
    Posted @ 2021/09/01 7:15
    Pretty! This was a really wonderful post. Thanks for supplying these details.
  • # Pretty! This was a really wonderful post. Thanks for supplying these details.
    Pretty! This was a really wonderful post. Thanks f
    Posted @ 2021/09/01 7:16
    Pretty! This was a really wonderful post. Thanks for supplying these details.
  • # Pretty! This was a really wonderful post. Thanks for supplying these details.
    Pretty! This was a really wonderful post. Thanks f
    Posted @ 2021/09/01 7:17
    Pretty! This was a really wonderful post. Thanks for supplying these details.
  • # It's wonderful that you are getting thoughts from this paragraph as well as from our discussion made here.
    It's wonderful that you are getting thoughts from
    Posted @ 2021/09/02 4:29
    It's wonderful that you are getting thoughts from this
    paragraph as well as from our discussion made here.
  • # Hi there colleagues, how is the whole thing, and what you wish for to say concerning this article, in my view its truly awesome in favor of me.
    Hi there colleagues, how is the whole thing, and w
    Posted @ 2021/09/04 10:31
    Hi there colleagues, how is the whole thing, and what you wish for to
    say concerning this article, in my view its truly awesome in favor of me.
  • # Hi there colleagues, how is the whole thing, and what you wish for to say concerning this article, in my view its truly awesome in favor of me.
    Hi there colleagues, how is the whole thing, and w
    Posted @ 2021/09/04 10:32
    Hi there colleagues, how is the whole thing, and what you wish for to
    say concerning this article, in my view its truly awesome in favor of me.
  • # Hi there colleagues, how is the whole thing, and what you wish for to say concerning this article, in my view its truly awesome in favor of me.
    Hi there colleagues, how is the whole thing, and w
    Posted @ 2021/09/04 10:33
    Hi there colleagues, how is the whole thing, and what you wish for to
    say concerning this article, in my view its truly awesome in favor of me.
  • # Hi there colleagues, how is the whole thing, and what you wish for to say concerning this article, in my view its truly awesome in favor of me.
    Hi there colleagues, how is the whole thing, and w
    Posted @ 2021/09/04 10:34
    Hi there colleagues, how is the whole thing, and what you wish for to
    say concerning this article, in my view its truly awesome in favor of me.
  • # I will immediately clutch your rss feed as I can't find your e-mail subscription link or newsletter service. Do you have any? Please let me recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest ba
    I will immediately clutch your rss feed as I can't
    Posted @ 2021/09/14 4:01
    I will immediately clutch your rss feed as I can't find your e-mail subscription link or
    newsletter service. Do you have any? Please let me
    recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest bars
  • # I will immediately clutch your rss feed as I can't find your e-mail subscription link or newsletter service. Do you have any? Please let me recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest ba
    I will immediately clutch your rss feed as I can't
    Posted @ 2021/09/14 4:02
    I will immediately clutch your rss feed as I can't find your e-mail subscription link or
    newsletter service. Do you have any? Please let me
    recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest bars
  • # I will immediately clutch your rss feed as I can't find your e-mail subscription link or newsletter service. Do you have any? Please let me recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest ba
    I will immediately clutch your rss feed as I can't
    Posted @ 2021/09/14 4:03
    I will immediately clutch your rss feed as I can't find your e-mail subscription link or
    newsletter service. Do you have any? Please let me
    recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest bars
  • # I will immediately clutch your rss feed as I can't find your e-mail subscription link or newsletter service. Do you have any? Please let me recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest ba
    I will immediately clutch your rss feed as I can't
    Posted @ 2021/09/14 4:04
    I will immediately clutch your rss feed as I can't find your e-mail subscription link or
    newsletter service. Do you have any? Please let me
    recognize so that I may just subscribe. Thanks. quest bars https://www.iherb.com/search?kw=quest%20bars quest bars
  • # It's a shame you don't have a donate button! I'd certainly donate to this outstanding blog! I guess for now i'll settle for bookmarking and adding your RSS feed to my Google account. I look forward to new updates and will share this website with my Face
    It's a shame you don't have a donate button! I'd c
    Posted @ 2021/10/22 20:42
    It's a shame you don't have a donate button! I'd certainly donate to this outstanding blog!

    I guess for now i'll settle for bookmarking and adding your RSS feed to
    my Google account. I look forward to new updates and will
    share this website with my Facebook group. Chat soon! part time jobs
    hired in 30 minutes https://parttimejobshiredin30minutes.wildapricot.org/
  • # you're in point of fact a excellent webmaster. The website loading pace is incredible. It sort of feels that you're doing any distinctive trick. Moreover, The contents are masterwork. you've performed a wonderful task in this matter!
    you're in point of fact a excellent webmaster. The
    Posted @ 2021/10/25 15:48
    you're in point of fact a excellent webmaster. The
    website loading pace is incredible. It sort of feels that you're doing any distinctive
    trick. Moreover, The contents are masterwork. you've performed a wonderful task
    in this matter!
  • # Excellent post. I was checking continuously this weblog and I am inspired! Extremely helpful info specifically the remaining section : ) I care for such information a lot. I was seeking this particular info for a long time. Thanks and good luck.
    Excellent post. I was checking continuously this w
    Posted @ 2021/10/26 18:37
    Excellent post. I was checking continuously this weblog and I
    am inspired! Extremely helpful info specifically the remaining section :
    ) I care for such information a lot. I was seeking
    this particular info for a long time. Thanks and
    good luck.
  • # This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
    This article gives clear idea designed for the new
    Posted @ 2021/11/12 15:06
    This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
  • # This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
    This article gives clear idea designed for the new
    Posted @ 2021/11/12 15:07
    This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
  • # This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
    This article gives clear idea designed for the new
    Posted @ 2021/11/12 15:08
    This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
  • # This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
    This article gives clear idea designed for the new
    Posted @ 2021/11/12 15:09
    This article gives clear idea designed for the new visitors of blogging, that really how to do running a blog.
  • # Yesterday, while I was at work, my sister stole my iPad and tested to see if it can survive a 30 foot drop, just so she can be a youtube sensation. My iPad is now destroyed and she has 83 views. I know this is completely off topic but I had to share it
    Yesterday, while I was at work, my sister stole my
    Posted @ 2021/12/23 9:20
    Yesterday, while I was at work, my sister stole my iPad and tested to see
    if it can survive a 30 foot drop, just so she can be a youtube sensation.
    My iPad is now destroyed and she has 83 views.
    I know this is completely off topic but I had to share it with someone!
タイトル
名前
Url
コメント