//private void ExportExcelOpenXml(UploadAllData allData)
//{
// try
// {
// string outputname = "DownloadLead_" + DateTime.Now.Year.ToString() + DateTime.Now.ToString("MMdd_HHmmss") + ".xlsx";
// string fileName = System.IO.Path.Combine(Server.MapPath("~/Upload"), outputname);
// using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
// {
// WorkbookPart workbookPart = document.AddWorkbookPart();
// workbookPart.Workbook = new Workbook();
// WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// worksheetPart.Worksheet = new Worksheet();
// Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
// Sheet sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
// sheets.Append(sheet);
// workbookPart.Workbook.Save();
// SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// // Constructing header
// Row row = new Row();
// row.Append(
// ConstructCell("ชื่อลูกค้า", CellValues.String),
// ConstructCell("นามสกุลลูกค้า", CellValues.String),
// ConstructCell("ประเภทลูกค้า", CellValues.String),
// ConstructCell("บัตรประชาชน/นิติบุคคล", CellValues.String),
// ConstructCell("Owner Lead ID", CellValues.String),
// ConstructCell("Delegate Lead ID", CellValues.String),
// ConstructCell("เบอร์โทร#1", CellValues.String),
// ConstructCell("เบอร์โทร#2", CellValues.String),
// ConstructCell("รายละเอียด Lead", CellValues.String)
// );
// // Insert the header row to the Sheet Data
// sheetData.AppendChild(row);
// // Inserting each employee
// foreach (var data in allData.LeadDataList)
// {
// row = new Row();
// row.Append(
// ConstructCell(data.Firstname, CellValues.String),
// ConstructCell(data.Lastname, CellValues.String),
// ConstructCell(data.CardTypeDesc, CellValues.String),
// ConstructCell(data.CitizenId, CellValues.String),
// ConstructCell(data.OwnerEmpCode, CellValues.String),
// ConstructCell(data.DelegateEmpCode, CellValues.String),
// ConstructCell(data.TelNo1, CellValues.String),
// ConstructCell(data.TelNo2, CellValues.String),
// ConstructCell(data.Detail, CellValues.String));
// sheetData.AppendChild(row);
// }
// worksheetPart.Worksheet.Save();
// }
// //เก็บใส่ Session เพื่อไว้ใช้ในหน้า Export SLM_SCR_045.aspx
// Session[excelfilepath] = fileName;
// Session[outputfilename] = outputname;
// Session[contenttype] = "xlsx";
// string script = "window.open('SLM_SCR_045.aspx', 'uploaddata', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=300, height=100, resizable=yes');";
// ScriptManager.RegisterStartupScript(Page, GetType(), "uploaddata", script, true);
// }
// catch
// {
// throw;
// }
//}
/// <summary>
/// Export Excel 2003
/// </summary>
/// <param name="allData"></param>
private void ExportExcel(UploadAllData allData)
{
try
{
string outputname = "DownloadLead_" + DateTime.Now.Year.ToString() + DateTime.Now.ToString("MMdd_HHmmss") + ".xls";
string fileName = System.IO.Path.Combine(Server.MapPath("~/Upload"), outputname);
List <object[]> data = new List <object[]>();
allData.LeadDataList.ForEach(p => {
data.Add(new object[] {
p.Firstname, p.Lastname, p.CardTypeDesc, p.CitizenId, p.OwnerEmpCode
, p.DelegateEmpCode, p.TelNo1, p.TelNo2, p.Detail, p.StatusDesc, p.Remark, p.TicketId
});
});
var ebz = new ExcelExportBiz();
var list = new List <ExcelExportBiz.SheetItem> {
new ExcelExportBiz.SheetItem {
SheetName = "Sheet1",
RowPerSheet = 0,
Data = data,
Columns = new List <ExcelExportBiz.ColumnItem> {
new ExcelExportBiz.ColumnItem {
ColumnName = "ชื่อลูกค้า", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "นามสกุลลูกค้า", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "ประเภทลูกค้า", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "บัตรประชาชน/นิติบุคคล", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "Owner Lead ID", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "Delegate Lead ID", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "เบอร์โทร#1", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "เบอร์โทร#2", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "รายละเอียด Lead", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "สถานะ", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "Remark", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem {
ColumnName = "Ticket Id", ColumnDataType = ExcelExportBiz.ColumnType.Text
}
}
}
};
if (ebz.CreateExcel(fileName, list))
{
Session[excelfilepath] = fileName;
Session[outputfilename] = outputname;
string script = string.Format("window.open('SLM_SCR_045.aspx', '{0}', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=300, height=100, resizable=yes');", DateTime.Now.ToString("yyyyMMdd_HHmmss"));
ScriptManager.RegisterStartupScript(Page, GetType(), "uploaddata", script, true);
}
else
{
AppUtil.ClientAlert(Page, ebz.ErrorMessage);
}
}
catch
{
throw;
}
}
protected void TestExcel_Click(object sender, EventArgs e)
{
ExcelExportBiz ebz = new ExcelExportBiz();
if (!ebz.CreateExcel("D:\\test.xls", new List <ExcelExportBiz.SheetItem>()
{
new ExcelExportBiz.SheetItem()
{
SheetName = "ทดสอบ",
RowPerSheet = 0,
Columns = new List <ExcelExportBiz.ColumnItem>()
{
new ExcelExportBiz.ColumnItem()
{
ColumnName = "ID", ColumnDataType = ExcelExportBiz.ColumnType.Number
},
new ExcelExportBiz.ColumnItem()
{
ColumnName = "Name", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem()
{
ColumnName = "Birth", ColumnDataType = ExcelExportBiz.ColumnType.DateTime
}
},
Data = new List <object[]>()
{
new object[] { 1, "ทดสอบ1", new DateTime(1980, 01, 23) },
new object[] { 2, "ทดสอบ2zzz", new DateTime(1982, 01, 7) },
new object[] { 3, "ทดสอบ3 haha", new DateTime(1981, 01, 05) },
new object[] { 4, "ทดสอบ4 abcdef", new DateTime(1980, 07, 14) },
new object[] { 5, "ทดสอบ5 haheha", new DateTime(1986, 04, 6) },
new object[] { 6, "ทดสอบ6 jajaja", new DateTime(1984, 01, 3) },
new object[] { 7, "ทดสอบ7 jrrjrj", new DateTime(1989, 02, 2) },
new object[] { 8, "ทดสอบ8 lallaa", new DateTime(1980, 03, 25) },
new object[] { 9, "ทดสอบ9 sorry", new DateTime(1980, 01, 05) },
new object[] { 10, "ทดสอบ10 nega nega", new DateTime(1980, 01, 05) },
new object[] { 11, "ทดสอบ11", new DateTime(1985, 01, 15) }
}
}
,
new ExcelExportBiz.SheetItem()
{
SheetName = "ไหนลองดู",
RowPerSheet = 0,
Columns = new List <ExcelExportBiz.ColumnItem>()
{
new ExcelExportBiz.ColumnItem()
{
ColumnName = "ID", ColumnDataType = ExcelExportBiz.ColumnType.Number
},
new ExcelExportBiz.ColumnItem()
{
ColumnName = "Full Name", ColumnDataType = ExcelExportBiz.ColumnType.Text
},
new ExcelExportBiz.ColumnItem()
{
ColumnName = "Birthday", ColumnDataType = ExcelExportBiz.ColumnType.DateTime
}
},
Data = new List <object[]>()
{
new object[] { 1, "ทดสอบ1", new DateTime(1980, 01, 23) },
new object[] { 2, "ทดสอบ2zzz", new DateTime(1982, 01, 7) },
new object[] { 3, "ทดสอบ3 haha", new DateTime(1981, 01, 05) },
new object[] { 4, "ทดสอบ4 abcdef", new DateTime(1980, 07, 14) },
new object[] { 5, "ทดสอบ5 haheha", new DateTime(1986, 04, 6) },
new object[] { 6, "ทดสอบ6 jajaja", new DateTime(1984, 01, 3) },
new object[] { 7, "ทดสอบ7 jrrjrj", new DateTime(1989, 02, 2) },
new object[] { 8, "ทดสอบ8 lallaa", new DateTime(1980, 03, 25) },
new object[] { 9, "ทดสอบ9 sorry", new DateTime(1980, 01, 05) },
new object[] { 10, "ทดสอบ10 nega nega", new DateTime(1980, 01, 05) },
new object[] { 11, "ทดสอบ11", new DateTime(1985, 01, 15) }
}
}
}))
{
lblResult.Text = ebz.ErrorMessage;
}
else
{
lblResult.Text = "OK!";
}
}