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 ออกมาสวยงามในแบบที่เราสั่งได้

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s