แผนภูมิ รูปภาพ และวัตถุวาดเขียนของ HotXLS ใน Delphi

อะไรก็ตามที่ลอยอยู่เหนือ grid ของ worksheet (แผนภูมิ โลโก้ ตราประทับ กล่องคำอธิบาย) คือ drawing object และ drawing object ถูกนิยามด้วยสองสิ่ง: มันคืออะไร และมันถูกยึดตำแหน่งไว้ที่ไหน จุดยึดตำแหน่งคือส่วนที่คนมักทำผิด แผนภูมิไม่ได้อยู่ในเซลล์ มันอยู่ในสี่เหลี่ยมที่ปักหมุดไว้กับช่วงแถวและคอลัมน์ และข้อมูลที่มันพล็อตเป็นชุด A1 reference แยกต่างหากที่จุดยึดไม่รู้จักเลยแม้แต่น้อย ย้ายกรอบแล้วพล็อตยังคงอยู่ที่เดิม แทรกแถวไว้ข้างล่างแล้วกรอบก็จะเลื่อนลงไปด้วย การรักษาระบบพิกัดสองแบบนี้ให้ชัดเจนคือสิ่งที่ทำให้โค้ดวาดรูปทำงานถูกต้องเป็นส่วนใหญ่

HotXLS เป็นไลบรารี Object Pascal แบบ native ที่อ่านและเขียน XLS และ XLSX ได้โดยไม่ต้องใช้ Excel automation และมันมีโมเดลการวาดสองแบบแยกจากกัน เพราะไฟล์สองฟอร์แมตนี้เก็บภาพวาดต่างกัน ฟอร์แมต BIFF8 .xls เก็บแผนภูมิไว้บน sheet เฉพาะของมันเอง และเก็บ shape ที่ลอยอยู่ไว้ใน OfficeArt stream ที่แนบกับ worksheet ฟอร์แมต OOXML .xlsx สามารถฝังแผนภูมิไว้ข้างใน grid ได้เลย โดยยึดไว้กับสี่เหลี่ยมของเซลล์ เคียงคู่กับรูปภาพและ shape ที่ลอยอยู่แบบเดียวกัน object model สะท้อนความแตกต่างนี้ และความล้มเหลวที่คุ้มค่าจะพูดถึงล้วนมาจากการเอากฎของฟอร์แมตหนึ่งไปใช้กับอีกฟอร์แมตหนึ่ง

container ไหนใส่อะไรได้บ้าง

การเลือก container ต้องมาก่อนโค้ดแผนภูมิใด ๆ ทั้งสิ้น เพราะประเภท object ที่ใช้ได้แตกต่างกันระหว่างสองฟอร์แมตนี้:

• XLS (BIFF8): แผนภูมิอยู่บน chart sheet เฉพาะที่สร้างผ่าน AddChartSheet บน collection Sheets รูปภาพ text box สี่เหลี่ยม วงรี และเส้น คือ OfficeArt shape ที่จัดการผ่าน collection Shapes ของ worksheet ไม่มี API สำหรับฝังแผนภูมิไว้ข้างใน grid ของ worksheet ธรรมดา
• XLSX (OOXML): แผนภูมิสามารถฝังลงใน worksheet ได้โดยตรงด้วย TXLSXWorksheet.AddChart ยึดไว้กับสี่เหลี่ยมของเซลล์ หรือวางไว้บน chart sheet เฉพาะด้วย TXLSXWorkbook.AddChartSheet รูปภาพใส่เข้าไปด้วย AddImage หรือ AddImageFromFile และป้ายกำกับที่ลอยอยู่ด้วย AddTextBox

ดังนั้นความต้องการที่พูดว่า "sheet แดชบอร์ดที่มีแผนภูมิอยู่ข้าง ๆ ตัวเลข" จริง ๆ แล้วคือความต้องการสำหรับ .xlsx คุณทำได้ใกล้เคียงใน .xls ได้แค่ผลักแผนภูมิไปไว้บน sheet ของมันเอง ซึ่งเปลี่ยนวิธีที่ผู้ใช้นำทางไฟล์และเปลี่ยนวิธีที่โค้ดของคุณต้องทำงาน sheet ที่ได้กลับมาจาก AddChartSheet ฝั่ง XLS คือ chart substream ไม่ใช่ grid: การเขียนลงไปด้วย Cells.Item จะสร้าง drawing stream ที่ไม่สอดคล้องกัน ซึ่งสร้างเสร็จโดยไม่มี error ใด ๆ แล้ว Excel ก็จะทิ้งมันไปตอนเปิด แผนภูมิจะหายไปเฉย ๆ และไม่มีอะไรใน build log บอกว่าทำไม ให้ปฏิบัติต่อ sheet ที่ได้กลับมาว่าเป็น chart-only เท่านั้น แล้วรายงาน "แผนภูมิหายไป" ทั้งกลุ่มนี้จะหมดไปเลย

