1、html页面
//导出
function btn_export() {
var queryParams = {};
queryParams = $('.searchPanel').GetWebControls();
if (queryParams.Year == "") {
dialogMsg(lang.DialogWarnYear, 1);
return false;
}
location.href = "/PrefManager/Calcuate/ExportExcel/?queryJson=" + JSON.stringify(queryParams);
}
2、Controller.cs
[HttpGet]
public ActionResult ExportExcel(string queryJson)
{
CalcuateQueryViewModel queryModel = new CalcuateQueryViewModel();
if (!string.IsNullOrEmpty(queryJson))
{
queryModel = queryJson?.ToObject<CalcuateQueryViewModel>();
}
string fileName = "员工工作量报表.xls";
System.IO.MemoryStream ms = new System.IO.MemoryStream();
fileName = queryModel.Year+"年度员工工作量报表_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
ms = calcuateBiz.ReportExcel(queryModel);
return File(ms.ToArray(), "application/ms-excel", fileName);
}
3、业务层
public DataTable GetReportList(CalcuateQueryViewModel queryModel)
{
StringBuilder sb = new StringBuilder();
int year = int.Parse(queryModel.Year);
sb.AppendFormat(@"
SELECT tt.LegalEntityName ,
tt.BUName ,
tt.DepartmentName ,
tt.TeamName ,
tt.PieceType ,
tt.EmployeeNo ,
tt.EmployeeName ,
tt.PositionName ,
tt.BaseJob ,
tt.BaseUnit ,
[{0}01] AS 'Jan' ,
[{0}02] AS 'Feb' ,
[{0}03] AS 'Mar' ,
[{0}04] AS 'Apr' ,
[{0}05] AS 'May' ,
[{0}06] AS 'Jun' ,
[{0}07] AS 'Jul' ,
[{0}08] AS 'Aug' ,
[{0}09] AS 'Sep' ,
[{0}10] AS 'Oct' ,
[{0}11] AS 'Nov' ,
[{0}12] AS 'Dec'
FROM ( SELECT a.Month ,
a.BUID ,
b.BUName ,
a.DepartmentID ,
d.DepartmentName ,
a.EmployeeNo ,
m.FullNameCN AS EmployeeName ,
t.TeamCode ,
t.TeamName ,
t.PieceType ,
lg.LegalEntityID ,
lg.LegalEntityNameCN AS LegalEntityName ,
p.PositionID ,
p.PositionName ,
a.BaseJob ,
a.BaseUnit ,
a.ActJob
FROM dbo.HR_PF_Calc a
LEFT JOIN dbo.MDT_BU b ON b.BUID = a.BUID
LEFT JOIN dbo.MDT_Department d ON d.DepartmentID = a.DepartmentID
LEFT JOIN dbo.MDT_Employee m ON m.EmployeeNo = a.EmployeeNo
LEFT JOIN dbo.MDT_Position p ON p.PositionID = m.PositionID
LEFT JOIN dbo.MDT_LegalEntity lg ON lg.LegalEntityID = m.LegalEntityID
LEFT JOIN dbo.HR_PF_Team t ON t.TeamID = a.TeamID
WHERE a.IsActive = 1
AND lg.IsActive = 1
AND t.IsActive = 1
AND m.IsActive = 1
AND LEFT(a.Month, 4) = '{0}'", year);
if (!string.IsNullOrEmpty(queryModel.LegalEntityID))
{
sb.AppendFormat(" AND lg.LegalEntityID = '{0}'", queryModel.LegalEntityID);
}
if (!string.IsNullOrEmpty(queryModel.BUID))
{
sb.AppendFormat(" AND a.BUID = '{0}'", queryModel.BUID);
}
if (!string.IsNullOrEmpty(queryModel.DepartmentID))
{
sb.AppendFormat(" AND a.DepartmentID = '{0}'", queryModel.DepartmentID);
}
if (!string.IsNullOrEmpty(queryModel.EmployeeNo))
{
sb.AppendFormat(" AND a.EmployeeNo = '{0}'", queryModel.EmployeeNo);
}
if (!string.IsNullOrEmpty(queryModel.TeamCode))
{
sb.AppendFormat(" AND t.TeamCode = '{0}'", queryModel.TeamCode);
}
if (!string.IsNullOrEmpty(queryModel.PieceType))
{
sb.AppendFormat(" AND t.PieceType = '{0}'", queryModel.PieceType);
}
sb.AppendFormat(@" ) AS bb PIVOT ( SUM(ActJob) FOR Month IN (
[{0}01], [{0}02],
[{0}03], [{0}04],
[{0}05], [{0}06],
[{0}07], [{0}08],
[{0}09], [{0}10],
[{0}11], [{0}12] ) ) AS tt", year);
sb.Append(@" ORDER BY tt.EmployeeNo");
Framework.Db.DbHelper db = Framework.Db.DbFactory.CreateDbRead();
DataSet ds = db.ExecuteDataSet(CommandType.Text, sb.ToString());
if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return new DataTable();
}
}
public MemoryStream ReportExcel(CalcuateQueryViewModel queryModel)
{
DataTable dt = GetReportList(queryModel);
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Style style = workbook.Styles[workbook.Styles.Add()];
style.HorizontalAlignment = TextAlignmentType.Center;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
Cells cells = sheet.Cells;
if (dt.Columns.Count != 0)
{
cells.Merge(0, 0, 1, dt.Columns.Count);
}
cells[0, 0].SetStyle(style);
cells[0, 0].PutValue($"{queryModel.Year}年度员工实际工作量报表");
List<string> columnsList = new List<string> { "法人公司", "BU名称", "部门", "组名称", "考核类型", "工号", "姓名", "岗位", "合格工作量", "合格工作量单位", "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月" };
for (int j = 0; j < columnsList.Count; j++)
{
cells[1, j].SetStyle(style);
}
int rowIndex = 1;
SetColumnsName(cells, columnsList, rowIndex);
rowIndex++;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
style.Font.IsBold = false;
cells[rowIndex, j].SetStyle(style);
if (j > 9)
{
style.Custom = "0.00";
style.HorizontalAlignment = TextAlignmentType.Right;
cells[rowIndex, j].SetStyle(style);
cells[rowIndex, j].PutValue(dt.Rows[i][j].ToString(), true);
}
else
{
style.HorizontalAlignment = TextAlignmentType.Center;
cells[rowIndex, j].SetStyle(style);
cells[rowIndex, j].PutValue(dt.Rows[i][j].ToString());
}
}
rowIndex++;
}
int dataRowIndex = 3;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < columnsList.Count; i++)
{
cells[rowIndex, i].SetStyle(style);
}
cells[rowIndex, 0].PutValue("总计");
cells[rowIndex, 10].Formula = $"=SUM(K{dataRowIndex}:K{rowIndex})";
cells[rowIndex, 11].Formula = $"=SUM(L{dataRowIndex}:L{rowIndex})";
cells[rowIndex, 12].Formula = $"=SUM(M{dataRowIndex}:M{rowIndex})";
cells[rowIndex, 13].Formula = $"=SUM(N{dataRowIndex}:N{rowIndex})";
cells[rowIndex, 14].Formula = $"=SUM(O{dataRowIndex}:O{rowIndex})";
cells[rowIndex, 15].Formula = $"=SUM(P{dataRowIndex}:P{rowIndex})";
cells[rowIndex, 16].Formula = $"=SUM(Q{dataRowIndex}:Q{rowIndex})";
cells[rowIndex, 17].Formula = $"=SUM(R{dataRowIndex}:R{rowIndex})";
cells[rowIndex, 18].Formula = $"=SUM(S{dataRowIndex}:S{rowIndex})";
cells[rowIndex, 19].Formula = $"=SUM(T{dataRowIndex}:T{rowIndex})";
cells[rowIndex, 20].Formula = $"=SUM(U{dataRowIndex}:U{rowIndex})";
cells[rowIndex, 21].Formula = $"=SUM(V{dataRowIndex}:V{rowIndex})";
rowIndex++;
}
return workbook.SaveToStream();
}
public void SetColumnsName(Cells cells, List<string> columnsList, int rowIndex, int startColumnsIndex = 0)
{
for (int i = 0; i < columnsList.Count; i++)
{
cells[rowIndex, i + startColumnsIndex].PutValue(columnsList[i]);
}
}