book.rs

//! xlsx を読むところ
//!
//! 試すファイルは、XML を手で書いてこの中で組み立てる。

mod common;

use common::{Workbook, styles};
use folio::book::Book;
use folio::cell::{Row, Value};
use folio::date::{self, Moment};
use std::path::PathBuf;


/// 試しに使うファイルの置き場。target の下なので消えても差し支えない
fn place(name: &str) -> PathBuf {
    let mut path = PathBuf::from("target");
    path.push(format!("book-{name}.xlsx"));
    path
}


/// シートを読み切って、行を全部返す
fn rows(book: &mut Book, name: &str) -> Vec<Row> {
    let mut sheet = book.read(name).unwrap();
    let mut result = Vec::new();

    while let Some(row) = sheet.read().unwrap() {
        result.push(row.clone());
    }

    result
}


/// 行の中の、列と値の組
fn value(row: &Row) -> Vec<(u32, Value)> {
    row.cell.iter().map(|one| (one.column, one.value.clone())).collect()
}


/// 文字の値
fn text(value: &str) -> Value {
    Value::Text(value.to_string())
}


#[test]
fn values_come_back_with_their_types() {
    let path = place("value");

    Workbook::new()
        .sheet("データ", concat!(
            "<sheetData>",
            "<row r=\"1\"><c r=\"A1\" t=\"s\"><v>0</v></c><c r=\"B1\"><v>3.5</v></c><c r=\"C1\" t=\"b\"><v>1</v></c></row>",
            "<row r=\"2\"><c r=\"A2\" t=\"s\"><v>1</v></c><c r=\"B2\"><v>-1</v></c><c r=\"C2\" t=\"b\"><v>0</v></c></row>",
            "<row r=\"3\"><c r=\"A3\" t=\"s\"><v>2</v></c><c r=\"B3\"><v>1234567890123</v></c>",
            "<c r=\"C3\" t=\"e\"><v>#N/A</v></c><c r=\"D3\"><v>1.5E-3</v></c></row>",
            "</sheetData>",
        ))
        .shared(&["<t>名前</t>", "<t>記号 &lt;&amp;&gt;\"'</t>", "<t xml:space=\"preserve\">改行\nあり</t>"])
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "データ");

    assert_eq!(row.len(), 3);
    assert_eq!(value(&row[0]), vec![(1, text("名前")), (2, Value::Number(3.5)), (3, Value::Boolean(true))]);
    assert_eq!(value(&row[1]), vec![(1, text("記号 <&>\"'")), (2, Value::Number(-1.0)), (3, Value::Boolean(false))]);
    assert_eq!(row[2].cell[0].value, text("改行\nあり"));
    assert_eq!(row[2].cell[1].value, Value::Number(1234567890123.0));
    assert_eq!(row[2].cell[2].value, Value::Error("#N/A".to_string()));
    assert_eq!(row[2].cell[3].value, Value::Number(0.0015));
}


#[test]
fn inline_and_formula_strings_are_text() {
    let path = place("inline");

    Workbook::new()
        .sheet("文字", concat!(
            "<sheetData><row r=\"1\">",
            "<c r=\"A1\" t=\"inlineStr\"><is><t>その場の文字</t></is></c>",
            "<c r=\"B1\" t=\"str\"><f>A1&amp;\"!\"</f><v>その場の文字!</v></c>",
            "<c r=\"C1\" t=\"inlineStr\"><is><r><t>飾り</t></r><r><rPr><b/></rPr><t>つき</t></r></is></c>",
            "<c r=\"D1\" t=\"d\"><v>2026-03-31T00:00:00</v></c>",
            "</row></sheetData>",
        ))
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "文字");

    assert_eq!(row[0].cell[0].value, text("その場の文字"));
    assert_eq!(row[0].cell[1].value, text("その場の文字!"));
    assert_eq!(row[0].cell[1].formula, "A1&\"!\"");
    assert_eq!(row[0].cell[2].value, text("飾りつき"));
    assert_eq!(row[0].cell[3].value, text("2026-03-31T00:00:00"), "日付の型は文字のまま返す");
}