การฝังแผนภูมิลงใน worksheet ของ XLSX

เส้นทาง XLSX คือเส้นทางที่มีพื้นที่ให้ขยับได้ และเป็นจุดที่ระบบพิกัดสองแบบจากตอนต้นบทความกลายเป็นรูปธรรม สี่เหลี่ยมของจุดยึดที่ส่งให้ AddChart ถูกแสดงเป็นแถวและคอลัมน์ของ worksheet และกำหนดว่ากรอบแผนภูมิอยู่ตรงไหน ข้อมูล series ถูกแสดงเป็น A1 reference แบบสัมบูรณ์ที่รวมชื่อ sheet ไว้ด้วย ทั้งสองอย่างนี้เป็นอิสระจากกัน: คุณสามารถย้ายกรอบไปอยู่อีกฟากหนึ่งของ sheet ได้ และมันก็จะยังพล็อตเซลล์เดิมอยู่ดี

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'Region';
    Sheet.Cells[1, 2].Value := 'Revenue';
    Sheet.Cells[2, 1].Value := 'East';
    Sheet.Cells[2, 2].Value := 1184350;
    Sheet.Cells[3, 1].Value := 'Central';
    Sheet.Cells[3, 2].Value := 902210;
    Sheet.Cells[4, 1].Value := 'West';
    Sheet.Cells[4, 2].Value := 1010675;

    // กรอบยึดไว้กับแถว 6..22, คอลัมน์ 1..8
    Chart := Sheet.AddChart(xlsxChartColumn, 'Revenue by Region', 6, 1, 22, 8);
    Chart.AddSeries('Revenue', 'Sales!$A$2:$A$4', 'Sales!$B$2:$B$4');
    Chart.ValueAxisTitle := 'USD';

    Sheet.AddImageFromFile(1, 5, 'logo.png');
    Book.SaveAs('dashboard.xlsx');
  finally
    Book.Free;
  end;
end;

อาร์กิวเมนต์ที่กัดคุณได้คือสตริง range ที่ส่งให้ AddSeries มันเป็น literal ที่ถูกจับไว้ ณ ขณะที่เรียก และมันไม่รู้เลยว่าคุณอาจจะเพิ่มข้อมูลอีกยี่สิบแถวเข้าไปภายหลัง ให้สร้างมันจากจำนวนแถวที่คุณคำนวณหลังจากเขียนข้อมูลแล้วเท่านั้น ไม่ใช่ก่อนหน้านั้น แผนภูมิแบบ scatter และ bubble ใช้อาร์กิวเมนต์สองตัวเดียวกันแต่มีความหมายต่างกัน: range ของ category ตอนนี้ให้ค่า X และ range ของ value ให้ค่า Y ส่วนรัศมีของ bubble มาจาก reference ตัวที่สามที่ตั้งผ่าน BubbleSizeRange บน TXLSXChartSeries ที่ได้กลับมา ให้อ่านการเรียกนี้ว่า "X, Y, size" แทนที่จะเป็น "categories, values" เมื่อคุณออกจากกลุ่มแผนภูมิ column และ bar แล้ว

TXLSXChartType ครอบคลุมแผนภูมิแบบ column, bar, line, pie, area, doughnut, scatter, bubble และ radar ซึ่งครอบคลุมงานรายงานทั่วไปในชีวิตประจำวัน สำหรับแผนภูมิเต็มหน้าที่ไม่มี grid ล้อมรอบ Book.AddChartSheet จะคืนค่า sheet ที่ property IsChartSheet เป็น true มันคือคู่เทียบของ chart sheet รุ่นเก่าในฝั่ง .xlsx และมีข้อคาดหวังเดียวกัน: อย่าเขียนเนื้อหาเซลล์ลงไปในมัน

