How to Export From Listview to Excel Template

สำหรับ K2 Five ตั้งแต่ version 5.1 ขึ้นไป จะมีความสามารถในการ Export List to Excel อยู่แล้วครับ แต่ในกรณีที่เราต้องการ export excel แบบมีการเตรียม format ไว้ก่อน ต้องทำอย่างไรบ้าง วันนี้ได้รับเกียรติจาก K.Susy มาแชร์ ว่าต้องทำอย่างไรบ้าง เชิญติดตามได้เลยครับ

           สวัสดีค่ะวันนี้เรามาแชร์ประสบการณ์ดีๆ  ใน K2 กันคะ คุณเคยเจอไหมที่ลูกค้าอยากได้   Report  Excel สวยงามแบบนี้ เรามาดูขั้นตอนการทำกันเลยค่ะ

 ขั้นตอนที่ 1 การติดตั้ง และตั้งค่าใน K2 Management

1.1 ติดตั้ง  “Microsoft Access Database Engine.exe”  ซึ่งเป็น Service ให้ SQL Server ติดต่อกับ Excel  ได้เราสามารถ download ที่ https://www.microsoft.com/en-us/download/details.aspx?id=13255

ภาพที่ 1 ติดตั้ง  Microsoft Access Database Engine

1.2 Config K2 ใน  Management ส่วนที่เป็น Service Instance กำหนดให้ Suppress Transection = True  และ User ที่ Connect DB ต้องเป็น Admin นะคะ ไม่งั้นเวลาเรา Execute  Store Procedure ผ่าน K2 จะ   Error   ค่ะ

ภาพที่ 2 การ Config  K2 Service Instance

1.3 ติดตั้ง Excel/CSV Import File Service Broker สามารถ download ได้ที่

https://community.nintex.com/t5/K2-Five-blackpearl/Excel-CSV-Import-Service-Broker/ta-p/176973 (เมื่อโหลดเสร็จจะมีตัวอย่างการตั้งค่า K2 Management อย่าลืมทำกันนะคะ)

ภาพที่ 3 ติดตั้ง Service File Broker และการ Add Service Type

ขั้นตอนที่ 2 การออกแบบ Excel Template ตามที่เราต้องการ

การออกแบบ Template Excel นั้นสิ่งสำคัญคือ Data Type ใน Excel ต้อง Match กับ Data Type ใน DB หากไม่ได้นำไปใช้คำนวณสูตรใดๆ แนะนำ Set  Column เป็น Text ไปเลยค่ะ  ส่วนที่เป็น Input data ที่จะนำข้อมูลจาก ListView มาแสดงนั้น ไม่ควรตีตาราง/หรือตีเส้น ต้องปล่อยให้เป็น Cell ว่างๆ เพราะจะทำให้ข้อมูลที่ส่งมาจาก DB ถูกต่อท้ายที่เรากระทำการใส่สี/ตีเส้นทันที 

ภาพที่ 4 ตัวอย่าง Excel Template

หากกรณีเราถ้าต้องการให้ส่วนที่เป็น  Input data ตีเส้น/ใสสีให้สวยงามแนะนำให้ใช้ Rule วันนี้เราก็มี ตัวอย่างการใช้ Rule มาดูกันเลยค่ะ

ภาพที่ 5 วิธีการตีเส้นให้กับตาราง Input data ด้วย Rule

*Highlight  Column ที่ต้องการก่อนเลือก “Conditional Formatting”

ภาพที่ 6 วิธีการเปลี่ยน สีพื้นหลัง row เมื่อ data ใน Row ไม่เหมือนกับ Row ด้านบน

Highlight  Column ที่ต้องการก่อนเลือกแล้วไปที่  “Conditional Formatting”  วิธีนี้เราจะใช้เทคนิคการ MOD แต่ใน Excel เราจะสร้าง Column MOD รองรับไว้ก็พอไม่ต้องเขียนสูตร MOD ใน Excel เพราะเราจะไปทำใน DB แทน และอย่าลืม Hide Column MOD ด้วยนะคะ

ขั้นตอนที่ 3 สร้าง Folder ใน Server  และ Copy Excel Template ที่เราเตรียมไว้

 เมื่อเราทำการสร้าง Excel Template เสร็จแล้วให้นำไฟล์ Excel ไปวางไว้ใน Server Path ที่เราต้องการได้เลยค่ะ ในที่นี้จะทำ 2 Folder ค่ะ (Folder หนึ่งไว้สำหรับเก็บ Template Excel ต้นฉบับ และ อีก Folder ไว้เก็บไว้ Excel ที่ Gen เสร็จแล้วค่ะ)  

ภาพที่ 7 ตัวอย่างการนำ Excel Template ไปวางไว้ใน Server

