exceldaozhuanyi

Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells;//单元格

 

//列头样式
Style styleHeader = workbook.Styles[workbook.Styles.Add()];//新增样式
styleHeader.HorizontalAlignment = TextAlignmentType.Center;//文字居中
styleHeader.Font.Name = "宋体";//文字字体
styleHeader.Font.Color = System.Drawing.Color.White;
styleHeader.Font.Size = 14;//文字大小
styleHeader.Font.IsBold = true;//粗体
styleHeader.IsTextWrapped = true;//单元格内容自动换行
//背景颜色
styleHeader.ForegroundColor= System.Drawing.Color.FromArgb(255,60,77,232);
styleHeader.Pattern = BackgroundType.Solid;

//上下左右增加细边框线
styleHeader.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

 

Style styleContent = workbook.Styles[workbook.Styles.Add()];//新增样式
styleContent.HorizontalAlignment = TextAlignmentType.Center;//文字靠左
styleContent.Font.Name = "宋体";//文字字体
styleContent.Font.Size = 12;//文字大小
styleContent.IsTextWrapped = true;//单元格内容自动换行
styleContent.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleContent.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//styleContent.Borders.SetColor(System.Drawing.Color.Black);

var clcount = ((dynamic)tongjilist[0]).WORKLIST.Count;
var rowcount = tongjilist.Count;
cells[0, 0].PutValue("序号");
cells[0, 0].SetStyle(styleHeader);
cells.SetColumnWidthPixel(0, 60);//设置单元格200宽度
cells.SetRowHeight(0, 30);//第一行，30px高

cells[0, 1].PutValue("姓名");
cells[0, 1].SetStyle(styleHeader);
cells.SetColumnWidthPixel(1, 80);

 

for (int i = 2; i < clcount + 2; i++)
{
cells[0, i].PutValue(i-1);
cells[0, i].SetStyle(styleHeader);
cells.SetColumnWidthPixel(i, 60);
}

cells[0, clcount + 2].PutValue("合计");
cells[0, clcount + 2].SetStyle(styleHeader);
cells.SetColumnWidthPixel(clcount + 2, 60);

for (int a = 1; a < rowcount + 1; a++)
{

var titem = (dynamic)tongjilist[a - 1];
var itemworklist = (dynamic)titem.WORKLIST;

cells[a, 0].PutValue(a);
cells[a, 0].SetStyle(styleContent);
cells.SetRowHeight(a, 25);

cells[a, 1].PutValue(titem.PEOPLENAME);
cells[a, 1].SetStyle(styleContent);
for (int i = 2; i < clcount + 3; i++)
{

 

if (i == clcount + 2)
{
cells[a, i].PutValue(titem.WORKDAYCOUNT);
cells[a, i].SetStyle(styleContent);

}
else
{
var workitem = itemworklist[i - 2];

 

if (workitem.DAYSTATUS == 1)
{
cells[a, i].PutValue("");
cells[a, i].SetStyle(styleContent);

}
else if (workitem.DAYSTATUS == 2)
{
cells[a, i].PutValue("");
cells[a, i].SetStyle(styleContent);
}
else if (workitem.TYPEID == 0)
{

cells[a, i].PutValue("");
cells[a, i].SetStyle(styleContent);
}
else
{
cells[a, i].PutValue(workitem.TYPENAME);
cells[a, i].SetStyle(styleContent);

}
}
}

}

// sheet.FreezePanes(2, 0, 2, clcount+3);//冻结标题行

string sUpFileDir = "~/UploadFiles/Integratedinfo/KPIKCI/Workinfo/" + string.Format("{0:yyyyMMdd}", DateTime.Now) + "/";
string sSavePath = HttpContext.Current.Server.MapPath(@sUpFileDir);
if (Directory.Exists(sSavePath) == false)
{
Directory.CreateDirectory(sSavePath);
}
string dataname = inmonthtime.Year + "年" + inmonthtime.Month + "月";
string filename = string.Format("{0}_{1}_{2:yyyyMMddHHmmss}", dataname, "考勤", DateTime.Now) + ".xls";

sSavePath = System.IO.Path.Combine(sSavePath, filename);

workbook.Save(sSavePath);

string fanhui = sUpFileDir.Substring(1) + filename;

 

 

return JsonResult((int)StatusCodeEnum.Success, "统计", fanhui);