รูปภาพใส่เข้าไปในรูปแบบไบต์ และวัดขนาดเป็นหน่วย EMU

มี overload อยู่สองแบบสำหรับแทรกรูปภาพ และการสับสนระหว่างสองแบบนี้คือบั๊กด้านรูปภาพที่พบบ่อยที่สุดใน code review AddImage(ARow, ACol, AData, AFormat) ต้องการไบต์ของรูปภาพที่เข้ารหัสไว้แล้วใน AData: เนื้อหาดิบของ PNG, JPEG, GIF หรือ BMP ถ้าส่ง path ของไฟล์ให้มัน คุณก็จะเก็บสตริงยาวสี่สิบไบต์ที่ viewer ไหน ๆ ก็ถอดรหัสไม่ได้ ซึ่งเป็นรายงานไอคอนรูปภาพที่พังพอดี ที่คุณไม่อยากไปดีบักหลัง deploy เลย เมื่อแหล่งที่มาเป็นไฟล์บนดิสก์ ให้เรียก AddImageFromFile แทน แล้วปล่อยให้ไลบรารีอ่านไบต์และจัดหมวดหมู่ฟอร์แมตให้คุณเอง

จากนั้นก็มาถึงเรื่องขนาด DrawingML ไม่ได้วัดเป็นพิกเซล มันวัดเป็น English Metric Unit ซึ่ง 914400 EMU เท่ากับหนึ่งนิ้ว และที่ 96 DPI 9525 EMU เท่ากับหนึ่งพิกเซล object TXLSXImage เปิดให้ใช้ WidthEMU และ HeightEMU ดังนั้นโลโก้ที่ต้องการแสดงผลขนาด 180 คูณ 60 พิกเซลจึงต้องการ 1714500 คูณ 571500 EMU ให้ใส่การแปลงหน่วยนั้นไว้ในค่าคงที่ที่ตั้งชื่อไว้แล้วคำนวณเทียบกับมัน ตัวเลขมหัศจรรย์อย่าง 1714500 ที่กระจัดกระจายอยู่ทั่วโค้ดอ่านไม่รู้เรื่องและจะผิดอย่างเงียบ ๆ ทันทีที่มีใครสักคนเปลี่ยน DPI เป้าหมาย แถวและคอลัมน์ของจุดยึด อย่างที่บังเอิญพูดถึง เริ่มนับที่ 1 ตรงกับ cell API ส่วนที่เหลือ ไม่ใช่การคำนวณ EMU ที่เริ่มนับที่ 0

Chart sheet และ shape ในไฟล์ XLS รุ่นเก่า

ในฝั่ง BIFF8 overload ของ AddChartSheet ที่มีลูกเล่นมากกว่าจะรับประเภทแผนภูมิ ชื่อแกน และ open array ของ record TXLSChartSeriesInfo ที่แต่ละ record เก็บชื่อและ range ของ categories กับ values เป็นสตริง shape ที่ลอยอยู่เป็นเรื่องแยกต่างหาก: มันไปอยู่บน data worksheet เอง ผ่าน collection Shapes ของมัน ไม่ใช่บน chart sheet

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // นับ reference ผ่าน interface: อย่า Free
  Data := Book.Sheets.Add;
  Data.Name := 'Data';
  Data.Cells.Item[1, 1].Value := 'Month';
  Data.Cells.Item[1, 2].Value := 'Units';
  Data.Cells.Item[2, 1].Value := 'Apr';
  Data.Cells.Item[2, 2].Value := 1530;
  Data.Cells.Item[3, 1].Value := 'May';
  Data.Cells.Item[3, 2].Value := 1721;

  Series[0].Name := 'Units';
  Series[0].Categories := 'Data!$A$2:$A$3';
  Series[0].Values := 'Data!$B$2:$B$3';
  Trend := Book.Sheets.AddChartSheet('Trend', xlsChartTypeLine,
    'Units sold', 'Month', 'Units', Series);
  // Trend เป็น chart substream: ห้ามเรียกเมธอดของเซลล์กับมันเด็ดขาด

  Data.Shapes.AddTextBox('Source: ERP nightly export', 6, 1, 8, 4);
  Data.Shapes.AddPicture('approved-stamp.bmp');
  Book.SaveAs('trend.xls');
