/// <summary>
/// Creat Worksheet of Excel for Table Data
/// </summary>
/// <param name="name">the name of the worksheet</param>
/// <param name="worksheetTemplate">the .xlsx support template</param>
/// <param name="headers">the header of the table to be shown in excel</param>
/// <param name="rowsData">the data of the table</param>
/// <param name="suffix">the suffix of the excel, which is used to define the type of the excel.</param>
/// <returns>rf.OfficeOpenXml.ExcelWorksheet/rf.NPOI.SS.UserModel.ISheet/null</returns>
/// <example>
/// <code language="C#" title="create .xlsx excel">
/// RF.GlobalClass.Excel excel = new RF.GlobalClass.Excel();
/// excel.createWorksheet(name: "LuckDrawData", headers: new string[] { "NO", "scoreCardCode", "scoreCardOwnerName", "scoreTimes", "Item" }, rowsData: result.data["TableItemList"], suffix:RF.GlobalClass.Excel.suffix.xlsx);
/// RF.GlobalClass.WebForm.Excel.saveAs(excel: excel, Response: Response, name: "LuckDrawData" + RF.GlobalClass.Utils.DateTime.GetDateTimeString(DateTime.Now), suffix: RF.GlobalClass.Excel.suffix.xlsx);
/// </code>
/// </example>
/// <example>
/// <code language="C#" title="create .xls excel">
/// RF.GlobalClass.Excel excel = new RF.GlobalClass.Excel();
/// excel.createWorksheet(name: "LuckDrawData", headers: new string[] { "NO", "scoreCardCode", "scoreCardOwnerName", "scoreTimes", "Item" }, rowsData: result.data["TableItemList"], suffix:RF.GlobalClass.Excel.suffix.xls);
/// RF.GlobalClass.WebForm.Excel.saveAs(excel: excel, Response: Response, name: "LuckDrawData" + RF.GlobalClass.Utils.DateTime.GetDateTimeString(DateTime.Now), suffix: RF.GlobalClass.Excel.suffix.xls);
/// </code>
/// </example>
public object createWorksheet(string name = defaultWorksheetName, string[] headers = null, List <Dictionary <string, string> > rowsData = null, Excel.Suffix suffix = Excel.Suffix.xls)
{
rf.OfficeOpenXml.ExcelWorksheet worksheetTemplate = null;
try
{
suffix = Suffix.unknow == suffix ? this.suffix : suffix;
switch (suffix)
{
case Excel.Suffix.xls:
#region fillWorksheet
try
{
name = name + ".xls";
rf.NPOI.SS.UserModel.ISheet sheet = hssfworkbook.CreateSheet(name);
return(fillWorksheet(sheet: sheet, headers: headers, rowsData: rowsData));
}
catch (Exception ex)
{
}
#endregion
break;
case Excel.Suffix.xlsx:
#region fillWorksheet
try
{
rf.OfficeOpenXml.ExcelWorksheet worksheet;
name = name + ".xlsx";
if (null != worksheetTemplate)
{
worksheet = package.Workbook.Worksheets.Add(name, worksheetTemplate);
}
else
{
worksheet = package.Workbook.Worksheets.Add(name);
}
return(fillWorksheet(worksheet, headers: headers, rowsData: rowsData));
}
catch (Exception ex)
{
}
#endregion
break;
default:
break;
}
this.suffix = suffix;
}
catch (Exception ex) { }
return(null);
}
private rf.NPOI.SS.UserModel.ISheet createWorksheet(string name = defaultWorksheetName, string[] headers = null, List <Dictionary <string, string> > rowsData = null)
{
rf.NPOI.SS.UserModel.ISheet sheet = hssfworkbook.CreateSheet(name);
#region comment
// sheet.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
//int x = 1;
//for (int i = 1; i <= 15; i++)
//{
// rf.NPOI.SS.UserModel.IRow row = sheet.CreateRow(i);
// for (int j = 0; j < 15; j++)
// {
// row.CreateCell(j).SetCellValue(x++);
// }
//}
#endregion
return(fillWorksheet(sheet: sheet, headers: headers, rowsData: rowsData));
}
/// <summary>
/// Fill sheet of .xls excel with data
/// </summary>
/// <param name="sheet">the work sheet to be filled</param>
/// <param name="headers">the headers to fill</param>
/// <param name="rowsData">the row data to fill</param>
/// <returns>rf.NPOI.SS.UserModel.ISheet</returns>
public rf.NPOI.SS.UserModel.ISheet fillWorksheet(rf.NPOI.SS.UserModel.ISheet sheet, string[] headers = null, List <Dictionary <string, string> > rowsData = null)
{
// add header
rf.NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
headers.Select <string, rf.NPOI.SS.UserModel.IRow>(delegate(string _value, int _idx)
{
row.CreateCell(_idx).SetCellValue(_value);
return(row);
}).ToArray();
// add rows
rowsData.Select(delegate(Dictionary <string, string> _rowData, int _rowIndex)
{
row = sheet.CreateRow(_rowIndex + 1);
return(_rowData.Select(delegate(KeyValuePair <string, string> _kvp, int _colIndex)
{
row.CreateCell(_colIndex).SetCellValue(_kvp.Value);
return row;
}).ToArray());
}).ToArray();
return(sheet);
}
