62,046
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 生成excel
/// </summary>
/// <param name="dt"></param>
/// <param name="title"></param>
/// <param name="Sum"></param>
/// <returns></returns>
private HSSFWorkbook DealDataTable(List<DataTable> dt, List<string> title, List<string> Sum)
{
HSSFWorkbook saraly = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "广州xxxx公司";
saraly.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "";
saraly.SummaryInformation = si;
/*首行的标题*/
HSSFCellStyle fist1 = saraly.CreateCellStyle();
fist1.WrapText = false;
HSSFFont fist1Font = saraly.CreateFont();
fist1Font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
fist1Font.FontHeight = 480;
fist1.SetFont(fist1Font);
fist1.Alignment = HSSFCellStyle.ALIGN_CENTER;
fist1.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*表头*/
HSSFCellStyle fist2 = saraly.CreateCellStyle();
fist2.WrapText = true;
HSSFFont fist2Font = saraly.CreateFont();
fist2Font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
fist2.SetFont(fist2Font);
fist2.Alignment = HSSFCellStyle.ALIGN_CENTER;
fist2.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*内容*/
HSSFCellStyle context = saraly.CreateCellStyle();
context.WrapText = true;
HSSFFont font = saraly.CreateFont();
context.SetFont(font);
context.Alignment = HSSFCellStyle.ALIGN_CENTER;
context.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*红色文字的内容*/
HSSFCellStyle contextRed = saraly.CreateCellStyle();
contextRed.WrapText = true;
HSSFFont fontRed = saraly.CreateFont();
fontRed.Color = HSSFFont.COLOR_RED;
contextRed.SetFont(fontRed);
contextRed.Alignment = HSSFCellStyle.ALIGN_CENTER;
contextRed.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*红色背景的文字内容*/
HSSFCellStyle contextGood = saraly.CreateCellStyle();
contextGood.WrapText = true;
HSSFFont fontGood = saraly.CreateFont();
contextGood.SetFont(fontGood);
contextGood.Alignment = HSSFCellStyle.ALIGN_CENTER;
contextGood.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
HSSFPalette customPalette = saraly.GetCustomPalette();
customPalette.SetColorAtIndex(HSSFColor.LIGHT_BLUE.index, (byte) 228, (byte) 223, (byte)236);
contextGood.FillPattern=HSSFCellStyle.SOLID_FOREGROUND;
contextGood.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
/*循环表*/
for (int i = 0; i < dt.Count; i++)
{
HSSFSheet sheet = saraly.CreateSheet(title[i]);
int ColumnsCount = dt[i].Columns.Count;
if (dt[i].Columns.Contains("sysOutFlag"))
{
ColumnsCount = ColumnsCount - 1;
}
if (title[i] != "sheet1")
{
/*首行*/
HSSFRow rowTitle = sheet.CreateRow(0);
rowTitle.Height = 800;
HSSFCell cell = rowTitle.CreateCell(0);
cell.CellStyle = fist1;
cell.SetCellValue(title[i]);
sheet.AddMergedRegion(new Region(0, 0, 0, ColumnsCount - 1));
/*标题*/
HSSFRow rowTitle1 = sheet.CreateRow(1);
rowTitle1.Height = 730;
for (int ii = 0; ii < ColumnsCount; ii++)
{
HSSFCell cellT = rowTitle1.CreateCell(ii);
cellT.CellStyle = fist2;
cellT.SetCellValue(dt[i].Columns[ii].ToString());
cellT.SetCellType(HSSFCell.CELL_TYPE_STRING);
}
}
Dictionary<string, itemSum> a = new Dictionary<string, itemSum>();
/*内容*/
for (int ii = 0; ii < dt[i].Rows.Count; ii++)
{
HSSFRow rowContent = sheet.CreateRow(ii + (title[i] == "sheet1" ? 0 : 2));
rowContent.Height = 620;
for (int jj = 0; jj < ColumnsCount; jj++)
{
HSSFCell cellT = rowContent.CreateCell(jj);
cellT.CellStyle = context;
if (dt[i].Columns.Contains("sysOutFlag"))
{
string sysOutFlag = dt[i].Rows[ii]["sysOutFlag"].ToString();
if (sysOutFlag == "1")
{
cellT.CellStyle = contextRed;
}
else if (sysOutFlag == "2")
{
cellT.CellStyle = contextGood;
}
}
setDataColumnValue(cellT, dt[i].Columns[jj].DataType.ToString(), "", dt[i].Rows[ii][jj].ToString());
if (Sum.Contains(dt[i].Columns[jj].ColumnName))
{
if (a.ContainsKey(dt[i].Columns[jj].ColumnName))
{
a[dt[i].Columns[jj].ColumnName].toalCnt = a[dt[i].Columns[jj].ColumnName].toalCnt + decimal.Parse(dt[i].Rows[ii][jj].ToString());
}
else
{
a.Add(dt[i].Columns[jj].ColumnName, new itemSum(jj, decimal.Parse(dt[i].Rows[ii][jj].ToString())));
}
}
}
if (title[i] == "sheet1")
{
sheet.AddMergedRegion(new Region(ii, 0, ii, 10));
}
}
/*附加列*/
if (dt[i].Rows.Count > 1)
{
HSSFRow rowSum = sheet.CreateRow(dt[i].Rows.Count + 2);
rowSum.Height = 620;
foreach (KeyValuePair<string, itemSum> keyValuePair in a)
{
HSSFCell cellSum = rowSum.CreateCell(keyValuePair.Value.index);
cellSum.CellStyle = context;
setDataColumnValue(cellSum, "System.Decimal", "", keyValuePair.Value.toalCnt.ToString());
}
}
/*兼职*/
if (dt[i].Columns.Contains("教师") && dt[i].Columns.Contains("职工代码") && dt[i].Columns.Contains("类型") && dt[i].Columns.Contains("标准"))
{
HSSFCellStyle bottom = saraly.CreateCellStyle();
bottom.WrapText = false;
bottom.Alignment = HSSFCellStyle.ALIGN_CENTER;
bottom.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
bottom.BorderTop = HSSFCellStyle.BORDER_THIN;
HSSFRow rowSum = sheet.CreateRow(dt[i].Rows.Count + 4);
sheet.AddMergedRegion(new Region(dt[i].Rows.Count + 4, 0, dt[i].Rows.Count + 4, ColumnsCount - 1));
rowSum.Height = 620;
HSSFCell buttomCell = rowSum.CreateCell(0);
buttomCell.CellStyle = bottom;
if (dt[i].Columns.Contains("课时费(元)"))
{
setDataColumnValue(buttomCell, "部门负责人: 人力资源审核: 制表人: ");
}
else {
setDataColumnValue(buttomCell, "制表人 教务负责人 ");
}
}
}
return saraly;
}