工时汇总.png
public void NpoiExportExcel2(DateTime startWeek, DateTime endWeek)
{
DataTable dt = UseTimeService.Instance.GetDataTableApply();
var fileName = "asdasda";
var exportTemplatePath = "~/ManhourTemplate/工时表.xlsx";
DownloadExcel(fileName, dt, exportTemplatePath);
}
public void DownloadExcel(string reportName, DataTable dt,string exportTemplatePath)
{
Stream s = RenderDataTableToExcel2(dt, exportTemplatePath);
if (s != null)
{
MemoryStream ms = s as MemoryStream;
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(reportName) + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"));
System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
System.Web.HttpContext.Current.Response.Flush();
ms.Close();
ms.Dispose();
}
else
{
System.Web.HttpContext.Current.Response.Write("出错，无法下载！");
}
}
public Stream RenderDataTableToExcel2(DataTable sourceTable, string exportTemplatePath)
{
XSSFWorkbook workbook = null;
MemoryStream ms = null;
ISheet sheet = null;
XSSFRow headerRow = null;
string templetFileName = System.Web.HttpContext.Current.Server.MapPath(exportTemplatePath);
FileStream file = new FileStream(templetFileName, FileMode.Open, FileAccess.Read);
workbook = new XSSFWorkbook(file);
DataTable psdt = UseTimeService.Instance.GetPersonTotalWorkTime(); //个人工时总计
DataTable pjdt = UseTimeService.Instance.GetProjectTotalUseTime(); //项目耗时总计
string alltime = UseTimeService.Instance.GetAllUseTime(); //项目耗时总计
try
{
ms = new MemoryStream();
sheet = workbook.GetSheet("汇总");
int rowIndex = sheet.LastRowNum;
IFont font = workbook.CreateFont();//声明字体
font.Boldweight = (Int16)FontBoldWeight.Bold;//加粗
font.FontHeightInPoints = 18;//字体大小
var cellStyle = workbook.CreateCellStyle();
DefineCellStyle(cellStyle);
cellStyle.TopBorderColor = HSSFColor.BrightGreen.Index; //边框颜色
cellStyle.SetFont(font);//加入单元格
cellStyle.Alignment = HorizontalAlignment.Center; // 水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; // 垂直居中
List<string> projectlist = new List<string>();
List<string> stafflist = new List<string>();
stafflist.Add("工时项目");
int n = 2;//因为模板有表头，所以从第3行开始写
for (int j = 0; j < sourceTable.Rows.Count; j++)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(j + n);
string ProjectName = sourceTable.Rows[j]["项目名称"].ToString().Trim();
string StaffName = sourceTable.Rows[j]["员工姓名"].ToString().Trim();
projectlist.Add(ProjectName);
stafflist.Add(StaffName);
}
stafflist.Add("总计");
projectlist.Add("总计");
//设置表头
XSSFRow headDataRow = sheet.CreateRow(0) as XSSFRow;
headDataRow.HeightInPoints = 30; // 行高
XSSFCell headCell = headDataRow.CreateCell(0) as XSSFCell; // 创建单元格
headCell.SetCellValue("综管部工时汇总（ "+DateTime.Now.ToString()+" )");
headCell.CellStyle = cellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0,0,0, psdt.Rows.Count+1)); //合并单元格
XSSFRow dataRow2 = (XSSFRow)sheet.CreateRow(1);
for (int i = 0; i < projectlist.Distinct().ToArray().Length; i++)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(i+n);
DefineCellStyle(dataRow.CreateCell(0).CellStyle);
// dataRow.Cells[0].SetCellValue(projectlist.Distinct().ToArray()[i].Trim().ToString());
for (int j = 0; j < stafflist.Distinct().ToArray().Length; j++)
{
XSSFCell newCell = dataRow2.CreateCell(j) as XSSFCell;
XSSFCell newCell2 = dataRow.CreateCell(j) as XSSFCell;
DefineCellStyle(dataRow2.CreateCell(j).CellStyle);
newCell.SetCellValue(stafflist.Distinct().ToArray()[j].Trim().ToString());
for (int k = 0; k < sourceTable.Rows.Count; k++)
{
if (sourceTable.Rows[k]["项目名称"].ToString().Trim().Equals(projectlist.Distinct().ToArray()[i].Trim().ToString()) && sourceTable.Rows[k]["员工姓名"].ToString().Trim().Equals(stafflist.Distinct().ToArray()[j].Trim().ToString()))
{
newCell2.SetCellValue(sourceTable.Rows[k]["总工时"].ToString().Trim());
dataRow.Cells[0].SetCellValue(projectlist.Distinct().ToArray()[i].Trim().ToString());
}
}
}
if (i< projectlist.Distinct().ToArray().Length-1)
{
for (int p = 0; p < pjdt.Rows.Count; p++)
{
if (pjdt.Rows[p]["项目名称"].ToString().Trim().Equals(projectlist.Distinct().ToArray()[i].Trim().ToString()))
{
dataRow.Cells[stafflist.Distinct().ToArray().Length - 1].SetCellValue(pjdt.Rows[p]["项目工时总计"].ToString().Trim());
}
}
}
else
{
//alltime
dataRow.Cells[stafflist.Distinct().ToArray().Length - 1].SetCellValue(alltime);
}
if (i== (projectlist.Distinct().ToArray().Length-1))
{
dataRow.Cells[0].SetCellValue(projectlist.Distinct().ToArray()[projectlist.Distinct().ToArray().Length-1].Trim().ToString());
for (int j = 0; j < psdt.Rows.Count; j++)
{
dataRow.Cells[j+1].SetCellValue(psdt.Rows[j]["个人工时总计"].ToString().Trim());
}
}
}
//列宽自适应，只对英文和数字有效
for (int i = 0; i <= sourceTable.Columns.Count; ++i)
{
sheet.SetDefaultColumnStyle(i,cellStyle);
sheet.AutoSizeColumn(i);
}
workbook.Write(ms);
ms.Flush();
}
catch (Exception ex)
{
throw;
return null;
}
finally
{
ms.Close();
sheet = null;
headerRow = null;
workbook = null;
}
return ms;
}
public void DefineCellStyle(ICellStyle cellStyle)
{
cellStyle.BorderBottom = BorderStyle.Thin; //下边框
cellStyle.BorderLeft = BorderStyle.Thin;//左边框
cellStyle.BorderTop = BorderStyle.Thin;//上边框
cellStyle.BorderRight = BorderStyle.Thin;//右边框
}
// 集合转 DataTable类型
public static DataTable ToDataTable<T>(IEnumerable<T> collection)
{
var props = typeof(T).GetProperties();
var dt = new DataTable();
dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
if (collection.Count() > 0)
{
for (int i = 0; i < collection.Count(); i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in props)
{
object obj = pi.GetValue(collection.ElementAt(i), null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
dt.LoadDataRow(array, true);
}
}
return dt;
}
网友评论