ความเดิมตอนที่แล้ว ผู้เขียนได้พูดถึงความสำคัญของข้อมูล และวิธีการ Import ข้อมูลจากไฟล์ Excel กันไปพอสมควร สำหรับในบทความนี้ ผู้เขียนจะขอพูดถึงวิธีการส่งออกข้อมูล หรือที่เรามักเรียกกันติดปากว่า “Export ข้อมูล” กันบ้าง เพื่อให้ผู้พัฒนาที่มีความสนใจสามารถพัฒนาโปรแกรมได้ครบวงจรทั้งแบบนำเข้าและส่งออกข้อมูล โดยผู้เขียนจะไม่ขอพูดถึงในรายละเอียดที่ได้กล่าวไว้แล้วก่อนหน้า ผู้อ่านสามารถอ่านรายละเอียดเพิ่มเติมได้ในบทความ “เรียนรู้วิธีการ Import ข้อมูลในรูปแบบไฟล์ Excel ด้วย ASP.NET (C#)” สำหรับในบทความนี้ผู้เขียนจะเน้นในส่วนของการ Export ข้อมูลเท่านั้น ซึ่งจะมีการอธิบายใน 2 ลักษณะเช่นกัน คือ แบบ Single sheet และแบบ Multiple sheet เพื่อให้เห็นเป็นแนวทางและสามารถนำไปต่อยอดการพัฒนาเพิ่มเติมสำหรับแต่ละท่านได้
กรณีส่งออกข้อมูล(Export) ซึ่งในบทความนี้จะแบ่งเป็น 2 ลักษณะ ดังนี้
- การส่งออกข้อมูลแบบ Single-sheet ซึ่งมีขั้นตอนการทำงานไม่ซับซ้อนมากนัก ดังนี้
protected void btnExport_Click(object sender, EventArgs e)
{
//// ตารางสมมติ สร้างขึ้นเพื่อให้ผู้พัฒนาเห็นภาพ หากเป็นกรณีใช้งานจริงจะเป็นข้อมูลที่ดึงจากฐานข้อมูลเพื่อ Export ในรูปแบบไฟล์ Excel
DataTable table = new DataTable();
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Latitude", typeof(decimal));
table.Columns.Add("Longitude", typeof(decimal));
table.Columns.Add("Description", typeof(string));
table.Rows.Add("University1", 7.006923, 100.500238, "Desc1");
table.Rows.Add("University2", 7.172661, 100.613726, "Desc2");
StringBuilder sb = new StringBuilder();
if (table.Rows.Count > 0)
{
string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls");
//// ลักษณะการตั้งค่าเพื่อเชื่อมต่อด้วย OleDb ซึ่งในกรณีนี้ไฟล์ Excel จะต้องมีนามสกุลเป็น .xls แต่หากเป็นนามสกุลแบบ .xlsx ต้องเปลี่ยนการกำหนดค่าให้เป็น
conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"; แทน
string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
using (OleDbConnection con = new OleDbConnection(conString))
{
////เขียนคำสั่งในการสร้างตาราง ซึ่งในที่นี้คือ WorkSheet ที่ต้องการ พร้อมทั้งกำหนดชื่อและชนิดของข้อมูลในแต่ละคอลัมน์
string strCreateTab = "Create table University (" +
" [Name] varchar(50), " +
" [Latitude] double, " +
" [Longitude] double, " +
" [Description] varchar(200)) ";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
////รันคำสั่งที่เขียนในการสร้างตาราง
OleDbCommand cmd = new OleDbCommand(strCreateTab, con);
cmd.ExecuteNonQuery();
////เขียนคำสั่งในการเพิ่มข้อมูล(insert) ข้อมูลในแต่ละฟิลด์ รวมทั้งประกาศพารามิเตอร์ที่ใช้ในการรับค่าข้อมูลที่อ่านได้
string strInsert = "Insert into University([Name],[Latitude]," +
" [Longitude], [Description]" +
") values(?,?,?,?)";
OleDbCommand cmdIns = new OleDbCommand(strInsert, con);
cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
cmdIns.Parameters.Add("?", OleDbType.Double);
cmdIns.Parameters.Add("?", OleDbType.Double);
cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
////วนค่าที่ได้จากฐานข้อมูลและกำหนดค่าให้กับพารามิเตอร์และรันคำสั่งในการเพิ่มข้อมูลทีละรายการ
foreach (DataRow i in table.Rows)
{
cmdIns.Parameters[0].Value = i["Name"];
cmdIns.Parameters[1].Value = i["Latitude"];
cmdIns.Parameters[2].Value = i["Longitude"];
cmdIns.Parameters[3].Value = i["Description"];
cmdIns.ExecuteNonQuery();
}
}
////สร้างไฟล์ที่ต้องการดาวน์โหลดจากการอ่านที่ได้ทั้งหมด เพื่อบันทึกเป็นไฟล์ Excel ที่มีชื่อว่า University.xls
byte[] content = File.ReadAllBytes(fileName);
HttpContext context = HttpContext.Current;
context.Response.BinaryWrite(content);
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=University.xls");
Context.Response.End();
}
}
- การส่งออกข้อมูลแบบ Multiple sheet ใช้ในกรณีที่มีข้อมูลที่ Export จำนวนมากและต้องการแบ่งข้อมูลที่มีอยู่ออกเป็น WorkSheet ย่อย ซึ่งหลักการทำงานจะคล้ายกับการส่งออกข้อมูลแบบ Single-sheet แต่จะซับซ้อนกว่าในส่วนของการวนค่าจากในฐานข้อมูลมาใส่ใน WorkSheet โดยมีการกำหนดจำนวนแถวที่จะให้บันทึกในแต่ละ WorkSheet หากเกินค่าที่กำหนดจะสร้าง WorkSheet ใหม่และบันทึกข้อมูลลงไปใน WorkSheet นั้น
static StringBuilder sqlInsert = new StringBuilder();
static StringBuilder strScript = new StringBuilder();
public static void ExportToMultipleXLSheets( System.String OutputFileName)
{
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
mOutputFileName + ";Extended Properties='Excel 8.0'";
OleDbConnection xlConn = new OleDbConnection(connstr);
try
{
xlConn.Open();
////ตารางสมมติเช่นเดียวกับที่ได้กล่าวไว้ในแบบการส่งออกข้อมูลแบบ Single sheet
DataTable table = new DataTable();
table.Columns.Add("EmployeeID", typeof(string));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("LastName", typeof(string));
table.Columns.Add("Position", typeof(string));
table.Rows.Add("0001", "Jack", "Rayman", "Programmer");
table.Rows.Add("0002", "John", "Wicky", "Programmer");
table.Rows.Add("0003", "Jenifer", "Wincy", "Programmer");
table.Rows.Add("0004", "Kate", "Wrapper", "Manager");
table.Rows.Add("0005", "Bella", "Cole", "Programmer");
table.Rows.Add("0006", "Sandy", "Stick", "Programmer");
table.Rows.Add("0007", "Tom", "Runner", "Programmer");
////เป็นการเรียกใช้เมธอดที่ใช้ในการเตรียมคำสั่งที่จะใช้วนสร้างตาราง/Worksheet
PrepareScript(table);
////เป็นเมธอดที่ใช้ในการเริ่มต้นทำงานคำสั่งในการ Export โดยจะมีการคำนวณจำนวนแถวของข้อมูลว่าเกินที่ระบุไว้หรือไม่ หากเกินกำหนดจะสร้าง WorkSheet ใหม่นั่นเอง
StartExport(table, xlConn);
if (xlConn != null)
{
if (xlConn.State == ConnectionState.Open) xlConn.Close();
xlConn.Dispose();
}
////อ่านค่าและ Export ไปยังไฟล์ที่มีชื่อว่า ExportMultiSheetData.xls
byte[] content = File.ReadAllBytes(mOutputFileName);
HttpContext context = HttpContext.Current;
context.Response.BinaryWrite(content);
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=ExportMultiSheetData.xls" );
context.Response.End();
}
catch (Exception exp)
{
throw new Exception("ImportToMultipleXLSheets", exp.InnerException);
}
finally
{
if (xlConn != null)
{
if (xlConn.State == ConnectionState.Open) xlConn.Close();
xlConn.Dispose();
}
}
}
o เมธอดในการเตรียมคำสั่งที่ใช้ในการสร้างตารางและเพิ่มข้อมูล
private static string PrepareScript(DataTable DTable)
{
// เตรียมคำสั่งในการสร้าง WorkSheet
sqlInsert.Length = 0;
strScript.Length = 0;
////วนค่าเพื่ออ่านค่าคอลัมน์ที่มีในแต่ละตาราง
for (int i = 0; i < DTable.Columns.Count; i++)
{
sqlInsert.Append( "[" + DTable.Columns[i].ColumnName + "],");
strScript.Append("[" + DTable.Columns[i].ColumnName.Replace("'", "''") + "]");
////กำหนดชนิดของข้อมูลแต่ละคอลัมน์
if (DTable.Columns[i].DataType.ToString().ToLower().Contains("int") || DTable.Columns[i].DataType.ToString().ToLower().Contains("decimal"))
strScript.Append(" double");
else
strScript.Append(" text");
strScript.Append(", ");
}
sqlInsert.Remove(sqlInsert.Length - 1, 1);
strScript.Remove(strScript.Length - 2, 1);
strScript.Append(") ");
////ผลที่ได้ :
sqlInsert >> [EmployeeID],[Name],[LastName],[Position]
strScript >> [EmployeeID] text, [Name] text, [LastName] text, [Position] text )
เพื่อนำไปใช้ในการ run คำสั่งการทำงานสร้าง (create table) และเพิ่มข้อมูล (insert)
return strScript.ToString();
}
o เมธอดในการสร้างตาราง/WorkSheet ตามคำสั่งที่เตรียมไว้
private static void CreateXLSheets(DataTable DTable,
OleDbConnection xlConn, System.String XLSheetName)
{
// สร้าง WorkSheet ใหม่
System.Text.StringBuilder SqlFinalScript = new System.Text.StringBuilder();
OleDbCommand cmdXl = new OleDbCommand();
try
{
SqlFinalScript.Length = 0;
cmdXl.Connection = xlConn;
///สร้างคำสั่งในการสร้างตาราง/WorkSheet ขึ้นใหม่ โดยตั้งชื่อตามพารามิเตอร์ที่ได้รับมา
SqlFinalScript.Append("CREATE TABLE " + XLSheetName + " (");
SqlFinalScript.Append(strScript.ToString());
cmdXl.CommandText = SqlFinalScript.ToString();
cmdXl.ExecuteNonQuery();
}
catch (Exception xlSheetExp)
{
throw (new Exception("CreateXLSheetException", xlSheetExp.InnerException));
}
finally
{
cmdXl.Dispose();
}
}
private static void StartExport(DataTable DTable, OleDbConnection xlConn)
{
Int64 rowNo = 0, xlSheetIndex = 1, TotalNoOfRecords = 0;
System.String NewXLSheetName = "Sheet";
System.Text.StringBuilder strInsert = new System.Text.StringBuilder();
TotalNoOfRecords = DTable.Rows.Count;
OleDbCommand cmdXl = new OleDbCommand();
cmdXl.Connection = xlConn;
for (int count = 0; count < DTable.Rows.Count; count++)
{
strInsert.Length = 0;
////กรณีที่เป็นแถวแรกจะทำการสร้าง WorkSheet ขึ้นใหม่ โดยเรียกใช้เมธอด CreateXLSheets
if (rowNo == 0 )
{ CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
}
rowNo += 1;
////กรณีที่จำนวนแถวใน 1 WorkSheet เกินที่กำหนดไว้จะสร้างชีทใหม่ ในกรณีนี้คือจะสร้าง WorkSheet ใหม่ทุกๆ 3 แถว และเพิ่มลำดับของ index ของ WorkSheet คราวละ 1
if (TotalNoOfRecords > 3 && rowNo > 3
{
xlSheetIndex += 1;
CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
rowNo = 1;
}
////เขียนคำสั่งในการเพิ่มข้อมูลไปยัง WorkSheet ที่กำหนด โดยมีการแทนที่ค่าของคำสั่งที่เตรียมไว้แล้วก่อนหน้านี้ในตอนเรียกใช้เมธอด PrepareScript
strInsert.Append("Insert Into [" + NewXLSheetName + xlSheetIndex.ToString() + "$](" + sqlInsert.ToString() + ") Values (");
foreach (DataColumn dCol in DTable.Columns)
{
if (dCol.DataType.ToString().ToLower().Contains("int"))
{
if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
strInsert.Append("NULL");
else
strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
}
else if (dCol.DataType.ToString().ToLower().ToLower().Contains("decimal"))
{
if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
strInsert.Append("NULL");
else
strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
}
else
strInsert.Append("\"" +
DTable.Rows[count][dCol.Ordinal].ToString().Replace("'",
"''") + "\"");
strInsert.Append(",");
}
strInsert.Remove(strInsert.Length - 1, 1);
strInsert.Append(");");
////run คำสั่งที่สร้างขึ้นในการเพิ่มข้อมูลทีละรายการ
cmdXl.CommandText = strInsert.ToString();
cmdXl.ExecuteNonQuery();
}
}
o เขียนการทำงานเมื่อกดปุ่ม “Export”
protected void btnExport_Click(object sender, EventArgs e)
{
string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + ".xls");
//เรียกใช้เมธอดในการ Export ข้อมูลแบบ Multiple sheet
ExportToMultipleXLSheets(fileName);
}
หลักการทำงานคร่าวๆในการ Export ข้อมูลใน 2 ลักษณะจะมีหลักการพื้นฐานคล้ายกัน ซึ่งจะสรุปได้ดังนี้
- ดึงข้อมูลจากฐานข้อมูลมาเก็บไว้ใน Datadatable หรือ Dataset
- กำหนดชื่อไฟล์ที่จะใช้ในการเชื่อมต่อกับ OleDb
- เชื่อมต่อกับ OleDb โดยการกำหนดค่าต่างๆ เพื่อใช้ในการทำงานกับไฟล์ Excel นั้นๆ
- เตรียมกำหนดคำสั่ง sql command ในการสร้างโครงสร้างตาราง(create table) และ เพิ่มข้อมูล (insert) ในตารางที่สร้างไว้ หากเป็นกรณีที่ต้องการสร้างแบบ Multiple sheet
ก็จะทำการเตรียมชุดคำสั่งไว้ และนำไปวน run คำสั่งนั้นๆตามจำนวนรอบที่มีการสร้าง WorkSheet ใหม่นั่นเอง
- สั่ง run คำสั่งดังกล่าว หากจำนวนแถวของข้อมูลเกินกว่าที่กำหนดต่อ 1 WorkSheet (ซึ่งในกรณีสมมติให้เป็น 3 แถว) จะทำการสร้าง WorkSheet ใหม่
- อ่านค่าที่ได้จากไฟล์ที่เชื่อมต่อกับ OleDb และผ่านกระบวนการ Export ข้อมูล มาเขียนเป็นไฟล์ Excel เพื่อให้ผู้ใช้สามารถบันทึกข้อมูลในรูปแบบไฟล์ที่ Export ได้
- หากเป็นการ Export ข้อมูลแบบ Multiple sheet จำนวนของ WorkSheet จะขึ้นอยู่กับข้อมูลที่ดึงมาและจำนวนที่กำหนดไว้ต่อ 1 WorkSheet นั่นเอง
หมายเหตุ : Namespace ที่ต้องอ้างอิงเพิ่มเติมในการใช้งานโค้ดที่กล่าวไว้ข้างต้น มีดังนี้
– System.IO
– System.Data.OleDb
– System.Data
– System.Text
บทความนี้ถือเป็นเพียงการแนะนำวิธีเบื้องต้นในการ Export ข้อมูลไฟล์ในรูปแบบ Excel เท่านั้น ซึ่งผู้พัฒนาสามารถนำความรู้หรือข้อมูลที่ได้จากบทความนี้ไปประยุกต์ ดัดแปลงหรือเพิ่มลูกเล่นให้กับงานที่ท่านกำลังพัฒนาอยู่ได้ เพื่อเพิ่มความสามารถในกระบวนการนำ-เข้าส่งออกดังกล่าว นอกจากนี้ผู้เขียนหวังเป็นอย่างยิ่งว่าบทความนี้อาจเป็นอีกหนึ่งทางเลือกสำหรับผู้พัฒนามือใหม่หรือผู้ที่กำลังค้นหาวิธีการนี้อยู่ สามารถนำไปพัฒนาต่อได้โดยง่าย หากผิดพลาดประการใด ผู้เขียนขออภัยไว้ ณ ที่นี้ค่ะ
แหล่งข้อมูลอ้างอิง :
http://www.codeproject.com/Articles/33271/Import-and-Export-to-Multiple-Worksheets
http://dotnetawesome.blogspot.com/2013/11/how-to-import-export-database-data-from_18.html
เพื่อดูผลลัพธ์ ออกมาดังรูป 9