ขั้นตอนที่ 4 การเขียน Store Procedure ใน K2 Designer

ตัวอย่าง การเขียน SQL Command ใน Store Procedure
*ข้อควรระวังหาก Module นี้มี User ใช้มากกว่า 1 คนชื่อ File ที่ Genไม่ควรซ้ำกัน แนะนำไปประยุกต์ใช้กันเองนะคะอันนี้เป็นแค่ตัวอย่าง และอย่าลืมตั้ง Password ให้ยากต่อการเดาด้วยนะคะเพราะเราจะเปิด Cmdshell ใช้งาน

USE [Your_DBNAME]
GO
/****** Object:  StoredProcedure [dbo].[Procedure_ExportToExcelTemplate]    Script Date: 9/25/2021 12:03:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Procedure_ExportToExcelTemplate]  AS
begin
-----กรณี cmdshell เขียนถูกต้องแต่ error ให้ run คำสั่งนี้
--exec sp_configure 'show advanced options', 1
--reconfigure
--exec sp_configure 'xp_cmdshell', 1
--reconfigure
--exec sp_configure 'show advanced options', 0
--reconfigure

-----กรณี Openrowset excel เขียนถูกต้องแต่ error ให้ run คำสั่งนี้
--sp_configure ‘show advanced options’, 1;
--RECONFIGURE;
--sp_configure ‘Ad Hoc Distributed Queries’, 1;
--RECONFIGURE;
--GO

--คำสั่ง Copy File จาก Excel Template ไปเก็บไว้อีก Floder เพื่อเก็บไว้ที่ Gen เสร็จเรียบร้อย
exec master..xp_cmdshell 'del C:\K2\TestExcelTemplate\FileGenerate\FileGenerateExcel.xlsx'
exec master..xp_cmdshell 'copy  C:\K2\TestExcelTemplate\Template\FileGenerateExcel.xlsx C:\K2\TestExcelTemplate\FileGenerate'  
--คำสั่ง Query ข้อมูลจาก DB แล้วนำไป insert ข้อมูลใส่ Excel Template
insert into 
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml; HDR=yes; Database=C:\K2\TestExcelTemplate\FileGenerate\FileGenerateExcel.xlsx',[Sheet1$])
--query ข้อมูลให้ตรง Column ใน Excel
select 
 [Year] 
,[field_id]
,[Field_Name] 
,[Field_Name_abbr] 
,[FieldQue_Name]
,[Score] 

--*MOD หาค่า เศษ 1 กับ 0 แต่ในที่นี้เราจะแปลง 0=A ,1=B เนื่องจากถ้าเราใช้ 0,1 Column ที่เป็นค่าว่างจะถูกเทสีไปด้วยซึ่งเราไม่ต้องการ
--เทคนิคง่ายๆ ก็คือ แปลงให้เป็น A,B
,CASE
  WHEN (DENSE_RANK() OVER (ORDER BY a.[field_id])%2)= 0
  THEN 'A'
  ELSE 'B'
END  as [MOD] 
FROM  [Plan_MT_Question_Field] a

end;

ขั้นตอนที่ 5 สร้าง SmartObject ใน K2 Designer

5.1 สร้าง Smart Object ไว้สำหรับให้ K2 Execute Store Procedure

5.2 สร้าง SmartObject เพื่อเรียกใช้ File Service Broker ที่ใช้สำหรับ Download File จาก Server

5.3 สร้าง SmartObject ให้ ListView

ขั้นตอนที่ 6 สร้าง View ใน K2 Designer

6.1 สร้าง View เพื่อแสดงข้อมูล

6.2 สร้าง View ให้ File Broker

ขั้นตอนที่ 7 การสร้าง Form ใน K2 Designer

ผลลัพธ์ไฟล์ Excel ออกมาสวยงามในแบบที่เราสั่งได้

Sync custom Active Directory Fields to K2 SmartObject

ปกติแล้ว เวลาเราจะ Sync user properties จาก Active Directory ใน K2 เราก็มักจะใช้ Service Broker ที่ชื่อว่า AD Service2 แล้วก็เรียกใช้ Method GetUsers / GetUserDetails จาก AD User SmartObject

AD user

ซึ่งเราก็จะได้ Field มาตรฐานที่ใช้กันบ่อยๆ ประมาณนึง เช่น

ad user prop

ปัญหาคือ ถ้าใน Active Directory เรามีการสร้าง Custom Field เช่น EmployeeID แล้วเราต้องการเอา Field นั้นมาใช้งานใน K2 เราจะทำได้อย่างไร

นี่เป็นที่มาของการใช้ Dynamic Active Directory Service Broker ซึ่งสามารถโหลดได้ผ่าน Community ที่นี้เลย

โดยหลักการทำงานของ Service Broker นี้จะเปิดทางให้เราสามารถ Add Custom Field ได้ผ่าน Tools ที่ชื่อว่า Service Schema Configurator

service schema

โดยเมื่อเราทำการ Add Field ที่เราต้องการแล้วก็ให้ Save Schema แล้วก็ Refresh Service Broker นี้อีกครั้ง เราก็จะเห็น Field ใหม่มากับ SmartObject ล่ะ

DynamicADSMO

ถึงขั้นนี้เราก็สามารถนำ SmartObject นี้ไปใช้งาน พร้อมทั้งเห็นค่า Field Custom ที่เรา Config ไว้ได้แล้วนั้นเอง

Register K2 for SharePoint with SharePoint O365 for K2 Five

พอดีมีโอกาสจะต้อง Config K2 for SharePoint ของ K2 Five เพื่อ Upload เอกสารจาก K2 ขึ้น SharePoint O365 ซึ่งเจอปัญหาระหว่างทางพอสมควร จึงอยากเขียน Blog นี้มา เพื่อทำเป็น Reference เผื่อในอนาคตนะครับ เผื่อใครที่จะเจอปัญหาเดียวกัน

ก่อนจะเริ่มขออ้างอิงสมัยก่อน แต่ก่อนนั้น ตอนที่เป็น K2 4.7 และ O365 ใหม่ๆ เราจะสามารถโยนเอกสารขึ้น SharePoint ได้ง่ายๆ โดย Register ผ่าน Service Instance ที่ชื่อว่า SharePoint Content ซึ่งเคยเขียนรายละเอียดไว้ที่ Blog นี้

แต่อนิจจา Service Instance นี้ได้หายไปจาก K2 Five เนื่องด้วยเหตุผลที่ว่า O365 ต้องเป็นการ Authentication ผ่าน oAuth เท่านั้น จึงทำให้กระบวนการ COnfiguration จะยากขึ้นหน่อย แต่ไม่ยากเกินไปครับ

ก่อนอื่นเลย จะต้องมี Prerequisite ดังนี้ครับ

  • K2 ต้องมี SSL ซึ่งก็คือ URL ของ K2 ต้องเป็น https นั้นเอง
  • เครื่อง K2 จะต้องเปิด Site เหล่านี้ เพื่อให้ O365 วิ่งลงมาเชื่อมต่อได้
    • /Runtime
    • /Identity
    • /autodiscover
  • เครื่อง K2 จะต้องออก Internet ได้ เพราะว่าต้องวิ่งไปหา trust.k2.com
  • ต้องใช้ Account ที่เป็น Tenant Admin ของ 365 เพราะว่าต้องสร้าง App Catalog ด้วย
  • Tenant Admin นั้นต้องมีสิทธิ์เป็น Admin ใน Server Right ของ K2
  • K2 กับ O365 ใช้ AAD เดียวกัน

ถ้าเราทำ Prerequisite เรียบร้อย เราก็พร้อมล่ะ มาเริ่มกันได้เลย

1. Set up App Catalog
ก็เข้าไปที่ O365 Portal Admin แล้วไปที่ SharePoint Admin Center แล้วกด Open ใน Menu Apps

หลังจากนั้น Click App Catalog แล้วก็ใส่ชื่อ App Catalog ที่เราต้องการ

เมื่อสร้างเสร็จก็จะได้ App Catalog หน้าตาประมาณนี้

2. Deploy K2 for SharePoint App ขึ้น App Catalog

ในกรณีที่เราต้องการเชื่อมต่อระหว่าง K2 on Premise ไปหา O365 ทาง K2 แนะนำให้ Deploy App จาก Folder Installer ขึ้นไป ซึ่งอยู่ที่ Installation/SharePoint Online/K2 for SharePoint.app เพื่อ Make sure ว่า App จะตรง version กัน

แต่ในกรณีที่เราต้องการเชื่อมต่อระหว่าง K2 Cloud ไปหา O365 อันนี้ให้ไป Download K2 Five for SharePoint จาก SharePoint Store ได้เลย

3. เมื่อทำการ Deploy App เสร็จ ก็ให้ไปที่ Add an app เพื่อ App “K2 for SharePoint” ขึ้นมาใน App Catalog

4. เมื่อ Add เสร็จแล้ว App นั้นจะไปอยู่ใน Site Contents เราก็ Click ที่ icon เพื่อทำการ Register ได้เลย

5. มาถึงขั้นตอนสำคัญ เมื่อ Click ไปจะขึ้นหน้า Wizard เพื่อให้เราใส่ URL ของ K2 ของเรา ซึ่งจำเป็นจะต้องใส่ https นะ เช่น ในที่นี้ เราก็ใส่ https://ventek-k2-53 แล้วกด Next ซึ่งการทำงานส่วนนี้ทาง K2 จะไปหา Description ของ Site ต่างๆ ผ่าน /autodiscover เพื่อทำการ Register ให้เรา (ผ่านหน้า relayingAdd)

6. จะมีการ Link ไปหา K2 URL จาก O365 เพื่อให้เราใส่ Login อีกครั้ง ถ้าสังเกตดู จะเห็นว่าตอนนี้ URL จะเป็นของ K2 ล่ะ ตรงนี้ให้เราใส่ Tenant Admin เหมือนเดิม เพื่อทำการ Register นะ ไม่อย่างนั้น จะเกิดปัญหา Authentication Error แบบนี้

ถ้าลงได้เรียบร้อยจะได้หน้าตาแบบนี้ ก็ปล่อยให้มันหมุนๆจนเสร็จ

สำหรับคนที่เจอปัญหา 404 File not found ซึ่งมักจะเป็นในกรณีที่มีการทำ Reverse Proxy

ให้ลองเข้า /Runtime/Runtime/Form/K2+Application+Configure+Server/ ดูว่าเข้าได้มั้ยเพราะว่าบาง Proxy เมื่อเห็น + ใน URL จะทำการ Ignore ไป เราต้องไป Allow มันนะ

สุดท้ายแล้วจะได้หน้าตาประมาณนี้มา

ณ ขั้นตอนนี้ คือเราทำการ Register เชื่อมต่อระหว่าง K2 ของเรา กับ O365 เป็นที่เรียบร้อย

7. ทำการ Registration Wizard โดย Click ที่ Menu “Registration Wizard” ภายใต้ Administration

8. Deploy App มาเราจะใช้ K2 for SharePoint ที่ Site ไหนบ้าง โดย Click ที่ Menu “Manage App Deployments (SharePoint)

ขั้นตอนนี้จะให้เราใส่ Site Collection บน O365 ที่เราต้องการ Enable App นี้ เราก็ใส่ URL ไปแล้วเลือก Apply All Paths ก็เป็นอันจบ

9. Activate App

เมื่อเราทำการ Setting ทั้งหมดเสร็จแล้วก็มาทำการ Activate App ภายใต้ Menu “Manage App Activation” ซึ่งจะให้เราเลือก Site Collection แล้วก็ทำการ Activate ได้เลย

ณ ตอนนี้เราลง App สมบูรณ์แล้ว ขั้นตอนต่อไปก็จะไปสร้าง SMO ที่ Site Collection ที่เราเชื่อมต่อ

10. สร้าง SMO ที่ Site Collection

ให้ไปที่ Site Contents แล้วเลือก Documents Library ที่เราต้องการ Upload เอกสารไป ที่ Site Contents

จะเห็นว่าที่ Ribbon Menu จะมี Menu K2 ขึ้นมาล่ะ เราก็เลือก Application

เราก็เลือกจะสร้าง SMO ซึ่งพอเสร็จขั้นตอนนี้ เราจะเห็นว่าจะมีการสร้าง Service Instance ใหม่ให้เรา ภายใต้ SharePoint 2013 Service Instance แล้วเราก็จะสามารถสร้าง SMO จาก Document Library ได้ล่ะ

servicetester

โดย SMO นี้ก็จะมี Method ให้เราใช้หลายอย่าง แต่ที่เราจะใช้ก็คือ Upload Documents นั้นเอง

ลองเอา test.txt ขึ้นไป ก็จะได้ประมาณนี้บน O365

เป็นอันว่าจบกระบวนการ

Key Takeaway ก็คือ

  1. ใช้ Account Tenant ในการ Login และเชื่อมต่อ ของทั้งกระบวนการ
  2. ถ้ามีการใช้ Reverse Proxy ต้องระวังเรื่องเครื่องหมาย + ที่โดน Ignore
  3. ถ้าเจอ Error เรื่อง Authenticate SignoutURL ลอง run อีกรอบ เพราะว่าเจอเหมือนกัน

K2Blackboard คืออะไร ใครควรดูบ้าง?

K2Blackboard เป็น VDO คลิปภาษาไทยสั้นๆ สอนการใช้งาน K2 ทุกเรื่อง ไม่ว่าจะเป็น Smartform, workflow, smartobject และเรื่องอื่นๆที่เกี่ยวข้อง ตั้งแต่เริ่มต้น ผ่านทาง Youtube และ Facebook เอาไว้สำหรับคนที่อยากเริ่มต้น ทบทวนและหาแรงบันดาลใจ

 

ติดตามคลิปใหม่ได้ทุกวันจันทร์เช้าทุกสัปดาห์ หวังว่าจะชอบ ฝากไว้ในอ้อมใจด้วยครับ