end;

มีรายละเอียดด้าน lifetime สองอย่างที่สำคัญตรงนี้ และมันดึงกันไปคนละทิศทาง TXLSWorkbook ถูกถือไว้ผ่าน interface IXLSWorkbook และนับ reference count ดังนั้นการเรียก Free กับมันเองจะทำให้ release ซ้ำสอง TXLSXWorkbook จากหัวข้อก่อนหน้าเป็น object ธรรมดาและต้อง free ใน try..finally code reviewer คนเดียวกันที่ตั้งข้อสังเกตว่าขาด Free ในฝั่ง XLSX ก็ต้องตั้งข้อสังเกตว่ามีอยู่ในฝั่ง XLS ด้วย ซึ่งเป็นกับดักที่เกิดขึ้นจริงเมื่อคุณทำงานกับทั้งสองฟอร์แมตในยูนิตเดียวกัน ตัวช่วยสร้าง shape เองมีความสม่ำเสมอ: AddRectangle, AddOval และ AddLine พร้อมกับ DeleteInRange ที่ล้างพื้นที่ของภาพวาด ทั้งหมดยึดตำแหน่งด้วยคู่แถวและคอลัมน์ ดังนั้น template ที่แทรกแถวไว้เหนือมันจะเลื่อนมันไปพร้อมกับ grid ด้วย

มี property อีกตัวหนึ่งที่คุ้มค่ากับไฟล์รุ่นเก่า TXLSPicture.TransparentColor จะปิดบังสีพื้นหลังที่เลือกไว้ออกจาก bitmap ซึ่งเป็นวิธีที่คุณวางตราประทับรูปทรงไม่ใช่สี่เหลี่ยม (ตราประทับ "Approved" ลายน้ำ) ทับ grid ในฟอร์แมตที่การ render แบบ BIFF ไม่เคยรู้จัก PNG alpha เลย ตั้งค่าสีที่ตราประทับถูกสร้างขึ้นมาด้วย แล้วสี่เหลี่ยมที่ล้อมรอบก็จะหายไป

สีธีมไม่รอดผ่านการไป-กลับของ BIFF8

การเติมสีของ drawing แบบ OOXML สามารถชี้ไปยังช่องสีธีมได้ ซึ่งเป็นเหตุผลที่การเปลี่ยนสีทั้งไฟล์ .xlsx ด้วยการสลับธีมของมันทำได้ง่ายและถูก BIFF8 drawing record ไม่มีช่องแบบนั้น เมื่อ HotXLS ใช้สีธีมกับภาพวาดแบบ XLS มันจะแปลงสีนั้นเป็นค่า RGB แบบ literal แล้วเก็บค่านั้นไว้ ดัชนีธีมที่มันมาจากจะหายไปทันทีที่ไฟล์ถูกเขียน และเปิดใหม่ก็กู้คืนไม่ได้ สิ่งนี้จะจับได้โดยเฉพาะกับเครื่องมือรายงานแบบ white-label ประเภทที่รีแบรนด์เอกสารที่สร้างขึ้นเดียวกันให้ลูกค้าหลายราย ให้เก็บการแม็ปธีมไปเป็น RGB ไว้ในการตั้งค่าของคุณเอง แล้วนำมาใช้ซ้ำทุกครั้งที่สร้างไฟล์ แทนที่จะคาดหวังว่าจะอ่านมันกลับออกมาจากไฟล์ .xls ที่บันทึกไว้แล้ว

มีการตัดสินใจที่เกี่ยวข้องกันปรากฏขึ้นในด้านประสิทธิภาพด้วย XLS facade สามารถสั่งให้ข้ามการ parse ชั้นภาพวาดไปทั้งหมดได้ เมื่อสิ่งที่คุณต้องการจากไฟล์รุ่นเก่าขนาดใหญ่คือข้อมูลเซลล์เท่านั้น โดยตั้งค่า _DisableGraphics เป็น true ซึ่งจะลดเวลาจริงในการอ่านแบบ bulk ลงได้ ข้อเสียคือถาวร: workbook ที่เปิดด้วยวิธีนี้จะไม่มี OfficeArt stream อยู่ในหน่วยความจำเลย ดังนั้นการบันทึกมันจะเขียนภาพวาดออกไปจากการมีอยู่เลย สงวน flag นี้ไว้สำหรับงาน analytics แบบอ่านอย่างเดียวเท่านั้น ภาพรวมด้านประสิทธิภาพที่กว้างกว่านี้อยู่ในบันทึกของเราเรื่องประสิทธิภาพของ workbook ขนาดใหญ่ใน HotXLS

