private void SetBorderTop(int firstRow, int lastRow, int firstCol, int lastCol)
{
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, new CellRangeAddress(firstRow, lastRow, firstCol, lastCol), currentSheet, workbook);
}
public ActionResult ExportData(string queryJson)
{
try
{
Pagination pagination = new Pagination();
pagination.sord = "asc";
pagination.records = 0;
pagination.page = 1;
pagination.rows = 1000000000;
var watch = CommonHelper.TimerStart();
//pagination.p_kid = "v.USERID";
//pagination.p_fields = " v.FULLNAME,v.DEPARTMENTID,v.DEPARTMENTCODE, v.REALNAME,v.DUTYID,v.DUTYNAME,v.REMARK ,v.DEPTTYPE,v.dkremark,v.DEPTSORT , v.SORTCODE,1 as personcount ";
//pagination.p_tablename = @" V_ATTENDANCEWARNING v";
//pagination.conditionJson = " v.DEPARTMENTID not in ('0') ";
//Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
var exportTable = hikinoutlogbll.GetAttendanceWarningPageList(pagination, queryJson);
////设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "人员考勤告警";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 16;
//excelconfig.FileName = "人员考勤告警.xls";
//excelconfig.IsAllSizeColumn = true;
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
////需跟数据源列顺序保持一致
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fullname", ExcelColumn = "单位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname", ExcelColumn = "姓名", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname", ExcelColumn = "岗位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dkremark", ExcelColumn = "备注", Width = 250 });
////调用导出方法
//ExcelHelper.ExcelDownload(exportTable, excelconfig); ////设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "人员考勤告警";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 16;
//excelconfig.FileName = "人员考勤告警.xls";
//excelconfig.IsAllSizeColumn = true;
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
////需跟数据源列顺序保持一致
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fullname", ExcelColumn = "单位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname", ExcelColumn = "姓名", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname", ExcelColumn = "岗位名称", Width = 50 });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dkremark", ExcelColumn = "备注", Width = 250 });
////调用导出方法
//ExcelHelper.ExcelDownload(exportTable, excelconfig);
//导出excel
string title = "人员考勤告警";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 30 * 20;
int column = exportTable.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 22;
font.FontName = "宋体";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
headerFont.FontHeightInPoints = 16;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("单位名称");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("岗位名称");
row1.GetCell(2).CellStyle = headerStyle;
row1.CreateCell(3).SetCellValue("备注");
row1.GetCell(3).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
font1.Boldweight = 25;
font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < exportTable.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(exportTable.Rows[i]["fullname"].ToString() + "(" + exportTable.Rows[i]["personcount"].ToString() + ")");
rowTemp.CreateCell(1).SetCellValue(exportTable.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(exportTable.Rows[i]["dutyname"].ToString());
rowTemp.CreateCell(3).SetCellValue(exportTable.Rows[i]["dkremark"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
rowTemp.GetCell(3).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
//合并单元格
MergeCells(sheet, exportTable, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex)
{
}
return(Success("导出成功。"));
}
public ActionResult ExportData(Pagination pagination, string queryJson)
{
try {
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "v_info.userid";
pagination.p_fields = @"";
pagination.p_tablename = "v_userinfo v_info left join base_department d on v_info.departmentid = d.departmentid left join (select * from HJB_PERSONSET where MODULETYPE = 1) t on v_info.userid = t.userid";
pagination.conditionJson = " Account!='System'";
pagination.sidx = "DEPTSORTss,v_info.DEPTSORT asc,v_info.deptcode asc,v_info.userid";
pagination.sord = "desc";
var data = hikinoutlogbll.GetTableUserRole(pagination, queryJson);
var queryParam = queryJson.ToJObject();
foreach (DataRow dr in data.Rows)
{
if (dr["nature"].ToString() == "专业" || dr["nature"].ToString() == "班组")
{
//DataTable dt = departmentBLL.GetDataTable(string.Format("select fullname from base_department where encode=(select encode from base_department t where instr('{0}',encode)=1 and nature='{1}' and organizeid='{2}') or encode='{0}' order by deptcode", dr["DEPARTMENTCODE"], "部门", dr["organizeid"]));
//if (dt.Rows.Count > 0)
//{
// string name = "";
// foreach (DataRow dr1 in dt.Rows)
// {
// name += dr1["fullname"].ToString() + "/";
// }
// dr["dutyname"] = name.TrimEnd('/');
//}
dr["dutyname"] = dr["dutyname"] + "(" + dr["DEPTNAME"] + ")";
}
if (dr["userid"] != null)
{
string sTime = queryParam["sTime"].ToString();
string eTime = queryParam["eTime"].ToString();
string userId = dr["userid"].ToString();
string sql = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a
left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 0 and
a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd')
and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20))
else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId);
DataTable dt = operticketmanagerbll.GetDataTable(sql);
if (dt.Rows.Count > 0)
{
dr["bmname"] = dr["bmname"] + "(进门总次数：" + dt.Rows[0]["num"].ToString();
}
string sql2 = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a
left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 1 and
a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd')
and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20))
else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId);
DataTable dt2 = operticketmanagerbll.GetDataTable(sql2);
if (dt2.Rows.Count > 0)
{
dr["bmname"] = dr["bmname"] + "出门总次数：" + dt2.Rows[0]["num"].ToString() + ")";
}
}
}
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "人员进出门禁数据信息";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 25;
//excelconfig.FileName = "人员进出门禁数据导出.xls";
//excelconfig.IsAllSizeColumn = true;
////每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "bmname".ToLower(), ExcelColumn = "单位(部门)" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname".ToLower(), ExcelColumn = "姓名" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "intnum".ToLower(), ExcelColumn = "进门次数(次)" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "outnum".ToLower(), ExcelColumn = "出门次数(次)" });
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
//导出excel
string title = "人员进出门禁数据信息";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 24 * 20;
int column = data.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.HeightInPoints = 30;
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 25;
font.FontName = "微软雅黑";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
//headerFont.FontHeightInPoints = 4;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("单位(部门)");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("岗位名称");
row1.GetCell(2).CellStyle = headerStyle;
row1.CreateCell(3).SetCellValue("进门次数(次)");
row1.GetCell(3).CellStyle = headerStyle;
row1.CreateCell(4).SetCellValue("出门次数(次)");
row1.GetCell(4).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
//font1.Boldweight = 25;
//font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < data.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
//rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(data.Rows[i]["bmname"].ToString());
rowTemp.CreateCell(1).SetCellValue(data.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(data.Rows[i]["dutyname"].ToString());
rowTemp.CreateCell(3).SetCellValue(data.Rows[i]["intnum"].ToString());
rowTemp.CreateCell(4).SetCellValue(data.Rows[i]["outnum"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
rowTemp.GetCell(3).CellStyle = bodyStyle;
rowTemp.GetCell(4).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
//合并单元格
MergeCells(sheet, data, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex) {
}
return(Success("导出成功。"));
}
public ActionResult ExportData(string state)
{
try {
var curuser = OperatorProvider.Provider.Current();
string sql = @"select case when b.NATURE = '班组' then b.PARENTNAME else b.DEPTNAME END as DEPTNAME,ID,a.userid,b.REALNAME,b.gender,b.dutyname,a.devicename,TO_CHAR(a.CREATEDATE,'yyyy-mm-dd hh24:mi:ss') as datetime,case when (length(b.deptcode)>20) then (select d.SORTCODE from base_department d where d.deptcode = substr(b.deptcode,1,20)) else b.DEPTSORT end as DEPTSORTss from
bis_hikinoutlog a left join V_USERINFO b on a.userid = b.userid left join(select* from HJB_PERSONSET where MODULETYPE = 0) t on a.userid = t.userid
where REALNAME is not NULL and a.inout = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE + 0 > a.CREATEDATE + 0)
";
//判断当前登陆用户是什么级别
if (!curuser.RoleName.IsEmpty())
{
string RoleName = curuser.RoleName.ToString();
if (ViewBag.IsAppointAccount != 1)
{
if (RoleName.Contains("承包商级用户"))
{
//承包商级用户只可查看本单位门禁数据
sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId);
sql += string.Format(@" and b.ROLENAME like '%{0}%' and b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{1}')", RoleName, curuser.UserId);
}
}
else if (RoleName.Contains("厂级部门用户") || RoleName.Contains("安全管理员") || RoleName.Contains("公司领导") || RoleName.Contains("公司管理员") || RoleName.Contains("公司级用户") || RoleName.Contains("超级管理员") || curuser.UserId.Contains("1521c21a-62c9-4aa1-9093-a8bda503ea89"))
{
//此级别的用户可查看所有数据
}
else
{
sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId);
if (state == "0")
{
sql += string.Format(@" and (b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{0}') or b.PARENTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{0}'))", curuser.UserId);
}
}
}
if (state == "0")
{
sql += " and DEPTTYPE is NULL ORDER BY DEPTSORTss, b.deptsort,b.DEPTCODE,b.userid desc";
}
else
{
sql += " and DEPTTYPE is not NULL ORDER BY DEPTTYPE,DEPTSORTss,b.deptsort,b.DEPTCODE,b.userid desc";
}
DataTable data = operticketmanagerbll.GetDataTable(sql);
//导出excel
string title = "在厂人员统计信息";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 24 * 20;
int column = data.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.HeightInPoints = 30;
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 25;
font.FontName = "微软雅黑";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
//headerFont.FontHeightInPoints = 4;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("部门名称");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("性别");
row1.GetCell(2).CellStyle = headerStyle;
row1.CreateCell(3).SetCellValue("岗位名称");
row1.GetCell(3).CellStyle = headerStyle;
row1.CreateCell(4).SetCellValue("门禁通道名称");
row1.GetCell(4).CellStyle = headerStyle;
row1.CreateCell(5).SetCellValue("进厂时间");
row1.GetCell(5).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
//font1.Boldweight = 25;
//font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < data.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
//rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(data.Rows[i]["deptname"].ToString());
rowTemp.CreateCell(1).SetCellValue(data.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(data.Rows[i]["gender"].ToString());
rowTemp.CreateCell(3).SetCellValue(data.Rows[i]["dutyname"].ToString());
rowTemp.CreateCell(4).SetCellValue(data.Rows[i]["devicename"].ToString());
rowTemp.CreateCell(5).SetCellValue(data.Rows[i]["datetime"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
rowTemp.GetCell(3).CellStyle = bodyStyle;
rowTemp.GetCell(4).CellStyle = bodyStyle;
rowTemp.GetCell(5).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
sheet.AutoSizeColumn(5);
//合并单元格
MergeCells(sheet, data, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex) {
}
return(Success("导出成功。"));
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = (state == "0" ? "内部" : "外部") + "人员统计信息";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 25;
//excelconfig.FileName = "实时在厂人员统计导出.xls";
//excelconfig.IsAllSizeColumn = true;
////每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "部门名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname".ToLower(), ExcelColumn = "姓名" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "gender".ToLower(), ExcelColumn = "性别" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "devicename".ToLower(), ExcelColumn = "门禁通道名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "datetime".ToLower(), ExcelColumn = "进厂时间" });
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
}
public ActionResult ExportData(string queryJson)
{
try
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 1000000000;
pagination.p_kid = "u.USERID";
pagination.p_fields = @"
u.DEPARTMENTID,u.DEPARTMENTCODE, u.REALNAME,u.DUTYID,u.DUTYNAME,u.depttype,
case when u.nature = '班组' then u.parentname else u.DEPTNAME end FULLNAME,
COUNT(u.USERID) OVER(partition by case when u.nature = '班组' then u.parentname else u.DEPTNAME end) AS personcount,
case when(length(u.deptcode) > 20) then(select d.SORTCODE from base_department d where d.deptcode = substr(u.deptcode, 1, 20)) else u.DEPTSORT end as DEPTSORTss ";
pagination.p_tablename = @" V_USERINFO u
left JOIN BIS_HIKINOUTLOG bh on bh.USERID = u.USERID ";
pagination.conditionJson = @" u.DEPARTMENTID not in ('0') AND TO_CHAR(bh.CREATEDATE, 'yyyy-MM-dd') is NULL
AND u.USERID not in (SELECT USERID FROM HJB_PERSONSET WHERE MODULETYPE=3) " ;
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
DataTable exportTable = hikinoutlogbll.GetAbsenteeismPageList(pagination, queryJson);
//导出excel
string title = "连续缺勤统计";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 30 * 20;
int column = exportTable.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 22;
font.FontName = "宋体";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
headerFont.FontHeightInPoints = 16;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("单位名称");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("岗位名称");
row1.GetCell(2).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
font1.Boldweight = 25;
font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < exportTable.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(exportTable.Rows[i]["fullname"].ToString() + "(" + exportTable.Rows[i]["personcount"].ToString() + ")");
rowTemp.CreateCell(1).SetCellValue(exportTable.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(exportTable.Rows[i]["dutyname"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
//合并单元格
MergeCells(sheet, exportTable, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex)
{
}
return(Success("导出成功。"));
}