การอ้างอิงตารางแบบมีโครงสร้างของ Excel ใน Delphi ด้วย HotXLS

ตอนนี้ HotXLS ประมวลผลการอ้างอิงตารางแบบมีโครงสร้างแล้ว ดังนั้น =SUM(Table1[Amount]) จะให้ค่าตัวเลขแทนที่จะถูกข้ามไป ตัวแก้ปัญหารองรับ Table[Column], Table[[Column]] ช่วงคอลัมน์อย่าง Table[[Q1]:[Q4]] และตัวระบุรายการ [#Data], [#All], [#Headers] และ [#Totals] โดยแก้แต่ละแบบเทียบกับโมเดลตารางของเวิร์กบุ๊กในเวลาแยกวิเคราะห์ ในขณะที่ข้อความสูตรต้นฉบับเขียนกลับเหมือนเดิมทุกประการ

มีรูปแบบหนึ่งที่ขาดหายไปโดยตั้งใจ และเป็นรูปแบบที่คนมักเจอเป็นอันดับแรก คือทางลัดแถวปัจจุบัน [@Column] ไม่ได้รับการรองรับ ด้วยเหตุผลเชิงโครงสร้างที่ควรค่าแก่การเข้าใจ มากกว่าจะหาทางเลี่ยงแบบไม่รู้ที่มา

เหตุใดการอ้างอิงแบบมีโครงสร้างจึงไม่ใช่แค่ช่วงเซลล์ที่มีชื่อสวยงาม

เพราะชื่อที่กำหนดไว้จะตรึงที่อยู่ไว้ ในขณะที่การอ้างอิงตารางไม่ทำเช่นนั้น เขียน DataBlock เป็นชื่อที่ชี้ไปยัง Sheet1!$A$2:$D$100 แล้วมันจะยังคงเป็นสี่เหลี่ยมผืนผ้านั้นจนกว่าจะมีอะไรเขียนทับมัน แต่เขียน Sales[Amount] แล้วมันหมายถึง “คอลัมน์ Amount ของตาราง Sales” ไม่ว่าขอบเขตของตารางนั้นจะเป็นอย่างไรในตอนที่สูตรถูกประมวลผล เพิ่มยี่สิบแถวเข้าไปในตาราง แล้วผลรวมก็จะครอบคลุมแถวเหล่านั้นด้วย ไม่มีการอ้างอิงใดต้องปรับเลย เพราะไม่เคยมีที่อยู่อยู่ในสูตรตั้งแต่แรก

คุณสมบัติเชิงสัญลักษณ์แบบนี้เองคือเหตุผลที่การอ้างอิงนี้แก้ด้วยการแทนที่สตริงไม่ได้ ตัวแก้ปัญหาต้องค้นหาตารางตามชื่อในเวิร์กบุ๊ก มองหาคอลัมน์ตามข้อความหัวตาราง ตัดสินใจว่าแถวใดที่ตัวระบุรายการที่ร้องขอครอบคลุม แล้วสร้างสี่เหลี่ยมผืนผ้าที่เป็นรูปธรรมขึ้นมา HotXLS ทำสิ่งนี้ระหว่างการคอมไพล์สูตรผ่านโมเดลตาราง นี่คือเหตุผลที่สูตรที่เขียนไว้ก่อนตารางจะขยายตัวยังคงประมวลผลตามขอบเขตปัจจุบันของตารางอยู่เสมอ

ไวยากรณ์ที่ HotXLS ประมวลผลได้

ไวยากรณ์ที่รองรับครอบคลุมผลลัพธ์แบบสี่เหลี่ยมผืนผ้าเดียว และควรระบุไว้ให้ชัดเจน เพราะเอกสารของ Excel นำเสนอขอบเขตที่กว้างกว่ามากที่เอนจินส่วนใหญ่ใช้งานได้จริง HotXLS รับ [Col] และรูปแบบมีวงเล็บซ้อน [[Col]] ตัวระบุรายการเปล่า [#Data], [#All], [#Headers] และ [#Totals] รูปแบบผสม [[#Data],[Col]] ช่วงภายในตัวระบุรายการอย่าง [[#Data],[Col1]:[Col2]] และช่วงธรรมดา [Col1]:[Col2]

สิ่งที่ชุดนี้ให้คุณได้คือรูปแบบการอ้างอิงทุกแบบที่สร้างบล็อกต่อเนื่องหนึ่งบล็อก: คอลัมน์เดียว ชุดคอลัมน์ที่ติดกัน ส่วนที่เฉพาะเนื้อหาหรือรวมหัวตารางของทั้งสองแบบนั้น การรวมแบบไม่ติดกันและผลลัพธ์แบบหลายพื้นที่อยู่นอกขอบเขตนี้ เมื่อการอ้างอิงแก้ไม่ได้ สูตรจะยังคงพฤติกรรมข้ามไปโดยไม่มีค่าแบบเดิม แทนที่จะแทนที่ด้วยการเดา ดังนั้นการอ้างอิงที่แก้ไม่ได้จะไม่มีวันกลายเป็นตัวเลขที่ผิดแต่ดูสมเหตุสมผล

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... write the header row and 24 data rows ...

    Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

เหตุใดรูปแบบแถวปัจจุบันจึงถูกตัดออกโดยตั้งใจ

[@Column] และ [#This Row] หมายถึง “เซลล์ของคอลัมน์นั้นบนแถวที่สูตรนี้อาศัยอยู่” ค่านี้จึงขึ้นอยู่กับตำแหน่งของเซลล์ที่กำลังประมวลผล ไม่ใช่แค่ขึ้นอยู่กับตารางเท่านั้น นี่คือการอ้างอิงคนละแบบ ไม่ใช่สี่เหลี่ยมผืนผ้าที่ตัวคอมไพเลอร์แก้ได้ครั้งเดียว แต่เป็นการแก้ปัญหาต่อเซลล์ที่ต้องทำซ้ำสำหรับทุกแถวที่สูตรนั้นครอบครองอยู่

HotXLS คืนค่า False จากตัวแก้ปัญหาช่วงตารางสำหรับรูปแบบเหล่านี้ ซึ่งส่งมันเข้าสู่เส้นทางข้ามไปโดยไม่มีค่า ข้อความสูตรถูกเก็บรักษาไว้และเขียนกลับโดยไม่เปลี่ยนแปลง ดังนั้นเวิร์กบุ๊กที่ใช้ [@Amount] จะเปิดได้ถูกต้องใน Excel หลังจากผ่านแอปพลิเคชันของคุณไปแล้ว มีเพียงค่าที่ HotXLS คำนวณเท่านั้นที่ขาดหายไป เมื่อต้องเลือกระหว่างค่าที่ขาดหายกับค่าที่คำนวณผิดแถว การขาดหายเป็นสิ่งที่คุณตรวจจับได้

ทางแก้ในทางปฏิบัติเป็นเรื่องกลไกล้วน ๆ: ในเวิร์กบุ๊กที่คุณสร้างขึ้นเอง ให้เขียนการอ้างอิงแบบสัมพัทธ์สไตล์ A1 ที่เทียบเท่ากันแทน ซึ่งเป็นสิ่งที่ Excel เก็บไว้ภายในอยู่แล้วสำหรับตรรกะระดับตารางส่วนใหญ่ ในเวิร์กบุ๊กที่คุณเพียงแค่ประมวลผล ให้ปล่อยสูตรไว้ตามเดิมและอ่านค่าที่แคชไว้ซึ่ง Excel เก็บไว้อยู่แล้ว ซึ่งเป็นสิ่งที่ไปป์ไลน์แบบโหลดแล้วรายงานผลมักต้องการ

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table <> nil then
    begin
      // ค้นหาแบบ recordset บนเนื้อหาตาราง ผลลัพธ์แถวเริ่มนับที่ 1
      Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
      while Row > 0 do
      begin
        Log(VarToStr(Sheet.Cells[Row, 4].Value));
        Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
      end;
    end;
  finally
    Book.Free;
  end;
end;

เกิดอะไรขึ้นเมื่อตารางเปลี่ยนรูปทรง

การอ้างอิงแบบมีโครงสร้างจะกลายเป็นโมฆะ แทนที่จะถูกชี้ไปที่อื่นอย่างเงียบ ๆ เมื่อสิ่งที่มันตั้งชื่อไว้หายไป ลบคอลัมน์ออกไป แล้วสูตรที่อ้างอิงคอลัมน์นั้นจะกลายเป็นโมฆะแบบเดียวกับที่ Excel ทำให้เป็นโมฆะ ลบหรือเปลี่ยนชื่อตาราง แล้วการอ้างอิงถึงมันก็จะถูกจัดการแบบเดียวกัน นี่คือพฤติกรรมที่ถูกต้องและสะท้อนการปรับการอ้างอิงตามปกติ ตามที่อธิบายไว้ใน การปรับการอ้างอิงสูตรเมื่อแทรกและลบ ซึ่งหน้าที่ของเอนจินคือรักษาความถูกต้องของสูตรไว้ ไม่ใช่แค่ทำให้ดูเหมือนถูกต้อง

การเพิ่มแถวคือกรณีตรงกันข้าม และไม่ต้องปรับอะไรเลย เพราะการอ้างอิงตั้งชื่อตาราง ไม่ใช่สี่เหลี่ยมผืนผ้า การต่อแถวเข้าไปในขอบเขตของตารางจะขยายสิ่งที่ [#Data] ครอบคลุม โดยไม่ต้องแตะสูตรแม้แต่ตัวเดียว นี่คือคุณสมบัติที่ทำให้ตารางคุ้มค่าต่อการใช้ในเทมเพลตรายงาน แถวผลรวมจะยังคงบวกทุกอย่างที่การนำเข้าสร้างขึ้น ไม่ว่าจะกลายเป็นกี่แถวก็ตาม

วินัยในการเขียนกลับ

HotXLS เก็บข้อความสูตรต้นฉบับไว้ เวิร์กบุ๊กที่โหลดมาพร้อม SUM(SalesTable[Amount]) จะถูกบันทึกด้วย SUM(SalesTable[Amount]) ไม่ใช่ที่แก้ไขแล้วเป็น SUM(D2:D25) เรื่องนี้สำคัญกว่าที่เห็น ผู้ใช้ที่เปิดผลลัพธ์ของคุณใน Excel คาดหวังจะเห็นสูตรที่เขาเขียนไว้ และที่อยู่ที่แก้ไขแล้วจะเปลี่ยนโมเดลที่ดูแลตัวเองได้ให้กลายเป็นโมเดลที่เปราะบางซึ่งหยุดครอบคลุมแถวใหม่ ๆ ไปอย่างเงียบ ๆ

ความสามารถที่เกี่ยวข้องอีกสองอย่างทำให้ภาพสมบูรณ์ นิยามตารางเอง รวมถึงตารางที่ไม่มีหัวตารางและคอมเมนต์ต่อตาราง จะเขียนกลับผ่านโมเดลตารางตามที่อธิบายไว้ใน การตรวจสอบข้อมูล AutoFilter และตารางของ Excel และเมื่อหลายเซลล์ใช้รูปแบบเดียวกัน XLSX จะเก็บมันไว้เพียงครั้งเดียวเป็นสูตรที่ใช้ร่วมกัน ซึ่งจะถูกขยายและเขียนออกใหม่ตามที่อธิบายไว้ใน การขยาย shared formula si การอ้างอิงแบบมีโครงสร้างภายในสูตรที่ใช้ร่วมกันต้องผ่านทั้งสองเส้นทาง ดังนั้นทั้งสองต้องทำงานถูกต้อง และมันก็เป็นเช่นนั้นจริง

HotXLS อ่านและเขียน XLS, XLSX และ ODS จาก Delphi และ C++Builder โดยไม่ต้องติดตั้ง Excel และไม่ต้องใช้ระบบอัตโนมัติของ Office ใด ๆ ประมวลผลสูตรด้วยเอนจินของตัวเอง โมเดลตาราง เอนจินสูตร และ API การคำนวณใหม่มีเอกสารอธิบายไว้ที่ หน้าคอมโพเนนต์สเปรดชีตสำหรับ Delphi ของ HotXLS