public void TestRGBColour()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("50299.xlsx");
// Check the CTColor is as expected
XSSFColor rgb3 = (XSSFColor)((XSSFCellStyle)wb.GetCellStyleAt((short)25)).FillForegroundXSSFColor;
Assert.AreEqual(false, rgb3.GetCTColor().IsSetIndexed());
Assert.AreEqual(0, rgb3.GetCTColor().indexed);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetTint());
Assert.AreEqual(-0.34999, rgb3.GetCTColor().tint, 0.00001);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetRgb());
Assert.AreEqual(3, rgb3.GetCTColor().GetRgb().Length);
// Now check the XSSFColor
Assert.AreEqual(0, rgb3.Indexed);
Assert.AreEqual(-0.34999, rgb3.Tint, 0.00001);
Assert.IsFalse(rgb3.HasAlpha);
Assert.IsTrue(rgb3.HasTint);
Assert.AreEqual("FFFFFFFF", rgb3.ARGBHex);
Assert.AreEqual(3, rgb3.RGB.Length);
Assert.AreEqual(255, rgb3.RGB[0]);
Assert.AreEqual(255, rgb3.RGB[1]);
Assert.AreEqual(255, rgb3.RGB[2]);
Assert.AreEqual(4, rgb3.ARGB.Length);
Assert.AreEqual(255, rgb3.ARGB[0]);
Assert.AreEqual(255, rgb3.ARGB[1]);
Assert.AreEqual(255, rgb3.ARGB[2]);
Assert.AreEqual(255, rgb3.ARGB[3]);
// Tint doesn't have the alpha
// tint = -0.34999
// 255 * (1 + tint) = 165 truncated
// or (byte) -91 (which is 165 - 256)
Assert.AreEqual(3, rgb3.GetRgbWithTint().Length);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[0]);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[1]);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[2]);
// Set the colour to black, will Get translated internally
// (Excel stores 3 colour white and black wrong!)
// Set the color to black (no theme).
rgb3.SetRgb(new byte[] { 0, 0, 0 });
Assert.AreEqual("FF000000", rgb3.ARGBHex);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[2]);
// Set another, is fine
rgb3.SetRgb(new byte[] { 16, 17, 18 });
Assert.IsFalse(rgb3.HasAlpha);
Assert.AreEqual("FF101112", rgb3.ARGBHex);
Assert.AreEqual(0x10, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0x11, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0x12, rgb3.GetCTColor().GetRgb()[2]);
}
/// <summary>
/// 高版本获取自定义颜色
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgb"></param>
/// <returns></returns>
private static short GetCustomColor(this XSSFWorkbook workBook, string rgb)
{
short indexed = defaultColorIndexed;
if (string.IsNullOrEmpty(rgb))
{
return(indexed);
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
return(indexed);
}
byte red = 0;
byte green = 0;
byte blue = 0;
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
return(indexed);
}
byte[] bytes = { red, green, blue };
XSSFColor color = new XSSFColor();
color.SetRgb(bytes);
indexed = color.Indexed;
return(indexed);
}
static ExcelCreate()
{
_scoreColor = new XSSFColor();
_scoreColor.SetRgb(new byte[] { 255, 153, 58 });
_winRateColor = new XSSFColor();
_winRateColor.SetRgb(new byte[] { 255, 0, 0 });
_bgColor1 = new XSSFColor();
_bgColor1.SetRgb(new byte[] { 112, 206, 188 });
_bgColor2 = new XSSFColor();
_bgColor2.SetRgb(new byte[] { 220, 230, 241 });
}
/// <summary>
///
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgbs"></param>
private static void SetCustomColor(this XSSFWorkbook workBook, IEnumerable <string> rgbs)
{
// 获取调色板
XSSFColor color = null;
short indexed = 8;
foreach (var rgb in rgbs)
{
if (indexed > 63)
{
indexed = 8;
}
if (string.IsNullOrEmpty(rgb))
{
continue;
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
continue;
}
byte red = 0;
byte green = 0;
byte blue = 0;
// 处理RGB数据
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
continue;
}
byte[] bytes = { red, green, blue };
color = new XSSFColor();
color.SetRgb(bytes);
color.Indexed = indexed;
indexed++;
}
}
bool write2Exl(StageDef domain, IWorkbook workbook)
{
foreach (DGObjectDef objectDef in domain.DGObjectContainer)
{
XSSFCellStyle cellStyle1 = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFCellStyle cellStyle2 = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor color1 = new XSSFColor();
XSSFColor color2 = new XSSFColor();
//根据自己需要设置RGB
byte[] colorRgb1 = { (byte)214, (byte)220, (byte)228 };
byte[] colorRgb2 = { (byte)221, (byte)235, (byte)247 };
color1.SetRgb(colorRgb1);
color2.SetRgb(colorRgb2);
cellStyle1.FillForegroundColorColor = color1;
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle2.FillBackgroundColorColor = color2;
cellStyle2.FillPattern = FillPattern.SolidForeground;
string sheetName = objectDef.LangStr ?? objectDef.Code;
ISheet sheet = workbook.CreateSheet(sheetName);
writeDescription(sheet, objectDef);
wrtieTitle(sheet, objectDef, cellStyle1, cellStyle2);
}
return(true);
}
/// <summary>
/// 輸出出勤資料Excel
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public Stream RG01ToExcel(ReportFormModel ReportFrom)
{
List <RG01Model> RG01List = new List <RG01Model>();
string SQL = "SELECT *, ";
SQL += " CASE Status WHEN 'A' THEN '新增'WHEN 'U' THEN '編輯'WHEN 'D' THEN '刪除' WHEN 'AM' THEN '補登新增' WHEN 'U' THEN '補登編輯' WHEN 'DM' THEN '補登刪除' END AS StatusDisplay, ";
SQL += " Convert(char(10),Convert(datetime,OutDate),20) as OutDateFormat,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat FROM gvOutgoing WHERE 1=1";
if (ReportFrom.Company != "")
{
SQL += " AND Company = @Company ";
}
if (ReportFrom.EmpNo != "")
{
SQL += " AND OutMan = @OutMan ";
}
if (ReportFrom.DepartMentNo != "")
{
SQL += " AND DepartMentNo = @DepartMentNo ";
}
SQL += " AND OutDate BETWEEN @StartDate AND @EndDate ";
SQL += " AND AlermTotal > 0 Order by OutMan,OutDate,OutTime";
DataTable dt = DbAccess.ExecuteDataTable(SQL,
new DbParameter[] {
DataAccess.CreateParameter("Company", DbType.String, ReportFrom.Company.ToString()),
DataAccess.CreateParameter("OutMan", DbType.String, ReportFrom.EmpNo.ToString()),
DataAccess.CreateParameter("DepartMentNo", DbType.String, ReportFrom.DepartMentNo.ToString()),
DataAccess.CreateParameter("StartDate", DbType.String, ReportFrom.StartDate.ToString().Replace("-", "")),
DataAccess.CreateParameter("EndDate", DbType.String, ReportFrom.EndDate.ToString().Replace("-", ""))
}
);
for (int i = 0; i < dt.Rows.Count; i++)
{
RG01Model tmpM = new RG01Model();
tmpM.OutId = dt.Rows[i]["OutId"].ToString();
tmpM.OutDate = dt.Rows[i]["OutDateFormat"].ToString();
tmpM.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
tmpM.OutTime = dt.Rows[i]["OutTimeFormat"].ToString();
tmpM.OutMan = dt.Rows[i]["OutMan"].ToString();
tmpM.Status = dt.Rows[i]["StatusDisplay"].ToString();
tmpM.CompanyName = dt.Rows[i]["CompanyName"].ToString();
tmpM.DepartMentName = dt.Rows[i]["DepartMentName"].ToString();
tmpM.AlermTotal = dt.Rows[i]["AlermTotal"].ToString();
tmpM.GoOutTime = dt.Rows[i]["GoOutTimeFormat"].ToString();
tmpM.CustomerName = dt.Rows[i]["CustomerName"].ToString();
tmpM.Location = dt.Rows[i]["Location"].ToString();
/*
* SELECT
* Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat
* FROM gvOutgoing
*/
SQL = "SELECT *, ";
SQL += " CASE Alerm WHEN 0 THEN '正常' WHEN 1 THEN '異常' END AS AlermDisplay,CASE Status WHEN 'A' THEN '新增'WHEN 'U' THEN '編輯'WHEN 'D' THEN '刪除' WHEN 'AM' THEN '補登新增' WHEN 'U' THEN '補登編輯' WHEN 'DM' THEN '補登刪除' END AS StatusDisplay, ";
SQL += " Convert(char(10),Convert(datetime,OutDate),20) as OutDateFormat,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat ";
SQL += " FROM gvOutHistory WHERE OutId = @OutId AND OutMan = @OutMan Order by UpdateDate,UpdateTime";
DataTable DtDtl = DbAccess.ExecuteDataTable(SQL,
new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, tmpM.OutId),
DataAccess.CreateParameter("OutMan", DbType.String, tmpM.OutMan),
}
);
for (int j = 0; j < DtDtl.Rows.Count; j++)
{
RG01DtlModel tmpDtl = new RG01DtlModel();
tmpDtl.OutDate = DtDtl.Rows[j]["OutDateFormat"].ToString();
tmpDtl.OutTime = DtDtl.Rows[j]["OutTimeFormat"].ToString();
tmpDtl.CustomerName = DtDtl.Rows[j]["CustomerName"].ToString();
tmpDtl.GoOutTime = DtDtl.Rows[j]["GoOutTimeFormat"].ToString();
tmpDtl.RecordManName = DtDtl.Rows[j]["RecordManName"].ToString();
tmpDtl.Location = DtDtl.Rows[j]["Location"].ToString();
tmpDtl.Status = DtDtl.Rows[j]["StatusDisplay"].ToString();
tmpDtl.UpdateDate = DtDtl.Rows[j]["UpdateDateFormat"].ToString();
tmpDtl.UpdateTime = DtDtl.Rows[j]["UpdateTimeFormat"].ToString();
tmpDtl.Alerm = DtDtl.Rows[j]["AlermDisplay"].ToString();
tmpM.RG01Dtl.Add(tmpDtl);
}
RG01List.Add(tmpM);
}
IWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = (XSSFSheet)workbook.CreateSheet();
//定義EmpInfo顏色
XSSFCellStyle EmpInfoStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColor = new XSSFColor();
BgColor.SetRgb(new byte[] { 102, 204, 255 });
EmpInfoStyle.SetFillForegroundColor(BgColor);
EmpInfoStyle.FillPattern = FillPattern.SolidForeground;
//定義Header顏色
XSSFCellStyle HeaderStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColorHeader = new XSSFColor();
BgColorHeader.SetRgb(new byte[] { 252, 213, 180 });
HeaderStyle.SetFillForegroundColor(BgColorHeader);
HeaderStyle.FillPattern = FillPattern.SolidForeground;
//定義Header顏色
XSSFCellStyle BodyStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColorBody = new XSSFColor();
BgColorBody.SetRgb(new byte[] { 216, 218, 188 });
BodyStyle.SetFillForegroundColor(BgColorBody);
BodyStyle.FillPattern = FillPattern.SolidForeground;
int IntRow = 0; //Excel 第幾列
string IntEmp = "";
for (int i = 0; i < RG01List.Count; i++)
{
if (IntEmp != RG01List[i].OutMan)
{
if (IntEmp != "")
{
//換行!!
IRow SpaceDataRowV = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCellV = (XSSFCell)SpaceDataRowV.CreateCell(0);
IntRow++;
}
//員工資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow EmpInfo = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell Cell0 = (XSSFCell)EmpInfo.CreateCell(0);
Cell0.SetCellValue("員工編號");
Cell0.CellStyle = EmpInfoStyle;
XSSFCell Cell1 = (XSSFCell)EmpInfo.CreateCell(1);
Cell1.SetCellValue("員工姓名");
Cell1.CellStyle = EmpInfoStyle;
XSSFCell Cell2 = (XSSFCell)EmpInfo.CreateCell(2);
Cell2.SetCellValue("公 司 別");
Cell2.CellStyle = EmpInfoStyle;
XSSFCell Cell3 = (XSSFCell)EmpInfo.CreateCell(3);
Cell3.SetCellValue("部 門");
Cell3.CellStyle = EmpInfoStyle;
XSSFCell Cell4 = (XSSFCell)EmpInfo.CreateCell(4);
Cell4.SetCellValue("異常次數");
Cell4.CellStyle = EmpInfoStyle;
IntRow++;
IRow EmpInfoContent = (XSSFRow)sheet.CreateRow(IntRow);
EmpInfoContent.CreateCell(0).SetCellValue(RG01List[i].OutMan);
EmpInfoContent.CreateCell(1).SetCellValue(RG01List[i].EmployeeName);
EmpInfoContent.CreateCell(2).SetCellValue(RG01List[i].CompanyName);
EmpInfoContent.CreateCell(3).SetCellValue(RG01List[i].DepartMentName);
int AlermCount = 0;
AlermCount = (from tmp in RG01List
where tmp.OutMan == RG01List[i].OutMan
select tmp).Count();
EmpInfoContent.CreateCell(4).SetCellValue(AlermCount);
IntRow++;
IntEmp = RG01List[i].OutMan;
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊員工資料
}
//單頭資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow headerRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell headerCell2 = (XSSFCell)headerRow.CreateCell(2);
headerCell2.SetCellValue("外出日期");
headerCell2.CellStyle = HeaderStyle;
XSSFCell headerCell3 = (XSSFCell)headerRow.CreateCell(3);
headerCell3.SetCellValue("預計外出時間");
headerCell3.CellStyle = HeaderStyle;
XSSFCell headerCell4 = (XSSFCell)headerRow.CreateCell(4);
headerCell4.SetCellValue("會議時間");
headerCell4.CellStyle = HeaderStyle;
XSSFCell headerCell5 = (XSSFCell)headerRow.CreateCell(5);
headerCell5.SetCellValue("客戶名稱");
headerCell5.CellStyle = HeaderStyle;
XSSFCell headerCell6 = (XSSFCell)headerRow.CreateCell(6);
headerCell6.SetCellValue("地 點");
headerCell6.CellStyle = HeaderStyle;
XSSFCell headerCell7 = (XSSFCell)headerRow.CreateCell(7);
headerCell7.SetCellValue("單據狀態");
headerCell7.CellStyle = HeaderStyle;
IntRow++;
IRow headerContentRow = (XSSFRow)sheet.CreateRow(IntRow);
headerContentRow.CreateCell(2).SetCellValue(RG01List[i].OutDate);
headerContentRow.CreateCell(3).SetCellValue(RG01List[i].OutTime);
headerContentRow.CreateCell(4).SetCellValue(RG01List[i].GoOutTime);
headerContentRow.CreateCell(5).SetCellValue(RG01List[i].CustomerName);
headerContentRow.CreateCell(6).SetCellValue(RG01List[i].Location);
headerContentRow.CreateCell(7).SetCellValue(RG01List[i].Status);
IntRow++;
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊單頭資料
//單身資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow bodyRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell bodyCell4 = (XSSFCell)bodyRow.CreateCell(4);
bodyCell4.SetCellValue("項 次");
bodyCell4.CellStyle = BodyStyle;
XSSFCell bodyCell5 = (XSSFCell)bodyRow.CreateCell(5);
bodyCell5.SetCellValue("系統判定");
bodyCell5.CellStyle = BodyStyle;
XSSFCell bodyCell6 = (XSSFCell)bodyRow.CreateCell(6);
bodyCell6.SetCellValue("單據狀態");
bodyCell6.CellStyle = BodyStyle;
XSSFCell bodyCell7 = (XSSFCell)bodyRow.CreateCell(7);
bodyCell7.SetCellValue("外出日期");
bodyCell7.CellStyle = BodyStyle;
XSSFCell bodyCell8 = (XSSFCell)bodyRow.CreateCell(8);
bodyCell8.SetCellValue("外出時間");
bodyCell8.CellStyle = BodyStyle;
XSSFCell bodyCell9 = (XSSFCell)bodyRow.CreateCell(9);
bodyCell9.SetCellValue("會議時間");
bodyCell9.CellStyle = BodyStyle;
XSSFCell bodyCell10 = (XSSFCell)bodyRow.CreateCell(10);
bodyCell10.SetCellValue("客戶名稱");
bodyCell10.CellStyle = BodyStyle;
XSSFCell bodyCell11 = (XSSFCell)bodyRow.CreateCell(11);
bodyCell11.SetCellValue("地 點");
bodyCell11.CellStyle = BodyStyle;
XSSFCell bodyCell12 = (XSSFCell)bodyRow.CreateCell(12);
bodyCell12.SetCellValue("編輯時間");
bodyCell12.CellStyle = BodyStyle;
XSSFCell bodyCell13 = (XSSFCell)bodyRow.CreateCell(13);
bodyCell13.SetCellValue("編 輯 者");
bodyCell13.CellStyle = BodyStyle;
IntRow++;
for (int j = 0; j < RG01List[i].RG01Dtl.Count; j++)
{
IRow ItemRow = (XSSFRow)sheet.CreateRow(IntRow);
ItemRow.CreateCell(4).SetCellValue((j + 1).ToString());
ItemRow.CreateCell(5).SetCellValue(RG01List[i].RG01Dtl[j].Alerm);
ItemRow.CreateCell(6).SetCellValue(RG01List[i].RG01Dtl[j].Status);
ItemRow.CreateCell(7).SetCellValue(RG01List[i].RG01Dtl[j].OutDate);
ItemRow.CreateCell(8).SetCellValue(RG01List[i].RG01Dtl[j].OutTime);
ItemRow.CreateCell(9).SetCellValue(RG01List[i].RG01Dtl[j].GoOutTime);
ItemRow.CreateCell(10).SetCellValue(RG01List[i].RG01Dtl[j].CustomerName);
ItemRow.CreateCell(11).SetCellValue(RG01List[i].RG01Dtl[j].Location);
ItemRow.CreateCell(12).SetCellValue(RG01List[i].RG01Dtl[j].UpdateDate + " " + RG01List[i].RG01Dtl[j].UpdateTime);
ItemRow.CreateCell(13).SetCellValue(RG01List[i].RG01Dtl[j].RecordManName);
IntRow++;
}
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊單身資料
//換行!!
IRow SpaceDataRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCell = (XSSFCell)SpaceDataRow.CreateCell(0);
IntRow++;
}
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
sheet = null;
workbook = null;
return(ms);
}
/// <summary>
/// 輸出員工出勤記錄表Excel
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public static Stream RA01ToExcel(DataTable SourceTable)
{
IWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = (XSSFSheet)workbook.CreateSheet();
IRow headerRow = (XSSFRow)sheet.CreateRow(0);
//定義超過五分鐘後的顏色
XSSFCellStyle LateStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.Color = IndexedColors.DarkRed.Index;
LateStyle.SetFont(font);
//定義超過遲到 五分鐘內的顏色
XSSFCellStyle LateStyle5Min = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFFont font2 = workbook.CreateFont() as XSSFFont;
font2.Color = IndexedColors.Green.Index;
LateStyle5Min.SetFont(font2);
//定義曠職顏色
XSSFCellStyle AbsStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColor = new XSSFColor();
BgColor.SetRgb(new byte[] { 255, 255, 0 });
AbsStyle.SetFillForegroundColor(BgColor);
AbsStyle.FillPattern = FillPattern.SolidForeground;
AbsStyle.SetFillForegroundColor(BgColor);
//定義輸出欄位
List <DataTableDisplay> DisplayList = new List <DataTableDisplay>();
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeNo",
DisplayName = "員工編號"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "DepartMentName",
DisplayName = "部門"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeName",
DisplayName = "姓名"
});
//2016.11.12 Scott新增英文名欄位
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeEName",
DisplayName = "英文名"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CompanyName",
DisplayName = "公司別"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "WorkDay",
DisplayName = "日期"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "StartWorkTime",
DisplayName = "上班"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EndWorkTime",
DisplayName = "下班"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "StartWorkOvertime",
DisplayName = "加班上班"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EndWorkOvertime",
DisplayName = "加班下班"
});
// 1051129 Scott 將出勤描述 分為上午、下午、加班 3欄
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "AttendanceDescM",
DisplayName = "出勤描述(上午)"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "AttendanceDescN",
DisplayName = "出勤描述(下午)"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "AttendanceDescOT",
DisplayName = "出勤描述(加班)"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "LateMinFormat",
DisplayName = "遲到"
});
// Scott 105.12.27 移除備註欄為
//DisplayList.Add(new DataTableDisplay()
//{
// ColumnName = "Memo",
// DisplayName = "備註"
//});
for (int i = 0; i < DisplayList.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(DisplayList[i].DisplayName);
}
int IntRow = 1;
if (SourceTable.Rows.Count > 0)
{
string BrEmpNo = SourceTable.Rows[0]["EmployeeNo"].ToString();
string Test = "";
for (int i = 0; i < SourceTable.Rows.Count; i++)
{
if (BrEmpNo != SourceTable.Rows[i]["EmployeeNo"].ToString())
{
//換行!!
IRow tmpDataRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCell = (XSSFCell)tmpDataRow.CreateCell(0);
IntRow++;
BrEmpNo = SourceTable.Rows[i]["EmployeeNo"].ToString();
}
IRow dataRow = (XSSFRow)sheet.CreateRow(IntRow);
for (int j = 0; j < DisplayList.Count; j++)
{
try
{
Test = SourceTable.Rows[i][DisplayList[j].ColumnName].ToString();
}
catch
{
//查無此欄位 則空著!!
XSSFCell CellR = (XSSFCell)dataRow.CreateCell(j);
CellR.SetCellValue("");
continue;
}
//** 人事部稽核用與Web輸出不同!!! **
//若是正常打卡且有出差,則出差兩字拿掉
/*
* if (DisplayList[j].ColumnName == "AttendanceDescM" && SourceTable.Rows[i]["AttendanceDescM"].ToString().IndexOf("出差") != -1)
* {
*
* if ((SourceTable.Rows[i]["StartWorkTime"].ToString() != "" && SourceTable.Rows[i]["EndWorkTime"].ToString() != ""))
* {
* //正常上班 正常下班
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace("出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace("出差", "");
* }
* else if ((SourceTable.Rows[i]["StartWorkTime"].ToString() != "" && SourceTable.Rows[i]["EndWorkOvertime"].ToString() != ""))
* {
* //正常上班 加班下班
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace("出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace("出差", "");
* }
* else if ((SourceTable.Rows[i]["StartWorkOvertime"].ToString() != "" && SourceTable.Rows[i]["EndWorkOvertime"].ToString() != ""))
* {
* //加班上班 加班下班
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescM"] = SourceTable.Rows[i]["AttendanceDescM"].ToString().Replace("出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace(",出差", "");
* SourceTable.Rows[i]["AttendanceDescN"] = SourceTable.Rows[i]["AttendanceDescN"].ToString().Replace("出差", "");
* }
* }
*/
//員工
XSSFCell Cell = (XSSFCell)dataRow.CreateCell(j);
Cell.SetCellValue(SourceTable.Rows[i][DisplayList[j].ColumnName].ToString());
//遲到分鐘數 顏色判定
if (DisplayList[j].ColumnName == "LateMinFormat")
{
switch (SourceTable.Rows[i]["LateMin"].ToString())
{
case "":; break;
case "0":; break;
case "1": Cell.CellStyle = LateStyle5Min;; break;
case "2": Cell.CellStyle = LateStyle5Min;; break;
case "3": Cell.CellStyle = LateStyle5Min;; break;
case "4": Cell.CellStyle = LateStyle5Min;; break;
default: Cell.CellStyle = LateStyle; break;
}
}
//曠職 顏色判定
if (DisplayList[j].ColumnName == "AttendanceDescM")
{
string Tmp = SourceTable.Rows[i]["AttendanceDescM"].ToString();
if (Tmp.IndexOf("曠職") != -1)
{
Cell.CellStyle = AbsStyle;
}
}
if (DisplayList[j].ColumnName == "AttendanceDescN")
{
string Tmp = SourceTable.Rows[i]["AttendanceDescN"].ToString();
if (Tmp.IndexOf("曠職") != -1)
{
Cell.CellStyle = AbsStyle;
}
}
}
IntRow++;
}
/*
* foreach (DataColumn column in SourceTable.Columns)
* {
* XSSFCell cell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
* cell.SetCellValue(p.ToString() +"-"+row[column].ToString());
*
*
* switch(column.ColumnName.ToString())
* {
* case "LateMin":;break;
* }
*
*/
//加一筆空白行數
//dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName.ToString()+"-"+);
}
/*
* XSSFRow row = (XSSFRow)sheet.CreateRow(1);
*
*
* XSSFCell cell;
*
* for (int i = 0; i < title.Length; i++)
* {
*
* cell = (XSSFCell)row.CreateCell(i + 2);
* cell.CellStyle = LateStyle5Min;
* cell.SetCellValue(title[i]);
* }
*/
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
public void computeResult(string uniqueID, IDictionary <int, string> eventDict, XSSFWorkbook wb, string sheetName, List <string> responseList,
List <string> questionTypeList, IDictionary <string, string> uniqueAnswerDict,
List <string> questionList, List <List <string> > answerList, string resultType = "Normal")
{
//Create bold font for excel
XSSFCellStyle xStyle = boldFont(wb);
int rowCount = 0;
int totalNumChoiceLength = 0;
if (resultType != "Percent")
{
XSSFCreationHelper createHelper = (XSSFCreationHelper)wb.GetCreationHelper();
}
XSSFSheet summarySheet = (XSSFSheet)wb.CreateSheet(sheetName);
//Group all the different answers list together
foreach (KeyValuePair <string, string> entry in uniqueAnswerDict)
{
// Create a row and put some cells in it. Rows are 0 based.
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(entry.Value));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
int cellCount = 1;
List <string> answerChoiceList = JsonConvert.DeserializeObject <List <string> >(entry.Key);
if (answerChoiceList.Count > totalNumChoiceLength)
{
totalNumChoiceLength = answerChoiceList.Count;
}
foreach (string choice in answerChoiceList)
{
row.CreateCell(cellCount).SetCellValue(choice);
row.GetCell(cellCount).CellStyle = xStyle;
cellCount += 1;
}
rowCount += 1;
}
xStyle = boldFont(wb);
//xStyle.FillPattern = FillPattern.NoFill;
List <string> openEndedList = new List <string>();
Dictionary <int, List <string> > openEndedResponseDict = new Dictionary <int, List <string> >();
Dictionary <string, int> choiceResponseDict = new Dictionary <string, int>();
//Get a list of response
foreach (var response in responseList)
{
FeedbackEntity feedbackEntity = JsonConvert.DeserializeObject <FeedbackEntity>(response);
List <string> feedbackResponse = JsonConvert.DeserializeObject <List <string> >(feedbackEntity.Response);
int qnNo = 1;
foreach (string feedback in feedbackResponse)
{
List <string> openEndedResponse = new List <string>();
if (questionTypeList[qnNo - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
if (openEndedResponseDict.ContainsKey(qnNo))
{
openEndedResponseDict[qnNo].Add(feedback);
}
else
{
openEndedResponse.Add(feedback);
openEndedResponseDict.Add(qnNo, openEndedResponse);
}
}
else if (questionTypeList[qnNo - 1] == "2") //Multiple Choice
{
if (choiceResponseDict.ContainsKey(qnNo + "||" + feedback))
{
choiceResponseDict[qnNo + "||" + feedback] += 1;
}
else
{
choiceResponseDict.Add(qnNo + "||" + feedback, 1);
}
}
qnNo += 1;
}
}
//Print out all the question number and text
int qnCount = 1;
foreach (var qnText in questionList)
{
if (questionTypeList[qnCount - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
openEndedList.Add("Q" + qnCount + "." + qnText);
}
else if (questionTypeList[qnCount - 1] == "2") //Multiple Choice
{
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue("Q" + qnCount + ". ");
row.GetCell(0).CellStyle = xStyle;
row.CreateCell(0 + totalNumChoiceLength + 1).SetCellValue(qnText);
xStyle = normalFont(wb);
color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
for (int i = 0; i < answerList[qnCount - 1].Count; i++)
{
//Print out all the question response
if (choiceResponseDict.ContainsKey(qnCount + "||" + answerList[qnCount - 1][i]))
{
double displayValue = 0;
if (resultType == "Percent")
{
double value = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
displayValue = value / responseList.Count * 100;
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2) + "%");
}
else
{
displayValue = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2));
}
row.GetCell(1 + i).CellStyle = xStyle;
}
else
{
row.CreateCell(1 + i).SetCellValue(0);
row.GetCell(1 + i).CellStyle = xStyle;
}
xStyle.SetFillForegroundColor(color); //to end off the color xStyle
}
rowCount += 1;
}
qnCount++;
}
summarySheet.CreateRow(rowCount);
rowCount++;
xStyle = boldFont(wb);
//Print out all the openEnded Questions
foreach (var openEnded in openEndedList)
{
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue(openEnded);
row.GetCell(0).CellStyle = xStyle;
rowCount += 1;
int qnNo = Convert.ToInt32(openEnded.Split('.')[0].Remove(0, 1));
//Create rows for response answers
if (openEndedResponseDict.ContainsKey(qnNo))
{
foreach (var response in openEndedResponseDict[qnNo])
{
xStyle = normalFont(wb);
row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(1).SetCellValue(response);
row.GetCell(1).CellStyle = xStyle;
rowCount += 1;
}
}
else
{
rowCount += 1;
}
}
for (int i = 1; i <= 15; i++) // this will aply it form col 1 to 10
{
summarySheet.AutoSizeColumn(i);
}
}