Export ข้อมูลไฟล์ Excel ในแบบ Single และ Multiple sheet ด้วย ASP.NET(C#)

            ความเดิมตอนที่แล้ว ผู้เขียนได้พูดถึงความสำคัญของข้อมูล และวิธีการ 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();
}
}
}

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();
}

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();

}
}

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 นั่นเอง

            บทความนี้ถือเป็นเพียงการแนะนำวิธีเบื้องต้นในการ 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