การรักษาจุดยึดให้เสถียรขณะที่ grid เปลี่ยนแปลง

รายงานแทบไม่เคยคงขนาดเดิมไว้เท่ากับตอนที่สร้างขึ้นมา และนี่คือจุดที่โมเดลจุดยึดจากตอนต้นบทความให้ผลตอบแทน การดำเนินการเชิงโครงสร้างของ XLSX facade (InsertRows, DeleteRows และตัวที่เทียบเท่าสำหรับคอลัมน์) จะย้ายชั้นที่พึ่งพาไปพร้อมกับเซลล์ด้วย พื้นที่ที่ merge ไว้ ไฮเปอร์ลิงก์ คอมเมนต์ frozen pane, filter range, conditional format, validation, ตาราง defined name และสำหรับหัวข้อนี้ก็คือจุดยึดของรูปภาพและแผนภูมิ ล้วนเดินทางไปด้วยกันทั้งหมด โลโก้ที่ยึดไว้ที่แถว 1 จะยังอยู่ด้านบนเมื่อมีสิบแถวถูกใส่เข้าไปข้างล่าง กรอบแผนภูมิที่ยึดไว้ใต้บล็อกข้อมูลจะเลื่อนลงเมื่อบล็อกนั้นโตขึ้น สิ่งเดียวที่จะไม่ถูกเขียนใหม่คือสตริง range ใด ๆ ที่คุณจับไว้เป็น literal ก่อนที่การแทรกจะเกิดขึ้น เพราะมันเป็นแค่ข้อความที่ไลบรารีไม่มีเหตุผลจะย้อนกลับไปแก้ นั่นคือสิ่งที่กำหนดลำดับที่ปลอดภัยสำหรับการกรอกข้อมูลลงใน template: เขียนและจัดรูปร่างข้อมูลก่อน แล้วสร้างแผนภูมิและวางรูปภาพเป็นขั้นตอนสุดท้าย โดยสตริง range ทุกตัวมาจากจำนวนแถวที่คุณมีหลังจากการแทรกแล้ว ไม่ใช่ก่อนหน้านั้น

มีเครื่องมือเล็ก ๆ อีกสองอย่างที่เติมชุดเครื่องมือด้านการจัดวางให้สมบูรณ์ TXLSTextBox.SetArea ในฝั่ง XLS จะย้ายจุดยึดของ text box หรือ auto shape ที่มีอยู่แล้วไปยังสี่เหลี่ยมของเซลล์ใหม่ ซึ่งดีกว่าการลบแล้วสร้างใหม่เมื่อบล็อก footer เลื่อนตำแหน่ง และ overload แบบ bitmap ของ AddPicture รับ TBitmap ที่มีชีวิตอยู่จริง พร้อม flag โปร่งใสที่เป็นตัวเลือกได้ ดังนั้นอะไรก็ตามที่โค้ด VCL ของคุณเองวาดได้ (เกจวัด แถบ sparkline ประเภทแผนภูมิที่รายการ native ไม่มีให้) ก็สามารถประทับตราลงใน sheet ได้โดยตรงโดยไม่ต้องเขียนไฟล์ชั่วคราวก่อนเลย

แผนภูมิและรูปภาพแทบจะเป็นชั้นสุดท้ายที่เติมเข้าไปบนรายงานที่มีโครงสร้างอยู่แล้วเสมอ ซึ่งเป็นเหตุผลที่งานพื้นฐานเป็นตัวกำหนดว่ามันจะลงตัวได้เรียบร้อยหรือไม่ การกรอกข้อมูลที่แผนภูมิจะอ้างอิงถึงอธิบายไว้ในการสร้างรายงานแบบขับเคลื่อนด้วย template และการรักษา grid ให้เสถียรอยู่ใต้จุดยึดของคุณเป็นหัวข้อของการ merge เซลล์และการควบคุมเลย์เอาต์ เอกสาร class และเมธอดฉบับเต็มอยู่ที่หน้าผลิตภัณฑ์HotXLS Delphi Component