#[test]
fn rich_text_is_joined_and_phonetic_guides_are_left_out() {
    let path = place("rich");

    Workbook::new()
        .sheet("飾り", "<sheetData><row r=\"1\"><c r=\"A1\" t=\"s\"><v>0</v></c><c r=\"B1\" t=\"s\"><v>1</v></c></row></sheetData>")
        .shared(&[
            "<r><t>太字</t></r><r><rPr><b/><sz val=\"11\"/></rPr><t xml:space=\"preserve\">と細字</t></r>",
            "<t>東京</t><rPh sb=\"0\" eb=\"2\"><t>トウキョウ</t></rPh><phoneticPr fontId=\"1\"/>",
        ])
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "飾り");
    assert_eq!(value(&row[0]), vec![(1, text("太字と細字")), (2, text("東京"))]);
}


#[test]
fn every_shared_item_keeps_its_number() {
    let path = place("item");

    // 空の <si/>、前置きつきの要素、注記、uniqueCount のずれがあっても、番号どおりに引ける
    let xml = format!(
        "{}<x:sst xmlns:x=\"{}\" count=\"9\" uniqueCount=\"99\">\
         <x:si><x:t>いち</x:t></x:si><x:si/><!-- 注記 --><x:si><x:t/></x:si>\
         <x:si>\n  <x:t>よん</x:t>\n</x:si></x:sst>",
        common::HEAD,
        common::SPREADSHEET,
    );

    Workbook::new()
        .sheet("番号", concat!(
            "<sheetData><row r=\"1\">",
            "<c r=\"A1\" t=\"s\"><v>0</v></c><c r=\"B1\" t=\"s\"><v>1</v></c>",
            "<c r=\"C1\" t=\"s\"><v>2</v></c><c r=\"D1\" t=\"s\"><v>3</v></c><c r=\"E1\" t=\"s\"><v>9</v></c>",
            "</row></sheetData>",
        ))
        .shared_xml(&xml)
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    assert_eq!(book.shared().count(), 4);

    let row = rows(&mut book, "番号");
    let seen: Vec<Value> = row[0].cell.iter().map(|one| one.value.clone()).collect();
    assert_eq!(seen, vec![text("いち"), text(""), text(""), text("よん"), text("")], "範囲の外の番号は空の文字");
}


#[test]
fn a_broken_sheet_is_an_error() {
    let path = place("broken-sheet");
    let body: String = (1..=20000).map(|row| format!("<row r=\"{row}\"><c r=\"A{row}\"><v>{row}</v></c></row>")).collect();
    Workbook::new().sheet("壊れ", &format!("<sheetData>{body}</sheetData>")).write(&path);

    // シートのパートの、縮めた中身の真ん中の 1 バイトを変える
    let archive = folio::zip::Archive::open(&path).unwrap();
    let entry = archive.entry().iter().find(|one| one.name == "xl/worksheets/sheet1.xml").unwrap().clone();
    drop(archive);

    let mut data = std::fs::read(&path).unwrap();
    let middle = entry.offset as usize + 30 + entry.name.len() + entry.compressed as usize / 2;
    data[middle] ^= 0x55;
    std::fs::write(&path, &data).unwrap();

    let mut book = Book::open(&path).unwrap();
    let mut sheet = book.read("壊れ").unwrap();

    let failed = loop {
        match sheet.read() {
            Ok(Some(_)) => {}
            Ok(None) => break false,
            Err(_) => break true,
        }
    };

    assert!(failed, "壊れていることに、読み終えるまでに気づくこと");
}


#[test]
fn characters_written_as_codes_come_back() {
    let path = place("code");

    Workbook::new()
        .sheet("符号", concat!(
            "<sheetData><row r=\"1\">",
            "<c r=\"A1\" t=\"s\"><v>0</v></c><c r=\"B1\" t=\"s\"><v>1</v></c>",
            "<c r=\"C1\" t=\"inlineStr\"><is><t>A_x0009_B</t></is></c>",
            "</row></sheetData>",
        ))
        .shared(&["<t>行1_x000D_\n行2</t>", "<t>_x005F_x0041_</t>"])
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "符号");
    assert_eq!(value(&row[0]), vec![(1, text("行1\r\n行2")), (2, text("_x0041_")), (3, text("A\tB"))]);
}


