先放两张图
页面效果图
excel效果图
//先获取到表格
try
{
string ParentGUID =“”;
//离职人员工龄分析统计
DataTable dt1 = GetDataTable();
DataTable dtResult = new DataTable();
//克隆表结构
dtResult = dt1.Clone();
foreach (DataColumn col in dtResult.Columns)
{
//修改列类型
col.DataType = typeof(string);
}
foreach (DataRow row in dt1.Rows)
{
DataRow newDtRow = dtResult.NewRow();
foreach (DataColumn column in dt1.Columns)
{
newDtRow[column.ColumnName] = Convert.ToString(row[column.ColumnName]);
}
dtResult.Rows.Add(newDtRow);
}
//插入列名
int _type = 0;
var _ColumnName = GetDataTable().Select();
foreach (var _cln in _ColumnName)
{
dtResult.Columns.Add(_cln["Name"].ToString());
}
foreach (DataRow _row in dtResult.Rows)
{
var _deptId = _row["DeptGuid"].ToString();
if (_deptId == Guid.Empty.ToString())
{
_type = 1;
_deptId = ParentGUID;
}
var _Dt = GetTitleLevelDataTable(_deptId, _type);
foreach (DataRow _dr in _Dt.Rows)
{
_row[_dr["Name"].ToString()] = _dr["num"];
}
}
}
catch (Exception ex)
{
}
//导出方法
IWorkbook excel = new HSSFWorkbook();//创建.xls文件
ISheet sheet = excel.CreateSheet("员工离职分析表"); //创建sheet
//获取动态列
var _dtCount = GetDataTable();
ICellStyle style = excel.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
IFont font = excel.CreateFont();
font.Boldweight = 600;
font.FontHeight = 16 * 16;
style.SetFont(font);
#region 构造表头
IRow row = sheet.CreateRow(0);//创建行对象，填充表头
ICell cell = row.CreateCell(0);
cell.SetCellValue("员工离职分析表");
cell.CellStyle = style;
#region#创建第一行
row = sheet.CreateRow(1);
cell = row.CreateCell(0);
cell.SetCellValue("年度");
cell.CellStyle = style;
cell = row.CreateCell(1);
cell.SetCellValue(#34;{ DateTime.Now.Year}年");
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("");
cell.CellStyle = style;
for (int i = 1; i <= _dtCount.Rows.Count; i++)
{
cell = row.CreateCell(i + 6);
cell.SetCellValue("");
cell.CellStyle = style;
}
#endregion
#region#创建第二行
row = sheet.CreateRow(2);
cell = row.CreateCell(0);
cell.SetCellValue("序号");
cell.CellStyle = style;
cell = row.CreateCell(1);
cell.SetCellValue("组织名称");
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue("离职人员工龄统计(L)");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("");
cell.CellStyle = style;
for (int i = 1; i <= _dtCount.Rows.Count; i++)
{
cell = row.CreateCell(i + 6);
cell.SetCellValue("职称统计");
cell.CellStyle = style;
}
#endregion
#region#创建第三行
row = sheet.CreateRow(3);
cell = row.CreateCell(0);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(1);
cell.SetCellValue("");
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue("L<1");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("1<=L<3");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("3<=L<5");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("5<=L<10");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("10<=L");
cell.CellStyle = style;
for (int i = 1; i <= _dtCount.Rows.Count; i++)
{
cell = row.CreateCell(i + 6);
cell.SetCellValue(_dtCount.Rows[i - 1]["Name"].ToString());
cell.CellStyle = style;
}
#endregion
#endregion
//合并行
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6 + _dtCount.Rows.Count));//标题
sheet.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));//序号
sheet.AddMergedRegion(new CellRangeAddress(2, 3, 1, 1));//组织名称
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 2, 6));//离职人员工龄统计(L)
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 7, 6 + _dtCount.Rows.Count));//职称统计
// 报表开始
DataTable dataTable = GetEmployeeLeaveReport().data as DataTable;
ICellStyle _style = excel.CreateCellStyle();
_style.Alignment = HorizontalAlignment.Center;
_style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
for (int i = 0; i < dataTable.Rows.Count; i++)
{
IRow drow = sheet.CreateRow(i + 4);
ICell cellbody = drow.CreateCell(0, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i]["RowNum"]?.ToString());
cellbody.CellStyle = _style;
cellbody = drow.CreateCell(1, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i]["DeptName"]?.ToString());
cellbody.CellStyle = _style;
cellbody = drow.CreateCell(2, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i]["L<1"]?.ToString());
cellbody.CellStyle = _style;
cellbody = drow.CreateCell(3, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i]["1<=L<3"]?.ToString());
cellbody.CellStyle = _style;
cellbody = drow.CreateCell(4, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i]["3<=L<5"]?.ToString());
cellbody.CellStyle = _style;
cellbody = drow.CreateCell(5, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i]["5<=L<10"]?.ToString());
cellbody.CellStyle = _style;
cellbody = drow.CreateCell(6, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i]["10<=L"]?.ToString());
cellbody.CellStyle = _style;
for (int j = 1; j <= _dtCount.Rows.Count; j++)
{
var _Clname = _dtCount.Rows[j - 1]["Name"].ToString();
cellbody = drow.CreateCell(j + 6, CellType.String);
cellbody.SetCellValue(dataTable.Rows[i][_Clname]?.ToString());
cellbody.CellStyle = _style;
}
}
//自适应列宽
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
HttpResponse httpResponse = HttpContext.Current.Response;
httpResponse.Clear();
httpResponse.Buffer = true;
httpResponse.Charset = Encoding.UTF8.BodyName;
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("员工离职分析表", System.Text.Encoding.UTF8) + ".xls");
httpResponse.ContentEncoding = Encoding.UTF8;
httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";
excel.Write(httpResponse.OutputStream);
httpResponse.End();
