[HttpGet]
[IdentifyDescription("all")]
public HttpResponseMessage RegistrationTaskExportAll()
{
var list = db.Queryable<cppccmembers>().ToList();
var ids_users = list.Select(v => v.userID);
var mlUsers = db.Queryable<users>().Where(v => ids_users.Contains(v.ID)).ToList();
var ids_nation = list.Select(v => v.nationID ?? 0);
var mlNation = db.Queryable<nation>().Where(v => ids_nation.Contains(v.ID)).ToList();
var mlDict = common.Tools.GetDir(db, new long[] { 4, 16, 70 });
var ALlsug = db.Queryable<suggestion>().ToList();
var suggert = ALlsug.Where(v => SqlFunc.ContainsArray(ids_users.ToList(), v.userID)).ToList();
var sugID = suggert.Select(v => v.ID).ToList();
var reply = db.Queryable<suggestion_reply>().Where(v => SqlFunc.ContainsArray(sugID, v.suggestionID)).OrderBy(v => v.insertTime, OrderByType.Asc).ToList();
int mergeStartRow = 0; // 从第几行开始合并
int mergeEndRow = 0; // 到第几行结束合并
int mergeStartCol = 0; // 从第几列开始合并
int mergeEndCol = 0; // 到第几列结束合并
//首先创建Excel文件对象
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("码上见代表");
var row = ExcelExportHelper._.CreateRow(sheet, 0, 28);
var cell = row.CreateCell(0);
sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）
//单元格边框样式
var cellStyle = ExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Left, VerticalAlignment.Center, 10, false, 400 , "宋体", true, false, true);
//二级标题列样式设置
var headTopStyle = ExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, false, 700, "楷体", true, false, false, false, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
FontUnderlineType.None, FontSuperScript.None, false);
//表头名称
var headerName = new[] { "姓名", "性别", "手机号码", "党派", "代表团", "人大职务", "工作单位及职务", "群众姓名", "群众手机号", "建议内容", "答复内容" };
row = ExcelExportHelper._.CreateRow(sheet, 0, 24);
for (var i = 0; i < headerName.Length; i++)
{
cell = ExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]);
sheet.SetColumnWidth(i, 5000);
}
var rowlength = 0;
for (int i = 0; i < list.Count; i++)
{
var length = rowlength;
var count = suggert.Where(c => c.userID == list[i].userID).ToList();
var singleUser = mlUsers.FirstOrDefault(m => m.ID == list[i].userID);
mergeStartRow = mergeStartRow +1;
mergeEndRow = mergeEndRow + 1;
if (count.Count < 2)
{
for (var j = 0; j < 1; j++)
{
var replyContent = "";
var index = 1;
reply.Where(c => c.suggestionID == suggert.Where(a => a.userID == list[i].userID).FirstOrDefault()?.ID).ToList().ForEach(c =>
{
replyContent += (index + "." + c.contents + "");
index++;
});
row = ExcelExportHelper._.CreateRow(sheet, rowlength + 1, 20);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 0, singleUser.name);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 1, Reuse.GetDicValue(common.PlatformDict.GenderDict, (list[i].gender ?? 0)));
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 2, singleUser.mobilePhone);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 3, mlDict.FirstOrDefault(m => m.ID == (list[i].partyID ?? 0))?.title);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 4, mlDict.FirstOrDefault(m => m.ID == (list[i].circlesID ?? 0))?.title);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 5, mlDict.FirstOrDefault(m => m.ID == (list[i].CPPCCPostID ?? 0))?.title);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 6, list[i].workUnitPost);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 7, count.Where(v=>v.userID == list[i].userID).FirstOrDefault()?.name);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 8, count.Where(v => v.userID == list[i].userID).FirstOrDefault()?.mobilePhonex);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 9, count.Where(v => v.userID == list[i].userID).FirstOrDefault()?.contents);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 10, replyContent);
rowlength++;
length++;
}
}
else
{
mergeEndRow = mergeEndRow + count.Count-1;
length= length + count.Count;
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRow, mergeEndRow, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRow, mergeEndRow, 1, 1));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRow, mergeEndRow, 2, 2));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRow, mergeEndRow, 3, 3));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRow, mergeEndRow, 4, 4));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRow, mergeEndRow, 5, 5));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRow, mergeEndRow, 6, 6));
mergeStartRow = mergeStartRow + count.Count -1;
for (var j = rowlength; j < length; j++)
{
var replyContent = "";
var index = 1;
reply.Where(c => c.suggestionID == suggert.Where(a => a.userID == list[i].userID).FirstOrDefault()?.ID).ToList().ForEach(c =>
{
replyContent += (index + "." + c.contents + "");
index++;
});
row = ExcelExportHelper._.CreateRow(sheet, rowlength + 1, 20);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 0, singleUser.name);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 1, Reuse.GetDicValue(common.PlatformDict.GenderDict, (list[i].gender ?? 0)));
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 2, singleUser.mobilePhone);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 3, mlDict.FirstOrDefault(m => m.ID == (list[i].partyID ?? 0))?.title);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 4, mlDict.FirstOrDefault(m => m.ID == (list[i].circlesID ?? 0))?.title);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 5, mlDict.FirstOrDefault(m => m.ID == (list[i].CPPCCPostID ?? 0))?.title);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 6, list[i].workUnitPost);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 7, count[length - rowlength-1].name);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 8, count[length - rowlength-1].mobilePhonex);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 9, count[length - rowlength - 1].contents);
cell = ExcelExportHelper._.CreateCells(row, cellStyle, 10, replyContent);
rowlength++;
}
}
}
MemoryStream memoryStream = new MemoryStream();
workbook.Write(memoryStream);
byte[] result = memoryStream.ToArray();
memoryStream.Close();
workbook.Close();
return Reuse.ReturnFile(result);
}ExcelExportHelper.cs
public class ExcelExportHelper
{
private static ExcelExportHelper _exportHelper;
public static ExcelExportHelper _
{
get => _exportHelper ?? (_exportHelper = new ExcelExportHelper());
set => _exportHelper = value;
}
/// <summary>
/// TODO:先创建行，然后在创建对应的列
/// 创建Excel中指定的行
/// </summary>
/// <param name="sheet">Excel工作表对象</param>
/// <param name="rowNum">创建第几行(从0开始)</param>
/// <param name="rowHeight">行高</param>
public HSSFRow CreateRow(ISheet sheet, int rowNum, float rowHeight)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(rowNum); //创建行
row.HeightInPoints = rowHeight; //设置列头行高
return row;
}
/// <summary>
/// 创建行内指定的单元格
/// </summary>
/// <param name="row">需要创建单元格的行</param>
/// <param name="cellStyle">单元格样式</param>
/// <param name="cellNum">创建第几个单元格(从0开始)</param>
/// <param name="cellValue">给单元格赋值</param>
/// <returns></returns>
public HSSFCell CreateCells(HSSFRow row, HSSFCellStyle cellStyle, int cellNum, string cellValue)
{
HSSFCell cell = (HSSFCell)row.CreateCell(cellNum); //创建单元格
cell.CellStyle = cellStyle; //将样式绑定到单元格
if (!string.IsNullOrWhiteSpace(cellValue))
{
//单元格赋值
cell.SetCellValue(cellValue);
}
return cell;
}
/// <summary>
/// 行内单元格常用样式设置
/// </summary>
/// <param name="workbook">Excel文件对象</param>
/// <param name="hAlignment">水平布局方式</param>
/// <param name="vAlignment">垂直布局方式</param>
/// <param name="fontHeightInPoints">字体大小</param>
/// <param name="isAddBorder">是否需要边框</param>
/// <param name="boldWeight">字体加粗 (None = 0,Normal = 400，Bold = 700</param>
/// <param name="fontName">字体（仿宋，楷体，宋体，微软雅黑...与Excel主题字体相对应）</param>
/// <param name="isAddBorderColor">是否增加边框颜色</param>
/// <param name="isItalic">是否将文字变为斜体</param>
/// <param name="isLineFeed">是否自动换行</param>
/// <param name="isAddCellBackground">是否增加单元格背景颜色</param>
/// <param name="fillPattern">填充图案样式(FineDots 细点，SolidForeground立体前景，isAddFillPattern=true时存在)</param>
/// <param name="cellBackgroundColor">单元格背景颜色（当isAddCellBackground=true时存在）</param>
/// <param name="fontColor">字体颜色</param>
/// <param name="underlineStyle">下划线样式（无下划线[None],单下划线[Single],双下划线[Double],会计用单下划线[SingleAccounting],会计用双下划线[DoubleAccounting]）</param>
/// <param name="typeOffset">字体上标下标(普通默认值[None],上标[Sub],下标[Super]),即字体在单元格内的上下偏移量</param>
/// <param name="isStrikeout">是否显示删除线</param>
/// <returns></returns>
public HSSFCellStyle CreateStyle(HSSFWorkbook workbook, HorizontalAlignment hAlignment, VerticalAlignment vAlignment, short fontHeightInPoints, bool isAddBorder , short boldWeight, string fontName = "宋体", bool isAddBorderColor = false, bool isItalic = false, bool isLineFeed = false, bool isAddCellBackground = false, FillPattern fillPattern = FillPattern.NoFill, short cellBackgroundColor = HSSFColor.Yellow.Index, short fontColor = HSSFColor.Black.Index, FontUnderlineType underlineStyle =
FontUnderlineType.None, FontSuperScript typeOffset = FontSuperScript.None, bool isStrikeout = false)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //创建列头单元格实例样式
cellStyle.Alignment = hAlignment; //水平居中
cellStyle.VerticalAlignment = vAlignment; //垂直居中
cellStyle.WrapText = isLineFeed;//自动换行
//背景颜色，边框颜色，字体颜色都是使用 HSSFColor属性中的对应调色板索引，关于 HSSFColor 颜色索引对照表，详情参考：https://www.cnblogs.com/Brainpan/p/5804167.html
//TODO：引用了NPOI后可通过ICellStyle 接口的 FillForegroundColor 属性实现 Excel 单元格的背景色设置，FillPattern 为单元格背景色的填充样式
//TODO:十分注意，要设置单元格背景色必须是FillForegroundColor和FillPattern两个属性同时设置，否则是不会显示背景颜色
if (isAddCellBackground)
{
cellStyle.FillForegroundColor = cellBackgroundColor;//单元格背景颜色
cellStyle.FillPattern = fillPattern;//填充图案样式(FineDots 细点，SolidForeground立体前景)
}
//是否增加边框
if (isAddBorder)
{
//常用的边框样式 None(没有),Thin(细边框，瘦的),Medium(中等),Dashed(虚线),Dotted(星罗棋布的),Thick(厚的),Double(双倍),Hair(头发)[上右下左顺序设置]
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
}
//是否设置边框颜色
if (isAddBorderColor)
{
//边框颜色[上右下左顺序设置]
cellStyle.TopBorderColor = HSSFColor.DarkGreen.Index;//DarkGreen(黑绿色)
cellStyle.RightBorderColor = HSSFColor.DarkGreen.Index;
cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Index;
cellStyle.LeftBorderColor = HSSFColor.DarkGreen.Index;
}
/**
* 设置相关字体样式
*/
var cellStyleFont = (HSSFFont)workbook.CreateFont(); //创建字体
//假如字体大小只需要是粗体的话直接使用下面该属性即可
//cellStyleFont.IsBold = true;
cellStyleFont.Boldweight = boldWeight; //字体加粗
cellStyleFont.FontHeightInPoints = fontHeightInPoints; //字体大小
cellStyleFont.FontName = fontName;//字体（仿宋，楷体，宋体 ）
cellStyleFont.Color = fontColor;//设置字体颜色
cellStyleFont.IsItalic = isItalic;//是否将文字变为斜体
cellStyleFont.Underline = underlineStyle;//字体下划线
cellStyleFont.TypeOffset = typeOffset;//字体上标下标
cellStyleFont.IsStrikeout = isStrikeout;//是否有删除线
cellStyle.SetFont(cellStyleFont); //将字体绑定到样式
return cellStyle;
}
//TODO:关于Excel行列单元格合并问题
#region
// //一、首先创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
// var sheet = workbook.CreateSheet("Excel底部名称");
// //二、指定合并的行列
// /**
// 第一个参数：从第几行开始合并
// 第二个参数：到第几行结束合并
// 第三个参数：从第几列开始合并
// 第四个参数：到第几列结束合并
//**/
// CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
// sheet.AddMergedRegion(region);
// //三、设置单元格的列宽sheet.SetColumnWidth(单元格索引,1000);//设置对应列宽（单元格索引从0开始，后面接宽度）
#endregion
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