#[test]
fn several_sheets_keep_their_order_names_and_visibility() {
    let path = place("sheet");

    Workbook::new()
        .sheet("一枚目", "<sheetData><row r=\"1\"><c r=\"A1\" t=\"inlineStr\"><is><t>いち</t></is></c></row></sheetData>")
        .sheet("二枚目", "<sheetData><row r=\"1\"><c r=\"A1\" t=\"inlineStr\"><is><t>に</t></is></c></row></sheetData>")
        .sheet("三枚目", "<sheetData><row r=\"1\"><c r=\"A1\" t=\"inlineStr\"><is><t>さん</t></is></c></row></sheetData>")
        .hide("二枚目")
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let name: Vec<String> = book.sheet().iter().map(|one| one.name.clone()).collect();
    let visible: Vec<bool> = book.sheet().iter().map(|one| one.visible).collect();

    assert_eq!(name, vec!["一枚目", "二枚目", "三枚目"]);
    assert_eq!(visible, vec![true, false, true]);
    assert_eq!(rows(&mut book, "三枚目")[0].cell[0].value, text("さん"));
    assert_eq!(rows(&mut book, "一枚目")[0].cell[0].value, text("いち"));
    assert_eq!(rows(&mut book, "二枚目")[0].cell[0].value, text("に"));
}


#[test]
fn a_date_is_told_apart_by_its_format() {
    let path = place("date");

    // cellXfs の 0 番は標準、1 番は日付、2 番は日時、3 番は自前の日付、4 番は自前の数
    Workbook::new()
        .sheet("日付", concat!(
            "<sheetData><row r=\"1\">",
            "<c r=\"A1\" s=\"1\"><v>46112</v></c><c r=\"B1\" s=\"2\"><v>46112.5</v></c>",
            "<c r=\"C1\" s=\"3\"><v>46112</v></c><c r=\"D1\" s=\"4\"><v>46112</v></c><c r=\"E1\"><v>46112</v></c>",
            "</row></sheetData>",
        ))
        .style(&styles(&[(164, "yyyy\\-mm\\-dd"), (165, "0.00")], &[0, 14, 22, 164, 165]))
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    assert!(!book.epoch1904());

    let mut sheet = book.read("日付").unwrap();
    let style = sheet.style();
    let row = sheet.read().unwrap().unwrap();
    let date: Vec<bool> = row.cell.iter().map(|one| style.is_date(one.style)).collect();
    assert_eq!(date, vec![true, true, true, false, false]);

    let Value::Number(serial) = row.cell[1].value else {panic!("数ではありません")};
    let when = date::moment(serial, false);
    assert_eq!(when, Moment {year: 2026, month: 3, day: 31, hour: 12, minute: 0, second: 0});
}


#[test]
fn the_1904_system_is_read_from_the_workbook() {
    let path = place("1904");

    Workbook::new()
        .sheet("日付", "<sheetData><row r=\"1\"><c r=\"A1\" s=\"1\"><v>0</v></c></row></sheetData>")
        .style(&styles(&[], &[0, 14]))
        .epoch1904()
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    assert!(book.epoch1904());

    let mut sheet = book.read("日付").unwrap();
    assert!(sheet.epoch1904());

    let row = sheet.read().unwrap().unwrap();
    let Value::Number(serial) = row.cell[0].value else {panic!("数ではありません")};
    assert_eq!(date::moment(serial, true).year, 1904);
}


#[test]
fn a_formula_is_kept_with_its_value() {
    let path = place("formula");

    Workbook::new()
        .sheet("式", concat!(
            "<sheetData>",
            "<row r=\"1\"><c r=\"A1\"><v>2</v></c><c r=\"B1\"><f>A1*2</f><v>4</v></c></row>",
            "<row r=\"2\"><c r=\"A2\"><v>3</v></c><c r=\"B2\"><f t=\"shared\" ref=\"B2:B3\" si=\"0\">A2*2</f><v>6</v></c></row>",
            "<row r=\"3\"><c r=\"A3\"><v>4</v></c><c r=\"B3\"><f t=\"shared\" si=\"0\"/><v>8</v></c></row>",
            "</sheetData>",
        ))
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "式");

    assert_eq!(row[0].cell[1].formula, "A1*2");
    assert_eq!(row[0].cell[1].value, Value::Number(4.0));
    assert_eq!(row[1].cell[1].formula, "A2*2");
    assert_eq!(row[2].cell[1].value, Value::Number(8.0));
    assert_eq!(row[2].cell[1].formula, "", "共有数式の続きのセルは、数式の文字を持たない");
    assert_eq!(row[0].cell[0].formula, "");
}


#[test]
fn rows_and_columns_can_be_left_out() {
    let path = place("sparse");

    Workbook::new()
        .sheet("とびとび", concat!(
            "<sheetData>",
            "<row r=\"5\"><c r=\"C5\" t=\"inlineStr\"><is><t>み</t></is></c></row>",
            "<row r=\"7\"/>",
            "<row r=\"100\"><c r=\"A100\"><v>1</v></c><c r=\"AX100\"><v>50</v></c></row>",
            "</sheetData>",
        ))
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "とびとび");

    assert_eq!(row.len(), 3);
    assert_eq!(row[0].number, 5);
    assert_eq!(value(&row[0]), vec![(3, text("み"))]);
    assert_eq!(row[1].number, 7);
    assert!(row[1].cell.is_empty());
    assert_eq!(row[2].number, 100);
    assert_eq!(value(&row[2]), vec![(1, Value::Number(1.0)), (50, Value::Number(50.0))]);
}


#[test]
fn a_missing_reference_counts_on_from_the_one_before() {
    let path = place("count");

    Workbook::new()
        .sheet("数え", concat!(
            "<sheetData>",
            "<row><c><v>1</v></c><c><v>2</v></c></row>",
            "<row r=\"5\"><c r=\"B5\"><v>3</v></c><c><v>4</v></c></row>",
            "<row><c><v>5</v></c></row>",
            "</sheetData>",
        ))
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "数え");
    let seen: Vec<(u32, Vec<u32>)> = row.iter().map(|one| (one.number, one.cell.iter().map(|cell| cell.column).collect())).collect();
    assert_eq!(seen, vec![(1, vec![1, 2]), (5, vec![2, 3]), (6, vec![1])]);
}


#[test]
fn empty_and_unreadable_cells() {
    let path = place("empty");

    Workbook::new()
        .sheet("空", concat!(
            "<sheetData><row r=\"1\">",
            "<c r=\"A1\" s=\"1\"/><c r=\"B1\"><v></v></c><c r=\"C1\"><v>abc</v></c>",
            "<c r=\"D1\"><v> 42 </v></c><c r=\"E1\" t=\"b\"><v> 1 </v></c>",
            "</row></sheetData>",
        ))
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "空");

    assert_eq!(row[0].cell[0].value, Value::Empty, "書式だけのセルは空で返す");
    assert_eq!(row[0].cell[0].style, 1);
    assert_eq!(row[0].cell[1].value, Value::Empty);
    assert_eq!(row[0].cell[2].value, text("abc"), "数として読めない値は文字のまま返す");
    assert_eq!(row[0].cell[3].value, Value::Number(42.0));
    assert_eq!(row[0].cell[4].value, Value::Boolean(true));
}


#[test]
fn whitespace_inside_and_between_tags_is_allowed() {
    let path = place("space");

    Workbook::new()
        .sheet("空白", concat!(
            "<sheetData>\n  <row r=\"1\">\n",
            "    <c r=\"A1\"\n s=\"1\"\t t=\"s\"><v>0</v></c>\n",
            "    <c r = \"B1\" s='2'><v>2</v>\n    </c>\n",
            "    <c r=\"C1\" t=\"inlineStr\"><is>\n      <t>その場</t>\n    </is></c>\n",
            "    <c r=\"D1\" ><v> 4 </v></c>\n",
            "    <c r=\"E1\" s=\"3\" />\n",
            "  </row >\n</sheetData>",
        ))
        .shared(&["<t>名前</t>"])
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "空白");

    assert_eq!(row.len(), 1);
    assert_eq!(value(&row[0]), vec![
        (1, text("名前")),
        (2, Value::Number(2.0)),
        (3, text("その場")),
        (4, Value::Number(4.0)),
        (5, Value::Empty),
    ]);

    let style: Vec<u32> = row[0].cell.iter().map(|one| one.style).collect();
    assert_eq!(style, vec![1, 2, 0, 0, 3]);
}


#[test]
fn a_column_can_be_found_by_its_number() {
    let path = place("at");

    Workbook::new()
        .sheet("引き", "<sheetData><row r=\"1\"><c r=\"B1\"><v>2</v></c><c r=\"G1\"><v>7</v></c></row></sheetData>")
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let mut sheet = book.read("引き").unwrap();
    let row = sheet.read().unwrap().unwrap();

    assert_eq!(row.at(7).unwrap().value, Value::Number(7.0));
    assert!(row.at(3).is_none());
}


#[test]
fn a_namespace_prefix_does_not_matter() {
    let path = place("prefix");
    let main = common::SPREADSHEET;

    let xml = format!(
        "{}<x:worksheet xmlns:x=\"{main}\"><x:sheetData><x:row r=\"1\">\
         <x:c r=\"A1\" t=\"s\"><x:v>0</x:v></x:c><x:c r=\"B1\"><x:v>9</x:v></x:c>\
         </x:row></x:sheetData></x:worksheet>",
        common::HEAD
    );

    Workbook::new().worksheet("前置き", &xml).shared(&["<t>名前</t>"]).write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "前置き");
    assert_eq!(value(&row[0]), vec![(1, text("名前")), (2, Value::Number(9.0))]);
}


#[test]
fn what_is_outside_the_rows_does_not_get_in_the_way() {
    let path = place("outside");

    Workbook::new()
        .sheet("まわり", concat!(
            "<dimension ref=\"A1:B2\"/><sheetViews><sheetView workbookViewId=\"0\"/></sheetViews>",
            "<cols><col min=\"1\" max=\"1\" width=\"24\" customWidth=\"1\"/></cols>",
            "<sheetData>",
            "<row r=\"1\" spans=\"1:2\" ht=\"30\" customHeight=\"1\"><c r=\"A1\"><v>1</v></c></row>",
            "<!-- 注記 --><row r=\"2\" hidden=\"1\"><c r=\"B2\"><v>2</v></c></row>",
            "</sheetData>",
            "<mergeCells count=\"1\"><mergeCell ref=\"A8:C8\"/></mergeCells>",
            "<pageMargins left=\"0.7\" right=\"0.7\" top=\"0.75\" bottom=\"0.75\" header=\"0.3\" footer=\"0.3\"/>",
        ))
        .write(&path);

    let mut book = Book::open(&path).unwrap();
    let row = rows(&mut book, "まわり");

    assert_eq!(row.len(), 2);
    assert_eq!(value(&row[0]), vec![(1, Value::Number(1.0))]);
    assert_eq!(value(&row[1]), vec![(2, Value::Number(2.0))]);
}


#[test]
fn an_empty_sheet_has_no_rows() {
    let path = place("blank");
    Workbook::new().sheet("空", "<sheetData/>").write(&path);

    let mut book = Book::open(&path).unwrap();
    assert!(rows(&mut book, "空").is_empty());
}


#[test]
fn many_rows_are_read_in_order() {
    let path = place("many");
    let count = 120000u32;
    let mut body = String::from("<sheetData>");

    for row in 1..=count {
        body.push_str(&format!(
            "<row r=\"{row}\"><c r=\"A{row}\" t=\"s\"><v>{}</v></c><c r=\"B{row}\"><v>{row}</v></c><c r=\"C{row}\"><v>{}</v></c></row>",
            row % 997,
            row as f64 * 1.5
        ));
    }

    body.push_str("</sheetData>");
    let item: Vec<String> = (0..997).map(|index| format!("<t>名前{index}</t>")).collect();
    let shared: Vec<&str> = item.iter().map(|one| one.as_str()).collect();
    Workbook::new().sheet("大", &body).shared(&shared).write(&path);

    let mut book = Book::open(&path).unwrap();
    let mut sheet = book.read("大").unwrap();
    let mut seen = 0u32;
    let mut total = 0.0;

    while let Some(row) = sheet.read().unwrap() {
        seen += 1;
        assert_eq!(row.number, seen);
        assert_eq!(row.cell.len(), 3);
        assert_eq!(row.cell[0].value, Value::Text(format!("名前{}", seen % 997)));

        if let Value::Number(number) = row.cell[1].value {
            total += number;
        }
    }

    assert_eq!(seen, count);
    assert_eq!(total, (count as f64) * (count as f64 + 1.0) / 2.0);
}


#[test]
fn a_missing_sheet_is_an_error() {
    let path = place("missing");
    Workbook::new().sheet("ある", "<sheetData/>").write(&path);

    let mut book = Book::open(&path).unwrap();
    assert!(book.read("ない").is_err());
}


#[test]
fn a_file_that_is_not_a_book_is_an_error() {
    let path = place("broken");
    std::fs::write(&path, "これは xlsx ではありません").unwrap();
    assert!(Book::open(&path).is_err());
}